When you set out to plan your summer vacation, one of the most important components can be a good road map to help you find which path you need to follow to get to your final destination. Likewise, when you sit down to write a new program on the AS/400, one of the first steps is to determine what paths are available for the data you need to process.
To find the existing data paths that are available over a physical file on the AS/400, you generally will perform the following steps:
1. Run the Display Database Relations (DSPDBR) command over the physical file in question.
2. Write down the file and library names of each logical file found on the DSPDBR display.
3. Run the Display File Description (DSPFD) command over each logical file to see if the data path you need already exists.
These steps are time consuming and cut into your productivity as a programmer.
Some creative programmers have developed tools that will perform these steps for you automatically and save you much of the time required (see "Display Key Fields," MC, September 1991). These tools write the results of the DSPDBR and DSPFD commands to a file and then either print or display the information. The Display Access Path (DSPACCPTH) command in this article goes one step further. By using the system Application Program Interfaces (APIs), DSPACCPTH generates the information much more quickly.
The DSPACCPTH command output will look like the example in 1. If a path exists for the physical file, it will be listed first. Key fields and their sequence (ascending or descending) are shown. Then, the key fields are followed by any select or omit statements for each logical file built over the physical (even if the logical file is in a different library).
The DSPACCPTH command output will look like the example in Figure 1. If a path exists for the physical file, it will be listed first. Key fields and their sequence (ascending or descending) are shown. Then, the key fields are followed by any select or omit statements for each logical file built over the physical (even if the logical file is in a different library).
Breaking Down the Code
2 contains the code for the DSPACCPTH command, 3 contains the code for the display file ACC001DF, and 4 contains the code for the CPP, RPG program ACC001RG. Let's take a closer look at the RPG program in 4 since that's where all of the processing takes place.
Figure 2 contains the code for the DSPACCPTH command, Figure 3 contains the code for the display file ACC001DF, and Figure 4 contains the code for the CPP, RPG program ACC001RG. Let's take a closer look at the RPG program in Figure 4 since that's where all of the processing takes place.
The program begins by calling the Create User Space (QUSCRTUS) API to create a temporary user space in QTEMP. Subsequent APIs retrieve file information from the user space. Next, the program calls the Retrieve Object Description (QUSROBJD) API to determine if the file exists. If the file does not exist or if it is not a physical file, the SNDMSG subroutine uses the Send Program Message (QMHSNDPM) API to report the error. If the physical file does exist, the GETFIL subroutine retrieves the key field information for the physical file.
The GETFIL subroutine calls the Retrieve File Description (QDBRTVFD) API, which returns information about the key fields in a variable called RCVVAR. This variable contains data that can be at different locations each time you run the DSPACCPTH command depending on the number of key fields. To extract the data in RCVVAR, the program loads the information into one long generic array. The RCVVAR variable also contains offset values to indicate where the data is located. Using the offset values, parts of the array are moved into data structures. The data structures contain the subfields that are used to load the display file (ACC001DF) fields.
Two loops have been established to get all the key field information for physical and logical files. The first loop uses FMTNUM, which is the number of formats in the file (logical files can have more than one format). The field QBDFOS contains the offset location to key field information defined in the FNDSEL data structure. The FNDSEL structure contains the offset field (OFFSET), which points to an array of key field names. The field NUMKEY indicates how many key fields exist and is used as the index for the second loop. One subfile record is written for each key field in the array.
The next step is to determine whether or not there are any select or omit statements used in the path. The FNDSEL structure also contains offset values to get at this information. SOON is the number of select/omit statements, while SOOF is the offset to the array.
To get a list of all the logical files over the physical file, the program uses the List Database Relations (QDBLDBR) API. This API lists the logical files in the user space. The Retrieve User Space (QUSRTVUS) API extracts the file information for each logical file. This is done by first retrieving a generic header structure that contains the location of the file information and then "walking" through the list stored in the user space until each file has been processed. For each file in the list, the GETFIL subroutine is executed to perform the same functions as were done on the physical file.
Let DSPACCPTH Be Your Guide
As you will see when you run this command, you now have an instant "roadmap" of all of the paths that exist over any physical file on your system. This tool can be a real time saver when you are designing new programs.
Doug Pence is the founder and Ron Hawkins is the research and development manager of Computer Processing Unlimited, Inc. in San Diego, California.
REFERENCE System API Reference (SC41-3801, CD-ROM QBKAVD00).
The Display Access Path Utility
Figure 1 The Display Access Path Screen
UNABLE TO REPRODUCE GRAPHIC
The Display Access Path Utility
Figure 2 DSPACCPTH Command
/*===============================================================*/ /* To compile: */ /* */ /* CRTCMD CMD(XXX/DSPACCPTH) PGM(XXX/ACC001RG) + */ /* SRCFILE(XXX/QCMDSRC) */ /* */ /*===============================================================*/ CMD PROMPT('Display Access Paths') PARM KWD(FILE) TYPE(NAME1) MIN(1) PROMPT('File') NAME1: QUAL TYPE(*NAME) LEN(10) QUAL TYPE(*CHAR) LEN(10) DFT(*LIBL) SPCVAL((' ' + *LIBL)) CHOICE('Name, *LIBL') + PROMPT('Library')
The Display Access Path Utility
Figure 3 ACC001DF Display File
*=============================================================== * To compile: * * CRTDSPF FILE(XXX/ACC001DF) SRCFILE(XXX/QDDSSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A DSPSIZ(24 80 *DS3) A CF12 A CF03 A R SFLRCD SFL A SFLIB 10A O 7 2 A SFFILE 10A O 7 13 A SFFMT 10A O 7 24 A SFKEY 10A O 7 35 A SFASND 1A O 7 46 A SFRULE 1A O 7 48 A SFCOMP 2A O 7 50 A SFVALU 28A O 7 53 A R SFLCTL SFLCTL(SFLRCD) A SFLSIZ(0030) A SFLPAG(0015) A OVERLAY A 21 SFLDSP A SFLDSPCTL A N03 SFLEND(*MORE) A 1 30'Display Access Paths' A DSPATR(HI) A 3 2'File . . . . . . . . :' A OUTFIL 10A O 3 26 A 4 4'Library . . . . . :' A OUTLIB 10A O 4 28 A 6 2'Library ' A DSPATR(HI) A 6 13'File ' A DSPATR(HI) A 6 24'Format' A DSPATR(HI) A 6 35'Key Field' A DSPATR(HI) A 6 45'Seq' A DSPATR(HI) A 6 49'Select/Omit Values' A DSPATR(HI) A R FORMAT1 A 23 2'F3=Exit' A COLOR(BLU) A 23 13'F12=Previous' A COLOR(BLU) *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7
The Display Access Path Utility
Figure 4 ACC001RG RPG Program
*=============================================================== * To compile: * * CRTRPGPGM PGM(XXX/ACC001RG) SRCFILE(XXX/QRPGSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 FACC001DFCF E WORKSTN F RRN KSFILE SFLRCD E AR 4096 1 E A2 28 1 IGENDS DS I B 113 1160SIZINP I B 125 1280OFFLST I B 133 1360NUMLST I B 137 1400SIZENT IINPUT DS I 1 20 USRSPC I 1 10 SPCNAM I 11 20 SPCLIB I 21 28 OUTFMT I 29 48 FILLII I 29 38 FILNAI I 39 48 FILLBI I 49 58 RCDFMI ILIST DS I 1 20 MAINFL I 1 10 MNFILE I 11 20 MNLIB I 21 30 DEPFIL I 31 40 DEPLIB I 41 41 DEPTYP I 42 44 DEPRSR I B 45 480BINREF IERROR IDS I B 1 40BYTPRV I B 5 80BYTAVA I 9 15 MSGID I 16 16 ERR### I 17 116 MSGDTA IRCVVAR DS 4096 I B 62 630FMTNUM I B 317 3200QDBFOS I 337 338 ACCTYP I DS I B 1 40STRPOS I B 5 80STRLEN I B 9 120LENSPC I B 13 160RCVLEN I B 17 200MSGKEY I B 21 240MSGDLN I B 25 280MSGQNB IKEYDTA DS I 1 10 DEPKEY I 14 14 ASEDES IFNDSEL DS 150 I 70 79 FNDFMT I B 117 1180NUMKEY I B 130 1310SOON I B 132 1350SOOF I B 136 1390OFFSET IKEYSEL DS 150 I 3 3 RULE I 4 5 COMP I 6 15 CMPNAM I B 29 320SOSO IKEYSOS DS 150 I B 5 60NL I 21 48 SELVAR I '*REQUESTER*LIBL' C REQSTR C *ENTRY PLIST C PARM FILLIB 20 C MOVELFILLIB OUTFIL C MOVE FILLIB OUTLIB C MOVEL'USRSPC' SPCNAM C MOVEL'QTEMP' SPCLIB C MOVELOUTFIL FILNAI C MOVELOUTLIB FILLBI C Z-ADD116 BYTPRV C 'QCPFMSG' CAT 'QSYS':3 MSGF * Create user space C CALL 'QUSCRTUS' C PARM USRSPC C PARM *BLANKS ATRSPC 10 C PARM 1024 LENSPC C PARM *BLANKS VALSPC 1 C PARM '*CHANGE' AUTSPC 10 C PARM *BLANKS TXTSPC 50 C PARM '*YES' RPLSPC 10 C PARM ERROR * Attempt to retrieve object description C CALL 'QUSROBJD' C PARM RCVVAR C PARM 100 RCVLEN C PARM 'OBJD0100'FILFMT 8 C PARM FILLIB C PARM '*FILE' OBJTYP 8 C PARM ERROR * File doesn't exist, send message and get out C MSGID IFNE *BLANKS C EXSR SNDMSG C ELSE C MOVE FILLIB SFILLB C 10 SUBSTRCVVAR:39 OUTLIB C MOVE *ON FIRST 1 * Write access path C EXSR GETFIL C MSGID IFNE 'CPF5715' C MSGID CABEQ'CPF3210' END C MOVE *OFF FIRST * List database relations to the user space C CALL 'QDBLDBR' C PARM USRSPC C PARM 'DBRL0100'OUTFMT 8 C PARM FILLII C PARM '*FIRST' RCDFMI C PARM *BLANKS IGNORE 10 C PARM ERROR C MSGID IFNE 'CPF5715' C Z-ADD1 STRPOS C Z-ADD140 STRLEN * Retrieve user space general information C CALL 'QUSRTVUS' C PARM USRSPC C PARM STRPOS C PARM STRLEN C PARM GENDS C Z-ADD1 STRPOS C Z-ADDSIZINP STRLEN * Retrieve user space detail information C CALL 'QUSRTVUS' C PARM USRSPC C PARM STRPOS C PARM STRLEN C PARM INPUT C MOVEL'USRSPC' SPCNAM C MOVEL'QTEMP' SPCLIB C OFFLST ADD 1 STRPOS C Z-ADDSIZENT STRLEN * Retrieve the list by walking through the user space C DO NUMLST C CALL 'QUSRTVUS' C PARM USRSPC C PARM STRPOS C PARM STRLEN C PARM LIST C DEPFIL CABEQ'*NONE' NORECS C MOVELDEPFIL SFILLB C MOVE DEPLIB SFILLB C EXSR GETFIL C EXSR CLEAR C ADD SIZENT STRPOS C ENDDO C ENDIF C ENDIF C NORECS TAG C RRN IFGT 0 C SETON 21 C ENDIF C WRITEFORMAT1 C EXFMTSFLCTL C ENDIF C END TAG C SETON LR *=============================================================== C SNDMSG BEGSR * Send error message C CALL 'QMHSNDPM' C PARM MSGID C PARM MSGF 20 C PARM FILLIB C PARM 20 MSGDLN C PARM '*DIAG' MSGTYP 10 C PARM '*' MSGQ 10 C PARM 1 MSGQNB C PARM MSGKEY C PARM ERROR C ENDSR *=============================================================== C GETFIL BEGSR * Get key field info for each logical C CALL 'QDBRTVFD' C PARM RCVVAR C PARM 4096 RCVLEN C PARM RFILLB 20 C PARM 'FILD0100'FILFMT 8 C PARM SFILLB 20 C PARM RCDFMT 10 C PARM '0' OVRRID 1 C PARM '*LCL' SYSTEM 10 C PARM '*EXT' FMTTYP 10 C PARM ERROR C MSGID IFNE 'CPF5715' C MOVEARCVVAR AR,1 C FIRST IFEQ *ON C MOVE AR,9 TSTTYP 1 C TESTB'2' TSTTYP 01 C *IN01 IFEQ *ON C MOVE 'CPF3210' MSGID C EXSR SNDMSG C ENDIF C ENDIF C FIRST IFEQ *OFF C *IN01 OREQ *OFF C Z-ADDQDBFOS I 40 C DO FMTNUM C MOVEAAR,I FNDSEL C OFFSET ADD 1 S 40 C FIRST IFEQ *OFF C EXSR CLEAR C ADD 1 RRN C WRITESFLRCD C ENDIF C MOVE RFILLB SFLIB C MOVELRFILLB SFFILE C MOVELFNDFMT SFFMT C DO NUMKEY C MOVEAAR,S KEYDTA C TESTB'0' ASEDES 79 C SELEC C *IN79 WHEQ *OFF C MOVE 'A' SFASND C *IN79 WHEQ *ON C MOVE 'D' SFASND C ENDSL C MOVE DEPKEY SFKEY C ADD 1 RRN 40 C WRITESFLRCD C MOVE *BLANKS SFLIB C MOVE *BLANKS SFFILE C MOVE *BLANKS SFFMT C MOVE *BLANKS SFVALU C ADD 32 S C ENDDO C SOON IFNE *ZEROS C EXSR SELOMT C ENDIF C MOVE *BLANKS SFCOMP C MOVE *BLANKS SFRULE C ADD 160 I C ENDDO C ENDIF C ENDIF C ENDSR *=============================================================== C SELOMT BEGSR C SOOF ADD 1 I1 40 C DO SOON C MOVEAAR,I1 KEYSEL C COMP IFEQ 'AL' C ITER C ENDIF C MOVE COMP SFCOMP C MOVE RULE SFRULE C SOSO ADD 1 I2 40 C MOVEAAR,I2 KEYSOS C MOVEASELVAR A2 C SUB 19 NL C NL IFGT *ZEROS C MOVEA*BLANKS A2,NL C ENDIF C MOVEAA2,1 SFVALU C MOVE CMPNAM SFKEY C ADD 1 RRN 40 C MOVE *BLANKS SFASND C WRITESFLRCD C ADD 32 I1 C ENDDO C ENDSR *=============================================================== C CLEAR BEGSR C MOVE *BLANKS SFLIB C MOVE *BLANKS SFFILE C MOVE *BLANKS SFCOMP C MOVE *BLANKS SFFMT C MOVE *BLANKS SFRULE C MOVE *BLANKS SFVALU C MOVE *BLANKS SFKEY C MOVE *BLANKS SFASND C ENDSR *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7
LATEST COMMENTS
MC Press Online