Creating and using a Cursor

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mghale - 20 Apr 2006 16:55 GMT

I'm trying to create a cursor and complete some processing against DB2.
I'm using DB2 V8.2 on AIX. I don't wont to create a stored procedure,
I just want to use a regular query editor such as command editor or
DBArtisan.

It seems DB2 doesn't like the cursor syntax in either one of these
tools. Can you use cursours outside of a program or SPROC? and just in
a simple SQL File?

In SQL Server I can use cursors, and everything for that matter right
from Query Analyzer. I guess my question is can I do that same sort of
excercise using the DB2 tools?

Thanks...

Reply to this Message

Knut Stolze - 20 Apr 2006 17:12 GMT

> I'm trying to create a cursor and complete some processing against DB2.
> I'm using DB2 V8.2 on AIX. I don't wont to create a stored procedure,
[quoted text clipped - 8 lines]
> from Query Analyzer. I guess my question is can I do that same sort of
> excercise using the DB2 tools?

I guess the question is what you actually want to do. Usually it's not a
good idea to simply apply some techniques from one system to another.
Understanding how DB2 (and SQL in general) works is probably better in the
long run.

Cursors are also available as normal SQL in the DB2 command line. You can
do this (turning auto-commit off or using holdable cursors):

db2 => DECLARE c1 CURSOR FOR SELECT tabname FROM syscat.tables;
db2 => OPEN c1;
db2 => FETCH FROM c1;

TABNAME
---------------------------------------------------------------------
COORD_REF_SYS

1 record(s) selected.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Serge Rielau - 20 Apr 2006 17:18 GMT

Can you define "Db2 does not like"?
If what you get is "cursor not open" it's because you have auto commit
driven by the client. You tool should have a knob to switch it of.
In CLP you simply do:
UPDATE COMMAND OPTIONS USING C OFF
DECLARE cur CURSOR FOR VALUES 1
OPEN cur
FETCH cur
CLOSE cur

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

mghale - 20 Apr 2006 19:06 GMT

Thanks for the replies.

First - turning off auto-commit solved my issue. Thanks for that as I
simply overlooked it.

Second - I'm trying to do something with a cursor that I probably
shouldn't be. Here's the scenario....

I have a list of columns that exist in one to many tables (without RI
enforcing values). I'ved been tasked with generating a list of every
distinct value for each column in the list. The list is really long so
I thought I could use a cursor to evaluate the table name and column
name combination to dynamically build a string that would be my
select...union sql statement for each column name in the list. I was
thinking I could use the cursor to hold a variable for each value and
concatenate a UNION clause for each table that any one column is in.
Then when the column name changed start a new string that would be the
select...union statement for the next column in the list.

If you know a better way, which I'm hoping you do, I would be very
greatful to hear. Since I'm still having trouble decalring variables
within the command editor. Keeps giving me an error sqlstate = 42601.

Thanks and any further help you could provide is greatly appreciated!

Reply to this Message