Configuring Run SQL Scripts for Ad Hoc Queries

Published: June 10, 2009

by Skip Marchesani

Many months ago, when I needed to create and execute an ad hoc query using SQL, my tool of choice was Interactive SQL, available via the System i green screen interface. I don't recall any dramatic change taking place--I suspect it was gradual--but these days I find myself using Run SQL Scripts (a.k.a. SQL Script Center or Script Center) from System i Navigator, instead of Interactive SQL, to execute an ad hoc SQL Query.

The Script Center offers a couple of advantages over Interactive SQL. Specifically the Script Center allows you to intermix and execute SQL statements and batch CL commands, store one or more SQL statements and/or batch CL commands as a script for later retrieval and execution, include text or comments for documentation (not that we programmers would ever need to document anything), and display multiple result sets from the execution of multiple SQL SELECT statements.

One thing the Script Center cannot do (which Interactive SQL can do) is provide multi-level prompting for SQL statements. However, these days I'm pretty comfortable with SQL syntax and don't need to lean on SQL statement prompting as much as I used to.

One frustration that I hear is that the script center is not the best tool for ad hoc SQL queries.

My answer to that is if you attempt to create and execute ad hoc SQL queries with the Script Center using the default configuration or setup options, you may find yourself frustrated--just as I was when I first started to use it. The good news is it's fairly easy to tweak the default options and optimize the Script Center for ad hoc SQL Queries.

Figure 1.

Viewing Completion Messages and Results Sets

Before we discuss any tweaking, let's take a quick look at the Output Pane in the lower portion of the Run SQL Scripts window. The Output pane will always have 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 with the default options, there will also be a tab to the right of the Messages tab that displays the results set for the SELECT statement, as shown in Figure 1.

Choosing the Correct Options

The results tab does not lend itself to ad hoc SQL queries with complex results sets and is limited in function. If you want to do ad hoc SQL queries, and execute multiple SQL statements and/or batch CL commands and display multiple result sets, you need to tweak the Script Center by changing the Script Center run time options.

Figure 2.

To make any changes, click on Options in the toolbar, then select "Display Results in a Separate Window," and "Run Statements on Double Click." Also make sure that "Stop on Error" and "Smart Statement Selection" are selected, as shown in Figure 2. Normally they would be selected since they are the default. With these options selected, multiple SELECT statements (and/or multiple batch CL commands) can be executed, and the results set for SELECT statements will be displayed in a separate window.

A detailed discussion along with examples of the above changes is available in my article, Displaying Multiple Results Sets in Run SQL Scripts.

Make It Easy On Your Eyes

The next tweak is optional, but if you're like me and find your eyes are aging along with the rest of your body, you may want to increase the font size of text displayed in the Run SQL Scripts window. To change the font size, click on View in the toolbar at the top left of the Run SQL Scripts window and then click on Font.

Figure 3.

Next select the desired font and size in the resulting Font pop up window, as shown in Figure 3. Note that the default font and size is Tahoma 12, and the font and size used in the figures for this article is Tahoma 18. It's also worth noting that in V5R4 a font change only affects the text in the Run SQL Scripts window, while in V6R1 a font change affects both the text in the Run SQL Scripts window and the information displayed in the result set window.

Changing JDBC Setup Connection Options

The final tweaks that you may want to make for ad hoc SQL queries are to set up your default SQL library and library list, select either SQL or System object naming convention, and make sure the displayed information in your result set is translated correctly.

Figure 4.

These options are changed in the JDBC Setup window, which is displayed by clicking on Connection in the toolbar and then JDBC Setup in the drop-down, as shown in Figure 4.

Figure 5.

When the JDBC Setup window opens, by default the Server tab is displayed as shown in Figure 5. The Server tab allows you to specify an SQL Default Library (current library for Script Center) and library list to be used for the execution of any SQL statement or batch CL command.

Figure 6.

To select the object naming convention for the Script Center, click on the Format tab in the JDBC Setup window. "Naming Convention" is at the top left and SQL (library.object) or System (library/object) can be selected as shown in Figure 6. Most likely you will select "System," unless you are creating SQL statements to be used on database platforms other than DB2 for System i.

A detailed discussion along with examples for SQL Default Library, Library List, and object Naming Convention is available in my article, Changing the Library List for Run SQL Scripts.

There's Hexadecimal Data In My Result Set!

Have you ever run an SQL query and wound up with hexadecimal data in the result set? The first time it happened to me, I wanted to know why it happened and how I could fix it.

To make a long story short, this can occur when you derive or create a new column on the fly in your SELECT statement. That new column is a combination of data from one or more character and numeric columns, and as a result of combining the data, it's not translated correctly when displayed on your PC. Fortunately, it's easy to fix.

Figure 7.

To correctly translate the combined character and numeric data, click on the Translation tab in the JDBC Setup window, then select Translate CCSID 65535, as shown in Figure 7. Selecting this option will ensure that the correct code page is used to translate the data when it's displayed on your PC.

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.

RELATED STORIES

Displaying Multiple Results Sets in Run SQL Scripts

Changing the Library List for Run SQL Scripts

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