NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-141
Examples—CREATE INDEX
•
The following example creates an index on a single column of the EMPLOYEE
table and specifies a maximum number of extents for the index.
CREATE INDEX EMPLOYE2 ON EMPLOYEE (JOBCODE)
CATALOG PERSNL MAXEXTENTS 200
WITH SHARED ACCESS NAME CR_IND_EMP2 COMMIT BY REQUEST;
...
CONTINUE CR_IND_EMP2 ONCOMMITERROR COMMIT BY REQUEST;
The WITH SHARED ACCESS option allows other processes to select, delete,
insert, and update records in the EMPLOYEE table during most of the operation;
without WITH SHARED ACCESS, other processes would be able to select from the
EMPLOYEE table only during the operation. The COMMIT BY REQUEST option
allows the user to control entry to the commit phase of the operation, which does
lock out other processes. The CONTINUE statement starts the commit phase,
directing SQL to return control to the user if a retryable error occurs during the
phase.
•
The following example improves the efficiency of queries on customers placing
orders by adding an index on the CUSTNUM column in the ORDERS table. It
specifies WITH SHARED ACCESS so that—as in the previous example—there is
no application downtime during most of the operation.
CREATE INDEX SALES.XORDCUS ON SALES.ORDERS (CUSTNUM)
CATALOG SALES
MAXEXTENTS 500
WITH SHARED ACCESS REPORT ON COMMIT BY REQUEST;
...
CONTINUE CREATE_INDEX;
Because the statement does not specify the NAME option of WITH SHARED
ACCESS, the operation name defaults to CREATE_INDEX. REPORT ON turns on
EMS reporting for the WITH SHARED ACCESS operation. COMMIT BY
REQUEST allows the user to control entry to the final phase of the operation, as
explained in the preceding example, but in this case, the CONTINUE statement that
starts the commit phase does not request user control if retryable errors occur. Any
errors in the final phase of the operation cause the entire operation to be rolled back.
•
The following example prevents the addition of duplicate employee names to the
employee table by creating a unique index on the LAST_NAME and
FIRST_NAME columns:
CREATE UNIQUE INDEX PERSNL.XEMPNAM
ON PERSNL.EMPLOYEE (LAST_NAME FIRST_NAME)
CATALOG PERSNL;
•
The following example creates a nonpartitioned index using a simple parallel sort
with subsorts. The DEFINEs set up a sort operation with four subsort processes. The