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:
- 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.
- In the Data Source Explorer, right-click the source
data connection, and then click Properties.
- In the Properties window, click Data Privacy
Modeling.
- On the Data Privacy Modeling page, specify the physical
data model, and then click OK.
- 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.
- Under the source connection, select one or more objects. Use the Ctrl key and Shift key to select multiple objects.
- Right-click the selected objects, and then click Copy.
- 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.
- Complete the steps of the wizard. To see information
about the fields in the wizard, press F1.
- 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.