Collection Services data files: QAPMSQLPC
This database file contains the performance data about the SQL Plan Cache collected by the *SQL collection category.
| Field Name | Description | Attribute |
|---|---|---|
| INTNUM | Interval number: The nth sample database interval based on the start time specified in the Create Performance Data (CRTPFRDTA) command. | PD (5,0) |
| DATETIME | Interval date and time: The date and time of the sample interval, expressed in local system time. | Timestamp |
| INTSEC | Elapsed interval seconds: The number of seconds since the last sample interval. | PD (7,0) |
| SQCURQRY | Active queries. A snapshot, taken at sample time, of the total number of queries currently active. | B (18,0) |
| SQCURPLN | Plans in SQL plan cache. A snapshot, taken at sample time, of the total number of plans currently in the SQL plan cache. | B (18,0) |
| SQPCSIZE | Plan cache size. The current size (in megabytes) of the SQL plan cache. | B (18,0) |
| SQPCLIMIT | Plan cache size limit. The maximum size (in megabytes) that the SQL plan cache is allowed to be. | B (18,0) |
| SQPCTHRESH | Plan cache size threshold. The maximum size (shown as a percentage of the plan cache size limit) that the SQL plan cache is allowed to be before Db2® automatically manages the SQL plan cache and replaces older plans with new plans. | B (18,0) |
| SQCURSUBC | Sub caches. A snapshot, taken at sample time, of the total number of sub caches. | B (18,0) |
| SQCURSMP | Reserved. | B (18,0) |
| SQCURMTI | MTIs. A snapshot, taken at sample time, of the total number of Maintained Temporary Indexes (MTIs). | B (18,0) |
| SQCURPRUNL | Pruning event listeners. A snapshot, taken at sample time, of the total number of pruning event listeners. | B (18,0) |
| SQCURROQ | Cached runtime objects. A snapshot, taken at sample time, of the total number of cached runtime objects (for queries) in the SQL plan cache. | B (18,0) |
| SQCURTROQ | Cached runtime objects with temporary results. A snapshot, taken at sample time, of the total number of cached runtime objects (for queries) in the SQL plan cache which may have retained part or all of the query answer set in a temporary copy. | B (18,0) |
| SQCURRROQ | Reusable runtime objects. A snapshot, taken at sample time, of the total number of reusable runtime objects (for queries) in the SQL plan cache. | B (18,0) |
| SQCURTEMP | Temporary storage. A snapshot, taken at sample time, of the total amount of temporary storage (in megabytes) associated with the SQL plan cache. | B (18,0) |
| SQPLNBLT | Plans built. The number of plans built during the interval. | B (18,0) |
| SQQRYRROQ | Queries run with reusable runtime objects. The number of queries executed during the interval using runtime objects (for queries) where the runtime objects were candidates for being cached in the SQL plan cache for reuse by the same or other jobs. | B (18,0) |
| SQQRYNRROQ | Queries run with non-reusable runtime objects. The number of queries executed during the interval using runtime objects (for queries) where the runtime objects cannot be cached in the SQL plan cache. | B (18,0) |
| SQQRYNOROQ | Queries run without runtime objects. The number of queries executed during the interval where a plan in the SQL plan cache was used but there were no cached runtime objects (for queries) available for use. | B (18,0) |
| SQPROBES | Plan cache probes. The number of times the plan cache was probed during the interval. | B (18,0) |
| SQMATCH | Plan cache probe matches. The number of times a plan cache probe found a match during the interval. | B (18,0) |
| SQNOMATCH | Plan cache probe misses. The number of times a plan cache probe was unable to find a match during the interval. | B (18,0) |
| SQPCWAKE | Plan cache awakenings. The number of times the plan cache woke up during the interval. | B (18,0) |
| SQPCNAP | Plan cache naps. The number of times the plan cache fell asleep during the interval. | B (18,0) |
| SQPLNPRUN | Plans pruned. The number of plans pruned from the SQL plan cache during the interval due to plan cache size. | B (18,0) |
| SQPLNRMV | Plans removed. The number of obsolete plans removed from the SQL plan cache during the interval. | B (18,0) |
| SQOPEN | Query opens. The number of full and pseudo opens that occurred during the interval. | B (18,0) |
| SQFOPEN | Query full opens. The number of full opens that occurred during the interval. | B (18,0) |
| SQFOPENROQ | Query full opens using cached runtime objects. The number of full opens that occurred during the interval which used both a plan and a cached runtime object from the SQL plan cache. | B (18,0) |
| SQPOPNHCLS | Queries hard closed. The number of queries that were hard closed during the interval. | B (18,0) |
| SQMTICRT | MTIs created. The number of Maintained Temporary Indexes (MTIs) created during the interval. | B (18,0) |
| SQMTIDLT | MTIs deleted. The number of Maintained Temporary Indexes (MTIs) deleted during the interval. | B (18,0) |
| SQAQPWAKE | AQP interrogations. The number of executing queries checked by Adaptive Query Processing (AQP) during the interval. | B (18,0) |
| SQAQPRPL | Runtime object replacements by AQP. The number of runtime objects replaced because of Adaptive Query Processing (AQP). | B (18,0) |
| SQFULLOPT | Full optimizations. The number of full optimizations that occurred during the interval. | B (18,0) |
| SQREOPT | Reoptimizations with valid plans. The number of reoptimizations that occurred during the interval when valid plans existed. | B (18,0) |
| SQPCFLD01 |  Total MTI Size. The current total size (in megabytes) of all
currently existing MTIs. |
B (18,0) |
| SQPCFLD02 | Current plan cache hit ratio. |
B (18,0) |
| SQPCFLD03 | Mapping Errors. The number of mapping errors detected by SQE
during the interval. |
B (18,0) |
| SQPCFLD04 | Reserved. | B (18,0) |
| SQPCFLD05 | Reserved. | B (18,0) |
| SQPCFLD06 | Reserved. | B (18,0) |
| UTCTIME | UTC interval date and time: The date and time of the sample interval, expressed in Coordinated Universal Time (UTC). | Timestamp |
| SQOPTIA101 |
The number of plans examined and deleted during the interval due to a file or member that is
not the same object as the one referred to in the access plan detected. |
B(18,0) |
| SQOPTVA101 |
The number of plans examined and not used during the interval due to the file wait time that
changed since the access plan was created. |
B(18,0) |
| SQOPTOA401 |
The number of plans examined and deleted during the interval due to a significant file size
change since the access plan was created. |
B(18,0) |
| SQOPTVA401 |
The number of plans examined and not used during the interval due to the selectivity of rows
selected being significantly different from the examined plan. |
B(18,0) |
| SQOPTVA402 |
The number of plans examined and not used during the interval due to the disk read response
time being significantly different from the examined plan. |
B(18,0) |
| SQOPTOA501 |
The number of plans examined and deleted during the interval due to a new access path (index)
created since the plan was created. |
B(18,0) |
| SQOPTIA601 |
The number of plans examined and deleted during the interval due to an access path (index)
used by the access plan being deleted since the access plan was created. |
B(18,0) |
| SQOPTIA701 |
The number of plans examined and deleted during the interval due to system programming
changes since the access plan was created. |
B(18,0) |
| SQOPTVA701 |
The number of plans examined and not used during the interval due to a difference in
attributes such as Allow Copy Data (ALWCPYDTA) or *ALLIO/FIRSTIO in the examined plan. |
B(18,0) |
| SQOPTVA702 |
The number of plans examined and not used during the interval due to a difference in native
access options in the examined plan. |
B(18,0) |
| SQOPTIA702 |
The number of plans examined and deleted during the interval due to changes in the
updateability. |
B(18,0) |
| SQOPTVA901 |
The number of plans examined and not used during the interval due to a difference in date,
time, or timestamp format options in the examined plan. |
B(18,0) |
| SQOPTVAA01 |
The number of plans examined and not used during the interval due to a difference in the
specified sort sequence table in the examined plan. |
B(18,0) |
| SQOPTVAB01 |
The number of plans examined and not used during the interval due to a significant difference
in the amount of memory fair share for the query. |
B(18,0) |
| SQOPTVAB02 |
The number of plans examined and not used during the interval due to a significant difference
in the number of processor units available. |
B(18,0) |
| SQOPTVAB03 |
The number of plans examined and not used during the interval due to a difference in the
number of threads allowed for a job. |
B(18,0) |
| SQOPTVAB04 |
The number of plans examined and not used during the interval due to a difference in the
workload group for a job. |
B(18,0) |
| SQOPTVAD01 |
The number of plans examined and not used during the interval due to a difference in the SMP
option. |
B(18,0) |
| SQOPTVB001 |
The number of plans examined and not used during the interval due to a difference in the
QAQQINI option. |
B(18,0) |
| SQOPTVB101 |
The number of plans examined and not used during the interval due to a difference in the
commit level or the rollback hold option. |
B(18,0) |
| SQOPTVB201 |
The number of plans examined and not used during the interval due to a difference in the
FETCH N ROWS specification. |
B(18,0) |
| SQOPTIB401 |
The number of plans examined and deleted during the interval due changes in referential or
check constraints. |
B(18,0) |
| SQOPTVB501 |
The number of plans examined and not used during the interval due to a difference in the
QAQQINI MATERIALIZED_QUERY_TABLE_USAGE option. |
B(18,0) |
| SQOPTIB501 |
The number of plans examined and deleted during the interval due to other changes in MQT
specifications. |
B(18,0) |
| SQOPTVB601 |
The number of plans examined and not used during the interval due to a difference in host
variable values that made this plan not valid for this set of host variables. |
B(18,0) |
| SQOPTRSV01 |
Reserved |
B(18,0) |
| SQOPTRSV02 |
Reserved |
B(18,0) |
| SQOPTRSV03 |
Reserved |
B(18,0) |
| SQOPTRSV04 |
Reserved |
B(18,0) |
| SQOPTRSV05 |
Reserved |
B(18,0) |
| SQHCLS01 |
The number of queries hard-closed by the SQE optimizer during the interval due to an internal
problem with query options |
B(18,0) |
| SQHCLS02 |
The number of queries hard-closed by the SQE optimizer during the interval due to internal
system changes or a bad MTI. |
B(18,0) |
| SQHCLS03 |
The number of queries hard-closed by the SQE optimizer during the interval due to a new
access path (index) over one of the tables in the query. |
B(18,0) |
| SQHCLS04 |
The number of queries hard-closed by the SQE optimizer during the interval SQOPTVB001 to a
significant change in table size since the query was full-opened. |
B(18,0) |
| SQHCLS05 |
The number of queries hard-closed by the SQE optimizer during the interval due to a MQT used
in the access plan that has exceeded its refresh age. |
B(18,0) |
| SQHCLS06 |
The number of queries hard-closed by the SQE optimizer during the interval due to host
variable values that are no longer compatible with current plan. |
B(18,0) |
| SQHCLS07 |
The number of queries hard-closed by the SQE optimizer during the interval due to new
statistics being seen. |
B(18,0) |
| SQHCLS08 |
The number of queries hard-closed by the SQE optimizer during the interval due to the commit
level changing. |
B(18,0) |
| SQHCLS09 |
The number of queries hard-closed by the SQE optimizer during the interval due to the
optimizer detecting a significant change in the amount of IO versus estimated IO. |
B(18,0) |
| SQHCLS10 |
The number of queries hard-closed by the SQE optimizer during the interval due to the
optimizer detecting an incompatibility between the stated optimization goal of *FIRSTIO and the
actual number of rows fetched. |
B(18,0) |
| SQHCLS11 |
The number of queries hard-closed by the SQE optimizer during the interval due to a deferred
index estimate completing or a problem detected during PSEUDO_HOST_VAR INI plan replacement. |
B(18,0) |
| SQHCLS12 |
The number of queries hard-closed by the SQE optimizer during the interval due to AQP
requesting an optimization. |
B(18,0) |
| SQHCLS13 |
The number of queries hard-closed by the SQE optimizer during the interval due to the
optimizer deciding to hard-close the AQP replacement plan. |
B(18,0) |
| SQHCLS14 |
Reserved |
B(18,0) |
| SQHCLS15 |
Reserved |
B(18,0) |
| SQHCLS16 |
Reserved |
B(18,0) |
| SQHCLS17 |
Reserved |
B(18,0) |
| SQHCLS18 |
Reserved |
B(18,0) |
| SQHCLS19 |
Reserved |
B(18,0) |