DB2 9.5 and IBM Data Studio Part 3: Overview diagrams - the basics
November 13, 2007
As Ive been introducing you to the IBM Data Studio integrated development environment (IDE) thats new with DB2 9.5, Ive 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 youll 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, Ill 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, Ill no longer explain the terminology change in subsequent articles.
Assumptions if youre 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 thats been enabled for offline work. For more information on this capability, refer to Part 2 in this series.
In addition, I assume youve run the following data definition language (DDL) and data manipulation language (DML) statements on the SAMPLE database (changing the schema to match yours):
CREATE TABLE PAULZ.DJCOUNTRIES (COUNTRY_ID SMALLINT NOT NULL, COUNTRY_NAME VARCHAR (50) NOT NULL, CONSTRAINT COUNTRYIDPK PRIMARY KEY (COUNTRY_ID)) INSERT INTO PAULZ.DJCOUNTRIES VALUES (1,'Canada'), (2,'USA'),(3,'Australia'),(4,'Greece') CREATE TABLE PAULZ.DJFRIENDS ( ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 10), FIRST_NAME VARCHAR (50), LAST_NAME VARCHAR (50) NOT NULL, COUNTRY_CODE SMALLINT NOT NULL, CONSTRAINT PK_ID PRIMARY KEY ( ID), CONSTRAINT FKTOCOUNTRYID_COUNTRY_CODE FOREIGN KEY (COUNTRY_CODE) REFERENCES PAULZ.DJCOUNTRIES (COUNTRY_ID) ON DELETE RESTRICT ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION) INSERT INTO DJFRIENDS (FIRST_NAME, LAST_NAME, COUNTRY_CODE) VALUES ('George', 'Baklarz', 1), ('Roman', 'Melnyk', 1), ('Anastasia', 'Zikopoulos', 4), ('Chris','Neilson',3), ('Glen', 'Mosely', 2), ('Cindy','Munns',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 didnt 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: