[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1030
  • Last Modified:

DB2 cursors

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
mohitgyl
Asked:
mohitgyl
  • 2
1 Solution
 
momi_sabagCommented:
you can use the sqlcode variable
check it's value after the fetch - 0 means all good, 100 means no more rows
0
 
gmarinoCommented:
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
 
mohitgylAuthor Commented:
@qmarino:If i have ten lacs rows then what do i do?

@momi: Can you please be mor4e specific in terms of syntax.
0
 
mohitgylAuthor Commented:
@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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now