How to find Execution time of Select, Insert and Update in stored procedures

[taru]

Env: DB2 UDB 8.1 on AIX

We are executing a db2 sql stored procedure which is invoked from a
java client. The stored procedure contains few select, insert and
update statements.

Using the DB2 snapshot and event monitor we are able to find the time
taken for the stored procedure execution.

We want to find what is the execution time for each
select,insert,u pdate statement within the stored procedure. We were not
able to find a way to do the same?

tariq

[Shyam Peri]

One could possible intiate the "event monitor over statements" and then
log the statements information

[bughunter]

event monitor for statements (add where clause to minimize output).
Typically in LUW every operator in SP converted to static sql and
stored in package. Package name like SCHEMA.Pxxxxxxx , where xxxxxxx -
number.

You can get procedure name by sql (create a function)

SELECT rtrim(rtrim(pro cschema)||'.'|| procname)
from SYSCAT.ROUTINED EP rd inner join SYSIBM.SYSPROCE DURES sp on
(rd.ROUTINESCHE MA, rd.ROUTINENAME ) =
(sp.PROCSCHEMA, sp.SPECIFICNAME )
where rd.BTYPE='K'
and rtrim(rtrim(rd. BSCHEMA) || '.' || rd.BNAME) = UCASE(
Package_Name)

By package section you can found real sql

SELECT TEXT FROM SYSIBM.SYSSTMT
where RTRIM(PLCREATOR ) ||'.'|| RTRIM(PLNAME) = UCASE(Package_N ame)
and SectNo = sect )

I can't say about AIX, but event monitor output in LUW like this
sp call:

*) Statement Event ...
Appl Handle: 1
Appl Id: APPL_ID
Appl Seq number: 0105

Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Prepare
Section : 4
Creator : NULLID
Package : SYSSH200
Consistency Token : SYSLVL01
Package Version ID :
Cursor : SQL_CURSH200C4
Cursor was blocking: FALSE
Text : CALL SO.SO_PAGE_ACCE SS(?)
-------------------------------------------
Start Time: 13.12.2005 13:39:41.878441
Stop Time: 13.12.2005 13:39:41.878633
Exec Time: 0.000192 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000

Then You see body for SP: this is first cursor in SP.

*) Statement Event ...
Appl Handle: 1
Appl Id: APPL_ID
Appl Seq number: 0105

Record is the result of a flush: FALSE
-------------------------------------------
Type : Static
Operation: Open
Section : 1
Creator : SO
Package : P3594943
Consistency Token : tAx8NeGV
Package Version ID :
Cursor : SO_PAGE_ACCESS_ C
Cursor was blocking: TRUE
-------------------------------------------
Start Time: 13.12.2005 13:39:41.879779
Stop Time: 13.12.2005 13:39:41.879814
Exec Time: 0.000035 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000
Andy

[Serge Rielau]

taru wrote:

Env: DB2 UDB 8.1 on AIX

We are executing a db2 sql stored procedure which is invoked from a
java client. The stored procedure contains few select, insert and
update statements.

Using the DB2 snapshot and event monitor we are able to find the time
taken for the stored procedure execution.

We want to find what is the execution time for each
select,insert,u pdate statement within the stored procedure. We were not
able to find a way to do the same?

"Profiling SQL Procedures"
http://www-128.ibm.com/developerwork...dm-0406rielau/

Cheers
Serge

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