By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,850 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,850 IT Pros & Developers. It's quick & easy.

Creating and using a Cursor

P: n/a
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...

Apr 20 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
mghale wrote:
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?


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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Apr 20 '06 #2

P: n/a
mghale wrote:
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?

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 20 '06 #3

P: n/a
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!

Apr 20 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.