Retrieve query source

A utility that can easily be tweaked to provide visibility of files and fields used within iSeries 400 queries....

This is extremely useful when system documentation has become out of date and system changes are due.

I wrote the program to gather information on all queries. But it wouldn't be difficult to adjust the code for particular libraries only.

The clean up at the end has been commented out to allow later analysis over the files.

The program has little error trapping and assumes you have source file QTXTSRC in QGPL. You will need AS/400 Query and SQL on your machine.

  RTVQRYC (CLP)
/*********************************************/          
/*                                           */          
/* RETRIEVE QUERY SOURCE FOR SEVERAL QUERIES */          
/* MARTIN CUBITT  KEYMED LIMITED             */          
/* 3 OCTOBER 2001                            */          
/*                                           */          
/* USEFUL FOR CHECKING FILE/FIELDS           */          
/* WHERE-USED WHEN DOCUMENTATION IS          */          
/* NOT COMPLETE.                             */          
/*                                           */          
/* REQUIRES AS/400 QUERY AND SQL             */          
/*                                           */          
/*********************************************/          
             DCL        VAR(&CMD) TYPE(*CHAR) LEN(768)   
             DCLF       FILE(RTVQRYW1)                   
/* DELETE ANY EXISTING WORK FILES/MEMBER */                        
             DLTF       FILE(QTEMP/RTVQRYW1)                       
             MONMSG     MSGID(CPF0000)                             
             DLTF       FILE(QTEMP/RTVQRYW2)                       
             MONMSG     MSGID(CPF0000)                             
             RMVM       FILE(QGPL/QTXTSRC) MBR(RTVQRYTMP)          
             MONMSG     MSGID(CPF0000)                             
                                                                   
/* RETRIEVE KNOWN QUERY AND PROCESS THAT TO GET SOURCE TO */       
/* OVERRIDDE AND FILE TO SET AS NOMAX */                           
                                                                   
/* RETRIEVE SOURCE FOR QUERY TO QGPL/QTXTSRC(RTVQRYTMP) */         
             RTVQMQRY   QMQRY(QGPL/RTVQRYQ) SRCFILE(QGPL/QTXTSRC) +
                          SRCMBR(RTVQRYTMP) ALWQRYDFN(*YES)        
/* NAME THIS SOURCE 'SOURCE' FOR FUTURE REFERENCE */               
             OVRDBF     FILE(SOURCE) TOFILE(QGPL/QTXTSRC) +        
                          MBR(RTVQRYTMP)                           
/* SET UP SQL COMMAND (IGNORE H AND V LINES FROM SOURCE) */           
             CHGVAR     VAR(&CMD) VALUE('SELECT ''' || &ODLBNM || +   
                          ''', ''' || &ODOBNM || ''', SRCSEQ, +       
                          SRCDTA FROM SOURCE WHERE +                  
                          SUBSTR(SRCDTA,1,1) NOT IN(''H'',''V'')')    
/* PROCESS SQL STATEMENT TO CREATE OUTPUT TO RTVQRYW2 */              
             RUNSQL     REQUEST(&CMD) OUTPUT(*OUTFILE) +              
                          OUTFILE(QTEMP/RTVQRYW2) MEMBER(*ADD/*FIRST) 
/* CHANGE FILE TO NOMAX */                                            
             CHGPF      FILE(QTEMP/RTVQRYW2) SIZE(*NOMAX)             
             CLRPFM     FILE(QTEMP/RTVQRYW2)                          
                                                                      
                                                                      
             DSPOBJD    OBJ(*ALLUSR/*ALL) OBJTYPE(*QRYDFN) +          
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)    
             CALL       PGM(RTVQRYC1)                                 
/* REPORT FINDINGS */                                             
             RUNQRY     QRY(QGPL/RTVQRYQ)                         
                                                                  
/* TIDY UP */                                                     
/*           DLTF       FILE(QTEMP/RTVQRYW1)                  +   
             MONMSG     MSGID(CPF0000)                        +   
             DLTF       FILE(QTEMP/RTVQRYW2)                  +   
             MONMSG     MSGID(CPF0000)                        +   
             RMVM       FILE(QGPL/QTXTSRC) MBR(RTVQRYTMP)     +   
             MONMSG     MSGID(CPF0000)                        +   
             DLTOVR     FILE(SOURCE)                          +   
             MONMSG     MSGID(CPF0000)                        */                                                           

The section:
             DSPOBJD    OBJ(*ALLUSR/*ALL) OBJTYPE(*QRYDFN) +          
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)    
             CALL       PGM(RTVQRYC1)                                 

can be repeated for example:
             DSPOBJD    OBJ(LIB1/*ALL) OBJTYPE(*QRYDFN) +          
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)    
             CALL       PGM(RTVQRYC1)                                 
             DSPOBJD    OBJ(LIB2/*ALL) OBJTYPE(*QRYDFN) +          
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)    
             CALL       PGM(RTVQRYC1)                                 
             DSPOBJD    OBJ(LIB3/*ALL) OBJTYPE(*QRYDFN) +          
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/RTVQRYW1)    
             CALL       PGM(RTVQRYC1)                                 

and the data will accumulate.


RTVQRYC1 (CLP)
/*********************************************/             
/*                                           */             
/* RETRIEVE QUERY SOURCE FOR SEVERAL QUERIES */             
/* MARTIN CUBITT  KEYMED LIMITED             */             
/* 3 OCTOBER 2001                            */             
/*                                           */             
/* USEFUL FOR CHECKING FILE/FIELDS           */             
/* WHERE-USED WHEN DOCUMENTATION IS          */             
/* NOT COMPLETE.                             */             
/*                                           */             
/* REQUIRES AS/400 QUERY AND SQL             */             
/*                                           */             
/*********************************************/             
             DCL        VAR(&CMD) TYPE(*CHAR) LEN(768)      
             DCLF       FILE(RTVQRYW1)                      
LOOP:                                                                  
             RCVF                                                      
             MONMSG     MSGID(CPF0000) EXEC(GOTO CMDLBL(END))          
                                                                       
             RTVQMQRY   QMQRY(&ODLBNM/&ODOBNM) SRCFILE(QGPL/QTXTSRC) + 
                          SRCMBR(RTVQRYTMP) ALWQRYDFN(*YES)            
             CHGVAR     VAR(&CMD) VALUE('SELECT ''' || &ODLBNM || +    
                ''', ''' || &ODOBNM || ''', SRCSEQ, SRCDTA FROM +      
                          SOURCE WHERE SUBSTR(SRCDTA,1,1) NOT +        
                          IN(''H'',''V'')')                            
             RUNSQL     REQUEST(&CMD) OUTPUT(*OUTFILE) +               
                          OUTFILE(QTEMP/RTVQRYW2) MEMBER(*ADD/*FIRST)  
                                                                       
             GOTO       CMDLBL(LOOP)                                   
                                                                       
END:                                                                   


RTVQRYQ (QRY)
The query used to report the findings.. feel free to change to suit your needs!

H QM4 05 Q 01 E V W E R 01 03 01/10/04 09:44           
V 1001 050                                             
V 5001 004 *HEX                                        
SELECT                                                 
  ALL       SEL1||' '||SEL2 AS LIBQRY, SRCSEQ, SRCDTA  
  FROM      QTEMP/RTVQRYW2 T01                         
  ORDER BY  LIBQRY ASC, SRCSEQ ASC