Modeling a DB2 UDB database with IBM Rational XDE Developer for Java

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Level: Introductory

Suita Gupta (suitag@my.ibm.com), Developer Technical Support, IBM
Nanda Pilaka (nandap@us.ibm.com), Developer Technical Support, IBM

14 Apr 2004

This article shows you the basics of how to model a DB2 UDB database using IBM Rational XDE Developer for Java.

Editor's note

On June 5, 2006 IBM announced the withdrawal of the Rational Rose XDE product family listed below. The Rational XDE components are being withdrawn from the market; however, the Rational Rose components will continue to be supported and will be re-packaged into a new set of offerings.

For more comprehensive information on the product migrations with the IBM Rational June 2006 product release plans, please visit: http://www.ibm.com/software/rational/support/migrations/

Introduction

Everyone involved in database application development follows some kind of methodology. The methodology you choose helps you to analyze your requirements and to develop and deliver the final product. A principal piece of your methodology will be designing or modeling your database, based upon your business requirements. In this article, we'll talk about designing and modeling specifically for DB2® Universal Database™ using Rational® XDE Developer for Java™.

The Unified Modeling Language (UML) is the standard graphical notation for modeling business and software application needs. The use of the UML for object-oriented software development has become widespread in the past few years. In addition to using UML for object modeling, the strength of UML can be applied to modeling and designing the data stores which your application is going to use. Data modeling basically involves analyzing the client's requirements in order to develop a model of the client's data needs which can then be treated as the blueprint or the framework around which to build the database for the application.

This article will show you how easy it is to generate a model for a database, based on one that we are already familiar with, the DB2 Sample database. By the end of this article you will see how to model the DB2 Sample database and some of its objects. We will also show you how to generate the DDL scripts from the completed model and how you can use these scripts to create the final DB2 database.

We are going to use the Rational XDE Developer for Java version 2003.06.00 and IBM DB2 Universal Database v8.1 for our modeling exercises. Rational XDE supports data modeling for the ANSI SQL 92 standard, all versions of DB2 UDB from v5.2 onward and DB2 for OS/390® 5.x, 6.x, 7.x. Rational XDE can be used to model Oracle, Sybase and SQL Server databases as well.

Benefits of data modeling

There are many advantages of data modeling, not only for the data designers and administrators in the development team but also for the programmers involved.

• The database designer can use a tool like Rational XDE to model and visualize the database, ensure database rules, add different database objects, play around with relationships.
• The database administrator can either create a model of an already-existing database or generate DDL from the model and create the database for a specific implementation.
• A data model provides application developers a much better understanding of the overall application architecture, even though generally speaking they are not required to know in-depth about database architecture details. They can use the data model to model their classes for example from the table structure.
• A data model can also be useful to the end user in verifying that all of their data needs have been captured accurately.

If all the modeling in the project is done using UML, the data model will fit in well with the rest of the system design and help the entire team to work together seamlessly.

Back to top

Before we begin

Before we begin creating a database model, let's look at a screenshot of Rational XDE and the menus and options we are going to use for modeling the database, shown in Figure 1:

We will need to create a project for our data model. Click File -> New -> Project. Choose to create a Data Modeling Project and give it a name 'Data Model'. Once it has been created you will be able to see it in the Model Explorer pane as shown in Figure 2 below.

Two models have been created for you -- a physical data model and a logical data model. Data architects and database designers often use a logical data model to capture data requirements in a form that is independent of a particular database implementation. The logical model is a good vehicle for communicating with business stakeholders to ensure that data requirements are clearly captured, in part because it is not cluttered with database-specific detail. Often, database designers refine their logical data models into one or more "normal forms" -- to eliminate data duplication and increase the quality of the resultant design. Once you feel confident in the accuracy of your logical data requirements, you can transform the logical model into a physical data model, which adapts the model to a particular target database. This physical data mode is then refined and optimized for a particular database system or application.

Use of a logical data model is outside of the scope of this article. For the remainder of this article we will focus on database design at the physical data model level.

Back to top

Modeling databases and table spaces

