Use IBM Rational Data Architect to take control of your DB2 database

Data modeling plus a lot more using Rational Data Architect

Rational Data Architect (RDA) extends your data modeling capability and reaches far beyond typical database design. Use RDA to build strong logical or physical data models and realize your design into real world working database structures. View changes before they are made using RDA's unique impact analysis and compare and synchronize features. Also find the tools you need that are pre-built into RDA to do things like build simple or complex SQL queries.

Share:

Anson Kokkat (ansonk@ca.ibm.com), Product Manager for Data Server Tools, IBM

Photo: Anson KokkatAnson works at IBM as a product manager for Data Server Tools. Most of Anson’s professional career has been spent working on application development technologies related to data servers. He has an extensive background in DB2 and WebSphere integration, JDBC technology, .NET, stored procedures, Eclipse programming, and data modeling.



25 October 2007

Introduction

Building a house requires a lot of planning and design before the actual building begins. It's not often wise to start putting up walls and creating rooms without knowing the dimensions and specifications and what it's going to be used for. You also want to plan for future growth when unexpected changes occur, such as if a new baby arrives. Building a database should take on the same premise. You want to plan and design for your database before you actually go ahead and create the tables. The database should be able to accommodate future business requirements and adhere to the capacity of future growth.

Product name changeh

On December 16th, 2008 IBM announced that as of Version 7.5.1, Rational Data Architect is renamed to InfoSphere Data Architect to feature its role in InfoSphere Foundation Tools.

Look at some of the steps involved with building a house and compare that with building a database. Then use the comparison to help you understand how RDA and DB2 9 gives you the ability to build real business results.

Table 1. RDA compared to building a house
Building a houseBuilding a database
Design and plan what rooms you want and the dimensions.
Understand the business data that you want to capture and how to accurately place that in database structures.
The original plan often has to be modified to accommodate for needed changes to the design.
Plan for change and assess its impact before any changes are made to decide how it will affect the overall design.
You need the proper tools to build a house and to make sure that it is of the top-most quality.
RDA gives you the tools to build queries, and with DB2 9 as the foundation, you have the ability to manipulate XML data as well.

Connection to your database

RDA makes it very simple to connect to your data source and it already picks up all your cataloged databases. These connections can be found in the Database Explorer window.

Figure 1. Database connection using the Database Explorer window
Database connection using the Database Explorer window

To connect, right-click the database that you want, and then click Reconnect.

Enter your user ID and password, and when completed, you will see all of the data related to your database. Since you have connected to a V9.1 sample database, you should be able to see all the tables, columns, tablespaces, and other database objects that are of importance.

Figure 2. Database Explorer window
Database Explorer window

This is a very powerful part of RDA because you can even sample the contents of the table and make any necessary changes to the data where needed.

Figure 3. Sampling data directly from the tables
Sampling data directly from the tables

You saw above how to connect to your DB2 V9.1 database and look at various database objects.


Logical data modeling or physical data modeling and everything in between

RDA gives you the ability to create blueprints from scratch or manipulate an existing design to understand exactly what you do and how it impacts your changes. Utilizing the data design features of RDA and combining this with DB2 9, you have full control over all of your information assets.

Logical data modeling

The logical data model in RDA is to databases as sketches are to designing a house. Before you go and create the overall blueprints for the house, you want to start some type of design to get an overall look and feel for what you want to build.

In RDA, creating a logical data model allows you to show relationships between various entities, attributes for those entities, and the relationships between all these different objects. What you would usually do is define a logical data model before you turn it into a physical model. This gives you the ability to validate the data model with business users before you actually perform any physical design. You can view your house designs before you even define details of the plumbing.

To create a logical data model, navigate to File > New > Logical Data Model.

Figure 4. Create a new logical data model
Create a new logical data model

There are other ways to create logical data models as well, but see what happens next when you use this method.

If you are creating a new logical data model, you can create a "blank logical data model" using the template defined.

Figure 5. Create a new logical data model continued
Create a new logical data model continued

Once you click Finish, you should see the name of your new logical data model created in the Data Project Explorer window under your current project that you are working on.

Figure 6. New logical data model diagram
New logical data model diagram

Now try to understand the concept of a domain and how it fits in here. In a logical data model you are describing things and properties about these things. There are pre-defined objects established by RDA with respect to the logical data model, for example you can add an attribute to an already existing data type such as:

  • CHAR
  • VARCHAR
  • INT
  • DECIMAL
  • TIMESTAMP

When you are defining an attribute it has to come from a business perspective. Say that there are business analysts in your organization that want to define what type of data they want to include in your organization. They will not know how to define the notation of what they want based on the above pre-defined types. The terminology they will most likely use is something like the following.

