Solved

DB2 cursors

Posted on 2010-08-20
4
987 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now