We'll start by creating the database model. The database is the biggest element that can be modeled and is denoted in the data model using the UML notation of <<Database>> as you will see in Figure 3 below. Rational XDE allows you to choose any of its supported DBMS as the implementation database for your data model. In this article we have chosen DB2 UDB version 8 for demonstrating the data modeling process.

Creating the database component

In the Model Explorer, right click on the Main ->Add Data Modeler ->Database.

Then, click on the newly created database component in the Model Explorer, and type a name. We will use the name sampledb as the unique name for our database.

Right click on sampledb in the Model Explorer and click Data Modeler -> Open Specification.

In the Database Specification dialog box, under the Database list select IBM DB2 8.x as the target database.

Click OK, and drag and drop the database component onto the Diagram view

Rational XDE allows you to model a tablespace to which you can assign tables. We shall see how to model a tablespace called sample_tbsp which has one container assigned to it. Please note that the container path you specify in the following steps should already exist.

Before proceeding further you can close the Logical Data Model by right clicking on it in the Model Explorer. Your model can be saved at any point using the Ctrl+S hotkey.

Creating a tablespace

In the Model Explorer right click the database and then click Add Data Modeler -> Tablespace.

In the Model Explorer, right click the new tablespace and click Data Modeler -> Open Specification.
In the General tab, enter the name of the tablespace, for example sample_tbsp. You can also specify other details such as type of tablespace, extent size, prefetch size, page size, and buffer pool name.

Once done, click OK and drag and drop the tablespace component onto the Diagram view. Once you have modeled the database and the tablespace, Rational XDE will automatically create a dependency relationship between the database and the tablespace as seen in Figure 8 below:

Creating a container

In the Model Explorer right click a tablespace and then click Data Modeler -> Open Specification.
In the Container tab, click on the NEW icon and enter the path name for the new container.

Checkpoint: Your model should now look as shown in Figure 10:

Back to top

Tables and relationships

We are going to model two tables from the original sample database: the employee and the department tables. We are also going to model the referential integrity relationship between these tables.

Steps to create the table component:

1. In the Model Explorer, right-click the data model, and click Add Data Modeler ->Table.
2. Specify the table name in the General Tab of the Table Specification dialog box, and add the columns for this table in the Columns Tab. You can also specify which column would be the primary key for the table.
   Figure 11. Adding a table to the model
   
   
3. Click OK and you will see the Table component in the Model Explorer. Drag and drop this onto the Diagram view.

We are now going to establish referential integrity between the employee table (Foreign Key: workdept) and the department table (Primary Key: deptno). This can be done by creating an 'identifying' relationship between these two tables:

1. Select the Identifying Relationship option from the Data Modeler list in the Toolbox, then first click on the parent department table component in the Diagram view and then click on the child employee table component.
   
   Figure 12: Setting up referential integrity
   
   
2. In the Relationship Specification dialog box, you can specify the child and parent roles.
   
   Figure 13: Relationship specification
   
   
3. You can also change the name of the foreign key column in the employee table from the Migrated Keys tab of the Relationship Specification dialog box:
   
   Figure 14. Migrated keys
   
   

You will see that a new column has been added to the employee table. You can change the 'workdept' column position in the employee table, from the Column Tab of the Table Specification dialog box as shown below in Figure 15:

Your model should look similar to Figure 16 below:

Back to top

Views

Rational XDE allows you to create views based on tables, or based on SQL queries. These queries could include WHERE, GROUP BY or ORDER BY clauses. You can either specify the SQL query for the view manually or you can select the columns, for your view, from the View Specification dialog box.

Here are the steps to create the view component:

1. In the Model Explorer right-click on the model, and then click Add Data Modeler -> View. A new view displays in the Model Explorer.
2. Right-click the new view, and then click Data Modeler > Open Specification. The View Specification dialog box opens.
3. In the General tab, enter 'emp_view' as the name for the view in the Name box.
4. Click OK and drag and drop the view onto the Diagram view.

1. Select View Dependency from the Data Modeler list of the Toolbox, and then click first on the View component (emp_view) and then click on the source table component (employee table) of the diagram.
   
   Figure 17: Creating a view
   
   
2. This will populate your view with all the columns in the source table.
3. You can then specify a customized SQL query for your view by checking the 'User Defined' checkbox in the General tab of the View Specification dialog box:
   
   Figure 18: View specification
   
   
