Identify files used in all queries

• Terry Durbin
• Published: 03 Jan 2005

Using the following code you can identify files used in all queries in a library, library list or ALLUSR.

 
*************** CLP      *****************
             PGM        PARM(&LIB)
/* THE PARAMETER PASSED TO THIS PROGRAM COULD BE A SINGLE LIBRARY, *LIBL, +
    *ALLUSR.  */

             DCL        VAR(&LIB) TYPE(*CHAR) LEN(10)
             DCLF       FILE(QTEMP/QUERIES)

/* BECAUSE THE DCLF IS FOR A FILE IN QTEMP, THE NEXT STATEMENT WILL NEED +
    TO BE RUN MANUALLY TO CREATE THE FILE PRIOR TO COMPILING THIS PROGRAM.   +
    THE COMMAND CAN MANUALLY BE RUN TO CREATE THE FILE WITH JUST ONE     +
    QRYDFN RATHER THAN *ALL   */

             DSPOBJD    OBJ(&LIB/*ALL) OBJTYPE(*QRYDFN) +
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/QUERIES)
             CLRPFM     FILE(TDURBIN/QUERYREF)
             DLTF       FILE(QTEMP/QRYDFNSRC)
             MONMSG     MSGID(CPF0000)
             CRTSRCPF   FILE(QTEMP/QRYDFNSRC)

 LOOP:       RCVF
             MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(END))
             RTVQMQRY   QMQRY(&ODLBNM/&ODOBNM) +
                          SRCFILE(QTEMP/QRYDFNSRC) ALWQRYDFN(*YES)

             DLTOVR     FILE(*ALL)
             OVRDBF     FILE(QRYDFNSRC) TOFILE(QTEMP/QRYDFNSRC) +
                          MBR(&ODOBNM)
             CALL       PGM(TDURBIN/CHKQRY) PARM(&ODOBNM &ODLBNM)
             DLTOVR     FILE(*ALL)
             RMVM       FILE(QTEMP/QRYDFNSRC) MBR(&ODOBNM)
             MONMSG     MSGID(CPF0000)
             GOTO       CMDLBL(LOOP)

 END:        ENDPGM
***************    end of CLP  **************************

***************    DDS for QUERYREF FIle ****************
     A          R QRYRCD
     A            QRYLIB        10
     A            QRY           10
     A            FILELIB       10
     A            FILE          10
***************    end of DDS  **************************

***************    RPGLE    ****************************
     Fqrydfnsrc ip   f   92        disk
     Fqueryref  o  a e             disk
     Dar1              s              1    dim(21)
     Dar2              s              1    dim(10)
     Iqrydfnsrc aa
     I                                 15   18  from
     I                                 15   19  where
     I                                 25   45  names
     C     *entry        plist
     C                   parm                    qry              10
     C                   parm                    qrylib           10
     C     'WHERE'       ifeq      where
     C     'ORDER'       oreq      where
     C     'GROUP'       oreq      where
     C                   move      ' '           files             1
     C                   endif
     C     files         ifeq      '1'
     C                   exsr      getnames
     C                   goto      end
     C                   endif
     C     'FROM'        ifeq      from
     C                   move      '1'           files
     C                   exsr      getnames
     C                   endif
     C     end           tag
      *****************************************************
     C     getnames      begsr
     C                   movea     names         ar1
     C     '/'           scan      names         x                 2 0
     C                   add       1             x
     C                   movea     ar1(x)        ar2
     C                   sub       1             x
     C                   movea     *blanks       ar1(x)
     C                   movea     ar1           filelib
     C                   movea     ar2           work10           10
     C     ' '           scan      work10        x
     C     x             ifne      0
     C                   movea     *blanks       ar2(x)
     C                   endif
     C                   movea     ar2           file
     C                   write     qryrcd
     C                   endsr
***************    end of DDS  **************************

  
  

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: Tips, tutorials and more.

Visit the ITKnowledge Exchange and get answers to your systems management questions fast.

Read this Search400.com Featured Topic: Take control of your iSeries.

Ask the Experts yourself: Our systems management gurus are waiting to answer your technical questions.