You can shred (format as a table) the lock plan information in the XMLTextInfo column to be able to query it and read it. Without shredding, output from that column typically looks like this:
sel xmltextinfo from dbc.dbqlxmllocktbl;
*** Query completed. 7 rows found. One column returned.
*** Total elapsed time was 1 second.
XMLTextInfo
<?xml version="1.0" encoding="UTF-8"?> <!--XML row for DBQLLockXML--> <DBQLLockXML xsi:schemaLocation="http://schemas.teradata.com/dbqllockplan dbqllockplan.xsd" xmlns="http://schemas.teradata.com/dbqllockplan" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <LockContention QueryID="307191340665578443" CollectTimeStamp="2014-09-07T23:27:07.16" DelayStartTime="2014-09-07T23:24:01.70" AbortFlag="FALSE" TransactionCount="0"> <ContentionData LockDelay="1438" LocalDeadLock="false" GlobalDeadLock="false" MultipleBlocker="false" vproc="1"> <BlockerRef> <RequestRef> <RequestID RequestID_1="0" RequestID_2="9"/> <Step StepLevel="0" StepNum_1="1" StepNum_2="0" StatementNo="1"/> <Transaction unique_1="1" unique_2="26210" vproc="30719" TransactionState="Active" TransactionStartTime="2014-09-07T23:23:07.2" /> </RequestRef> <SessionRef LogicalHostId="1"> <Session SessionID_1="0" SessionID_2="1139" SessionID_Combined="1139"/> <User UserName="DBC" AccountName="DBC"/> </SessionRef> <LockData LockTypeRequested="Read" LockObjectRequested="R" LockKind="RowHash on All Partitions" > </LockData> <LockObject DatabaseName="MAM" TableName="t1"/> <Job> <Message MessageClass="21" MessageKind="8" MessageClassName="SYSMSGS2SCLASS" MessageKindName="TNT"/> <Operation WorkLevel="0" OperationType="TermiNate Transaction (Spawned StpEDT)" JobType="AmpStep"/> </Job> </BlockerRef> <BlockedRef> <RequestRef> </RequestRef> <SessionRef LogicalHostId="1"> <Session SessionID_1="0" SessionID_2="1140" SessionID_Combined="1140"/> </SessionRef> <LockData LockTypeRequested="Write" LockObjectRequested="R" LockKind="RowHash on All Partitions" StartPartition="0" StartRowHashInt="4294967295" EndPartition="0" EndRowHashInt="4294967295" > </LockData> </BlockedRef> </ContentionData> </LockContention> </DBQLLockXML>
To shred the lock plan data in the XMLTextInfo field and see details of a lock contention, follow this procedure:
1 Ensure 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...
2 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;
3 Grant privileges to the new user locklogshredder and log on as locklogshredder.
Note: 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;
4 Drop the target tables in case they remain from a previous shredding operation.
Note: 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;
5 Prepare the output to be formatted as needed using BTEQ.
6 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);
7 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 in 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.
8 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;
9 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;
10 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 ?
11 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
12 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';