Data modeling with InfoSphere Data Architect and Informix Dynamic Server

Accelerate data integration projects and create trusted information assets

This tutorial shows you how to use IBM® InfoSphere® Data Architect (IDA) (formerly IBM Rational® Data Architect) as a modeling tool for Informix® Dynamic Server (IDS), and has been updated to include information about the 7.5.1 release of IDA. Learn about the various features of IDA, including how models can be used to represent business entities, database entities, and domain entities. Follow along with scenarios and examples that reveal the benefits IDA provides specifically for IDS.

Share:

Venkatesh Gopal, Senior Software Engineer, IBM

Author Photo: Venkatesh GopalVenkatesh Gopal is a Senior Software Engineer working in the Data Studio team. He has over 14 years of experience in application development for databases using different technologies (Java, Microsoft and 4GLs) and also Eclipse technologies.



Srini Bhagavan (srinib@us.ibm.com), Senior Software Engineer, IBM

Srini BhagavanSrini Bhagavan is a Senior Software Engineer and Development Manager working in the Data Studio team. He has over 15 years of experience in database technology, Telecom, Open Source Application development and more recently in building Eclipse based administration tools for DB2, Informix and MySQL.



12 March 2009 (First published 26 November 2008)

Before you start

Learn what to expect from this tutorial, and how to get the most out of it.

About this tutorial

This tutorial walks you through the different features in IDA and shows you how it can be used with IDS. Learn how to reverse engineer a physical model from a database, transform business requirements into models (logical and physical), and associate domain elements that help in identifying critical and confidential information, all using IDS as the database.

Objectives

In this tutorial, you will learn how to:

  • Connect to an IDS server
  • Reverse engineer a new physical model from an existing database
  • Create a physical database schema from a logical model
  • Modify an existing logical model and moving that change through to the database
  • Maintain data consistency and quality by attaching a domain model to the database

Prerequisites

This tutorial assumes you have some basic knowledge about Informix database servers. It also assumes some basic knowledge in working with Eclipse-based tools. If you want some background on navigating Eclipse-based tools, the IDA Welcome page includes an overview of the Eclipse platform.

System requirements

You need a system running Windows® XP or Windows Vista® with about 1GB of disk space to install IDA and IDS. For this tutorial, I have used Informix Dynamic Server, Version 11.10.TC2 and IDA, Version 7.5.1. This tutorial uses the superstores_demo database that has been created using the dbaccessdemo9 script in the INFORMIXDIR\bin directory.


Using IDA with Informix Dynamic Server

Introduction to InfoSphere Data Architect

IBM InfoSphere Data Architect (IDA), formerly Rational Data Architect, is a collaborative data design solution to discover, model, relate, and standardize diverse and distributed data assets. IDA is a fundamental tool in any enterprise data environment because it enables you to:

  • Increase data quality and enterprise consistency by providing the following capabilities:
    • Establish and reuse a logical data model as the semantic hub for enterprise architectures
    • Automate data design tasks, such as being able to generate a physical design for a particular data source from a logical design; IDA supports heterogeneous data environments across DB2, Informix, Oracle, Microsoft, Sybase, and mySQL
    • Reverse engineer a physical model from an existing database schema and compare and synchronize data models
    • Analyze and enforce compliance to enterprise naming and data element definition standards
    • Create rules to generate names that comply to a standard or to rules you define
    • Import a business glossary or integrate with InfoSphere Business Glossary
  • Simplify and accelerate integration design by allowing you to
    • Visualize the structure of your existing heterogeneous data sources
    • Understand the relationships of these data sources based on characteristics of the data or on column metadata
    • Define a relationship between existing data assets by using point-and-click
  • Drive greater alignment, control, and efficiency in the software development process with the following capabilities
    • Integrate with other Rational Software Development tools including Rational RequisitePro, WebSphere Business Modeler, Rational Software Modeler, Rational Software Architect for WebSphere Software, and Rational ClearCase
    • Simplify collaboration among business analysts, architects, developers, and administrators
    • Combine tools within the same Eclipse instance to move between data-centric roles or share objects across geographically distributed teams

