Solved

DB2 cursors

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

11 Experts available now in Live!

Get 1:1 Help Now