- Before you start
- Using IDA with Informix Dynamic Server
- Reverse engineering models from the database
- What can you do now?
- Starting top down from logical models
- Enhancing your model then syncing it up with the physical model
- Modifying data type mapping preferences
- Enforcing consistency by attaching domain elements to your model
- Appendix: Model elements support for Informix servers
- Downloadable resources
- Related topics
Data modeling with InfoSphere Data Architect and Informix Dynamic Server
Accelerate data integration projects and create trusted information assets
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.
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
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.
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
- 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.
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
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
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
Locate the Data Source Explorer in the lower left hand corner of the screen, as Figure 4 shows:
Figure 4. 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
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
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.
- Go to File > New > Data Design Project.
- 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
- Expand the storeModel project, right-click on Data Models, and select New > Physical Data Model.
- Follow these steps in the New Physical Data Model wizard to create
a physical model from the existing database.
- From the Database drop down, select Informix, as
shown below in Figure 8:
Figure 8. Step 1 of the Physical Model Wizard
- Make sure the Create from Reverse engineering radio button is selected, and then click Next.
- Pick superstores_demo (as shown in Figure 9) for the
connection and click Next:
Figure 9. Step 2 of the Physical Model Wizard
- On the Select Schema page, pick a schema and click
Next. In the example shown in Figure 10, vgopal
Figure 10. Step 3 of the Physical Model Wizard
- 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
Figure 11. Step 4 of the Physical Model Wizard
- Click Finish.
- 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
- From the Database drop down, select Informix, as shown below in Figure 8:
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:
- 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.
- Make sure the Infer Implicit relationships check box is checked.
- Select the Customer, Orders, and Items tables.
- 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
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.
- Right-click on the model, as shown in Figure 14, and select
Figure 14. Analyzing your model
- Choose the rules that you want to enable for analysis, as
shown in Figure 15. The result is a
Figure 15. Analyzing your model
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
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
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
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
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.
- Copy over the changes from the left to the right by clicking on the leftmost highlighted button, as shown in Figure 20.
- 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
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.
- 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
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.
- Let's create the model of a simple Book Store, as shown in
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
- 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
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
Follow these steps to create all three entities and their primary keys and relationships:
- Create the Book entity.
- Add primary Key BookID.
- Add Attributes ISBN, DatePublished, BookTitle, and BookComments.
- Set the types, as shown in Figure 25:
Figure 25. Properties of the Book entity
- Create the BookCategory entity.
- Add primary key BookID.
- Add attributes CatCode and CatDes.
- Set the types, as shown in Figure 26:
Figure 26. Properties of the BookCategory entity
- Create the Author entity.
- Add primary keys BookID and AuthorID.
- Add attributes AuthorName and AuthorBio.
- Set the types, as shown in Figure 27:
Figure 27. Properties of the Author entity
- 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
- 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.
- 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.
- Once you are done with your logical model, let's transform it to a
physical model. Here are the steps to accomplish this:
- In the storesModel project, navigate your way to storesLogicalModel.ldm.
- 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.
- Select Create New Model and click on Next.
- Choose Informix from the database drop down and click on Next.
- Leave defaults on in the next screen and click on Next.
- 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
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
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:
- Add a Library Entity with two TimeStamp fields — DateCheckedOut and DateDue — to the logical model using the steps described in the previous section.
- 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.
- 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.
- 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:
- Highlight the Library entry.
- Use the toolbar (shown circled in Figure 31) to copy over the LIBRARY change from the logical to the physical model.
- 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
- 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.
- Bring up the Preferences window from the top toolbar by going to Window > Preferences.
- 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.
- 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
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.
- Navigate to the root of the storesModel project and right-click on New > Domain Model.
- Give it the name LibraryDomainModel and then click on Finish.
- Navigate to LibraryDomainModel.ddm in the Data Project Explorer and then right-click on Package1 and select Add Data Object > Atomic Domain.
- 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
- 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 Related topics 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
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.
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
- Synonym (Alias)
- Distinct user defined type
- Stored Procedure
- Foreign Key
- Informix Data types supported
- 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.
- Data Architect e-Kit (developerWorks, December 2008): Learn how IBM solutions can help discover new enterprise information sources and content as well as improve the quality of its information. This e-Kit includes a great tutorial on using InfoSphere Data Architect with Optim Data Privacy Solutions — "Manage data privacy from design to development" (developerWorks, December 2008).
- Best practices: Information Modeling with InfoSphere Data Architect Version 7: Discover Learn the best practices for information modeling based on the experiences of a team of data modelers using IBM InfoSphere Data Architect with DB2.
- "The power of InfoSphere Data Architect" (developerWorks, August 2007): Learn how IDA addresses the issue of data's importance in SOA, and how IDA is used in a business environment.
- "Use InfoSphere Data Architect to integrate data sources" (developerWorks, March 2006): Discover how IDA can be used to discover relationships between data sources and integrate them into a common model.
- "Data Studio Administrator, Part 1: Integrating Data Studio Administrator and InfoSphere Data Architect" (developerWorks, July 2008): Learn about the common look and feel and the strengths of each product, as well as the benefits of combining them together.
- "Using InfoSphere Data Architect to import and export XML" (developerWorks, November 2006): Learn about using XML as a conduit for importing and exporting models and using the information in other products.
- "Use IBM InfoSphere Data Architect to model your Oracle databases" (developerWorks, January 2008): See how IDA can be used with non-IBM databases.
- "InfoSphere Data Architect skills series, Part 1: Access and integrate enterprise metadata with InfoSphere Data Architect" (developerWorks, July 2006): Hone your InfoSphere Data Architect skills. follow a simple scenario to learn how InfoSphere Data Architect can be used to provide a modeling and design tool to create a unified, virtual view across heterogeneous data sources.
- "Migrate ERwin data models to InfoSphere Data Architect" (developerWorks, September 2007): Migrate existing data models created in CA ERwin Data Modeler to InfoSphere Data Architect, and explore some of InfoSphere Data Architect's features data modelers and data architects use on a daily basis.
- "Understanding leads to trust: Sharing a common vocabulary across InfoSphere Foundation Tools" (developerWorks, February 2009): Learn how to work with business metadata and use it to enforce naming standards.
- InfoSphere Data Architect: Download a trial version of InfoSphere Data Architect.
- Informix Dynamic Server: Download a trial version of Informix Dynamic Server Enterprise Edition.