05
Sat, Oct
2 New Articles

Get a Clear Picture of Your Database

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Once upon a time, life was simple when it came to determining dependencies in a database: You simply ran a Display Database Relationships (DSPDBR) command to see all of the logical files that were built over a physical file. Of course, you had to run the DSPDBR command for all the files in your database and then spend some time perusing the reports.

But that was when life was simple. Today, our databases are more complex, given the extra dependencies with such things as referential constraints, check constraints, and triggers. You still use of DSPDBR, but you also have to use the Display File Description (DSPFD) command to see the extra dependencies. You have my infinite admiration if you can easily decipher all of these reports and get a clear view of how a database hangs together.

The good news is that the Database feature in iSeries Navigator offers a couple of alternatives that make it very easy to get a clear picture (and I do mean picture) of the interdependencies in a database: Database Relations and Database Navigator. Please note that although iSeries Navigator uses SQL terminology (table, view, and index) to refer to database objects, you can still use it to access your traditional physical and logical files.

Database Relations

First, select Show Related from the context menu of any table, as shown in Figure 1, to get a list of its dependents.

http://www.mcpressonline.com/articles/images/2002/Get%20a%20Clear%20Picture%20of%20Your%20DatabaseV4--05240600.png

Figure 1: Select Database Relationships from a context menu. (Click images to enlarge.)

Figure 2 shows the resulting Objects Related window, which lists the dependencies for the SAMPLE_EMPLOYEE table. But this is not just a list of related objects; it is also a means of maintaining the related objects. The context menu of any object in the list is the same as the context menu you would get for the object in the main iSeries Navigator window.

http://www.mcpressonline.com/articles/images/2002/Get%20a%20Clear%20Picture%20of%20Your%20DatabaseV4--05240601.png

Figure 2: This is the context menu for a related object.

Database Navigator

Although Database Relations may provide an easier-to-interpret interface than the 5250 command equivalent, it is nothing compared to Database Navigator. Database Navigator draws a map of your database that not only provides a picture of your database and its dependencies, but also allows you to maintain the database.

To create a new map, select New -> Map from the context menu of Database Navigator Maps, as shown in Figure 3.

http://www.mcpressonline.com/articles/images/2002/Get%20a%20Clear%20Picture%20of%20Your%20DatabaseV4--05240602.png

Figure 3: Take the option to create a new map.

The left side of the resulting map window, shown in Figure 4, allows you to specify the database objects that you wish to select for the map. The top pane allows you to search for specific objects, while the bottom pane provides three tabs for selecting database objects: a Schema Tree similar to that shown in the main Databases window, a Schema Table that lists all tables in all schema in the schema list, and an Objects in Map tab that lists all the objects that are in the map. You can change the list of schema by selecting Options -> Change List of Schemas from the menu, but be warned that this also will change the list of schemas displayed for the Databases option in the main iSeries Navigator window.

The easiest way to generate a map is to add tables to it. Adding a table to a map adds the table and all of its dependents. Figure 4 shows the option to Add to Map being taken from the context menu of the SAMPLE_EMPLOYEE table. The context menu also has the usual options that are available for a table in the main iSeries Navigator window.

http://www.mcpressonline.com/articles/images/2002/Get%20a%20Clear%20Picture%20of%20Your%20DatabaseV4--05240603.png

Figure 4: Add the Employee table to the map.

When the option is taken to add an item to the map, Database Navigator displays a Finding Relationships status window that indicates the progress of the operation. After a couple of moments, you will have a generated map, as shown in Figure 5. The minimum of information is shown, but it is interesting to note that other tables are included in the map due to foreign key constraints, views that join the selected table to other tables, or the fact that other tables are journaled to the same journal as the selected table.

http://www.mcpressonline.com/articles/images/2002/Get%20a%20Clear%20Picture%20of%20Your%20DatabaseV4--05240604.png

Figure 5: Now you've generated a map for the Employee table.

To see more details in the map, you must select the relevant icons on the right side of the toolbar or the equivalent options from View -> Show Objects of Type from the menu. If an icon or option is grayed out, it means that no objects of that type are included in the map. By clicking the corresponding icon, you can select to show or hide the following:

  • Indexes
  • Views
  • Journals
  • Journal receivers
  • Primary key constraints
  • Check constraints
  • Unique key constraints
  • Table aliases
  • View aliases
  • Triggers
  • Materialized query tables
  • Table partitions

If you are not sure what an icon represents, simply point at it and a pop-up box will explain it.

Figure 6 shows the result of selecting all icons for the generated map. Isn't it interesting to see the amount of information that is placed in the map from just selecting the Employee table? And it is also a little difficult to read.

