DB2 Version 9.7 for Linux, UNIX, and Windows

Database schema transporting

Transporting a database schema involves taking a backup image of a database and restoring the database schema to a different, existing database. When you transport a database schema, the database objects in the transported schema are re-created to reference the new database, and the data is restored to the new database.

A database schema must be transported in its entirety. If a table space contains both the schema you want to transport, as well as another schema, you must transport all data objects from both schemas. These sets of schemas that have no references to other database schemas are called transportable sets. The data in the table spaces and logical objects in the schemas in a transportable set reference only table spaces and schemas in the transportable set. For example, tables have table dependencies only on other tables in the transportable set.

During the process of transporting table spaces, a buffer pool from the target database should be assigned to every transported table space. DB2® uses the following algorithm to find suitable buffer pools:

If the page size of the transport table space matches the page size of default buffer pool of target database, the default buffer pool is assigned to the transport table space.
If the transport table space page size is different from the page size of the default buffer pool, an attempt is made to compare the buffer pool ID of a transport table space with a buffer pool of the same ID and page size on the target database.
- If a match is found, that buffer pool is assigned to the table space.
- If a match is not found, the first buffer pool with an equivalent size is used.
- When no matches are available, a hidden buffer pool of the matching page size is assigned as a last resort.

It is strongly recommended to check the buffer pools that are assigned to transported table spaces after transport. The statement SELECT SUBSTR(TBSPACE,1,15), BUFFERPOOLID FROM SYSCAT.TABLESPACES can be used to list the assigned buffer pools to a table space.

If necessary, issue the ALTER TABLESPACE command to update the buffer pool.

The following diagram illustrates a database with several table spaces and schemas. In the diagram, the table spaces that are referenced by the schemas are above the schemas. Some schemas reference multiple table spaces and some table spaces are referenced by multiple schemas.

Figure 1. Sets of table spaces and schemas

Diagram showing valid sets of schemas that can be transported

The following combinations of table spaces and schemas are valid transportable sets:

tablespace1 with schema1 and schema2
tablespace2 and tablespace3 with schema3
tablespace4, tablespace5, and tablespace6, with schema4 and schema5
A combination of valid transportable sets also constitutes a valid transportable set:
- tablespace1, tablespace2, and tablespace3, with schema1, schema2, and schema3

The set tablespace4 and tablespace5 with schema4 is not a valid transportable set because there are references between tablespace5 and schema5 and between schema5 and tablespace6. The set requires tablespace6 with schema5 to be a valid transportable set.

You can transport database schemas by using the RESTORE command with the TRANSPORT option.

When database schemas are transported, a temporary database is created and named as a part of the transport operation. The transport staging database is used to extract logical objects from the backup image so that they can be re-created on the target database. If logs are included in the backup image, they are also used to bring the staging database to a point of transactional consistency. The ownership of the transported table spaces is then transferred to the target database.

Note: The TRANSPORT option is not available on a partitioned database environment.

Considerations about the database objects re-created when transporting database schemas

Review the following information related to the re-creation of database objects when transporting database schemas:

Database object	Consideration when transporting schemas
SQL routines (not external routines using SQL)	A new copy of the SQL routine is created in the target database. For SQL stored procedures, additional catalog space is consumed because an additional copy of the stored procedure byte code is created in the new database.
External routines	A new catalog entry is created for each routine. This catalog entry references the same binary file as the original source routine. The RESTORE command does not copy the external routine binary file from the source system.
Source tables in states causing access problems	For tables that are not in normal state at the time the backup image was generated, such as tables in check pending state or load pending state, the data from those tables might not be accessible in the target database. To avoid this, you can move the tables to normal state in the source database before schema transport.
Tables containing the data capture attribute	Source tables with data capture enabled are transported to the target database with the data capture attribute and continue to log interdatabase data replication information. However, replicated tables do not extract information from this table. The user has the option of registering the new target table to act as a replication source after the RESTORE command completes.
Tables using label-based access control (LBAC)	During the transport of data that is protected by LBAC, the transport operation re-creates the LBAC objects on the target database. If LBAC objects of the same name exist on the target database, the transport operation fails. To ensure that restricted data access is not compromised, the transport operation does not use existing LBAC objects on the target database.
Temporary table spaces	If there are any system temporary table spaces that are defined with the source backup image and the transport operation excludes them from the table space list, these system temporary table spaces are still created in the staging database but not the final target database. As a result, you need to issue the SET TABLESPACE CONTAINERS command for these system temporary table spaces in order to provide valid containers to complete the restore operation, just as you would for any table spaces that are specified within the table space list.

When you transport table spaces, a log record with a special format is created on the target database. This format cannot be read by previous DB2 versions. If you transport table spaces and then downgrade to a version earlier than DB2 Version 9.7 Fix Pack 2, then you cannot recover the target database that contains the table spaces that were transported. To ensure that the target database is compatible with earlier DB2 versions, you can roll forward the target database to a point in time before the transport operation.

Important: If the database roll forward operation detects a table space schema transport log record, the corresponding transported table space is taken offline and moved into drop pending state. This is because database does not have complete logs of transported table spaces to rebuild transported table spaces and their contents. You can take a full backup of the target database after transport completes, so subsequent the roll forward operation does not pass the point of schema transport in the log stream.