Product manager Anson Kokkat has a blog entry on why the product was re-branded to InfoSphere and how it is a key component of InfoSphere Foundation Tools. But the basic uses of the product have not changed, and we think Informix DBAs and modelers can get a lot of benefit from using it.

Launching IDA

Launch IDA by going to Start > All Programs > IBM Software Delivery Platform > IBM InfoSphere Data Architect 7.5.1 > IBM InfoSphere Data Architect, as Figure 1 illustrates:

Figure 1. Launching IDA
Launching IDA

When the product is launching, it will ask you for a default workspace location. Please pick either the default or give it an appropriate directory location and click on OK. This launches the product with the default perspective.

Connecting to an IDS instance

After launching the product, you should first connect to an Informix database instance. In order to do that, make sure that the Data perspective is showing, as Figure 2 shows. A data perspective has the "Data Project Explorer" showing on the top left corner and a "Data Source Explorer" showing on the bottom left corner.

Figure 2. Data perspective
Data perspective

If the Data perspective is not active, go to Window > Open Perspective > Data, or go to Window > Open Perspective > Other, and select Data, as Figure 3 illustrates:

Figure 3. Switching perspectives
Switching perspectives

Locate the Data Source Explorer in the lower left hand corner of the screen, as Figure 4 shows:

Figure 4. Data Source Explorer view
Data Source Explorer view

Once that is done, go to the "Data source Explorer" view and right click on Databases > New. A wizard pops up; Informix from the left side and enter the other details relevant to your server, as shown in Figure 5:

Figure 5. Connecting to Informix
Connecting to Informix

Note: To add the location of the JDBC driver, click on the triangle next to the JDBC Driver drop-down list in the New Connection wizard (highlighted above in red). You can use the Informix JDBC driver or the common IBM Data Server Driver for JDBC and SQLJ that provides connectivity to both IDS (11.0 and later) and DB2. To use the common driver, the Informix server has to be configured with DRDA.

Click on Test Connection to make sure your connection parameters are correct and click on Finish when you are done. This uses the defaults for all the other steps and gets you connected to the database. You can then click on the database and explore into the different database objects, as shown in Figure 6:

Figure 6. Navigating into Informix objects
Navigating into Informix objects

Reverse engineering models from the database

This scenario shows how to create a model from your existing database schema. You perform these steps when you want to be able to start using models to document your database changes and to communicate with other members of the team.

  1. Go to File > New > Data Design Project.
  2. Name the project "storesModel", and click on Finish. You should be able to see the project in the Data Project Explorer, as Figure 7 shows:
    Figure 7. Data Project Explorer
    Data Project Explorer
  3. Expand the storeModel project, right-click on Data Models, and select New > Physical Data Model.
  4. Follow these steps in the New Physical Data Model wizard to create a physical model from the existing database.
    1. From the Database drop down, select Informix, as shown below in Figure 8:
      Figure 8. Step 1 of the Physical Model Wizard
      Step 1 of the Physical Model Wizard
    2. Make sure the Create from Reverse engineering radio button is selected, and then click Next.
    3. Pick superstores_demo (as shown in Figure 9) for the connection and click Next:
      Figure 9. Step 2 of the Physical Model Wizard
      Step 2 of the Physical Model Wizard
    4. On the Select Schema page, pick a schema and click Next. In the example shown in Figure 10, vgopal is selected:
      Figure 10. Step 3 of the Physical Model Wizard
      Step 3 of the Physical Model Wizard
    5. In the Database Elements page, de-select all of the database elements except "Tables" and "Indexes", as shown in Figure 11. (As you can see, you can create models for several elements. For this tutorial, we just want to create a model for all the tables in the database).
      Figure 11. Step 4 of the Physical Model Wizard
      Step 4 of the Physical Model Wizard
    6. Click Finish.
    7. Congratulations, you've just created a physical model from the existing database. You can see the model objects created by exploring into Data Models in the Data Project Explorer, as shown in Figure 12:
      Figure 12. Physical Model for superstores_demo
      Physical Model for superstores_demo

