Using the SQL Features in Operations Navigator

September 15, 2004 Joel Cochran

A couple of years ago, I fell in love with SQL. Okay, maybe not in love, but definitely in appreciation. Initially it was a hate/hate relationship. Like most people in RPG land, I had been weaned on native file access and was shocked to discover as I ventured into languages on other platforms that there really is no equivalent. I learned SQL out of necessity, and in the process I’ve become a true believer, even something of an SQL evangelist within my tiny sphere of influence. This article introduces you to using some of the SQL features available in Operations Navigator.

GETTING THERE

I’d like to first say that I’m still using the OpsNav install I got with our V5R1 install about three years ago. Personally, I’ve never really liked OpsNav. I tend to be a purist, and I still do most of my work in an emulator. I find OpsNav, and most other GUI things, slow and cumbersome. That said, I occassionally find something very useful in OpsNav. My most recent discoveries focus on OpsNav’s SQL capabilities.

One of my boss’ recent projects required the use of Microsoft SQL Server 2000 to work with some client data. Once we got it installed and the database imported, a very long story in itself, we began playing with the tools and interfaces supplied with the server software. While I found a lot of it unfriendly and difficult to navigate, my boss found it intriguing and useful. You see, I had forgotten one basic fact: that not everyone is a programmer. In fact, most of us in the business world are not programmers, but that in no way minimizes the need for these people to be able to manipulate data. In some cases, like my boss’, these changes need to be made en masse, and quickly.

My boss does know databases fairly well. He has used Client Access to move data from an AS/400 to his PC, manipulated it with Excel or FoxPro, and then pushed it back to the ‘400. Recently he has been learning SQL, so I showed him the Interactive SQL entry screen on the ‘400, and he has been able to use that for simple queries and changes, but it really isn’t user-friendly enough for him to use regularly. Another problem reared its ugly head as well. While we have five black beauties in our shop, only two of them have the SQL Developer’s Kit. As a result, he cannot perform all the work he needs to on every box, so he ends up either reverting to familiar but time-consuming methods or getting one of the programmers to write a program on another machine and move it to the necessary box.

This forced me to revisit the options available through OpsNav, and I like what I’ve found.

RUNNING SQL STATEMENTS

Let’s jump right to the main course, running SQL statements in OpsNav. This is the feature with the most immediate benefit. Besides not requiring you to purchase the licensed program for the SQL Developer’s Kit, any user with OpsNav installed can access the tool using just a few mouse clicks. It does require that the Database section of OpsNav be installed: if you have just the basic install of Client Access on your PC you won’t see any of these options.

Open OpsNav and connect to the desired host machine. Double-click the listing for Database. You may have to wait a few seconds, but some new options should appear in the bottom frame, now titled Database Tasks. In the right-hand column, the second option should read Run an SQL Script. Click that option and a new window will open for running SQL Scripts. I like to maximize this window as soon as it opens, which makes subsequent use of the window much easier.

This window is the primary interface I use. To run a statement, simply type it into the entry box and terminate it with a semicolon (;). If you are used to SQL on the AS/400, there is one quick adjustment you will need to make: the default separator character is a period (.) instead of a slash (/), so a simple SELECT statement might look like this:

Select * from MYLIB.MYFILE ;

The statements are not case-sensitive, so the capitals above are just for emphasis. You can also write very clean, easy-to-read statements with lots of line breaks because the statement does not end until it encounters the semicolon:

Select count(*) as TOTAL_COUNT, 
           avg(LAND_VALUE/NUM_ACRES) as AVERAGE_PER_ACRE
from MYLIB.MYFILE
where NUM_ACRES > 0
   and LAND_VALUE > 1000
;

To run this script, highlight the statement and click the button on the toolbar for Run Selected (it looks like an hourglass with one document) or press Ctrl-Y. You can also click the toolbar for Run All (the hourglass with three documents) at anytime, and it will run all the statements in your window (more on this in a moment). Either the results or an error listing will show up in the bottom frame of the screen. One interesting feature is that you can grab a column with the mouse and move it to another column position. This won’t change the SQL statement, so if you run it again it will revert to the order of the select variables.

There are a couple of things you can do here that you cannot do in Interactive SQL. Since the semicolon acts as a statement separator as well as a terminator, you can run multiple statements one after the other. Here is an example of running multiple statements:

Select count(*) as TOTAL_COUNT, 
           avg(LAND_VALUE/NUM_ACRES) as AVERAGE_PER_ACRE
from MYLIB.MYFILE
where NUM_ACRES > 0
   and LAND_VALUE > 1000
;

Select min(LAND_VALUE/NUM_ACRES) as LOWEST_VALUE,
           max(LAND_VALUE/NUM_ACRES) as HIGHEST_VALUE
