If you have been hanging around an AS/400, iSeries or IBM i for any length of time you have undoubtedly used Query/400 or IBM i query. For cranking out reports it is a great menu driven tool that runs right on top of the familiar green screen.

I like Query/400 primarily because it has an easy learning curve. Don’t get me wrong, I love the power and flexibility of using SQL on the i, but query has a place in my heart just because it simple and you don’t need to learn any syntax to crank out report.

But maybe you haven’t yet flocked to the new DB2 Web Query or transitioned over to another reporting too. I get it, some shops have dozens or even hundreds of existing queries and they are completely happy with using them.

I am not here to try and convince you to drop your beloved query. But instead you can start breathing new life into your query definitions by blending them together with tools in SQL like views. Let me explain.

A lot of reports revolve around changing variables like dates. One of the major drawbacks to query is not being able to pass a parameter, like a date range, cleanly. The typical way to handle query parameters (without writing any CL programs or making use of Start Query Manager Query) is forcing the user to open up the definition, change the select records statement to include the desired range, then save it and run.

But by combining the best of both worlds you can create a view that does this for you automatically and use that view in your query report.

So in the case of our query that uses a date record selection. Let’s say you pull records in an existing query definition from an order history file for the past thirty days. By creating a view over the order history file with a where clause and combined with an expression, something like \”where orderdate >= current_date – 30 days\” and boom, anytime you query this view you will only get those records.

You can quickly see the power of using views with your queries and reports. Having to create secondary files that contain ranges of data and joining them to your queries are a thing of the past. And you did all of this without a lick of CL code. Pretty cool, huh?

Now the really good news about views. A view on the IBM i is created as non-keyed logical file. But they do not take up a bunch of system overhead and resources. No keys mean the system does not have to maintain an additional access path for the views you created. This is a major benefit to using views.

But now for the bad news about making and using views. Because a view is not keyed and has no access path to retrieve data from the underlying table, the SQL engine on the IBM i evaluates a query when the view is accessed.

So if performance and wait time is important when using custom views you are going to want to review the access paths to the file or files the view is built on top of and make use of them. You may even consider creating indexes to optimize things a bit, but that is a topic for another day.

John Andersen is a long time AS/400, iSeries and IBM i programmer and manager. Check out his site and discover how to start cranking out your own Query400 and IBM i reports in a flash.

John Andersen is a long time IBM i advocate, programmer and IT manager. To discover how to use Query/400 on your AS/400, iSeries and IBM I power system check out his site at http://www.queryinaflash.com

Author Bio: John Andersen is a long time AS/400, iSeries and IBM i programmer and manager. Check out his site and discover how to start cranking out your own Query400 and IBM i reports in a flash.

Category: Computers and Technology
Keywords: as400,iseries,ibm i,query/400,sql,db2