Operations Navigator, or OpsNav, as it is affectionately known, has kicked up iSeries operation, administration, and application development several notches, yet many of you still use green-screen methods or other products that don't compare with what OpsNav can do. One of those things that OpsNav does really well is allow you to manage DB2 so come read along with me and learn how to easily and effectively work with your DB2 objects using OpsNav.

Open the V5R1 version of OpsNav on your PC and expand the plus sign (+) on one of your AS/400s listed. (If you are using an earlier version of OpsNav, feel free to follow along. I just can't guarantee that everything will work the same way as I explain in this article.) Now expand the Database link. You should see two embedded entries: Libraries and SQL Performance Monitors. I am going to talk about Libraries in this article. Now expand the library entry, and you should see a list of libraries. This list comes from the user portion of your library list. If you right-click the Libraries entry, you will get a menu with five options: Explore, Open, Create Shortcut, New Library, and Select Libraries to Display. This list is pretty self-explanatory, but you should know you have some flexibility in what libraries show up in your list. You can also create a new library on the iSeries, as well as create a desktop shortcut to this list of libraries. Now pick a library and right-click it to see what other cool things you can do. You will see a few of the same menu options as you did previously, but there are also some new ones. You can select explore to see a list of the DB2-related objects in the right window pane. It is important to remember that this list does not include program objects or commands, only database objects such as tables (files), views (logical files), and stored procedures. I really like that it lists stored procedures, because I use them in my shop and there isn't an easy green-screen way (if there's a green-screen way at all) to list the stored procedures in a library. So this is a nice feature. You can also delete a library (if you have the proper AS/400 permission) and view the properties of the library, including storage and save information. Those of you following along at home will notice that I skipped the New option. I did this because I wanted to save the best for last. The New option allows you to add specific DB2 objects to your library. Most of these objects you should already know and love, so I'm not going to explain each one, but I will take you on a journey of what you accomplish with each.

When adding a new table, a panel is displayed to let you enter the table name and description. Once you've entered the name and description, click the OK button to navigate to the next panel. From here, the world is your oyster as it relates to DB2 tables. You can choose to add columns, referential constraints, indexes, triggers, or check constraints by just clicking on the appropriate tab. You can insert columns manually or browse other tables and apply columns from other tables. The beauty of this feature is that you can accomplish all table-related tasks without ever leaving this window.

The same holds true for the remainder of the objects listed on the New pop-up menu. You get one-stop shopping, so to speak. A view is a way of representing data from one or more tables. A view, or logical file, contains no data of its own; it is a "path" to other data. To create a view, click the View option and type the name and description. You will also select your Check option on this panel (a future article is in the works covering referential integrity). Click OK to get to the next panel. From this panel you can select one of many tables from which to build your view. Once the tables have been chosen, you can then choose which columns you want from each table and which rows (the where clause) you want from the chosen tables. At any point, you can check out the SQL you have created for a particular view. Finally, you can edit and syntax-check the SQL with the click of a button. Once you have the appropriate SQL created for your view, press the Submit button to create your view. The Alias option allows you to create an alternative name for a view, table, or member of a view or table.

The Journal option, from the New menu, allows you to create new journals and journal receivers. From the first panel, you can type the name of the journal, its description, and the name of the library you want to contain the journal receivers. You can then press OK to create the journal with a default journal receiver and default attributes. Or, as we technical folks are inclined to do, you can click the Advanced button to get to a panel that allows you to define how the receivers are to be managed, how much data to store in the fixed portion of the journal entry, and whether you want internal entries removed. To specifically define journal receiver attributes, click the New Receiver button to display the appropriate panel. Notice the receiver name is already filled in. You can keep the default entry or change it to meet the specific standard in your shop. From this panel, you can also set the storage threshold for each receiver.

Stored procedures are a very powerful feature of DB2 UDB. You may have figured this out already by employing them at your shop. When you click Procedure, from the New pop-up menu, you will be given the choice between an External procedure and an SQL procedure (as opposed to the CREATE PROCEDURE command used in the interactive SQL command entry screen, where both SQL and External procedures are created using the same command). I'll explain an SQL procedure first.

On the first panel, you can supply the name and the description of your SQL procedure. You will set the maximum number of result sets to allow, whether or not you want your result sets to be deterministic, and how the SQL data will be accessed. The Specific Name box allows you to add a name, with up to 128 characters, that will specifically describe the procedure. From the Parameters tab, you define the parameters for your procedure. Click the Insert button to add a new parameter, then click each attribute of the new parameter to change that attribute. Click the SQL Statements tab to enter your logic behind the procedure. Since this is an SQL procedure, SQL is the language you will use to define the logic of your procedure. You can enter your SQL statements by typing them directly on the panel, or you can insert part or all of your statements from the SQL Statement examples at the top of the form. If you click the drop-down box, you will see many examples you can choose from.

To create an external procedure, you will select External from the Procedure option. The General and Parameter tabs are basically the same as the SQL version. The only difference is that you are able to define the style of the parameter for an external procedure. Click the External Program tab to see the primary difference between external and SQL procedures. Since the logic in an external procedure resides in a language and program other than SQL, here is where you tell the procedure the external program name and the language in which it was written.

User-defined functions are not much different from stored procedures, except that you don't use an SQL Call to invoke a function and a function always returns one, and only one, value. Creating functions in Operations Navigator is also similar to creating procedures. Notice, as was the case with procedures, you are given a choice as to the type of function to create. Also, just as with procedures, two of the choices are External and SQL. The External and SQL creation panels are similar in procedures and functions. The difference is that, with functions, you must also define the return value. Under the General tab, there is a place to define the type, length, and scale of the return value. Other than this extra feature, creating external and SQL procedures and functions is similar. Another type of function you can create is called a sourced function. A sourced function is one that is based on a previously defined function. It looks much like the other function creation panels, except that with a sourced function, you have the ability to select a built-in or other user-defined function on which to base your new function. Click the drop-down box to get a list of all built-in functions. Built-in functions are listed first, then followed by any user-defined functions.

The last option on the New menu is Type. It looks much like the information presented in the General tab of the Create Function form. You first provide the name and description of your data type, then base your type on a built-in type or another user-defined type by clicking the Sourced type drop-down box. User-defined data types will be explained in a later article.

Now that you've created tables, views, journals, stored procedures, and other exciting DB2 objects, you can view and manipulate them easily using the OpsNav panel. Simply double left-click the library of your choice (or right-click and select Explore or Open). Here is where OpsNav further demonstrates its power. Double-clicking any object in the list will produce different results. If you double-click a stored procedure, you can change the attributes. However, when you double-click a table, the contents of that table are displayed. From this panel, you can not only view the contents of a table but also insert rows and update columns (assuming you have the proper authority). When you double-click a view, you are able to see the contents of that view, but you are not able to update or insert to that view. A closer look reveals what's really going on. If you left-click on Table, you will notice certain options that can be chosen for that table. The option in bold is the default option; that is, the option that is selected when you double-click the object. Because OpsNav is able to recognize each object, you are able to easily perform actions specific to each type of object. If you left-click a table and select Properties, you are then able to modify the properties of the table. That way, you learn simple navigation and Operations Navigator takes care of the rest. You don't have to learn each object-specific command, as you do in the green-screen world.

All this power to control DB2 UDB on the AS/400 without using a green screen. Pretty cool, huh? OpsNav makes organizing and managing your database extremely simple by incorporating the Microsoft Windows-like environment, which most of us have become accustomed to (be it right or wrong), into its tool set. Whether you are cleaning objects out of a library or creating new tables and views, OpsNav provides a common, consistent, and intuitive place to perform your tasks. Since it is a Microsoft Windows client, you can perform simultaneous tasks, in separate windows, with the greatest of ease. Give it whirl and you'll be sold.