SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—544536-007
9-15
Adding SQL/MX Tables
The name for the new table must be unique among names of tables, views, SQL/MP
aliases, and procedures within its schema. You cannot specify a Guardian physical
location as the name of the table, although you can specify the names of the Guardian
files that will contain table data by using the LOCATION clause. For more information,
see SQL/MX Subvolume Naming Guidelines on page 7-3.
To create a table, you must own its schema or be the super ID. If you add a table
containing a column that corresponds to an existing column in another table, you
should define the new table’s column with the same characteristics as the existing
column. To create a constraint on the table that refers to a column in another table, you
must have REFERENCES privileges on that column and access to the table that
contains the column.
For more information, see the SQL/MX Reference Manual.
Steps for Adding an SQL/MX Table
1. Plan the column definitions, checking that the data type of any column that might
be necessary for join or predicate search operations matches the joined column.
2. Start an MXCI session. Enter a LOG command to initiate a log file for statements
and commands entered in this session. Keep the log for your records.
3. Make sure your new table name is unique among existing table, view, SQL/MP
alias, and SPJ names in the schema.
4. Determine a name for the table you wish to add.
5. Use the DISPLAY USE OF command to identify which user modules are
associated with this object. See the similarity check criteria in the SQL/MX
Programming Manual for C and COBOL to determine if your changes are likely to
cause similarly check to fail and force automatic recompilation. If they will, you
should SQL compile these modules after making the changes to avoid expensive
automatic recompilations at run time. SQL applications that are running while you
make these changes will still undergo automatic recompilation.
For information about explicit and automatic recompilation, see the SQL/MX
Programming Manual for C and COBOL. For information about using DISPLAY
USE OF, see Checking Module Dependencies with DISPLAY USE OF on
page 11-19 and the SQL/MX Reference Manual.
6. Query the system metadata to ensure the new table name is unique among
existing table, view, SQL/MP alias and SPJ names in the schema.
7. Enter the CREATE TABLE statement.
8. Use the GRANT statement to assign access privileges on the new table to
authorized users.
Note. SQL/MX tables must have a user-defined clustering key to have partitions. You can
partition any table or index provided its clustering key is not just system-generated SYSKEY.