philpl1jb Sep 23, 2011   1:15 PM GMT

The DspPgmRef command can create an outfile of all programs in a library and what files they use but it does not excent to SQL uses. The DspDBR will give you a list of logicals over the pf. Using any of these means using the physical. Phil

54,090 pointsBadges:

NullFields Sep 23, 2011   2:22 PM GMT

For a quick look, DSPPGMREF will work. I've written this program/command combo that builds the program reference file for all the libraries you specifiy. Put it on the schedule to run regularly, and you have a table that is always available to answer your queries: Command:

CMD        PROMPT('Update Program References')              
PARM       KWD(REFLIB) TYPE(*NAME) LEN(10) PROMPT('Library +
             where Ref. DB Created')                        
PARM       KWD(LIBS) TYPE(*NAME) LEN(10) MIN(1) MAX(250) +  
             PROMPT('Library name')                         

The program behind the command:

            PGM        PARM(&BCLIB &LIBSTOREF)                  
                                                                
DECLARES:                                                       
            /* Lib where file exists     */                     
            DCL        VAR(&BCLIB) TYPE(*CHAR) LEN(10)          
            /* Libs containing objects to reference(up to 30) */
            DCL        VAR(&LIBSTOREF) TYPE(*CHAR) LEN(2502)    
                                                                
            /* Current list item number */                      
            DCL        VAR(&CURNBR) TYPE(*DEC) LEN(5 0)         
            /* Current library being processed */               
            DCL        VAR(&CURLSTITM) TYPE(*CHAR) LEN(100)     
            /* Library being worked */                          
            DCL        VAR(&LIB2WORK) TYPE(*CHAR) LEN(10)       
                                                                
            /* Work variables for display */                    
            DCL        VAR(&LISTCOUNT) TYPE(*DEC) LEN(5 0)      
            DCL        VAR(&WORK22) TYPE(*CHAR) LEN(22)         
            /*Build the reference file new each time */                      
            CLRPFM     FILE(&BCLIB/PGMREFS)                                  
                                                                             
            /* Display in job log the number of list items being processed */
            CHGVAR     VAR(&LISTCOUNT) VALUE(%BIN(&LIBSTOREF 1 2))           
            EDTVAR     CHROUT(&WORK22) NUMINP(&LISTCOUNT)                    
            SNDPGMMSG  MSG(&WORK22 |< ' Libraries in the list')              
                                                                             
            DOWHILE    COND(&CURNBR *GE 0)                                   
            /* Extract list for processing */                                
               EXTLST     LIST(&LIBSTOREF) ELMLEN(10) ELEMENT(&CURLSTITM) +  
                            CURNBR(&CURNBR)                                  
                                                                             
               IF         COND(&CURNBR *GE 0) THEN(DO)                       
                                                                             
                  CHGVAR     VAR(&LIB2WORK) VALUE(%SST(&CURLSTITM 1 10))     
                                                                             
                  SNDPGMMSG  MSG('Processing... ' || &LIB2WORK)              
                  DSPPGMREF  PGM(&LIB2WORK/*ALL) OUTPUT(*OUTFILE) +          
                               OBJTYPE(*ALL) OUTFILE(&BCLIB/PGMREFS) +       
                               OUTMBR(*FIRST *ADD)                           
                                                                             
ERRMSG:           MONMSG     MSGID(CPF3064 CPF3033 CPF3034) EXEC(SNDMSG +    
                               MSG('Library ' || &LIB2WORK || ' failed +     
                               for DSPPGMREF command') TOUSR(U860970))
               ENDDO                                                  
            ENDDO                                                     
                                                                      
ENDIT:      ENDPGM                                                    

Once this table is defined, you can build this command/program/query combo for quick answers to your "Where is this object used" questions... Here is the WHEREUSED command:

CMD        PROMPT('Identify where object is used')     
PARM       KWD(OBJNAM) TYPE(*CHAR) LEN(10) MIN(1) +    
             CHOICE('Enter all/partial object name') + 
             PROMPT('Object Name')                     

Here is the WHEREUSED program:

STARTHERE:  PGM        PARM(&OBJIN)                                    
                                                                       
DECLARE:    DCL        VAR(&OBJIN) TYPE(*CHAR) LEN(10)                 
            DCL        VAR(&VAR) TYPE(*CHAR) LEN(13)                   
                                                                       
            CHGVAR     VAR(&VAR) VALUE('''' || &OBJIN |< '%' |< '''')  
RUNQRY:     STRQMQRY   QMQRY(WHEREUSED) OUTPUT(*) SETVAR((OBJREF +     
                         &VAR))                                        
                                                                       
ENDPGM:     ENDPGM                                                     

Here is the source for the *QMQRY object:

SELECT DISTINCT                                        
-- Columns                                             
      A.WHFNAM, A.WHLIB,  A.WHPNAM, A.WHTEXT,          
      A.WHSNAM, A.WHFUSG, A.WHOTYP, A.WHSPKG           
-- Tables                                              
      FROM PGMREFS A                                   
-- Selected Records                                    
      WHERE WHFNAM LIKE &OBJREF                        
-- Sort Columns                                        
      ORDER BY A.WHLIB, A.WHPNAM, A.WHFNAM, A.WHSNAM   

At our site, we no longer utilize this, because we purchase a product called X-Analysis from Databorough. It provides all the whereused/whatused functionality for IBM i based code as well as providing those answers to non-IBM i based connection to IBM i based objects (e.g. JDBC/ODBC connections from java/.net/VB code sitting on AIX/Windows servers).

880 pointsBadges:

TomLiotta Sep 23, 2011   9:52 PM GMT

I've started running a set of jobs similar to what NullFields describes on various systems. I also run jobs to create lists of all source members and lists of all program and service program modules. A few years back, I'd run them over weekends (or when I chose to repopulate the files by submitting them manually). Nowadays, systems seem to handle running the jobs every night. It's handy being able to run simple SQL statements to locate where all copies of a source member of a given name exist or where any programs are that have a particular module bound in. If I can get a system to do my work for me, I'm all in favor. Tom

125,585 pointsBadges:

Yorkshireman Sep 26, 2011   9:20 AM GMT

Roughly, you can look through source code but you may not have all of it You can run DSPPGMREF over all objects on the system and capture the result (this is what X-analysis does) You can RTV files used from queries You could take a view on whether functionality like DFU or WRKDBF or SQL is going to alter the file You could *know* if .NET functions et al have any access and You can examine journal entries - because this is what *really* causes changes - but keep doing it, because year-end and housekeeping routines have a habit of popping up unexpectedly long after you think the work is done. Why do you need to know? - perhaps the context would help limit the task.

6,085 pointsBadges:

WoodEngineer Sep 26, 2011   4:53 PM GMT

About three years ago Roger Stein wrote an article in SystemiNews where he published a handy utility which does what you want. The article is probably still available in the archives.

7,900 pointsBadges: