Tracking query in the system

[Guest.Visitor]

My boss wants to track all the queries in the system by library. He wants a report that shows the Query Name, Text/Description, Files Used, and Records Selections. Is their an IBM file that store these informations that I can query or write a program against that file? Is there a way I can produce this report without manually typing the data? We have 1100 plus queries in the system now. Please help. Thanks, Ken

[B.Myrick]

Ken, For a starter, you can use: dspojbd obj(*allusr/*all) objtype(*qrydfn) output(*outfile) + outfile(yourlib/queries) This will give you a file name QUERIES in the library you define in the OUTFILE keyword. You can then use (: Query/400 :) to generate a report by library, the name, description, date created and other information. This does NOT give you the file or printer information or output type, such as printer, display or file. You can use DMPOBJ yourlib/queryname type(*QRYDFN). You can convert the resulting spool files to a diskfile and parse the file. You will need a list of physical and logical files to %scan with. This method is a little more involved, but once you see the data avaible, I think you can create a database of files and printers and libraries, to link to the first Query report. -bret BTW - No. This IS the simple way of doing it, with the tools we have at hand. Perhaps HAWKEYE or ASNA or other 3rd party software can do this.

[Guest.Visitor]

Create an OUTFILE of objects of type *QRYDFN (as Bret suggests) and then read the file in a CL program and do a RTVQMQRY against each query: PGM DCLF FILE(QADSPOBJ) DSPOBJD OBJ(YourLib/*ALL) OBJTYPE(*QRYDFN) OUTPUT(*OUTFILE) OUTFILE(QTEMP/QUERY) OVRDBF FILE(QADSPOBJ) TOFILE(QTEMP/QUERY) LOOP: RCVF MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL($ENDPGM)) RTVQMQRY QMQRY(&ODLBNM/&ODOBNM) SRCFILE(YourLib/QTXTSRC) SRCMBR(*QMQRY)ALWQRYDFN(*YES) GOTO LOOP: $ENDPGM: ENDPGM If your boss wants all of this summarized or reformatted, well you're going to have a long weekend! Chris

[B.Myrick]

Chris, Great idea! I would further this by creating QMQRYSRC sourcefile in my worklibrary that contains nothing but the RTVQMQRY members. Then I would use OVRDBF mylib/qmqrysrc mbr(*all). Then run an RPG program over this to parse the FROM LIBRARY/FILE information. If pos 3 in the line(s) after the FROM is a blank, this line either contains another filename, or a continuation of fields. If it is a file, then a "/" will be in the line. This would show another file. -bret