Problem solve Get help with specific problems with your technologies, process and projects.

Retrieving query specifications programmatically for search capabilities on file specs, etc.

Retrieving specifications on a set of queries in Query/400 so you can find what files are used, the output specs (file location, or printer / printq, etc.), or other information is next to impossible. The only way to find out the specs is either by going into every query or printing out the specifications for each query and reading through the printouts. Luckily, the printouts are all laid out in the same format and the same layout order. Because of this, I was able to write a program that would take my spooled file specifications and copy them to a flat file which I break apart into a file format that can be searched on for input file specifications, output specifications, etc. It has been a huge time saver when I have needed to recompile logicals that are used by my queries or when needing to change files to add more fields, etc. (trying to avoid the inevitable "Level for file FILENAME in LIBRARY does not match query" error). If anybody else has a better solution to the problem that is quicker (and free), I would love to hear from them.

1. Sign off / Sign on to your AS/400 system (I do this to get a new Unique Job Number from the spooled files I've created previously with queries, etc.)

2. Perform a WRKQRY and go to the listing of the library of which you want to test for specifications.

3. Print the specs out for each query in the library, making sure your OUTQ is on hold -- you don't actually want to print them. (There is a limit of 30 selections at a time for the print spec feature -- so print 3 screens at a time until you are finished).

4. After completing, go to your spooled files and you will notice the spoolfile name of QPQUPRFIL (one for each printout). Press F11 until you can see the screen that shows File, File Nbr, Job, User, Number, Queue and Library. You will need the values of File, Job, User and Number.

5. I have created a screen that you can copy the information from the WRKSPLF screen directly into blanks on an entry screen using Copy/Paste. The value of File Nbr is not important because it is an incrementing number from 1 to the last query from which you printed the specs. The first part of my CL program uses a counter and loop scheme to issue a CPYSPLF command to copy each of the spooled files into a flat file that I use in either a supplemental query or RPG program after this step is completed. (Monitor for message CPF3303 to break out of the counter loop and perform any secondary procedures - program will create this message on the CPYSPLF attempt after the last spooled file is copied to your flat file).

6. I now have the specifications in a flat file that I can run an RPG program against or a query against to retrieve the Input File Specs, Output Specs, or anything else you need from the specifications. This is because the Query Names are always stored in a specific block of 10 spaces on the report directly after " Query . . . . . . . . . . . . . . . . . ", the Query text is stored in a 30 character block directly after " Query text . . . . . . . . . . . . . . ", the input files are all stored after the lines that state "Selected files" and "ID File", etc.

7. After moving the fields out programmatically, I can then search for specific criteria on the queries, such as input specs, output specs, output fields, query description, whether the output file is to be created or replaced or added to, whether the output is in detail or summary, etc. It will even tell me if the input file is missing by a checking for the QRY1615 error after the input file spec, etc.

Dig Deeper on RPG iSeries programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.