|
View previous topic :: View next topic
|
| Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
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 |
|
 |
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| 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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| 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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| An accespath which doesn't involve creation of an intermediate result table will have 0 in SQLERRD(3). |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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". . . 
d |
|
| Back to top |
|
|
pramod prasad
New User
Joined: 20 May 2007
Posts: 6
Location: hyderabad
|
|
|
|
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 |
|
|
|
|
