Backup and restore SQL mode for DB2 Universal database

Motivation

The SQL language provides the concept of a schema that is used to group all types of SQL objects. Schemas in the SQL language can be interpreted as directories in the file system. DB2 UDB for Linux, UNIX, and Windows well support the concept of SQL schema, isolate USER from current schema, and provide PATH for routine parsing.

Patterns are easy to use, but they are managed differently. Tasks such as renaming patterns, pattern-level backup and recovery, and replication of patterns are difficult because scripting must be done manually, and objects and their dependencies can be complex.

This article provides a process library with a simple interface that enables users to back up all objects in a given pattern to a file system, restore backups in another mode, and delete all objects in a given pattern.

Installation

After you download and extract the Backupschema.zip files that are included in this article, you get the following files:

Backupschema.sql

This file contains a number of DDL statements, such as the installation process and individual helper routines and tables. By default, the mode used by all objects is "ADMIN". If you want to change this pattern, simply modify the set schema and the set PATH statement at the beginning of the file and replace "ADMIN" with the schema name of your choice.

To execute this script, connect to the database as the DB2 administrator, and then execute DB2-TVF backupschema.sql.

These processes use Sysproc. Admin_cmd () Stored procedures are exported and use Sysproc. Db2load () stored procedures for loading. (Sysproc.admin_cmd () was introduced in DB2 V8.2.2 (FP 9). Therefore, the minimum version required is V8.2.2. ) Sysproc.admin_cmd () is executed under the fenced user ID and group specified by the sqllib\adm\.fenced owner. Therefore, the exported file has the same owner and group as sqllib\adm\.fenced. Therefore make sure that this user and group have write privileges to the specified directory, and that the user ID used to access the file has access. The simple solution is to change the owner of the sqllib\adm\.fenced to the instance owner. A more secure solution is to make these two IDs members of the same group, and then change the sqllib\adm\.fenced group ownership to this group only. This enables access to the resulting file through a group membership relationship.

Specification

Dropschema ()

This process deletes all the objects defined in the schema and eventually deletes the schema itself.

Parameters

The name of the pattern to be deleted. The SQL identifier is case sensitive. To delete the mode "MySchema", the schema name must be uppercase: ' MySchema '.

If an object in another schema relies on the pattern to be deleted, then the general rules for deleting objects are applied. Therefore, in most cases, the dependent objects will also be deleted or invalidated. However, some objects (such as SQL functions) implement RESTRICT semantics. If this is the case, then the process will fail.

The Dropschema process executes under transaction control, and it does not perform commits or fallback itself. Therefore, to persist the deletion of the schema, you must commit the delete operation. Similarly, if an error occurs, the caller of the procedure needs to decide whether to rollback the procedure.

Backupschema (,)

This procedure backs up all the object logic in the schema to the file system. The supported objects are:

tables, including statistical data. Range cluster tables (RCT), materialized query tables (MQTS), and types of tables are not supported.

View, but does not include a view of the type.

Functions, including source, SQL, and external functions. Note that the executable code for the external function is not backed up. The statistical data for the function is not backed up.

SQL procedures and external processes. Note that the executable code for the external procedure is not backed up.

Different data types. Note that structured data types are not supported.

Index, but does not include an index extension.

All constraints.

sequences, including their high water mark.

Comments on all objects supported.

Privileges on all objects that are supported.

This backup process can include the PATH, SCHEMA, and PREP options.

Federated objects (such as nickname) and database partitioning characteristics (DPF) are not supported.

Parameters

The case sensitive name of the pattern to be backed up.