Run SQL Scripts Using Operations Navigator

by Kevin Vandever

I am going to assume that you have Client Access Express V5R1 installed and running on your desktop and that you have the latest CAE Service Pack installed. The first thing you need to do to try out the techniques I'll discuss in this article is to expand the services under a specific iSeries in your list. You do that by clicking on the plus (+) sign next to the iSeries you are interested in. Right-click the Database entry, and select Run SQL Scripts from the menu. You will be presented with a window like the following:

Tons of Tasks

There are many SQL-related tasks that you can perform from this window, but I'm going to focus on entering and running SQL statements. To create an SQL script, simply enter valid SQL statements, separated by semicolons. Once you enter the SQL statements, there are a number of ways to run your script. Take a look at the three hourglass icons, in the SQL toolbar. (V4R5 will have green arrows instead of hourglass icons.) These icons represent different options for running your SQL statements. The hourglass on the far left, with the three cascading blue marks, runs all the SQL statements you've entered since the beginning of your session. The middle hourglass icon, with two cascading blue marks, allows you to run a group of SQL statements starting from a specific entry in the script. For example, if you have four separate SQL statements and you want to run the last two, you click the SQL statement you want to start, click the middle hourglass icon, and all statements--from your selected one to the end--will run. The far- right hourglass icon is used to run selected statements throughout your script. If you've run 20 separate SQL statements during the day and now want to rerun, say, the first, eighth, and 15th statements, you do so by highlighting the statement you want to run (in V4R5 I believe you double-click each statement), which stores it in the run hopper, so to speak, then click the far-right hourglass icon to run the selected statements. How the highlighted statements run depends on whether or not the Smart Statement Selection option is "on." Click Options and Smart Statement Selection to toggle between "on" and "off." A black check mark will indicate that this option is "on." When Smart Statement Selection is "on," the highlighted statements will run, in order, as individual statements. When this option is "off," the highlighted statements will run as one statement.

Of course, if you don't like using the hourglass icons, you can select the Run option, at the top of the window. This also provides you with a choice of methods for running your SQL statements. In V5R1, an additional run option was included. Click Options and "Run Statement on Double-Click" to run individual statements just by double-clicking them. I like that option.

Controlling SQL Run Options

Before I let you go away and play with OpsNav, I want to explain some of the other options you have at your control. Click Options, and you will see six available options to choose from. A black check mark next to an option signifies that the option is "on." Below is a brief explanation of each option.

Include Error Message Help in Run History--This was an option in the Options menu in V4R5; it was removed in V5R1. If you are running V4R5 and this option is turned on, additional error information will appear in the run history window, directly below the SQL script window. If turned off, no extra error information is made available to that window.

Go Ahead, Give It a Spin

Now that I've shown you how to run SQL scripts, as well as some of the available options to use in the process, let's try it out. The following figure shows what happens when the default error handling is turned on; that is, Stop on Error and Smart Statement Selection:

Notice that there are two SQL statements. The first one is in error because column XYZ doesn't exist. If you try to run all SQL statements (remember how to do that?), the script is stopped on the first SQL statement in error, and the subsequent statements are not run. If you turn off the "Stop on Error" option and run all SQL statements again, you will get the results shown here:

Notice that this time the second SQL statement was allowed to run while the error from the first statement was logged in the run history window. You can then select the Messages tab or the tab that contains the SQL statement you ran, depending on what you want to see. The previous figure shows that I have selected the Messages tab, and you will see that I still get the error message from the first SQL statement, but it also shows that my second SQL statement ran successfully. Also notice that there is another tab labeled by the actual SQL statement that processed successfully. I could click that tab to see the results of that statement. Pretty cool, huh?

Run Wild

I have shown you some very basic features of the Run SQL Scripts screen in Operations Navigator. Just from what I showed you here, it is enough to make you want to use this tool as your standard interactive SQL tool on the iSeries. The good news is that there's a whole lot more. I didn't mention the VisualExplain features or talk about connecting to other servers or using JDBC. Then there's the syntax checker, the job log viewer, and the options for clearing results and running history areas. And I didn't even cover all the details of the options I did show you. This is a very powerful feature of a very powerful tool. Play around for a while and check back for more OpsNav articles in the future.