29
Fri, Nov
0 New Articles

The Display Access Path Utility

RPG
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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 
BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: