?
Solved

DB2 cursors

Posted on 2010-08-20
4
Medium Priority
?
1,013 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month8 days, 13 hours left to enroll

764 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