Suppose you're working on a program to access a particular file. You're probably going to need answers to some basic questions about the file before you start coding. For example, what are the names of the fields in the file? What are the data types and sizes of those fields? How many decimal positions do the numeric fields have? Does the file have an access path, and, if so, what are the key fields?
Using native OS/400 commands, you would need to use at least two commands. First, you would probably run the Display File Field Description (DSPFFD) command. This presents you with a description of the fields, but the display is very cluttered. It shows you only a few fields per screen and forces you to page through lots of information to find what you're looking for. Next, you would probably run the Display File Description (DSPFD) command to determine the file's access path. Again you're presented with a cluttered display that makes it difficult to locate the names of the key fields.
The Display Record Format (DSPRCDFMT) utility presented in this article displays the vital information you need to know about a file in a concise, easy-to-read format, and it does the job fast because it uses APIs to retrieve the information. You can view this information on the screen or send it to a printer for easy offline reference.
I wrote this utility several years ago and have been using it ever since. The original version was published in "DSPRCDFMT-An API Alternative to DSPFFD" (MC, August 1991). This has always been one of my favorite utilities, but as I looked back at the original code, I realized there was quite a bit of room for improvement. So I decided it was time to give it an overhaul and bring it up to today's standards. By rewriting the code, I was able to improve the utility's performance, add a few new features, and make it fully compatible with the changes that have been made to the AS/400 database over the years. In this article, I'll introduce you to the new version of the DSPRCDFMT utility-Version 2.0. If you've been using the original version, you'll want to upgrade to this new version right away to start taking advantage of these improvements. If you've never used it before, I think you'll find it is an indispensable utility that you'll want to have.
The DSPRCDFMT command has three parameters. The first parameter (FILE) is the only one required; it allows you to enter the qualified name of the file you want to look at. The second parameter (RCDFMT) lets you enter the name of a record format within the file you've selected. The default value is *FIRST, which selects the first record format in the file. If you're displaying a multiformat logical file and you want to view a format other than the first one, you'll need to supply this parameter with a format name. The third parameter (OUTPUT) lets you control the output of the command. The default value presents the information on the screen, but you can use *PRINT to create a spooled file.
When you run the DSPRCDFMT command, you're presented with a screen similar to the one shown in 1. The top of the display contains the following file-level information:
When you run the DSPRCDFMT command, you're presented with a screen similar to the one shown in Figure 1. The top of the display contains the following file-level information:
* The name of the file and the library where it resides.
* The name of the record format currently being displayed.
* The file type (PF for physical or LF for logical).
* The record length of the record format.
* The number of fields.
* The text description of the file.
The main body of the display shows the fields along with their attributes. Here, you'll find the following information:
* The name of each field.
* The field type (e.g., Char, Packed, Zoned).
* The field size. (This is followed by the value V for variable length fields.)
* A number representing the relative position of each key field within the access path. (This is followed by the value D for key fields that are in descending order.)
* The field text or column heading if no text is available.
As you can see, you get a lot of valuable information in a condensed format.
In case you're curious about how this utility extracts file information using APIs, I'll briefly explain the code that accomplishes this task. The five components to the DSPRCDFMT utility are presented in2-6. Here you'll find a command definition, a CL program, a display file, a printer file, and an RPG program. The first four components are standard utility objects that don't require much explanation. The RPG program, RCD003RG (6), is where you'll find the most interesting code. Here, I've used several OS/400 APIs to extract file- and field-level information.
In case you're curious about how this utility extracts file information using APIs, I'll briefly explain the code that accomplishes this task. The five components to the DSPRCDFMT utility are presented in Figures 2-6. Here you'll find a command definition, a CL program, a display file, a printer file, and an RPG program. The first four components are standard utility objects that don't require much explanation. The RPG program, RCD003RG (Figure 6), is where you'll find the most interesting code. Here, I've used several OS/400 APIs to extract file- and field-level information.
The first API called in RCD003RG is the Retrieve File Description (QDBRTVFD) API. It returns the names of the key fields in the access path as well as the order of the keys, ascending or descending. These values are loaded into two arrays for later use.
The next API called is the List Fields (QUSLFLD) API. This produces information that describes the file and the attributes of the fields in the file. The output from this API is placed into a user space.
The rest of the API calls in the program are to the Retrieve User Space (QUSRTVUS) API to extract information back out of the user space. The QUSRTVUS API is called once to retrieve generic header information and once to retrieve file-level information, such as the file type, the record format name, and the number of fields. Then the QUSRTVUS API is called repeatedly in a loop to retrieve the field-level information, such as the field names, data types, and field sizes. Within this loop, information is also retrieved from the two arrays that contain key field information to identify the file's access path. The field-level information is written to a subfile or a printer file, depending on which type of output was requested. When all the fields have been processed, the program either displays the screen or writes the footing for the report.
In the process of testing this utility, I wanted to make sure it would work under the most extreme conditions. First, I determined V3R1's maximum limits for database files. Then I created files that used those limits to make sure that the utility would still work. Here are some interesting facts about DB2/400 database file limits under V3R1:
* The maximum number of fields is 8,000.
* The maximum number of key fields is 120.
* The maximum sum of the size of all key fields is 2,000 bytes.
* The maximum size of a character field is 32,766 bytes.
* The maximum size of a numeric field is 31 bytes.
* The maximum number of decimal positions for a numeric field is 31.
Even though you probably won't ever encounter files that come close to these limits, I tested this utility under these conditions, and it worked. In fact, it also works against all of the data types you can use in a database file, including some rather uncommon ones, such as binary, hex, and floating point.
I hope I've encouraged you to try this utility for yourself. As I've mentioned, it's practical, and it works under a wide variety of conditions. Here's one last thought I'd like to leave you with: This utility provides a classic example of how you can combine APIs to create commands that match or exceed the speed and functionality of native OS/400 commands. In this case, I've used only a handful of the hundreds of APIs available in OS/400. There are many more ways to combine these APIs to create exciting new utilities. If you create an innovative utility using APIs that you want to share with MC readers, send it to us. If we like it, we'll publish it. Think about it. Yours could be next.
Robin Klima is a senior technical editor for Midrange Computing. He can be reached by E-mail at
REFERENCE
OS/400 System API Reference V3R1 (SC41-3801, CD-ROM QBKAVD00).
The DSPRCDFMT Utility Version 2.0
Figure 1: The Display Record Format Screen
The DSPRCDFMT Utility Version 2.0
Figure 2: Command DSPRCDFMT
/*==================================================================*/ /* To compile: */ /* */ /* CRTCMD CMD(XXX/DSPRCDFMT) PGM(XXX/RCD003CL) + */ /* SRCFILE(XXX/QCMDSRC) */ /* */ /*==================================================================*/ CMD PROMPT('Display Record Format 2.0') PARM KWD(FILE) TYPE(QUAL) MIN(1) PROMPT('File') PARM KWD(RCDFMT) TYPE(*NAME) DFT(*FIRST) + SPCVAL((*FIRST)) PROMPT('Record format') PARM KWD(OUTPUT) TYPE(*CHAR) LEN(6) RSTD(*YES) + DFT(*) VALUES(* *PRINT) PROMPT('Output') QUAL: QUAL TYPE(*NAME) MIN(1) QUAL TYPE(*NAME) DFT(*LIBL) SPCVAL((*LIBL)) + PROMPT('Library')
The DSPRCDFMT Utility Version 2.0
Figure 3: CL Program RCD003CL
/*==================================================================*/ /* To compile: */ /* */ /* CRTCLPGM PGM(XXX/RCD003CL) SRCFILE(XXX/QCLSRC) */ /* */ /*==================================================================*/ PGM PARM(&FILE &RCDFMT &OUTPUT) DCL VAR(&FILE) TYPE(*CHAR) LEN(20) DCL VAR(&RCDFMT) TYPE(*CHAR) LEN(10) DCL VAR(&OUTPUT) TYPE(*CHAR) LEN(6) DCL VAR(&TYPE) TYPE(*CHAR) LEN(1) DCL VAR(&TEXT) TYPE(*CHAR) LEN(50) DCL VAR(&OBJATR) TYPE(*CHAR) LEN(10) DCL VAR(&MSGID) TYPE(*CHAR) LEN(7) DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(80) /* Send all errors to error handling routine */ MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR)) /* If running in batch send output to *PRINT */ RTVJOBA TYPE(&TYPE) IF COND(&TYPE *EQ '0') THEN(CHGVAR VAR(&OUTPUT) + VALUE('*PRINT')) /* Check to be sure file is a database file */ RTVOBJD OBJ(%SST(&FILE 11 10)/%SST(&FILE 1 10)) + OBJTYPE(*FILE) OBJATR(&OBJATR) TEXT(&TEXT) IF COND(&OBJATR *NE 'PF' *AND &OBJATR *NE 'LF') + THEN(SNDPGMMSG MSGID(CAE9076) + MSGF(QCPFMSG) MSGDTA(&FILE) MSGTYPE(*ESCAPE)) /* Create user space if necessary */ CHKOBJ OBJ(QTEMP/RCD003US) OBJTYPE(*USRSPC) MONMSG MSGID(CPF9801) EXEC(CALL PGM(QUSCRTUS) + PARM('RCD003US QTEMP' '' 100000 '' + '*ALL' '')) /* Call program to display record format information */ CALL PGM(RCD003RG) PARM(&FILE &RCDFMT &OUTPUT + &TEXT &MSGID &MSGDTA) IF COND(&MSGID *NE ' ') THEN(SNDPGMMSG + MSGID(&MSGID) MSGF(QCPFMSG) + MSGDTA(&MSGDTA) MSGTYPE(*ESCAPE)) /* Branch around error handling routine */ GOTO CMDLBL(ENDPGM) /* Error handling routine */ ERROR: RCVMSG MSGTYPE(*EXCP) MSGDTA(&MSGDTA) MSGID(&MSGID) SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) MSGDTA(&MSGDTA) + MSGTYPE(*ESCAPE) ENDPGM: ENDPGM
The DSPRCDFMT Utility Version 2.0
Figure 4: Display File RCD003DF
*=============================================================== * To compile: * * CRTDSPF FILE(XXX/RCD003DF) SRCFILE(XXX/QDDSSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A DSPSIZ(24 80 *DS3) A PRINT A CA03(03) A CA12(12) A R DSPDTL SFL A DTFLD 10 O 8 2 A DTTYPE 6 O 8 13 A DTSIZE 9 O 8 20 A DTKEY 3 0O 8 30EDTCDE(4) A DTSEQ 1 O 8 34 A DTTEXT 42 O 8 38 A R DSPHDR SFLCTL(DSPDTL) A SFLDSP A SFLDSPCTL A N03 SFLEND(*MORE) A SFLSIZ(0015) A SFLPAG(0014) A 1 30'Display Record Format' A DSPATR(HI) A 3 2'File . . :' A HDFILE 10 O 3 14 A 3 25'Record format . :' A HDRFMT 10 O 3 43 A 3 54'Record length . . :' A HDRLEN 5 0O 3 75EDTCDE(3) A 4 2'Library . :' A HDLIB 10 O 4 14 A 4 25'File type . . . :' A HDTYPE 10 O 4 43 A 4 54'Number of fields . :' A HDFLDS 4 0O 4 76EDTCDE(3) A 5 2'Text . . :' A HDTEXT 50 O 5 14 A 7 2'Field Type' A DSPATR(HI) A 7 23'Size Key Text' A DSPATR(HI) A R DSPFTR OVERLAY A 23 2'F3=Exit F12=Cancel' A COLOR(BLU) *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7
The DSPRCDFMT Utility Version 2.0
Figure 5: Printer File RCD003PR
*=============================================================== * To compile: * * CRTPRTF FILE(XXX/RCD003PR) SRCFILE(XXX/QDDSSRC) * *=============================================================== *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A R PRTHDR A 1 30'Display Record Format' A 3 2'File . . :' A HDFILE 10 O 3 14 A 3 25'Record format . :' A HDRFMT 10 O 3 43 A 3 54'Record length . . :' A HDRLEN 5 0O 3 75EDTCDE(3) A 4 2'Library . :' A HDLIB 10 O 4 14 A 4 25'File type . . . :' A HDTYPE 10 O 4 43 A 4 54'Number of fields . :' A HDFLDS 4 0O 4 76EDTCDE(3) A 5 2'Text . . :' A HDTEXT 50 O 5 14 A 7 2'Field Type' A 7 23'Size Key Text' A R PRTDTL SPACEB(1) A DTFLD 10 O 2 A DTTYPE 6 O 13 A DTSIZE 9 O 20 A DTKEY 3 0O 30EDTCDE(4) A DTSEQ 1 O 34 A DTTEXT 42 O 38 A R PRTFTR SPACEB(2) A 15'* * * * *' A 27'E N D O F L I S T I N G' A 57'* * * * *' *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7
The DSPRCDFMT Utility Version 2.0
Figure 6: RPG Program RCD003RG
*============================================================ * To compile: * * CRTRPGPGM PGM(XXX/RCD003RG) SRCFILE(XXX/QRPGSRC) * *============================================================ *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 FRCD003PRO E PRINTER UC FRCD003DFCF E WORKSTN UC F SRN KSFILE DSPDTL E LN 9 1 E KY 120 10 E SQ 120 1 E TYP 7 14 1 DSC 7 IRCVVAR DS 7736 I B 37 380NBRKYS I B 53 560OFFSET I DS I I 'RCD003US QTEMP ' 1 20 USRSPC I B 21 240STRPOS I B 25 280STRLEN I B 29 320RCVLEN IERRCOD DS I I 96 B 1 40BYTPRV I B 5 80BYTAVA I 9 15 ERRID I 17 96 ERRDTA IGENDS DS I B 65 680SIZHDR I B 117 1200OFFHDR I B 125 1280OFFLST I B 133 1360NUMLST I B 137 1400SIZENT IHEADER DS I 1 10 HDFILE I 11 20 HDLIB I 21 30 HDTYPE I 31 40 HDRFMT I B 41 440RCDLEN ILIST DS I 1 10 DTFLD I 11 11 DTATYP I B 21 240FLDLEN I B 25 280DIGITS I B 29 320DECPOS I 33 74 DTTEXT I 268 268 VARLEN * C *ENTRY PLIST C PARM FILE 20 C PARM RCDFMT 10 C PARM OUTPUT 6 C PARM TEXT 50 C PARM MSGID 7 C PARM MSGDTA 80 * * Open print file or display file C OUTPUT IFEQ '*PRINT' C OPEN RCD003PR C ELSE C OPEN RCD003DF C ENDIF * * Retrieve file description C CALL 'QDBRTVFD' C PARM RCVVAR C PARM 7736 RCVLEN C PARM RTNFIL 20 C PARM 'FILD0300'FMTNAM 8 C PARM FILE C PARM RCDFMT C PARM '1' OVRRID 1 C PARM '*LCL' SYSTEM 10 C PARM '*INT' FMTTYP 10 C PARM ERRCOD * * If no error then retrieve key fields C BYTAVA IFEQ 0 C OFFSET ADD 11 X 40 C DO NBRKYS C ADD 1 Y 40 C 10 SUBSTRCVVAR:X KY,Y C ADD 18 X C 1 SUBSTRCVVAR:X SQ,Y C ADD 46 X C ENDDO C ENDIF * * List fields into user space C CALL 'QUSLFLD' C PARM USRSPC C PARM 'FLDL0100'OUTFMT 8 C PARM FILE C PARM RCDFMT C PARM '1' OVRRID C PARM ERRCOD * * Trap for errors C BYTAVA IFGT 0 C MOVELERRID MSGID C MOVELERRDTA MSGDTA C ELSE * C Z-ADD1 STRPOS C Z-ADD140 STRLEN * * Retrieve generic header C CALL 'QUSRTVUS' C PARM USRSPC C PARM STRPOS C PARM STRLEN C PARM GENDS * C OFFHDR ADD 1 STRPOS C Z-ADDSIZHDR STRLEN * * Retrieve header section C CALL 'QUSRTVUS' C PARM USRSPC C PARM STRPOS C PARM STRLEN C PARM HEADER * C Z-ADDNUMLST HDFLDS C Z-ADDRCDLEN HDRLEN C MOVELTEXT HDTEXT * * Print heading if necessary C OUTPUT IFEQ '*PRINT' C WRITEPRTHDR C ENDIF * C OFFLST ADD 1 STRPOS C Z-ADDSIZENT STRLEN * * Process list data section C DO NUMLST * * Retrieve entry C CALL 'QUSRTVUS' C PARM USRSPC C PARM STRPOS C PARM STRLEN C PARM LIST * * Retrieve data type description C MOVE *BLANKS DTTYPE C Z-ADD1 X C DTATYP LOKUPTYP,X 99 C *IN99 IFEQ *ON C MOVELDSC,X DTTYPE C ENDIF * * Determine if current entry is a key field C Z-ADD*ZEROS DTKEY C MOVE *BLANKS DTSEQ C Z-ADD1 X C DTFLD LOKUPKY,X 99 C *IN99 IFEQ *ON C Z-ADDX DTKEY C TESTB'0' SQ,X 99 C *IN99 IFEQ *ON C MOVE 'D' DTSEQ C ENDIF C ENDIF * * Calculate field size C MOVEA*BLANKS LN C DIGITS IFNE 0 C MOVE DIGITS SIZE 5 C MOVEASIZE LN,1 C MOVE ',' LN,6 C MOVE DECPOS DEC 2 C DECPOS IFLT 10 C MOVE DEC LN,7 C ELSE C MOVEADEC LN,7 C ENDIF C ELSE C VARLEN IFEQ '1' C SUB 2 FLDLEN C MOVE 'V' LN,9 C ENDIF C MOVE FLDLEN SIZE C MOVEASIZE LN,1 C ENDIF C Z-ADD1 X C LN,X DOWEQ'0' C X ANDLT9 C MOVE *BLANKS LN,X C ADD 1 X C ENDDO C MOVEALN DTSIZE * * Print detail line or write subfile record C OUTPUT IFEQ '*PRINT' C WRITEPRTDTL C ELSE C ADD 1 SRN 40 C WRITEDSPDTL C ENDIF * C ADD SIZENT STRPOS C ENDDO * * Print footer or display screen C OUTPUT IFEQ '*PRINT' C WRITEPRTFTR C ELSE C WRITEDSPHDR C EXFMTDSPFTR C ENDIF C ENDIF * C MOVE *ON *INLR *============================================================ ** PPacked SZoned BBinary FFloat AChar LDate TTime ZTmStmp HHex JDBCS-J EDBCS-E ODBCS-O GDBCS-G
More 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.
TRY 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.
Forms 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.
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:
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.
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:
LATEST COMMENTS
MC Press Online