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

NOTE: Unlike the DROP statement, PURGEDATA does not remove the file label.
For more information, see “Using PURGEDATA to Delete Data From Tables” (page 207)
Dropping Schemas
Use the DROP SCHEMA statement to drop a schema.
To drop a schema successfully:
• It must be empty unless you use the CASCADE option.
• You must own the schema or be the super ID.
• You must have remote passwords for any nodes to which the catalog of the schema has been
registered.
The RESTRICT option, by default, will not allow a schema to be dropped if the schema is not empty
of specifications for dependent objects. However, the CASCADE option, if specified, will drop
both the schema and any such dependent objects.
If the schema is fairly large and contains many rows, DROP SCHEMA with the CASCADE might
fail with file system error 35, “Unable to obtain an I/O process control block, or the transaction
or open lock unit limit has been reached.” In this case, too many locks were requested. When this
occurs, you need to update MAXLOCKSPERTCB to 10000 or more.
For more information, see the SQL/MX Reference Manual.
Steps for Dropping a Schema
1. 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.
2. Determine the name of the schema that you wish to drop.
3. 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 more information about explicit and automatic recompilation, see the SQL/MX Programming
Manual for C and COBOL. For more information about using DISPLAY USE OF, see “Checking
Module Dependencies with DISPLAY USE OF” (page 223) and the SQL/MX Reference Manual.
4. Enter the DROP SCHEMA statement.
5. 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.
For more information, see the SQL/MX Reference Manual.
Dropping SQL/MP Aliases
Use the DROP SQLMP ALIAS statement to drop an SQL/MP alias from a schema (remove its
mapping from an SQL/MX ANSI name to a physical SQL/MP file name).
The DROP SQLMP ALIAS statement does not cause the underlying SQL/MP object to be dropped.
Similarly, dropping an underlying SQL/MP object does not cause any SQL/MP aliases to be
dropped. Those aliases remain unchanged and orphaned.
For more information, see the SQL/MX Reference Manual.
Dropping Objects From an SQL/MX Database 171