Viewing the job's overrides

I have been burned before by file overrides, which point input, output, update, etc. both to an expected file. Anything new that comes along to help me show overrides makes me happy. The Fall 2025 Technology Refreshes, IBM i 7.6 TR1 and 7.5 TR7, has done that with a new SQL View: OVERRIDE_INFO. This view will return information about the overrides for the current job only.

I can override all kinds of files:

• OVRDBF:  database files
• OVRDSPF:  display files
• OVRMSGF:  message files
• OVRPRTF:  printer files
• OVRSAVF:  save files
• OVRTAPF:  tape files

I readily admit I have not used all of these commands.

There are three different levels I can scope an override:

1. *CALLLVL:  the override persists until the currently called program, and any it may call, ends
2. *ACTGRPDFN:  the override persists in the named activation group
3. *JOB:  override persists until the job ends

I would use the Display Overrides command, DSPOVR, to view any overrides that might exist. Before I use that command I need some overrides. To make it easier to repeat I created a CL program with various override commands within:

01 OVRDBF FILE(TESTFILE) TOFILE(MYLIB/TESTFILE) OVRSCOPE(*JOB) 02 OVRDBF FILE(TESTFILE1) OVRSCOPE(*CALLLVL) 03 OVRPRTF FILE(QSYSPRT) DUPLEX(*YES) OUTQ(MYOUTQ) + 04 HOLD(*YES) SAVE(*YES) OVRSCOPE(*CALLLVL) 05 OVRDSPF FILE(TESTDSPF) TOFILE(MYLIB/TESTDSPF) OVRSCOPE(*CALLLVL) 06 CRTSAVF FILE(QTEMP/SAVF01) 07 OVRSAVF FILE(SIMONSAVF) TOFILE(QTEMP/SAVF01) OVRSCOPE(*CALLLVL) 08 CRTTAPF FILE(QTEMP/TAPEFILE) DEV(TAP01) 09 OVRTAPF FILE(QTAPE) TOFILE(QTEMP/TAPEFILE) 10 OVRMSGF MSGF(TESTMSGF) TOMSGF(MYLIB/TESTMSGF) 11 RUNSQL SQL('DROP TABLE MYLIB.OUTFILE') COMMIT(*NC) 12 RUNSQL SQL('CREATE TABLE MYLIB.OUTFILE AS + 13 (SELECT * FROM SYSTOOLS.OVERRIDE_INFO) + 14 WITH DATA') COMMIT(*NC) 15 DSPOVR

Line 1: This override is for a database file, and its scope is the job.

Line 2: Another database file overridden, scoped at the call level.

Line 3 and 4: The printer file QSYSPRT is overridden, and scoped at the call level.

Line 5: One of my display files is overridden at the call level.

Lines 6 and 7: I create a save file, and then override it at the call level.

Lines 8 and 9: I create a tape file, and then override it with the default scope.

Line 10: I have to admit I have never overridden a message file before, which is at the default scope.

Line 11: I use the RUNSQL command to execute a DROP TABLE SQL statement.

Lines 12 – 14: I then create an output file, using the CREATE TABLE statement, of the results returned from OVERRIDE_INFO.

Line 15: I have included the DSPOVR command here just to show its results versus those from OVERRIDE_INFO.

After compiling the above program, I called it and the DSPOVR output was displayed:

Display All Merged File Overrides Call level . . . . . . . . . . . : * Type options, press Enter. 5=Display override details 8=Display contributing file overrides Opt File Type Keyword Specifications _ TESTMSGF MSG TOMSGF(MYLIB/TESTMSGF) _ QTAPE TAP TOFILE(QTEMP/TAPEFILE) _ SIMONSAVF SAV TOFILE(QTEMP/SAVF01) _ TESTDSPF DSP TOFILE(MYLIB/TESTDSPF) _ QSYSPRT PRT DUPLEX(*YES) OUTQ(*LIBL/MYOUTQ) SAVE(*YES) ... _ TESTFILE1 DB _ TESTFILE DB TOFILE(MYLIB/TESTFILE)

I can see all of overrides, and if I wanted more information I would place a "5" in the option column and press Enter. This brings up different screens depending on the type of file that was overridden. Below shows the screen for a database file:

Display Override with Data Base File File . . . . . . . . . . . . . . : TESTFILE Call level . . . . . . . . . . . : * Merged . . . . . . . . . . . . . : *YES Keyword Value File being overridden . . . . . . : FILE TESTFILE Overriding to data base file . . : TOFILE TESTFILE Library . . . . . . . . . . . . . : MYLIB

