Taming SQL Queries with a CozTool

Bob Cozzi has done a lot with the IBM midrange server platform–probably more than most, including founding the platforms’ first publication and presiding over its largest user group at one point. But Cozzi may have top it all with his latest creation: A handy little database utility called SQL Query File that he developed and sells through his software company, CozTools.

SQL Query File is a green-screen tool designed to let IBM i professionals quickly and easily query their DB2 for i database using standard SQL, right from the command line. You can cut the SQL you previously used with Query/400 or third-party tools, and paste it right in Cozzi’s tool, where it might run faster.

But just issuing SQL queries from the command line for the database’s SQL Query Engine (SQE) itself isn’t enough to write home about–there are lots of ways to do that. What sets SQL Query File apart is its output capability. Once users have run their query, they can have the output of that query appear in an variety of formats, including PDF, HTML, text, CSV, Excel, JSON, and XML–not to mention putting the results in a database file or spilling it out to the screen or a spool file.

CozTools’ SQL Query File outputs the results of DB2 for i queries in a variety of handy formats.

Cozzi offers the following explanation for the significance of this: “What good is a tool if every time you need an alternate output format, you have to buy another tool, purchase and optional plug-in, or rewrite the request so it works with some other unrelated component?” the company writes. “With SQL Query File, you simply specify the output format on the OUTPUT parameter and your existing industry standard SQL statement is used to produce the data in the format you request.”

Nothing is easier, says CozTool. While others require the user to learn HTML, JavaScript, or “a technology that’ll be gone next week,” SQL Query File does the work for you. What’s more, users can get “cool scrolling HTML tables” via the WEBSQL CGI program that’s included with SQL Query File. According to the company, this “allows users to run the same SQL statements from the Web, and automatically create an HTML scrolling table,” which some people call “subfiles for the Web.”

The SQL Query File tool can be used to generate ad hoc reports, perform data mining on the DB2 for i database, or execute data conversions, the vendor says. And the capability it provides the user to adjust the queries and tweak the parameters enable it to fill a number of other needs. User will no longer write a program to list the contents of a database file.

The software also includes the WRKQRYF (Work with File using SQL Query File) command, which lists the fields in the file and allows users to selectively build their SQL statements and view the results on the green screen or as printed output. When the user is done fiddling around, he can save the SQL statement and refer to it as often as needed.

The product is free for customers on i5/OS V5R4 and IBM i 6.1, but requires payment for IBM i 7.1 and IBM i 7.2. Pricing starts at $1,995 for up to a six-core machine, or $3,995 for an unlimited number of cores. It can also be licensed on a monthly basis, starting at $35 per month for one or two cores. For more information see www.sqlqueryfile.com/.