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
