IBM Mainframe Forum

by **sinmani** » Fri Oct 04, 2013 5:28 pm

Hi Forum,

I Am using a cursor for a DB2 table.

Now I have to read the values until END of CURSOR and load them in a Table until END of cursor.
However if NO ROWS are found then the program is to be abended.

How can I do this??

Are NO ROW FOUND and END OF CURSOR different types in Cursor processing??

by **dick scherrer** » Fri Oct 04, 2013 7:45 pm

Hello,

One way to do what you want is to add to a counter each time a row is fetched.

When the sql code says there is no record found, check the count. If it is still zero, there were no rows.

by **Akatsukami** » Fri Oct 04, 2013 7:47 pm

Think about it: if the requirement were to read a file and store the records in a table, but to "abend" (whatever that means to you) the program if the file were empty, how would you do it?

by **sinmani** » Sat Oct 05, 2013 9:37 am

Counter is a simple option Which would work.
But I wanted to know if we have any such specific SQL code for NO ROWS FOUND.
What I knew that at the end of last fetch we would get a SQL code = 100. Similarly for No rows found too I will get SQL code = 100.
So I coded it that way. Then a very senior person told me that these two conditions can be made to work differently.

by **dick scherrer** » Mon Oct 07, 2013 6:40 am

Hello,

It this "senior" knows these things, s/he should be able to clarify.

make sure they explain why testing for 100 would NOT work for your case.

by **Akatsukami** » Mon Oct 07, 2013 10:36 pm

sinmani wrote:Counter is a simple option Which would work.
But I wanted to know if we have any such specific SQL code for NO ROWS FOUND.

No.

I wrote a simple test program:

Select all

foo46: proc (parms) options (main) reorder;

dcl desc char (255),
parms char (100) var,
freq fixed bin (31);

EXEC SQL
INCLUDE SQLCA;

EXEC SQL
DECLARE A CURSOR FOR
SELECT DESC FROM EAMAY
WHERE FREQUENCY = :FREQ;

freq = parms;

EXEC SQL
OPEN A;

put skip edit ('SQLCODE on open is ', sqlcode) (a, f(6));

do while (sqlcode=0);
EXEC SQL
FETCH A INTO :DESC;

put skip edit ('SQLCODE on fetch is ', sqlcode) (a, f(6));

end;

end foo46;

and executed it with this JCL:

Select all

//hlqJ12 JOB ,'This is a test',CLASS=S,MSGCLASS=1,REGION=0M,
// SCHENV=DB2@HD0D
//*MAIN FAILURE=RESTART,LINES=(999)
//STEP1 EXEC PGM=IKJEFT1B
//STEPLIB DD DSN=hlq.WORK.LOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(HD0D)
RUN PROGRAM(POO46) PLAN(POO46) PARMS('/8')
/*

(8 being a value that I happen to know is not held by frequency on any row in that table). I got the following output:

Select all

SQLCODE on open is 0
SQLCODE on fetch is 100

So I believe it is time that your very senior person, as we say, put up or shut up.

IBM Mainframe Forum

END OF CURSOR AND NO ROWS FOUND IN CURSOR.

END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.

Re: END OF CURSOR AND NO ROWS FOUND IN CURSOR.