4. Then enter your customized SQL query in the SQL tab of the same dialog box. Click OK once you're done.
   
   Figure 19. View specification
   
   

Back to top

Stored procedures

Rational XDE also allows you to model stored procedures for your database. The supported DBMSs are:

• IBM DB2 UDB 5.2, 6.1, 7.0, and 8.0
• IBM DB2 MVS 5.x, 6.x, and 7.x
• Oracle 7.3, 8.x, and 9i for Windows NT
• Microsoft SQL Server 6.5, 7.0, and 2000
• Sybase Adaptive Server 12.x

Keep in mind though that a realization relationship must exist between the database and the stored procedure in order for the DDL to be executed against the target database. Stored procedures are grouped in stored procedure containers in Rational XDE. Before generating the DDL for the model, the tables, stored procedures and views need to be assigned to the database using realization relationship.

Steps to create a stored procedure component:

1. In the Model Explorer window, right click on Main.
2. Select Add Data Modeler and click Stored Procedure Container. This will create a stored procedure container in the data model with the default name 'ProcedureContainer1'.
3. You may use the default name for the container or change it via the Properties window below the Model Explorer window.
4. Right click on the stored procedure container 'ProcedureContainer1', select Add Data Modeler and click Stored Procedure. This will add the stored procedure component with a default name of 'Procedure1'. Again you may either use this default name or provide another name if you wish to do via the Properties window below the Model Explorer window.
5. You must also add a 'database realization' relationship from the database to the stored procedure in order to generate the DDL for the stored procedure.
6. To add a 'database realization' relationship, click on Data Modeler in the Toolbox, scroll down the list to 'database realization', click on it to select it.
7. Then click on the database component first, and next click on the stored procedure container component to create the realization relationship. The data model should appear as shown below in Figure 20:
   
   Figure 20. Data model showing realization relationship
   
   
8. The stored procedure name and signature (parameters and so on) can be changed at this point or later by right clicking on the stored procedure in Model Explorer, selecting Data Modeler and clicking Open Specification.
9. This will open the Stored Procedure Specification dialog.
10. In this dialog, you can modify the procedure name, the language it is written in, and so on using the General tab, add parameters using the Parameters tab and finally specify an action body using the Action Body tab as shown in Figure 21 below:

Back to top

Triggers

XDE allows you to create user-defined triggers to enforce business rules in your database.
To model a trigger:

1. Right click on the desired table in the Model Explorer.
2. Select Data Modeler and click on Open Specification to open the table's specification.
3. In the specification dialog, click on the Triggers tab, and click New to model a new trigger for the table.
4. You can create either an 'after' or 'before' trigger along with an action body for the trigger. Figure 22 below illustrates how to do this:

Back to top

Generating DDL scripts based on the data model

Rational XDE provides the Forward Engineering wizard to generate data definition language (DDL) scripts for the entire database or for a specific component in the data model. The DDLs generated by Rational XDE conform to the ANSI SQL 92 standard. XDE also allows you to execute the generated DDL against the target database which you specify before you start a data model project. Therefore make sure that you have correct connectivity to the target database. Also all database components in the data model must be connected to the database component by means of relationships as explained in previous sections. If this is not done for any component, no DDL will be generated for that particular component.

To generate a DDL script:
In the Model Explorer, right-click an existing database, package, or table, and then click Data Modeler > Forward Engineer. The Forward Engineering Wizard dialog box for data modeling opens. Follow the instructions in the wizard. Figure 23 below is a screenshot of the process of generating a DDL for the entire database using the Forward Engineering wizard:

Back to top

Connecting to the database

Rational XDE allows you to connect to a DBMS and execute the generated DDL to create the database objects. The supported DBMSs are:

1. IBM DB2 UDB 5.2, 6.1, 7.0, and 8.0
2. IBM DB2 MVS 5.x, 6.x, and 7.x
3. Oracle 7.3, 8.x, and 9i for Windows NT
4. Microsoft SQL Server 6.5, 7.0, and 2000
5. Sybase Adaptive Server 12.x

