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.
- The specified file is a physical file.
- The specified file is a data file, as opposed to a source file.
- The specified file is externally described.
- The total number of record formats defined for the file is 1.
- The file is not an SQL table.
- The file was not created by *IBM.
The file object specifies a blank source file member as its object creation source.
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.
I used the scanning process of SEC100T against the restricted libraries to locate Query/400 output files already found in these libraries.
Monitor the QAUDJRN audit journal (‘CD’ entries) for newly created files, and report on any Query/400 output files created in restricted libraries. I also programmatically move these offending files to a quarantine library, while the violation of company policies is investigated.
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
Download a zip file containing all of the source code.
To create the VFYQRYOUTF command, compile and run the SEC100M CL program, following the instructions in the source header.
Articles on Apis 