from MYLIB.MYFILE
where NUM_ACRES > 0
   and LAND_VALUE > 1000
;

Click on the Run All button and these statements will both execute in order. At first, it will appear that only one statement ran, but if you look at the bottom of the window you will see that there are clickable tabs with the statements as the labels. This isn’t particularly user friendly, but does allow you to find and view results from both queries. This can be handy for quick comparisons between similar statements. For statements that do not return a result set, the statements will be executed in order, with a report in the bottom frame, telling you that each one completed successfully. If OpsNav encounters a statement that is malformed, the process will end at that statement with a typical ‘400-style error listing.

USING .SQL FILES

Another extremely useful thing you can do is to save these scripts as “.SQL” files. Once saved, these scripts can be stored, shared, and rerun. Just like saving an OS/400 query, the capability to reuse an SQL statement is very appealing. Using SQL instead of query makes this a more powerful tool as well because of the ability to update, insert, and delete. The natural corollary to this is that I can run any .SQL file. This means that I can accept .SQL files from outside sources, like a dump file from a MySQL database. The file may require some minor tweaking, but it is certainly better than retyping every command or insert statement. You could also create the file with an editor of your choosing, like NotePad, if you don’t like the OpsNav interface, and then open it and run the statements.

For example, I’ve recently used this method to create several new databases on the AS/400. I do so by using my favorite editor and creating a series of statements separated by semicolons. Specifically, I start with a “Create Database” (or “Create Collection”) statement to create the library. I then follow this with a series of “Create Table” statements. Additionally, I’ll add statements such as “Label On,” “Comment On,” or “Rename Table” for each table. In special cases, I can even add “Insert” statements to populate the tables.

This approach offers several appealing benefits. First, the entire library can be created with just a couple of mouse clicks, including some of the special statements listed above. Second, I can easily replicate the library on another machine. Third, the textual format of the .SQL file allows me to easily add extensive commentary, including database file relationships and record-level data samples. Finally, I could easily modify a copy of the .SQL document for another database.

This is not meant to advocate using SQL instead of DDS, which is a discussion for another article; rather, this is just meant to show some of the possibilities. This approach could be used just as easily to modify data on distributed systems: imagine you are a vendor maintaining a list of codes and you want to add codes to a file in your existing install base. While you could write and distribute a program to perform this function, you could just as easily make a download available to your clients on a Web site or an FTP site and have your clients execute the .SQL script instead.

ANALYZING SQL STATEMENTS

If you have written RPG programs with embedded SQL, you have probably learned that optimization means a lot. While the nitty-gritty details are beyond the scope of this article, you can learn a lot about how the system implements your SQL by running the program in debug mode and reading the joblog. This will activate the SQL Query Optimizer and create enormous job logs, and buried within those logs are the nuggets you need to make your SQL perform better. By testing these statements in OpsNav, you can view that information without the pain of debug and its huge job logs. You can also discover what SQLCODEs and SQLSTATEs your statement may throw and so prepare your program to handle anticipated messages.

To find all this valuable information you need look no further than the toolbar. Enter an SQL statement, as discussed above, but instead of selecting a Run button, try using Visual Explain Only. (You can use Run and Explain if you like, but for just analyzing statements I use Visual Explain Only). Clicking the button opens a new window with three frames. The largest frame is a diagram that I don’t use much: I’m sure there are DBAs out there who would find it useful, but the way I look at it, the more complex the diagram, the more work your statement has to do. In the right-hand frame you will find a lot of information about the statement, including how long it took to run the statement, the “SQL Return Code,” and “SQLSTATE.”

The real goldmine is in the bottom frame: once again, look for more tabs along the bottom of the window and you should find one labeled “Optimizer messages.” Click that and you will see the optimizer messages you normally find in the job log. You can double-click any of these messages to view the message detail. You are especially on the lookout for a message entitled “Access Path Suggestion for File. . . ,” which is typically the first thing you can do to increase SQL performance. Again, the real benefit here is to be able to do this in a more preparatory mode rather than in actual debug; this is very useful in the development of your SQL programs.

THE USUAL CAVEATS

As usual, there are some caveats and complaints. First of all, printing the results of a query appears to be impossible. If it is not impossible, certainly not intuitive. Worse than that, you cannot copy and paste the contents of a result window, which would be an acceptable alternative. Granted, as I mentioned at the top of this article, I’m still using V5R1, so I’m sure the tools have improved since. And there are plenty of useful features that I haven’t covered, such as SQL Statement examples and the Generate SQL wizard. All in all, the SQL features of OpsNav can be very useful and educational; I hope you give them a try.

Joel Cochran is the director of research and development for a small software firm in Staunton, Virginia, and is the author and publisher of www.RPGNext.com. E-mail: jcochran@itjungle.com