SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

Adding Stored Procedures in Java (SPJs)
Use the CREATE PROCEDURE statement to add an SPJ to your SQL/MX database.
For more information, see the SQL/MX Guide to Stored Procedures in Java.
Adding SQL/MX 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 the “SQL/MX Subvolume Naming
Guidelines” (page 73).
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.
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.
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. 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.
6. Enter the CREATE TABLE statement.
7. Use the GRANT statement to assign access privileges on the new table to authorized users.
8. Revise the application source code as needed to reflect your changes to the database. Process
and compile the updated source file. For more information, see the SQL/MX Programming
Manual for C and COBOL.
9. Perform a TMF online dump. For a partitioned table, perform an online dump of each partition.
For more information, see “Creating SQL/MX Tables” (page 79).
Adding Triggers
To add a trigger to an SQL/MX table, use the CREATE TRIGGER statement.
User applications that change (INSERT, UPDATE, or DELETE) information in a table are automatically
recompiled when a trigger with a matching event is added or dropped. User applications that use
a SELECT on the subject table do not require recompilation. User applications require SQL
recompilations only when triggers are added, altered or dropped. No source code changes or
language compilations are required.
For more information, see the SQL/MX Reference Manual.
Adding Objects to an SQL/MX Database 159