The company is a business that builds custom couches for homes. The home couches have the following characteristics:

  • The home seat can only be 17.8" wide.
  • The material for the couch should be a light velvet if there are dark hardwood floors, and a dark velvet if there are light hardwood floors.
  • Some of the couches made for certain customers will have video screen inserts that will be different than the regular couches.
  • Some of the couches will be equipped with baby seat anchors and some will not.

So as you can see, the business user defines the requirements of what they want and in most cases it extends beyond the standard pre-defined types that are in RDA. For example, you cannot describe the above conditions using just VARHCAR(20).

This is where a domain comes in handy. You can create a domain model so that it can be used in other domain models or other logical data models.

To create a domain model within RDA navigate to File > New > Domain Model.

Figure 7. Create a new domain model
Create a new domain model

You can also right click on the package in your logical data model, then navigate to Add Data Object > Atomic Domain.

Figure 8. Alternate way to create a data model
Alternate way to create a data model

If you are going to add the domain to the logical domain model, then Figure 8 shows you how to do this. From here you can add the constraints or documentation that outlines the rules that the business analyst has described above.


Physical data modeling

Physical data modeling can be described best as the blueprints of the house. It describes all the details of the database and gives all the necessary information needed to manipulate your models based on what you want to do.

There are many aspects to physical data modeling, such as creating a new data model from scratch using the Physical Data Modeling wizard. You can also create a physical model from elements of another physical model that are already available. This is as simple as dragging and dropping the elements from the Data Project Explorer window.

Even still, with physical data models you can reverse engineer from the actual database or from the data definition language (DDL) file. You can also take a logical data model and transform that into a physical data model.

The following are elements of physical data modeling. However, there are other things that you can do:

  • Create a physical data model from scratch
  • Add objects to your physical data model from the Data Project Explorer window
  • Reverse engineering
  • Transform a logical data model into a physical data model
  1. Create a physical data model from scratch by navigating to File > New > Physical Data Model.

    This brings up the Physical Data Model wizard.

    Figure 9. Create a new physical data model
    Create a new physical data model

    The next screen that shows up prompts you to enter some basic information about the version of the database that you are going to use. When you select DB2 UDB V9.1 you are then enabling RDA to be able to model the database based on all the new features of DB2 9:

    Figure 10. New Physical Data Model screen
    New Physical Data Model screen

    Then select a template for the new model, that, in this case, contains a new empty physical model.

    Figure 11. Physical Data Model Template
    Physical Data Model Template

    After you click Finish you can see that a new model is created in the Data Project Explorer window with the dbm extension that means a physical data model.

    Figure 12. Physical data model in the Data Project Explorer window
    Physical data model in the Data Project Explorer window
  2. Add objects to your physical data model from the Data Project Explorer window.

    Now that you have your blank physical data model, there is a lot that you can do to get started with this model. If you want to add certain elements from another diagram you can easily drag and drop items from the Data Project Explorer window into this new physical data model that you have just created. Otherwise, you can simply mouse over into a blank area of the diagram and RDA will prompt you with what to do, such as add a table.

  3. Reverse engineering

    The above example showed how to create a blank physical data model, but what if you already have the database defined and you want to modify your design. This is called "reverse engineering." You can reverse engineer a physical model from a database or from a DDL file all through a wizard.

  4. Convert a logical data model to a physical data model.

    Look at how to create a physical data model if you have a logical data model that is already defined.

    Look at HOME.ldm within the Data Project Explorer window and the tables and data that describes this database.

    Figure 13. Logical data model to physical data model conversion
    Logical data model to physical data model conversion

    There are a couple of different ways to transform a logical data model to a physical data model. The following uses the Data Transformation wizard to accomplish the task.

    First, click Home.ldm. Then, navigate to Data > Transform > Physical Data Model.

    Figure 14. Transform a logical data model to a physical data model
    Transform a logical data model to a physical data model

    The Transform to Physical Data Model wizard opens.

    Keep Create New Data Model checked.

    Figure 15. Transform to physical data model options
    Transform to physical data model options

    Click Finish to complete the transformation to the physical data model.

You can now expand your physical data model under Data Models to see all the tables.

Figure 16. New transformed physical data model in the Data Project Explorer window
New transformed physical data model in the Data Project Explorer window

To see the new diagram from your physical database model, right-click Diagrams Folder and select New Overview Diagram.

Select all the elements you want on your diagram. Keep all the rest as default, and click OK.

You can show further items in the diagram by clicking Show Key and Show non-key.

Figure 17. New physical data model diagram
New physical data model diagram

You have so far seen the concept of logical data models, domain models, and physical data models. Now look at how you can change your physical data model and create DDL from the physical data model.

Making any changes to your physical data model involves expanding to the object that you want to change, making the changes, and then saving the model.

