Better SQL Queries, and That Ain't All

by Dan Burger

If you have ever run SQL scripts in Client Access Express, you know it is slower than a three-legged turtle walking up a hill. After you enter a query, there's time to hike three blocks, have a donut and a cup of coffee, and come back before you get any results. Not that we're anti-donut or anything, but there are quicker ways to accomplish queries--and stored procedures and logical files--than what you may be used to.

One of the ways to add some juice to your system is with SQLThing Enterprise Edition 4.2, which was recently released by Client Server Development, a Jacksonville, Florida, company specializing in client/server tools for the iSeries and AS/400, DB2 Universal, Sybase, Oracle, and Microsoft SQL Server environments.

SQLThing was designed to help programmers and power users extract and collate information on their AS/400 systems, with features that include a stored procedure editor, report writing capabilities, a 3-D spreadsheet, graphing capabilities, export of information to PC file formats, and Visual Basic and Java code generation.

For those who test and develop SQL statements, develop PSM programs, regularly explore databases, build and distribute grid reports, or develop queries, SQLThing will likely deliver performance and productivity improvements that stand out against the iSeries' built-in tool, Query/400. Its performance-enhancing capabilities can be attributed to the C++ and Visual Basic design.

SQLThing might be most commonly used as a query tool, but it can also generate Active Server Pages or Java classes to call stored procedures. It connects to your AS/400 via ODBC and operates from Windows 95, 98, ME, NT, 2000, and XP environments.

Client Server Development introduced SQLThing in the late 1980s as a programmers' tool for the execution and testing of SQL statements. It was first ported to the AS/400 in 1996.

The most noticeable difference in this enterprise edition of SQLThing is the GUI interface that offers better integration with the iSeries. For instance, it is now possible, with the SQL Editor, to create SQL statements, test statements, debug statements, export results, format grid reports, and other functions.

The advantages show up in areas such as the capability to persist queries and reports into an XML format and then load them into the SQLThing End User Tool, where reports and queries can be run into an interactive viewer or output in PDF, HTML, or several other popular PC file formats.

Users also will find difficult queries, such as those that involve joining several tables, are made easier because of a friendly query wizard. Another convenient item is the query diagrammer. It breaks the query into an easy to understand relational chart with symbols that relate to objects in the database. The benefit comes from being able to easily follow the symbols to get metadata on the objects.

SQLThing Enterprise Edition also supports an integrated job-log viewer for debugging and performance tuning complex statements. By placing jobs in debug mode, users can see all of the iSeries optimizer messages and warnings pertaining to a query, which is a convenient performance diagnostic tool.

SQLThing Enterprise Edition also prompts for parameters, allowing users to test statements as they execute from target environments. Statement results also can be viewed from a data window, which features sophisticated reporting and grouping capabilities.

SQLThing Enterprise Edition automatically prompts for any parameters required by a query, which practically eliminates the chance of incorrect data being used. It also persists the parameters, so that subsequent calls to the same procedure or statement can use the same parameters. The importance of this, according to Howard F. Arner Jr., president of Client Server Development, is that queries need to be tested in the way they will be executed in the application program; otherwise it is possible that the optimizer may choose a different implementation of the query based on hard-coded values.

"Using parameters in statements is a best-practice for SQL," Arner says. "We support testing statements with parameters, and Client Access Express can't test statements that have parameter markers." Arner is also the author of the book SQL at Work.

The results of any query can be exported to Access, Word, Excel, Lotus 123, dBase, Paradox, Foxpro, XML, Text, and CSV.

Pricing for SQLThing Enterprise Edition 4.2 begins at $950 per copy, with discounts offered on multiple-copy purchases. TheSQLThing End User Tool licenses are $150 per user. Site licenses and licensing per iSeries server are also options in a purchase agreement. Maintenance and support costs are annual and based on 15 percent of the license fee.

Current versions of the product are available for trial use by contacting Client Server Development.

Sponsored By FAST400

What makes IBM different from Microsoft regarding Fast400?? What is Fast400? You are hearing a lot about Fast400 aren't you? But what is Fast400? Fast400 is a "tuning" product for the iSeries. Fast400 will allow an iSeries server to utilize the available CPW for interactive processing. IBM would have you believe that these interactive cards that cost thousands to millions of dollars, actually add value to your server. By buying Fast400, you do not ever need to buy another interactive card for your iSeries. For a free demonstration of Fast400, please visit www.fast400.net. Why Fast400? A few years ago Microsoft would not let other software companies build tools to work with the Windows operating system. Microsoft did all kinds of scurrilous things to stop other manufacturers' software from working on their platform. They would put code in the base operating system that prevented other companies code from working properly. IBM even had these issues with Operations Navigator. In the early days of Operations Navigator, the developers in Rochester had to scrap early versions because Microsoft did not want IBM leverage on what was proprietary to them. Netscape also had a few problems using the Windows operating system. The result Now we all know what happened to Microsoft. After spending tens of millions of our tax dollars in the trial, the US government told Microsoft that they were acting as a monopoly and what they did was not right or fair. The similarity IBM is doing exactly the same thing to Fast400 as Microsoft did. IBM has changed the operating system of the iSeries 400 to prevent Fast400 from working. In fact this has been done several times now, and each time the Fast400 developers produce a new fix to circumvent the IBM action. Why does IBM do this? because Fast400 takes money out of IBM's pocket. The potential for IBM to make billions from its user base, for delivering virtually no product is tantamount to corporate deception! Did IBM change the operating system when EMC introduced a low cost storage solution for the iSeries? The future The cat and mouse game between IBM and Fast400 is already a year old. Every time IBM changes the operating system to disable Fast400, the developers of Fast400 produce a new version within days to enable it again. Does Fast400 have a commercial agenda? Of course it does. Fast400 is in business to provide its clients with added benefits, which will maximise the interactive performance of iSeries 400 servers. And as we are a business, why shouldn't we charge a nominal fee for that service? A fee that our clients see as being fair and proper. After all, it's not Fast400 that is making enemies in the user base. As long as IBM wants to play "David and Goliath" we will continue to "out" the giant. Fast400 is not running, you can be assured!! For more information, please visit www.fast400.net.