Member Information Stored Procedure

The following stored procedure is useful for returning metadata about indexes associated with a physical file.  The procedure consists of a CL program to run the DSPFD command, a stored procedure wrapper for the CL program and a stored procedure that gets the indexes associated with the physical file, calls the second procedure in a loop and then returns a result-set of information to the calling program.

CL Program

Put the following code into a source physical file on the iSeries, use the member name SPDSPFD. This program calls DSPFD and sends the output to the passed in filename in the QTEMP library. If Y is passed in the 4^th argument, the program will replace any information in the file.

             PGM        PARM(&LIB &FIL &OFI &OVR)                     
             DCL        VAR(&LIB) TYPE(*CHAR) LEN(10)                 
             DCL        VAR(&FIL) TYPE(*CHAR) LEN(10)                 
             DCL        VAR(&OFI) TYPE(*CHAR) LEN(10)                 
             DCL        VAR(&OVR) TYPE(*CHAR) LEN(1)                  
             IF         COND(&OVR = Y) THEN(DSPFD FILE(&LIB/&FIL) +   
                          TYPE(*MBR) OUTPUT(*OUTFILE) +               
                          OUTFILE(QTEMP/&OFI))                      
             ELSE       CMD(DSPFD FILE(&LIB/&FIL) TYPE(*MBR) +        
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/&OFI) +    
                          OUTMBR(*FIRST *ADD))                        
             ENDPGM                                                   

 Issue the following command to create the CL program.

CRTCLPGM PGM(SQLBOOK/SPDSPFD) SRCFILE(SQLBOOK/QCLSRC) 
TEXT('CL Stored Procedure Program')                                                                 

Procedure Wrapper

Use SQLThing Enterprise Edition to issue the following create procedure statement:

CREATE PROCEDURE SQLBOOK.SPDSPFD
            ( LIB        INOUT CHAR(10),
              FIL        INOUT CHAR(10),
              OUTF    INOUT CHAR(10),
              OVERWRITE INOUT CHAR(1))
EXTERNAL NAME sqlbook.spdspfd
PARAMETER STYLE GENERAL
LANGUAGE CL

You now have a procedure that can run the DSPFD command for member information.

Procedure to call the procedure

Next you need to create a stored procedure which retrieves information on all of the logical files associated with a physical file and calls the SPDSPFD procedure in a loop. Here is the code for that procedure:

CREATE PROCEDURE SQLBOOK.LFINFO
( INOUT LIB CHARACTER(10),
       INOUT FIL CHARACTER(10))
 RESULT SETS 1
 LANGUAGE SQL 
 NOT DETERMINISTIC 
  /*Begin Procedure Code*/
BEGIN 
 DECLARE TFIL CHAR ( 10 ) ;
 DECLARE TLIB CHAR ( 10 ) ;
 DECLARE YN CHAR ( 1 ) DEFAULT 'Y' ;
 DECLARE FN CHAR(10) DEFAULT  'XXX';
 DECLARE AT_END INT DEFAULT 0 ; 

 DECLARE C1 CURSOR FOR 
 SELECT DBXFIL , DBXLIB 
   FROM QSYS . QADBXATR INNER JOIN QSYS . QADBLDEP 
            ON ( DBFLDP = DBXLIB AND DBFFDP = DBXFIL ) 
   WHERE DBFFIL = FIL AND DBFLIB = LIB AND DBXATR IN ( 'LF' , 'IX' ) ;

 DECLARE C2 CURSOR FOR 
 SELECT * FROM QTEMP . XXX ;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET AT_END = 1 ;
 OPEN C1 ;
 CALL SQLBOOK . SPDSPFD ( LIB , FIL ,FN, YN ) ;
 SET YN = 'N' ;

 WHILE AT_END = 0 DO 
      FETCH C1 INTO TFIL , TLIB ;
      CALL SQLBOOK . SPDSPFD ( TLIB , TFIL ,FN, YN ) ;
 END WHILE ;
 CLOSE C1;
 OPEN C2;
 SET RESULT SETS CURSOR C2;
 END  

What the procedures do

Ok, if you are using SQLThing Enterprise, simply go to the procedure editor, select the procedures list tab and refresh the list of procedures.

You can then highlight the procedure and press the icon to execute the procedure. SQLThing will then prompt for the two parameters. Enter QSYS for the first parameter and QADBXREF for the second parameter and then press Bind.

On the iSeries, the procedure will execute the SQL statement in cursor C1 to retrieve a list of all logical files that are over the physical QADBXREF in the QSYS lib. It will then call the SPDSPFD procedure in order to get member information about the physical file.

Note that is passes Y in the fourth argument to the procedure, causing DSPFD to overwrite any existing information in the XXX file in the QTEMP library. Next, the procedure enters a loop and for each logical file associated with the physical it calls SPDSPFD and passes N in the fourth argument, causing DSPFD to add its information to the end of the XXX file in the QTEMP library.

Once the last record in the result set is processed, the program opens cursor C2, which retrieves all of the information written to the XXX file in QTEMP, and then returns the cursor to the calling client application.

In SQLThing, the procedure results will now be displayed in a data window if the Procedure Testing Options is set to Return Result Set, (this option is available from the Environment, Options menu on the Database Options tab).

If you want to try SQLThing Enterprise Edition to assist you in creating and testing stored procedures just send a quick note to Howard Arner. If you do not have a C Compiler or the SQL Development kit on your AS/400 but want to use this procedure, you can download a save file of the compiled objects from here.