Copying database objects between and within databases

In the Data Source Explorer, you can copy database objects and, optionally, the object data from one database to another database, or within a database from one schema to another.

Before you begin

You can select database objects in the Data Source Explorer to copy to a homogeneous or a heterogeneous database, or to another schema. Through options in the Paste Database Objects wizard, you can copy objects and, optionally, their data directly to a target database. Or you can copy the objects and generate a consistent subset of test data, with optional masking to protect privacy.

The source and target can be any of the database combinations that are listed in the table in Support for copying database objects.

The following are requirements for copying database objects between and within databases:
  • If you want to copy data, the target server must support the creation of global temporary tables.
  • If you want to paste the copied data in a DB2® database, a user temporary table space for which you have been granted USE privilege must exist. If you create a new data connection to the target DB2 database, you must create a user temporary table space before copying objects and their data. The page size depends on the page size of the tables that you are copying; in most cases, a 4 KB page size is sufficient.
  • If you want to copy data from an Oracle database, either homogeneously or heterogeneously, you must use the ojdbc14.jar or a later version instead of the default OCI driver.
  • If you want to generate a masked test data subset, a physical data model in a data design project in your workspace must be annotated with data privacy and masking options.

Procedure

To copy database objects between databases or within a database:

  1. If you want to generate a masked test data subset, associate the data connection with a physical data model that is annotated with data privacy and masking options.
    1. In the Data Source Explorer, right-click the source data connection, and then click Properties.
    2. In the Properties window, click Data Privacy Modeling.
    3. On the Data Privacy Modeling page, specify the physical data model, and then click OK.
  2. In the Data Source Explorer expand the source data connection, and then expand the database folders to expose the objects that you want to copy.
  3. Under the source connection, select one or more objects. Use the Ctrl key and Shift key to select multiple objects.
  4. Right-click the selected objects, and then click Copy.
  5. Expand the data connection in which you want to paste the copied objects, right-click the database node or a schema node, and then click Paste. The Paste Database Objects wizard opens.
  6. Complete the steps of the wizard. To see information about the fields in the wizard, press F1.
  7. Click Finish.

Results

If you selected the option to open the DDL file for editing, the DDL statements are saved to the file and the project that you specified. The file opens in the SQL and XQuery editor.

If you selected the option to run the DDL statements on the server, the statements are run on the server for the target data connection. The results are shown in the SQL Results view and in the summary report. Any errors and problems are logged in the Problems view. The Data Source Explorer is refreshed to show the new target database objects.

If you selected the option to paste a test data subset, an Optim interchange file is created in the export location that you specified. The file is used to create a script that generates test data from the source database and inserts the data in the specified schema in the target database. You can run the file in the Optim product.

What to do next

Tip: You also can copy selected database objects by dragging the objects from one database to another database or from one schema to another schema within a database. The following default options are used when you drag objects:
  • Copy both objects and data directly to the target database.
  • Copy all the rows of data.
  • Copy all of the unselected objects that are contained in a copied object.
  • Run the generated DDL on the target server, and stop with the first error.
  • Report both errors and warnings.

Feedback