IDENTIFY Request | Application Programming Reference | Teradata Vantage - 17.10 - IDENTIFY - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)
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

Input Data

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.

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.

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.

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.
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.

Monitor Privileges

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:

Usage Notes - IDENTIFY

Before using this request, see Impact of Object Name Length on PM/API Requests.

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 Teradata Vantage™ - Data Dictionary, B035-1092).

System Table Name IDENTIFY Option
DBC.SessionTbl IDENTIFY SESSION
DBC.DBase IDENTIFY DATABASE

IDENTIFY USER

DBC.TVM IDENTIFY TABLE

CLIv2 Response Parcels

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

Response

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, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.

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.

Sample Input - CLIv2 Request

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

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 database server using a user_id of 725.

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 database server using a table_id of 183351.

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

Sample Input - Teradata JDBC Driver Request

For an example of how the PM/API request, built in Java, appears when sent to the database server, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.

Sample Output

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.

Relationship Between IDENTIFY and MONITOR SESSION

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: Write Lock Blocking Session

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
     .
     .
     .
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