You can also look at overview diagrams for the elements in the model. You can either pick all the elements or you can explicitly select the elements that you want to see.

Let's look at some overview diagrams for our tables:

  1. From the Data Project Explorer, navigate to the data model you created in the previous step by selecting storesModel > Data Models > Database Model.dbm > superstores_demo > vgopal > Diagrams. Right-click on Diagrams and select New Overview Diagram.
  2. Make sure the Infer Implicit relationships check box is checked.
  3. Select the Customer, Orders, and Items tables.
  4. Click on Finish. Because you checked the "Infer Implicit relationships" box in the previous step, you can see that the diagram has also picked up the tables Stock and Location because of foreign key relationships from the original tables you chose.

You can look at the table definitions, the primary keys, foreign keys, and user-defined types either by navigating through the explorer on the left or by clicking on the object in the diagram itself. For example, if you click on the Customer object, you can then see its properties in the Properties View at the bottom of the screen. For the orders table, the properties are displayed in Figure 13. Just click on the orders table in the diagram and look at the different tabs in the Properties view.

Enhancements with IDA 7.5.1: With IDA 7.5.1, there have been several enhancements in the underlying support for IDS. You can see additional attributes for elements that you could not see with the 7.5 release. For example, the table properties show whether the table is a "RAW" table, and you can also see and set the Extent sizes and other properties. There are enhancements to trigger, index, and constraint definitions as well. In Figure 13, below, the highlighted portion shows some of the additions for a table:

Figure 13. Properties view for a table
Properties view for a table

What can you do now?

This section contains some optional things you can try on your new physical model:

  • Analyze the model for conformance to enterprise standards
  • Generate appropriate DDL for the target database, such as Informix Dynamic Server
  • Publish the physical model
  • Transform to a logical data model
  • Compare with original physical schema

Analyze model for conformance to enterprise standards

By analyzing the model, you can ensure that your model conforms to certain database design rules, naming standards, and more.

  1. Right-click on the model, as shown in Figure 14, and select Analyze Model:
    Figure 14. Analyzing your model
    Analyzing your model
  2. Choose the rules that you want to enable for analysis, as shown in Figure 15. The result is a detailed analysis.
    Figure 15. Analyzing your model
    Analyzing your model

Generate DDL

Right-click on the model and choose Generate DDL. This opens up a wizard, as shown in Figure 16. (Note that you can issue a Generate DDL at either the individual schema level or for the entire database physical model). Pick up the model elements you want DDL generated for, and, in the third step of the wizard, you can select the Run DDL on Server check box. This enables you to run the DDL on the data server directly after the script gets generated. The script, by default, gets stored in the project directory.

Enhancements with IDA 7.5.1: Extended syntax generation over the earlier release for different elements, like tables (RAW table, extent size support), triggers (matching Informix more closely), indexes, and constraints.

Figure 16. Generate DDL wizards
Generate DDL wizard

Publish the physical model

To publish the physical model, highlight any of the model files in the Data Project Explorer (.ldm, .dbm, or .ddm files), then go to the main toolbar, click on Data > Publish > Web, and give the location of a destination folder, as shown in Figure 17. You can then see the model by opening the index.html file created in the destination folder.

Figure 17. Publishing a model
Publishing a model

Transform to a logical model

Creating a logical model lets you keep a record of your data model and can be used by model architects as they usually use logical data models instead of physical models.

Click on the physical model in the Data Project Explorer (Database Model.dbm) and then click on the main toolbar button highlighted in Figure 18:

Figure 18. Convert from physical to logical model
Convert from physical to logical model

Compare with the physical model

You can compare your physical model to the database at the entire database level or a single table level. Suppose you added a deliver_date column to the Orders table in the model. You can compare with the source database by right-clicking on the Orders table and then selecting Compare with > Original source.

You will see a comparison window pop up in the central editor, as in Figure 19:

Figure 19. Comparing physical model to database
Comparing physical model to database

