16.10 - Shredding the Lock Plan Information in the XMLTextInfo Column - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

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>