OVRDBF and OPNQRYF

Introduction

In IBM i CL (Control Language) programming, the “override” concept is used in the context of overriding certain system values and commands temporarily.

The format of this command is:

OVRDBF FILE(overridden-file-name)                +
TOFILE(library name/database file name)+
MBR(member name)                          +
POSITION(file positioning option)   +
SECURE(secure from previous override)     +
SHARE(open data path sharing option)      +
OVERSCOPE(file override scope)

Below are the key points regarding the use of “override” in CL programming on the AS/400 platform:

• Override Commands:  CL programs often use the “OVRDBF” (Override with Database File) command to temporarily change the behavior of a database file. This allows you to use a different file or record format within a program without permanently altering the file’s attributes.
• Override Database Files:  “OVRDBF” is commonly used to change the file, library, or member used in a program. For example, you can override a file to work with a specific customer’s data within the same program.
• Override Control Language Defaults:  The “OVRPRTF” (Override with Printer File) command is used to change the default attributes of printer files, such as page size, character set, and more, for a specific output operation within a CL program.
• Scope:  Overrides in CL programming typically have a local scope, meaning they affect only the specific instance of a command or operation within the program. Once the program finishes its execution, these overrides do not persist.
• Temporary Changes:  Overrides provide a way to make temporary changes to the behavior of your CL program without altering system-wide settings. This is especially useful when you need to customize program behavior for specific scenarios.
• Nesting Overrides:  You can nest overrides within CL programs. For example, you can override a file within a sub-procedure, and the override will be in effect only for the duration of that sub-procedure.
• Resetting Overrides:  It’s important to remember that overrides are temporary. To revert to the default settings, you may need to use the “DLTOVR” (Delete Override) command or simply let the program finish its execution.

Syntax is fixed/free.

In IBM i Control Language (CL), you can use both fixed-format and free-format syntax for the `OVRDBF` (Override Database File) command, just like with other CL commands. Below, we will provide examples of the `OVRDBF` command in both fixed and free formats.

Fixed-Format Syntax:

PGM
OVRDBF FILE(MYLIB/MYFILE) TOFILE(MYLIB/MYFILE2) MBR(MEMBER2)
/* Other CL commands */
DLTOVR FILE(MYLIB/MYFILE) /* Delete the override */
ENDPGM

In fixed-format CL, you typically start each statement at a specific column and follow a specific structure. The `OVRDBF` command starts at column 6, followed by its parameters. Columns 1-5 are reserved for sequence numbers. The `DLTOVR` command is used to delete the override.

Free-Format Syntax:

PGM
OVRDBF FILE(MYLIB/MYFILE) TOFILE(MYLIB/MYFILE2) MBR(MEMBER2)
/* Other CL commands */
DLTOVR FILE(MYLIB/MYFILE)     /* Delete the override */
ENDPGM

In free-format CL, you have more flexibility in terms of layout and indentation. Statements can start at any position within a line, making the code easier to read. The above example accomplishes the same tasks as the fixed-format example but uses a more modern, flexible syntax.

You can choose the format that best suits your coding style and project requirements, but keep in mind that modern IBM i systems generally support free-format CL for increased readability and maintainability.

Usage

A basic example of how to use the `OVRDBF` command to override file attributes in a CL (Control Language) program:

OVRDBF FILE(MYLIB/MYFILE) TOFILE(MYLIB/MYFILE2) MBR(MEMBER2)

In this example, the `OVRDBF` command is used to override the file attributes for `MYLIB/MYFILE`. It specifies that the program should access `MYLIB/MYFILE2` instead of the default file, and it should use `MEMBER2` as the file member.

NOTE: Overriding file attributes should be used with caution, as it can impact the behavior of programs and jobs. It’s important to document and manage these overrides carefully to avoid unexpected issues. Additionally, you need the appropriate authority to perform file overrides on IBM i.

Example:

Here’s a simple example in IBM i CL programming that demonstrates how to use the “OVRDBF” command to temporarily override a database file:

