The past couple of installments of APIs by Example dealt with database-related topics and utilities, and showed API-driven examples of how to take advantage of APIs when supporting the everyday tasks of a business application programmer.

Today I continue down that path and present the Work with Database Files (WRKDBF) and Display File Field Descriptions (DSPFFD2) commands. Because I'm aiming to provide a useful set of database tools for programmers, I've also included links to a very powerful freeware utility called UNDEL2 that lets you undelete previously deleted records in a database file.

The WRKDBF command offers a front end to the commands presented earlier, the DSPFFD2 command included today, and native IBM file-related commands -– and finally (and optionally) the freeware tool UNDEL2, which was written by Dave McKenzie. I've included a couple of links at the end of this article pointing you to sites where you can download the UNDEL2 utility. Be sure to read and fully comprehend the README file included with the download before you attempt to install and run the UNDEL2 command on your system.

Here's the WRKDBF command prompt:

                      Work with Database Files (WRKDBF)                
 Type choices, press Enter.                                               
 File . . . . . . . . . . . . . .                 Name, generic*, *ALL    
   Library  . . . . . . . . . . .     *LIBL       Name, *LIBL, *CURLIB... 
 File type  . . . . . . . . . . .   *ALL          *ALL, *PF, *LF, *DDMF   
 Sort order . . . . . . . . . . .   *FILE         *FILE, *LIB             

You can limit the list by file type to include only physical files (*PF), logical files (*LF), DDM files (*DDMF), or select all three types of database files (*ALL). Only physical files of type DTA (Data) are included. There's also an option to sort the list in either library, then file name or file name, then library order. Running the command

  WRKDBF FILE(QADB*)   
         TYPE(*PF)
         ORDER(*FILE) 

should take you to a display panel similar to the one below:

                          Work with Database Files                   WYNDHAMW
                                                            06-10-07  09:35:21
 Type options, press Enter.                                                    
   1=PDM   2=Change   3=Copy   4=Delete   5=Display data   6=Display fields    
   7=Display access paths   8=File description   9=Run query   10=Update data  
   13=Change description    14=Clear   15=Un-delete   16=Generate SQL DDL      
 Opt  File        Library     Type   Text                                      
      QADBCCST    QSYS        PF     Constraint Field Usage Information        
      QADBFCST    QSYS        PF     File Level Constraint Cross Reference File
      QADBFDEP    QSYS        PF     Cross reference dependency file           
      QADBIFLD    QSYS        PF     Cross reference physical file             
      QADBKFLD    QSYS        PF     Cross reference physical file             
      QADBPKG     QSYS        PF     SQL Package physical file                 
      QADBXMQT    QSYS        PF                                               
      QADBXRDBD   QSYS        PF     RDB Directory physical file               
      QADBXREF    QSYS        PF     Cross reference physical file             
      QADBXSFLD   QSYS        PF                                               
                                                                       More...
 Parameters or command                                                         
 ===>                                                                          
 F3=Exit      F4=Prompt   F5=Refresh   F9=Retrieve   F11=Display full text     
 F12=Cancel   F17=Top     F18=Bottom    

The availability of option 1=PDM and option 15=Un-delete depends on the presence of the Work with Objects using PDM (WRKOBJPDM) and Undelete Records (UNDEL2), respectively, since these are the commands evoked by said options. As for the remaining options, they provide access to the following database file related commands:

• 2=Change -- Runs or prompts the Change File command for the relevant type of file, either the Change Physical File (CHGPF), the Change Logical File (CHGLF) or the Change DDM File (CHGDDMF).

• 3=Copy -- Runs or prompts the Copy File (CPYF) command.

• 4=Delete -– Runs or prompts the Delete File (DLTF) command, following the presentation of a confirmation panel.

• 5=Display data -– Runs or prompts the Display Physical File Member (DSPPFM) command.

• 6=Display fields -– Runs or prompts the Display File Field Description (DSPFFD2) command.

• 7=Display access paths –- Runs or prompts the Display Physical File Access Paths (DSPPFAP) command.

• 8=File description –- Runs or prompts the Display File Description (DSPFD) command.

• 9=Run query -– Runs or prompts the Run Query (RUNQRY) command.

• 10=Update data -– Runs or prompts the Update Data with Temp Program (UPDDTA) command.

• 13=Change description -– Runs or prompts the Change Object Description (CHGOBJD) command.

• 14=Delete -– Runs or prompts the Clear Physical File Member (CLRPFM) command, following the presentation of a confirmation panel.

• 16=Generate SQL DDL –- Runs or prompts the Generate SQL DDL (GENSQLDDL) command.

Please refer to the WRKDBF command and display panel help text for further details. Links to the articles including the DSPPFAP and GENSQLDDL commands are provided at the end of this article. As mentioned earlier, the DSPFFD2 command is part of today's article, and the DSPFFD2 command prompt has the following appearance:

                   Display File Field Description (DSPFFD2)            
 Type choices, press Enter.                                             
 File . . . . . . . . . . . . . .                 Name                  
   Library  . . . . . . . . . . .     *LIBL       Name, *LIBL, *CURLIB  
 Record format  . . . . . . . . .   *FIRST        Name, *FIRST          
                            Additional Parameters                       
 System . . . . . . . . . . . . .   *LCL          *LCL, *RMT, *FILETYPE 

