|Putting Run SQL Scripts to Work for You|
|Programming - SQL|
|Written by Guest.Visitor|
|Tuesday, 01 January 2008 18:00|
Execute a script that contains a single SQL statement, multiple SQL statements, or even CL commands!
By Skip Marchesani
Several years ago, I began using what is now called the Run SQL Scripts function (also known as the SQL Script Center or Script Center in iSeries Navigator or System i Navigator, respectively) to demonstrate various SQL functions and to answer SQL syntax and function questions when I teach SQL-related topics. Now that the SQL Script Center is becoming more widely known and used, when I teach and use it for demonstrations, I am getting more and more questions about how it works.
One reason for the increasing number of questions is that there is little information available for Run SQL Scripts via IBM's System i Information Center Web site, and IBM has no softcopy publication available for it. The only documentation available is Help, which has improved over time but on occasion can still be a challenge when you're trying to find an answer to a question. So based on the more frequently asked questions I get while teaching, here are some Script Center tips.
Opening Run SQL Scripts
In case someone reading this is unfamiliar with Run SQL Scripts in iSeries Navigator or System i Navigator, it is part of the Database function, so therefore you must have the Database function installed to use Run SQL Scripts. It can be said that in part it is the PC replacement for Interactive SQL (the STRSQL command) on the green-screen side of the System i. Run SQL Scripts and Interactive SQL do overlap in function, but each can do things that the other cannot.
To open Run SQL Scripts, expand the Database function (click on the + next to Database) and then right-click on the database name, which will appear immediately below the Database function. The Run SQL Scripts function then appears in three places: near the bottom of the resulting pop-up menu, as an option in the File drop-down menu in the toolbar at the top of the panel, and as an option under Database Tasks in the lower pane at the bottom of the panel. Clicking on any of these three options will open the Run SQL Scripts panel.
What Can the SQL Script Center Do?The SQL Script Center allows the user to execute a script that contains a single SQL statement or multiple SQL statements. If the script contains multiple SQL statements, the user can execute the entire script in three ways: by starting with the first statement in the script and ending with the last statement in the script, by choosing the currently selected or highlighted statement or statements (the statement where the cursor is currently positioned), or by selecting a subset of the script, starting with the currently selected statement and ending with the last statement in the script. The drop-down menu for Run in the toolbar at the top of the panel shows these options as All, Selected, or From Selected. There is also an hourglass-shaped icon for each of these options immediately below the toolbar.
Executing SQL Statements and CL CommandsDid you notice in the previous paragraph that I started out using the words "SQL statements" and ended by just using the word "statement" without prefacing it with "SQL"? There is a method to my madness. Since this is the
An SQL statement or a CL command is entered on the upper portion of the Input pane of the Run SQL Scripts window. SQL statements are entered as is, without any prefix. CL commands must be prefaced with CL: (not case-sensitive). Each SQL statement and/or CL command to be executed by the
The nice thing about the SQL Script Center is that once a script has been created (a script is a collection of one or more SQL statements and/or CL commands), it can be stored as a PC file with a .SQL extension, on a PC, or in the System i IFS. This file that contains the script can then be accessed and reopened by the
Changing Font Size for the Input PaneWhen entering statements in the Input pane, the default font is hard to read if displayed using an LCD projector for teaching purposes. And if your eyes are going through the aging process (like mine), the default font may be hard to read on your PC display. Again, users are pleasantly surprised to find that they can change the font type and increase the font size for the Input pane to make it easier to read. Just click on View in the toolbar, and then click on Font in the resulting drop-down menu. I find that an 18-point font works well when displaying the Input panel using an LCD projector.
Viewing Completion Messages and Results SetsThe lower portion of the window is the Output pane. There will always be a Messages tab, where a completion message is displayed for each SQL statement or CL command that is executed. And when a SELECT statement is executed, there will be a Results tab that displays the results set for the SELECT statement.
Displaying Multiple Results Sets WindowsThe issue that many users have with the Results tab is that it displays only the results set for the last SELECT statement executed, and many times you want to see the results set for a series of SELECT statements so they can be compared. Again, this is very easy to do: Just click on Options in the toolbar and then click on Display Results in a Separate Window to select it. Make sure that Smart Statement Selection is selected (normally, it should be selected since it's the default). With these options selected, two or more SELECT statements can be executed, and the results set for each will be displayed in a separate window.
The window for one results set is displayed directly on top of the window for the other, so you have to manually reposition and possibly resize all displayed windows to be able to see each one and make visual comparisons. The down side of Display Results in a Separate Window is that each open window must be manually closed to remove it from your desktop; however, I feel that this is a minor inconvenience compared to the benefit of having multiple results sets displayed at the same time.
Executing Multiple SQL Statements and/or CL CommandsTo execute two or more SELECT statements (or other SQL statements or CL commands), there are a couple of options. You can highlight each SELECT statement one at a time by placing the cursor on the statement and then selecting Run Selected either in the drop-down menu for Run in the toolbar or by clicking the appropriate icon for Run Selected directly underneath the toolbar.
Two or more SQL statements can also be executed without having to select each one. First, as mentioned previously, make sure Display Results in a Separate Window is selected from the Options drop-down menu on the toolbar. Then highlight each SQL statement to be executed and move the cursor to select Run Selected as described in the previous paragraph.
Each of these two options requires that the statement or statements be selected and then the cursor moved to the appropriate execution option on the tool bar.
The third option available for executing an SQL statement in the Options drop-down menu on the toolbar eliminates selecting the statement and then selecting the execution option. This option is the one that I prefer to use when teaching. If you select Run Statement on Double-Click from this drop-down menu, you can execute any single SQL or CL statement by placing the cursor on the statement and double-clicking it with your mouse. This allows you to easily execute a series of SQL statements and/or CL commands—one at a time—and, if it's a SELECT statement, have each results set displayed in a separate window. And you don't have to keep moving the cursor between the statements and the toolbar.
Prompting SQL Statement CreationOne question that is asked on a very frequent basis is whether the
Interactive SQL provides a good multilevel SQL prompter that works with a subset of the SQL statements available in DB2. The
A Powerful and Flexible ToolThe Run SQL Scripts function (a.k.a. the
|Last Updated on Friday, 04 January 2008 03:22|