Navigation Integration
John Bussert
Over the past few months I've mentioned IBM's Operations Navigator for maintaining system operations activities. Unfortunately, many people say they tried the navigator in earlier releases when it was painfully slow and didn't have much functionality, thus tainting their view. It's worth a look, if you're on V4R4.
This issue I'd like to look at some of the things it can do specifically to help manage multiple systems and databases.
I've found several downfalls. One is that it doesn't have the same interface as IBM's DB2 UDB product. This isn't a requirement, but when dealing with databases it would be nice to learn and deal with just one interface. The other is that every once in a while it crashes or locks up. But, just like with other Windows programs, I've learned to ignore some of this and use the three-finger salute (CTL/ALT/DLT). If you're using Windows NT, this is less of a problem, but it doesn't go away completely.
During the install of the Client Access program, you'll want to choose the custom install options to get all its functionality, but, to be sure, you don't want users with all this capability. For that reason, IBM defaulted it to load just enough to run a user's job. If you forget this step, you can (and must) reinstall it.
After loading, go to the databases icon and get properties (you know the drill--left click, etc.). Here you can select the libraries to manage, so select the data libraries you want to use for your application. You could use all of them, but you'll have to wait forever while it builds a list of the files in the library.
Once you select, you'll see the objects in the library with the tables (that's physical files to you non-SQL folks) first, followed by the views (logical files). Now that you can see them, you can manipulate them. Pick a physical file, right click on it, and then select properties.
A whole new world opens up to you. Inserting tabs for triggers, constraints, keys, referential integrity and other elements of file maintenance can be done from here. You can add a field to a table, add a primary key to a physical file that's not keyed, or even add cascading deletes for header/detail type files!
So what, you ask? You can do the same thing with commands on green screens. Well after you've used it a few times, the answer is obvious-you don't have to use the green screens and you don't have to remember the commands (or look them up, either). Also, when you save the change, it does the SQL "alter table" or whatever change function is required without you knowing what to do.
Quick, what is the syntax for the SQL create vector index? If you don't know, use the Navigator and click the Vector Checkbox in the indexes panel and it will walk you through creating it.
There are a couple of initial fears that you'll go through. The first is that you do not have DDS for the things you're doing. This is very scary at first, but after a few times you'll start to see where some of the productivity comes from. You don't need DDS! Hmmm...
Don't get me wrong; I'm not suggesting you use this tool to do changes on the fly to production tables. You still need to know what you're doing and why. But now you can. And it can make an unbelievable impact on your development.
Let's say you have a program that's allowing blanks in your item number when the user adds a record to the item master. You know you shouldn't have blanks, don't want blanks, but you can't find it. Is it a program adding a blank, or is it a program that is blanking the key? Well, create a constraint on inserts or updates that checks for BLANKS in the key. You don't need to write any application code.
There is a gotcha here though--you need to have journaling on the table before you can open the file and use it. The database engine uses journaling and commitment control to do the rollback of changes if a constraint fails.
The beauty of this is that if a user gets hold of a DFU you built (shame on you!) to update the item, or a contractor has a program with an error in it, they still can't clear the key. The rules are in the database itself and can't be circumvented without removing the rule.
All of this is accomplished without having to learn the syntax, or even the functionality of what's happening underneath. This is powerful and productive if used correctly. IBM has done a fabulous job of providing us the first native GUI tool that's usable on the 400. On top of that, it can help us deliver more intelligent database designs at a cost we've basically already paid.
Another nice feature is the SQL performance tools that are included. You can set up and monitor jobs that use SQL to review how they're functioning and determine if there's anything you can do for better performance. This is really only available through Ops Navigator and by itself worth the price (free).
All in all, we've been clamoring for a GUI 400 since it came out. This is our first real tool built specifically to provide functionality that is more cumbersome or in some cases, only available from this workbench. It's not even close to its predecessors, so take a new look. I think you'll find it refreshing and it will give you an idea where IBM's going with GUI on the AS/400.