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

successfully or, if necessary, rolling it back to the known good state that existed at the start of the
operation.
If a flag remains set after a MODIFY request completes, this signifies that the table or index the
MODIFY request is acting upon contains unusable space. When the MODIFY request completes,
it starts an ORSERV process that reclaims the unused file space, reloads the file, and calls on DP2
to reset any UNRECLAIMEDSPACE or INCOMPLETE SQLDDL OPERATION flag that remains set.
While either flag remains set, any new MODIFY or DUP operation you attempt to perform on the
table or index will fail with error 20290 (operation still in progress). It might be possible to
successfully perform DML operations on the object, but all other operations will fail.
Verifying That a Flag Is Set in a Partition
To determine whether or not an UNRECLAIMEDSPACE or INCOMPLETE SQLDDL OPERATION
flag is set for any of a given table's or index's partitions, perform a SHOWLABEL or FUP INFO
command on each partition. If you use SHOWLABEL, you must provide Guardian names for
partitions because SHOWLABEL returns file label information for the primary partition only when
ANSI names are used.
Using RECOVER or FUP RELOAD to Recover a Failed MODIFY Operation and Reset the Flag
When you receive an error message in response to a request or some other indication that a
MODIFY operation has failed before completion, you can issue a query on the affected table or
index as shown. The query returns information you can use to recover a failed MODIFY request.
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select substring (o1.object_name from 1 for 15) as lock_name,
l.operation as util,
l.status,
substring (l.process_uid from 1 for 25) as process ID
from objects o, objects o1, ddl_locks l
where o.object_name = 'CUSTOMER'
and o.object_uid = l.base_object_uid
and l.object_uid = o1.object_uid
and o.schema_uid =
(select schema_uid
from nonstop_sqlmx_<sys name>.system_schema.schemata
where
schema_name = 'SALES' and
cat_uid =
(select cat_uid
from nonstop_sqlmx_<sys name>.system_schema.catsys
where cat_name = 'SAMDBCAT'
)
)
for read uncommitted access
order by l.object_uid;
PROCESS_ID
----------------
STATUS
-----------
UTIL
-----
LOCK_NAME
-----------------------
\DMR15.$:2:619:15659164IMCUSTOMER_LOCK
As shown in the example, the query can return DDL lock name and process ID information.
Depending on the information the query returns, perform one of these steps to recover a failed
utility operation and reset a flag:
• If the query returns no information, the MODIFY operation has completed. However, the
ORSERV process that MODIFY started has not completed. Determine which partition still has
an UNRECLAIMEDSPACE or INCOMPLETE SQLDDL OPERATION flag set (see “Verifying That
Using MODIFY to Manage Table and Index Partitions 187