Using EXPLAIN to identify locks chosen by Db2

You can use Db2 EXPLAIN to determine what kind of locks Db2 uses to process an SQL statement.

To analyze the locks used by Db2:

1. Use the EXPLAIN statement, or the EXPLAIN option of the BIND and REBIND sub-commands, to determine which modes of table, partition, and table space locks Db2 initially assigns for an SQL statement.
   EXPLAIN stores the results in the PLAN_TABLE.
2. Query the PLAN_TABLE.
   Each PLAN_TABLE row describes the processing for a single table, either one named explicitly in the SQL statement that is being explained or an intermediate table that Db2 creates. The TSLOCKMODE column shows the initial lock mode for that table.

   The lock mode value applies to the table or the table space, depending on the value of LOCKSIZE and whether the table space is segmented or nonsegmented. For partitioned and universal table spaces, the lock mode applies only to locked partitions. Lock modes for LOB and XML table spaces are not reported with EXPLAIN.

   The following tables show the table or table space locks that Db2 chooses, and whether page or row locks are used also, for each particular combination of lock mode and size.

   Universal table spaces:

   EXPLAIN lock type	IS	S	IX	U	X
   Table space lock acquired is:	IS	S	IX	U	X
   Page or row locks acquired?	Yes	No	Yes	No	No
   Mass delete locks acquired?	No	No	No	No	No

   Non-segmented

   EXPLAIN lock type	IS	S	IX	U	X
   Table space lock acquired is:	IS	S	IX	U	X
   Page or row locks acquired?	Yes	No	Yes	No	No
   Mass delete locks acquired?	Yes	Yes	No	No	No

   Segmented table spaces with LOCKSIZE ANY, ROW, or PAGE

   EXPLAIN lock type	IS	S	IX	U	X
   Table space lock acquired is:	IS	IS	IX	n/a	IX
   Table lock acquired is:	IS	S	IX	n/a	X
   Page or row locks acquired?	Yes	No	Yes	n/a	No
   Mass delete locks acquired?	Yes	Yes	No	No	No

   Segmented table spaces with LOCKSIZE TABLE

   EXPLAIN lock type	IS	S	IX	U	X
   Table space lock acquired is:	n/a	IS	n/a	IX	IX
   Table lock acquired is:	n/a	S	n/a	U	X
   Page or row locks acquired?	n/a	No	n/a	No	No
   Mass delete locks acquired?	Yes	Yes	No	No	No

   Segmented table spaces with LOCKSIZE TABLESPACE

   EXPLAIN lock type	IS	S	IX	U	X
   Table space lock acquired is:	n/a	S	n/a	U	X
   Table lock acquired is:	n/a	n/a	n/a	n/a	n/a
   Page or row locks acquired?	n/a	No	n/a	No	No
   Mass delete locks acquired?	Yes	Yes	No	No	No