DB2 UDB Version 8.2 includes a lot of enhancements for developers. A not so well known, key feature is the is the DB2 UDB modeling enhancements in the Rational(r) eXtreme Development Environment (XDE) that aids in schema development and data modeling. Paul Zikopoulos briefly covers some of the key database-specific features that developers who follow the Rational Unified Process(r) (RUP) can leverage when working with the data model.
by Paul Zikopoulos
DB2 Universal Database (DB2 UDB) Version 8.2 became generally available in September 2004. This release includes lots of enhancements for developers: a Java2 Platform, Enterprise Edition (J2EE) certified and redistributable JDBC Type 4 driver, support for CLR-based stored procedures, WSDL generation in support of Web services with mere clicks of a button, and more!
Another key feature for development, yet not so well known, is the DB2 UDB modeling enhancements in the IBM Rational eXtreme Development Environment (XDE) to aid in schema development and data modeling. In this article, I briefly cover some of the key database-specific features that developers who follow the Rational Unified Process (RUP) can leverage when working with the data model. The Rational XDE supports the modeling of an application, and when combined with the data modeling feature creates an ecosystem where the application development group and those charged with administering the data environment can work in tandem, on a common data model, which ultimately leads to corporate efficiencies.
With the Rational XDE, here are some of the activities you can perform with the data model:
- Creating the base data model
- Modeling the logical-to-physcial storage design model (table spaces)
- Modeling business rules for data deployment (domains)
- Modeling views
- Generating business logic in the data model (stored procedures, triggers, and so on)
- Generating an object model from the data model
- Generating a data model from an object model
- Reverse engineering a database into a data model
- Forward engineering a data model into a database
- Synchronizing changes between the data model and the database
Using the Rational XDE
The tools provided in the Rational XDE are available for developers via plug-ins into Microsoft Visual Studio.NET (VS.NET), WebSphere Studio Application Developer, and on their own in an Eclipse-based environment. In this article, I illustrate some of the database-specific functions using VS.NET.
When you install the Rational XDE add-in for VS.NET, the installation program automatically registers the plug-in for use with this IDE. From here, you build the data model by adding a Rational project as you would with any VS.NET-based project.
Data modeling objects
In this section, I show you some of the many data objects that you can model in Rational XDE.
The database object
At the time this article was written, the Rational XDE plug-in supports lots of databases, including DB2 UDB for Linux, UNIX, and Windows (from Version 8 back to Version 5) and DB2 UDB for z/OS (from Version 7 (V8 is coming soon) back to Version 5).
Each Rational XDE project can contain multiple data models and each data model can contain multiple databases. To keep it simple, I illustrate a single database (shown below). You can add the database to your model by right-clicking on the Main object in the Model Explorer and selecting Add Data Modeler->Add Database. (Note that this object is not synchronized to your deployment environment - if you want to forward engineer objects you create to this database, it should already exist.)
The table space object
A table space maps the logical design of the database to disk. Using the Add Data Modeler option in the Model Explorer window, you can add a table space to your data model. After defining the table space, you can drag-and-drop it onto the data model and its association with the TESTDB database is automatically generated:
Once you have added the table space to your data model, you can define its properties by selecting the table space, right clicking the mouse button, and selecting Data Modeler->Open Specification.
You can see in the previous figure that you have most of the options that you would expect to have when creating a DB2 UDB table space, including: prefetch size, extent size, assigning a buffer pool to the table space, specifying how the table space will be managed, and so on.
The domain object
Rational XDE has a domain object that can be used to set business rules for the database. Using a domain, you can define rules such as required fields and default values. The great thing about a domain is that you create it once and bind it to as many fields in the database as you want. This object is especially useful when the application development teams have the autonomy to create the schemas, yet the database administration team wants to ensure the development teams implement new schemas in a 'best practices' or 'practiced standards' manner.
The domain object is a new approach for database administrators (DBAs) in that they are used to defining these rules on a per table basis. More advanced DBAs utilize a user-defined type (UDT) to enforce these type of restrictions, yet they are on a per database level.
Consistency and maintenance benefits can be accrued using domains in a data model. For example, if you implement a Phone Number domain, then all phone numbers in your data model (home, work, cellular, and so on) can inherit the rules that you define for any sort of phone numbers (they must include the country code, and so on). In addition to this, if the data ever needs to be changed, you can do so in one location and trickle down the rule to all data columns that are built from the domain (for example, adding an area code to a phone number to support local calling).
The following figure shows an example of defining a domain - notice the Check Constraints where you can define code check for the data.
The table object
Once you have created a database and specified how it will be defined physically on disk (remember, the domain object is optional), you can add a table to your data model. You can add a table to your data model by selecting the Table object from the Data Modeler Toolbox and dragging the object onto the palette. This action will open the Table Specification window, as shown below:
You can see that you have most of the DB2 UDB options that you would expect when creating a table. You can define columns and their data types (including identity columns), referential integrity (RI) constraints, triggers, domain associations, key constraints, and more.
Once you have finished defining your table, the schema will be updated in the data model, as shown below. (Simply associate the table with the database by drawing a Database Realization line from the database to the table - this object is available in the Data Modeler Toolbox.)
You could continue to model additional objects, including stored procedures, triggers, Primary and Foreign key relationships, RI rules, views, and more.
Forward engineering the data model
You can take your data model and forward engineer it into a
product or, more likely, a test environment. The Rational XDE will either
generate the data definition language (DDL) for your model, create the data
model, or both. To generate a data model, select the database from the Model
Explorer window, right-click the mouse button, and select Data Modeler`Forward Engineer.
A wizard will help you select the objects that you want to
forward engineer, specify a file to contain the generated DDL, and specify a
database to execute the DDL, or any combination thereof. In the example below,
I chose to generate the DDL for the data model. This DDL could be checked into
Rational Clear Case or source control program from the IDE and re-used for a
As you can see, only the DDL for my table was generated,
which is all I selected in the wizard.
Reverse engineering the data model
So far in this article, I have shown you how to create and
forward engineer a data model into a test or production environment. You can
also generate a data model from an existing database and work with it in the Rational
XDE. You can reverse engineer a data model by selecting your project from the Model
Explorer window, right-clicking the project, and selecting the Reverse
Engineer option. A wizard will help you through the process. You can
reverse engineer your data model from an existing DDL file (for example, the
one that you created using the Rational XDE, or one created using the db2look
command), or from a database, as shown below:
As you can see in the previous figure, I decided to connect
to the BANKDB database and reverse engineer only the table spaces for my
database. (As you can see, the USERSPACE1 table space contains two tables,
DEPTXDE and EMPXDE.)
At this point, you could add more objects to the data model
and synchronize the changes back into the database, or vice versa.
example, you can see in the previous figure that I added a view to the data
model. (To do this, follow the instructions to add a table outlined earlier in
this article, only instead of a database realization, draw a view dependency.)
The view is based on tables that exist in the table space but were not imported
as objects into the model since they are part of a different schema.
Synchronizing the database and the data model
Let's assume that the DBA group has transferred a data model
to the application development group and the application development group
added an object (such as a view). The Rational XDE allows you to compare and sync
the data model, (or vice versa). In other words, if the database or data model
has changed, it can compare and generate the DDL to ensure they remain synchronized.
To synchronize a data model and an associated database, right-click your database
in the Model Explorer, and select Data Modeler->Compare and Sync. A wizard (similar
to the forward and reverse engineering wizards) will guide you through the process.
For example, the BANKDB database contained no views in the
PAULZ schema, as shown below:
This database was reversed engineered (detailed above) and a
view was added (it was built over another table in the database). To get the
changes to the model back into the database, I used the wizard to synchronize
the changes between the data model and the database.
The wizard generated a DDL script that would produce these
In addition to this, I asked the wizard to execute this
script for me, thereby creating the view shown below:
Putting it all together
As you can see, the DB2 UDB integration into the Rational
XDE provides enterprises with a powerful unification tool that can bridge the
gap between all components of the application and the underlying database schema.
When combined with the RUP methodology and a unifying modeling language such as
UML, the process, efficiency, and quality of your code steps up into a whole
new world. Interested? Learn more at: www.ibm.com/software/awdtools/developer/datamodeler/.
About the author
Paul C. Zikopoulos, BA,
an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than
nine years of experience with DB2 UDB and has written numerous magazine
articles and books about it. Paul has co-authored the books: DB2
Version 8: The Official Guide, DB2:
The Complete Reference, DB2
Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's
Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical
Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and
Database Administration). You can reach him at: email@example.com.