DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
This exercise demonstrates how you can determine the cause of an application slow down with the DB2® WLM monitoring facilities.
Estimated time: 10-15 minutes
The DB2 WLM monitoring facilities provide information and statistics for work in a database. Once the cause of a slow-down is identified, you can remedy the situation.
Two applications are used in this exercise, app1.db2 and app2.db2. Both applications perform DML operations on the SAMPLE database. Run the app1.db2 script in one window followed immediately by the app2.db2 script in a second window.
db2 -tvf app1.db2
db2 -tvf app2.db2The app2.db2 script should now be hanging. From a third window, issue table function WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES to find the states of all applications running on the database. For this example, you can think of a workload occurrence as the same as an application. This table function shows information for all workload occurrences in a service class. Since we want to see all workload occurrences in the database, we use wildcards represented by ' ' as service_superclass_name and service_subclass_name input parameters.
CONNECT TO SAMPLE
SELECT INTEGER(APPLICATION_HANDLE) APPL_HANDLE,
VARCHAR(CLIENT_APPLNAME, 15) AS APPL_NAME,
VARCHAR(SYSTEM_AUTH_ID, 20) AS USER_ID
FROM TABLE
(WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES('', '', -2))The output will look something such as the following:
APPL_HANDLE APPL_NAME USER_ID
----------- --------------- --------------------
12 CLP app1.db2 DB2USR1
17 CLP app2.db2 DB2USR1
18 - DB2USR1
19 - DB2USR1
4 record(s) selected.From the output, we can tell that the application handle for app2.db2 is 17.
To find out what the agents for app2.db2 are doing use the WLM_GET_SERVICE_CLASS_AGENTS table function. This table function shows information on agents working in a service class. Since we want to see the agents working for application handle 17, we specify this in the application_handle input parameter. For this example, we are not interested in agents for a particular service class, so we specify wildcards for the service_superclass_name and service_subclass_name input parameters.
SELECT INTEGER(APPLICATION_HANDLE) AS APPL_HANDLE,
UOW_ID, ACTIVITY_ID,
VARCHAR(AGENT_TYPE, 15) AS AGENT_TYPE,
VARCHAR(AGENT_STATE, 10) AS AGENT_STATE,
VARCHAR(EVENT_TYPE, 10) AS EVENT_TYPE,
VARCHAR(EVENT_OBJECT, 10) AS EVENT_OBJ,
VARCHAR(EVENT_STATE, 10) AS EVENT_STATE
FROM TABLE
(WLM_GET_SERVICE_CLASS_AGENTS('', '', 17, -2))The output will look something like
APPL_HANDLE UOW_ID ACTIVITY_ID AGENT_TYPE AGENT_STATE EVENT_TYPE
EVENT_OBJ EVENT_STATE
----------- ----------- ----------- --------------- ----------- ----------
---------- -----------
17 1 2 COORDINATOR ACTIVE ACQUIRE
LOCK IDLE 1 record(s) selected.From the output, you can see that the coordinator agent for application 17 is idle and waiting to acquire a lock. This is the reason why app2.db2 appears to be hanging.
Now that we know why the application is hanging, we can remedy the situation. We know the application is waiting on a lock. To find out which lock this application is waiting on and which application is holding the lock, we can use the db2pd tool. First, we need to find out the current transaction number for our hanging application: Issue db2pd -transactions for application handle 17.
db2pd -db sample -transactions app=17The output will look something such as the following:
Address AppHandl [nod-index] TranHdl Locks State
Tflag Tflag2 Firstlsn Lastlsn LogSpace
SpaceReserved TID AxRegCnt GXID
0x07000000302A7080 17 [000-00017] 7 5 READ
0x00000000 0x00000000 0x000000000000 0x000000000000 0
0 0x000000000AC3 1 0From the output, we can tell that application 17 has transaction handle 7. We can now find which locks this transaction is waiting on by issuing the db2pd -locks command for transaction handle 7.
db2pd -db sample -locks 7 waitThe output will look something such as the following:
Address TranHdl Lockname Type Mode Sts
Owner Dur HoldCount Att ReleaseFlg
0x07000000304013F0 7 00020010000000000640002D52 Row .NS W
2 1 0 0x00 0x00000002The output shows that the application is waiting on a row lock. The owner of the lock has transaction handle 2. This transaction is holding the lock and causing our hang. The final step is to determine the corresponding application handle for transaction handle 2. Issue db2pd -transactions command for transaction handle 2.
db2pd -db sample -transactions 2The output will look something such as the following:
Address AppHandl [nod-index] TranHdl Locks State
Tflag Tflag2 Firstlsn Lastlsn LogSpace
SpaceReserved TID AxRegCnt GXID
0x07000000302A2080 12 [000-00012] 2 6 WRITE
0x00000000 0x00000000 0x000002EE000C 0x000002EE005E 232
396 0x000000000ABB 1 0From the output, we can see that transaction handle 2 corresponds to application handle 12. Referring back to the results from table function WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES, you can see that application 12 refers to app1.db2. This application is holding a row lock that is needed by app2.db2. To make app2.db2 proceed, you may commit, rollback or terminate the unit of work or process from the window running app1.db2. Alternatively, you may also force off app1.db2 by issuing FORCE APPLICATION on application handle 12.
db2 force application (12)Additional Information: Another way to diagnose hanging applications due to lock contention is to use the MON_GET_APPL_LOCKWAIT and the MON_FORMAT_LOCK_NAME monitor table functions. These table functions provide information on lock holders and waiters.