PGM
/* Declare variables */
DCLF FILE(MYLIB/CUSTOMER) OPNID(CUSTFILE) /* Declare file */
/* Override the database file to use a different me   mber */
OVRDBF FILE(CUSTFILE) TOFILE(MYLIB/CUSTOMER) MBR(NEWMBR)
/* Your program logic here */
/* You can now use the CUSTOMER file with the NEWMBR member */
/* Close the overridden file */
CLOF OPNID(CUSTFILE)
/* Delete the override */
DLTOVR FILE(CUSTFILE)
ENDPGM

In this example:

• We declare a file using the `DCLF` command, specifying the library (MYLIB) and file (CUSTOMER) that we want to work with. We also give it an open identifier (OPNID) of CUSTFILE.
• We use the “OVRDBF” command to override the CUSTOMER file temporarily. We specify that we want to use a different member (NEWMBR) within the same file. This override will only affect the file operations within the program.
• You can perform your program logic using the overridden file. Any database file operations  within the program will use the specified member (NEWMBR) instead of the default.
• After you’ve finished using the overridden file, you close it using the `CLOF` command.
• Finally, we delete the override with the `DLTOVR` command, ensuring that the change doesn’t affect subsequent programs or system-wide operations.

This example demonstrates how to temporarily override a database file member within a CL program, allowing you to work with different data within the same file without permanently changing the file’s attributes.

I’ll include another example of a CL program with expected output:

PGM
/* Declare variables */
DCLF FILE(MYLIB/CUSTOMER) OPNID(CUSTFILE) /* Declare file */
DCL &CUSTID *CHAR 5
DCL &CUSTNAME *CHAR 30
/* Override the database file to use a different member */
OVRDBF FILE(CUSTFILE) TOFILE(MYLIB/CUSTOMER) MBR(NEWMEMBER)
/* Open the overridden file */
OPNDBF FILE(CUSTFILE)
/* Read customer data from the overridden file */
RCVF OPNID(CUSTFILE)
MONMSG MSGID(CPF0864) EXEC(GOTO EOF)
/* Process customer data */
CHGVAR &CUSTID %SST(CUSTREC 1 5)
CHGVAR &CUSTNAME %SST(CUSTREC 6 30)
SNDPGMMSG MSG('Customer ID: ' || &CUSTID)
SNDPGMMSG MSG('Customer Name: ' || &CUSTNAME)
GOTO READFILE
/* End of file reached */
EOF:
/* Close the overridden file */
CLOF OPNID(CUSTFILE)
/* Delete the override */
DLTOVR FILE(CUSTFILE)
ENDPGM

In this example, we assume a CUSTOMER file in the MYLIB library with a member named NEWMEMBER(member will be created by CRTMBR). This program opens the file, overrides it to use the NEWMEMBER, retrieves and processes customer records, and then closes and deletes the override.

Expected Output:

Customer ID: 00123
Customer Name: Generic Text
Customer ID: 00456
Customer Name: Customer01

This example demonstrates the following steps:

• The program declares variables for customer ID and name.
• It uses the “OVRDBF” command to override the CUSTOMER file, specifying the NEWMEMBER as the member to use.
• The program opens the overridden file using “OPNDBF.”
• It enters a loop to read and process customer records until the end of the file is reached.
• Inside the loop, it extracts customer IDs and names from the record and sends them as messages.
• When the end of the file is reached, it closes the file and deletes the override.

This program temporarily overrides the database file, reads data from the specified member, and processes it, producing the expected output.

Open Query File (OPNQRYF)

Introduction

OPNQRYF command opens a database file that satisfies the database query request. It creates a temporary access path (ODP – Open Data Path) & this access path contains the information needed to select, order, group and join the records. Once the access path is created, we can read the record from the file using normal CL commands. The access path is discarded after its use.

ODP – Access path describes the order in which records are to be read. It can be kept on the system permanently (such as physical or logical file) or temporarily (OPNQRYF). OPNQRYF command creates a temporary access path for one time use, and then discarded. The open data path contains the information like file name, format name, current record pointer, record selection information etc.

Parameters of OPNQRYF:

