Creating a new database design

Set up

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

Before you can deploy your schema, you must connect to the Db2 database.

Create

Follow these steps to create the model:

1. In the InfoSphere Data Architect workbench, ensure that 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 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 the name of the project (such as new-database-design) and click Finish. The new data design project appears in the Data Project Explorer view.
4. In the Data Project Explorer view, right-click Data Models and select New > Physical Data Model.
5. In the New Physical Data Model window, select the destination folder and enter the file name for the new physical model. Select DB2 for Linux, UNIX, and Windows as the database and V10.5. Then, click Finish.

   For more information about physical models, see Physical data models.

6. In the Diagram1 view that opens, start creating tables. Right-click anywhere inside the Diagram1 view and select Add Data Object > Table.

   For more information, see Adding or deleting objects from a physical data model diagram.

7. Add columns to your tables. Right-click the table object and click Add Data Object > Column.

   Repeat this step until you create all columns for the table. Also, use this Add Data Object menu to create indexes, keys, and triggers for a table.

8. Repeat steps 6 and 7 until all tables and views are created.
9. Click File > Save to save the physical model.

Generate and deploy

Follow these steps to generate and then deploy a DDL script to create your schema:

1. Generate a DDL script from the physical data model that you created in the Create 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. For example, select only Fully qualified names, Quoted identifiers, and CREATE statements 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; for example, change the default schema name from Schema to your Db2 schema name, which might be the same as your Db2 user name.
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 connection for the 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 in the Db2 database. Congratulations! If the status shows Failed, click the status to see the details. To fix the problem repeat the steps to modify The DDL script and run it again.