Hello,

Have you tried to use EVENT MONITOR to track the pkg cache? If you store the output to e.g. unformatted event (UE) table. You can then filter the queries by the APPL_ID. The structure of the table is as follows:

[db2inst1@db2dev ~]$ db2 describe table TRACKSTMTS

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PARTITION_KEY                   SYSIBM    INTEGER                      4     0 No    
APPL_ID                         SYSIBM    VARCHAR                     64     0 Yes   
APPL_NAME                       SYSIBM    VARCHAR                    255     0 Yes   
EVENT_CORRELATION_ID            SYSIBM    VARCHAR                     32     0 Yes   
EVENT_DATA                      SYSIBM    BLOB                 142627332     0 No    
EVENT_ID                        SYSIBM    BIGINT                       8     0 No    
EVENT_TIMESTAMP                 SYSIBM    TIMESTAMP                   10     6 No    
EVENT_TYPE                      SYSIBM    VARCHAR                    128     0 No    
MEMBER                          SYSIBM    SMALLINT                     2     0 No    
RECORD_SEQ_NUM                  SYSIBM    BIGINT                       8     0 No    
RECORD_TYPE                     SYSIBM    BIGINT                       8     0 No    
SERVICE_SUBCLASS_NAME           SYSIBM    VARCHAR                    128     0 Yes   
SERVICE_SUPERCLASS_NAME         SYSIBM    VARCHAR                    128     0 Yes   
WORKLOAD_NAME                   SYSIBM    VARCHAR                    128     0 Yes   
MON_INTERVAL_ID                 SYSIBM    BIGINT                       8     0 Yes   

  15 record(s) selected.

 It is then a little bit tricky, to export the SQL statements, but see this link for more info about the process.
https://www.ibm.com/docs/en/db2/11.5?topic=cpcedgr-example-using-package-cache-information-db2advis-look-performance-improvement-opportunities

Regards
Ondrej

Thank you for your response.

In the idmc (IBM Data Management Console) repository database,
I can retrieve information about a specific appl_id that was executed in the past as shown below.

db2 "select appl_name,thresh_violations,pool_temp_data_l_reads,sql_text_summary,sqlcode,collected + 9 hours,sql_hash_id
from ibmconsole.event_activity
where appl_id = '10.3.2.35.53386.230817083042'"

However, I'm unable to retrieve the complete SQL text.
I'm inquiring about this because the idmc web console displays the complete SQL text.
It seems that there might be information stored in a certain table, and I would like to know the table and columns involved."

[db2inst1@db2dev ~]$ db2 describe table TRACKSTMTS                                Data type                     ColumnColumn name                     schema    Data type name      Length     Scale Nulls------------------------------- --------- ------------------- ---------- ----- ------PARTITION_KEY                   SYSIBM    INTEGER                      4     0 No    APPL_ID                         SYSIBM    VARCHAR                     64     0 Yes   APPL_NAME                       SYSIBM    VARCHAR                    255     0 Yes   EVENT_CORRELATION_ID            SYSIBM    VARCHAR                     32     0 Yes   EVENT_DATA                      SYSIBM    BLOB                 142627332     0 No    EVENT_ID                        SYSIBM    BIGINT                       8     0 No    EVENT_TIMESTAMP                 SYSIBM    TIMESTAMP                   10     6 No    EVENT_TYPE                      SYSIBM    VARCHAR                    128     0 No    MEMBER                          SYSIBM    SMALLINT                     2     0 No    RECORD_SEQ_NUM                  SYSIBM    BIGINT                       8     0 No    RECORD_TYPE                     SYSIBM    BIGINT                       8     0 No    SERVICE_SUBCLASS_NAME           SYSIBM    VARCHAR                    128     0 Yes   SERVICE_SUPERCLASS_NAME         SYSIBM    VARCHAR                    128     0 Yes   WORKLOAD_NAME                   SYSIBM    VARCHAR                    128     0 Yes   MON_INTERVAL_ID                 SYSIBM    BIGINT                       8     0 Yes     15 record(s) selected.

To retrieve the complete SQL text for detailed analysis, you'd typically look into the associated EXPLAIN tables or other monitoring tables that store detailed SQL information.

Here are a few steps to find the complete SQL text:

1. Extended Monitoring Tables:

   • If extended monitoring is enabled, you can check the MON_GET_PKG_CACHE_STMT table function or the MON_PKG_CACHE_STMT view for detailed SQL statement text

   • 
     SELECT STMT_TEXT 
     FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) 
     WHERE APPL_ID = '10.3.2.35.53386.230817083042';
     
     
     

   • The ibmconsole.event_activity table does have the sql_text_summary column, but as the name suggests, it's usually a summary.

     The full SQL text related to an appl_id or sql_hash_id is likely stored in a different table, possibly in a BLOB or CLOB datatype, due to the potentially large size of some SQL statements.
     
     

     Use the system catalog tables to search for potential tables/columns:

     
     SELECT TABNAME, COLNAME, TYPENAME
     FROM SYSCAT.COLUMNS
     WHERE TABSCHEMA = 'IBMCONSOLE' AND TYPENAME IN ('BLOB', 'CLOB');
     
     

I found the complete SQL from the table below. Thank you for your response.

db2 "select SQL_TEXT from IBMCONSOLE.SQL_DIM

where SQL_HASH_ID = 5415886468674426743"