Look at an example of adding an index to your CUSTOMER table.

Expand your BuildHome Project that you created above by expanding Data Models > Home.dbm > Database > Home_schema >. Right click CUSTOMER, and navigate to Add Data Object > Index.

Figure 18. Add index to customer table
Add index to customer table

This creates a CUSTOMER_IDX that you can see under your customer table drop-down menu.

If you want to specify the type of index that you want created, simply click index that was just created and look at the Details section of the Properties view.

Figure 19. Index details
Index details

You have the following various options with how you could specify the index:

  • Unique
  • You can specify the Key Columns and specify the order using the toolbar arrows.
  • You can specify which non-key columns to include in the index and use the arrows again to specify the order.
  • Specify a PCTFree value if needed.

You can save your Home.dbm physical data model with the changes that you just made.

Create the DDL from your physical data model

You would use this if you are creating a new database. Otherwise, you would use the compare and synchronize feature to accomplish this.

To generate the DDL for your project, right-click Home_schema and select Generate DDL.

Figure 20. Generate DDL from Explorer
Generate DDL from Explorer

You can keep the defaults on the Options page. For the next Objects page, you select Tables and Indexes only, since that is all you have.

At the end, you should see Save and Run DDL that allows you to preview the DDL. You even have the ability to run the DDL on the server with the checkbox if you so choose.

Figure 21. Generate DDL window
Generate DDL window

When you click Finish you should see the DDL script file located in your Data Project Explorer window under SQL Scripts. The SQL Scripts folder is under the current project that you are working on. You can view or edit this file at any time.

Figure 22. Location of DDL in SQL Scripts folder
Location of DDL in SQL Scripts folder

The article has shown how to work with tables in RDA, but you are not limited to this alone. Data modeling capabilities extend to model objects such as the following:

  • Buffer pools
  • Partition groups
  • Table spaces
  • Indexes
  • Triggers

Look at how to model some of these elements as well using storage diagrams. Storage diagrams tell you where all of your physical data from your tables is kept. You have access to build storage diagrams directly from RDA.

Figure 23. Model storage diagram
Model storage diagram

When you right-click Add Storage Overview Diagram you get to select which elements you want.

You then see the storage diagram with all the elements you selected right in front of you.

Figure 24. Model tablespaces properties
Model Tablespace properties

Say that you have a partitioned database, which means it is part of a database that contains its own data, indexes, configuration files, and transaction logs. Data Partioning Feature (DPF) is a new feature that allows you to work with these portioned databases and is available in DB2 9.

When you are looking at your storage diagram in DB2 9, you should also see partition groups, as it is part of the DPF of DB2 9.

If you click on the above tablespace, you can see the properties for that tables space with the ability to change various properties.

If you wanted to see the list of all the tables in the tablespace, click Tables.

Figure 25. List of tables in the tablespace
List of tables in the tablespace

The ability to model all data elements in DB2 9 is not limited to the above. If you were trying to show this diagram to one of your bosses, you have the ability to use the palette to generate the shapes and lines that you wanted.

Logical data models are used for creating the blueprint needed to design your database. The domain models allow you to extend the capability of giving your objects in your models some depth and breadth. The physical data model gives you a picture of your database entities and lets you make decisions, then execute them on your current database model.


Compare and synchronize and impact analysis with DB2

With the ability to compare objects within your models or databases, you have the ability to decide how different objects compare to each other. You can compare the following:

  • Databases to databases
  • Models to models
  • Models to databases
  • Databases to models

With this information the user then has the ability to decide what changes they want to make and create an impact analysis to see how this change will affect their whole system. RDA clearly shows you how the change will impact your system, and you can even display a report for easy viewing and for sharing with others.

Look at the compare and synchronize and the impact analysis functionality within RDA in more detail.

Look at an example of putting the compare and synchronize features of RDA to use. The one thing you can always count on when working in an organization as a DBA is change. You always get requests to add new information to your existing infrastructure and it is expected to be done as soon as possible. RDA gives you the ability to understand the changes and integrate them with ease.

Take an example of comparing a physical database model with a database that is already implemented.

Revisit the example of the BuildHome project. Say that you have one CUSTOMER table from this project, but you also have another table with other updated information from another project in another department. If you want to compare the two tables, you could use the compare editor to see the differences.

Right-click CUSTOMER in the BuildHome project and navigate to Compare With > Another Data Object.

Figure 26. Compare feature in RDA
Compare feature in RDA

Then, you simply drill down the other database to the table that you want to compare. In this case, you compare it with the STAFF table in your SAMPLE database.

Say that you want to merge the changes from your BuildHome CUSTOMER, Cust_Id with your ID column in your STAFF table.

