Analysis: Query's Big Brother
By John Bussert
Retrieving data is one of the most basic functions we perform with our systems. At least we should. We spend all this time getting the database design right and getting the data input forms/screens the way we want them, at some point we want to get useful stuff out of it. That's where report writers, data warehouses and other extraction tools come in.
On the AS/400 there's the lowly Query utility that's actually not too bad at extracting data. It's not great but I have a lot of clients using it for much of their business reporting. Some have even graduated into linking multiple queries together, where one creates a work file and a follow-on query then reports on that data.
Query has been on the AS/400 since the days of the System/38 (and really the 36). Most people know, however, that there is a big brother to Query called Query Manager (QM). Many users see the commands and documentation on this and assume that this is just a regular query tool. It's not. It's substantially more robust than Query, although quite a bit different as far as its use goes.
Query Manager is a utility that allows you to create queries and format various forms. It includes a scripting language with which you can build procedures that execute the queries in sequence. It was born on the larger systems as part of System Application Architecture (SAA), IBM's attempt at industry standardization from a few years ago. Because of this though, it may "look and feel different" from other tools you've used on the 400. But don't let that be a deterrent to trying it.
You more than likely have the tool on your machine and don't even know it. You can verify this by looking at your licensed programs (GO LICPGM) and scrolling down until you see "Query Manager and SQL Development Kit." If you have that then you have Query Manager.
Invoke STRQM to get into the environment. You'll probably want to take a look at the manuals or the softcopy books. In the V4R3 books, a good place to start is "DB2 for AS/400 Query Manager Use," SC41-5212.
One advantage of QM is that it gives you more power over the format of the query report (and the format can be used with multiple queries). Another is that once you've built a format you like, you can run multiple selections gathering different data, sorting it various ways using the same format.
It's really built on top of SQL with some formatting and print capabilities built in. The big advantage is its scripting and formatting capabilities along with distributed reporting support. You can also use QM to help learn basic SQL skills. Since it uses SQL as an underlying architecture, you can build a report, then view the SQL syntax it generated. These reports can be built such that they prompt the user to enter selection data such as dates or customer numbers.
One of the better features of QM is its scripting facility. Instead of building a number of queries and a control language (CL) program to string them together, you can use QM and build a QM Procedure. This is done much like a CL Procedure where there are commands to build, run, manipulate and process queries. The nice thing about this is the control you have without using CL. The disadvantage of course is learning the new commands, although there really are not very many.
Because it incorporates Distributed Relational Database Architecture (DRDA) technology, another important feature of QM is its ability to link to multiple databases across machines. There is also a high level program interface (similar to an API) that allows you to call QM functions from an RPG (or Cobol) program. This is a little like using imbedded SQL, but might be easier for some to understand because it uses the Call/Parm interface.
The net of all this is that there is a tool that you probably already have that can be very useful if learned (ah - that naughty word!). Any tool that can reduce your workload or backlog is worth the look.