Displaying Multiple Results Sets in Run SQL Scripts
October 15, 2008 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.
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.
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.
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.
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.
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.
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.
|
