Subfile Keywords in IBM i

Subfile Keywords:

Subfiles are one of the important concepts on IBM i and widely used. Below are some of the Keywords used in Subfiles. 

SFLNXTCHG (Subfile Next Change) - Used to mark the Subfile record as Modified. So that this record can be read using READC (Read Changed Record) operation in the Program.

This needs to be defined in Subfile data format. 

This can be used by defining Indicator against this Keyword or without defining any Indicator. Using Indicator would help programmer control the operation in the Program. Without the indicator, every record would be marked as Modified by default and READC would read every record.

Click Here to see more about SFLNXTCHG & READC.

E.g.:

A  50                                  SFLNXTCHG

In this example, Indicator '50' can be used to control the SFLNXTCHG. Turning ON the Indicator '50' and Updating the Subfile record would modify the Subfile record allowing it to be read using READC. 

 

SFLRCDNBR (Subfile Record Number) - Used to Specify which page of Subfile Record to be displayed. 

CURSOR or *TOP can be added to this keyword to specify where the cursor needs to be placed. 

A            CSRRRN         4S 0H      SFLRCDNBR(CURSOR)

This would point the cursor at the Subfile Input field. 

A            CSRRRN         4S 0H      SFLRCDNBR(*TOP)

This would point the cursor at the top of page. 

SFLPAG - Used to define the number of records to be displayed on screen per page. 

SFLSIZ - Used to define the total number of records within subfile. Maximum number of records allowed is 9999. 

SFLPAG & SFLSIZ can be used based on the type of Subfile. 

• Load All Subfile - SFLSIZ can be defined as 9999. And, maximum number of records allowed is 9999.
• Expandable Subfile - SFLSIZ can be defined with SFLPAG + 1 (E.g.: If SFLSIZ is 8, SFLPAG would be 9).Maximum number of records allowed in Subfile is 9999. 
• Single Page Subfile - SFLSIZ would be equal to SFLPAG (E.g.: If SFLSIZ is 8, SFLPAG would also be 8). There can be any number of records added to Subfile and this needs to be handled in the program.

SFLCLR - Clears the Subfile. This can be controlled with Indicator and used to Clear the records from Subfile.

A  28                                  SFLCLR

This Indicator needs to be turned ON only to clear the Subfile and needs to be Turned OFF while writing writing the records and displaying the Subfile.

SFLDSP - Used to determine whether Subfile (Data) needs to be displayed or not. 

A  25                                  SFLDSP

This indicator needs to be turned ON only if there is a data in Subfile. And, needs to be turned OFF if there is no data in the subfile or clearing the subfile. 

SFLDSPCTL - Used to determine whether fields in Subfile Control format needs to be displayed or not.

A  26                                  SFLDSPCTL

This indicator needs to be turned ON for displaying the Subfile (even if there is no data) and needs to be turned OFF while clearing the subfile. 

SFLEND - Used to determine end of the Subfile.

A  40                                  SFLEND

By default, Subfile End would be turned OFF and '+' (or More...) sign is displayed at the end of subfile. This needs to be turned ON in the program to mark the End of Subfile and 'Bottom' would be displayed at the end of subfile.

SFLCSRRRN - Returns the RRN of the record where cursor is present. 

A                                      SFLCSRRRN(&SFLCSR)

RRN would be returned to the field defined along with Keyword which can be accessed in the Program.

RGZPFM Usage & Important Parameters

Reorganize Physical File Member (RGZPFM):

RGZPFM command is used to remove the deleted records from Physical file member and reorganizes the member.

Some of the Key points to remember.

• Only one member can be Reorganized at a time.
• Key file can be mentioned (either Physical File or Logical File with Key fields) to Reorganize the data with Keyed Sequence.
• Rebuild Access Path for the Physical file and associated logical files. This can be done either Synchronously or Asynchronously to help save the time and/or improve performance.

Below are some of the key parameters to note. 

FILE - Name of Physical File

MBR - Name of the Physical File Member to be Reorganized. Defaults to '*FIRST', Member Name can be entered.

KEYFILE - Used to specify whether file needs to be Reorganized and Key File to be used to Reorganize records. Below are the possible values for this parameter. 