Pressing F3 ends the program and I am returned to a command line.

After the program completed I immediately used the following statement to see what overrides OVERRIDE_INFO would return:

01 SELECT * FROM SYSTOOLS.OVERRIDE_INFO

It returned the following result:

OVERRIDE OVERRIDE FILE_NAME TYPE _LIBRARY _FILE KEYWORD_SPECIFICATIONS --------- ---- -------- -------- ---------------------------------------------------- TESTFILE DB MYLIB TESTFILE TOFILE(MYLIB/TESTFILE)

At first it may appear as a surprise that OVERRIDE_INFO only returned one result. But if you look at the CL program you will see that only one override was scoped at the job level. The others were either at the call or activation group level. These other overrides would have been deleted when the CL program ended.

Now you understand why lines 12 – 14 in the CL program created an outfile based on the results from OVERRIDE_INFO. Here it would have captured all of the overrides. Which means I can use the following statement to view the contents of the outfile:

01 SELECT * FROM OUTFILE ORDER BY TYPE

Which returns the following:

OVERRIDE OVERRIDE FILE_NAME TYPE _LIBRARY _FILE KEYWORD_SPECIFICATIONS --------- ---- -------- -------- ---------------------------------------------------- TESTFILE1 DB TESTFILE DB MYLIB TESTFILE TOFILE(MYLIB/TESTFILE) TESTDSPF DSP MYLIB TESTDSPF TOFILE(MYLIB/TESTDSPF) TESTMSGF MSG TOMSGF(MYLIB/TESTMSGF) QSYSPRT PRT DUPLEX(*YES) OUTQ(*LIBL/MYOUTQ) SAVE(*YES) HOLD(*YES) SIMONSAVF SAV QTEMP SAVF01 TOFILE(QTEMP/SAVF01) QTAPE TAP QTEMP TAPEFILE TOFILE(QTEMP/TAPEFILE)

All of the above were overridden in the CL program.

How could I use this? I decided to use the results from OVERRIDE_INFO to delete any overrides that were at the job scope.

I can accomplish this easily with the following two CL commands:

01 DLTOVR FILE(FILE_NAME) LVL(*JOB) 02 DLTOVR FILE(*ALL) LVL(*JOB)

Line 1: If I want to delete the override for a single file I could use this version of the Delete Override command, DLTOVR.

Line 2: If I want to delete all overrides that are scoped at the job level I could use *ALL as the file name and all job level overrides are deleted, regardless of the type of file that is overridden.

In my example I want to only delete the overrides to database files that are at the job level. I have two programs to do this the first, I have called TESTCL, will perform the overrides at different scopes, and it calls a RPG program, TESTRPG, which contains SQL, to use OVERRIDE_INFO to delete any override to a database file at the job level.

TESTCL looks like:

01 OVRDBF FILE(TESTFILE0) TOFILE(MYLIB/TESTFILE) OVRSCOPE(*JOB) 02 OVRDBF FILE(TESTFILE1) OVRSCOPE(*CALLLVL) 03 OVRDBF FILE(TESTFILE2) TOFILE(MYLIB/TESTFILE) OVRSCOPE(*ACTGRPDFN) 04 OVRPRTF FILE(QSYSPRT) DUPLEX(*YES) OUTQ(RPGPGM) + 05 HOLD(*YES) SAVE(*YES) OVRSCOPE(*CALLLVL) 06 RUNSQL SQL('CREATE TABLE MYLIB.OUTFILE1 AS + 07 (SELECT * FROM SYSTOOLS.OVERRIDE_INFO) + 08 WITH DATA') COMMIT(*NC) 09 CALL PGM(TESTRPG) 10 RUNSQL SQL('CREATE TABLE MYLIB.OUTFILE2 AS + 11 (SELECT * FROM SYSTOOLS.OVERRIDE_INFO) + 12 WITH DATA') COMMIT(*NC)

Lines 1 – 3: Overrides to database files at the three scoping levels.

Line 4: I add this override to a printer file just to show that I am only removing the override for database files, and not all files.

Lines 6 - 8: Use OVERRIDE_INFO to capture all the overrides before the RPG program is called.

Line 9: The RPG program is called.

Lines 10 – 12: I use OVERRIDE_INFO to list the overrides after the RPG program into a second outfile.

The RPG program, TESTRPG, is simple with just two SQL statements within it:

