Back in November, I mentioned that it was possible to extract the SQL source from existing queries using the RTVQMQRY command. I have since had an opportunity to play around with the IBM i Query Manager and have found it to be a surprisingly useful reporting tool.
What is it?
According to the iQuery Management Programming pdf, Query Management is the i5/OS implementation of query management Common Programming Interface (CPI). This allows you to retrieve data from a relational database and – most usefully – control the output formatting. It does this, unsurprisingly, by splitting the data extraction (the QM Query) and presentation (the QM form).
Where is it?
Broadly speaking, there are two options you need to know about in order to use Query Manager. First there is the STRQM command which will lead you through the process of creating and maintaining Query Management queries and forms. And then there are the Query Management CL Commands, all of which can be found on menu CMDQM, and which allow you to work with and execute existing QM Queries.
Inevitably, there are multiple ways of achieving the same result. What follows is what works for me but I do recommend that, if you do want to play around with this, investigate the available options to see what works best in your environment.
Before you begin
In STRQM, take the Work with Query Manager profiles option and change the following:
- The Default library for QM objects is initially set to *CURLIB. This is less than optimal, so I changed this to my own Development/Test library.
- Paging down, I also found the Default query creation mode. This is initially set to Prompted (which gives you a rather painful Query/400-like interface for creating queries). I changed this to SQL.
Define your Query
There are a couple of options here. The first, and simplest, approach is to STRQM, take the Work with Query Manager queries option and then the Create option. This will give you an SEU screen into which you can enter your SQL (assuming, of course, your default query creation mode is set to SQL). The advantage of taking this approach is that this screen includes both a syntax checker and an option to execute the statement (either all, or a sample). So you can enter, validate and test your SQL, all from the same screen.
If, however, you have an unshakable aversion to SEU, you can create and edit a source member using whatever tool or tools you prefer and then use the CRTQMQRY command to create your query. Once the query is created, your source member is no longer needed.
Whichever way you do it, you will eventually end up with an object of type *QMQRY. This is your QM Query, an SQL select that can be executed with the STRQMQRY command.
And some flexibility
This is where things start to become interesting. The STRQMQRY command includes a Set variables (SETVAR) parameter which allows you to specify up to 50 variables to be set by query management before the query is run. That’s parameters in non-IBM speak. This is easier to explain with an example, so here’s an example using a couple of realy simple purchase order tables. The code for creating these tables and populating them with a couple of one-line orders is below the fold.
And so, to the query. I have, a very simple QM Query (which I have imaginatively named QMTEST) that lists all orders in the simple orders database.
The query looks like this…
select header_order_number, header_order_date,
line_ean_number, line_quantity, line_price
from orderhdr
join orderline on header_index = line_header_index
And the interactive result looks like this…
HEADER_ORDER_NUMBER HEADER_ORDER_DATE LINE_EAN_NUMBER LINE_QUANTITY LINE_PRICE
------------------- ----------------- --------------- ------------- ----------
000001 TST0000001 01/21/2013 1234567890123 5 27.5000
000002 TST0000002 01/22/2013 1234567890321 4 16.5000
****** * * * * * E N D O F D A T A * * * * *
This is fine, as far as it goes, but it’s unlikely that you will ever want a list of all orders ever entered. So here’s the same QM Query with a date selection added.
select header_order_number, header_order_date,
line_ean_number, line_quantity, line_price
from orderhdr
join orderline on header_index = line_header_index
where header_order_date between &FROMDATE and &TODATE
Now, if I run the query interactively, it will prompt for a from and to date. More usefully, however, I can enter a from and to date when launching the query with: STRQMQRY so that:
STRQMQRY QMQRY(QMTEST) SETVAR((FROMDATE '''01/21/2013''') (TODATE '''01/21/2013'''))
Returns this:
HEADER_ORDER_NUMBER HEADER_ORDER_DATE LINE_EAN_NUMBER LINE_QUANTITY LINE_PRICE
------------------- ----------------- --------------- ------------- ----------
000001 TST0000001 01/21/2013 1234567890123 5 27.5000
****** * * * * * E N D O F D A T A * * * * *
There are a couple of gotchas to watch out for here.
- Because lower-case characters in variable names are changed to upper-case characters when passed to the command processing program, everything needs to be upper case.
- If you are passing a string (or date) surrounded by quotes, you need to use triple quotes. The ouer quotes are removed and the double quotes within the value are condensed to a single quote when the value is passed to the command processing program. No, it didn’t make much sense to me either.
You can, of course, pass a variable rather than a constant value and as soon as you do this, it really does become useful.
Design your Form
Now the fun begins. Having a parameterised, executable SQL object has its uses, but the QM form provides a presentation layer that allows you to format the output into something that’s almost pretty enough to put in front of your end users.
You can edit a source member and then use the CRTQMFORM command to create your QM form, but the source is very sparse indeed. As such, I found that using the Work with Query Manager report forms option in STRQM to go through the prompts was quite useful.
Execute anywhere
And then you’re done. QM Queries are not a universal solution, obviously, but I have seen cases where they can be useful – most notably in conjunction with a tool such as CoolSpools that will email the QM Query, as an Excel workbook, to whatever distribution list has asked for it.
And finally
This started out as a really short blog post in which my only intention was to make a few notes to help me keep track of what I’m doing. As can sometimes happen, however, it grew into this monster that you have just ploughed your way through.
The PO Tables
Trying to explain what I was doing without referring to any actual data would be a little unwieldy, to say the least, so here’s some data I made up earlier. It would be an understatement to describe it as being a bit basic, but if you slap the below scripts
The table definitions
-- First, the Order Header create table QMTESTLIB/orderhdr (header_index for column hdrindex int generated always as identity, header_order_number for column hdrorder char(10), header_order_date for column hdrdate date); alter table orderhdr add primary key (header_index); create unique index QMTESTLIB/orderhdrl1 on QMTESTLIB/orderhdr (header_order_number asc); -- And then the Order Lines table create table QMTESTLIB/orderline (line_index for column linindex int generated always as identity, line_header_index for column linhdridx int, line_ean_number for column linean char(13), line_quantity for column linqty smallint, line_price for column linprc decimal(7, 4)); alter table orderline add primary key (line_index); -- And tie the tables together alter table orderline add foreign key (line_header_index) references orderhdr (header_index) on delete cascade on update no action;
And, some data
insert into orderhdr (header_order_number, header_order_date)
values('TST0000001', '01/21/13');
insert into orderline
(line_header_index, line_ean_number, line_quantity, line_price)
values((SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM/SYSDUMMY1),
'1234567890123', 5, 27.50);
insert into orderhdr (header_order_number, header_order_date)
values('TST0000002', '01/22/13');
insert into orderline
(line_header_index, line_ean_number, line_quantity, line_price)
values((SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM/SYSDUMMY1),
'1234567890321', 4, 16.50);
Lightly Seared on the Reality Grill 