This comparison dialog allows you to compare any changes between your physical model and your database. Also, the comparison dialog lets you analyze change impacts, copy changes over, and generates the differential DDL for you.

  1. Copy over the changes from the left to the right by clicking on the leftmost highlighted button, as shown in Figure 20.
  2. Then, generate the ALTER DDL by clicking on the rightmost highlighted button, as shown in Figure 20.

This capability is very powerful by letting you manage changes selectively.

Figure 20. Comparing physical model to database
Comparing physical model to database

Starting top down from logical models

Logical modeling deals with gathering business requirements and converting those requirements into a model. The logical model revolves around the needs of the business, not the database, although the needs of the business are used to establish the needs of the database. Modeling architects deal with logical modeling. When the model design meets the necessary requirements, they get translated to physical models and finally get deployed as physical artifacts to the database. This is a more typical scenario for new applications. Here is how you do it.

  1. Right-click on the storesModel project, and select New > Logical Data Model. Give the model the name, storesLogicalModel and then click on Finish. A new Editor will come up on the right of the screen, as Figure 21 shows:
    Figure 21. Creating a new logical model
    Creating a new logical model

    Note: Because this is at the logical model level, we will deal with logical level items rather than database level items. So, in this model, we add entities, attributes and relationships and the like. When this model gets transformed to a physical model, the entities get mapped to tables, attributes get mapped to columns, and so on.

  2. Let's create the model of a simple Book Store, as shown in Figure 22. The entities in this model are Book, Author, and BookCategory. For each of the Book entities, there could be more than one Author, and each Book gets slotted into one or more BookCategory entities. So there is a one-to-many relationship between Book and Author entities and BookCategory entities.
    Figure 22. Model that we will be creating
    Model that we will be creating
  3. Create the entities and attributes using the Palette designer to the right of the diagram window, as shown in Figure 23:
    Figure 23. Creating an entity
    Creating an entity

    You can add elements to the entity by right-clicking (or hovering) on the entity and using the two buttons that appear (as shown in Figure 24) to add a Primary Key or attributes. Alternatively, you can add attributes in the Property Dialog on the bottom.

    Figure 24. Adding attributes
    Adding attributes

    Follow these steps to create all three entities and their primary keys and relationships:

    1. Create the Book entity.
    2. Add primary Key BookID.
    3. Add Attributes ISBN, DatePublished, BookTitle, and BookComments.
    4. Set the types, as shown in Figure 25:
      Figure 25. Properties of the Book entity
      Properties of the Book entity
    5. Create the BookCategory entity.
    6. Add primary key BookID.
    7. Add attributes CatCode and CatDes.
    8. Set the types, as shown in Figure 26:
      Figure 26. Properties of the BookCategory entity
      Properties of the BookCategory entity
    9. Create the Author entity.
    10. Add primary keys BookID and AuthorID.
    11. Add attributes AuthorName and AuthorBio.
    12. Set the types, as shown in Figure 27:
      Figure 27. Properties of the Author entity
      Properties of the Author entity
  4. Add relationships by using the Palette designer. Alternatively, hover over the parent entity and drag the arrow to the desired child entity, as shown in Figure 28:
    Figure 28. Adding relationships
    Adding relationships
    • Create a relationship between Book and BookCategory. Drag the arrow Book to BookCategory, select Create Identifying Foreign Key Relationship, and pick Use the Existing Child Attribute/Column in the dialog that shows up.
    • Use the exact same method to create a relationship between Book and Author.
  5. Once the logical model has been created, save the model by clicking on the Save button in the main toolbar. You can use the property view to change the type or any other detail for the entities.
  6. Once you are done with your logical model, let's transform it to a physical model. Here are the steps to accomplish this:
    1. In the storesModel project, navigate your way to storesLogicalModel.ldm.
    2. In the main toolbar on top, click on the Transform to Physical Data Model button. Refer to Figure 18 to see where the button is. It is located next to the Transform to logical model button.
    3. Select Create New Model and click on Next.
    4. Choose Informix from the database drop down and click on Next.
    5. Leave defaults on in the next screen and click on Next.
    6. Click on Finish.

This creates a folder called storesLogicalModel.dbm in the Data Models folder and also in the Data Diagrams folder. Look at the diagram, and you will see Tables and Columns in the physical model, as Figure 29 shows:

Figure 29. Physical model for the BookStore
Physical model for the BookStore

Things you can do now

Now that you have a physical model, you can enhance the model further by adding any database-related entities (like indexes and triggers). For example, to add a trigger to the table Book, right-click on Book > Add Data Object > Trigger (Figure 30) and define the logic that goes into your trigger. Remember to save it:

Figure 30. Add a trigger to the Book table
Add a trigger to the Book table

Also, you can generate DDL statements and deploy them to the database. Do this by right-clicking on an object (such as a table) and then clicking on Generate DDL. Figure 28 shows you how to get to the Generate DDL option. Using the wizard, you can either save the script or run it on the server. You can create a single script for your entire physical model as well.


Enhancing your model then syncing it up with the physical model

At some point in time you may have to enhance your logical model and then move the changes to your physical model. You can do this by simply adding a new logical entity to the logical model and then comparing the model with the physical model. Say you want to change your Bookstore example to be a Library now. In order to do this, you need a new Library entity but want to keep all of the other existing entities. In other words, the new logical model will have Library, Book, Author and BookCategory as the logical entities.

Here are the steps:

  1. Add a Library Entity with two TimeStamp fields — DateCheckedOut and DateDue — to the logical model using the steps described in the previous section.
  2. After that, create a relationship between Library and Book using BookID. As a reminder, highlight the Book entity, and after you see two boxes, drag it from the Book entity to the Library entity.
  3. Transform from a logical to a physical model as described in Step 6 of the previous section. In this instance, however, choose Update Existing Model and click on Browse to pick up the storesLogicalModel.dbm file.
  4. Once that is done, you will see a compare screen like the one shown in Figure 31, below, showing you what the differences are between the two models. From this screen, you can specify what you would like to do with the differences (copy the change or ignore the change). For example, to merge changes from the physical model to the database you can:
    1. Highlight the Library entry.
    2. Use the toolbar (shown circled in Figure 31) to copy over the LIBRARY change from the logical to the physical model.
    3. Click on the Generate DDL button (which is circled in Figure 31) to generate the necessary DDL for the delta change.
    Figure 31. Syncing up logical with physical models
    Syncing up logical with physical models
  5. Once the changes are done, you can go to the physical model and change the column types. Because the TIMESTAMP type gets transformed to a CHAR(10) type (this is the default, and can be changed using the preferences option described below), you can change it to an Informix-specific DATETIME YEAR TO SECOND data type for both the DateCheckedOut and DateDue columns. You can also do this when the transform is taking place as well when the changes are copied over from the logical to the physical model.

Modifying data type mapping preferences

There is extensive support for the Informix data types when you are working at the physical model level, including BYTE, TEXT, NCHAR, NVARCHAR, LVARCHAR, BOOLEAN, INTERVAL, DATETIME in addition to the other basic types as shown in the Appendix.

You can set up the type conversions to be used when going from logical to physical models and physical to logical models by configuring them in the IDA preferences for a particular database. You can also add to the list if you find something is missing from the pre-existing list.

  1. Bring up the Preferences window from the top toolbar by going to Window > Preferences.
  2. Navigate the tree by expanding the Data Management section (Figure 32), then going to Transform > Data Type Map, and then click on Logical to Physical . This brings up a set of type mappings on the right side.
  3. Select Informix from the Database vendor type drop-down list, and then click in the Physical Data Type field to change the mapped-to data type. For this example, modify the default TIMESTAMP mapping to map to a physical data type of DATETIME YEAR TO SECOND then click on Apply and OK. This will be used for all future type mappings.
Figure 32. Setting up preferences
Setting up preferences

Enforcing consistency by attaching domain elements to your model

A domain model describes the domain types that an organization allows and their constraints. Using domain data types instead of base data types ensures that you maintain consistency across an organization, and allows reuse of common data type definitions for greater team efficiency.