01 **free 02 exec sql DROP TABLE IF EXISTS QTEMP.OUTFILE ; 03 exec sql CREATE TABLE QTEMP.OUTFILE 04 (FILE_NAME,TYPE,OVR_LIB,OVR_FILE,KEYWORDS,RTNCODE) 05 AS 06 (SELECT FILE_NAME,TYPE,OVERRIDE_LIBRARY,OVERRIDE_FILE, 07 KEYWORD_SPECIFICATIONS, 08 QSYS2.QCMDEXC('DLTOVR FILE(' || FILE_NAME || ') ' || 09 'LVL(*JOB)') 10 FROM SYSTOOLS.OVERRIDE_INFO 11 WHERE TYPE = 'DB') 12 WITH DATA ; 13 *inlr = *on ;

Line 2: I could have used a Delete file command, DTLF, I decided to use a SQL DROP TABLE instead. The IF EXISTS means that if the table does not exist the Drop statement will not error.

Lines 3 – 12: As I am using the QCMEXEC scalar function I need to create an output file to contain the return codes from the scalar function. I decided that the output file should contain all the columns from OVERRIDE_INFO, followed by the QCDMEXC scalar function return code. The QCMDEXC uses the DLTOVR command to delete the override for the file at the job level. On line 11 the WHERE ensures that the SQL statement will only include database files.

The above demonstrates what I consider to be an absence from OVERRIDE_INFO, the scope. If the scope was included in the results the previous SQL statement would have included an extra line to include only those overrides at a job level. Alas, without this the statement will try to delete the override at a job level for all database files. Fortunately, if the command in the QCMDEXC scalar function is not valid it returns a negative return code.

After compiling both programs, I called TESTCL, and when it completed I use the following to look at what happened.

First I wanted to confirm all of the overrides before TESTRPG was called. To do that I used the following:

01 SELECT * FROM MYLIB.OUTFILE1

As OUTFILE1 contains the before situation, four results are returned, one for each override performed in TESTCL.

OVERRIDE OVERRIDE FILE_NAME TYPE _LIBRARY _FILE KEYWORD_SPECIFICATIONS --------- ---- -------- -------- ---------------------------------------------------- QSYSPRT PRT DUPLEX(*YES) OUTQ(*LIBL/MYOUTQ) SAVE(*YES) HOLD(*YES) TESTFILE2 DB MYLIB TESTFILE TOFILE(MYLIB/TESTFILE) TESTFILE1 DB TESTFILE0 DB MYLIB TESTFILE TOFILE(MYLIB/TESTFILE)

Next I checked the after, what overrides remained after TESTRPG was called:

01 SELECT * FROM MYLIB.OUTFILE2

There are now three results as the override for TESTFILE0 was deleted as it was scoped at the job level.

OVERRIDE OVERRIDE FILE_NAME TYPE _LIBRARY _FILE KEYWORD_SPECIFICATIONS --------- ---- -------- -------- ---------------------------------------------------- QSYSPRT PRT DUPLEX(*YES) OUTQ(*LIBL/MYOUTQ) SAVE(*YES) HOLD(*YES) TESTFILE2 DB MYLIB TESTFILE TOFILE(MYLIB/TESTFILE) TESTFILE1 DB

Finally I checked the output file that was generated by the QCMDEXC scalar function within TESTRPG.

01 SELECT * FROM QTEMP.OUTFILE

I have the three results, as I expected as the printer file was not selected. The return code showed that attempts to delete an override at the job level for TESTILE2 and TESTFILE1 failed, as they are not scoped at the job level. As TESTFILE0 was scoped at the job level the deletion of the override was successful.

FILE_NAME TYPE OVR_LIB OVR_FILE KEYWORDS RTNCODE --------- ----- ------- -------- ------------------------ ------- TESTFILE2 DB MYLIB TESTFILE TOFILE(MYLIB/TESTFILE) -1 TESTFILE1 DB -1 TESTFILE0 DB MYLIB TESTFILE TOFILE(MYLIB/TESTFILE) 1

I have to admit being a bit disappointed that a scope column is missing from OVERRIDE_INFO. Therefore, I created an IBM idea asking for a scope column to be added to the results. IMHO another useful enhancement would be for a SQL Table function to be created to return the same information for any job, I would just pass the job name information to it, as I can now with the DSPOVR command.

 

You can learn more about the OVERRIDE_INFO SQL View from the IBM website here.

 

This article was written for IBM i 7.6 TR1 and 7.5 TR7.