Db2pd -dynamic might report multiple "Dynamic SQL Variations"
existing in the package cache, each with different environment,
after the application, which had compiled given statement, has
disconnected from the database, e.g.:
Dynamic Cache:
Number of References 3
Number of Statement Inserts 1
Number of Statement Deletes 0
Number of Variation Inserts 3
Number of Statements 1
Dynamic SQL Statements:
AnchID StmtUID NumEnv NumVar NumRef NumExe Text
477 1 3 3 3 3 insert into
session.my_cgtt values(1)
Dynamic SQL Environments:
AnchID StmtUID EnvID Iso QOpt Blk
477 1 1 CS 5 B
477 1 2 CS 5 B
477 1 3 CS 5 B
Dynamic SQL Variations:
AnchID StmtUID EnvID . NumRef . Val Insert Time
Sect Size Num Copies
477 1 1 . 1 . Y
2017-05-12-14.36.42.429113 5488 0
477 1 2 . 1 . Y
2017-05-12-14.36.43.732168 5488 1
477 1 3 . 1 . Y
2017-05-12-14.37.45.927716 5488 1
Those variation will be deleted from package cache at some point
in the future, once new statements are inserted, but with
applications extensively using CGTTs, this might negatively
affect the package cache hit ratio.
SQL variations referencing CGTTs cannot be reused by any
subsequent connection, which makes automatic removal more
desirable, as it it the case with declared global temporary
table (DGTTs). |