You can add some domain-specific rules into the model by creating a domain model and then attaching the domain level elements to your logical or physical model. In this example, the Library logical model is extended to add a Member Entity. The Member entity has MemberID, Address and License Attributes. The License field has protected information considered to be of PII (Personal Identification Information) category, so it has to have certain special rules applied. For this to happen, you have to create a domain model first.

  1. Navigate to the root of the storesModel project and right-click on New > Domain Model.
  2. Give it the name LibraryDomainModel and then click on Finish.
  3. Navigate to LibraryDomainModel.ddm in the Data Project Explorer and then right-click on Package1 and select Add Data Object > Atomic Domain.
  4. In the Properties tab shown below, set the fields as follows in the Properties view:
    • Enter LicenseType for the Name field
    • Select CHAR from the Base Type drop-down
    • Set the Length field to 20
  5. In the Privacy tab:
    • Select PII from the Classification drop-down
    • Use the Masking Method field to set a masking method. (Masking methods are employed by the IBM Optim Data Privacy Solutions and Test Data Manager to mask data when the data is being extracted for test purposes, thereby hiding the original data. IDA 7.5.1 provides this integration with IBM Optim 6.x. You can export the model to an Optim-specific file format and then use the Optim tool for test data extraction purposes. See Resources for a link to the Data Architect e-Kit, which includes a tutorial on how to do this — "Manage data privacy from design to development" (developerWorks, December 2008).

After you have created your domain model, you can add a new logical model entity for Member with details as shown in Figure 33 and set its attributes to:

  • MemberID - Integer
  • Address - character - length 50
  • License - LicenseType
  • Also, add a relationship between Library and Member using MemberID

You can then translate the model to a physical model, and also export the physical model to the Optim Format for use by the Optim Test Data Manager product.

Figure 33. Final LogicalModel
Final LogicalModel

Conclusion

Hopefully, this tutorial has helped you see some of what you can do with IDA and Informix Dynamic Server. IDA has extensive support for Informix, which can help modeling architects create models and convert them into physical database artifacts for a wide variety of different databases. In addition, IDA can shell share with other Data Studio and Rational Software Development components, thereby providing a single IDE to do your database modeling and development. This means you create a smaller memory footprint, allow for common sharing of artifacts like connections, and have all the features in a single platform.

Acknowledgements

The authors would like to thank Anson Kokkat, Seeling Cheung, and Kathryn Zeidenstein for reviewing the document and providing valuable comments.


Appendix: Model elements support for Informix servers

There is extensive support for Informix servers in InfoSphere Data Architect. Most of them have been enhanced with IDA 7.5.1. Here is a list on what all database elements supported in the tool for Informix, including Informix Data types:

  • Elements supported in the different models
    • Schema
    • User
    • Role
    • Table
    • View
    • Synonym (Alias)
    • Distinct user defined type
    • Stored Procedure
    • Function
    • Sequence
    • Column
    • Index
    • Foreign Key
    • Constraints
    • Trigger
  • Informix Data types supported
    • Blob
    • Boolean
    • Byte
    • Char
    • Clob
    • Date
    • DateTime
    • Decimal
    • Double
    • Float
    • Int8
    • Integer
    • Interval
    • LVarchar
    • Money
    • NChar
    • NVarchar
    • Numeric
    • Serial
    • Serial8
    • Smallint
    • Smallfloat
    • Text
    • Varchar
    • Other user defined types that you have created
  • Generation of DDL for all of the above elements
  • Generation of delta DDL when changes are made to the physical model and need to be synced up with the database. This generates appropriate ALTER statements (for instances like addition of a new column to a table, or changing the data type for a column to a compatible data type, and so on), or a more complete set of statements for managing the changes.

Resources

Learn

Get products and technologies

Discuss

  • DataStudio blog: Find advice, hints and tips, and lessons learned from Data Studio experts and their experiences working with customers around the world.
  • developerWorks blogs: Participate in developerWorks blogs and get involved in the developerWorks community.

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, Rational
ArticleID=375754
ArticleTitle=Data modeling with InfoSphere Data Architect and Informix Dynamic Server
publish-date=03122009