The Leading Educational Resource for IT Professionals

SQL 101: Tools of the Trade - i Navigator’s Run SQL Scripts

by Victoria Mack December 21, 2016 0 Comments

A more “modern” alternative to STRSQL, discussed in the last two articles, is the i Navigator’s Run SQL Scripts tool. Let’s explore it together, shall we?


While STRSQL is a green-screen tool, Run SQL Scripts is part of the i Navigator package. You can access it by choosing the Run SQL Scripts option, either from the bottom-right pane of the i Navigator window after you’ve chosen the Databases tree node from the right panel, as shown in Figure 1, or by right-clicking the database name and choosing the respective option.


122116RafaelFigure1 dmu

Figure 1: Choose the Run SQL Script option in i Navigator’s bottom pane.


Either action will cause a new window to pop up: the Run SQL Scripts tool, shown in Figure 2. Here you can execute SQL statements just like STRSQL but with a more user-friendly and modern interface.


122116RafaelFigure2 dmu

Figure 2: The Run SQL Scripts window looks like this.


Let’s take a closer look at the interface. Under the Edit menu, you’ll find the “Insert From Examples…” option, which will show an extensive list of examples. This list contains SQL statement examples and even a few CL command templates, which you can easily adapt to your needs. Just below the menu, you’ll find the heart of the tool: the command input pane. Here is where you enter your statements by typing them, using either the examples or simply copy/paste, because this pane behaves like Window’s Notepad in many ways.


When you’re finished creating your command, you can run it via the Run menu. You’ll find that it contains three options:

  • Selected—This option executes the selected or highlighted statement or statements. If you don’t have a statement selected, the statement where the cursor is currently positioned will be executed.
  • From Selected—This option executes a subset of the statements that exist in the input pane, starting with the currently selected statement and ending with the last statement in the input pane. (Maybe IBM should have called this option “Execute from Here to the Bottom” instead.)
  • All—The final option available is quite straightforward. It executes all the statements, starting with first statement in the pane and ending with the last.


There’s also an option that simplifies running a single statement. If you go to the Options menu and click Run Statement On Double-Click, you’ll be able to run statements more quickly, without having to go to the Run menu, as shown in Figure 3.


122116RafaelFigure3 dmu

Figure 3: The Run Statement On Double-Click option lets you run statements quickly.


Note that the Examples dropdown list contains SQL and CL templates. This means that you can write a script that contains both SQL statements and CL commands, in whichever order you like, as long as you prefix your CL commands with CL: . (CL, then a colon, and then a blank space). For instance, if I wanted to clear the file MYFILE from library MYLIB, I’d just type the following command and execute it using one of the aforementioned options:




Beware, however: Not all CL commands are allowed! You can only use batch-compatible commands. This means, for instance, that DSP* commands that direct output to the screen are not allowed.


This ability to execute CL commands and mix them with SQL statement in your input pane allows you to build proper scripts. Naturally, “proper scripts” usually include some sort of documentation. In this context, you can use double dash characters (--) to write a single line comment, just like you’d use double slash characters (//) in free-format RPG. For longer, multi-line comments, you have to use /* and */ to delimit your comment lines, for instance:



   This is a set of multiple





Regarding the writing style of the statements, Run SQL Scripts also offers the *SYS and *SQL options that STRSQL does, but here the default is *SQL, which means that the database engine is expecting a dot (.) instead of a slash (/) as the separator between the library and file names. In order to change it, you need to go to the Connection menu in the top tool bar and click JDBC Settings. When the window pops up, click the Format separator. You’ll see something similar to Figure 4.


122116RafaelFigure4 dmu

Figure 4: The JDBC Settings window allows you to change the naming convention.


In this window, you can change, among other things, the naming convention from *SQL to *SYS or vice versa. When you’re happy with your choices, confirm them by clicking the appropriate button (the label varies with the version of Client Access) and close the window.


Go ahead and write a statement or two. Remember that regardless of the naming convention you choose, you must end all SQL statements and CL commands with a semicolon (;).


You’re now ready to run your statements. After the statements are executed, the output pane, which sits just below the input pane, will show the results. This pane will have at least two flaps or separators, one for the results themselves and another for the messages generated by the execution of the script. If your script contains more than one SELECT statement, it’s possible to see multiple result separators—one for each SELECT. If you want to see the results in separate windows, you need to go to the Options menu and click Display Results in Separate Window, as shown in Figure 5.


122116RafaelFigure5 dmu

Figure 5: Choose the “Display Results in Separate Window” option.


Finally, you can save your scripts as text files, using File > Save, as in most applications. Saving your scripts is particularly useful when you’re working with DDL statements, because you can create a script that drops all tables and associated SQL objects, and recreate them with just a few clicks. You write the script once, save it, and then you can open and run it later. There’s a lot more you can tweak in this tool, but these basic notions should be enough for now. I might discuss some neat features, like the Visual Explain, later in this series. There’s not a lot of documentation about it online, so whenever I have a problem, I tend to go to IBM’s knowledge database to look for solutions.


For those of you who already have the new Client Access Java version, named Access Client Solutions, I’ll be discussing the new and improved version of Run SQL Scripts on the next TechTip of this series. There are a gazillion other tools that you can use, like WinSQL, Toad, and IBM’s new RDi Database Explorer tool. Most provide similar functionality to the two presented in these last few articles. Find one that suits your needs and learn as much about it as you can. The time you invest now will be time you’ll save later.


About the Author: Rafael Victoria-Pereira

Victoria Mack
Victoria Mack


Also in MC Press Articles

Gifts to Give Yourself This Holiday

by Victoria Mack December 21, 2016 0 Comments

It’s the holiday season, and everyone is in the gift-giving mood. Be sure you don’t forget to invest in your company and career.

brian mayWritten by Brian May

It’s a special time of the year. Family gatherings for the holidays, football season, and time in the woods all make this one of my favorite seasons. The month of December is also unique for IT departments. December is certainly not business as usual for most of us.

It’s time for budgets. That may mean requesting budget items for next year or spending surplus budget before the end of the year. It’s often when project work slows down a bit as end users, and IT staff alike, take time away from the office. It’s a time when stress is often at its lowest, and it’s just easier to get some things done.

Continue Reading →

Energy, Vibe, and Atmosphere

by Victoria Mack December 19, 2016 0 Comments

This is IT. We must be willing to bend.

steve pitcherWritten by Steve Pitcher

With a growing emphasis in talking about the state of the current IBM i workforce, also known as the “IBM i skills shortage,” it behooves oneself to keep the noise level to a minimum in order to make even-keeled decisions. In short, don’t necessarily believe all the hype you read.

I’d like to think of this as an extension piece to “The IBM i Skills Shortage Myth.” It’s not necessarily a “part two” per se, but more of a story that runs parallel. I’ve been trying to write this for about six weeks, but some things are just hard to put into words, especially when they involve how you feel as opposed to what you know. Besides, writing what you know is easy. Writing what you feel leaves room for reader interpretation, so you have to be more careful.

Continue Reading →

Six Key Drivers That Push Your Business Forward

by Victoria Mack December 19, 2016 0 Comments

Integration can be a competitive weapon that can positively impact the bottom line through encouraging revenue velocity, cost containment, risk mitigation, and long-term customer satisfaction.

mark denchyWritten by Mark Denchy

Business is moving faster and faster, as customers and business partners demand more automated interactions. IT is no longer supporting the business; it is becoming the core of the business. Digital transformation of businesses requires us to connect and interact with both customers and suppliers in new and unexpected ways.

Continue Reading →