DB2 9.5 and IBM Data Studio Part 3: Overview diagrams - the basics

Tuesday Nov 13th 2007 by Paul Zikopoulos

Part 3 of this series examines more deeply the overview diagrams introduced in part 2.

As I’ve been introducing you to the IBM Data Studio integrated development environment (IDE) that’s new with DB2 9.5, I’ve shown you how to set up database connection objects and the actions that you can perform on them. In this article, I want to delve deeper into the overview diagrams that I introduced you to in Part 2 of this series and show you just how powerful this feature is.

Note: In earlier parts of this series, I referred to this toolset by its name as of the DB2 Viper 2 Beta 2 version. As you’ll see, this has now changed as DB2 Viper 2 has been officially named DB2 9.5, and the IDE has been officially dubbed IBM Data Studio. In Beta 1, it was referred to as Viper Studio. In this article, I’ll continue using the newly announced names for both DB2 and the IBM Data Studio IDE. Since the new names will have now been in effect for two parts in this series, I’ll no longer explain the terminology change in subsequent articles.

Assumptions if you’re starting here...

I recommend that you start with Part 1 because I tend to build on the concepts introduced and objects created in this series sequentially. For this article, I assume that you have a live database connection to the SAMPLE database, and that this connection is filtered such that the user account used to create the SAMPLE database is the only schema that shows up in the Database Explorer view. For example:

Note: You can perform most of the steps in this article without a live connection to the database, by using a database connection object that’s been enabled for offline work. For more information on this capability, refer to Part 2 in this series.

In addition, I assume you’ve run the following data definition language (DDL) and data manipulation language (DML) statements on the SAMPLE database (changing the schema to match yours):

(1,'Canada'), (2,'USA'),(3,'Australia'),(4,'Greece')


VALUES ('George', 'Baklarz', 1),
('Roman', 'Melnyk', 1),
('Anastasia', 'Zikopoulos', 4),
('Glen', 'Mosely', 2),

To keep things simple: filter the tables in your schema

In Part 2 I showed you how to create an overview diagram; the generated diagram got pretty messy because there are a lot of schema objects in the SAMPLE database. For the purposes of this article, before you create the overview diagram (and to keep things simple), perform the following steps:

1.  Select the Tables folder, right-click, and select Filter:

2.  Enable a table filter using the Selection option such that only the DEPARTMENT, DJFRIENDS, DJCOUNTRIES, EMPLOYEE, ORG, SALES, and STAFF tables are displayed in the Tables folder. Your table filter definition should look like this:

3.  Click Finish. Once the filter is applied, the Database Explorer view should look similar to this:

Creating the overview diagram

Create an overview diagram for this article using the following steps:

1.  Select a live database connection object.

2.  Expand the database connection object tree until you locate the Tables folder, right-click, and select Add to overview diagram. The Overview Diagram Selection window opens.

3.  Name this overview diagram SAMPLE (DJ FILTERED TABLES) using the Diagram name field.

4.  Select the Infer implicit relationships check box. The Overview Diagram Selection window should now look similar to this:

5.  By default, the tables that are included via your defined filter on the Tables folder are selected. For this article, keep the default selections and click OK.

6.  Adjust the focus of your overview diagram until it looks similar to the following figure. (You should be able to simply move the slide bar to the right to get this view of your diagram.)

Note: Since you selected the Infer implicit relationships option when creating this overview diagram, you may see some relationships you didn’t expect. For our example, highlight any relationships between the DJFRIENDS and STAFF table (by holding your mouse button down and selecting the connecting lines), and delete them using the Delete key. In addition, click and hold your mouse button, select the STAFF and SALES tables and drag them to the right until they are out of focus in your overview diagram. Your overview diagram should look like this:

Working with overview diagram properties

You can do lots of things with an overview diagram. I’ll cover the properties you can change in this article and the rest of the capabilities in Part 4.

The Filters tab

As discussed in the previous parts in this series, you use the Properties tab to work with selected objects. This tab is dynamic: as you switch focus between highlighted objects, the tab changes to show the corresponding properties that are available to set.

In the following example, you can see that I’ve selected two separate objects and that each has its own set of corresponding properties:

Click for larger image

To select the properties for the entire overview diagram, click any white space in the diagram. When you are working with the properties of the overview diagram in general, the Properties tab should look similar to this:

