16.10 - Shredding the Data in the DBQLXMLLockTbl - 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)
To shred the lock plan data in the XMLTextInfo field and see details of a lock contention, follow this procedure:
  1. 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...
  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.
    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.
    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 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.
  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';