Investigating and resolving timeout situations

Timeout situations can occur for many reasons, including factors relating to both DB2® or IRLM.

To investigate and resolve timeout situations:

1. Check the LOCKRULE column value in SYSIBM.SYSTABLESPACE for the table space being accessed. A lock size of TABLE or TABLESPACE might cause a timeout. If your application does not need to lock the entire table or table space, you can resolve the timeout by changing the lock size to PAGE or ANY.
2. Check the number of LOCKS PER TABLE(SPACE), which was set when DB2 was installed. If many page or row locks are acquired and held, a small value for LOCKS PER TABLE(SPACE) might cause lock escalation. You can resolve the timeout by changing the value on the DSNTIPK update panel or by specifying the RELEASE(COMMIT) bind options and committing changes more frequently.
3. Check EXPLAIN output for the failing SQL statement and examine the value of the PLAN_TABLE.TSLOCKMODE column for every table or table space. If the competing applications are attempting to obtain incompatible locks, you might be able to resolve the timeout by running the applications sequentially rather than concurrently. You might also resolve the problem by changing lock size and the following bind options: RELEASE and ISOLATION.
4. Check the RELEASE bind option for the application. Binding with RELEASE(DEALLOCATE) causes partition, table, table space, and DBD locks to be held longer than binding with RELEASE(COMMIT), possibly causing a timeout.
5. Issue the DISPLAY DATABASE command and specify the LOCKS keyword during program execution. By doing so, you can verify that only expected locks are held when the timeout occurs. If unexpected locks are held, you might also resolve the problem by changing lock size and the following bind options: RELEASE and ISOLATION.
6. Check the ISOLATION bind option of the application. The isolation level affects whether locks are acquired and how long they are held.
7. Increase the wait time if the condition is caused by an undetected deadlock. For example, an agent might be holding a lock on the resource for longer than the specified time or the IRLM default wait time.

   If the time limit is too small, you can increase the limit by specifying a new IRLM locked resource wait time limit. You can take one of the following actions to make the change:

   • Use the parameter in the DSN6SPRM assembler macro in the DSNTIJUZ job stream
   • Update the 'wait-time' field of the DB2 installation IMS™ Resource Lock Manager panel, IRLMRWT.

   You must also specify all other parameters contained in this version of the CSECT. Then take one of the following actions:

   • Resubmit installation job DSNTIJUZ with the link-edit SYSIN file 'NAME' parameter that matches the -START DB2 'PARM=' parameter.
   • Reassemble DSN6SPRM and relink-edit DSNZPARM by resubmitting installation job DSNTIJUZ. The link-edit SYSIN file 'NAME' parameter must match the -START DB2 'PARM=' parameter.