Friday, May 15, 2020

Usage of OVRDBF & OPNQRYF - Explained


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.
    • 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.
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 (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.

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).

No comments:

Post a Comment

Numeric data types in Python

Numeric Data types There are three different data types to store numeric data.  int float complex int (integer) Data type 'int' is u...