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,update statement within the stored procedure. We were not
able to find a way to do the same?

tariq

Jan 25 '06 #1

Shyam Peri

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

Jan 25 '06 #2

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(procschema)||'.'|| procname)
from SYSCAT.ROUTINEDEP rd inner join SYSIBM.SYSPROCEDURES sp on
(rd.ROUTINESCHEMA, 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_Name)
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_ACCESS(?)
-------------------------------------------
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

Jan 25 '06 #3

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,update 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

Jan 25 '06 #4