SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Utilities
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
5-4
Checking DDL Locks
Checking DDL Locks
Many utilities, for example, DUP, MODIFY, and POPULATE INDEX, lock both metadata
and user data during the operation. The following are three kinds of locks:

Transactional locks on metadata and user data rows
A utility runs in multiple TMF transactions, managed by the utility itself. If the
utility fails before completion, then TMF will back out the latest of those
transactions and then release the lock.

Non-transactional file locks on user data
Some utilities must hold shared locks on user data outside TMF transactions,
to prevent concurrent updates while the utility is reading the data. If the utility
operation fails before completion, such locks are automatically released.

Logical DDL Locks on metadata
Most utilities require that the definition of the object being worked on remains
stable for the duration of the utility operation. To ensure this, the utility inserts a
so-called 'DDL Lock' into metadata, which prevents other DDL and utility
operations from changing the definition of the object. If the utility operation fails
before completion, the DDL Lock remains intact.
If a utility operation fails before completion, you must recover the following using the
RECOVER utility:

Work done in TMF transactions that are internally committed by the utility.
For example, a MODIFY TABLE utility may fail at a point where the target
partition has been created but data movement has not yet started. Running
RECOVER with the CANCEL option will then remove the target partition.
Running RECOVER with the RESUME option performs the data movement to
the created target partition.

Removing the DDL Lock.
Using the MODIFY TABLE example, MODIFY inserts a DDL Lock for the
affected table. Running RECOVER removes the DDL Lock.
To find out whether a failed utility operation needs to be recovered, issue this query
from an MXCI prompt:
select substring(o.object_name from 1 for 40)
as object_name, o.object_type
from
nonstop_sqlmx_<system name>.system_schema.catsys c,
nonstop_sqlmx_<system name>.system_schema.schemata s,
<cat>.definition_schema_version_<version number>.objects
o,
<cat>.definition_schema_version_<version
number>.ddl_locks d
where c.cat_name = '<cat>'