Number of cursor rows returned??

jawalker · #1 (**permalink**) 04-20-10, 10:24

Is there a DB2 variable that can be accessed via COBOL program to find the number of rows returned in a cursor?
Besides physically counting while fetching,

MarkhamDBA · #2 (**permalink**) 04-20-10, 12:41

i don't know any other ways of doing it, but running a statement "select count(*) ..." with same 'where' clause before your cursor statement.

n_i · #3 (**permalink**) 04-20-10, 13:40

Quote:

Originally Posted by jawalker

Is there a DB2 variable that can be accessed via COBOL program to find the number of rows returned in a cursor?
Besides physically counting while fetching,

Obviously, the number of rows returned in a cursor is not known until all of them are processed, so you might just as well count them.

schintala · #4 (**permalink**) 04-20-10, 15:04

I remember using one of the SQLCA variable to get no of rows effected by SQL execution. Just look at this variable SQLERR(3) from this cobol structure.

01 SQLCA SYNC.
05 SQLCAID PIC X(8) VALUE "SQLCA ".
05 SQLCABC PIC S9(9) COMP-5 VALUE 136.
05 SQLCODE PIC S9(9) COMP-5.
05 SQLERRM.
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP-5.
05 SQLWARN.
10 SQLWARN0 PIC X.
10 SQLWARN1 PIC X.
10 SQLWARN2 PIC X.
10 SQLWARN3 PIC X.
10 SQLWARN4 PIC X.
10 SQLWARN5 PIC X.
10 SQLWARN6 PIC X.
10 SQLWARN7 PIC X.
10 SQLWARN8 PIC X.
10 SQLWARN9 PIC X.
10 SQLWARNA PIC X.
05 SQLSTATE PIC X(5).

Marcus_A · #5 (**permalink**) 04-20-10, 15:18

"rows effected" only includes inserts, updates, or deletes.

stolze · #6 (**permalink**) 04-21-10, 05:35

As Nick said, there is often no way for DB2 to know the number of rows ahead of time. The only option would be to materialize the result and then count it. It does not matter if DB2 does the counting or the application - the query has to be executed twice (which is really bad performance-wise) or you have to count while fetching the rows. So if you do not care about performance, do the SELECT COUNT(*) approach.

dr_te_z · #7 (**permalink**) 04-21-10, 06:26

Quote:

Originally Posted by schintala

I remember using one of the SQLCA variable to get no of rows effected by SQL execution. Just look at this variable SQLERR(3) from this cobol structure.

Thats right. I've coded in the past:

Code:

perform cursor-open.
perform SQLERR(3) times
   perform cursor-fetch
   perform process-cursor-data
end-perform.
perform cursor-close.

Not the most robust example, but it works

Concerning the rows-affected: be aware that cascade-deletes are not included.

Internet Services

Web Development

Digital Marketing

Consumer Tech