As you begin to write a program, you ponder, "I know what file I have to read, but I wonder if I have an access path that will select records in the order I want them?"
So, you look at the physical file to see if it was keyed, and if so, what the key fields are. If the physical file was unkeyed or the key fields were not the ones you wanted, you look at whatever logical files are available. Then, you'd have to look at each logical, one at a time, to determine what the access path was, until hopefully you found one that gave you what you wanted.
Sounds like a lot of work, doesn't it? Wouldn't it be nice if you had a command that shows you all this information in a matter of seconds? Well, now you do. It's called Display Key Fields (DSPKEYFLD).
DSPKEYFLD command is a very good analysis tool that can be used not only to select which access path to use, but also to identify logical files that may no longer be needed. Having unnecessary logical files on your system can add overhead which degrades overall system performance. You may not even be aware that a logical file exists until you start using this command.
Someone may have created a temporary logical file in a test library and then forgotten about it. Or, a logical file may have been created to produce a report which is no longer being run. Additionally, you may have two logical files with the same access path over the same physical file.
These are things to look for when using this command. Logical files should only exist when necessary, and you should take full advantage of the logical files which make up a necessary part of your database. The DSPKEYFLD command will help you accomplish this.
How it Works
The DSPKEYFLD command has two parameters, FILE and OUTPUT. The FILE parameter must be entered with the qualified name of a physical file; if not, an error message is issued. The OUTPUT parameter defaults to *, which will present the information on the screen (if DSPKEYFLD is run interactively) or on the printer (if run in batch). You can optionally enter *PRINT to force the output to an output queue.
Program KEY001CL is the CPP for DSPKEYFLD. It executes a DSPDBR command on the physical file and sends the output to a file called KEY001W1. The program also executes a DSPFD TYPE(*ACCPTH) on the physical file and sends the output to another file called KEY001W2. The program then reads KEY001W1 sequentially and executes a DSPFD TYPE(*ACCPTH) to KEY001W2 for each record, this time adding records instead of replacing them. After that, RPG program KEY001RG takes over. Its job is to format the output and present the information on the display station or send it to a spool file.
Display Key Fields
Figure 1 Command DSPKEYFLD
DSPKEYFLD: CMD PROMPT('Display Key Fields') PARM KWD(FILE) TYPE(QUAL) MIN(1) PROMPT('Physical + file') PARM KWD(OUTPUT) TYPE(*CHAR) LEN(6) RSTD(*YES) + DFT(*) VALUES(* *PRINT) PROMPT('Output') QUAL: QUAL TYPE(*NAME) LEN(10) QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) + SPCVAL((*LIBL)) PROMPT('Library')
Display Key Fields
Figure 2 CL program KEY001CL
KEY001CL: + PGM PARM(&FILE &OUTPUT) DCL VAR(&FILE) TYPE(*CHAR) LEN(20) DCL VAR(&FILNAM) TYPE(*CHAR) LEN(10) DCL VAR(&LIBNAM) TYPE(*CHAR) LEN(10) DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(80) DCL VAR(&MSGID) TYPE(*CHAR) LEN(7) DCL VAR(&OBJATR) TYPE(*CHAR) LEN(10) DCL VAR(&OUTPUT) TYPE(*CHAR) LEN(6) DCLF FILE(QADSPDBR) MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR)) CHGVAR VAR(&FILNAM) VALUE(%SST(&FILE 1 10)) CHGVAR VAR(&LIBNAM) VALUE(%SST(&FILE 11 10)) RTVOBJD OBJ(&LIBNAM/&FILNAM) OBJTYPE(*FILE) RTNLIB(&LIBNAM) + OBJATR(&OBJATR) IF COND(%SST(&OBJATR 1 2) *NE 'PF') THEN(DO) SNDPGMMSG MSGID(CPF8056) MSGF(QCPFMSG) MSGDTA(&FILE) + MSGTYPE(*ESCAPE) GOTO CMDLBL(ENDPGM) ENDDO DSPDBR FILE(&LIBNAM/&FILNAM) OUTPUT(*OUTFILE) + OUTFILE(QTEMP/KEY001W1) DSPFD FILE(&LIBNAM/&FILNAM) TYPE(*ACCPTH) OUTPUT(*OUTFILE) + OUTFILE(QTEMP/KEY001W2) OVRDBF FILE(QADSPDBR) TOFILE(QTEMP/KEY001W1) RCVF: + RCVF MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(EOF)) IF COND(&WHREFI *NE ' ') THEN(DSPFD FILE(&WHRELI/&WHREFI) + TYPE(*ACCPTH) OUTPUT(*OUTFILE) OUTFILE(QTEMP/KEY001W2) + OUTMBR(*FIRST *ADD)) GOTO CMDLBL(RCVF) EOF: + DLTOVR FILE(QADSPDBR) OVRDBF FILE(QAFDACCP) TOFILE(QTEMP/KEY001W2) CALL PGM(KEY001RG) PARM(&OUTPUT &FILNAM &LIBNAM) DLTOVR FILE(QAFDACCP) GOTO CMDLBL(ENDPGM) ERROR: + RCVMSG MSGTYPE(*EXCP) MSGDTA(&MSGDTA) MSGID(&MSGID) SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) MSGDTA(&MSGDTA) + MSGTYPE(*ESCAPE) ENDPGM: + ENDPGM
Display Key Fields
Figure 3 CL program KEY001C1
KEY001C1: + PGM PARM(&FILE &LIB &LAST_USE &TEXT) DCL VAR(&CHGDATE) TYPE(*CHAR) LEN(13) DCL VAR(&DATE7) TYPE(*CHAR) LEN(7) DCL VAR(&FILE) TYPE(*CHAR) LEN(10) DCL VAR(&LAST_USE) TYPE(*DEC) LEN(6 0) DCL VAR(&LIB) TYPE(*CHAR) LEN(10) DCL VAR(&MMDDYY) TYPE(*CHAR) LEN(6) DCL VAR(&TEXT) TYPE(*CHAR) LEN(50) DCL VAR(&USEDATE) TYPE(*CHAR) LEN(7) DCL VAR(&YYMMDD) TYPE(*CHAR) LEN(6) /* Retrieve date last used and date last changed */ RTVOBJD OBJ(&LIB/&FILE) OBJTYPE(*FILE) TEXT(&TEXT) + CHGDATE(&CHGDATE) USEDATE(&USEDATE) /* If date last used is available, use it; else use the date + last changed */ IF COND(&USEDATE *GT '0000000') THEN(CHGVAR VAR(&DATE7) + VALUE(&USEDATE)) ELSE CMD(CHGVAR VAR(&DATE7) VALUE(%SST(&CHGDATE 1 7))) /* Reformat date to MMDDYY */ IF COND(&DATE7 *GT '0000000') THEN(DO) CHGVAR VAR(&YYMMDD) VALUE(%SST(&DATE7 2 6)) CVTDAT DATE(&YYMMDD) TOVAR(&MMDDYY) FROMFMT(*YMD) TOFMT(*MDY) + TOSEP(*NONE) CHGVAR VAR(&LAST_USE) VALUE(&MMDDYY) ENDDO ELSE CMD(CHGVAR VAR(&LAST_USE) VALUE(0)) ENDPGM
Display Key Fields
Figure 4 Display file KEY001DF
A DSPSIZ(24 80 *DS3) A PRINT A CA03(03 'Exit') A CA12(12 'Cancel') A R DSPSFL SFL A FILE 10A O 7 2 A LIB 10A O 7 13 A ATTR 6A O 7 24 A KEY 3Y 0O 7 32EDTCDE(4) A FIELD 10A O 7 37 A LSTUSE 6Y 0O 7 49EDTWRD(' / / ') A TEXT 50A O 8 24 A R DSPCTL SFLCTL(DSPSFL) A BLINK A OVERLAY A PUTOVR A SFLDROP(CA05) A SFLDSP A SFLDSPCTL A N90 SFLEND A SFLSIZ(0009) A SFLPAG(0008) A 1 32'Display Key Fields' A DSPATR(HI) A 3 2'File' A 3 13'Library' A 3 24'Text' A FILNAM 10A O 4 2 A LIBNAM 10A O 4 13 A PFTEXT 50A O 4 24 A 6 2'File' A DSPATR(HI) A 6 13'Library' A DSPATR(HI) A 6 24'Attrib' A DSPATR(HI) A 6 32'Key' A DSPATR(HI) A 6 37'Field' A DSPATR(HI) A 6 49'Last Use' A DSPATR(HI) A R DSPRCD A 23 2'F3=Exit F5=Fold/Truncate F12=C- A ancel' A COLOR(BLU)
Display Key Fields
Figure 5 Printer file KEY001P1
A R HEADER A 1 32'Display Key Fields' A 3 2'File' A 3 13'Library' A 3 24'Text' A FILNAM 10 4 2 A LIBNAM 10 4 13 A PFTEXT 50 4 24 A 6 2'File' A 6 13'Library' A 6 24'Attrib' A 6 32'Key' A 6 37'Field' A 6 49'Last Use' A 6 59'Text' A 8 1' ' * A R DETAIL SPACEA(1) A FILE 10 2 A LIB 10 13 A ATTR 6 24 A KEY 3 0 32EDTCDE(4) A FIELD 10 37 A LSTUSE 6 0 49EDTWRD(' / / ') A TEXT 50 59
Display Key Fields
Figure 6 RPG program KEY001RG
FQAFDACCPIF E DISK FKEY001DFCF E WORKSTN UC F SFLRN KSFILE DSPSFL FKEY001P1O E 88 PRINTER UC * E LN 5 1 * C *ENTRY PLIST C PARM OUTPUT 6 C PARM FILNAM C PARM LIBNAM * C READ QWHFDACP 99 C *IN99 DOWEQ'0' C APFILE IFNE SVFILE C APLIB ORNE SVLIB C SVFILE IFNE *BLANK C SVLIB ANDNE*BLANK C MOVE *BLANK FILE C MOVE *BLANK LIB C MOVE *BLANK ATTR C Z-ADD*ZERO KEY C MOVE *BLANK FIELD C Z-ADD*ZERO LSTUSE C MOVE *BLANK TEXT C ADD 1 SFLRN 30 * C OUTPUT IFEQ '* ' C WRITEDSPSFL C ELSE C *IN88 IFEQ '1' C WRITEHEADER C MOVE '0' *IN88 C END C WRITEDETAIL C END * C END C MOVELAPFILE SVFILE 10 C MOVELAPLIB SVLIB 10 C MOVELAPFILE FILE C MOVELAPLIB LIB C MOVELAPFATR ATTR C CALL 'KEY001C1' C PARM APFILE C PARM APLIB C PARM LSTUSE C PARM TEXT C END C MOVELAPKEYF FIELD C Z-ADDAPKEYN KEY C ADD 1 SFLRN * C OUTPUT IFEQ '* ' C WRITEDSPSFL C ELSE C *IN88 IFEQ '1' C WRITEHEADER C MOVE '0' *IN88 C END C WRITEDETAIL C END * C MOVE *BLANK FILE C MOVE *BLANK LIB C MOVE *BLANK ATTR C Z-ADD*ZERO LSTUSE C MOVE *BLANK TEXT C READ QWHFDACP 99 C END * C OUTPUT IFEQ '* ' C WRITEDSPRCD C EXFMTDSPCTL C END * C SETON LR ***************************************************************** C *INZSR BEGSR C CALL 'KEY001C1' C PARM FILNAM C PARM LIBNAM C PARM LSTUSE C PARM PFTEXT C OUTPUT IFEQ '* ' C OPEN KEY001DF C ELSE C OPEN KEY001P1 C WRITEHEADER C END C ENDSR
LATEST COMMENTS
MC Press Online