• *NONE (Default) - No Reorganization will happen and Deleted records will be removed from the Member.
• *RPLDLTRCD - Reorganization will happen by replacing Deleted records at the starting of the Member.
• *FILE - Reorganization will happen based on the Physical File's Key Sequence.
• Logical File - Reorganization will happen based on the mentioned Logical File's Key Sequence.

RBDACCPTH (Rebuild Access Path) - Used to specify if the Access paths need to be Rebuild and how. Below are the possible options.

• *YES (Default) - Access path will be rebuilt synchronously at the end of the Reorganization. This option may cause the RGZPFM to run for longer depending the size of the member and number of logical fiiles.
• *OPTIMIZE - Access paths will either be rebuilt asynchronously at the end of the reorganize operation, or maintained during the reorganize, based on which method will result in the access paths being rebuilt the fastest. We need to be careful while choosing this option as Rebuild might still be running after the RGZPFM is completed causing the Member locks making the member not available for any other operations. However, this would save the processing time if this is part of Reorganization activity across application or library.
• *NO - Access paths will be maintained during the reorganize. If *NO is specified, ALWCANCEL(*YES) must also be specified.

ALWCANCEL (Allow Cancel) - Used to specify whether to allow the Reorganization to be cancelled or not. 

• *NO (Default) - The Reorganization cannot be cancelled. If Job is ended, Any changes upto the cancellation will be discarded.
• *YES - The Reorganization can be cancelled. If a Reorganization is cancelled or ended, Subsequent Reorganize (with same parameters) will continue from where it was cancelled.

Usage of OVRDBF & OPNQRYF - Explained

OVRDBF:

OVRDBF (Override Database file) comes handy in some scenarios where, 

• Override the file (or Specific Member) named in the Program.
• This can be used to Override a file to point to the file in Specific library. 
• E.g.: FILE1 is present in both LIB1 & LIB2 and LIB1 is on top of the library list, Required file name and Library can be mentioned in TOFILE Keyword. 
• This can be achieved using EXTFILE keyword on F-SPEC in RPGLE.
• Or, to access the file with the different name.
• E.g.: If FILE1 & FILE2 are of same format and we need to use the file based on specific criteria in the Program, Required file name can be mentioned in TOFILE Keyword and File name to be used in FILE Keyword.
• This can be achieved using EXTFILE with Variable specified (with required file name populated in run time) and USROPN on F-SPEC in RPGLE.
• Access specific Member of file in case of Multi Member files.
• E.g.: If FILE1 has multiple members (MBR1, MBR2, etc...), This can be done by specifying the member name against MBR keyword.
• This can be achieved using EXTMBR on F-SPEC in RPGLE.

• Override some of the parameters as required by the Program. Below are some of the key parameters used in OVRDBF command.
• POSITION (Starting Position) - This parameter is used to specify the starting position for reading the records from Database file. Below are some of the possible values.
• *NONE, *START, *END
• *RRN, *KEYB, *KEYBE, *KEY, *KEYAE, *KEYA
• OVRSCOPE (Override Scope) - This parameter is used to define the scope of Override. Below are some of the Possible values.
• *ACTGRPDFN, *CALLLVL, *JOB
• SHARE (Share Open Data Path) - This parameter is used to define whether Open data path needs to be shared with other programs in the Job. 
• *NO - Open data path isn't shared with other programs.
• *YES - Open data path is shared with other programs. This needs to be specified if this override is required in the programs called in the Job.
• OPNSCOPE (Open Scope) - This parameter is used to define the scope of Open operation.
• *ACTGRPDFN - Scope is defined by the calling program.
• *JOB - Scope of operation is Job.

E.g.: 

OVRDBF FILE(FILE1)            //Name of File
       TOFILE(LIBRARY2/FILE2) //Name of File actually used instead of File
       MBR(MEMBER1)           //Name of Member to be used
       OVRSCOPE(*JOB)         //Override Scope
       SHARE(*YES)            //Share Open Data Path
       OPNSCOPE(*JOB)         //Open Scope

OPNQRYF:

OPNQRYF (Open Query File) comes handy when we need to setup Access path in run time. This opens the file with set of records that satisfies the Query mentioned.

Opened file can be accessed by the following called Programs which share the Open Data Path (See SHARE parameter in OVRDBF for more details).

OPNQRYF can be used to do the below database functions.

• Join Records from multiple files (or members).
• Calculate new field values.
• Calculate Aggregate Functions (Minimum, Average, etc...)
• Select Subset of records.
• Sort records by Key fields.

