V6R1 Enhancements for Run SQL Scripts

Published: August 13, 2008

by Skip Marchesani

Among the many enhancements that V6R1 provided for System i and i5/OS are several for Run SQL Scripts, which improve its function and broaden its usability. Run SQL Scripts is part of the Database function in System i Navigator.

Save Script to System i Source File

Prior to V6R1, scripts for Run SQL Scripts (a.k.a. SQL Script Center) could only be saved as PC files with a .SQL extension, either on a PC or on the System i IFS. Starting with V6R1, these script files can now be saved on the System i as a member in System i source file. This means that SQL scripts that contain database definitions and are used to create one or more database objects can now be stored in a source file like QDDSSRC along with the rest of the source members that contain database definitions. To save to a source file, click on File in the toolbar and you will be prompted with the various save options.

The following, while not a Run SQL Scripts enhancement, is related and important to mention. V6R1 now allows the SQL Statement Processor (RUNSQLSTM) to execute a Run SQL Scripts script (as long as it does not contain a SELECT statement) that includes CL commands. That script can be stored as a PC file on a PC or the IFS, or as a System i Source file. This provides greater flexibility in designing, writing, and executing SQL scripts.

Result Set Window

Prior to V6R1, the headings for the columns in the result set window used the column name as the column heading. If a database table also had column headings defined that were more meaningful than the column name, they could not be displayed. V6R1 provides the capability to toggle back and forth between column names and column headings where both are available. To toggle back and forth between the two, click on Connection in the toolbar and then JDBC Settings, and then use Other tab in the resulting window to see the column heading options for the result set window.

Check SQL Portability

There are times when you will write an SQL Script that will have to be executed on the System i and other relational DB platforms such as Oracle, Sequel Server, or my SQL. Such scripts must conform to the ANSI standard so that they will be portable to the other relational DB platforms. In V6R1, Run SQL Scripts gives you the capability to compare the current SQL script to the ANSI standard to check for portability to the other relational DB platforms. To enable this function, click on Options on the toolbar, and then select Check SQL Portability in the resulting drop down menu.

Change Font Size for Result Set Window

For those of us with aging eyes, the default font size of the SQL script window and the result set window can be a challenge to read. Back in a previous V5 release we received the capability to change the font size for the SQL script window. Now V6R1 provides the capability to increase the font size for the both the SQL script window and result set window. Just click on View on the toolbar to change font size for both. Not only does this help those of us with the aging eyes, it enhances Run SQL Scripts as a tool to teach and demo SQL statements and functions.

Improved Result Set Output Options

Prior to V6R1, the only output option for Run SQL Scripts was to display the result set. Without playing games, there was no way to have the output go directly to a spreadsheet file, or direct the result set to a printer. V6R1 provides this capability, which makes it much easier to use Run SQL Scripts to retrieve information from a System i database in the output format required--display, printed report, or spreadsheet format (or CSV or text). To enable result set output options, click on Options on the toolbar, and select Allow Save Results. Run an SQL SELECT statement and right-click on the result set window for the print option or to show the Save Results dialog.

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