Resolving loop, wait, or performance problems

If a request takes longer than expected to complete, check the application requester (AR) first.

Check the job log for message SQL7969, which indicates that a connection to a relational database is complete. This tells you that the application is a distributed relational database application.
Determine whether the system or the application is waiting or looping.
1. Check the AR for a loop using the Work with Job (WRKJOB) command to display the program stack.
2. Check the program stack to determine whether the system is looping. If the application itself is looping, contact the application programmer for resolution.
Determine whether the application requester is waiting.
- If you see QAPDEQUE and QCNSRCV on the stack, the AR is waiting for the application server (AS).
- If the system is not in a communications wait state, use problem analysis procedures to determine whether there is a performance problem or a wait problem.

Figure 1. Resolving loop, wait, or performance problems on the application requester

Flow chart for resolving wait, loop, and performance problems on the AR.

You can find the AR job name by looking at the job log on the AS. When you need to check the AS job, use the Work with Job (WRKJOB), Work with Active Jobs (WRKACTJOB), or Work with User Jobs (WRKUSRJOB) command to locate the job on the AS.

From one of these job displays, look at the program stack to see if the AS is looping:

If it is looping, use problem analysis to handle the problem.
If it is not looping, check the program stack for WAIT with QCNTRCV, which means the AS is waiting for the AR.
If both systems are in this communications wait state, there is a problem with the network.
If the AS is not in a wait state, there is a performance issue.

Figure 2. Resolving wait, loop, or performance problems on the application server

Flow chart for resolving wait, loop, or performance problems on the AS.

Two common sources of slow query performance are:

An accessed table does not have an index. If this is the case, create an index using appropriate fields as the key.
The rows returned on a query request are not blocked. Whether the rows are blocked can cause a significant difference in query performance. Tune the application to take advantage of blocking.

If you have not already created the SQL packages for the product in DB2® for i5/OS, the first time you connect to DB2 for i5/OS from a workstation using a product such as DB2 JDBC Universal Driver or DB2 for Linux, UNIX, and Windows, the packages are created automatically. The NULLID collection might need to be created automatically as well. This results in a delay in getting a response from the system for one of the first SQL statements issued after the initial connection.

A long delay can occur if the system to which you are trying to connect over TCP/IP is not available. A several minute timeout delay precedes the message A remote host did not respond within the timeout period. An incorrect IP address in the RDB directory can also cause a delay.