Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

The row numbers cannot be retrieved after OPEN curosr.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Fri Nov 11, 2011 3:39 pm    Post subject: The row numbers cannot be retrieved after OPEN curosr.
Reply with quote

I want to retrieve the row number of result table after OPEN CURSOR operation, so I did below test:

in cobol program, I have below statement:
Code:
*---------------------------------------------------------   
*DECLARE CURSOR FOR TABLE UPDATE                             
*---------------------------------------------------------   
     EXEC SQL                                               
         DECLARE APKEMPCCURSOR ASENSITIVE SCROLL CURSOR     
         WITH HOLD FOR                                       
         SELECT *                                           
         FROM   APKEMPC                                     
         FOR UPDATE OF ACCTNAME ,                           
                       TEL ,                                 
                       NEWID  ,                             
                       FILLERA  ,                           
                       FILLER1 ,                             
                       FILLER2 ,                             
                       FILLER3 ,                             
                       FILLER4 ,                             
                       FILLER5 ,                             
                       FILLER6 ,                             
                       FILLER7 ,                             
                       FILLER8 ,                             
                       FILLER9                               
                                                             
     END-EXEC.                                               
                                                 Scroll ===>


and then, I open the cursor:
Code:

EXEC SQL                                       
     OPEN APKEMPCCURSOR                       
END-EXEC                                       
                                               
DISPLAY 'SQLCA CONTENT AFTER OPEN CURSOR: '   
    PERFORM VARYING I     FROM +1 BY +1       
            UNTIL I           > 6             
    DISPLAY 'SQLERRD(' I '): ' SQLERRD(I)     
    END-PERFORM                               
                                               
EXEC SQL GET DIAGNOSTICS                       
    :ROW-COUNT = DB2_NUMBER_ROWS               
END-EXEC                                       
DISPLAY 'GET DIAGNOSTICS AFTER OPEN CURSOR. ' 
DISPLAY 'DB2_NUMBER_ROWS:   ' ROW-COUNT       


but I cannot find the row number for that cursor when I look into the log:
Code:
SQLCA CONTENT AFTER OPEN CURSOR:                     
SQLERRD(000000001): 0000000000                       
SQLERRD(000000002): 0000000000                       
SQLERRD(000000003): 0000000000                       
SQLERRD(000000004): 000000000J                       
SQLERRD(000000005): 0000000000                       
SQLERRD(000000006): 0000000000                       
GET DIAGNOSTICS AFTER OPEN CURSOR.                   
DB2_NUMBER_ROWS:   0000000000000000000000000000000   



but from IBM SQL reference, it is stated as below:
Quote:
The rows of the result table can be
derived during the execution of the OPEN statement and a temporary copy of
a result table can be created to hold those rows. They can be derived during
the execution of later FETCH statements.


Can anybody please tell me why I could not retrieve the row number? and How to do that?

thanks for your reply first.
Back to top
View user's profile Send private message

Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Fri Nov 11, 2011 8:39 pm    Post subject:
Reply with quote

I don't think you can get the number of rows qualified for Cursor Select after OPEN. Please correct me if I'm wrong.

The below is from the manual.

Quote:
ROW_COUNT
Identifies the number of rows associated with the previous SQL statement. If the previous SQL statement is a DELETE, INSERT, or UPDATE statement, ROW_COUNT identifies the number of rows that qualified for the operation. If the previous statement is a PREPARE statement, ROW_COUNT identifies the estimated number of result rows in the prepared statement.


I see that GET DIAGNOSTICS has been used with multiple-row INSERT or FETCH.

I'll refer the manual.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Nov 11, 2011 11:05 pm    Post subject:
Reply with quote

dejunzhu,

There is no concept of row number in DB2. In the result table, the row order is based on the ORDER BY clause / Clustering.

But there is something called ROWID. YOu may want to explore that.

Thanks,
Sushanth
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sun Nov 13, 2011 4:41 pm    Post subject:
Reply with quote

sushanth bobby wrote:
dejunzhu,

There is no concept of row number in DB2. In the result table, the row order is based on the ORDER BY clause / Clustering.

But there is something called ROWID. YOu may want to explore that.

Thanks,
Sushanth


Sorry, but I think I did not state myself clearly enough.
When I said 'row number' , I mean the number of rows in a table qualified for the cursor SELECT statement.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sun Nov 13, 2011 4:42 pm    Post subject:
Reply with quote

Gnana Sekaran Nallathambi wrote:
I don't think you can get the number of rows qualified for Cursor Select after OPEN. Please correct me if I'm wrong.

The below is from the manual.

Quote:
ROW_COUNT
Identifies the number of rows associated with the previous SQL statement. If the previous SQL statement is a DELETE, INSERT, or UPDATE statement, ROW_COUNT identifies the number of rows that qualified for the operation. If the previous statement is a PREPARE statement, ROW_COUNT identifies the estimated number of result rows in the prepared statement.


