Collecting data for DB2 performance issues

Merative ™ Social Program Management is now Cúram™ by Merative

Collecting data for Cúram DB2 performance issues

Question & Answer

Question

What data is required for troubleshooting Cúram based DB2 Performance Issues? The information that you gather is also known as MustGather information.

Answer

On windows, In order to collect data for DB2 Performance Issues, perform the following:

run db2cmd to open DB2 CLP Command prompt

1. Attach to the DB2 instance: db2 ATTACH TO <<instancename>>
2. Connect to the DB2 database:db2 CONNECT TO <<databasename>> USER <<username>> USING <<password>>
3. Turn on the DB2 monitor switches and reset the monitor data, use db2 GET MONITOR SWITCHES first if you need to note the current settings and revert following your test
   • db2 UPDATE MONITOR SWITCHES USING TABLE ON
   • db2 UPDATE MONITOR SWITCHES USING LOCK ON
   • db2 UPDATE MONITOR SWITCHES USING SORT ON
   • db2 UPDATE MONITOR SWITCHES USING STATEMENT ON
   • db2 UPDATE MONITOR SWITCHES USING BUFFERPOOL ON
   • db2 UPDATE MONITOR SWITCHES USING UOW ON/db2 RESET MONITOR ALL
4. Run your test. After the test completes, collect the monitor data by using shell redirection to save the data, consider using a timestamp or other uniquely identifying information for each output file:
   • db2 GET SNAPSHOT FOR DATABASE ON <<databasename>> > database.txt
   • db2 GET SNAPSHOT FOR APPLICATIONS ON <<databasename>> > app.txt
   • db2 GET SNAPSHOT FOR TABLES ON <<databasename>> > tables.txt
   • db2 GET SNAPSHOT FOR LOCKS ON <<databasename>> > locks.txt
   • db2 GET SNAPSHOT FOR TABLESPACES ON <<databasename>> > tbsp.txt
   • db2 GET SNAPSHOT FOR DYNAMIC SQL ON <<databasename>> > sql.txt
   • db2 GET SNAPSHOT FOR BUFFERPOOLS ON <<databasename>> > bp.txt
   • db2 GET DATABASE CONFIGURATION FOR <<databasename>> > cfg_db.txt
   • db2 GET SNAPSHOT FOR DATABASE MANAGER > dbm.txt
   • db2 GET DATABASE MANAGER CONFIGURATION > cfg_dbm.txt
5. After you collect the data, preserve the data; for example: jar -cvMf db2mon.zip database.txt app.txt tables.txt locks.txt tbsp.txt sql.txt bp.txt cfg_db.txt dbm.txt cfg_dbm.txt
6. After you collect the data, revert the switches to their previous state (usually OFF) and reset the monitor data; for example:
   • db2 UPDATE MONITOR SWITCHES USING TABLE OFF
   • db2 UPDATE MONITOR SWITCHES USING LOCK OFF
   • db2 UPDATE MONITOR SWITCHES USING SORT OFF
   • db2 UPDATE MONITOR SWITCHES USING STATEMENT OFF
   • db2 UPDATE MONITOR SWITCHES USING BUFFERPOOL OFF
   • db2 UPDATE MONITOR SWITCHES USING UOW OFF
   • db2 RESET MONITOR ALL
7. Repeat the steps as needed for multiple tests.

• Once you have collected the preceding information, you can submit the diagnostic information to Merative support on a support case via Cúram support