Recently, I needed to devise a process to calculate income projections for the upcoming year based on historical data. The task was simple enough: collect the monthly income figures for the past 12 months, drop any figures for known contract terminations, and present the results in a spreadsheet. This would give management a simple tool to massage the data as it saw fit. From those numbers, managers could plan a budget for the coming year.
Although this scenario is an oversimplification of the process, it captures the spirit of the request. Data for this type of request is never in short supply. The key is the tool you use to extract the data, and my favorite tool for this purpose is SQL.
Using the QMQRY Commands
SQL is probably familiar to most developers by now. If you have the SQL Development Kit (SDK), you can embed SQL into other languages such as RPG and COBOL, or store it in a source member to use with the Run SQL Statement (RUNSQLSTM) command. You can also start an interactive SQL session by using the Start SQL (STRSQL) command, or take advantage of Navigator for i's Run SQL Scripts. Recently, IBM released a PTF for i6.1 and i7.1 for the Run SQL (RUNSQL) command as well. Clearly, we have plenty of ways and plenty of reasons to use SQL.
At least one available option doesn't require you to purchase additional software to use SQL: Query Management Query (QMQRY) commands. I like these commands because they let me run SQL statements on any IBM i system, without worrying about whether the SDK has been installed.
If you've never used QMQRY, here are some easy steps to get you started. First, you'll need a source file to hold your SQL statements. You can use the following command to create source file QQMQRYSRC:
Notice that the record length is 91, not the default of 92. QMQRY won't work with the default length of 92; otherwise, it's a normal source physical file. You can use the editor of your choice to write your SQL statements. (I won't delve into the details of SQL because plenty of people have already written fine articles on the subject.)
Let's look at three QMQRY commands that you'll become familiar with quickly. I'll list only the most useful parameters for each of these commands. The Create Query Management Query (CRTQMQRY) command creates an executable object from your source:
SRCMBR(yourmember) TEXT('My First QMQRY')
The Start Query Management Query (STRQMQRY) command executes your QMQRY:
OUTFILE(yourlib/yourfile) SETVAR( (var1 quoted-value) (var2 quoted-value) )
The Delete Query Management Query (DLTQMQRY) command does just what you'd expect:
Pass the Parm, Please
Of all the options available for running SQL, I find QMQRY the most useful for recurring tasks because it lets me use substitution variables. In the scenario at the beginning of this article, suppose we want to extract data for calendar year 2012 to project income for 2013. However, we might also want to select 2011 data to see whether we're on track in 2012. A simple way to do this is to pass in a substitution variable containing the year. Consider this SQL statement:
We can change that statement to the following (note that the substitution variable begins with an ampersand and must be all uppercase):
To execute this statement, use the following command:
OUTFILE(yourlib/yourfile) SETVAR( (year '2012') )
We must pass in all variables as quoted text. The following statement shows how to pass a text value:
Of course, we can do the same with embedded SQL. However, consider the following SQL statement:
We can now pass in the following substitution variables:
(condition 'fieldx = 123') )
In fact, we could write the following SQL statement:
Now we can pass in five lines of SQL statements (maximum 55 characters per variable). This technique lets us easily build ad hoc queries, but it can also pose a security problem because any valid SQL statement can be processed this way.
My final favorite thing about QMQRY is the OUTPUT(*OUTFILE) OUTFILE(yourlib/yourfile) parms. These parameters make it easy for us to select data and write it directly to a file without having to do any special file setup in advance. If the file doesn't exist, QMQRY will create it for you.
With QMQRY in my toolbox, I can quickly extract data for any business analysis request. Try it out, and you'll have those results in no time!