19 March 2019
From v12, a new column GETPAGES is present In SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS.
This new column allows to easily find many useful information without activating trace or loading data. You just have to write a query.
A performance analysis is often based on package consumption but the number of GETPAGES by object is also a good marker about your DB2 activity.
The following query gives the Top 10 accessed objects since V12 migration:
SELECT TRIM(DBNAME)||'.'||TRIM(NAME)||'.'||CHAR(PARTITION), GETPAGES
ORDER BY GETPAGES DESC
FETCH FIRST 10 ROWS ONLY
Once retrieved, the data can then be easily plotted on a graph as shown below. Doing so, we can clearly observe a Pareto distribution with 20% of the objects accounting for 80% of the GETPAGES :
In this particular case, additional analysis revealed this huge amount of GETPAGES on this specific object was due to a lot of different packages or Dynamic SQL. These packages were not big CPU consumers so they didn’t appear during a performance improvement process. We fixed this with an index creation .
Episode 2 will be based upon new message DSNB233I ...