IDENTIFY Request | Application Programming Reference | VantageCloud Lake - IDENTIFY

IDENTIFY Request | Application Programming Reference | VantageCloud Lake - IDENTIFY - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

ft:locale

en-US

ft:lastEdition

2024-12-11

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

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 created. The IDENTIFY DATABASE or IDENTIFY USER request processes database and user names in the same manner.

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 cause error responses from IDENTIFY. If you use an internal session identifier as input to the IDENTIFY SESSION request, the request returns 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, the system returns 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:

Using Roles to Manage User Privileges
Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html

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 Data Dictionary Views).

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 run, the response contains the following sequence of parcel types.

Parcel Sequence	Parcel Number	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 following statement 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. 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.

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). The minimum response size is 32,000 bytes.

Number		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 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). The minimum response size is 32,000 bytes.

Number		Length	Body
Num	Name	Bytes	Field	Value
0001	Req	21	Request	IDENTIFY DATABASE or IDENTIFY USER
0003	Data	10	MonVerID	2
0003	Data	10	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 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). The minimum response size is 32,000 bytes.

Number		Length	Body
Num	Name	Bytes	Field	Value
0001	Req	18	Request	IDENTIFY TABLE
0003	Data	10	MonVerID	2
0003	Data	10	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 may 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 may 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 may 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 may 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 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 has stopped responding or is running slowly and you suspect a lock conflict.
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 preceding 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.

Teradata recommends using MONITOR SESSION, with IDENTIFY as a diagnostic tool.

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 typically 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, run the IDENTIFY request with:

HostId = 719
SessionNo = 1000

Or:

UserID = 10

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

ObjDBId = 12

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

ObjTId = 1097