NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
U-2
Examples—UNLOCK TABLE
Always follow an UNLOCK TABLE statement with a CONTROL TABLE
statement with the TABLELOCK ENABLE option, as shown in the examples. The
CONTROL TABLE statement provides information at compile time, unlike the
UNLOCK TABLE statement, which is in effect only at execution time.
When you specify the default value, TABLELOCK ENABLE, the executor can
determine at run time whether a table lock is necessary. This strategy increases
concurrency for statements that do not require a table lock, such as those statements
that need only a few row locks.
•
Host program considerations
A host program cannot execute an UNLOCK TABLE statement if the program has a
cursor open on the table or view with STABLE or REPEATABLE access. Close the
cursor with a CLOSE or FREE RESOURCES statement before you execute
UNLOCK TABLE statement.
Examples—UNLOCK TABLE
•
The following example locks and unlocks a nonaudited table from a SQLCI session
in which the AUTOWORK AUDITONLY is ON:
>> VOLUME $VOL1.PERSNL;
>> LOCK TABLE JOB IN EXCLUSIVE MODE;
--- SQL operation complete
>> DELETE FROM JOB WHERE JOBCODE
+> NOT IN (SELECT DISTINCT JOBCODE FROM EMPLOYEE);
--- 8 row(s) deleted.
...
>> UNLOCK TABLE JOB;
--- SQL operation complete
•
The following example locks and unlocks a nonaudited table from a program:
EXEC SQL
LOCK TABLE SALES.PARTS IN EXCLUSIVE MODE;
EXEC SQL
CONTROL TABLE SALES.PARTS TABLELOCK ON;
...
EXEC SQL
UNLOCK TABLE SALES.PARTS;
EXEC SQL
CONTROL TABLE SALES.PARTS TABLELOCK ENABLE;