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.

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.

Authorization

One of the following authorities is required to execute the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

None

Syntax

Read syntax diagramSkip visual syntax diagramMON_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.

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 following 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 lock_object_type - Lock object type waited on monitor element lock_object_type - Lock object type

For possible values, see lock_object_type - Lock object type waited on monitor element.
DATA_PARTITION_ID data_partition_id - Data partition identifier monitor element data_partition_id - Data partition identifier
TBSP_NAME The name of a table space tablespace_name - Table space name
TABSCHEMA table_schema - Table schema name monitor element table_schema - Table schema name
TABNAME table_name - Table name monitor element table_name - Table name
ROWID Row ID of the table -
PAGEID The page ID -
WORKLOAD_NAME workload_name - Workload name monitor element 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:
  • AUDIT_DDL
  • AUTONOMIC_POLICIES
  • CATALOG_ARRAY
  • CATALOG_COL_DICT_STATE
  • DESCRIBE
  • INIT_AUDIT
  • INIT_EVMON
  • INIT_PACKAGE
  • INIT_ROLE_ID
  • INIT_ROUTINE_ID
  • INIT_TRUSTED_CTX
  • PACKAGE_CREATION
  • STATIC_STMT
  • TEMP_TBSPACE
  • TRUSTED_CTX
  • USER_TEMP_TBSPACE
  • VERSION_TIMES
  • WLM
CONTAINER_ID container_id - Container identification monitor element -
STMT_UID The statement ID -
PACKAGE_TOKEN The package token -
INTERNAL Reserved for internal use -

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.