Solved

DB2 cursors

Posted on 2010-08-20
4
998 Views
Last Modified: 2012-05-10
I am using a cursor to fetch out some data from a table inside a procedure. What is the function to know if cursor has come to an end or not something like %notfound in oracle. Does it work for DB2 as well.
0
Comment
Question by:mohitgyl
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 33486447
you can use the sqlcode variable
check it's value after the fetch - 0 means all good, 100 means no more rows
0
 
LVL 4

Expert Comment

by:gmarino
ID: 33486643
Sorry - but I have to ask WHY you are using cursors.  Cursor represent row processing. Row Processing is bad for performance.  Getting the same work done via a single (or multiple chained) SQL statement(s) is what is called "Set Processing" - which is much better for performance.  

Efforts are needed to train programmers that in general:

   Row Processing (Cursors) - BAD!
   Set Processing (Single/multiple-chained SQL) - GOOD!


Signed,
Greg
The Performance Bigot

0
 

Author Comment

by:mohitgyl
ID: 33500557
@qmarino:If i have ten lacs rows then what do i do?

@momi: Can you please be mor4e specific in terms of syntax.
0
 

Author Comment

by:mohitgyl
ID: 33501185
@momi: I tried using SQLCODE=100 but it providing some error. It is as follows:

C:\Documents and Settings\admin>db2 -tvsf D:\SQL\merg1.sql
CREATE PROCEDURE ME () BEGIN ATOMIC declare exitcode integer default 0; DECLARE
ITM_TIMESTAMP VARCHAR(64); DECLARE ITM_HISTORY_ID VARCHAR(64); DECLARE E1 INTEGE
R; DECLARE E2 INTEGER; DECLARE E3 VARCHAR(64); DECLARE r INTEGER;DECLARE ITM_VAL
UE INTEGER; DECLARE ITM_STATUS_TAG VARCHAR(64); DECLARE a varchar(64); DECLARE C
_ITM CURSOR WITH RETURN FOR select timestamp,history_id,value,status_tag, substr
(history_id,(locate('SC',HISTORY_ID )),((locate('xTS',history_id))-(locate('SC',
HISTORY_ID)))) from ITMUSER.ncwipro where history_id like'%TS%';   OPEN  C_ITM ;
        while exitcode = 0 do FETCH C_ITM  INTO ITM_TIMESTAMP , ITM_HISTORY_ID ,
 ITM_VALUE ,ITM_STATUS_TAG,E3;  select scaid  into a from itmuser.maximotest whe
re scaid like '%' || E3; insert into ITMUSER.ITMTEST1 (timestamp,history_id, val
ue, status_tag,scaid) values(ITM_TIMESTAMP , ITM_HISTORY_ID , ITM_VALUE ,ITM_STA
TUS_TAG,a);  IF SQLCODE='100' THEN SET EXITCODE = 1; END IF; END while; CLOSE C_
ITM;end
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0206N  "SQLCODE" is not valid in the context where it is used.  LINE
NUMBER=1.  SQLSTATE=42703
C:\Documents and Settings\admin>db2 -tvsf D:\SQL\merg1.sql
CREATE PROCEDURE ME () BEGIN ATOMIC declare exitcode integer default 0; DECLARE
ITM_TIMESTAMP VARCHAR(64); DECLARE ITM_HISTORY_ID VARCHAR(64); DECLARE E1 INTEGE
R; DECLARE E2 INTEGER; DECLARE E3 VARCHAR(64); DECLARE r INTEGER;DECLARE ITM_VAL
UE INTEGER; DECLARE ITM_STATUS_TAG VARCHAR(64); DECLARE a varchar(64); DECLARE C
_ITM CURSOR WITH RETURN FOR select timestamp,history_id,value,status_tag, substr
(history_id,(locate('SC',HISTORY_ID )),((locate('xTS',history_id))-(locate('SC',
HISTORY_ID)))) from ITMUSER.ncwipro where history_id like'%TS%';   OPEN  C_ITM ;
        while exitcode = 0 do FETCH C_ITM  INTO ITM_TIMESTAMP , ITM_HISTORY_ID ,
 ITM_VALUE ,ITM_STATUS_TAG,E3;  select scaid  into a from itmuser.maximotest whe
re scaid like '%' || E3; insert into ITMUSER.ITMTEST1 (timestamp,history_id, val
ue, status_tag,scaid) values(ITM_TIMESTAMP , ITM_HISTORY_ID , ITM_VALUE ,ITM_STA
TUS_TAG,a);  IF SQLCODE='100' THEN SET EXITCODE = 1; END IF; END while; CLOSE C_
ITM;end
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0206N  "SQLCODE" is not valid in the context where it is used.  LINE
NUMBER=1.  SQLSTATE=42703

Open in new window

0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question