Backup and restore SQL schemas for DB2 Universal Database
A library for logical schema-level backup, restore, copy, and drop
The SQL language provides the concept of a schema to group SQL objects of all kinds together. Schemas can be understood as the SQL equivalent of directories in a file system. DB2 UDB for Linux, UNIX, and Windows traditionally has had strong support for the SQL schema concept, having separated the USER from the CURRENT SCHEMA early on and providing a PATH for routine resolution.
While usage of schemas is easy, their management is a different matter. Requirements such as renaming a schema, schema-level backup and restore, and making a copy of a schema cause significant challenges, since they must be hand scripted, and the number of objects and their dependencies are complex.
This article provides a library of procedures with a simple interface that allow the user to back up all objects in a given schema onto the file system, restore the backup under a different schema, and also drop all objects in a given schema.
After downloading and extracting the backupschema.zip file included in this article, you will find the following file:
This file contains the DDL, which installs the procedures as well as various helper routines and tables. By default, the schema used for all objects is "ADMIN". If you want to change this schema, simply modify the
SET SCHEMAand the
SET PATHstatements at the beginning of this file to replace "ADMIN" with a schema name of your choice
To execute the script, connect to the database as the DB2 administrator and then execute
db2 -tvf backupschema.sql
The procedures use the
SYSPROC.ADMIN_CMD() stored procedure to
export and the
SYSPROC.DB2LOAD() stored procedure to load. (
SYSPROC.ADMIN_CMD() has been introduced in DB2 V8.2.2 (FP 9). Therefore, V8.2.2 is the minimum version requirement.)
SYSPROC.ADMIN_CMD() executes under the fenced user id and group
specified by the owner of the
As a result the exported files have the same owner and group as
Therefore it is important to ensure that the user or group has privileges to write to the specified
directories, while the user id expected to access the files also has access.
The simple solution is to change ownership of
sqllib\adm\.fenced to the
instance owner. The safer solution is to have both IDs as members of a shared group and change only
the group ownership of
sqllib\adm\.fenced to that shared group.
This way it is ensured that you can access the produced files through the group membership.
The procedure will drop all objects defined in the schema and finally drop the schema itself.
The name of the schema to be dropped. SQL identifiers are case-sensitive. That is to drop the schema "MYSCHEMA", the schema must be capitalized:
If an object in another schema depends on the schema to be dropped, the regular rules for dropping objects apply. That is, in most cases the dependent object will be dropped or invalidated as well. Some objects, such as SQL functions, however, enforce RESTRICT semantics. If such a case is encountered, the procedure will fail.
DROPSCHEMA procedure is under transaction control and does not perform commit or rollback
on its own. So to persist the dropping of the schema, the drop must be commited. Similarly, if an error occurs, the invoker
of the procedure needs to decide whether to roll back the process to that point or not.
The procedure performs a logical backup of all objects in the schema to the file system. Supported objects are:
- Tables including statistics. Not supported are range clustered tables (RCT), materialized query tables (MQT), and typed tables.
- Views with the exception of typed views
- Functions including sourced, SQL and external functions. Note that the executables for external functions are not backed up. Statistics for functions are not backed up.
- SQL Procedures and external procedures. Note that the executables for external procedures are not backed up.
- Distinct datatypes. Note that structured data types are not supported.
- Indexes, with the exception of index extensions
- All constraints
- Sequences, including their high watermark
- Comments on all supported objects
- Privileges for all supported objects
The backup includes the PATH, SCHEMA, and PREP options, where applicable.
Federated objects such as NICKNAMEs as well as the database partitioning feature (DPF) are not supported.
The case-sensitive name of the schema to be backed up.
The directory in the file system to which the objects are to be backed up. The directory must exist and end with a slash on UNIX or Linux and a backslash on Windows; for example, 'D:\TEMP\MYSCHEMA\'. If the directory is NULL or an empty string, no files will be exported. The procedure will only populate the
DDLLOGtable with the DDL statements. This mode is used by the
After a successful backup, the directory will contain IXF files for:
- Tables in the schema that was backed up
- SYSSTAT views containing the table, column, and index statistics
- The DDL log containing the infomation to recreate all objects.
The user is free to add additional files, such as executables for external routines, to the directory. Once complete, the user can zip and tar the directory, archive, or FTP as desired.
This procedure restores a schema that was previously backed up using
All objects are being created using the ID of the current user.
If any object to be restored references an object in another schema, and this object does not exist in the target database,
the procedure will fail. This procedure performs internal commits.
If the restore is done into a different schema than the schema used for
all SQL objects containing SQL bodies (such as views, SQL routines, and check constraints) must not have explicit
references to the source schema in their definitions. That is:
CHECK (MYSCHEMA.MYTABLE.C1 > 0)
cannot be restored successfully into a schema other than "MYSCHEMA" while
CHECK (MYTABLE.C1 > 0) can.
To enable "redirected"
RESTORESCHEMA, it is therefore recommended to exploit the
CURRENT PATH and CURRENT SCHEMA special registers instead of qualifying the local schema expclicitly when defining SQL objects.
The case-sensitive name of the schema into which the objects are to be restored. If the schema already exists, it is dropped first. If the schema is NULL or the empty string, the procedure restores the schema to the name from where it was backed up. This mode is used by the
A string containing tablespace information to be added to each table definition. The clause may contain the
LONG IN <lobtablespace>, and
INDEX IN <indextablespace>clauses. This parameter can be an empty string or NULL, if defaults should be picked.
'IN DATA8K INDEX IN INDEX4K'
The directory in the file system where the files generated by
BACKUPSCHEMAare located. The directory must end with a slash on UNIX or Linux and a backslash on Windows; for example, 'D:\TEMP\MYSCHEMA\'.
The directory in the filesystem to which the procedure will write load message files. The directory must end with a slash or backslash as apropriate for the operating system.
This procedure makes a copy of a schema within the same database.
The case-sensitive name of the new schema.
A string containing tablespace information to be added to each table definition. If NULL or '' is provided, defaults will be chosen.
The case-sensitive name of the schema to be copied.
This procedure rebinds all SQL procedures in a schema. The procedure should be called after new statistics have been collected for tables used in procedures within the schema. That is, the procedure will re-optimize all SQL statements used in the SQL Procedures.
The case-sensitive name of the schema.
This procedure call drops all objects in the schema
CALL ADMIN.BACKUPSCHEMA('UTIL', 'D:\TEMP\UTIL_SCHEMA\')
This procedure call stores all objects in the schema
"UTIL"in the directory
CALL ADMIN.RESTORESCHEMA(NULL, NULL, 'D:\TEMP\UTIL_SCHEMA\', 'D:\TEMP\')
This procedure call retrieves the objects from
D:\TEMP\UTIL_SCHEMA\and restores them to the original schema using default tablespaces. Messages from load are written to
CALL ADMIN.RESTORESCHEMA('SRIELAU', 'IN "DATA" INDEX IN "INDEX"', 'D:\TEMP\USER_SCHEMA\', 'D:\TEMP\')
This procedure call retrieves the objects from
D:\TEMP\USER_SCHEMA\and restores them to the
"SRIELAU"schema using the tablespace
"DATA"for table data and
"INDEX"for indices. Messages from LOAD are written to
CALL ADMIN.COPYSCHEMA('SRIELAU', 'IN "DATA" INDEX IN "INDEX" LONG IN "LONG"', 'STOLZE')
This procedure call duplicates all objects in the schema
"STOLZE"into the schema
"SRIELAU"schema using the tablespace
"DATA"for table data,
"INDEX"for indices, and
"LONG"for LOB data.
This procedure call rebinds all SQL procedures in the schema
SRIELAU, picking up new statistics.
This section gives a high-level overview on the implementation of the procedures discussed so far in this article.
The main challenge to a successful restoration of all objects in a schema is the handling of the many dependencies between different objects within the schema. For example, tables may depend on user-defined distinct types; check constraints depend on functions; views depend on views, aliases, and functions; and so on.
Since DB2 UDB cannot create an object that depends on a non-existing entity, the correct order of execution appears vital. Upon looking at the problem closer, you can see, however, that the depth of the dependency graph tends to be shallow, and furthermore that some objects are natural endpoints. That is, user-defined distinct types can never depend on any other DDL object, and tables (excluding constraints) depend only on distinct types. Having realized that, it is possible to implement a restore in three phases:
- Restore all distinct types followed by all tables with their indexes.
- Copy the data into the tables and get statisticts. This is done early to ensure execution plans for SQL procedures are correct.
- Restore all other objects in a loop. That is, don't worry if any given object creation fails as long as progress is being made per iteration through the list of objects to be restored. Only if the process stalls is an error returned.
With this basic algorithm for restore, it is easy to implement a simple infrastructure.
Here the table
DDLLOG is used by the procedure
to record the DDL statements.
The first row (with the number 0) contains the source schema. This is followed by the DDL statements for distinct types and then tables.
The end of this phase is marked by an empty row, which is followed by all other objects.
The table contains a
SUCCESS column, which is used by
to log the successful creation of an object.
The composition of the DDL objects is entirely based on the documented
SYSCAT catalog views,
with one exception. The high watermark for an
IDENTITY column needs to be retrieved from
SYSIBM.SYSSEQUENCES.LASTASSIGNVAL, which is not exposed in DB2 UDB V8.
The interaction with the filesystem is achieved through the
which supports export and is used to write the
DDLLOG table, the user data, and the statisticts
to files. To load
SYSPROC.DB2LOAD is used.
The same brute-force approach used to restore a schema is used to drop a schema. The
DROPSCHEMA procedure simply keeps attempting to drop objects
until it either fails to make progress, or all objects are dropped.
This article provides a group of powerful procedures that allow for schema-level operations such as logical backup, restore, and copy of all objects in a given schema. In addition to providing value to ISVs and end customers with this library, this article also shows that DB2 UDB has sufficient SQL APIs to allow for powerful added value using customer-accesible methods.
- Download a free trial version of DB2 Universal Database Enterprise Server Edition.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Read more articles by Serge Rielau.
- The DB2 UDB Information Center is the first place to go for more details about all aspects of the product.
- Visit the developerWorks DB2 UDB page to expand your DB2 UDB skills and access resources.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.