show query output?

[missymssy]

Good morning. I need to identify any output files from a list of queries. I have the list of query names (and lib) in a data file, and was hoping to maybe loop through it in a CL, but haven't found a command to do it yet. Any ideas?

Oh, we also have TAATOOLS installed, so if any of you know of a way using them, that's OK also. But no RPG. LOL-no one in my shop could write an RPG program

[jamief]

Re: show query output?

found this by Terry Durbin ...didnt try it it does have RPG but..all you need do is compile it

             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  **************************

[Andrew K]

Re: show query output?

Don't want to come over all negative (and it is sunny outside), but I don't think that will work Jamie.

The RTVQMQRY command only essentially gives you the SQL behind the query (from which you can strip the input files) but will not give you any output file details.

Take a look a this link

http://www.systeminetwork.com/ClubTech/TNT400/bo400ng/AS400Q0114.htm

Some work needed (MI compiler ?!!!), but it is possible according to Gary's article.

[missymssy]

Re: show query output?

I think that's true. I did see this code somewhere when I was looking for a way to identify queries that used certain files as input. I never tried it though, since I saw there was a TAATOOL that would do it. (hey, we already paid for a liscense-why not use it all I can right?)

So I originally had a list of physical files that were changed. I got a list of any logicals built over them, programs and queries that access them. But almost missed this.

[Andrew K]

Re: show query output?

This looks like it would do the job, but is RPG I'm afraid

http://archive.midrange.com/repository/extqrydef.htm

I do not use TAATOOL, but glancing at their QRYUSE documentation I do not see that it refers to output files at all.

[missymssy]

Re: show query output?

Thanks Andrew. You are right about QRYUSE function. That's what I use to get the queries when I have the input files. But no output info. Oh, well.

I think I should look into RPG a little. I did have a night course about 10 years ago..

[FaStOnE]

Re: show query output?

It's like riding a bike and falling off. Once you skin your knee, you wanna scream obscenities and never touch it again, but it keeps calling until you finally pick it up and start riding. Then you find out it's pretty neat all over again.