Troubleshoot Long running sql statements with LONG_RUNNING_SQL view

A customer called me yesterday and complained about slow response time on a DB2 database. I asked a few questions and they mentioned a few ad-hoc queries were executing. If I’m doing production troubleshooting – running a sql trace is a powerful method to retrieve detailed information. Prior to getting the detailed information , I’ll use the SYSIBMADM.LONG_RUNNING_SQL view . This is a very useful DB2 administrative view , presenting long – running queries. Under the hood , the LONG_RUNNING_SQL view joins some system snapshots. SELECT APPL_NAME,AUTHID,INBOUND_COMM_ADDRESS, STMT_TEXT, AGENT_ID, ELAPSED_TIME_MIN, APPL_STATUS, DBPARTITIONNUM FROM SYSIBMADM.LONG_RUNNING_SQL ORDER BY APPL_NAME Check the APPL_STATUS... Read more →

How to write a DB2 loop with INSERT

Question: I’d like write a sql statement to loop through an INSERT statement and increment with a count. The purpose is to create some test tables for load testing. Answer: It is possible to create a loop in DB2 which loops through an incremental INSERT. This is a basic example, which can be customised for your purposes.Note the use of ATOMIC. The purpose of ATOMIC is to rollback before the call is passed back to the requestor, if there is a problem. In this example the CNT variable increments at every INSERT, up until it is under 100000. db2 “CREATE... Read more →

How to set up Avamar and DB2 backup object retention

I was configuring a DB2 LUW database to work with Avamar as the backup system. An issue arose about managing the DB2 database backup retention period. Does DB2 LUW or Avamar control the retention period for a DB2 LUW database? For some comparison , I’ll outline briefly how TSM is configured with DB2 LUW. If I set the NUM_DB_BACKUPS, REC_HIS_RETENTN, AUTO_DEL_REC configurations and values in a DB2 database and use TSM , then these values have a direct impact on the TSM retention. In a typical TSM configuration , the usage AUTO_DEL_REC_OBJ will delete the objects from TSM once the... Read more →