Technical Blog Post
Abstract
In DB2LUW How to find the statement/Query/SQL running against a Static SQL section number
Body
This question comes many times. So, trying to clarify a bit.
We have a Db2 application snapshot and it’s not showing the statement the application is running. Instead it’s showing package details.
Example,
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Application Snapshot
Application handle = 20160
Application status = UOW Waiting
Status change time = 07/20/2017 10:48:30.415436
Application code page = 819
Application country/region code = 1
Application name = my_application
Application ID = 21.21.33.23.49955.180624151926
Sequence number = 00003
Client login ID = mylogin
Configuration NNAME of client = abcdefg
Client database manager product ID = SQL09079
Process ID of client application = 10944662
Platform of client application = AIX 64BIT
Communication protocol of client = TCP/IP
Statement type = Static SQL Statement
Statement = Execute
Section number = 9
Application creator = AAA250
Package name = MYPACKAGE
Consistency Token =
Package Version ID = MM-R150E63-020
How can we find the SQL statement from that ?
creator, package_version_id, package_name and section number monitor elements could be used to query the SYSCAT.STATEMENTS system catalog table and obtain the static SQL statement text, using the sample query as follows:
SELECT SEQNO, SUBSTR(TEXT,1,120)
FROM SYSCAT.STATEMENTS
WHERE PKGNAME = 'package_name’ AND
PKGSCHEMA = 'creator' AND
VERSION = 'package_version_id' AND
SECTNO = section_number
ORDER BY SEQNO
So, in this specific example it will be,
SELECT SEQNO, SUBSTR(TEXT,1,120)
FROM SYSCAT.STATEMENTS
WHERE PKGNAME = ' MYPACKAGE ' AND
PKGSCHEMA = ' AAA250’ AND
VERSION = ' MM-R150E63-020' AND
SECTNO = 9
ORDER BY SEQNO
It’s documented in the Knowledge Center,
Also, this caution is mentioned there,
Exercise caution in obtaining static statement text, because this query against the system catalog table could cause lock contention. Whenever possible, only use this query when there is little other activity against the database.
UID
ibm11140358