Figure 27. Compare in diagram
Compare in diagram

You can easily see the columns from both tables in the compare and synchronize menu. To make the changes, you can use the Copy from Left to Right or the Copy from Right to Left menu items (identified above) to make the changes. These changes are realized immediately.

After this you can CREATE the DDL for the changes that are to be made, and you can either run it directly on the server or save the DDL as an SQL script to execute at a later time.

Figure 28. Generate DDL from the compare output
Generate DDL from the compare output
Figure 29. New diagram after compare is performed
New diagram after compare is performed

You get to see which other tables are affected in your model, and then can decide if you want to make the change or not.

This feature comes in especially useful when you are dealing with something like an XML column and you want to make a change to it and analyze its impact. With DB2 9, working with XML data is just as simple as working with relational data. In RDA, the compare and synchronize and impact analysis is also just as simple.

When you are looking at an XML data type in RDA, it shows up just as easy as any other data type in RDA.

Figure 30. XML modeling
XML modeling

Now you can see how easy it is to manipulate this data as it can be sampled from the database explorer as you would do for other database objects.


Build queries

When developing your applications, you need a tool that can assist you to develop and exploit the information that is in your database. The SQL Builder gives you a graphical tool to build your queries weather they are for relational data or for XML data. With DB2 9, the XML data is stored natively and so you have the ability to get to that data just as easily as you would with the normal relational data.

To access the SQL builder, navigate to File > New > SQL Statement.

This starts up the SQL Builder.

Figure 31. Create new SQL statement
Create new SQL statement

You can give the SQL statement a name so that it is stored with the .sql extension, in case you want to find it and execute or edit it later on.

You are then prompted with a window to select your connection. Enter the name of your database, and click Reconnect. Enter your user ID and password.

The resulting window that opens up is the SQL Builder.

Figure 32. SQL Builder
SQL Builder

If you want to add tables in the middle pane, right-click in an open area and select Add Table, then pick which table you want to add.

You can use the bottom pane for designing what you want your SQL statement to contain. You can also use the SQL Expression Builder for more of the complex SQL statements that you would like to develop.

With the SQL Builder you also have the ability to drag and drop items from particular tables into your SQL Builder pane and work with that data as well. For complete instructions, refer to the SQL Builder pages in the DB2 9 Information Center documentation (found in the Resources section).

Since XML data is a mainstay for DB2 9, see how to look at XML data in RDA. Use the example of the BuildHome organization. Say that there is another group in the organization (summer student hiring group) that is responsible for hiring students for a summer construction program. However, the data that they keep is not kept in any one database, rather they use an XML format to keep track of their students.

You can easily import the XML file by going to File-> Import.

Figure 33. Import XML files
Import XML files

After this, it shows up in the XML schema's heading of your project.

Figure 34. XML schemas folder
XML schemas folder

If you double-click Student_details.xml, you can see the XML file and its values and expand the necessary data elements to find out what the values are for each field. In this case, you have documented the student ID, student name, and student email.

Figure 35. XML file as it shows up in RDA
XML file as it shows up in RDA

If you are interested in generating SQL or XML queries, the easiest way to do this would be through the mapping editor. You can define a source and target to generate the SQL or XML queries. In this case, the source would be your relational database and the target would be an XML schema definition (XSD) document that contains the structure of the XML document.

To create the mapping model in RDA, navigate to Mappings > New > Mapping Model.

Figure 36. Mapping Model with XML
Mapping Model with XML

Give you're mapping file a new name.

Figure 37. Mapping Editor
Mapping Editor

Click Finish to create a new mapping (msl) file in your editor. Now you can drag and drop your physical data model to the source, and in the target box you can drag and drop your XML schema document.

To create the mapping, right-click the object you want to map from the source, and the XML item that you want it mapped to on the target. Right-click the target XML item, and choose CREATE MAPPING.

Figure 38. Mapping Editor for XML
Mapping Editor for XML

To generate the actual SQL or XML, just use the Generate Script icon in the toolbar.

Figure 39. Generate SQL or XML
Generate SQL or XML

After leaving the Generate Script options as default, you can then view the SQL or XML that is needed to map these items together.

After you click Finish, your SQL or XML script shows up in the editor so you can view or edit this script as you like.

Figure 40. SQL or XML script that gets generated
SQL or XML script that gets generated

Building a home requires a lot of planning and design. Building a database, or keeping track of all the information in your organization, also requires a lot of planning and design. RDA gives you the tools to not only plan and design for all your information assets, but it also gives you the ability to work on the construction of your databases based on your design. Like designing a house, RDA allows you to maximize on your information assets so that your data is used to its fullest potential.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=264403
ArticleTitle=Use IBM Rational Data Architect to take control of your DB2 database
publish-date=10252007