http://www.mcpressonline.com/articles/images/2002/Get%20a%20Clear%20Picture%20of%20Your%20DatabaseV4--05240605.png

Figure 6: View all objects in the map.

You can use the Zoom icons on the toolbar (or View -> Zoom from the menu) to zoom in and out on the map. Then, use the horizontal and vertical bars to position the map to the required position. Figure 7 shows the result of zooming in on the map.

http://www.mcpressonline.com/articles/images/2002/Get%20a%20Clear%20Picture%20of%20Your%20DatabaseV4--05240606.png

Figure 7: Zoom in.

To see exactly where you are on the map, select the Show Overview Window icon from the toolbar (or View -> Show Overview Window from the menu) to display an overview window similar to that shown in Figure 8. Dragging the outline in the overview window repositions the image shown in the main map window accordingly, and you can resize the viewing window (zoom in/zoom out) by resizing the outline window.

http://www.mcpressonline.com/articles/images/2002/Get%20a%20Clear%20Picture%20of%20Your%20DatabaseV4--05240607.png

Figure 8: Here's a symmetric view of the Map Overview Window.

These are some of the other features available in Database Navigator:

  • You can change your preferences as to what should be included in the map by selecting Options -> User Preferences from the menu.
  • You can change the position of any item in the map by using the mouse to drag and drop it to the required position; connections to other objects are maintained.
  • You can change the style of the generated map by selecting View -> Arrange -> Circular or selecting View -> Arrange -> Hierarchic from the menu.
  • Flyover help is displayed when you leave the cursor on an object in the window (this can be disabled in user preferences).
  • All objects in the map have a context menu similar to that available in the main iSeries Navigator window.
  • You can change the representation of an object in the map to a more-detailed view by selecting Expand from the context menu of the object.
  • You can add user-defined relationships.
  • It is possible to have an item included in the map but not displayed by selecting Hide from the context menu of the object.
  • You can save the Database Navigator map by selecting the Save icon from the toolbar or by selecting File -> Save or File -> Save As from the menu. A Database Navigator map is actually stored as a table (i.e., a *FILE object). Thus, any maps you create are stored in a library on the iSeries and are not specific to your PC or profile. The list of available maps is shown when you select Database Navigator Maps.

Got the Picture?

Whether through Database Relations or through Database Navigator Maps, iSeries Navigator's ability to show the relationships of database objects far outweighs the 5250 equivalent.

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.

Database Relations and Database Navigator are two of the features that have long been missing from the database. With the advent of triggers and referential integrity, they have become a necessity. You will find it nigh on impossible to track all the relationships and dependencies unless you can see a meaningful overview. And these features are not just a view that shows you the construct of the database, but rather an interface that allows you to directly manipulate it, just as you would from the main Navigator window.

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.

Editor's Note: This article represents the type of information you'll find in the author's new best-selling book, The Programmer's Guide to iSeries Navigator.

Paul Tuohy has worked in the development of IBM midrange applications since the ‘70s. He has been IT manager for Kodak Ireland Ltd. and Technical Director of Precision Software Ltd., and he’s currently CEO of ComCon, a midrange consultancy company based in Dublin, Ireland. He has been involved in lecturing and training since the mid-‘80s.Paul is the author of Re-engineering RPG Legacy Applications and The Programmer's Guide to iSeries Navigator as well as the self-teach course “iSeries Navigator for Programmers.” He is also one of the quoted industry experts in the IBM Redbook Who Knew You Could Do That with RPG IV? He regularly contributes to a number of midrange publications and is an award-winning speaker who frequently appears at U.S. COMMON conferences and at the renowned RPG World conferences.

Paul Tuohy

Paul Tuohy has worked in the development of IBM midrange applications since the 1970s. He has been IT manager for Kodak Ireland, Ltd., and technical director of Precision Software, Ltd., and is currently CEO of ComCon, a midrange consultancy company based in Dublin, Ireland. He has been teaching and lecturing since the mid-1980s. 

Paul is the author of Re-engineering RPG Legacy Applications, The Programmer's Guide to iSeries Navigator, and the self-teach course "iSeries Navigator for Programmers." He is one of the partners of System i Developer and, in addition to speaking at the renowned RPG & DB2 Summit, he is an award-winning speaker at COMMON and other conferences throughout the world.


MC Press books written by Paul Tuohy available now on the MC Press Bookstore.

The Programmer’s Guide to iSeries Navigator The Programmer’s Guide to iSeries Navigator
Get to know iSeries Navigator and all the powerful tools and interfaces that will expand your programming horizons.
List Price $74.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: