< Previous | Next >

Using a wizard to reverse engineer an existing database schema

In this lesson, you will create a physical data model by reverse engineering the model from an existing database schema that uses the sample GSDB database. You can use the workbench to fetch existing schemas and databases to update objects before you commit the changes to the existing database.
You can create physical data models in the workbench in several ways. You can follow the steps in this exercise to reverse engineer a physical data model from a database, or you can use one of these other methods:
Use the New Physical Model wizard to reverse engineer from a DDL file
This option is useful if you want to use a DDL file instead of a live database connection as the source for the physical data model. To use this method, follow the steps below, but specify a DDL file in the New Physical Data Model wizard.
Drag and drop a schema or table from the Data Source Explorer to a data design project
This option is a quick way to create a physical data model, but it does not allow you to specify options for the new physical data model, such as a name for the file, or whether to also create an overview diagram. To use this method, drag a database, schema, or table from the Data Source Explorer to a project folder or a Data Models folder in the Data Project Explorer.
Transform a logical data model into a physical data model
This method requires you to start with a logical data model. In this tutorial, you are interested in making changes to an existing database, so this method is not useful. To use this method, highlight a logical data model in the Data Project Explorer and click Data > Transform > Physical Data Model from the main menu.

To use a wizard to reverse engineer from an existing database schema:

  1. Select the EMPLOYEE project in the Data Project Explorer.
  2. From the main menu, click File > New > Physical Data Model. The New Physical Data Model wizard opens.
  3. On the Model File page, specify the following settings to store your changes in the EMPLOYEE project:
    Destination folder
    Ensure that the EMPLOYEE project is specified in the Destination folder field. If the EMPLOYEE project is not already specified, click Browse to open a window where you can select the project name.
    File name
    Type sample_model.
    Database
    Select DB2 for Linux, UNIX, and Windows.
    Version
    Select 9.7.
    Create from...
    Select Create from reverse engineering.
    Your settings should now look like the following image.
    Completed Model File page
  4. On the Source page, select Database, then click Next. At this point, if you wanted to reverse engineer from a DDL file, you would select the DDL file check box.
  5. On the Select Connection page, select the GSDB database, then click Next.
  6. On the Schema page, click the box next to the GOSALESDW schema, then click Next. For this tutorial, you need to reverse-engineer only from the GOSALESDW schema, but you can select multiple schemas on this page if you want to reverse-engineer a model from more than one schema.
  7. Do not change the default settings on the Database Elements page, then click Next.
  8. On the Options page, select Overview to generate an overview diagram. Click Finish. You do not need to change the other default options.

    Overview diagrams are used to visualize the changes that you want to make to your data models. Overview diagrams can find and draw implicit foreign key relationships between data objects. You can use overview diagrams to better understand the interaction between data objects.

The sample_model.pdm physical data model is created and displayed in the Data Project Explorer in the Data Models folder. The model opens in the physical data model editor. The default overview diagram is created and stored in the following folder: EMPLOYEE > Data Models > GOSALESDW > Diagrams > GOSALESDW. The default overview diagram contains all tables that are in the GOSALESDW schema. In the next lesson, you will create a more simple overview diagram and then use the diagram to modify the GOSALESDW schema.
< Previous | Next >

Feedback