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

Backing Up and Restoring SPJ Metadata Information
Information about an SPJ is stored in the SQL/MX metadata tables. When you back up a catalog
or schema, Backup and Restore 2 automatically backs up the SQL/MX metadata for all SPJs in
the catalog or schema.
Backup and Restore 2 does not automatically restore SPJ metadata information. To restore it, run
the OBEY command file for the SPJ generated by the RESTORE SHOWDDL ON command. For
more information, see “Using the RESTORE SHOWDDL ON Option to Re-Create Objects” (page 67).
Backing Up and Restoring SPJ Class or JAR Files
An SPJ consists of a Java method within a Java class or JAR file stored in an OSS directory. To
back up class or JAR files, use OSS backup objects to specify their OSS directory locations:
BR> BACKUP $tape-drive, OSS (/usr/jarfiles, /usr/spjclasses);
To restore class or JAR files, use OSS restore objects to specify the original OSS directory locations
and optional target locations under which the original directories are restored:
BR> RESTORE $tape-drive, OSS ((/usr/jarfiles, TARGET /newdir),
(/usr/spjclasses, TGT /newdir));
For more information about backing up and restoring OSS objects, see the Backup and Restore 2
Manual.
Using the RESTORE SHOWDDL ON Option to Re-Create Objects
Every backup operation captures the DDL from all SQL/MX CREATE statements that have been
executed. This capture always occurs; there is no backup job option to prevent it.
When the SHOWDDL option of the RESTORE command is turned ON (the default is OFF), the
restore operation automatically generates an OSS EDIT file that corresponds to each CREATE
statement that has been executed. The information that is used to generate OSS EDIT files during
the restore operation is collected during the backup operation.
OSS EDIT files can be edited and used as MXCI OBEY command files to regenerate these SQX/MX
objects that Backup and Restore 2 does not restore:
• REFERENTIAL INTEGRITY constraints
• SPJs
• Triggers
• Views
The formats for naming OSS EDIT files generated by SHOWDDL are:
For Object TypeSHOWDDL File Name
REFERENTIAL INTEGRITY constraintSHOWDDL1_RI_Constraints
SPJSHOWDDL1_Stored_Procedures
TriggerSHOWDDL1_Triggers
ViewSHOWDDL1_Views
In the file name, the number after SHOWDDL identifies the number of OSS EDIT files that have
been created for those objects. For example, when the first file, SHOWDDL1_Stored_Procedures,
is full, Backup and Restore 2 generates a new file with the number 2,
SHOWDDL2_Stored_Procedures, into which it places the remaining SHOWDDL information.
This process of creating new OSS EDIT files continues until all SHOWDDL information for those
objects has been written to disk.
The OSS EDIT files are overwritten with every new execution of Backup and Restore 2 where the
SHOWDDL option is set to ON, and the same location for the files is specified.
Using Backup and Restore 2 for SQL/MX Database Recovery 67