Gaining Insights from Db2 Statistics

In a recent session, Todd Havekost, a Senior z/OS Performance Consultant at IntelliMagic, shared his expertise on utilizing Db2 Statistics and Accounting Data for performance improvement in z/OS infrastructures.

Todd emphasized the vast potential of Db2 and z/OS metrics to provide operational insights but also acknowledged the challenges often faced, such as reactive use of SMF data, siloed tooling, handling massive data volumes, and the scarcity of specialized skills due to retirements.

The session delved into Db2 statistics data, exploring key metrics and automated assessments for proactive monitoring. Todd highlighted buffer pool metrics, a critical component for Db2 performance and efficiency, and demonstrated how to analyze and optimize buffer pools using IntelliMagic Vision. He also discussed integrating Db2 IFCID 199 data with SMF 42 IO performance data for deeper insights.

Buffer Pools

Todd then transitioned to Db2 accounting data, captured in SMF 101 records, which provides detailed transaction-level data. He outlined strategies to navigate this extensive data, focusing on connection types, correlation IDs, authorization IDs, and plan or package names. This detailed analysis allows pinpointing issues and optimizing performance across various Db2 components.

When analyzing Db2 accounting data by connection type, Todd compared the volume of SQL statements and logging activity across different connection types, highlighting distinct time-of-day profiles for each. He notes significant differences in elapsed time per commit for work coming from various Db2 callers.

SQL Statements

He then focused on the analysis of prefetch activity in Db2, observing that dynamic prefetches dominate most caller types, with sequential prefetch being significant for DDF work. 

The discussion then moved to exploring data using correlation IDs, which vary based on the connection type. This analysis includes looking at CPU consumption and identifying specific jobs or transactions responsible for spikes in resource usage or particular patterns.

The session concluded with a discussion on overcoming the common challenges in data analysis, emphasizing proactive approaches, collaborative tools, focused data analysis strategies, and intuitive reporting for less experienced personnel. Todd demonstrated how combining data from both sources can provide a more comprehensive view of activities within specific Db2 members over time.

In summary, the session provided valuable insights into leveraging Db2 statistics and accounting data to enhance system performance, addressing both technical and operational challenges in the process.

Check out this Virtual User Group Session video, transcript, and resources here.

Amanda Hendley is the Managing Editor of Planet Mainframe and  Co-host of the iTech-Ed Mainframe User Groups.  She has always been a part of the technology community having spent eleven years at Technology Association of Georgia and six years at Computer Measurement Group. Amanda is a Georgia Tech graduate and enjoys spending her free time renovating homes and volunteering with  in Atlanta, Georgia.

Leave a Reply

Your email address will not be published. Required fields are marked *