Rows read from statement event monitor

[nekiv90]

Greetings,

How can 'rows read' from a DB2 CALL statement incur so many reads? I
would expect it to be zero.

Could someone enlighten me on this? Thanks!

Here is output from executing event monitor on STATEMENTS:

68) Statement Event ...
Appl Handle: 1114
Appl Id: *LOCAL.TEST.060125010754
Appl Seq number: 0008

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 1
Creator : NULLID
Package : SQLC2E06
Consistency Token : AAAAAcEU
Package Version ID :
Cursor : SQLCUR1
Cursor was blocking: FALSE
Text : call sel_by_div(11, '01', 1, ?) <------ Just a call
to stored procedure!
-------------------------------------------
Start Time: 25-01-2006 12:28:30.021650
Stop Time: 25-01-2006 12:31:56.127693
Exec Time: 206.106043 seconds
Number of Agents created: 1
User CPU: 195.250000 seconds <------ High CPU usage
System CPU: 0.031250 seconds
Fetch Count: 0
Sorts: 36
Total sort time: 0
Sort overflows: 0
Rows read: 33589918 <------ High number of rows read
Rows written: 1465
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 33578377
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 74
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 65379
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 100
sqlstate: 02000

[Serge Rielau]

ne*****@hotmail.com wrote:

Greetings,

How can 'rows read' from a DB2 CALL statement incur so many reads? I
would expect it to be zero.

Could someone enlighten me on this? Thanks!

Here is output from executing event monitor on STATEMENTS:

68) Statement Event ...
Appl Handle: 1114
Appl Id: *LOCAL.TEST.060125010754
Appl Seq number: 0008

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 1
Creator : NULLID
Package : SQLC2E06
Consistency Token : AAAAAcEU
Package Version ID :
Cursor : SQLCUR1
Cursor was blocking: FALSE
Text : call sel_by_div(11, '01', 1, ?) <------ Just a call
to stored procedure!
-------------------------------------------
Start Time: 25-01-2006 12:28:30.021650
Stop Time: 25-01-2006 12:31:56.127693
Exec Time: 206.106043 seconds
Number of Agents created: 1
User CPU: 195.250000 seconds <------ High CPU usage
System CPU: 0.031250 seconds
Fetch Count: 0
Sorts: 36
Total sort time: 0
Sort overflows: 0
Rows read: 33589918 <------ High number of rows read
Rows written: 1465
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 33578377
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 74
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 65379
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 100
sqlstate: 02000

I think (!) it may be the total of whatever happened inside of the
procedure. Coudl be easily tested...
Of course a CALL can also result in a read from the catalog, but that
should be minimal.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[nekiv90]

Thanks, Serge.

The SP consists of 4 'declare cursor'. I broke it up and ran one at a
time with an attempt to identify the slow runing one. They all ran fast
and eventually merge them to the final version I used to have. Again,
It ran fast and the 'rows read' is zero.

No change to db/dbm settings since last testing. Don't know exectly
what happended?!
Serge Rielau wrote:

ne*****@hotmail.com wrote:

Greetings,

How can 'rows read' from a DB2 CALL statement incur so many reads? I
would expect it to be zero.

Could someone enlighten me on this? Thanks!

Here is output from executing event monitor on STATEMENTS:

68) Statement Event ...
Appl Handle: 1114
Appl Id: *LOCAL.TEST.060125010754
Appl Seq number: 0008

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 1
Creator : NULLID
Package : SQLC2E06
Consistency Token : AAAAAcEU
Package Version ID :
Cursor : SQLCUR1
Cursor was blocking: FALSE
Text : call sel_by_div(11, '01', 1, ?) <------ Just a call
to stored procedure!
-------------------------------------------
Start Time: 25-01-2006 12:28:30.021650
Stop Time: 25-01-2006 12:31:56.127693
Exec Time: 206.106043 seconds
Number of Agents created: 1
User CPU: 195.250000 seconds <------ High CPU usage
System CPU: 0.031250 seconds
Fetch Count: 0
Sorts: 36
Total sort time: 0
Sort overflows: 0
Rows read: 33589918 <------ High number of rows read
Rows written: 1465
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 33578377
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 74
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 65379
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 100
sqlstate: 02000

I think (!) it may be the total of whatever happened inside of the
procedure. Coudl be easily tested...
Of course a CALL can also result in a read from the catalog, but that
should be minimal.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab