(IBM i fans only) Retrieving Files Used in Queries of an IBM i Library

By -

A colleague of mine left me a real treasure trove of queries, accumulated over many years of work. It’s an endless list: over 1000 queries, all well cataloged and described.


Recently, I needed to find a query related to customer orders. Knowing that the order file is called OTORD, the customer file CLCLI, and the sample file CMCMP, I started opening the most likely queries. The process was long and frustrating: Open Query > Specify file selections > View the files used > Close Query. Stressful, right?  

So, I thought I’d let our trusty IBM i do some work for me.


I created a procedure to retrieve the list of files used by all the queries in a specific library, with the ability to add some filters to answer more specific questions. For example:  

Which queries use the files OTORD00F, CLCLI00F, and CMCMP00F?  

The result? A clean and well-organized subfile:





Don't forget to set the screen to 132 columns!




The source code follows:

FQRCL       CLP

/******************************************************/         
/* RETRIEVE FILES USED IN QUERIES OF AN IBM I LIBRARY */         
/******************************************************/         
             PGM        PARM(&LIBRARY)                           
             DCL        VAR(&LIBRARY) TYPE(*CHAR) LEN(10)        
             DCL        VAR(&CPF    ) TYPE(*CHAR) LEN(07)        
             DLTF       FILE(QTEMP/FQRFILE)                      
             MONMSG     MSGID(CPF0000)                           
             DSPPGMREF  PGM(&LIBRARY/*ALL) OUTPUT(*OUTFILE) +    
                          OBJTYPE(*QRYDFN) OUTFILE(QTEMP/FQRFILE)
             MONMSG     MSGID(CPF3033) EXEC(CHGVAR VAR(&CPF) +   
                          VALUE(CPF3033))                        
             MONMSG     MSGID(CPF3064) EXEC(CHGVAR VAR(&CPF) +   
                          VALUE(CPF3064))                        
             CALL       PGM(FQRRPG) PARM((&CPF))                 
            ENDPGM                       

 

FQRRPGV     DSPF

     A                                      DSPSIZ(27 132 *DS4)
     A                                      CHGINPDFT(CS UL HI)
     A                                      INDARA
     A                                      CA03(03 'F3=EXIT')
     A                                      CA05(05 'F5=Refr')
     A                                      REF(FQRFILE)
     A*--------------------------------------------------------
     A          R SFL1                      SFL
     A*--------------------------------------------------------
     A            S1OPT          1A  B  8  2
     A            S1LIB     R             +3REFFLD(WHLIB )
     A            S1PNAM    R            + 1REFFLD(WHPNAM)
     A            S1FNAM    R            + 1REFFLD(WHFNAM)
     A            S1RFNM    R            + 1REFFLD(WHRFNM)
     A            S1FUSG    R            + 1REFFLD(WHFUSG)
     A            S1TEXT    R            + 1REFFLD(WHTEXT)
     A*--------------------------------------------------------
     A          R FMT01                     SFLCTL(SFL1)
     A*--------------------------------------------------------
     A                                      SFLPAG(0010)
     A                                      SFLSIZ(&NBRRECS)
     A                                      OVERLAY
     A       N50                            SFLDSP SFLDSPCTL
     A        50                            SFLCLR
     A        91                            SFLEND(*MORE)
     A                                      RTNCSRLOC(&PM_RCD &PM_FLD)
     A            PM_RCD        10A  H
     A            PM_FLD        10A  H
     A            SF1NUM         4S 0H
     A            NBRRECS        5S 0P
     A                                  1 26'FILES USED BY QUERY'
     A            F1LIB     R        B  3  6REFFLD(WHLIB )
     A            F1PNAM    R        B   + 1REFFLD(WHPNAM)
     A            F1FNAM    R        B   + 1REFFLD(WHFNAM)
     A            F1RFNM    R        B   + 1REFFLD(WHRFNM)
     A            F1FUSG    R        B   + 1REFFLD(WHFUSG)
     A            F1TEXT    R        B   + 1REFFLD(WHTEXT)
     A                                   + 1'<--FILTERS'
     A                                  4 25'OR'
     A            F1FNA2    R        B  4 28REFFLD(WHFNAM)
     A                                   +11' 1=I,2=E,3=I/E,4=A,5=I/A,6=E/A,7=I-
     A                                      /E/A,8=N/S,0=N/A'
     A                                  5 25'OR'
     A            F1FNA3    R        B  5 28REFFLD(WHFNAM)
     A
     A                                  6  2'OPT'
     A                                    +1'LIBRARY   '
     A                                    +1'QUERY     '
     A                                    +1'FILE       '
     A                                    +1'FORMAT    '
     A*--------------------------------------------------------
     A          R S1CMD
     A*--------------------------------------------------------
     A            S1MSG         78A    23  2
     A                                 24  2'F3=Exit'
     A                                    +3'F5=Refresh'

Note: To compile FQRRPGV and FQRRPG, you first need to generate the FQRFILE file. To quickly generate the FQRFILE file, execute:

DSPPGMREF  PGM(yourLibrary/*ALL) OUTPUT(*OUTFILE)             OBJTYPE(*QRYDFN) OUTFILE(QTEMP/FQRFILE) 

FQRRPG      SQLRPGLE

**FREE
     // ****************************************************
     // Retrieve Files Used in Queries of an IBM i Library *
     // ****************************************************
     ctl-opt option(*nodebugio:*srcstmt:*nounref) dftactgrp(*no);

     dcl-f FQRRPGV workstn indds(Dspf) sfile(SFL1 : SF1NUM);

     // constant
     dcl-c MaxSFLrec 9999 ;

     dcl-s ReLoadSFL ind inz(*on) ;

     // alfanumeric filter fields
     dcl-s F1LIB0lke varchar(35) inz('%') ;
     dcl-s F1LIB0prv varchar(35) inz(*blanks);
     dcl-s F1PNAMlke varchar(35) inz('%') ;
     dcl-s F1PNAMprv varchar(35) inz(*blanks);
     dcl-s F1FNAMlke varchar(35) inz('%') ;
     dcl-s F1FNAMprv varchar(35) inz(*blanks);
     dcl-s F1RFNMlke varchar(35) inz('%') ;
     dcl-s F1RFNMprv varchar(35) inz(*blanks);
     dcl-s F1TEXTlke varchar(55) inz('%') ;
     dcl-s F1TEXTprv varchar(55) inz(*blanks);
     dcl-s F1FNA2lke varchar(35) inz('%') ;
     dcl-s F1FNA2prv varchar(35) inz(*blanks);
     dcl-s F1FNA3lke varchar(35) inz('%') ;
     dcl-s F1FNA3prv varchar(35) inz(*blanks);

// numeric filter fields
dcl-s F1FUSGstr zoned(02) inz(*zeros);
dcl-s F1FUSGend zoned(02) inz(*hival);
dcl-s F1FUSGprv zoned(04) inz(*zeros);

     dcl-ds Dspf qualified ;
       Exit ind pos(03) inz(*off);
       Refr ind pos(05) inz(*off);
       DoForF6 ind pos(06) inz(*off);
       SflClr ind pos(50) inz(*off);
       SflEnd ind pos(91) inz(*off);
     end-ds ;

     dcl-ds RecordDs;
       WHLIB  char(10);
       WHPNAM char(10);
       WHFNAM char(11);
       WHRFNM char(10);
       WHTEXT char(50);
       WHFUSG zoned(2);
     end-ds;

     Dcl-pi FQRRPG;
       pCPF  char(7);
     End-pi;

     // Both "exec sql" is not executed but needed at compile time.
     // It must be placed just after the last "dcl" declaration.
     // LANGIDSHR defines the sort sequence as a "shared-weight sort
     // upper/lower insensitive, special characters are treated
     // "ä" as "A"
     // "Ü" as "E" and so on.
     exec sql set option Commit = *None;
     exec sql set option SRTSEQ = *LANGIDSHR;
     // preapre the data recordset
     exec sql declare C1 cursor for
     select WHLIB ,
            WHPNAM,
            WHFNAM,
            WHRFNM,
            WHTEXT,
            WHFUSG
     from FQRFILE
     where
           WHLIB  like :F1LIB0lke
     and   WHPNAM like :F1PNAMlke
     and   WHFNAM like :F1FNAMlke
     and   WHRFNM like :F1RFNMlke
     and   WHFUSG between :F1FUSGstr and :F1FUSGend
     and   WHTEXT like :F1TEXTlke
     or
           WHLIB  like :F1LIB0lke
     and   WHPNAM like :F1PNAMlke
     and   WHFNAM like :F1FNA2lke
     and   WHRFNM like :F1RFNMlke
     and   WHFUSG between :F1FUSGstr and :F1FUSGend
     and   WHTEXT like :F1TEXTlke
     or
           WHLIB  like :F1LIB0lke
     and   WHPNAM like :F1PNAMlke
     and   WHFNAM like :F1FNA3lke
     and   WHRFNM like :F1RFNMlke
     and   WHFUSG between :F1FUSGstr and :F1FUSGend
     and   WHTEXT like :F1TEXTlke

     order by WHLIB, WHPNAM

     for read only;
     //*************************************************************
     // Start working...
     //*************************************************************
     ReLoadSFL = *on;
     dow Dspf.Exit = *off;
       if ReLoadSFL;
         LoadSFL();
       endif;
       write S1CMD;
       exfmt FMT01;
       if F1FNA2 = *blanks;
          F1FNA2 = F1FNAM;
       endif;
       if F1FNA3 = *blanks;
          F1FNA3 = F1FNAM;
       endif;
       select;
       when Dspf.Exit; // F3=Exit
         leave;
       when Dspf.Refr   ; // Refresh
          F1FNAM = '           ';
          F1FNA2 = '           ';
          F1FNA3 = '           ';
       when Dspf.DoForF6; // F6=do something
       // call a program or a subroutine
       other; // Read user options
         dow 1 = 1;
           readc SFL1;
           if Not %eof;
             if S1OPT = '1';
             // call a program or a subroutine
             endif;
             if S1OPT = '2';
             // call a program or a subroutine
             endif;
             S1OPT = '>';
             update SFL1;
             S1OPT = ' ';
           else;
             leave;
           endif;
         enddo;
       endsl;
       SetFilters();
     enddo;
     exec sql close C1;
     *inlr = *on;
     //*************************************************************
     // Load subfile
     //*************************************************************
     dcl-proc LoadSFL;
       ReLoadSFL = *off;
       // Clear subfile
       Dspf.SflClr = *on;
       write FMT01;
       Dspf.SflClr = *off;
       // Load SFL
       SF1NUM = *zero;
       exec sql close C1;
       exec sql open C1;
       // Read recordset
       dow Sqlcode = 0 and SF1NUM < MaxSFLrec;
         exec sql fetch C1 into :RecordDS;
         if sqlcode = 0;
           S1LIB  = WHLIB ;
           S1PNAM = WHPNAM;
           S1FNAM = WHFNAM;
           S1RFNM = WHRFNM;
           S1FUSG = WHFUSG;
           S1TEXT = WHTEXT;
           SF1NUM += 1;
           write SFL1;
         else;
           Dspf.SflEnd = *on; // SFLEND *END
         endif;
       enddo;
       NBRRECS = SF1NUM;
       S1MSG = %Editc(NBRRECS:'X') + ' record loaded. No more record +
       to load';
       if sqlcode = 0 and NBRRECS >= MaxSFLrec; // MaxSFLrec reached
         S1MSG = %Editc(NBRRECS:'X') + ' records loaded. Use filters +
         to limit the records to be loaded.';
       endif;

       if NBRRECS = 0;
         clear SFL1;
         SF1NUM += 1;
         S1LIB  = '*';
         S1MSG = 'No records loaded';
         if pCPF = 'CPF3033';
            S1MSG = 'Object *ALL of type QRYDFN not found';
         endif;
         if pCPF = 'CPF3064';
           S1MSG = 'Library not found';
         endif;
         write SFL1;
       endif;

     end-proc;
     //*************************************************************
     // Set filters filed
     //*************************************************************
     dcl-proc SetFilters;
       if (F1LIB <>                F1LIB0prv); // char filter
           F1LIB0prv =             F1LIB ;
           F1LIB0lke = '%' + %trim(F1LIB ) + '%';
         ReLoadSFL = *on;
       endif;

       if (F1PNAM <>               F1PNAMprv); // Name
           F1PNAMprv =             F1PNAM;
           F1PNAMlke = '%' + %trim(F1PNAM) + '%';
         ReLoadSFL = *on;
       endif;

       if (F1FNAM <>               F1FNAMprv); // Name
           F1FNAMprv =             F1FNAM;
           F1FNAMlke = '%' + %trim(F1FNAM) + '%';
         ReLoadSFL = *on;
       endif;

       if (F1RFNM <>               F1RFNMprv); // Name
           F1RFNMprv =             F1RFNM;
           F1RFNMlke = '%' + %trim(F1RFNM) + '%';
         ReLoadSFL = *on;
       endif;

       if (F1FNA2 <>               F1FNA2prv); // Name
           F1FNA2prv =             F1FNA2;
           F1FNA2lke = '%' + %trim(F1FNA2) + '%';
         ReLoadSFL = *on;
       endif;

       if (F1FNA3 <>               F1FNA3prv); // Name
           F1FNA3prv =             F1FNA3;
           F1FNA3lke = '%' + %trim(F1FNA3) + '%';
         ReLoadSFL = *on;
       endif;

       if (F1FUSG <>   F1FUSGprv); // numeric filter need str & end
           F1FUSGprv = F1FUSG;
           F1FUSGstr = F1FUSG;
           F1FUSGend = F1FUSG;
           ReLoadSFL = *on;
       endif;
       if (F1FUSG = 0); // numeric filter: if 0 set from 0 to HiVal
           F1FUSGprv = F1FUSG;
           F1FUSGstr = *zeros;
           F1FUSGend = *HiVal;
       endif;

       if (F1TEXT <>               F1TEXTprv); // Name
           F1TEXTprv =             F1TEXT;
           F1TEXTlke = '%' + %trim(F1TEXT) + '%';
         ReLoadSFL = *on;
       endif;

     end-proc;

Note: To compile FQRRPGV and FQRRPG, you first need to generate the FQRFILE file. To quickly generate the FQRFILE file, execute:

DSPPGMREF  PGM(yourLibrary/*ALL) OUTPUT(*OUTFILE)             OBJTYPE(*QRYDFN) OUTFILE(QTEMP/FQRFILE)


 

Now, from the command line, type: CALL PGM(FQRCL) PARM((yourLibrary))


Remember: You can filter all columns in the SubFile using all the yellow fields, like this:




Note that it also works with the *ALL parameter to scan your entire IBM i

Try with: CALL PGM(FQRCL) PARM((*ALL))

There you go!


From this idea, it’s easy to modify the process to not only work with objects of type *QRYDFN but also *PGM, *SQLPKG, *SRVPGM, and *MODULE. In my next post, I’ll be developing this concept further. Stay tuned and keep an eye out for updates!

Comments

Post a Comment

Popular posts from this blog

SQL Indicator Variables in Embedded SQL for RPG on IBM i

By -
Image
SQL Indicator Variables in Embedded SQL for RPG on IBM i When working with DB2 on IBM i, handling NULL values properly is essential, especially when you retrieve data using embedded SQL in RPG. In this post, I’ll show you a simple and practical way to check if a column is NULL using what’s called an SQL Indicator Variable . The Scenario Let’s say you have a customer file called CLANA00F with the following fields: Field Type Length Description CLANN0 CHAR(1) 1 Customer status CLCCL0 DEC(7,0) 7 Customer ID CLCIB0 CHAR(3) 3 Customer type CLNOM0 CHAR(50) 50 Customer name CLSTS0 CHAR(1) 1 Customer status Now, imagine that CLSTS0 is nullable (i.e., it can contain NULL ), and you want to find out whether it has a value or not. The RPG Example **free ctl-opt dftactgrp ( *no ) actgrp ( *caller ); // **************************************************************** // * Autore.......: ALDO SUCCI ...
Read more

Using a Parameterized Cursor in SQLRPGLE

By -
Image
Using a Parameterized Cursor in SQLRPGLE – A Simple and Clean Example When working with embedded SQL on IBM i, using a cursor with parameters is a great way to selectively retrieve data from a file based on runtime input. In this post, I’ll walk you through a simple SQLRPGLE program that demonstrates this concept. This free-format RPG example shows how to: Accept an input parameter Open a cursor based on that parameter Fetch records from the CLANA00F file Display basic customer information Properly handle SQL errors What Is CLANA00F? The file CLANA00F contains customer master data. Here's a simplified layout of its most relevant fields: Field Type Description CLCCL0 S(7) Customer numeric ID CLNOM0 A(50) Customer name CLCIB0 A(3) International code Our goal is to retrieve only those customers whose CLCIB0 matches a parameter passed when the program is called. Program Highlights ctl-opt dftactgrp(*no) actgrp(*caller); dcl-s CLCCL0...
Read more

Dynamic SQL in RPG

By -
Image
Simple Example of Using Dynamic SQL in RPG Dynamic SQL is a powerful technique on IBM i that allows you to build SQL statements at runtime instead of hardcoding them. This flexibility is essential when your SQL needs to adapt based on user input, configuration, or variable conditions. In this post, I’ll walk you through a simple RPG example that uses a parameterized dynamic SQL statement to retrieve data from a file. The Program **FREE // ************************************************************* // * Author.......: ALDO SUCCI                                 * // * Description..: Simple example of using dynamic SQL in RPG * // *************************************************************   // Declare variable to hold the customer type (3 characters) dcl-s wCLCIB0 CHAR(3);   // Declare variable to hold the customer name retrieved from the database dcl-s wCLNOM...
Read more