SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-10
Utilities
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>'
and c.cat_uid = s.cat_uid
and s.schema_name = '<schema>'
and s.schema_uid = o.schema_uid
and o.object_name = '<table name>'
and o.object_type = 'BT'
and d.base_object_uid = o.object_uid;
You can use this query for indexes, replacing <table name> with <index name>
and 'BT' with 'IX'.
You can use this query for sequence generators, replacing <table name> with
<sequence generator name> and 'BT' with 'SG'.
A typical output of this query is:
OBJECT_NAME OBJECT_TYPE
---------------------------------------- -----------
EMPLOYEES BT
--- 1 row(s) selected.
>>
In this example, EMPLOYEES is the name of the object. You need to run the
RECOVER utility.
If the query does not return rows, the failed partition operation has rolled back
completely. You do not need to perform recovery.