Hi All,
can sum1 plz tell me the way to find out the SQL query creating LOCKS.
I am using V 8.2 on linux environment.

Thanks
Harsh Verma

Hi,
all of the queries will create locks. There are table/rows locks that are in use in any SQL query. There are IN, IS, IX, SIX, S, U, X and Z locks on tables and S, U, X, W, NS, NW, S, U, X and Z locks on rows.

What are you trying to do? What is your problem?

The only problematic locks are the one that prevents other applications to execute read/write to table.

I have written one script on DB2/Linux that checks every 3 seconds if there are some applications that locks other applications and if it does then application that is locking is forced from the database. The first this was shock for application developers, but soon after some application adoption was made there is almost no forcing anymore. So locking two applications together is dramatically reduces.

Here is SQL if you would like to get all applications that are locking/are locked, so lock-wait conditional appeared:


SELECT CURRENT TIMESTAMP AS CURR_TS , A.AGENT_ID_HOLDING_LK AS AGENT_LOCK, SUBSTR(C.APPL_NAME, 1, 20) AS APP_LOCK , C.SEQUENCE_NO AS APP_LOCK_SEQ, C.APPL_STATUS AS APP_LOCK_APPL_STATUS, E.STMT_TYPE AS APP_LOCK_STMT_TYPE, E.STMT_OPERATION AS APP_LOCK_STMT_OPERATION, A.AGENT_ID AS AGENT_WAIT, SUBSTR(B.APPL_NAME, 1, 20) AS APP_WAIT , B.SEQUENCE_NO AS APP_WAIT_SEQ, B.APPL_STATUS AS APP_WAIT_APPL_STATUS, D.STMT_TYPE AS APP_WAIT_STMT_TYPE, D.STMT_OPERATION AS APP_WAIT_STMT_OPERATION, CURRENT TIMESTAMP - A.LOCK_WAIT_START_TIME AS DURATION , A.LOCK_MODE , A.LOCK_OBJECT_TYPE, A.LOCK_MODE_REQUESTED, A.TABLE_SCHEMA , A.TABLE_NAME , E.STMT_TEXT AS SQL_LOCK_APP, D.STMT_TEXT AS SQL_WAIT_APP FROM TABLE (SNAPSHOT_LOCKWAIT('${DB_NAME}', -1)) AS A INNER JOIN TABLE (SNAPSHOT_APPL_INFO('${DB_NAME}', -1)) AS B ON A.AGENT_ID=B.AGENT_ID INNER JOIN TABLE (SNAPSHOT_APPL_INFO('${DB_NAME}', -1)) AS C ON A.AGENT_ID_HOLDING_LK=C.AGENT_ID INNER JOIN TABLE (SNAPSHOT_STATEMENT('${DB_NAME}', -1)) AS D ON A.AGENT_ID=D.AGENT_ID INNER JOIN TABLE (SNAPSHOT_STATEMENT('${DB_NAME}', -1)) AS E ON A.AGENT_ID_HOLDING_LK =E.AGENT_ID WHERE CURRENT TIMESTAMP - A.LOCK_WAIT_START_TIME > 3

Note: instead of ${DB_NAME} use your database name, e.g. SAMPLE

By the way, I have migrated database form v8.2 to v9.5 and set all parameters that could be set to automatic and there is way less locking accouring.

Also check if you have converted type-1 indexes to type-2 indexes. Type-2 indexes were introduced in v8, but if you have backup/restore database from v7 then you have type-1 indexes. This index type locks more because it uses less optimal next key locking. Maybe you should consider to migrate to latest version.

You can convert indexes using: db2 reorg index index_name convert
Check for all indexes that are available in database by selecting syscat.indexes table. Read more about type-1, type-2 indexes:
DB2 Migration to Version 8 - Part 2

If you want to get more help, please write what is your problem and tell as what would you like to do to get solved it.
Regards

I am just trying to do simple update and select nothing else..
there are 40 locks created.
They are re created once i force the application who is in Lock-wait condition.

Is there any way to find the exact sql creating locks,I can't fire the query u have provided as it is a production environment..
is there any way to resove locks, please suggest..




Thnx
Harsh Verma

Hi,
you are not forcing the correct application. If you have forced application that is in lock-wait that you have forced the one that is waiting for the lock. You have to force application that is locking table. In my SQL you have to force A.AGENT_ID_HOLDING_LK.

You can establish "event monitor" to have each lock stored into table.
Regards