Volume 8, Number 35 -- October 15, 2008

Displaying Multiple Results Sets in Run SQL Scripts

Published: October 15, 2008

by Skip Marchesani

The SQL Script Center, which is part of the System i Navigator Database function, allows the user to execute a script that contains single or multiple SQL statements. If the script contains multiple SQL statements, the user has the option of executing a single SQL statement, the entire script (starting with first statement in the script and ending with the last statement in the script) or two or more consecutive SQL statements as a subset of the script.

The results from executing an SQL statement are shown in the lower portion of the Run SQL Scripts window called the Output pane. There always will be a Messages tab where a completion message is displayed for each SQL statement or CL command that is executed as shown in Figure 1.

Figure 1

And when a SELECT statement is executed, assuming you are using the defaults for Options in the toolbar, there will be a Results tab that displays the results set for the SELECT statement as shown in Figure 2.

Figure 2

The issue that many users have with the Results tab is that it only displays the Results set for that 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. This is very easy to accomplish. Just click on Options in the toolbar, then click on Display Results in a Separate Window to select it. Also, make sure that Smart Statement Selection is selected (normally it would be selected since it's the default) as shown in Figure 3.

Figure 3

The next question is how to execute a series of SELECT statements at the same time. There are a couple of alternatives. You can highlight each SELECT statement to be executed and then click on Run Selected (far right hourglass) in the toolbar, as shown in Figure 4, and the Results set for each will be displayed in a separate window.

Figure 4

The windows for each result set are displayed directly on top of each other so you have to manually reposition and possibly resize all displayed windows to be able to see each one and make visual comparisons as shown in Figure 5. The downside 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.

Figure 5

There is another alternative for executing an SQL statement in the Options drop-down menu on the toolbar, which eliminates selecting the statement and then selecting the execution option. As shown in Figure 6, select Run Statement on Double-Click from the Options drop-down menu, and you can execute any single SQL 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--one at a time--and if it's a SELECT statement, have each Result set displayed in a separate window, without having to keep moving the cursor between the statements and the toolbar.

Figure 6

Skip Marchesani retired from IBM after 30 years and is now a consultant with Custom Systems Corporation. He is also a founding partner of System i Developer and the RPG & DB2 Summit. Skip spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400 and was involved with the development of the AS/400. He was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Skip is recognized as an industry expert on DB2 for i and the author of the book "DB2/400: The New AS/400 Database." He specializes in providing customized education for any area of the System i, iSeries, and AS/400; does database design and design reviews; and performs general System i, iSeries, and AS/400 consulting for interested clients. He has been a speaker for user groups, technical conferences, and System i, iSeries, and AS/400 audiences around the world. He is an award-winning COMMON speaker and has received its Distinguished Service Award. Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot

Sponsored By

Need Results Fast?
Web-enable with Genie!

                                                          · Easy Installation
                                                          · Codeless Customizations
                                                          · Instant Results

With Genie on your side, you can be
modernized in less than a day. Simply
install, customize, and deploy.

See the magic of Genie today.

Download a FREE 30-day trial at

Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Edwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

ARCAD Software:  October 22 Webcast! WDSc & RDi: Debugging with Don Yantzi
BCD:  Presto instantly Web enables 5250 Green Screen Apps with NO RPG, Cobol or DDS code changes
Vision Solutions:  A $20 gas card for completing a short i5/OS DR survey


IT Jungle Store Top Book Picks

Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
Getting Started with PHP for i5/OS: List Price, $59.95
The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
The System i Pocket RPG & RPG IV Guide: List Price, $69.95
The iSeries Pocket Database Guide: List Price, $59.00
The iSeries Pocket Developers' Guide: List Price, $59.00
The iSeries Pocket SQL Guide: List Price, $59.00
The iSeries Pocket Query Guide: List Price, $49.00
The iSeries Pocket WebFacing Primer: List Price, $39.00
Migrating to WebSphere Express for iSeries: List Price, $49.00
iSeries Express Web Implementer's Guide: List Price, $59.00
Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
Getting Started with WebSphere Express for iSeries: List Price, $49.00
WebFacing Application Design and Development Guide: List Price, $55.00
Can the AS/400 Survive IBM?: List Price, $49.00
The All-Everything Machine: List Price, $29.95
Chip Wars: List Price, $29.95

The Four Hundred
IBM Doubles the Cores on Midrange Power Systems

Sundry October Power Systems Announcements

SMB Manufacturers Testing PLM Integration Possibilities

As I See It: What's Old is New

IBM Updates i Rational Tools, and HATS Too

The Linux Beacon
Why Blade Servers Still Don't Cut It, and How They Might

Intel Keeps Both Arms Swinging with Xeons, Jabs with Itanium

Microsoft Ponies Up Another $100 Million for Novell Linux

Mad Dog 21/21: Newtonian Economics

Two More Xeon-Based Galaxy Servers from Sun

Four Hundred Stuff
IBM Close to Delivering DB2/400 Storage Engine for MySQL

PKS Provides the Missing Link from RPG to EGL

VAI Evolving Products to Meet Customers' Future Needs

IBM Delivers New Storage Options for i

BlueWare Goes SaaS with i OS-Based Health Applications

Big Iron
For Some Customers, the Mainframe Is Green

Top Mainframe Stories From Around the Web

Chats, Webinars, Seminars, Shows, and Other Happenings

System i PTF Guide
September 20, 2008: Volume 10, Number 38

September 14, 2008: Volume 10, Number 37

September 7, 2008: Volume 10, Number 36

August 30, 2008: Volume 10, Number 35

August 23, 2008: Volume 10, Number 34

August 16, 2008: Volume 10, Number 33

The Windows Observer
Citrix Addresses Performance with XenApp 5

Server Buyers Shop Like It's 1999 in the Second Quarter

Intel Keeps Both Arms Swinging with Xeons, Jabs with Itanium

Mad Dog 21/21: Newtonian Economics

Microsoft Does Something About Those SQL Injection Attacks

The Unix Guardian
What the Heck Is the Midrange, Anyway?

Overseas and Notebook Sales Offset Printer Declines for HP in Q3

Two More Xeon-Based Galaxy Servers from Sun

Mad Dog 21/21: Newtonian Economics

Intel's Nehalems to Star at IDF, AMD Pitches Shanghai

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar


Profound Logic Software
Group8 Security

Printer Friendly Version

Displaying Multiple Results Sets in Run SQL Scripts

A Practical Way to Add Exports to a Service Program

Admin Alert: Preventing Multiple IPs from Stopping Internet Traffic

Four Hundred Guru


From the IT Jungle Forums
Data Queues vs. MQ Series: Performance

Removing blanks from a CL Variable


SQL "Hidden" Field

Java Messages

MQ Help Desired

Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement