SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—523723-004
10-13
Recovering a Failed MODIFY Request and
Resetting Flags
Recovering a Failed MODIFY Request and Resetting Flags
The UNRECLAIMEDSPACE (F) flag or the INCOMPLETE SQLDDL OPERATION (D)
flag can be left set when a MODIFY operation fails to complete successfully. If
possible, the MODIFY utility responds by invoking its own internal recovery procedure
for the purpose of completing the operation 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_1200;
--- 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_figaro.system_schema.schemata
where
schema_name = 'SALES' and
cat_uid =
(select cat_uid
from nonstop_sqlmx_figaro.system_schema.catsys