• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • FROG: PC-based SQL for DB2 for i

    September 16, 2009 Ted Holt

    It is my pleasure to feature a software tool that has proven very valuable to me. It’s called FROG. It runs on a PC and provides an SQL interface to DB2 for i. What’s more, it’s free. Let me show you one way I frequently use it.

    If you’ll visit the FROG home page, you’ll find that FROG is provided to you free of charge by Innovative Systems, LLC. You also find information about what you don’t need (e.g., an ODBC connection) and what you will have to have (e.g., Client Access).

    Figure 1.

    Figure 1 is the DB2 window, which includes three primary panes. I want to draw your attention to the middle one, the one into which you key SQL commands, and the bottom panel, in which query results are displayed in a grid. In this example, I retrieved six rows from a table called ITEMDEMO.

    Once I have the data in the grid, I can easily put it into Excel. To do so, I right-click on any row in the grid and choose Select All Rows. FROG responds by displaying all rows of the grid with a colored background.

    Next I right-click again and select Copy data to clipboard as. A submenu appears, and I select CSV. At this point the data is in memory. In Excel, I use the usual Windows paste feature to load the data into a spreadsheet.

    I’ve used this technique to create numerous one-shot queries and prototypes.

    Let me point out a couple of features of the SQL command in the example.

    select itnbr as "Item", 
           replace(replace(itdsc,',',' '),'"','''') as "Description",
           ittyp as "Item type"
    from tholt.itemdemo
    

    Notice that each column has a legible correlation name. Whatever you put between the quotation marks becomes the column heading, stored in the first row you paste into the spreadsheet. Notice that the correlation name can contain blanks.

    Also, take a look at the second expression in the select clause.

    replace(replace(itdsc,',',' '),'"','''') as "Description",
    

    Commas mess up the paste operation, so the inner replace replaces commas with blanks. Quotation marks sometimes get dropped, so the out replace replaces quotation marks with apostrophes.

    FROG is handy, and the price is right. I’d like to hear from you if you give FROG a try.



                         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

    Manta Technologies:  Fall Sale on i training courses! Order by October 15 and SAVE 25%
    BCD:  Webinar, Sept. 23 - Rapidly Web Enable your IBM i 5250 Applications in a Cost Conscious Market
    COMMON:  Celebrate our 50th anniversary at annual conference, May 2 - 6, 2010, in Orlando

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.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 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
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Symtrax Updates Archive with Security and Search Improvements Start Planning for Power7 Iron Now

    Leave a Reply Cancel reply

Volume 9, Number 28 -- September 16, 2009
THIS ISSUE SPONSORED BY:

Help/Systems
System i Developer
WorksRight Software

Table of Contents

  • FROG: PC-based SQL for DB2 for i
  • Do-It-Yourself Data Types
  • Admin Alert: The Road to Live CBU Fail Over, Part 2

Content archive

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

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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 © 2025 IT Jungle