You are currently on IBM Systems Media’s archival website. Click here to view our new website.

IBM i > TRENDS > WHAT'S NEW

Sinking in a Query/400 Quagmire?

Use Print_Query_Definition to help modernize your database

Use Print_Query_Definition to help modernize your database

If you’re like many customers, you may have accumulated hundreds, perhaps thousands, of Query/400 objects over the years. While Query/400 has been a useful and popular tool, its use does seem to result in a large number of reports that tend to linger around and often require maintenance. I recently met a customer at COMMON who claimed to have more than 20,000 such objects! There are various reasons for this:

 
 

  • Many customers create new, ad-hoc queries on the fly to perform quick analysis of their data, then save that query “just in case” they might need it later. Often they forget about it and create an identical or similar one a short time later.
  • Often the accumulation occurs as a result of satisfying various yet similar end-user reporting requirements. Based on the user’s specifications, the query developer creates a report that’s quite satisfactory – but days later the user requests another one that has a slightly different twist to it (such as sorted by a different column or filtered to only show data for a specific column value). The next week a user in a different department requests the same query but one that accesses a file in a different library. And on and on it goes!

Whatever the reasons, over time you suddenly realize that your collection of Query/400 objects has become a sizable one that’s difficult to manage!

In addition to its propensity to result in redundant objects, Query/400 has some major drawbacks from a technological standpoint:

  • For database access, it uses the Query API interface to DB2 for i and not SQL. This means that all Query/400 requests are processed by the Classic Query Engine (CQE) and not the engine designed specifically for SQL access – the SQL Query Engine (SQE). This almost always equates to inferior performance because CQE doesn’t have the optimization technology built into SQE. It also means less-sophisticated database collection and analysis tools.
  • It doesn’t offer modern interfaces (such as web browser) for development activities and actually running the queries.
  • It doesn’t offer modern output formats such as PDF, Excel and HTML.
  • Because it doesn’t use SQL, it can’t take advantage of the seemingly endless number of features built into this industry database language. Common Table Expressions, stored procedures, sub-selects, unions, intersections, system and user defined functions, OmniFind text search server, grouping sets and cube/rollup functions are all examples of the power that’s built into SQL – none of which can be directly leveraged by Query/400. This is why you see so many examples of “query chaining” – where the report developer must write multiple Query/400 objects and chain them together in a CL program. In most cases, a single SQL statement could satisfy the same request! Moreover, since it would be processed by SQE, it will likely run much faster!

All of these are reasons to consider utilizing a different way to analyze your data. What are your options here? Well, you could use SQL if you’re proficient or comfortable using this language. This would allow you to leverage SQE but you’d still need to incorporate some additional process to get the data into the desired format such as PDF document or spreadsheet. Or you could employ tools (such as DB2 Web Query) that generate SQL for you AND have built-in features to send the data directly into a spreadsheet.

Gene Cobb is a DB2 for i technology specialist in the IBM i ERP development team.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

It’s Technical, Dear Watson

The “Jeopardy!” playing computer’s feeds and speeds

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters