15.10 - QryLockLogXMLV - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Query

DBC

 

View Column

Data Type

Format

Source Table.Column

ProcID

DECIMAL(5,0)

NOT NULL

-(5)9

DBQLXMLLockTbl.ProcID

CollectTimeStamp

TIMESTAMP(6)

NOT NULL

YYYY-MM-DDBHH:MI:SS

DBQLXMLLockTbl.CollectTimeStamp

QueryID

DECIMAL(18,0)

NOT NULL

--Z(17)9

DBQLXMLLockTbl.QueryID

XMLRowNo

INTEGER

NOT NULL

--,---,---,--9

DBQLXMLLockTbl.XMLRowNo

XMLTextInfo

CLOB(1048576)

UNICODE

NOT NULL

X(31000)

DBQLXMLLockTbl.XMLTextInfo

Access to this view is granted based on the security policy of your site. The data from the QryLockLogXMLV view comes directly from the DBQLXMLLockTbl table. The DBQLXMLLockTbl table logs lock contentions in XML format. The XMLTextInfo column stores the XML data that needs to be shredded.

The XML shredding feature is enabled by default in Teradata Database. Using Teradata client software, such as BTEQ or Teradata Viewpoint, you can call the SP_LockLog_Shredder stored procedure to shred the XML data in the DBQLXMLLockTbl table.

The lock logger displays lock objects as follows.

 

LockObjectRequested

Description

D

Database

T

Table

R

Row hash

TP

Table Partition range

RP

RowHash in Partition range

RK

RowHash in one partition

RN

RowKey range

The following statement retrieves all database lock contentions in XML format from the DBC.QryXMLLockTbl table:

sel xmltextinfo from DBC.QryLockLogXMLV;

The following is a partial listing of the results from the select statement. Note, the entries show examples of partition level locks and various lock objects (LockObjectRequested).

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>
                                         .
<?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="307181340665573876" CollectTimeStamp="2014-09-07T23:27:07.16" DelayStartTime="2014-09-07T23:24:38.82" AbortFlag="FALSE" TransactionCount="0"> <ContentionData  LockDelay="3897" LocalDeadLock="false" GlobalDeadLock="false" MultipleBlocker="false" vproc="1"> <BlockerRef> <RequestRef> <RequestID RequestID_1="0" RequestID_2="11"/> <Step StepLevel="0" StepNum_1="1" StepNum_2="0" StatementNo="1"/> <Transaction unique_1="0" unique_2="10590" vproc="30718" TransactionState="Active" TransactionStartTime="2014-09-07T23:24:01.70" /> </RequestRef> <SessionRef  LogicalHostId="1"> <Session SessionID_1="0" SessionID_2="1140" SessionID_Combined="1140"/> <User UserName="DBC" AccountName="DBC"/> </SessionRef> <LockData  LockTypeRequested="Write"  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="1139" SessionID_Combined="1139"/> </SessionRef> <LockData  LockTypeRequested="Read"  LockObjectRequested="R"  LockKind="RowHash on All Partitions" StartPartition="0"  StartRowHashInt="4294967295"  EndPartition="0"  EndRowHashInt="4294967295"  > </LockData> </BlockedRef> </ContentionData> </LockContention> </DBQLLockXML>
                                         .
<?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="307191340665578485" CollectTimeStamp="2014-09-08T05:07:37.48" DelayStartTime="2014-09-08T05:05:41.41" AbortFlag="FALSE" TransactionCount="0"> <ContentionData  LockDelay="1135" LocalDeadLock="false" GlobalDeadLock="false" MultipleBlocker="false" vproc="1"> <BlockerRef> <RequestRef> <RequestID RequestID_1="0" RequestID_2="13"/> <Step StepLevel="0" StepNum_1="1" StepNum_2="0" StatementNo="1"/> <Transaction unique_1="1" unique_2="26568" vproc="30719" TransactionState="Active" TransactionStartTime="2014-09-08T05:05:25.41" /> </RequestRef> <SessionRef  LogicalHostId="1"> <Session SessionID_1="0" SessionID_2="1142" SessionID_Combined="1142"/> <User UserName="DBC" AccountName="DBC"/> </SessionRef> <LockData  LockTypeRequested="Write"  LockObjectRequested="TP"  LockKind="Partition Range with complete RowHash Range" > </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="1143" SessionID_Combined="1143"/> </SessionRef> <LockData  LockTypeRequested="Write"  LockObjectRequested="RP"  LockKind="RowHash and Partition Range" StartPartition="3"  StartRowHashInt="4294967295"  EndPartition="3"  EndRowHashInt="4294967295"  > </LockData> </BlockedRef> </ContentionData> </LockContention> </DBQLLockXML>
                                         .
                                         .
 

For more information about ...

You can use ...

the DBQLXMLLockTbl table

Teradata Studio or Teradata Studio Express to view the DBQLXMLLockTbl table and columns or see Database Administration.

monitoring and displaying lock information

the Lock Viewer portlet in Teradata Viewpoint.