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

SCANQRY command to scan query programs

Command "SCANQRY" provides the ability to scan all query programs in a library for a specified string. The program first creates a list of all query programs for the specified library. Then it retrieves the query source from the query definition object for each query and places it in a temporary source file member. After the source is created for all queries, the members are scanned using the native FNDSTRPDM (Find String Using PDM) command. In addition, the first 10 positions of the search string are placed in the "User Data" portion of the spooled file to better identify it.

Code:

/******************************************************************************/ 
/* OBJECT NAME....: SCANQRY                                                   */ 
/* DESCRIPTION....: Scan query program(s) for a specified string.             */ 
/* COMPILE NOTES..: 1) Command processing Program is "SCANQRYC".              */ 
/* DATE WRITTEN...: 09/19/1994                                                */ 
/* AUTHOR.........: Eric Nepsund                                              */ 
/******************************************************************************/ 
/*                                                                            */ 
/* MAINTENANCE:                                                               */ 
/*                                                                            */ 
/*  ID     Date    Description                                                */ 
/* ---- ---------- --------------------------------------------------  -----  */ 
/* M001 mm/dd/yyyy (First & Last Name).  (Company)                            */ 
/* M001            (Description of changes here...)                           */ 
/*                                                                            */ 
/******************************************************************************/ 

             CMD        PROMPT('Scan Query Program(s)') 

             PARM       KWD(STRING) TYPE(*CHAR) LEN(40) MIN(1) + 
                          EXPR(*YES) PROMPT('Find ''string''') 

             PARM       KWD(OBJLIB) TYPE(*NAME) LEN(10) MIN(1) + 
                          EXPR(*YES) PROMPT('Object library') 

             PARM       KWD(SBMJOBD) TYPE(*NAME) LEN(10) + 
                          DFT(*USRPRF) SPCVAL((*NONE) (*USRPRF)) + 
                          EXPR(*YES) PROMPT('Submit job desc (in + 
                          *LIBL)') 

             PARM       KWD(SBMJOBNAM) TYPE(*NAME) LEN(10) + 
                          DFT(*JOBD) SPCVAL((*JOBD)) EXPR(*YES) + 
                          PMTCTL(PSBMJOBD) PROMPT('  Job name') 

             PARM       KWD(SBMJOBQ) TYPE(*NAME) LEN(10) DFT(*JOBD) + 
                          SPCVAL((*JOBD)) EXPR(*YES) + 
                          PMTCTL(PSBMJOBD) PROMPT('  Job queue + 
                          (in *LIBL)') 

             PARM       KWD(SBMJOBPTY) TYPE(*CHAR) LEN(5) DFT(*JOBD) + 
                          RANGE(1 9) SPCVAL((*JOBD)) EXPR(*YES) + 
                          PMTCTL(PSBMJOBD) PROMPT('  Job priority + 
                          (on JOBQ)') 

             PARM       KWD(SBMSCDDATE) TYPE(*CHAR) LEN(10) + 
                          DFT(*CURRENT) SPCVAL((*CURRENT) + 
                          (*MONTHSTR) (*MONTHEND) (*MON) (*TUE) + 
                          (*WED) (*THU) (*FRI) (*SAT) (*SUN)) + 
                          EXPR(*YES) PMTCTL(PSBMJOBD) PROMPT('  + 
                          Schedule date') 

             PARM       KWD(SBMSCDTIME) TYPE(*CHAR) LEN(10) + 
                          DFT(*CURRENT) SPCVAL((*CURRENT)) + 
                          EXPR(*YES) PMTCTL(PSBMJOBD) PROMPT('  + 
                          Schedule time') 

             PARM       KWD(SBMOUTQ) TYPE(*NAME) LEN(10) + 
                          DFT(*CURRENT) SPCVAL((*CURRENT) (*DEV) + 
                          (*JOBD) (*USRPRF)) EXPR(*YES) + 
                          PMTCTL(PSBMJOBD) PROMPT('  Output queue + 
                          (in *LIBL)') 

             PARM       KWD(SBMMSGQ) TYPE(*NAME) LEN(10) + 
                          DFT(*USRPRF) SPCVAL((*NONE) (*USRPRF) + 
                          (*WRKSTN)) EXPR(*YES) PMTCTL(PSBMJOBD) + 
                          PROMPT('  Message queue (in *LIBL)') 

 PSBMJOBD:   PMTCTL     CTL(SBMJOBD) COND((*NE *NONE)) 

/******************************************************************************/ 
/* OBJECT NAME....: SCANQRYC                                                  */ 
/* DESCRIPTION....: Scan query program(s) for a specified string.             */ 
/* COMPILE NOTES..: 1) Execute the following command prior to compiling       */ 
/*                     CL program.  This will create the work file            */ 
/*                     that is required for a clean compile.                  */ 
/*                        DSPOBJD OBJ(*ALL) OBJTYPE(*LIB)                     */ 
/*                        OUTPUT(*OUTFILE) OUTFILE(QTEMP/SCANQRYW1)           */ 
/* DATE WRITTEN...: 09/19/1994                                                */ 
/* AUTHOR.........: Eric Nepsund                                              */ 
/******************************************************************************/ 
/*                                                                            */ 
/* MAINTENANCE:                                                               */ 
/*                                                                            */ 
/*  ID     Date    Description                                                */ 
/* ---- ---------- --------------------------------------------------  -----  */ 
/* M001 mm/dd/yyyy (First & Last Name).  (Company)                            */ 
/* M001            (Description of changes here...)                           */ 
/*                                                                            */ 
/******************************************************************************/ 

             PGM        PARM(&STRING &QRYLIB &SBMJOBD &SBMJOBNAM &SBMJOBQ + 
                          &SBMJOBPTY &SBMSCDDATE &SBMSCDTIME + 
                          &SBMOUTQ &SBMMSGQ) 

/* Input variables */ 
   DCL VAR(&STRING)     TYPE(*CHAR) LEN(40) /* Search String */ 
   DCL VAR(&QRYLIB)     TYPE(*CHAR) LEN(10) /* Query Library */ 
   DCL VAR(&SBMJOBD)    TYPE(*CHAR) LEN(10) /* Submit Job Description */ 
   DCL VAR(&SBMJOBNAM)  TYPE(*CHAR) LEN(10) /* Submit Job Name */ 
   DCL VAR(&SBMJOBQ)    TYPE(*CHAR) LEN(10) /* Submit Job Queue */ 
   DCL VAR(&SBMJOBPTY)  TYPE(*CHAR) LEN(5)  /* Submit Job Priority */ 
   DCL VAR(&SBMSCDDATE) TYPE(*CHAR) LEN(10) /* Submit Schedule Date */ 
   DCL VAR(&SBMSCDTIME) TYPE(*CHAR) LEN(10) /* Submit Schedule Time */ 
   DCL VAR(&SBMOUTQ)    TYPE(*CHAR) LEN(10) /* Submit Output Queue */ 
   DCL VAR(&SBMMSGQ)    TYPE(*CHAR) LEN(10) /* Submit Message Queue */ 

/* Qualified names */ 

/* Program variables */ 
   DCL VAR(&STRING10)   TYPE(*CHAR) LEN(10) /* First 10 pos of string */ 
   DCL VAR(&STRING30)   TYPE(*CHAR) LEN(30) /* Last  30 pos of string */ 

/* Standard variables */ 
   DCL VAR(&MSGDTA)  TYPE(*CHAR) LEN(100) 
   DCL VAR(&MSGERR)  TYPE(*LGL)  LEN(1)   VALUE('0') 
   DCL VAR(&MSGESC)  TYPE(*LGL)  LEN(1)   VALUE('1') 
   DCL VAR(&MSGF)    TYPE(*CHAR) LEN(10) 
   DCL VAR(&MSGFLIB) TYPE(*CHAR) LEN(10) 
   DCL VAR(&MSGID)   TYPE(*CHAR) LEN(7) 

/* Files */ 
             DCLF       FILE(SCANQRYW1) 

/******************************************************************************/ 
/* Standard logic.                                                            */ 
/******************************************************************************/ 

/* Global message monitor. */ 
             MONMSG     MSGID(CPF0000) EXEC(GOTO CMDLBL(MSGSTART)) 

/* Submit job if specified. */ 
             IF         COND(&SBMJOBD *NE *NONE) THEN(DO) 
               SBMJOB     CMD(SCANQRY STRING(&STRING) OBJLIB(&QRYLIB) + 
                          SBMJOBD(*NONE)) JOB(&SBMJOBNAM) + 
                          JOBD(&SBMJOBD) JOBQ(&SBMJOBQ) + 
                          JOBPTY(&SBMJOBPTY) OUTQ(&SBMOUTQ) + 
                          SCDDATE(&SBMSCDDATE) SCDTIME(&SBMSCDTIME) + 
                          MSGQ(&SBMMSGQ) 
               GOTO       CMDLBL(EXIT) 
             ENDDO 

/******************************************************************************/ 
/* Main logic.                                                                */ 
/******************************************************************************/ 

/* Create temporary source physical file to store query definitions. */ 
             CRTSRCPF   FILE(QTEMP/&QRYLIB) TEXT('Source file for + 
                          SCANQRY command') 
             MONMSG     MSGID(CPF0000) 
             RMVM       FILE(QTEMP/&QRYLIB) MBR(*ALL) 
             MONMSG     MSGID(CPF0000) 

/* Make a list of the query programs to be checked. */ 
             DSPOBJD    OBJ(&QRYLIB/*ALL) OBJTYPE(*QRYDFN) + 
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/SCANQRYW1) + 
                          OUTMBR(*FIRST *REPLACE) 
             MONMSG     MSGID(CPF2123) EXEC(GOTO CMDLBL(EXIT)) /* No + 
                          objects found for criteria */ 

/* Convert each query to a source member. */ 
 READLOOP:   RCVF 
             MONMSG     MSGID(CPF0864) EXEC(DO) 
               CHGVAR     VAR(&MSGESC) VALUE('0') 
               GOTO       CMDLBL(STRSCAN) 
             ENDDO 

             RTVQMQRY   QMQRY(&ODLBNM/&ODOBNM) SRCFILE(QTEMP/&QRYLIB) + 
                          ALWQRYDFN(*YES) 

             GOTO       CMDLBL(READLOOP) 

/* Scan query source members for specified string.                            */ 
STRSCAN:     CHGVAR VAR(&STRING10) VALUE(%SST(&STRING  1 10)) 
             CHGVAR VAR(&STRING30) VALUE(%SST(&STRING 11 30)) 
             IF COND(&STRING30 *NE ' ') THEN(DO) 
               CHGVAR VAR(&STRING10) VALUE(%SST(&STRING 1 7) + 
                          *CAT '...') 
             ENDDO 
             OVRPRTF    FILE(QPUOPRTF) USRDTA(&STRING10) 
             FNDSTRPDM  STRING(&STRING) FILE(QTEMP/&QRYLIB) + 
                          MBR(*ALL) OPTION(*NONE) PRTMBRLIST(*YES) + 
                          PRTRCDS(*ALL) 
             DLTOVR     FILE(QPUOPRTF) 

/******************************************************************************/ 
/* Exit.                                                                      */ 
/******************************************************************************/ 

EXIT: 

/* Delete temporary files. */ 
             DLTF       FILE(QTEMP/SCANQRYW1) /* "list" of query programs */ 
             MONMSG     MSGID(CPF0000) 
             DLTF       FILE(QTEMP/&QRYLIB)   /* "source" for query programs */ 
             MONMSG     MSGID(CPF0000) 

/* Return. */ 
             RETURN 

/******************************************************************************/ 
/* Standard message handling.                                                 */ 
/******************************************************************************/ 

 MSGSTART:   IF         COND(&MSGERR *EQ '1') THEN(SNDPGMMSG + 
                          MSGID(CPF9999) MSGF(QCPFMSG) + 
                          MSGTYPE(*ESCAPE)) 

             CHGVAR     VAR(&MSGERR) VALUE('1') 

/* Move the diagnostic messages up to the next level. */ 
 MSGDIAG:    RCVMSG     MSGTYPE(*DIAG) MSGDTA(&MSGDTA) MSGID(&MSGID) + 
                          MSGF(&MSGF) SNDMSGFLIB(&MSGFLIB) 

             IF         COND(&MSGID *EQ '       ') THEN(GOTO + 
                          CMDLBL(MSGCOMP)) 

             SNDPGMMSG  MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) + 
                          MSGDTA(&MSGDTA) MSGTYPE(*DIAG) 

             GOTO       CMDLBL(MSGDIAG) 

/* Move the completion messages up to the next level. */ 
 MSGCOMP:    RCVMSG     MSGTYPE(*COMP) MSGDTA(&MSGDTA) MSGID(&MSGID) + 
                          MSGF(&MSGF) SNDMSGFLIB(&MSGFLIB) 

             IF         COND(&MSGID *EQ '       ') THEN(GOTO + 
                          CMDLBL(MSGESC)) 

             SNDPGMMSG  MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) + 
                          MSGDTA(&MSGDTA) MSGTYPE(*COMP) 

             GOTO       CMDLBL(MSGCOMP) 

/* Re-send the last escape message (if there is one). */ 
 MSGESC:     RCVMSG     MSGTYPE(*EXCP) MSGDTA(&MSGDTA) MSGID(&MSGID) + 
                          MSGF(&MSGF) SNDMSGFLIB(&MSGFLIB) 

             IF         COND(&MSGID *EQ '       ') THEN(RETURN) 

             SNDPGMMSG  MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) + 
                          MSGDTA(&MSGDTA) MSGTYPE(*ESCAPE) 

             ENDPGM 

Dig Deeper on RPG iSeries programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close