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                          =

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,

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.mon.doc/doc/r0001347.html

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.