Save and restore functions
The i5/OS® save and restore functions are used to save SQL objects to disk (save file) or to external media.
The saved versions can be restored onto any i5/OS operating system later. The save and restore functions allow an entire schema, selected objects, or only objects that have been changed since a given date and time to be saved. All information needed to restore an object to its previous state is saved. You can use the functions to recover from damage to individual tables by restoring the data with a previous version of the table or the entire schema.
When a program or service program that was created for an SQL procedure, an SQL function, or a sourced function is restored, it is automatically added to the SYSROUTINES and SYSPARMS catalogs, as long as a procedure or function does not already exist with the same signature and program name. SQL programs created in QSYS will not be created as SQL procedures when restored. Additionally, external programs or service programs that were referenced on a CREATE PROCEDURE or CREATE FUNCTION statement may contain the information required to register the routine in SYSROUTINES. If the information exists and the signature is unique, the functions or procedures will also be added to SYSROUTINES and SYSPARMS when restored.
When an SQL table is restored, the definitions for the SQL triggers that are defined for the table are also restored. The SQL trigger definitions are automatically added to the SYSTRIGGERS, SYSTRIGDEP, SYSTRIGCOL, and SYSTRIGUPD catalogs. The program object that is created from the SQL CREATE TRIGGER statement must also be saved and restored when the SQL table is saved and restored. The saving and restoring of the program object is not automated by the database manager. The precautions for self-referencing triggers should be reviewed when restoring SQL tables to a new library.
When an *SQLUDT object is restored for a user-defined type, the user-defined type is automatically added to the SYSTYPES catalog. The appropriate functions needed to cast between the user-defined type and the source type are also created, as long as the type and functions do not already exist.
When a *DTAARA for a sequence is restored, the sequence is automatically added to the SYSSEQUENCES catalog. If the catalog is not successfully updated, the *DTAARA will be modified so it cannot be used as a sequence and an SQL9020 informational message will be output in the job log.
Either a distributed SQL program or its associated SQL package can be saved and restored to any number of systems. This allows any number of copies of the SQL programs on different systems to access the same SQL package on the same application server. This also allows a single distributed SQL program to connect to any number of application servers that have the SQL package restored (CRTSQLPKG can also be used). SQL packages cannot be restored to a different library.
Restoring a schema to an existing library or to a schema that has a different name does not restore the journal, journal receivers, or IDDU dictionary (if one exists). If the schema is restored to a schema with a different name, the catalog views in that schema will only reflect objects in the old schema. The catalog views in QSYS2, however, will appropriately reflect all objects.
Parent topic:
Data integrity