Specify a database file name optionally qualified by a library to display a list of all fields contained in the specified record format. The System parameter allows you to define whether a local or remote file, or either depending of the type of the specified file name, should be selected. Here's an example of the resulting display panel:

                        Display File Field Description                WYNDHAMW
                                                            06-10-07  11:27:42
 File . . . . . . :   QADBFDEP            Record length  . :   10425           
   Library  . . . :     QSYS              Field count  . . :   9               
 Record format  . :   QDBFDEP                                                  
 File type  . . . :   PF                  Include field  . .                   
 Access path  . . :   *KEYED              Include text . . .                   
 Field       Data type  Buffer  Length  Dig  Dec  Key   Text                   
 DBFFIL      Var Char        1     130            2  A  Dependency name        
 DBFLIB      Char          131      10            1  A  Dependency Library name
 DBFFDP      Var Char      141     130            4  A  File name of dependent 
 DBFLDP      Char          271      10            3  A  Library name of depende
 DBFTDP      Char          281       1                  Dependency: D-data,V-vi
 DBFRDP      Char          282      10            5  A  Format name of dependen
 DBFLB2      Var Char      292     130                  Library name           
 DBF_FSIG    Var Char      422   10002                  Signature              
 DBF_PCNT    Binary      10424       2    4    0        Paramater count        
                                                                        Bottom
 F3=Exit   F7=Position to          F9=Display PF access paths                   
 F10=Display data base relations   F11=Display column headings   F24=More keys  

For logical files there's also a header field specifying whether any select/omit criteria are defined for the logical file.

The Include field and Include text input fields let you subset the list to only display fields containing the specified string in the field name or text and column headings, respectively.

• F9=Display Physical File Access Paths (DSPPFAP) command
• F10=Display Data Base Relations (DSPDBR) command
• F13=Display File Description (DSPFD) command
• F14=Run Query (RUNQRY) command
• F15=Display Select/Omit (DSPFD TYPE(*SELECT)) command
• F16=Generate SQL DDL (GENSQLDDL) command
• F21=Print File Field Description (PRTFFD) command

Press function key F24 to toggle through all available function keys, including those related to the list presentation. Again, online command and display panel help text is provided to explain the details.

I hope you find the data base tools provided and presented in this and previous articles useful, and that they help in the part of your daily job that involves database development and maintenance. If you have any other ideas for API-driven programmer tools and utilities that might be worth considering for an upcoming APIs by Example article, I'd be happy to hear about it!

This APIs by Example includes the following sources:

CBX178  -- RPGLE  -- Display File Field Description 2 - CPP           
CBX178E -- RPGLE  -- Display File Field Description 2 - UIM Exit Program  
CBX178H -- PNLGRP -- Display File Field Description 2 - Help          
CBX178M -- CLP    -- Display File Field Description 2 - Build Command 
CBX178P -- PNLGRP -- Display File Field Description 2 - Panel Group   
CBX178X -- CMD    -- Display File Field Description 2                                 

CBX179  -- RPGLE  -- Work with Data Base Files - CCP              
CBX179E -- RPGLE  -- Work with Data Base Files - UIM Exit Program 
CBX179H -- PNLGRP -- Work with Data Base Files - Help             
CBX179P -- PNLGRP -- Work with Data Base Files - Panel Group      
CBX179V -- RPGLE  -- Work with Data Base Files - VCP              
CBX179X -- CMD    -- Work with Data Base Files                    

CBX178M -- CLP    -- Display File Field Description 2 - Build Command 
CBX179M -- CLP    -- Work with Data Base Files - Build Command

To create all above objects, compile and run CBX178M as well as CBX179M. Compilation instructions are found in the source headers as usual.

Speaking of freeware tools such as Dave McKenzie's UNDEL2, there's also a tool called WRKDBF in circulation. That tool was written by Bill Reger, but has since been withdrawn from distribution. If you have installed Bill's WRKDBF command, be sure to rename it before trying to install the WRKDBF command presented here -– or alternatively, change the WRKDBF command name to a unique command name (for example WRKDBF2) in the CBX179CL command building CL program, before compiling and running that CL program.

I've also included a slightly adapted version of the previously published Print File Field Description (PRTFFD) command that can be evoked from the DSPFFD2 command's display panel:

CBX123  -- RPGLE  -- Print File Field Description - CPP            
CBX123H –- PNLGRP -- Print File Field Description - Help           
CBX123X –- CMD    -- Print File Field Description                  

CBX123M –- CLP    -- Print File Field Description - Build Command

Again, to create all above objects, compile and run CBX123M.

Dave McKenzie's UNDEL2 utility download sites:

Martin Rowe's dbg400.net:
http://dbg400.net/cgi-bin/twiki/view/DBG400/UndelVersion2

Leif Guldbrand's www.think400.dk:
http://www.think400.dk/downloads.htm

Previously published related articles:

APIs by Example: Print File Field Description:
http://www.systeminetwork.com/article.cfm?id=19279

APIs by Example: Reverse Engineering Database Files and Objects to SQL DDL Statements:
http://www.systeminetwork.com/article.cfm?id=55321

APIs by Example: Displaying and Locating a Physical File's Access Paths:
http://www.systeminetwork.com/article.cfm?id=55516

This article demonstrates the following DB & File API:

Retrieve Data Base File Description (QDBRTVFD) API:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/apis/qdbrtvfd.htm

You can retrieve the source code for this API example from the following link:
http://www.pentontech.com/IBMContent/Documents/article/55705_345_DbFilesFieldsMore.zip