In the environment I work there are many programs that produce "download reports". There are not paper reports, but extracts of file(s) that are sent, via email, to the requestor. Most of these use a Query to gather information together.
Many are coded like this:
Which means that when the Query is run it will use the files that are hard coded in the Query’s definition. Therefore, if I want to perform a test and use files in a testing library I have to change the Query.
Or if I want to change which file or library the output file is in I have to change the Query.
Fortunately, there is an easy way to overcome these issues by using the parameters in the RUNQRY command.
In the example below I have:
- "Soft coded" where the files are using the QRYFILE parameter.
- Set the output to be a file using the OUTTTPE parameter.
- Named the output file (QTEMP/OUTFILE), set the member to be output to (*FIRST = first member in the file), and what to do if the file already exists (*RPLFILE = replace existing file) in the OUTFILE parameter.
- As I do not want to print the definition of the query I use the PRTDFN parameter.
- And I want anyone to use the output file so I give it *ALL authority in the AUT parameter.
RUNQRY QRY(QUERY1) + QRYFILE((*LIBL/FILE1) + (*LIBL/FILE2)) + OUTTYPE(*OUTFILE) + PRTDFN(*NO) + OUTFILE(QTEMP/OUTFILE *FIRST *RPLFILE) + AUT(*ALL)
I can take the same query and change it to print out by changing the OUTTYPE parameter. I also:
- Want a detail report using the OUTFORM parameter.
- Want the page to be 198 characters wide, which I do using the FORMSIZE parameter.
RUNQRY QRY(QUERY1) + QRYFILE((*LIBL/FILE1) + (*LIBL/FILE2)) + OUTTYPE(*PRINTER) + OUTFORM(*DETAIL) + PRTDFN(*NO) + FORMSIZE(*RUNOPT 198)
The only issue I have with this is that it generates the Query Cover Page. The flag to not print the Cover Page is within the Query, but it is not able to prevent the cover page from being generated when using the RUNQRY command.
If you want to change the name of the spool file from QPQUPRFIL to something more meaningful I describe how to do it in the post Changing name of Query/400 print file.
You can also pass parameters to a Query, read Passing parms to a Query to learn how to do it.
In my opinion by using these parameters also makes it easier for the programmer, at a glance, to get an idea what the Query is doing.
You can learn more about the RUNQRY command on the IBM website here.
This article was written for IBM i 7.1, and it should work with earlier releases too.