SQL/MX 2.x Installation and Management Guide (H06.04+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—540436-001
10-14
Recovering a Failed MODIFY Request and
Resetting Flags
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
where cat_name = 'SAMDBCAT'
)
)
for read uncommitted access
order by l.object_uid;
LOCK_NAME UTIL STATUS PROCESS_ID
----------------------- ----- ----------- ----------------
CUSTOMER_77179541_2217 PI 4 \FIGARO.$:3:321:2235264
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 a Flag Is Set in a Partition
on
page 10-13) and perform a FUP STATUS on the partition, using its Guardian
name, to determine whether or not the reload process is running:
°
If the reload operation is still running, wait until it completes.
°
If the reload operation is not running, perform a FUP RELOAD on the partition
that has the UNRECLAIMEDSPACE or INCOMPLETE SQLDDL OPERATION
flag set.
•
If the query returns a process ID for the MXCMP process associated with the
MODIFY operation, run the TACL command STATUS process-name to determine
the current state of the process ID (running or not running) and the type of the DDL
lock returned by the query (active or inactive).
°
If the STATUS process-name command shows that the process is still
running, the returned lock information is for an active DDL lock. Wait until the
MODIFY operation completes. If the MODIFY operation starts an ORSERV