Introduction

If you wanted to copy one or more database schema from one database to another, you could possibly use db2move utility. The db2move command internally exports table data from source database into set of PC Interchange Exchange format file and imports data into the target database. This, however, is a very unwieldy activity when it comes to copying multiple massive fact tables. DB2 9.7 FP2 introduced a new feature called transportable schema to facilitate faster data copy from source database to the target database.

Transporting a database schema involves backing up the source database and restoring the database schema to a different, but existing target database. During the database schema transport restore, the objects in the specified schema are recreated in the new database and the data is restored to the newly created objects.

Schema Transport Process

A database schema must be transported in its entirety and no partial copy is possible. If table space contains multiple schemas, then you must transport all the objects from all the schemas. The sets of schema’s that have no references to other database schemas are called transportable sets and those can be transported from one database to another.

Schema Transport Example
DB2 Schema Transport Eaxmple

The valid transportable sets for our example is as below:

Transportable Set Table Spaces Schemas
1 TS1, TS2 MART
2 TS3, TS4, TS5, TS6 STORE, STAGING

You can transport the table spaces TS1, TS2 and the schema MART however the transport of the table spaces TS3, TS4  and the schema STORE would fail with an SQL error SQL2590N reason code 1 due to STAGING schema reference within these table spaces.

What is not a valid transport set?

The set, table space TS7, TS8 and the schema SALES is not a valid transportable set because source and target databases have these table spaces in common. If the source and target databases contain any schema with the same name, or any table spaces of the table space name, then the schema transport cannot be completed on those objects.

Schema Transport Process – Example

If you wanted to transport MART schema, you could do so by executing the RESTORE command with the TRANSPORT clause something like below:

restore db tsdb tablespace (TS1, TS2) schema (MART)  from /home/db2inst1 taken at 20160621040041 transport into ttdb redirect

SQL1277W  A redirected restore operation is being performed. During a tablespace restore, only table spaces being restored can have their paths reconfigured. During a database restore, storage group storage paths and DMS table space containers can be reconfigured.

DB20000I  The RESTORE DATABASE command completed successfully.

SET TABLESPACE containers for 3 using  (FILE ‘/data/TS1.DAT’ 5000) 

DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE containers for 4 using (FILE ‘/data/TS2.DAT’ 5000)

DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

(If you’re working on automatic storage table spaces, you don’t have to set the container paths as listed above)

RESTORE DB tsdb CONTINUE

DB20000I  The RESTORE DATABASE command completed successfully.

Schema Transport Process – Internals

When you transport database schema, a temporary database named like SYSTG00x is created. This transport staging database is used to extract logical objects from the backup image to recreate objects on the target database, however you will not be able to connect to the staging database anytime during the transport process. 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 and the temporary database will be dropped. Note that it is possible to specify a name for this staging database using the ‘STAGE IN’ clause. If this is done DB2 will leave the staging database around after the transport restore completes as opposed to dropping it as is typically done.

Schema Transport Steps
Schema Transport Internal Processes

Schema Transport – Limitations

 

Re-creatable Objects Non-Recreatable Objects
Tables, User created global temporary tables and Materialized Query Tables (MQTs). BLU tables, Range Partitioned tables, Hierarchy tables, typed tables, system catalog tables, aliases and user created global variables. Please note at this moment we can’t use schema transport in pureScale environment.
Views and Statistics Views. Typed views.
User defined functions, procedures except external routine executable. Functional mappings, templates, methods, sourced procedures, OLEDB external functions and external routine executable files.
User defined types and generated columns such as expression, identity, row change timestamp and row change token. Structured types.
Constraints such as Check, Referential Constraints (primary and foreign keys), Unique and functional dependencies.
Indexes, triggers, sequences, packages Index extensions.
Object authorizations, privileges such as LBAC credentials, roles etc., and access control and audit configurations.
Table statistics, profiles and hints. Usage list, wrappers, jobs, nicknames, and servers

Tip

Finally, the transport restore operation is not recoverable; this is because the database does not have a complete set of logs of the transported table spaces to rebuild the table spaces and their contents.  It is recommended to take a full back up on the target if the database is recoverable after the transport operation completes so that ensuring database roll-forward operations can start past the transport operation.