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.

Continue reading →