Wednesday, February 5, 2014

Soft coding parameters used by Query

query qryfile output printer

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:

   RUNQRY  QRY(QUERY1)

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.

5 comments:

  1. Something to look at would be to create a utility to build the SQL from the Query

    ReplyDelete
    Replies
    1. It is possible to get the SQL statement out of Query, see the post Getting the SQL statement of Query

      Delete
  2. Alvaro Roberto Meoño WongFebruary 6, 2014 at 10:36 AM

    Buenos dias, muy interesante respecto a los ejecutables del RunQuery, yo normalmente ejecuto los Querys por medio de paramatros donde el usuario asigna las diferentes compañias a proceder y los datos de cada uno, en las mismas bibliotecas o diferentes bibliotecas ej:

    *
    OVRDBF FILE(PLA931XX) TOFILE(QS36F/PLA931XX)
    *
    RUNQRY QRY(PLANIOBJ/PLA931XX) QRYFILE((QS36F/PLA931XX)) +
    OUTTYPE(*PRINTER) OUTFORM(*DETAIL) PRTDEV(*PRINT)
    *

    ReplyDelete
    Replies
    1. Translation using Google Translate reads
      Good morning, very interesting about executables RunQuery, I typically run through the Querys parameters where you assign the different companies to come and each data in the same library or different libraries eg ...

      Delete
  3. Kevin, look into RTVQMQRY ALWQRYDFN(*YES)

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.