Overview of copying database objects

You can copy database objects from one database to another database or from one schema to another schema within the same database.

When you copy database objects between databases, you can copy the objects homogeneously; for example, from a DB2® database to another DB2 database, or from an Oracle database to another Oracle database. You also can copy database objects heterogeneously; for example, from an Oracle database to a DB2 for Linux®, UNIX®, and Windows® database, or from a DB2 for Linux, UNIX, and Windows database to a DB2 for z/OS® database.

The source and target can be any of the following databases.

The database objects that you can copy include tables, indexes, views, constraints, triggers, synonyms, sequences, and user-defined types. You can copy PL/SQL routines (that is, procedures and user-defined functions) and packages between databases that support the PL/SQL language. You also can copy modules, SQL routines, and SQL PL routines between DB2 for Linux, UNIX, and Windows databases. Optionally, you can copy some or all of the data in the copied table objects.

Through the Optim product, you can define and copy a consistent subset of data to the target database. Consistent, or referentially intact, subsets of data are created by using the relationships that are defined in the source database to determine the rows to copy from the selected tables. The resulting data subset includes rows from each of the selected tables that satisfy these relationships. For example, if a relationship exists between the 'Customers' and 'Orders' tables, the resulting subset would include the order records that were created for the customer records that were selected. Optionally, you can mask the copied data by associating the source data connection with a physical data model that is annotated with data privacy and masking options. Data masking is used to ensure data privacy in nonproduction environments for purposes such as software development and testing, software user training, or data mining and research.

After selecting one or more database objects to copy, you can use Copy and Paste commands on pop-up menus to copy the database objects. A wizard contains options for pasting the database objects and their data, for filtering the copied data, and for error handling. Alternatively, you can drag the selected objects from the source database to the target database.

When you copy database tables between heterogeneous databases, the source database tables are transformed so that they can be inserted into the target database. Data types are mapped to the target data types that most closely resemble the source data types. For some data types, more than one target data type is available. The most typical data type is mapped by default, but you can select from among the available data types in the wizard.

DDL statements are generated for running on the target database. You have the option of modifying the SQL script in the SQL and XQuery editor and then running the modified script on the target database.

Note: This copy feature is not intended for copying large databases. You can copy database objects and data from a large database, such as a production database, to a smaller database for use in development and testing. The current limits on the number of database objects and the amount of data that you can copy in one copy activity are 100 objects and 100,000 rows of data. The object limit includes all of the dependent and contained objects for the selected objects. If you try to copy too many objects or too much data, an informational message describes the limits for copying. You can exclude dependent and contained objects from the copy process by using options on the Source/Target page in the Paste Database Objects wizard.

Feedback