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 Commands
Did 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 theAn 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 Pane
When 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 Sets
The 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 Windows
The 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 Commands
To 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 Creation
One question that is asked on a very frequent basis is whether theInteractive SQL provides a good multilevel SQL prompter that works with a subset of the SQL statements available in DB2. The
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online