Below are some of the important parameters that helps in achieving the above functions.

• FILE - File that needs to be opened (Library (default - *LIBL) and/or Member (default - *FIRST) can be entered as appropriate).
• OPTION - This parameter is used to specify how the file needs to be opened (i.e. Read, Update, etc...)
• *INP (Default) - Opens the file for Input. And, is the only valid option if OPNQRYF is used to Join multiple files.
• *OUT - Opens the file for Output.
• *UPD - Opens the file for Update.
• *DLT - Opens the file for Delete.
• QRYSLT - This parameter is used to specify the Selection criteria to determine which records needs to be accessed.
• *ALL - to access all the records in file.
• KEYFLD - This parameter is used to specify the Key fields to sort the records in the Opened file.
• *NONE - No Key fields are used.
• *FILE - File's Key fields are used.
• Any field name(s) can be entered.
• Key field order (*ASCEND, *DESCEND) can be specified.
• COMMIT - This parameter is used to specify if the file needs to be Opened with Commitment Control.
• *NO (Default) - File is not Opened under Commitment. 
• *YES - File is Opened under Commitment. File needs to be Journaled to use this option.
• OPNSCOPE - This parameter is used to define the Scope of Open Query.
• *ACTGRPDFN (Default) - Open scope is determined by the Calling Program.
• *ACTGRP - Open Scope is Activation Group.
• *JOB - Open Scope is limited to Job.

E.g.:

OPNQRYF FILE((FILE1))     //File Name
        OPTION(*INP *UPD) //Open Options
        KEYFLD((KFLD1))   //Key Fields
        COMMIT(*YES)      //Commintment
        OPNSCOPE(*JOB)    //Open Scope

Click Here to see if you need to decide on which is the best option (Logical File or Open Query File).

Load All Subfile - Full Free Format RPG

Load All Subfile in Full Free Format RPGLE:

Earlier in my Article I wrote a Simple Load All Subfile in RPGLE. Click Here for this Article and for more details on Subfile.

In this Article, we will use the same Example using Full Free Format RPGLE.

Refer this link for the Sample Physical file and Display File used in this Article.

Below is the Sample RPGLE code for Load All Subfile.

**Free
  // Define Physical File
  Dcl-F TESTFILE ;

  // Define Subfile
  Dcl-F TESTSUBFIL WORKSTN SFILE(SFDTAR:SFRRN) ;

  // Define Required Variables

  // Main Process
  ExSr Sub_Init ;
  ExSr Sub_Main ;
  ExSr Sub_Exit ;

  // Sub_Init - First time initialization routine
  BegSr Sub_Init ;
    SFPGMNAM = 'TESTLODALL'
    // Clear Subfile
    *In25 = *Off ;
    *In26 = *Off ;
    *In28 = *On ;
    Write SFCTLR ;
    *In28 = *Off ;
    *In25 = *On ;
    *In26 = *On ;
   EndSr ;

   // Sub_Main - Main Processing Routine
   BegSr Sub_Main ;

     ExSr Sub_Load ;

     Dow *In03 = *Off ;
       // If You Need Cursor to be Pointed on Specific Record,
       // You can move corresponding RRN to CSRRR field. In this
       // example I am placing Cursor on 3rd Page.
       CSRRRN = 17 ;
       If SFRRN = 0 ;
         *In25 = *Off ;
       EndIf ;
       Write SFFTRR ;
       Exfmt SFCTLR ;
     EndDo ;

   EndSr ;

   // Sub_Load - Load Subfile
   BegSr Sub_Load ;

     SFRRN = 0 ;
     CSRRRN = 1 ;
     Read TESTFILE ;
     Dow Not %Eof(TESTFILE) And SFRRN < 9999 ;
       SFRRN = SFRRN + 1 ;
       SFTSFLD1 = TSFLD1 ;
       SFTSFLD2 = TSFLD2 ;
       SFTSFLD3 = TSFLD3 ;
       SFTSFLD4 = TSFLD4 ;
       Write SFDTAR ;
       Read TESTFILE ;
     EndDo ;
     *In40 = *On ;

   EndSr ;

   // Sub_Exit - Finalization Routine
   BegSr Sub_Exit ;

     *InLr = *On ;

   EndSr ;