Shredding the Lock Plan Information in the XML Lock Log Table, DBQLXMLLockTbl - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ - Database Administration

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
tgx1512080410608.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
ujp1472240543947
Product Category
Software
Teradata Vantage

You can shred (format as a table) the lock plan information in the XMLTextInfo column of DBQLXMLLockTbl 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>
You cannot shred the contents of the XMLTextInfo column in QryLockLogXMLV. To shred this column, you must use the underlying table, DBQLXMLockTbl.