Skip to main content

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

Comments

Popular posts from this blog

All about READ in RPGLE & Why we use it with SETLL/SETGT?

READ READ is one of the most used Opcodes in RPGLE. As the name suggests main purpose of this Opcode is to read a record from Database file. What are the different READ Opcodes? To list, Below are the five Opcodes.  READ - Read a Record READC - Read Next Changed Record READE - Read Equal Key Record READP - Read Prior Record READPE - Read Prior Equal Record We will see more about each of these later in this article. Before that, We will see a bit about SETLL/SETGT .  SETLL (Set Lower Limit) SETLL accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Corresponding Record (or Next Record if exact match isn't found).  SETGT (Set Greater Than) SETGT accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Next Record (Greater Than the Key value). Syntax: SETLL SEARCH-ARGUMENTS/KEYFIELDS FILENAME SETGT  SEARCH-ARGUMENTS/KEYFIELDS FILENAME One of the below can be passed as Search Arguments. Key Fiel

What we need to know about CHAIN (RPGLE) & How is it different from READ?

CHAIN READ & CHAIN, These are one of the most used (& useful) Opcodes by any RPG developer. These Opcodes are used to read a record from file. So, What's the difference between CHAIN & READ?   CHAIN operation retrieves a record based on the Key specified. It's more like Retrieving Random record from a Database file based on the Key fields.  READ operation reads the record currently pointed to from a Database file. There are multiple Opcodes that start with READ and all are used to read a record but with slight difference. We will see more about different Opcodes and How they are different from each other (and CHAIN) in another article. Few differences to note.  CHAIN requires Key fields to read a record where as READ would read the record currently pointed to (SETLL or SETGT are used to point a Record).  If there are multiple records with the same Key data, CHAIN would return the same record every time. READE can be used to read all the records with the specified Ke

Extract a portion of a Date/Time/Timestamp in RPGLE - IBM i

%SUBDT Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times.  This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT.  Valid unit should be passed for the type of the value passed. Below are the valid values for each type. Date - *DAYS, *MONTHS, *YEARS Time - *HOURS, *MINUTES, *SECONDS Timestamp - *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS Syntax: %SUBDT(value : unit { : digits { : decpos} }) Value and Unit are the mandatory arguments.  Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp. We can either pass the full form for the unit or use the short form. Below i