Controlling IBM Query/400 Output Files

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.

Read the entire article

Download the zip file containing the source code.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s