DB2 commands to analyze performances issue

Today a list of commands to run (before, during and after the workload) to check performances issue of a db2 database. On the db2 instance connect to the database and run the commands:

• Start up the system

Db2 and the application using the database needs up and running. 

1) db2support -install

2) db2 "select * from table(mon_get_package_cache_stmt(null,null,null,null))" > pkg1.out

3) db2 "get snapshot for all on <db> "> snap1.out

4) db2pd -d <db> -dynamic > dyn1.out

• Run the workload

While the workload is running at peak load run (ie run multiple times):

5) tprof -skel -x sleep 10, then copy sleep.prof output to tprof1.out

6) db2 "select * from table(mon_get_package_cache_stmt(null,null,null,null))" > pkg2.out

7) db2 "get snapshot for all on <db> "> snap2.out

8)  db2pd -d <db> -dynamic > dyn2.out

Additionally:
- add a capture of edu timings to the data gathering script 
db2pd -edus > pdedus.<timestamp>

- when running at the high load interval, run db2trc (just need this once):

db2trc on -perfcount -t;sleep 30;db2trc dmp perftrc.dmp;db2trc perffmt perftrc.dmp perftrc.perffmt 

• Workload ends

Finally collects these output:

9) db2 "select * from table(mon_get_package_cache_stmt(null,null,null,null))" > pkg.end.out

10) db2 "get snapshot for all on <db> "> snap.end.out

11) db2pd -d <db> -dynamic > dyn.end.out

12) db2pd -d <db> -apm dump dynamic zpas (outputs a message with an output file name - copy that file to be analyzed)

13) Save a copy of the db2diag.log

Now you have all ouput files that can be analyzed.

Calculating footprint of DB2 database tables

Footprint is always important. Here I show you some examples of queries to calculate it for DB2 tables and indexes. In particular there are some interesting administrative table functions:  ADMIN_GET_TAB_INFO to get information about table size and ADMIN_GET_INDEX_INFO to get information about index size.

FOOTPRINT OF TABLES:
•  Data physical disk space for a specific table on a schema (MYSCHEMA), the value of data_object_p_size is the physical size of the data, the value is reported in KB:

db2 "SELECT data_object_p_size AS Table_Data_Physical_Size FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('MYSCHEMA','MYTABLE'))"

•  Data physical disk space for each table (passing NULL to the second parameter of the table function) and ordered by the descending size:

SELECT tabname, data_object_p_size  AS Table_Data_Physical_Size  FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('MYSCHEMA',NULL)) ORDER BY Table_Data_Physical_Size DESC

• Total physical disk space for all tables for data, indexes, long, lob and xml the value reported in GB:

SELECT SUM(((data_object_p_size + index_object_p_size + long_object_p_size + lob_object_p_size + xml_object_p_size)/1024)/1024) AS total_p_size FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO( 'MYSCHEMA', NULL ))

• Total physical for a specific table for data, indexes, long, log and xml, the value is reported in MB:

SELECT SUM(((data_object_p_size + index_object_p_size + long_object_p_size + lob_object_p_size + xml_object_p_size)/1024)) AS total_p_size FROM TABLE( SYSPROC.ADMIN_GET_TAB_INFO( 'MYSCHEMA', 'MYTABLE'))

FOOTPRINT OF INDEXES:
•  Physical disk space of the all indexes (MB)  for all tables or a specific table:

SELECT tabname, indname, index_object_p_size/1024 from table(SYSPROC.ADMIN_GET_INDEX_INFO('', 'MYSCHEMA',NULL)) AS t GROUP BY tabname, indname, index_object_p_size

To get the footprint for a specific table it needs to specify a string value instead of third parameter NULL on the table function. For example pass a table name like 'MYTABLE'.

Visit here the DB2 documentation to check the details of the table functions.