SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

to 128 bytes in length if it is displayed with a SELECT statement in MXCI. The heading name is an
SQL identifier that can be delimited and its characters must be from the ISO88591 character set.
If you specify a heading that is identical to the column name, INVOKE and SHOWDDL do not
display that heading. If you specify NO HEADING for the column, the application uses the column
name as the default.
UPSHIFT Attribute
The optional UPSHIFT attribute of the character types directs NonStop SQL/MX to upshift characters
before storing them in a column. For more information, see the SQL/MX Reference Manual.
Defining Corresponding Columns in Different Tables
Data type cross-matching between tables is the responsibility of the database administrator. Make
sure that corresponding columns used in different tables are defined with the same characteristics:
data type, size, default values, and constraints.
For example, a part-number column defined in several tables should have the same definition. Do
not define one column as PIC X(4) and another as PIC 9(4). By using the same column definition,
you ensure that applications can perform join operations during data retrieval or predicate
comparisons.
Understanding Data Type Correspondence With Host Languages
For more information about data type correspondence between NonStop SQL/MX and the host
programming languages used for embedded SQL/MX, see the SQL/MX Programming Manual
for C and COBOL.
Creating Indexes for SQL/MX Tables
An index provides an alternate access path to a table that differs from the inherent access path
(clustering key). Indexes can improve application performance for data retrieval operations by
providing the optimizer with a greater choice of access paths.
Each row in an SQL/MX index contains:
• The columns specified in the CREATE INDEX statement.
• The clustering key of the underlying table.
An index name is an SQL identifier. Indexes have their own name space within the schema, so an
index name can be the same as a table or constraint name. However, no two indexes in a schema
can have the same name.
If an existing index includes the selection columns for an SQL/MX statement, the optimizer may
use the index as an access path to the data. For more information, see “Understanding and Planning
SQL/MX Tables” (page 29).
Rules for Creating and Accessing Indexes
An index does not have its own privilege settings. An index always has the same security as the
table it indexes, so only users authorized to access the table can access the index.
• To create an index, you must own the schema for the underlying table or be a super ID user.
• Whenever you perform an INSERT, UPDATE, or DELETE operation on an indexed table, you
access the index.
• Whenever you perform a SELECT operation on an indexed table, you can also access the
index. However, you cannot SELECT an index directly, independent of its table, unless you
use an index-only query plan or you force an index-only scan with CONTROL QUERY SHAPE.
For more information, see the SQL/MX Reference Manual.
For more information, see “Access Privileges for SQL/MX Database Objects” (page 79).
96 Creating an SQL/MX Database