So That's What My Database Looks Like

Published: July 30, 2008

by Paul Tuohy

Today's databases can be complex creatures with lots and lots of dependencies. Once upon a time all you had to worry about was what logical files were built over a physical and whether or not it was attached to a journal. But today you can have view, indexes, constraints (key, foreign key and check), triggers and journals related to a table (physical file).

You can make use of the Display Database Relationships (DSPDBR) and Display File Definition (DSPFD) to see these relationships but, let's be honest; these aren't exactly the easiest reports to read. Alternatively, you could use some of the list APIs and roll your own analysis tool.

Or you could simply use some of the built-in tools in iSeries Navigator.

Dependencies

If you are just looking for a list of all dependencies for a table, in iSeries Navigator, expand Database→Your Database→Schemas→Your Schema→Tables, right click on the required table, and select Show Related from the context menu. Figure 1 shows the objects related to the table SCHEDULE in the schema (library) SIDSTUFF.

Figure1: Objects related to a table.

But the Show Related window is not just for displaying database objects related to a table; it can also be used to maintain any of those database objects. Right clicking on an object provides the same context menu available in the main database window, as shown in Figure 1.

Database Navigator

The Show Related option is quite useful when it comes to seeing the actual objects that are dependent on a table. But it doesn't always give you the full picture. For example, if one of the dependent objects is a view or a join logical that is joined to one, or more, other tables, you need to look at the definition of the view to see what that table is.

But iSeries Navigator has another option that can show you all the details. Actually, it will draw a picture of your database for you. In iSeries Navigator, expand Database→Your Database, right click on Database Navigator Maps, and select New→Map from the context window. In the resulting window, the left-hand panel lists all of the schemas you already have selected in the Database option in Navigator (you can add to the list or change it here); expand the required schema, expand the Tables option and right click on the required table to see the context menu shown in Figure 2.

Figure 2: Adding a table to a database navigator map.

Take the option to Add to Map. After viewing a progress window for a few moments (I particularly like the bit with the paint brush toward the end) you will see a basic picture of your database, as shown in Figure 3.

Figure 3: The initial map.

Yes, it is small and difficult to read but you can zoom in and out (more in a moment), and you will notice that simply pointing at any object in the map gives you details about the object, as shown in Figure 3.

At the right side of the toolbar are option buttons to show or hide indexes, views, journals, journal receivers, primary key constraints, check constraints, unique key constraints, table aliases, view aliases, triggers, materialized query tables, and table partitions. A button will be grey and unavailable if there is none of that item in a map. Clicking on the available buttons can change the picture dramatically, as shown in Figure 4.

Figure 4: The map with all available items selected.

By default journals and journal receivers are not included in a map, but you can include them by selecting Options→User Preferences from the menu.

Use the zoom in and zoom out buttons on the toolbar to size the map to your requirements. As with Show Relations, all the options that are available in the main Navigator window are also available on the context menu for any object in the map, as shown in Figure 5.

Figure 5: Zooming in and right-clicking for a context menu.

To help navigate around the map, click on the Show Overview Window button (to the right of the zoom buttons) to get a separate window to help you move around the map, as shown in Figure 6.

Figure 5: The overview window.

Options are available to structure the map in circular, hierarchical, or symmetric formats, and you can also drag and drop any object to position objects as required. Remember to save the map when you've finished constructing it!

In the End

Remember that the use of Database Relations and Database Navigator is not in any way dependent on the database being defined with DDL. You can try it now on one of your traditional databases defined using DDS.

Using Database Relations, you get a view of the database that might be possible to emulate in a 5250 session. But using Database Navigator, you get a true GUI interface that far exceeds anything provided on green screen.

Go on, give it a try.

Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of "Re-engineering RPG Legacy Applications," "The Programmers Guide to iSeries Navigator," and the self-study course called "iSeries Navigator for Programmers." Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot