NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-89
CONVERT Command
path, join sequence, and join method for future SELECT operations to SQL. The
example uses table EMPLOYEE with a primary key of EMP_NO and an alternate
index, IEMP, on column DEPT_NO.
CONTROL TABLE EMPLOYEE AS E1 ACCESS PATH INDEX IEMP
JOIN SEQUENCE 1;
CONTROL TABLE EMPLOYEE AS E2 ACCESS PATH PRIMARY
JOIN SEQUENCE 2
JOIN METHOD NESTED;
SELECT E1.EMP_NAME, E1.DEPT_NAME, E2.EMP_NAME, E2.DEPT_NAME
FROM EMPLOYEE E1, EMPLOYEE E2
WHERE E1.DEPT_NO = "9999" AND E1.MGR_ENO = E2.EMP_NO;
CONTROL TABLE * ACCESS PATH SYSTEM
JOIN SEQUENCE SYSTEM
JOIN METHOD SYSTEM;
The CONTROL TABLE directives that precede the SELECT force SQL to use two
different access paths to access two different instances of table EMPLOYEE (IEMP
for E1, and primary access for E2). The statements also force SQL to use a specified
join order for processing the data, and to use a nested join method to join the second
table (E2) with the first (E1).
•
The following example forces MDAM with 3 key columns for table T1:
CONTROL TABLE T1 ACCESS PATH PRIMARY MDAM ON USE 3 COLUMNS;
•
The following example cancels all previously set CONTROL TABLE options and
uses only the default values for the options:
CONTROL TABLE *;
CONVERT Command
CONVERT is an SQLCI utility that creates an EDIT file containing SQL commands to
convert an Enscribe file described in a Data Definition Language (DDL) dictionary to an
SQL table described in a specific catalog. You can invoke the EDIT file commands by
using the OBEY command to create the following:
•
A table containing rows that correspond to the record definition of the Enscribe file
and columns that correspond to the fields in each record.
•
Indexes on the table that correspond to the alternate keys associated with the
Enscribe file.