News and tips on Db2 v12 migration : Episode 1

19 maart 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
FROM SYSIBM.SYSTABLESPACESTATS
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 :
 
Top Access by 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 ...