DB2 Version 9.7 for Linux, UNIX, and Windows

MON_FORMAT_LOCK_NAME table function - format the internal lock name and return details

The MON_FORMAT_LOCK_NAME table function formats the internal lock name and returns details regarding the lock in a row-based format. Each returned row consists of a key-value pair relevant for that particular lock.

Note: If your database was created in Version 9.7 before Fix Pack 1, to run this routine you must have already run the db2updv97 command. If your database was created before Version 9.7, it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care of by the database migration). If you downgrade to Version 9.7, this routine will no longer work.

To get information about locks, use the MON_FORMAT_LOCK_NAME, MON_GET_LOCKS, and, MON_GET_APPL_LOCKWAIT table functions instead of the SNAPLOCKWAIT administrative view and SNAP_GET_LOCKWAIT table function, and the SNAPLOCK administrative view and SNAP_GET_LOCK table function, which are deprecated in Fixpack 1 of Version 9.7.


>>-MON_FORMAT_LOCK_NAME--(--lockname--)------------------------><

The schema is SYSPROC.

Table function parameters

lockname: An input argument of type VARCHAR(32) that specifies the internal binary name of the lock that is to be formatted. A NULL value results in error SQL0171N being returned.

Authorization

The following privilege is required:

EXECUTE privilege on the MON_FORMAT_LOCK_NAME table function

Examples

The internal lock name is returned in a variety of situations, such as being written to the db2diag log files, or as the value of the lock_name monitor element. The following example shows how to use the MON_FORMAT_LOCK_NAME table function to find out further information about the lock, in this case with a lock name of 0000000E00000000000B00C152.

SELECT SUBSTR(NAME,1,20) AS NAME,
       SUBSTR(VALUE,1,50) AS VALUE
       FROM
       TABLE( MON_FORMAT_LOCK_NAME('0000000E00000000000B00C152')) as LOCK

The following output is returned:

NAME                 VALUE
-------------------- -------------
LOCK_OBJECT_TYPE     ROW
ROWID                0
DATA_PARTITION_ID    49408
PAGEID               184549376
TBSP_NAME            SYSCATSPACE

  5 record(s) selected.

Information returned

Table 1. Information returned by the MON_FORMAT_LOCK_NAME table function
Column name	Data type	Description
NAME	VARCHAR(256)	Element of the lock name. See the next table for more details.
VALUE	VARCHAR(1024)	Value of the element.

Not all elements that make up the specified lock name are returned; only those key-value pairs that are relevant are returned.

The elements that can be returned are as follows:

Table 2. Monitor elements that can be returned
Element name	Description	Possible values or monitor element
LOCK_OBJECT_TYPE	The lock object type	lock_object_type - Lock object type For possible values, see "lock_object_type - Lock object type waited on monitor element".
DATA_PARTITION_ID	The identifier of the data partition for which information is returned. This element is only applicable to partitioned tables and partitioned indexes. When returning lock level information, a value of -1 represents a lock which controls access to the whole table.	data_partition_id - Data partition identifier
TBSP_NAME	The name of a table space	tablespace_name - Table space name
TABSCHEMA	The schema of the table	table_schema - Table schema name
TABNAME	The name of the table	table_name - Table name
ROWID	Row ID of the table	-
PAGEID	The page ID	-
WORKLOAD_NAME	Name of the workload	workload_name - Workload name
STORAGE_GRP_ID	The storage group ID	-
BUFFERPOOL_NAME	Name of the buffer pool	-
FED_SERVER_NAME	Name of the federation server	-
FED_USER_NAME	Name of the federation user mapping	-
SEQ_OPERATION	Operation requesting a sequence lock	Possible values are: AUTONOMIC_POLICIES CATALOG_ARRAY DESCRIBE INIT_EVMON INIT_PACKAGE INIT_AUDIT PACKAGE_CREATION INIT_ROUTINE_ID INIT_ROLE_ID TEMP_TBSPACE AUDIT_DDL VERSION_TIMES WLM TRUSTED_CTX INIT_TRUSTED_CTX STATIC_STMT USER_TEMP_TBSPACE
CONTAINER_ID	The container ID	container_id - Container identification monitor element
STMT_UID	The statement ID	-
PACKAGE_TOKEN	The package token	-
INTERNAL	Reserved for internal use	-