• FILE – It specifies the name of the file to be processed.
• OPTION – It allows to specify various options for how the query should be processed.
• FORMAT – It specifies the record format used for records. We can define which field to include in output.
• QRYSLT – It specifies the selection criteria for the records to be processed.
• KEYFLD – It specifies the fields to be used to key the records.
• IGNDECERR – It specifies whether to ignore decimal errors.
• COMMIT – Specifies whether to commit the changes to the query file after each record is processed.
• OPNSCOPE – Specifies the scope of the query file.
• DUPKEYCHK – Specifies whether to check for duplicate keys in the query file.
• ALWCPYDTA – Specifies whether the database is allowed to copy data when processing query.
• OPTIMIZE – It specifies whether the query is to be optimized. It can also be used to control level of optimization.

Parameters of OPNQRYF command with SQL equivalents:

Syntax:

OPNQRYF FILE (lib name/file name

Member-name

Record-format-name)

OPTION (open-option)

FORMAT (lib name/database file name

Record-format name)

QRYSLT (query selection)

KEYFLD (field name)

IGNDECERR(*YES/*NO)

COMMIT(*YES/*NO)

OPNSCOPE (*File name/*USR / ALL)

DUPKEYCHK(*YES/NO/SAME/MSG/UNIQUE)

ALWCPYDTA(*YES/NO)

OPTIMIZE(*NO/YES/MIN/MAX)

Example-

In above example:

• ‘File (WELCOME24/EMPPF)’: specifies the file to be query.
•  ‘QRYSLT’: defines the query selection criteria.
• ‘FORMAT’: specifies the output format for the result.
• ‘KEYFLD’: key fields for sorting & selecting unique records.
• ‘IGNDECERR’: Ignores decimal data errors during query processing.
• ‘COMMIT’: defines commitment control behaviour.
• ‘OPNSCOPE’: specifies the scope of open query.
• ‘DUPKEYCHK’: Enables or disable duplicate key checking.
• ‘ALWCPYDTA’: Allows or disallows copying data to temporary file.
• ‘OPTIMIZE’: specifies the optimization level for query processing.
• ‘OPTION’: Sets additional processing options.

OVRDBF – Override Database File (OVRDBF) command is used to change the file named in the program, or certain parameters of a file that are used by the program. All overrides (changes) are temporary and are effective until the override command has been in scope.

Parameters of OVRDBF:

• FILE – It include name of the file to be override (change).
• TOFILE – It include the name of the file to be used in place of the override file.
• MBR – it specifies the member used within the file.
• POSITION – It denotes the position of the cursor in override file.
• SHARE – It specifies whether the override file can be shared with other programs.

Note – OVRDBF is only used to share the open data path within the calling program (By specifying SHARE(*YES)). But if we don’t use the OVRDBF command then open data path will not be shared.

Using OVRDBF command.

Without Using OVRDBF Command.

Usage:

It can be used to open a file to a particular set of records as per the query request.

It can be used for Ordering & Grouping the Records.

It is also used for Joining records from multiple records.

Restrictions & Compatibility:

Restrictions-

• Temporary Nature: Open Query File are temporary files, and their data is only available for the duration of session. They are typically cleared when the session is ended, so we need to save or export the data if we want to keep it for future reference.
• Limited Storage: Open Query File are stored in a temporary library, and there is a limit to the amount of data that can be stored in this library. If query generates a large result set, we may encounter storage limitations.
• Read-Only: OPNQRYF is primarily used for reading data. We cannot use it to update, insert, or delete records in a database file.
• Performance: It may not be as efficient as SQL for certain types of queries.
• Complexity: It becomes complex when dealing with multiple file joins, complex conditions it make challenging to write & maintain query.

Compatibility-

• Query Syntax: Compatibility depends on the specific query for which we are trying to execute and whether it uses features that are supported by the system.
• Library and Object Names: Compatibility can be affected by the naming conventions used for libraries and objects in our system.

Examples:

• Basic Query
  

  

• Joining Files
  

  

• Sorting Results
  

  

• Creating a Temporary Result Set