To shred the lock plan data in the XMLTextInfo field and see details of a lock contention, follow this procedure:
- Make sure that the DIPPOST script ran on the system. At installation, DIPPOST is the final script executed as part of DIPALL. The script executes quickly if you need to run it. From the Linux command line, start DIP and then run the DIPPOST script:
dip
Select the name of the post commands script:DIPPOST Executing DIPPOST Please wait... DIPPOST is complete Would you like to execute another DIP script (Y/N)? n Exiting DIP...
- Using BTEQ, log on, delete any objects created by previous use of this procedure, and create a user-defined target database:
.logon unzen,dbc; drop database TargetDBName create database TargetDBName as perm=10000000
- Grant privileges to the new user locklogshredder and log on as locklogshredder.The DIPPOST script created user locklogshredder.
grant all on TargetDBName to TargetDBName with grant option; grant all on TargetDBName to dbc with grant option; grant all on TargetDBName to locklogshredder with grant option; grant all on locklogshredder to TargetDBName with grant option; grant all on td_sysxml to TargetDBName with grant option; grant all on TargetDBName to td_sysxml with grant option; .logon unzen/locklogshredder;
- Drop the target tables in case they remain from a previous shredding operation.The shredding operation creates the target tables to store the shredded data. The user does not create the SQL DDL. However, the user must prepare space for and accommodate the table. After each shredding operation, clean up the data from the previous shredding operation to avoid accumulating old data.
drop table TargetDBName.Shredded_LockTbl; drop table TargetDBName.Shredded_TransTbl;
- Prepare the output to be formatted as needed using BTEQ.
- Call the stored procedure that creates the target tables with the shredded data. If the tables already exist, a non-zero result is returned.
CALL LockLogShredder.SP_CreateTable('TargetDBName','Shredded_LockTbl', 'Shredded_TransTbl', resultCode); - Call the stored procedure that populates the target tables.
CALL LockLogShredder.SP_LockLog_Shredder('select random(0,21437899), CREATEXML(xmltextinfo) from dbc.dbqlxmllocktbl', 'TargetDBName','Shredded_Locktbl', 'Shredded_TransTbl', resultCode);This stored procedure shreds the XML data on DBQLXMLLockTbl, converting each XML from a long text string into multiple fields and storing them in two target tables. Target table Shredded_LockTbl holds the shredded output of most of the lock information in the TextInfo column of DBQLXMLLockTbl. The lock contention information contains only the blocking transaction and the first transaction that is blocked by it. Target table Shredded_TransTbl expands this to include all blocked transactions waiting behind the blocking transaction. - Display the count of the shredded rows in the target tables, so you know in advance the size of the result:
sel count(*) from TargetDBName.Shredded_LockTbl; sel count(*) from TargetDBName.Shredded_TransTbl;
- Query the shredded lock table and transaction table to see the list of blocked transactions:
sel queryid from TargetDBName.Shredded_LockTbl order by 1; sel queryid from TargetDBName.Shredded_TransTbl order by 1;
- Query the data to see the primary lock table rows. The following example retrieves information from Shredded_LockTbl to show the queries that are blocked from getting requested locks. Here table t3 already has a Write lock on it, and two other requests are blocked when they try to get Read and Exclusive locks on the same table. The Shredded_LockTbl.BlockedLockTypeRequested field gives information about the lock request that blocked the queries.
SELECT QueryId, DatabaseName, TableName, SessionID_Combined , BlockedUserName, BlkedDatabaseName , BlkedTableName , BlkedPEVproc, BlkedTransactionStartTime, BlkedStepNum_1, BlkedStepNum_2, BlockedSessionID_Combined, BlockedLockTypeRequested, BlkedTransactionState FROM TargetDBName.Shredded_LockTbl; *** Query completed. 2 rows found. 14 columns returned. *** Total elapsed time was 1 second. queryid 307195142968284064. DatabaseName USER1 TableName t3 SessionID_Combined 1646 BlockedUserName ? BlkedDatabaseName ? BlkedTableName ? BlkedPEVproc ? BlkedTransactionStartTime ? BlkedStepNum_1 ? BlkedStepNum_2 ? BlockedSessionID_Combined 1650 BlockedLockTypeRequested Read BlkedTransactionState ? queryid 307195142968284063. DatabaseName USER1 TableName t3 SessionID_Combined 1650 BlockedUserName ? BlkedDatabaseName ? BlkedTableName ? BlkedPEVproc ? BlkedTransactionStartTime ? BlkedStepNum_1 ? BlkedStepNum_2 ? BlockedSessionID_Combined 1651 BlockedLockTypeRequested Exclusive BlkedTransactionState ? - Query the data to see the transaction table rows. The following example shows the transaction state of a request during a lock contention. A query was blocked infinitely for requesting an Exclusive lock on t3 and was aborted after a while. In the following output, a second entry for the same queryid, without any object information and transaction state, shows that the query was aborted due to infinite waiting.
SELECT QueryID, DatabaseName, TableName, StepNum_1, StepNum_2, TransactionState, TransactionStartTime, unique_1 , unique_2 , PEVproc , LockObjectRequested, LockTypeRequested, LockStatus FROM TargetDBName.Shredded_TransTbl; *** Query completed. 3 rows found. 13 columns returned. *** Total elapsed time was 1 second. queryid 307195142968284072. DatabaseName USER1 TableName t3 StepNum_1 1 StepNum_2 0 TransactionState Inactive TransactionStartTime 2017-11-29 06:09:01.930000 unique_1 15 unique_2 10014 PEVproc 30719 LockObjectRequested R LockTypeRequested Exclusive LockStatus Waiting queryid 307195142968284071. DatabaseName ? TableName ? StepNum_1 ? StepNum_2 ? TransactionState ? TransactionStartTime ? unique_1 ? unique_2 ? PEVproc ? LockObjectRequested ? LockTypeRequested ? LockStatus ? queryid 307195142968284072. DatabaseName ? TableName ? StepNum_1 ? StepNum_2 ? TransactionState ? TransactionStartTime ? unique_1 ? unique_2 ? PEVproc ? LockObjectRequested R LockTypeRequested Exclusive LockStatus Waiting - Join the shredded target tables Shredded_LockTbl and Shredded_TransTbl using the QueryId field, which is in both tables.
SELECT a.queryid (TITLE 'QUERYID'), a.DatabaseName (TITLE 'Lock_DatabaseName'), a.TableName (TITLE 'Lock_TableName'), a.UserName (TITLE 'Lock_UserName'), a.AccountName (TITLE 'Lock_AccountName'), a.MessageClass (TITLE 'Lock_MessageClass'), a.MessageKind (TITLE 'Lock_MessageKind'), a.MessageClassName (TITLE 'Lock_MessageClassName'), a.MessageKindName (TITLE 'Lock_MessageKindName'), a.WorkLevel (TITLE 'Lock_WorkLevel'), a.OperationType (TITLE 'Lock_OperationType'), a.JobType (TITLE 'Lock_JobType'), a.LockTypeRequested (TITLE 'Lock_LockTypeRequested'), a.LockObjectRequested (TITLE 'Lock_LockObjectRequested'), a.BlockedLockTypeRequested (TITLE 'Lock_BlockedLockTypeRequested'), a.BlockedLockObjectRequested (TITLE 'Lock_BlockedLockObjectRequested'), a.LockDelay (TITLE 'Lock_LockDelay'), a.LocalDeadLock (TITLE 'Lock_LocalDeadLock'), a.GlobalDeadLock (TITLE 'Lock_GlobalDeadLock'), a.MultipleBlocker (TITLE 'Lock_MultipleBlocker'), a.ErrorText (TITLE 'Lock_ErrorText'), a.AbortFlag (TITLE 'Lock_AbortFlag'), a.ErrorCode (TITLE 'Lock_ErrorCode'), b.queryid (TITLE 'COMMON QUERYID'), b.DatabaseName (TITLE 'Trans_DatabaseName'), b.TableName (TITLE 'Trans_TableName'), b.unique_1 (TITLE 'Trans_unique_1'), b.unique_2 (TITLE 'Trans_unique_2'), b.PEVproc (TITLE 'Trans_PEVproc'), b.LockTypeRequested (TITLE 'Trans_LockTypeRequested'), b.LockObjectRequested (TITLE 'Trans_LockObjectRequested') FROM TargetDBName.Shredded_LockTbl a, TargetDBName.Shredded_TransTbl b WHERE a.QueryId = b.QueryId and a.GlobalDeadLock='true';