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

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—523723-004
9-15
Adding Stored Procedures in Java (SPJs)
Adding Stored Procedures in Java (SPJs)
Use the CREATE PROCEDURE statement to add an SPJ to your SQL/MX database.
For information and examples, see the SQL/MX Guide to Stored Procedures in Java.
Adding Tables
Use the CREATE TABLE statement to add a table to your SQL/MX database.
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-2.
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 a 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 and the SQL/MX Programming Manual for
Java 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.
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.