Time To Dump Query/400

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