Day-25.

 CL Programming:

 Overview:

This module enables the student to understand the concept of Overriding database.

 Outline:

Topics covered,

a.     Override Database

b.     Open Query File 

 Objective:

By the end of this module, the student should be able to:

 a. Override Database

 b. Open Query File 

  

File Overrides:

An Override is a CL command that temporarily changes a file name or some of the other attributes of a file. Override commands may be entered interactively from a display station or as part of a batch job. They may be included in a control language (CL) program. Regardless of how they are issued, overrides remain in effect only for the job, program or display station session in which they are issued. Furthermore, they have no effect on other jobs that may be running at the same time.

The system supplies three override functions: applying overrides, deleting overrides and displaying overrides.

Overrides may be used to change most, but not all, of the file attributes that are specified when the file is created.

Overriding a file is different from changing a file in that an override does not permanently change the attributes of a file. For example, if you override the number of copies for a printer file by requesting six copies instead of two, the file description for the printer file still specifies two copies, but six copies are printed. The system uses the file override command to determine which file to open and/or what its file attributes are.

Benefits of using overrides

Overrides are particularly useful for making minor changes to the way program functions or for selecting the data on which it operates, without having to recompile the program.

Overrides may be used for:

o    Changing the name of the file to be processed

o    Selecting the database file member to be processed

o    Indicating whether output is to be spooled

o    Directing output to a different tape unit

o    Changing printer characteristics such as lines per inch and number of copies

Override Commands

You can process override functions for files using the following CL commands:

OVRDBF: Override with Database File: Overrides (replaces) the database file named in the program, overrides certain parameters of a database file that is used by the program, or overrides the file and certain parameters of the file to be processed.

OVRDSPF: Override with Display File: Overrides (replaces) the display file named in the program, overrides certain parameters of a display file that is used by the program, or overrides the file and certain parameters of the file to be processed.

OVRPRTF: Override with Printer File: Overrides (replaces) the printer file named in the program or overrides certain parameters of a printer file that is used by the program or overrides the file and certain parameters of the file to be processed.

DLTOVR: Delete Override: Deletes one or more file overrides (including message file overrides) that were previously specified in a call level, activation group level, or job level for a job.

Overriding File Names-Scenario

Another simple form of overriding a file is to change the file that is used by the program. This may be useful for files that have been moved or renamed after the program has been compiled.

For example, you want the output from your application program to be printed using the printer file REPORTS instead of the printer file OUTPUT (OUTPUT is specified in the application program). Before you run the program, enter the following command:

o    OVRPRTF FILE (OUTPUT) TOFILE (REPORTS)

The file REPORTS must have been created by a CRTPRTF command before it can be used.

Consider another example Overriding file names using OVRDBF.

Suppose you want to use file EMPM instead of EMPMS. Before you run the program, enter the following command:

o      OVRDBF FILE (EMPMS) TOFILE (EMPM)

The file EMPM must have been created by a CRTPF command before it can be used.

Overriding File Attributes—Scenario

The simplest form of overriding a file is to override some attributes of the file. File attributes are built as a result of the following:

Override commands: At program run time, these commands can override the file attributes previously built by the merging of the file description and the file parameters specified in the user program.

For example, assume that you create a printer file OUTPUT whose attributes are:

§  Page size of 60 by 80

§  Six lines per inch

§  Two copies of printed output

§  Two pages for file output

§  Two pages for file separators

§  Overflow line number of 55

The Create Printer File (CRTPRTF) command looks like this:

CRTPRTF FILE (QGPL/OUTPUT) SPOOL (*YES)+

PAGESIZE (60 80) LPI (6) COPIES (2)+

FILESEP (2) OVRFLW (55)

However, before you run the application program, you want to change the number of copies of printed output to 3 and the overflow line to 60. The override command looks like this:

OVRPRTF FILE (EMPPAY) SHARE (*YES)

With SHARE (*YES) parameter, we area sharing the Open Data Path of the given file with all the program in a given job.

An open data path is the path through which all input/output operations for the file are performed. In the original program model, using the SHARE (*YES) parameters lets two or more programs running in the same job share an open data path is built. You can specify that if a file is opened more than once and an open data path is still active for it in the same job, the active ODP for the file can be used with the current open of the file, and a new open data does not have to be created.

This reduces the amount of time required to open the file after the first open, and the amount of main storage required by the job. SHARE (*YES) must be specified for the first open and other opens of the same file for the open data path to be shared. A well-designed (for performance) application will normally do a shared open on database files that will be opened in multiple programs in the same job. Specify SHARE (*YES) for other files depends on the application

Overriding File Names and File Attributes—Scenario

This form of overriding files is simply a combination of overriding file attributes and overriding file names. With this form of override, you can override the file that is to be used in a program and you can also override the attributes of the overriding file. For example, you want the output from your application program to be printed using the printer file reports instead of the printer file OUTPUT (OUTPUT is specified in the application program). In addition to having the application program use the printer file REPORTS; you want to override the number of copies produced to three. Assume the file REPORTS was created with the following command:

CRTPRTF FILE (REPORTS) SPOOL (*YES)+

PAGESIZE(68 132) LPI(8)OVRFLW(60)+

COPIES(2)FILESEP(1)

Before you run the program, type the following command:

OVRPRTF FILE(OUTPUT) TO FILE (REPORTS) COPIES (3)

Then call the application program, and three copies of the output are produced using the printer file REPORTS.

Note that this is not equal to the following two override commands:

Override1 OVRPRTF FILE (OUTPUT) TOFILE(REPORTS)

Override2 OVRPRTF FILE (REPORTS) COPIES(3)

Only one override is applied for each call level for an open of a particular file, so if you want to override the file that is used by the program and also override the attributes of the overriding file from one call level, you must use a single command. If two overrides are used, override 2 will cause output to be printed using the printer file REPORTS, but override 1 will be ignored.

Using the Open Query File (OPNQRYF) Command

The Open Query File(OPNQRYF) command that allows you to perform many data processing functions on database files. Essentially, the OPNQRYF command acts as a filter between the processing program and the database file. The database file can be a physical or logical file.

Unlike a database file created with the Create Physical File (CRTPF) command or the Create Logical File (CRTLF) command, the OPNQRYF command creates only a temporary file (Output of Query) for processing the data; it does not create a permanent file.

The following is a list of the major functions supplied by OPNQRYF:

·         Dynamic record selection

·         Dynamic keyed sequence access path

·         Improving performance

·         Sort sequence processing

The typical use of the OPNQRYF command is to select, arrange, and format the data so your high-level language program can read it sequentially.

Note: Specify selection fields from a database file without an ampersand (&). Fields declared in the CL program with DCL or DCLF require the ampersand.

We can specify key fields on the OPNQRYF command (parameter KEYFLD) in ascending or descending orders. Thus data sequencing is possible with OPNQRY command.

OVRDBF is required to share ODP s two Open one in CL program and other in called program of physical file EMPPAY otherwise all records will be available for called program instead of filtered records by Open Query.

Examples of Open Query:

1.Open Query with Numeric comparison example.

2.Open Query Character comparison example.

  

Copy From Query File

The Copy From Query File (CPYFRMQRY) command either all or part of a file that is opened with the Open Query File(OPNQRY) command to a physical file. It can add records to an existing physical file member of replace contents of a receiving physical file member by using the MBROPT parameter.

Lab Session:

Columns . . . :    1  71            Edit                         SHLIB/QCLPSRC

 SEU==>                                                                    OVRD

        *************** Beginning of data *************************************

0000.01 /*PROGRAM: USE OF OVRDBF AND OPNQRYF*/                                 

0001.00 PGM                                                                    

0001.01 /* DECLARE PHYSICAL FILE CLASS */                                      

0002.00              DCLF       FILE(SHLIB/CLASS)                              

0002.01 /* SHARE THE PHYSICAL FILE 'CLASS' */                                  

0003.00              OVRDBF     FILE(CLASS) SHARE(*YES)                        

0003.01 /* SELECT FIELDS BY GIVING CONDITIN IN QRYSELECT*/                     

0004.00              OPNQRYF    FILE((SHLIB/CLASS)) QRYSLT('CLSID = 100') +    

0005.00                           KEYFLD((CLSID))                              

0005.01 /* COPY THE CONTENT OF FILE IN ANOTHER NEW FILE HAVING NAME */         

0006.00              CPYFRMQRYF FROMOPNID(CLASS) TOFILE(SHLIB/CLS1) +          

0007.00                           MBROPT(*ADD) CRTFILE(*YES)                   

0007.01 /* SEE THE CONTENT OF NEWLY CREATED FILE */                            

0008.00              RUNQRY     QRYFILE((SHLIB/CLS1))                           

0008.01 /* CLOSE THE FILE WHICH IS OPEN FOR SELECTION OF FIELDS */             

0009.00              CLOF       OPNID(CLASS)                                   

0009.01 /* DELETE THE OVERRIDING FILE*/                                         

0010.00              DLTOVR     FILE(CLS1)                                     

0013.00 ENDPGM                                                                 

        ****************** End of data ****************************************

                                                                               

Above program shows the use of OVRDBF and OPNQRYF.