473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Creating and using a Cursor

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
3 9633
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: gonzal | last post by:
Hi Dose any body know why a temporary table gets deleted after querying it the first time (using SELECT INTO)? When I run the code bellow I'm getting an error message when open the temp table...
2
by: Tom Alcendor | last post by:
I am building a chess game application that allows a user to click on a piece and move it to a new square. When the user clicks on a piece the mouse pointer must change to the image associated...
2
by: Kevin | last post by:
When I declare a cursor,I use a variable to replace the sql statement: DECLARE rs CURSOR LOCAL FAST_FORWARD FOR @sqlPlan But it is not true.Who can correct for me. Another question is : How to...
7
by: kaul | last post by:
i want to create a 2-d array containg r rows and c columns by dynamic memory allocation in a single statement so that i will be able to access the ith and jth index as say arr how is that...
18
by: **Developer** | last post by:
If e.Button = MouseButtons.Left Then also from a Dim Answer As DialogResult = MessageBox.Show.. Select Case Answer Case DialogResult.Yes
12
by: Lucky | last post by:
Hi guys! i want to create one cursor in the t-sql. the problem is i want to use stored procedure instead of select command in cursor. can anyone tell me how can i use stored procedure's o/p to...
1
by: Wilfried Mestdagh | last post by:
Hi, I'm trying to create a custom cursor with following code: Bitmap bmp = new Bitmap(@"c:\1.png"); Graphics g = Graphics.FromImage(bmp); Cursor cursor = new Cursor(bmp.GetHicon());...
10
by: shubha.sunkada | last post by:
Hi, I have a recordset connection in asp that I am using to search records.If I use the client side cursorlocation (rs.cursorlocation=3) then it takes really long to return back the records due...
7
by: Burden | last post by:
Please help i am hitting my head against a wall here. I am creating a database to use with a touchscreen and i have created a pop up keyboard. I have got stuck on two things. Firstly when i am...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.