Cozzi Refines Data-Centric Ideas For IBM i Report Writer

April 13, 2015 Dan Burger

When IBM invests in IBM i, the database is first in line. In terms of “people hours” nothing comes close to the work being done on the database. When new versions of the operating system arrive, it’s the database that gets the greatest number of enhancements. Same with the Technology Refreshes, in almost all cases. Members of the ISV community and the Large User Group of top Power Systems companies are usually quick to take advantage, but there’s a bigger picture.

The power of SQL and data-centric programming is gaining IBM i converts, maybe more so now than at any time since SQL came on the scene when the relational database was invented. That’s been a while. SQL is older than the AS/400, and as old as the System/38, which may surprise a few people who haven’t left a trail of code that can be traced that far back in history.

Bob Cozzi has done some coding in his time. And his time in the IBM midrange is measured in multiple decades. His RPG books published in the 1980s and 1990s sold faster than snow shovels in Rochester, Minnesota. A lot of people learned RPG reading Cozzi’s books and attending his training sessions. These days he describes himself as a software developer and a consultant. His small company, Cozzi Productions, just announced version 2 of SQL Query File, IBM i-based SQL command tool and report writer.

His focus on SQL puts him in the data-centric development camp, which overrides the traditional application-centric development ideas that prevailed 20 or more years ago. To a large degree, application-centric coding still prevails today in IBM i environments, but as SQL use increases the data-centric ideas come along with it.

Data-centric development removes repetitive processing steps from application coding, which shortens the app dev process and, in theory, allows programmers to focus on improving business processes and writing better code. When processing is handled on the database side, it is written once. When it is handled on the application side, it requires the same code to be rewritten each time it becomes part of a new program. Coding at the database level reduces the coding at the application level, which ultimately saves time and money. Taking a data-centric approach means the database does more of the heavy lifting for the application, which gives the application developer to concentrate on building highly efficient business solutions.

That bit of background brings us to Cozzi’s SQL Query File tool, which first came into this world in July 2014. The just released version 2, Cozzi says, is “refined and hardened,” after being tested by several companies.

The feedback led to several interesting conclusions that Cozzi used to revise the product. The basic capabilities in the original product allowed users to specify the type of data output that fits the goal. It could be a normal interactive display or a 132-column report, but it could also be a PDF, or a plain text file. Most importantly, Cozzi came to realize users wanted to output to CSV (comma separated variable file), the type frequently used when exporting DB2 data for use with Microsoft Excel. He also learned the original output for Web use, which included HTML and XML, was off target. The big demand for Web use is output to JSON. Although that is not available at this time, Cozzi says it’s being tested and should be available soon.

“JSON demand is much greater than XML demand,” Cozzi says. “What’s in the database today to produce and consume XML is complex and it puts people off. I’ve used XML for a long time. But it is too hard. The people who are making the XML standard are making it too hard. It’s trying to be everything for everybody instead of doing what it does best, which is describing short bursts of data. With all the attributes and functions you have to call, the simplicity is being lost. It’s much more complicated than it needs to be, particularly the SQL DB2 implementation.”

There were a few other lessons learned, which led to adjustments.

For instance, originally SQL statements were stored in IFS files. That practice was replaced by the capability to run source file members. This allows Cozzi to add new macros when the need arises. Specifically that occurs when IBM releases new DB2 services as it often does with IBM i OS releases and Technology Refreshes. The macros provide access to the DB2 services.

“The DB2 services are ‘table created’ to allow the use of SQL to query system information, which formerly required APIs,” Cozzi explains. This simplifies access to useful information such as user profiles, last-sign-on date and time, disk status, and system values, which are all available from database files.

The consistency of IBM’s DB2 services implementation leaves a lot to be desired, Cozzi says, because they are not all located in the same place or named intuitively.

So he designed SQL Query File to reformat the services in a consistent and convenient way to view the data and then it saves them as a source file member. They become accessible by running an SQL statement, which is an ease of use benefit.

“This doesn’t require people to know SQL. It just helps them get to this information,” he says. Access is managed via a CL command line.

Another enhancement learned from testing SQL Query File version 1 was a performance boost. Cozzi says the interactive and print output options in the latest release are “roughly twice as fast” as the prior version. Better yet, CSV output in the new product provides as much as a 10x performance. In one instance that he noted, CSV conversions that were running at nearly 5 minutes were trimmed to fewer than 20 seconds. “But your results may vary,” he wisely cautions.

Pricing for the SQL Query File Command Line Tool is $1,995 for systems with up to six active cores. A no-max core license is available for $4,000.

Shops that are running IBM i5/OS V5R4 or IBM i 6.1 can download the product without a charge. A license purchase would be required when updating to IBM i 7.1 or 7.2.

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search