1. IBM DB2 APP Driver (ODBC)
2. IBM DB2 UDB JDBC Driver
3. Oracle OLE DB Provider
4. Oracle Thin JDBC Driver
5. OLE DB Provider
6. MS OLE DB for ODBC
7. MS OLE DB for Oracle (MDAC)
8. MS SQL OLE DB Provider
9. Sybase ASE ODBC Driver

Back to top

Reverse engineering

We have already covered forward engineering. With reverse engineering we can create models for already existing databases. This will allow us to make changes to the model such as adding tables, stored procedures, triggers, and so on, and then to forward engineer by generating and executing the DDL for the modified model.

To reverse engineer a database:

1. In the Model Explorer, right-click the data model and click Data Modeler -> Reverse Engineer. The Reverse Engineering Wizard opens.
2. Follow the instructions in the wizard to select a database and all the elements to be included in the reverse-engineering process.

Back to top

Compare and Sync mode

The Compare and Sync feature in Rational XDE is used to ensure that the data model is consistent with the generated DDL script or the target database. Compare and synchronization does not include diagrams, and relationships between models in its comparison. Before you compare and synchronize a data model with a database, verify that you have the correct database connectivity, including the correct logon and password.

To compare and synchronize a data model with a DDL file or database

1. In the Model Explorer or a diagram right-click an existing database, and then click Data Modeler > Compare and Sync.
2. The Data Model to Database Synchronization Wizard opens. Follow the instructions in the wizard.

1. If you are comparing a data model that was generated from reverse engineering a DDL, you must reset the Owner/Schema property to the name of the schema you reverse engineered. This must be done for each data model element you include in your comparison.
2. You assign all the elements that you want to include in the comparison and synchronization to the data model database. In the comparison process XDE ignores any element not assigned to the data model database.
3. Your data model database must use the same name and database target as the DDL or DBMS database that you are comparing.
4. If you are comparing views in your data model to views in a DDL, the DDL must use fully qualified names, especially in the SQL statement of the view. If the DDL does not use fully qualified names, the views appear as differences in the comparison process, and you must manually review the SQL statement for each view to discover any actual syntax differences.

Back to top

Conclusion

Finally, Figure 25 below shows what the database model now looks like for our test project 'Data Model'.

Figure 25. The database model

In conclusion, we have outlined the process of modeling databases using Rational XDE without going into too much detail so as to not overwhelm the beginning user. This simple example should get you started down the path so that you can begin to use this tool to meet your own database design and modeling needs. To obtain more information regarding the product, please refer to Rational XDE's online help from the Help menu.

Notes

1. The database DDL (the CREATE DATABASE command) is not inserted into or generated to the DDL file in the Forward Engineering wizard. What this means is that the target database will need to be manually created first before the DDL script can be executed against it.
2. Rational XDE version used in this article is 2003.06.00. When modeling stored procedures, you may find that the DDL for the stored procedure does not have an action body if you specified one in the Action Body tab of the stored procedure specification. This is a defect which has been fixed in Service Release 2 (fixpack 2) which is targeted for release April 9 2004. Please check www.rational.com for more information regarding the current service release.
3. When specifying an action body for a DB2 stored procedure, it is required to use an alternative SQL statement termination character, such as for example '@', because the semi-colon is used inside the stored procedure body to terminate stored procedure action statements. However when the stored procedure DDL is generated via the Forward Engineering wizard, there is an extra semi-colon generated after the '@' which is not required and which will also flag an error with DB2's SQL compiler.
4. It may be necessary to edit the generated DDL script to ensure that the right statement termination characters are being used.

About the authors

		Suita Gupta works as a technical support representative with the WebSphere Competency Center team. She has DB2 certifications in both application development and administration areas. Prior to working on the WebSphere support team, she worked with the Developer Relations DB2 Technical Support team working on a variety of DB2 application development and administration issues. You can reach Suita at suitag@my.ibm.com.

		Nanda Pilaka works as a Software Engineer with the Content Manager Business Partner Support team in Dallas, Texas. He has DB2 certifications in both application development and administration areas. Prior to working on the Content Manager support team, he worked with the Developer Relations DB2 Technical Support team at IBM Dallas working on a variety of DB2 application development and administration issues. You can reach Nanda at nandap@us.ibm.com.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Back to top