SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-92
Examples—CONTROL TABLE

Avoid having a process open multiple cursors on a table when any of the
cursors is used to update that table. If this is unavoidable, consider using
CONTROL TABLE SEQUENTIAL READ OFF.
Examples—CONTROL TABLE

This example sets SYNCDEPTH to 0 for the nonaudited table DEPT. You might
use such a directive before selecting and displaying all rows of the table, for
example, but not before a query that changes data.
CONTROL TABLE PERSNL.DEPT SYNCDEPTH 0;

This example requests buffering for sequential INSERT operations on table
CUSTOMER:
CONTROL TABLE SALES.CUSTOMER SEQUENTIAL INSERT ON;

This example forces SQL to choose a specific access plan for a query that
accesses a view created:
CREATE VIEW EMPDEPT AS
SELECT EMP_NAME, EMP_NO, E.DEPT_NO, DEPT_NAME, DEPT_LOCN
FROM EMPLOYEE E, DEPT D
WHERE E.DEPT_NO = D.DEPT_NO;
The CONTROL TABLE directives force a join sequence and access path for table
EMPLOYEE and SALARY, leaving SQL to choose the join sequence for table
DEPT. Because the query involves only three tables, the CONTROL TABLE
directives implicitly force DEPT to have a join sequence of 1.
CONTROL TABLE EMPDEPT AS ED BASETABLE E
ACCESS PATH INDEX IEMP
JOIN SEQUENCE 2;
CONTROL TABLE SALARY
ACCESS PATH PRIMARY
JOIN SEQUENCE 3;
SELECT * FROM EMPDEPT ED, SALARY SA
WHERE ED.EMP_NO = SA.EMP_NO
AND ED.DEPT_NAME = "Engineering";
The use of the correlation name ED for the view is not necessary but is included in
the first CONTROL TABLE directive to demonstrate that a correlation name can be
included for views. The qualification of “BASETABLE E” in the first CONTROL
TABLE directive is necessary, however; without it, the CONTROL TABLE options
would apply to both the EMPLOYEE and DEPT tables of the view EMPDEPT, and
an error would occur.

This example uses CONTROL TABLE to specify locks. The first directive requests
a table lock on table CUSTOMER. These statements lock and unlock the
nonaudited table DEPT, using CONTROL TABLE directives with the LOCK and