February 29, 2012 - Vol 2, Issue 5
Carsten's Security Code for IBM i
Controlling IBM Query/400 Output Files
By Carsten Flensburg
Some time ago I needed a way to identify all of the files on the system that had been created as an output file from the IBM Query/400 product. The reason for my need to identify these query output files, was a requirement to allow query output files to only reside in specific libraries.
My research did not provide me with a conclusive answer but after consulting with Chuck R. Pence of IBM who knows Query/400 inside and out, I arrived at a criteria, that so far have been accurate in determining whether a file was created by the Query/400 product.
In this article I provide 2 utilities that can assist you in finding and controlling those files created with Query/400.
Utility 1 – Locating Files created by Query/400
I have provided the source code for the CL program SEC100T. This program allows you to find all the files in a specified library that were created by Query/400. All files determined to have been created by Query/400 will cause a message to be sent to the message queue of the user running the program. Granted, it's not an elegant output option, but it does the job.
Utility 2 – The VFYQRYOUTF(Verify Query Output File) command
The VFYQRYOUTF command is used to check a specific file to determine if it was created by Query/400. If the file was created by Query/400, the command return variable is set to 'Y'. This command is used in Utility 1, to determine which files were created by Query/400. You can examine the program SEC100T to see how the command is used within a CL program.
The VFYQRYOUTF command has the following appearance, when prompted:
Verify Query Output File (VFYQRYOUTF) Type choices, press Enter. File . . . . . . . . . . . . . . Name Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB CL var for QRYFIND (1) . . Character value
Using the command inside of a CL program is quite simple. You specify the file and library to check, and the return variable specified for the QRYFIND parameter will be populated with a 'Y' if the file was created by Query/400. Otherwise the QRYFIND return variable will contain 'N'.
An F1=Help panel group is included to document all the command details.
Using VRYQRYOUTF to Detect New Query/400 Output files
Since I include the value *CREATE in the system value QAUDLVL, each time a new object is created on the system, a detailed entry is written to the QAUDJRN audit journal. The Journal Entry Type is 'CD'.
As I stated in the introduction to this article, the reason for my need to identify Query/400 output files, was a requirement to allow Query/400 output files to only reside in specific libraries.
I achieved this in two steps.
What else can you do?
The VFYQRYOUTF gives you the capability to identify files created by Query/400.
You can now use the command as the basis for your own utilities to implement your Query/400 output file policy. For example, you can set ownership and authorities for the output files. You can move all output files to a particular library. You can implement an aging routine to remove output files after a certain number of days, etc.
The VFYQRYOUTF command is comprised of the following source code members:
SEC100 -- RPGLE -- Verify Query Output File - CPP SEC100H -- PNLGRP -- Verify Query Output File - Help Panel SEC100M -- CLP -- Verify Query Output File - Build the command SEC100T -- CLP -- Verify Query Output File - CL Program using command SEC100X -- CMD -- Verify Query Output File - Command Definition
To create the VFYQRYOUTF command, compile and run the SEC100M CL program, following the instructions in the source header.
Please note that the VFYQRYOUTF command has been tested on a few systems. But, should you run into a situation where the command produces an inaccurate result, please send the information to the newsletter editor.
Note: As with all new programs, test these routines thoroughly before placing them into a production environment. No warranty is expressed or implied.
About the Author
Carsten lives in Copenhagen, Denmark, with his wife, Dorthe, and his two children, Julian and Emilie.