AS/400 Programming Tips, Tricks, & Techniques

Nov 2 2012   5:55PM GMT

Time To Dump Query/400

John Andersen Profile: John Andersen

I admit opening up Query/400 (IBM Query for i) on the rare occasion to crank out a straight forward report. I think its more for nostalgic reasons because Query was the first reporting tool I learned on the AS/400 so many years ago.

But my Query/400 use is becoming less and less and instead I am opting for reports, database mining and ad-hoc queries with interactive SQL or with IBM DB2 Query Manager product if I need something more polished or a paper and ink report.

Frankly, if your STILL running with Query/400 here is why you should be making the switch to SQL based tools and programs:

1. Query is getting really long in the tooth.

Have you noticed new features in the product? And because it’s not actively being developed you have to wonder how much longer IBM will license the product, or if the next operating system release will just break Query altogether.

It may be better to switch today than being forced to switch down the road.

2. The SQL standard is the now and the future of the IBM i platform.

The “native” tools like Query and OPNQRYF came about long ago when the database was not standardized on SQL. Starting on OS version 5 the database engine that plans and executes your queries evolved into the Classic Query Engine for older “native” tools and the SQL Query Engine for SQL based tools.

All new research and development is going into the SQE engine. And the performance gap is really widening, for instance you don’t have to make any changes to your SQL but a simple Database Group PTF can yield amazing performance gains… but only if your using SQL and the SQL Query Engine.

And by switching to SQL all of those quirky hacks in Query/400, like using the microseconds portion of a TIMESTAMP in a result field to convert data, are a thing of the past by using features built right into the SQL language like casts.

You can also add logic into your SQL statements with features like CASE, that just are not possible with Query or you had to kludge together multiple queries and some CL code.

3. You have a perfectly capable replacement reporting tool in Query Manager.

Sometimes you want a nice formatted report for printing, this is where marrying SQL and the DB2 Query Manager tool works.

Query Manager is really cool and even though its been around it is driven by SQL and is modern. And the real beauty is you probably already have Query Manager licensed, some shops don’t even realize they have it!

Query Manager addresses all of the shortcomings of Query/400… in particular data security and job management.

Using the configuration options in Query Manager, if you have regular users writing queries, you can keep them from wiping out data by accident or swamping the system by with excessive previews.

Some 400 shops are highly leveraged with query definitions… but there are some tools to convert them to QM Queries for you. To get started check out the RTVQMQRY and RTVQMFRM commands that will extrapolate the SQL and report form (Query Manage uses a query and report form together for output) out of your query definitions into QM objects that are ready to run.

-John Andersen

John Andersen is an irreverent IT manager and champion of the IBM i platform. For more information on cranking out reports with Query Manager check out his site at www.qmquery.com

3  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    I fully agree. I've recommended migrating off of Query/400 for almost 20 years since first encountering and learning QM.   And the real beauty is you probably already have Query Manager licensed...   It should be noted that licensing is part of the SQL Dev Kit and generally covers the user interface for QM rather than the ability to compile and run QM queries and report forms.   Essentially all AS/400s since V2 have had QM capability. Those that licensed the SQL Dev Kit also had the QM user interface as part of that license. The UI makes it easy to work with QM, but it's not required. The QM UI is available wherever the STRSQL interactive SQL command is found.   For users, giving access to STRQM is preferable to STRSQL. Within QM, you can restrict users to using only SELECT for example. There are no similar restrictions available for STRSQL.   For users familiar with SQL, there should be few issues with not having STRSQL. The biggest drawback I see is that there is no session history. But previous queries can be saved, copied and modified. For users familiar with Query/400, the QM "Prompt" mode of query creation provides an interface that is very similar to Query/400; no SQL is needed in "Prompt" mode. Users should be able to adapt quickly, often with no help.   The ability to define queries separate from report formats takes a practice session or two, since Query/400 has no ability to work the two parts individually. After it is understood that a single query might be used with numerous different report definitions or that a single report definition can be used to present many different queries, users often develop extensive query/report libraries, interchanging components to meet a variety of needs.   I can understand sites not wanting to lose their investments in hundreds or thousands of old Query/400 query objects. But I haven't seen any good reasons not to stop creating new ones and instead to switch to QM.   Tom
    125,585 pointsBadges:
    report
  • John Andersen

    @TomLiotta

    Thank you for your comment. And exactly spot on... QM addresses ALL of the security and job management shortcomings administrators have with Query/400, and then a few more.

    As far as the licensed product goes I was referring to the STRQM interface :-)

    -John Andersen

    205 pointsBadges:
    report
  • John Andersen
    [...] & IBM i Tutorials « Time To Dump Query/400 Nov 5 2012   9:13PM [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: