FROG: PC-based SQL for DB2 for i

Published: September 16, 2009

by 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