SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-150
Examples—CREATE INDEX
the table and its index’s partitions must be cataloged in version 350 or later
catalogs for the CREATE INDEX catalog to succeed.
Examples—CREATE INDEX

This example creates an index on the LAST_NAME and FIRST_NAME columns of
table EMPLOYEE:
CREATE INDEX \SYS1.$VOL1.PERSNL.EMPLOYE0 ON
\SYS1.$VOL1.PERSNL.EMPLOYEE (LAST_NAME, FIRST_NAME) CATALOG
\SYS1.$VOL1.PERSNL;

This 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.

This 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.