Transport examples

You can use the RESTORE DATABASE command with the TRANSPORT option to copy a set of table spaces and SQL schemas from one database to another database.

The following examples use a database named ORIGINALDB as source of the backup image and the target database TARGETDB.

The following illustration shows the ORIGINALDB table spaces and schemas:
Figure 1. ORIGINALDB database
Diagram showing ORIGINALDB database table spaces and schemas
The originalDB database contains the following valid transportable sets:
  • mydata1; schema1 + schema2
  • mydata2 + myindex; schema3
  • multidata1 + multiindex1 + multiuser2; schema4 + schema5
  • A combination of valid transportable sets also constitutes a valid transportable set:
    • mydata1 + mydata2 + myindex; schema1 + schema + schema3
The following illustration shows the TARGETDB table spaces and schemas:
Figure 2. TARGETDB database
Diagram showing TARGETDB database table spaces and schemas
If the sources and target databases contain any schemas with the same schema name, or any table spaces of the table space name, then you cannot transport that schema or table space to the target database. Issuing a transport operation that contains a schema or a table space that has the same name as a schema or a table space on the target database will cause the transport operation to fail. For example, even though the following grouping is a valid transportable set, it cannot be directly transported to the target database:
  • mydata2 + myindex; schema3 (schema3 exists in both the source and target databases)

If there exists a single online backup image for ORIGINALDB that contains all of the table spaces in the database, then this will be the source for the transport. This also applies to table space level backup images.

You can redirect the container paths for the table spaces being transported. This is especially important if database relative paths were used.

Examples

Example 1: Successfully transport the schemas schema1 and schema2 in the mydata1 table space into TARGETDB.
db2 restore db originaldb tablespace (mydata1) schema(schema1,schema2)
  from <Media_Target_clause> taken at <date-time> 
  transport into targetdb redirect

db2 list tablespaces
db2 set tablespace containers for <tablespace ID for mydata1> 
  using (path '/db2DB/data1')
   
db2 restore db originaldb continue
The resulting TARGETDB will contain the mydata1 table space and schema1 and schema2.
Figure 3. TARGETDB database after transport
Diagram showing TARGETDB database table spaces and schemas
Example 2: Transport the schema schema3 in the mydata2 and myindex table spaces into TARGETDB. You cannot transport a schema that already exists on the target database.
db2 restore db originaldb tablespace (mydata2,myindex) schema(schema3)
  transport into targetdb
The transport operation will fail because the schema schema3 already exists on the target database. TARGETDB will remain unchanged. SQLCODE=SQL2590N rc=3.
Example 3: Transport the schemas schema4 and schema5 in the multidata1, multiindex1, and multiuser2 table spaces into TARGETDB. You cannot transport a table space that already exists on the target database.
db2 restore db originaldb tablespace (multidata1,multiindex1,multiuser2) 
  schema(schema4,schema5) transport into targetdb
The transport operation will fail and TARGETDB will remain unchanged because table space multiuser2 already exists on the target database. SQLCODE=SQL2590N rc=3.
Example 4: Transport the myindex table space into TARGETDB. You cannot transport partial schemas.
db2 restore db originaldb tablespace (myindex) schema(schema3)
  transport into targetdb
The list of table spaces and schemas being transported is not a valid transportable set. The transport operation will fail and TARGETDB will remain unchanged. SQLCODE=SQL2590N rc=1.
Example 5: Restore the syscatspace table space into TARGETDB. You cannot transport system catalogs.
db2 restore db originaldb tablespace (syscatspace) schema(sysibm)
  transport into targetdb
The transport operation will fail because the system catalogs can not be transported. SQLCODE=SQL2590N rc=4. You can transport user defined table spaces or restore the system catalogs with the RESTORE DATABASE command without specifying the transport option.
Example 6: You cannot restore into a target database that does not exist on the system.
db2 restore db originaldb tablespace (mydata1) schema(schema1,schema2)
  transport into notexists
The transport operation will fail. Table spaces cannot be transported to a target database that does not exist.