Db2

 View Only

 MON_GET_PKG_CACHE_STMT returns empty STMT_TEXT despite monitoring enabled (DB2 12.1.3 FP0)

Giuseppe Calignano's profile image
Giuseppe Calignano posted Sat December 06, 2025 03:46 PM

Hi!

I have a DB2 12.1.3.0 database (wpdel) where MON_GET_PKG_CACHE_STMT returns rows but all STMT_TEXT columns are NULL/empty. The Data Management Console shows only "1 command without description" in the Package Cache tab instead of displaying cached statements. However, db2pd -dyn clearly shows the statements WITH text in the package cache.

Interestingly, another database (repodb) on the same instance with identical configuration works correctly - MON_GET_PKG_CACHE_STMT returns statement text as expected.

Environment

  • DB2 version: 12.1.3.0 Fix Pack 0 on Linux x86_64
  • Same instance hosts two databases: wpdel (broken) and repodb (working)
  • Both databases have identical monitoring configurations

Evidence

On wpdel (broken database):

db2pd -db wpdel -dyn | grep "Number of Statements"
Number of Statements          23

db2 "SELECT COUNT(*) as total_stmts, COUNT(STMT_TEXT) as stmts_with_text 
     FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2))"
TOTAL_STMTS STMTS_WITH_TEXT
----------- ---------------
         23               0

On repodb (working database):

db2 "SELECT COUNT(*) as total_stmts, COUNT(STMT_TEXT) as stmts_with_text 
     FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2))"
TOTAL_STMTS STMTS_WITH_TEXT
----------- ---------------
          1               1

The package cache clearly contains the statements (db2pd shows them), but the monitoring table function cannot retrieve the text.

Configuration Verified (identical on both databases):

Database level:

MON_ACT_METRICS = BASE
MON_REQ_METRICS = BASE
MON_OBJ_METRICS = EXTENDED
SECTION_ACTUALS = NONE

DBM level:

DFT_MON_STMT = ON

The disconnect between db2pd successfully showing statement text but MON_GET_PKG_CACHE_STMT returning NULL suggests the monitoring infrastructure is somehow corrupted or misconfigured at a layer we can't see.

Any insights would be greatly appreciated!

Thanks!

Giuseppe

Cintia Ogura's profile image
Cintia Ogura posted Mon December 08, 2025 11:37 AM

Hello Giuseppe,

Unfortunately without the DMC log files, it is hard to see what is going on with your environment.

However, based on your comments:

1.) The database parameters are correct

2.) The repository database under the same instance is working

I got the impression that your database level might be different from the instance level.

Can you please run the following SQL statement for the database having the issue:

db2 "select versionnumber from sysibm.sysversions order by 1 desc"

If the versionnumber does not match your db2level, you will need to run the db2updv121 command, so Db2 can update the internal APIs to the current Db2 version.

https://www.ibm.com/docs/en/db2/12.1.x?topic=sc-db2updv121-update-database-version-121-mod-pack-fix-pack-command

If that does not help, you will need to create a case with us, so I can take a look at the DMC log files to help the investigation.

Thanks, Cintia.

Related Content