Duplicating an existing database design

Set up

To duplicate a database, you must have access to a working installation of InfoSphere Data Architect. Follow these steps to install InfoSphere Data Architect:

1. Log on to the Db2 web console.
2. Download InfoSphere Data Architect from the web console.
3. Install InfoSphere Data Architect.

Connect

To work with InfoSphere Data Architect you must have a connection to the database source that you want to duplicate and a connection to the Db2 database.

Creating a connection to an existing database

Follow these steps to create a connection to your existing database and verify that you have access to the tables that you want to duplicate:

1. Start InfoSphere Data Architect.
2. Select a workspace. You can choose the default directory.
3. Create a new connection. Right-click Database Connections in the Data Source Explorer view, and then click New. If this view is not present, open the Data perspective by clicking Window > Open Perspective > Data.
4. In the New Connection window, select the database manager for your existing database source. Enter the information in the General tab and then test the connection to ensure that the information is correct. After the test is successful, click Finish.
5. Verify that the new connection was created. In the Data Source Explorer view, click Database connections and find the connection name. Next, expand the Database folder and drill down to verify that the necessary database objects are present.

Creating a connection to your Db2 database

Follow the steps in the Connecting InfoSphere Data Architect to a Db2 database section of these docs to create a connection to your Db2 database.

Replicate

A physical model describes how the data will be represented within the database. When you create a physical model, you specify the objects of the model, such as tables and columns. You also specify the mapping relationships between tables to ensure that the data is represented exactly as intended within the database.

Follow these steps to create a physical model from your existing database source:

1. In the InfoSphere Data Architect workbench, ensure that the Data perspective is active. The default perspective is the Data perspective. If the Data perspective is not active, click Window > Open Perspective > Data. The Data option does not show as an option in the menu if the Data perspective is active.
2. Create a new data design project by clicking File > New > Data Design Project.
3. In the New Data Design Project window, enter a name and click Finish. The new data design project appears in the Data Project Explorer view
4. Create a new physical data model by right-clicking the Data Models folder in the newly created data design project. Then, click New > Physical Data Model.
5. In the New Physical Data Model window, complete the required information such as database and version and click Create from reverse engineering. Click Next.
6. In the Source page, select Database. Click Next.
7. In the Select Connection page, select the connection for your existing database that you created and validated in the Connect task and click Next.
8. In the Select Objects page, select the schema name that you want to duplicate and click Next.
9. In the Database Elements page, select the database objects that you want to replicate. To copy the database structure, select only Tables and clear any other objects as shown in the following figure. Click Next.
10. In the Options page, select Overview to generate the necessary diagrams and view the implicit relationships to validate that the physical model is correct.
11. Click Finish to view the overview diagram that shows the new physical model that you created. The following example shows an overview diagram.
    

Transform

A logical data model is a detailed description of the data that does not include information about how the data is physically implemented via a specific database technology. The information is typically shown in a common language and does not contain technical jargon.

1. From the Data Project Explorer view in InfoSphere Data Architect, right-click the database name under the Physical Data Model, and then select Transform to Logical Data Model.
2. In the Transform To Logical Data Model window, select Create new model and click Next.
3. Specify the location of the new model file by entering the destination folder and the file name. Click Next.
4. In the Options page, select options for the logical name and the defaults for data types. Select the default package name and click Next.
5. Click Finish to save the generated logical data model to a resource.
6. In the Data Project Explorer view, right-click Package1 under the Logical Data Model, then select Transform to Physical Data Model.
7. In the Transform To Physical Data Model window, select Create new model and click Next.
8. In the Physical Data Model File page, select DB2 for Linux, UNIX, and Windows as the database and V10.5 as the version. Enter the file name of the physical data model for your Db2 database and click Next.
9. In the Options page, select options for the logical name and the defaults for data types. Select the default package name and click Next.
10. In the Output page, click Finish to save the generated physical data model to a resource.

Generate and deploy

Follow these steps to generate and then deploy a DDL script that duplicates your schema in your Db2 database:

1. Generate a DDL script from the physical data model for your Db2 database that you created in the Transform task. In the Data Project Explorer view in InfoSphere Data Architect, right-click Database and select Generate DDL.
2. In the Generate DDL window, select the options that you want to use and click Next. The following example shows the options that are selected just to generate CREATE statements with fully qualified names and quoted identifiers:
   
3. In the Objects page of the Generate DDL window, select the database objects that you want to create in your Db2 database. Click Next.
4. In the Save and Run DDL page of the Generate DDL window, select Edit and Run DDL in the SQL editor to edit the script and click Next.
5. In the Summary page of the Generate DDL window, click Finish.
6. In the SQL editor view for the script, remove the COMPRESS NO clause from the CREATE TABLE statements. This clause is not supported when you create column-organized tables. Also, make any additional changes to the script.
7. Click Run SQL. The following example shows the edited script without the COMPRESS NO clause and the Run SQL button to click:
   
8. In the Select Connection Profile window, select the database connection for your Db2 database. Click Finish.
9. Check the status of the script in the SQL Results window. A check mark indicates that the schema was successfully created on your Db2 database. Congratulations! If the status shows Failed, click the status to see the details. To fix the problem repeat steps 6 through 9 to modify the DDL script and run it again.