Support for copying database objects

You can copy database objects and their data to both homogeneous and heterogeneous databases.
Table 1 shows the databases that are supported for heterogeneous and homogeneous copying.
Table 1. Supported target databases for each source database
Source database Target databases
DB2® Version 9.5 for Linux, UNIX, and Windows
  • DB2 Version 9.5 for Linux, UNIX, and Windows
  • DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
  • DB2 Version 9.5 for Linux, UNIX, and Windows (except Version 9.7 features)
  • DB2 Version 9.7 for Linux, UNIX, and Windows
  • DB2 Version 9 for z/OS®
  • Oracle Database 10g
  • Oracle Database 11g
DB2 Version 9 for z/OS
  • DB2 Version 9.7 for Linux, UNIX, and Windows
  • DB2 Version 9 for z/OS
Oracle Database 10g
  • DB2 Version 9.7 for Linux, UNIX, and Windows
  • Oracle Database 10g
  • Oracle Database 11g
Oracle Database 11g
  • DB2 Version 9.7 for Linux, UNIX, and Windows
  • Oracle Database 10g (except Oracle Database 11g features)
  • Oracle Database 11g
Support for copying objects between homogeneous databases is limited only by the limitations that are described in Limitations on copying database objects and data. Support for copying object types between heterogeneous databases is described in the following list. Options that are not included in this syntax do not have DDL syntax generated for them. A warning message is issued for unsupported options.
Copying tables
Table definitions with the following syntaxes are supported for copying tables between heterogeneous databases:
  • A table with a primary key
  • A table with a NOT NULL constraint
  • A table with supported data types
  • A table with columns that have default values
Copying indexes
Unique indexes are supported for copying between heterogeneous databases.
When copying between DB2 for Linux, UNIX, and Windows and DB2 for z/OS databases, only the following syntax is supported:
CREATE <UNIQUE> INDEX index_name
	ON table_name
	(column_name	ASC|DESC);
Copying constraints
The following types of constraints on table and view objects are supported for copying between heterogeneous databases:
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
Copying views
Copying views between heterogeneous databases is supported at the level of support for the tables on which the views are based. The limitations on copying views are described in Limitations on copying database objects and data.
Copying triggers
You cannot copy a trigger by itself; a trigger is copied as part of a table or a view to which it belongs.
Trigger definitions with the following syntaxes are supported for copying triggers between heterogeneous databases:
  • FOR EACH ROW (that is, row triggers)
  • Statement triggers
  • BEFORE and AFTER
  • INSTEAD OF triggers on views, except those that contain a FOR EACH ROW clause
  • INSERT, UPDATE, and DELETE
Note: Only PL/SQL triggers are supported for copying from DB2 Version 9.7 for Linux, UNIX, and Windows database to Oracle.
Copying user-defined types
The following user-defined types are currently supported for copying between heterogeneous databases:
  • Oracle VARRAY user-defined types.
  • DB2 ARRAY and VARRAY user-defined types
In DB2 for Linux, UNIX, and Windows, VARRAY user-defined types are fully supported only in PL/SQL procedures.
Copying synonyms
Synonyms for the following objects are supported for copying heterogeneously between Oracle and DB2 for Linux, UNIX, and Windows:
  • Tables
  • Views
  • Other synonyms

Additionally, synonyms for nicknames are supported for copying from a DB2 for Linux, UNIX, and Windows source to an Oracle target. In DB2, synonyms are also referred to as aliases.

You can copy synonyms without the objects that they refer to. However, if an object whose synonym you copy does not exist in the target database, the server throws an error that indicates the missing object.

Copying sequences
Heterogeneous copying of sequences is supported between Oracle and DB2 for Linux, UNIX, and Windows.

All sequence parameters except CACHE are supported for copying.

Copying PL/SQL routines
Only PL/SQL routines, which are stored procedures and stored functions that are created by using PL/SQL syntax, are supported for copying between heterogeneous databases. Oracle and DB2 PL/SQL routines are supported for heterogeneous copying at the level that DB2 Version 9.7 for Linux, UNIX, and Windows supports PL/SQL. For full information, refer to the DB2 Version 9.7 for Linux, UNIX, and Windows documentation.
Copying modules
Entire modules are supported for copying between DB2 Version 9.7 for Linux, UNIX, and Windows databases.
Copying PL/SQL packages
Oracle and DB2 PL/SQL packages are supported for copying between heterogeneous databases at the level that DB2 Version 9.7 for Linux, UNIX, and Windows supports PL/SQL. For full information, refer to the DB2 Version 9.7 for Linux, UNIX, and Windows documentation.

Feedback