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.

• 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.

1. I used the scanning process of SEC100T against the restricted libraries to locate Query/400 output files already found in these libraries.
2. 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.

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 Flensburg
Carsten is the author of the column "Carsten's Security Code for IBM i" that appears regularly in the SecureMyi Security Newsletter.

He has also been a long time technical editor and author for The System iNetwork. He is an IBM i application development manager for Novasol, the European vacation rental company of Wyndham Worldwide Corporation.

Carsten lives in Copenhagen, Denmark, with his wife, Dorthe, and his two children, Julian and Emilie.