15.10 - IDENTIFY - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

Returns information on the locks blocking a session:

  • Name of the user associated with a session
  • User name of an object
  • Database name of an object
  • Name of a table
  •  

    Element

    Data Type

     

    Description

    IndByte

    BYTE

    Indicator bits that specify which fields to treat as NULL if you are using indicator mode.

    Each bit in the byte corresponds to one field in the input data.

    If data is supplied for that field, set the bit to zero.

    If the data for that field is NULL (that is, there is no data supplied for that field), set the bit to 1.

    Note: The IndByte field is only required if the CLIv2 request is submitted in indicator mode.

    mon_ver_id

    SMALLINT, NOT NULL

    MONITOR software version ID. This can be version 2 or later.

    For a general explanation of monitor version choices, see “MONITOR VERSION” on page 162.

    host_id

    SMALLINT

    Logical ID of a host (or client). host_id cannot exceed 1023. A host_id of zero identifies the system console ID of the host. A combination of host_id and session_no identifies a user causing a block.

    session_no

    INTEGER

    Session number. A combination of host_id and session_no identifies a user causing a block.

    To identify the user involved in a lock conflict, include the Blk_x_HostId and Blk_x_SessNo returned in a MONITOR SESSION response as input in the USING Data String for the IDENTIFY SESSION request.

    database_id

    INTEGER

    ID of the database for this session.

    To identify the database name of the object involved in a block, include the Blk_x_ObjDBId returned in a MONITOR SESSION response as input in the USING Data String for the IDENTIFY DATABASE request.

    user_id

    INTEGER

    ID of the user for this session.

    To identify the user involved in a lock conflict, include the Blk_x_UserID field returned in a MONITOR SESSION response as input in the USING Data String for the IDENTIFY USER request.

    table_id

    INTEGER

    Unique ID of a table.

    To identify the table name of the object involved in a block, include the Blk_x_ObjTId returned in a MONITOR SESSION response as input in the USING Data String for the IDENTIFY TABLE request.

    Note: The database name and user name are assigned an associated identifier when they are created. The IDENTIFY DATABASE or IDENTIFY USER request processes database and user names in the same manner because the database name and user name are almost equivalent.

    Note: Because the Blk_x_HostId, Blk_x_SessNo, and Blk_x_UserID fields returned by a MONITOR SESSION request may either be NULL or identify an internal session, the data returned by a MONITOR SESSION request that you use as input for the IDENTIFY request can result in error responses from IDENTIFY. If you use an internal session identifier as input to the IDENTIFY SESSION request, it will return an error message. The same error message is returned if you submit the IDENTIFY SESSION request for the DBQL/Teradata dynamic workload management software artificial internal session.

    If you use an NULL UserID as input to the IDENTIFY DATABASE or IDENTIFY USER request, they system will return an error message.

    To use this request, you must have the MONSESSION privilege as part of your default role or this privilege must be granted directly to you.

    For more information on roles and privileges, see:

  • Database Administration
  • Security Administration
  • Teradata JDBC Driver User Guide
  • Before using this request, see “Impact of Object Name Length on PM/API Requests” on page 54.

    The following table describes the different IDENTIFY options.

     

    Option

    Description

    IDENTIFY DATABASE

    Identify a locked database.

    IDENTIFY SESSION

    Identify a user (by session) who is causing a block. You can use IDENTIFY SESSION with a combination of host_id and session_no, or you can use IDENTIFY USER.

    IDENTIFY TABLE

    Identify a locked table.

    IDENTIFY USER

    Identify a user who is causing a block. You can use IDENTIFY SESSION, or you can use IDENTIFY USER user_id.

    The following table lists the system table for each option. You can use Data Dictionary views to examine the information in each system table (see Data Dictionary).

     

    System Table Name

    IDENTIFY Option

    DBC.SessionTbl

    IDENTIFY SESSION

    DBC.DBase

    IDENTIFY DATABASE

    IDENTIFY USER

    DBC.TVM

    IDENTIFY TABLE

    Regardless of the form of the IDENTIFY request you execute, the response contains the following sequence of parcel types.

     

    Parcel Sequence

    Parcel Flavor

    Length (Bytes)

    Comments/Key Parcel Body Fields

    Success

    8

    18 to 273

    StatementNo = 2

    ActivityCount = 1

    ActivityType = 85 (PCLIDENTIFY)

    DataInfo

    71

    6 to 64100

    Optional; this parcel is present if request was IndicData parcel.

    Record

    10

  • 5 to 64100 (record mode)
  • 6 to 64100 (indicator mode)
  • Depending on request (Data or IndicData), data is in record or indicator mode. This record contains the user name with the specified user ID or database ID, user name logged on as the specified session, or table name with the specified table ID.

    EndStatement

    11

    6

    StatementNo = 2‑byte integer

    EndRequest

    12

    4

    None

    Note: The statement described below corresponds to a ResultSet returned by the Teradata JDBC Driver, and each of the fields correspond to a ResultSet column returned by the Teradata JDBC Driver. For more information on ResultSets, see Teradata JDBC Driver Reference.

    The Record returns the following field/column:

     

    Field/Column Name

    Data Type

    Description

    Name

    VARCHAR(128) CHARACTER SET UNICODE, NOT NULL

    Name of the object (for example, database, user, or table) whose identifier was supplied by the IDENTIFY request.

    The following example shows how the Request parcels for an IDENTIFY SESSION request, built by CLIv2, appear when sent to the Teradata Database server using a host_id of 348 and a session_no of 1000.

    Note: In this example, the size of the response buffer is set at the maximum (64,000 bytes), although you can set it to any size. However, a minimum response size is 32,000 bytes.

     

    Flavor

    Length

    Body

    Num

    Name

    Bytes

    Field

    Value

    0001

    Req

    20

    Request

    IDENTIFY SESSION

    0003

    Data

    12

    MonVerID

    2

     

     

     

    HostId

    348

     

     

     

    SessionNo

    1000

    0004

    Resp

    6

    BufferSize

    64000

    The following example shows how the Request parcels for an IDENTIFY USER request, built by CLIv2, look when they are sent to the Teradata Database server using a user_id of 725.

    Note: In this example, the size of the response buffer is set at the maximum (64,000 bytes), although you can set it to any size. However, a minimum response size is 32,000 bytes.

     

    Flavor

    Length

    Body

    Num

    Name

    Bytes

    Field

    Value

    0001

    Req

    21

    Request

    IDENTIFY DATABASE or IDENTIFY USER

    0003

    Data

    10

    MonVerID

    2

     

     

     

    UserID

    725

    0004

    Resp

    6

    BufferSize

    64000

    The next example shows how the Request parcels for an IDENTIFY TABLE request, built by CLIv2, look when they are sent to the Teradata Database server using a table_id of 183351.

    Note: In this example, the size of the response buffer is set at the maximum (64,000 bytes), although you can set it to any size. However, a minimum response size is 32,000 bytes.

     

    Flavor

    Length

    Body

    Num

    Name

    Bytes

    Field

    Value

    0001

    Req

    18

    Request

    IDENTIFY TABLE

    0003

    Data

    10

    MonVerID

    2

     

     

     

    TableId

    183351

    0004

    Resp

    6

    BufferSize

    64000

    For an example of how the PM/API request, built in Java, appears when sent to the Teradata Database server, see Teradata JDBC Driver Reference.

    With a host_id of 52 and a session_no of 31467, the IDENTIFY SESSION request might return the following values. These example values are returned in text character format. Your application program may return the values in a different format or display.

    Success parcel:
     StatementNo: 1    ActivityCount: 1
     ActivityType: 85    FieldCount: 1
    DataInfo parcel:
     FieldCount: 1
    Record parcel.
     Parcel flavor:        10    Parcel body length:   31
     Name = "DBC                           ".
    EndStatement.
    EndRequest.

    With a database_id of 1012, the IDENTIFY DATABASE request might return the following values. These example values are returned in text character format. Your application program may return the values in a different format or display.

    Success parcel:
     StatementNo: 1    ActivityCount: 1
     ActivityType: 85    FieldCount: 1
    DataInfo parcel:
     FieldCount: 1
    Record parcel.
     Parcel flavor:        10    Parcel body length:   31
     Name = "weekly                        ".
    EndStatement.
    EndRequest.

    With a user_id of 1012, the IDENTIFY USER request might return the following values. These example values are returned in text character format. Your application program may return the values in a different format or display.

    Success parcel:
     StatementNo: 1    ActivityCount: 1
     ActivityType: 85    FieldCount: 1
    DataInfo parcel:
     FieldCount: 1
    Record parcel.
     Parcel flavor:        10    Parcel body length:   31
     Name = "weekly                        ".
    EndStatement.
    EndRequest.

    With a table_id of 63, the IDENTIFY TABLE request might return the following values. These example values are returned in text character format. Your application program may return the values in a different format or display.

    Success parcel:
     StatementNo: 1    ActivityCount: 1
     ActivityType: 85    FieldCount: 1
    DataInfo parcel:
     FieldCount: 1
    Record parcel.
     Parcel flavor:        10    Parcel body length:   31
     Name = "EventLog                      ".
    EndStatement.
    EndRequest.

    PM/API can report on locks placed by any user or object with the MONITOR SESSION and IDENTIFY requests. The MONITOR SESSION request helps you identify the types of locks blocking a session.

    The MONITOR SESSION request:

  • Monitors the currently executing processes and reports blocks preventing sessions from doing useful work, for both application or utility locks causing the block.
  • Tells you not only which session is blocked and on what type of object but also who is holding the lock. You can trace a blocked session back to the object locked and display the owner of the lock if your job is hung or is running very slowly and you suspect there is a lock conflict involved.
  • On a query, reports the lock conflicts per session with the MoreBlocks field to indicate if there are more lock conflicts involved but not reported. The types of lock information returned for each session include:
  • User ID or user of host utility job causing a block
  • Type of object (for example, database, table, or row hash) causing a lock
  • Mode or severity of lock
  • Database identifier of object being locked
  • Table identifier of object being locked
  • By looking at the logical host ID of a session causing the block in combination with the session number of the session causing a block, you can uniquely identify the session that is causing a block.

    Although you have the above lock information, you must use the IDENTIFY request to further identify the locks as either a user name, database name, or table name. Use the Blk_ data values (or fields) returned in the MONITOR SESSION operation as input for an IDENTIFY request.

    MONITOR SESSION, with IDENTIFY, is a more powerful diagnostic tool than the Show Locks utility accessed through Database Window (DBW), which displays information about only client utility locks on an object and does not report who the lock is blocking.

    Example  

    The following example output shows that SessionNo 1001 is blocked by SessionNo 1000 by a WRITE lock that is granted on a table object type. Records are sorted in HostId and then SessionNo order.

    First Record:

    HostId = 719
         .
         .
    SessionNo = 1000
         .
         .
         .

    Second Record:

    HostId = 719
         .
         .
    SessionNo = 1001
         .
         .
    Blk-1-HostId = 719
    Blk-1-SessNo = 1000
    Blk-1-UserID = 10
    Blk-1-LMode = 'W'
    Blk-1-OType = 'T'
    Blk-1-ObjDBId = 12
    Blk-1-ObjTId = 1097
    Blk-1-Status = 'G'
    Blk-2-HostId = NULL
         .
         .
         .

    Note: When the Blk_2_ HostId, Blk_2_SessNo, and Blk_2_UserID values are returned as NULLs, this usually means that the blocking job is a HUT job that has logged off without releasing its lock.

    Because the data returned here does not tell you which user is causing the block or which table is locked, you must next use the IDENTIFY request with the output from your MONITOR SESSION to return that information.

    To identify the user causing the lock, execute the IDENTIFY request with:

    HostId = 719
    SessionNo = 1000

    Or:

    UserID = 10

    To identify a locked database, execute the IDENTIFY request with:

    ObjDBId = 12

    To identify a locked table, execute the IDENTIFY request with:

    ObjTId = 1097