Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / January 2006

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
taru - 25 Jan 2006 12:03 GMT
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
Shyam Peri - 25 Jan 2006 12:38 GMT
One could possible intiate the "event monitor over statements" and then
log the statements information
bughunter@ru - 25 Jan 2006 13:13 GMT
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
Serge Rielau - 25 Jan 2006 13:25 GMT
> Env: DB2 UDB 8.1 on AIX
>
[quoted text clipped - 8 lines]
> 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/developerworks/db2/library/techarticle/dm-0406rielau/

Cheers
Serge

Signature

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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.