I see that GET DIAGNOSTICS has been used with multiple-row INSERT or FETCH.

I'll refer the manual.

I'm trying to retrieve DB2_NUMBER_ROWS field via GET DIAGNOSTIC SQL satement, not ROW-COUNT.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sun Nov 13, 2011 4:44 pm    Post subject:
Reply with quote

Actually, I am trying to retrieve the number of rows of a result table through below statement.
Code:
EXEC SQL GET DIAGNOSTICS                       
    :ROW-COUNT = DB2_NUMBER_ROWS               
END-EXEC
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Nov 14, 2011 2:26 pm    Post subject:
Reply with quote

Quote:
DB2_NUMBER_ROWS
If the previous SQL statement was an OPEN or a FETCH which caused the size of the result table to be known, returns the number of rows in the result table. For SENSITIVE cursors, this value can be thought of as an approximation since rows inserted and deleted will affect the next retrieval of this value. If the previous statement was a PREPARE statement, returns the estimated number of rows in the result table for the prepared statement. Otherwise, the value zero is returned

not all cursors are resolved in a way that the number of rows is known.

especially a SELECT * FROM APKEMPC will be resolved via a tablescan and db2 will only retrieve one page (16/32/64 pages when prefetching) on the FETCH statement and not the complete table on the OPEN.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Tue Nov 15, 2011 6:25 am    Post subject:
Reply with quote

hi, GuyC,
Thanks for your reply.
but I tried to open a cursor which performs index scan as follow:
Code:
DECLARE APKEMPCCURSOR ASENSITIVE SCROLL CURSOR
WITH HOLD FOR                                 
SELECT ACCTID                                 
FROM   APKEMPC                                 

the result is the same as the previous one:
Code:
SQLCA CONTENT AFTER OPEN CURSOR:                   
SQLERRD(000000001): 0000000000                     
SQLERRD(000000002): 0000000000                     
SQLERRD(000000003): 0000000000                     
SQLERRD(000000004): 000000000J                     
SQLERRD(000000005): 0000000000                     
SQLERRD(000000006): 0000000000                     
GET DIAGNOSTICS AFTER OPEN CURSOR.                 
DB2_NUMBER_ROWS:   0000000000000000000000000000000 


Can you help explain?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 15, 2011 9:15 am    Post subject:
Reply with quote

Hello,

Quote:
not all cursors are resolved in a way that the number of rows is known.
Which part of GuyC's reply was not clear. . .

Quote:
Actually, I am trying to retrieve the number of rows of a result table through below statement.
Then why not simply issue a SELECT COUNT(*) WHERE whatever you want. . . This will always the count of rows that meet the SELECT as far as i know.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Nov 15, 2011 2:08 pm    Post subject:
Reply with quote

An accespath which doesn't involve creation of an intermediate result table will have 0 in SQLERRD(3).
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Tue Nov 15, 2011 6:49 pm    Post subject:
Reply with quote

GuyC wrote:
An accespath which doesn't involve creation of an intermediate result table will have 0 in SQLERRD(3).
There seems to be a persistent myth out there that DB2 *always* creates a result table. I think this may stem from programmers being told that once a cursor is Opened, the data is sitting out there waiting to be fetched. "It's kind of like reading a file" my first DB2 instructor told me (in 1986!).
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 15, 2011 8:59 pm    Post subject: Reply to: The row numbers cannot be retrieved after OPEN cur
Reply with quote

Hello,

Quote:
There seems to be a persistent myth out there that DB2 *always* creates a result table.

From some of the really awful queries i've seen, this may be not be a myth for some "developers". . . icon_wink.gif

d
Back to top
View user's profile Send private message
pramod prasad

New User


Joined: 20 May 2007
Posts: 6
Location: hyderabad

PostPosted: Thu Nov 17, 2011 11:48 pm    Post subject: Reply to: The row numbers cannot be retrieved after OPEN cur
Reply with quote

Intermediate/Temporary tables are not always build. It depends if resultant data needs to be sorted.
Can you try with a CURSOR having ORDER BY.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts FTOPEN TEMP is failing due to dataset... mbenaud TSO/ISPF 7 Thu Jul 28, 2016 4:28 pm
No new posts comparing comp-3 and unpacked numbers juares castro COBOL Programming 3 Mon May 30, 2016 6:46 pm
This topic is locked: you cannot edit posts or make replies. Secure Browse to open dataset sandip_mainframe TSO/ISPF 3 Tue Apr 26, 2016 11:18 am
This topic is locked: you cannot edit posts or make replies. BAE Systems - Recruitment Open Days -... Express Mainframe Jobs 0 Fri Apr 22, 2016 8:19 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us