TL;DR Summary: Updatable catalog statistics in Db2 12 enhance performance optimization and access path selection by providing critical data distribution insights.
- Key tables include SYSIBM.SYSCOLDIST, SYSIBM.SYSCOLSTATS, and SYSIBM.SYSTABLES.
- These statistics significantly improve query performance and understanding of data distribution.
Catalog statistics that updatable and used for access path selection
| Table | Statistics |
| SYSIBM.SYSCOLDIST | CARDF COLGROUPCOLNO COLVALUE FREQUENCYF HIGHVALUE LOWVALUE NUMCOLUMNS QUANTILENO TYPE |
| SYSIBM.SYSCOLSTATS | COLCARD HIGHKEY LOWKEY PARTITION |
| SYSIBM.SYSCOLUMNS | COLCARDF HIGH2KEY LOW2KEY |
| SYSIBM.SYSINDEXES | CLUSTERRATIOF FIRSTKEYCARDF FULLKEYCARDF NLEAF NLEVELS DATAREPEATFACTORF |
| SYSIBM.SYSINDEXSTATS | DATAREPEATFACTORF |
| SYSIBM.SYSKEYTARGETS | HIGH2KEY LOW2KEY STATS_FORMAT |
| SYSIBM.SYSKEYTGTDIST | CARDF KEYGROUPKEYNO KEYVALUE FREQUENCYF HIGHVALUE LOWVALUE NUMKEYS QUANTILENO TYPE |
| SYSIBM.SYSROUTINES | CARDINALITY INITIAL_INSTS INITIAL_IOS INSTS_PER_INVOC IOS_PER_INVOC |
| SYSIBM.SYSTABLES | CARDF NPAGES NPAGESF PCTROWCOMP |
| SYSIBM.SYSTABLESPACE | NACTIVEF |
| SYSIBM.SYSTABSTATS | CARDF NPAGES |
Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.
Db2 Guide
- Db2 12 for z/OS: An Introduction
- Scalar Functions in Db2 12
- How to Read IBM<sup>®</sup> Syntax Diagrams for Db2 12
- Language Elements in Db2 12
- Aggregate Functions in Db2 12
- Table & Row Functions in Db2 12
- Queries in Db2 12
- Commands in Db2 12
- SQL Control Statements in Db2 12
- Explain Tables in Db2 12
- DSNZPARMs in Db2 12
- Bind Parameters in Db2 12
- Db2 Limits in Db2 12
- SQL Communication Area (SQLCA) in Db2 12
- REXX SQLCA in Db2 12
- Get Diagnostics in Db2 12
- IFCIDS in Db2 12
- Catalog Tables in Db2 12
- Exceptions in Db2 12
- XML Schema Repository Tables in Db2 12
- Updatable Catalog Statistics in Db2 12
- IBM Utilities in Db2 12
- SQL Positive Return Codes in Db2 12
- SQL Error Return Codes in Db2 12
These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.