• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • V6R1 Enhancements for Run SQL Scripts

    August 13, 2008 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    looksoftware:  snap the best back-end into the coolest front-end
    Computer Measurement Group:  CMG '08 International Conference, December 7-12, Las Vegas
    COMMON:  Join us at the Focus 2008 workshop conference, October 5 - 8, in San Francisco, California

    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

    New President Onboard at BluePhoenix PowerTech Acquired by Help/Systems, Private Equity Firm

    Leave a Reply Cancel reply

Volume 8, Number 29 -- August 13, 2008
THIS ISSUE SPONSORED BY:

WorksRight Software
Help/Systems
Group8 Security

Table of Contents

  • Serving Up Spreadsheets
  • V6R1 Enhancements for Run SQL Scripts
  • Admin Alert: Common Mistakes When Failing Over to a CBU

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050
  • DRV Brings More Automation to IBM i Message Monitoring
  • Managed Cloud Saves Money By Cutting System And People Overprovisioning
  • Multiple Security Vulnerabilities Patched on IBM i
  • Four Hundred Monitor, June 22
  • IBM i PTF Guide, Volume 24, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.