Application does not complete in the expected time
If the request takes longer than expected to complete, the first place to check is at the application requester (AR).
Check the job log for message SQL7969, which indicates that a connection to a relational database is complete. This tells you the application is a distributed relational database application. Check the AR for a loop by using the Work with Job (WRKJOB) command to display the program stack, and check the program stack to determine whether the system is looping. If the application itself is looping, contact the application programmer for resolution. 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 show whether there is a performance problem or a wait state somewhere else. Figure 1. Resolving wait, loop, or performance problems on the application requester
You can find the AR job name by looking at the job log on the AS. When 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 your network. If the AS is not in a wait state, there is a performance issue that might have to be addressed.
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 an appropriate field or 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. It is important to understand the factors that affect blocking, and tune the application to take advantage of it.
If you have not already created the SQL packages for the product in DB2® UDB for iSeries™, the first time you connect to DB2 Universal Database™ for iSeries 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 somewhat lengthy delay in getting a response back from the system for one of the first SQL statements issued after the initial connection.
A long delay occurs 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 causes this behavior as well. Figure 2. Resolving wait, loop, or performance problems on the application server
Parent topic:
Isolating distributed relational database problems
Related concepts
Factors that affect blocking for DRDA
Related tasks
Locating distributed relational database jobs
Working with jobs in a distributed relational database
Working with user jobs in a distributed relational database
Working with active jobs in a distributed relational database