Click for larger image

It’s outside the scope of this series to go into all of the properties available for all objects; you can learn more about this wonderful feature by experimenting on a test system. It is, however, worth detailing the general properties of overview diagrams and how they affect the overview diagram itself.

Compartment display options

Use the fields in this box to specify which objects are displayed in the overview diagram. For example, the Show key option will place a key icon () between key fields in your table. The DJFRIENDS and DJCUSTOMER tables each have primary keys, and this option makes that fact quickly visible. The Show non-key option shows all of the non-primary key columns on the table, including an icon to quickly identify foreign key relationships (). As you might expect, Show index adds indexes to the overview diagram in the same manner that the Show trigger options shows any triggers that are defined on the table.

If you selected all of these options, the designer palette would look like this:

Click for larger image

In our example, selecting the Show trigger option simply shows an empty box because we haven‘t created any triggers on the DJCOUNTRIES and DJFRIENDS tables. Note, however, that when you create a primary key, a corresponding unique index is created and this is why, if you select Show index, you don’t get an empty box as in the case of the trigger.

Foreign key relationship display options

You use this box to work with the display options as they relate to any foreign key relationships within the overview diagram. By default, the Show name and Show label options are selected for any new overview diagram. (You can’t use the Show label option unless you’ve selected the Show name option.) If you select the Show referential integrity check box, the type of foreign key constraint is also added to the overview diagram. These options are shown below:

Table display options

The Show qualified table name option fully qualifies the table with the corresponding schema to which it belongs, as shown below:

Column display options

The column display options for overview diagrams are pretty self-explanatory. You can see the data types of each column using the Show data types check box. You can include the NULLability characteristic of a column using Show nullable decoration. You can identify foreign keys within columns using Show foreign key decoration (as opposed to showing the foreign key relationships between tables, which we looked at earlier). An example of these options is shown below:

The Format tab

The Format tab in the Properties tab lets you work with the display options of your tables, views, columns, and foreign key relationships. In the following figure, you can see that I’ve elected to focus on tables in the overview diagram by changing their color to purple using the Table background color field. Since I’m not that interested in any views that rely upon these tables, I’ve chosen to leave the COUNTRIES view’s background white. (You can see that the COUNTRIES view is built on the DJCOUNTRIES table by the relationship arrow between the objects in the overview diagram.) I’ve also leveraged the Foreign key column foreground color box such that I can use a color scheme to quickly point out the foreign key in the table beyond looking for the [FK] moniker that was added using options discussed in the previous section. Since no implicit foreign key relationships exist between these tables, I’ve left the Implicit foreign key relationships line color box set to the default. These settings and their effects on the overview diagram are shown below:

The General tab

The General tab is used to provide metadata about the overview diagram. Specifically, it contains the name you gave the diagram as well as the type of notation:

The Documentation tab

You can use this tab to document anything you want in the diagram. In my next article, I’ll show you how to put annotations on specific objects in the overview diagrams; however, this tab is for information that you want to maintain but may not want to append directly to objects. For example:

The Appearance tab

You can use the Appearance tab to change the font, point size of the font, and highlighting of text within an overview diagram. For example, in the following figure I’ve defined all labels to display in a bolded, 10-point Times New Roman Baltic font:

The Advanced tab

The Advanced tab allows you to set all kinds of advanced features that are outside the scope of this article. For example, you can set the page width and height where the overview diagram is displayed and more. Another thing you can do with this tab is change the name of the overview diagram; this is the only way to do this once it is created.

Once Last Thing...

Unfortunately, you can’t save overview diagrams as objects from the Database Explorer view; however, you can save them as images for future reference. This means that, for the next part in this series, you’ll have to start from scratch, but as you can see from this article, building the overview diagram and defining its properties is a simple thing.

Wrapping it up...

In this article, I took you through the properties you can set with an overview diagram. In the next part of this series, I’m going to complete my discussion of overview diagrams.

» See All Articles by Columnist Paul C. Zikopoulos

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than 13 years of experience with DB2 and has written more than 150 magazine articles and is currently working on book number 12. Paul has authored the books Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, 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). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA class, and trying to figure out the world according to Chloë – his daughter. You can reach him at: paulz_ibm@msn.com.


IBM and DB2 are registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, or service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2007. All rights reserved.


The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.

Mobile Site | Full Site