Quick Query Over a Database File

Published: August 23, 2006

Dear esteemed professional colleagues:

Today's tip is not rocket science, but as I have said before, rocket science won't help me keep the factory running. Today I share with you a utility that I use constantly, day-in and day-out. Maybe some of you will find it handy as well.

The utility of the day is a command I wrote years ago to help me with testing. As you well know, when you're testing a program, you often have to look at database files to make sure they were modified correctly. The Display Physical File Member (DSPFFM) command is helpful sometimes, but it suffers from a couple of glaring problems, namely (1) the fields are jammed together and (2) packed and binary data is not easy for humans to read. I wrote the Query File (QF) utility as a way to quickly view the contents of a database file in a legible format.

The QF utility consists of two objects: a command and an OPM CL program. Here's the source for command QF:

/* =============================================================== */
/* Command: QF                                                     */
/* Program: QFC                                                    */
/* Purpose: Query a file                                           */
/*                                                                 */
/* To create:                                                      */
/*                                                                 */
/* CRTCMD CMD(xxx/QF) PGM(xxx/QFC) SRCFILE(xxx/QCMDSRC) SRCMBR(QF) */
/* ================================================================*/
                                                                   
             CMD        PROMPT('Query a file')                     
             PARM       KWD(FILE) TYPE(Q1) MIN(1) PROMPT('File')   
 Q1:         QUAL       TYPE(*NAME) MIN(1) EXPR(*YES)              
             QUAL       TYPE(*NAME) DFT(*LIBL) SPCVAL((*LIBL) +    
                          (*CURLIB)) EXPR(*YES) PROMPT('Library')  
             PARM       KWD(MBR) TYPE(*NAME) DFT(*FIRST) +         
                          SPCVAL((*FIRST) (*LAST)) EXPR(*YES) +    
                          PROMPT('Member')                         
             PARM       KWD(DEVICE) TYPE(*CHAR) LEN(1) RSTD(*YES) + 
                          DFT(D) VALUES(D P) EXPR(*YES) +          
                          PROMPT('Output to display or printer?')
             PARM       KWD(SELECT) TYPE(*CHAR) LEN(1) RSTD(*YES) +
                          DFT(N) VALUES(Y N) EXPR(*YES) +          
                          PROMPT('Select records?')

Here's the source for OPM CL program QFC:

/* =================================================================*/ 
/* Program: QFC                                                     */ 
/* Command: QF                                                      */ 
/* Purpose: Query a file                                            */ 
/*                                                                  */ 
/* To create:                                                       */ 
/*                                                                  */ 
/*    CRTCLPGM   PGM(xxx/QFC) SRCFILE(xxx/QCLSRC) SRCMBR(QFC)       */ 
/* =================================================================*/ 
            
pgm (&QualFile &Mbr &Device &Select) 
                                     
   dcl &QualFile *char   20          
   dcl &Mbr      *char   10          
   dcl &Device   *char    1 /* d=display, p=printer */ 
   dcl &Select   *char    1                            
   dcl &RcdSlt   *char    4 value(*NO)
   dcl &OutType  *char   10 value(*DISPLAY)    
                                               
      dcl   &Abending      *lgl                
      dcl   &MsgDta        *char    256        
      dcl   &MsgF          *char     10        
      dcl   &MsgFLib       *char     10        
      dcl   &MsgID         *char      7        
      dcl   &PgmName       *char     10        
      dcl   &RtnType       *char      2        
                                               
      monmsg (cpf0000 qry0000) exec(goto abend)
                                               
   if (&Select *eq 'Y' *or &Select *eq 'y') +  
      then(chgvar &RcdSlt '*YES')              
                                               
   if (&Device *eq 'P' *or &Device *eq 'p') +  
      then(chgvar &OutType '*PRINTER')

   runqry qryfile((%sst(&QualFile 11 10)/%sst(&QualFile 1 10) &Mbr)) + 
 outtype(&OutType) rcdslt(&RcdSlt) prtdfn(*NO)             
   return                                                              
/* ===========================================================*/ 
/* * Routine to handle unexpected errors */                      
Abend:                                                           
/* Don't let this program go into a loop here. */                
      if &Abending then(do)                                      
         sndpgmmsg  msgid(cpf9898) msgf(qcpfmsg) msgtype(*escape) + 
                      msgdta('Program' *bcat &PgmName *bcat +    
                             'ended abnormally at label Abend') 
         monmsg cpf0000  
         return          
      enddo              
      chgvar   &Abending '1' 
                             
/* Resend diagnostic & escape messages to the caller          */ 
/* caller as diagnostic messages.                             */
RcvDiag:                                                         
      rcvmsg     msgtype(*diag) msgdta(&msgdta) msgid(&msgid) + 
rtntype(&RtnType) +                          
                    msgf(&msgf) sndmsgflib(&msgflib)             
      if (&RtnType *eq '02') do                                  
         sndpgmmsg  msgid(&msgid) msgf(&msgflib/&msgf) +         
                   msgdta(&msgdta) msgtype(*diag)                
         goto RcvDiag                                            
      enddo                                                      
                                                                 
RcvEscape:                                                       
      rcvmsg     msgtype(*excp) msgdta(&msgdta) msgid(&msgid) +  
                    rtntype(&RtnType) +            
                    msgf(&msgf) sndmsgflib(&msgflib)             
      if ((&RtnType *eq '15') *or (&RtnType *eq '17')) do        
         sndpgmmsg  msgid(&msgid) msgf(&msgflib/&msgf) +         
                   msgdta(&msgdta) msgtype(*diag)                
      enddo

/* ========================================================== */
Escape:                                                        
 /* cancel the program */                                      
      sndpgmmsg  msgid(cpf9898) msgf(qcpfmsg) msgtype(*escape) +
                   msgdta('QF command ended abnormally')       
endpgm

QF has four parameters:

1. A qualified file name
2. A file member (default is *FIRST)
3. Whether the file data is to be displayed (D) or printed (P)
4. Whether or not the record selection panel is to be presented (Y or N)

Here are some examples of how you might use the QF command.

qf qiws/qcustcdt
qf qcustcdt select(y)
qf myfile mbr(m2005)

QF runs the Run Query (RUNQRY) command, so it doesn't do anything you can't already do, but it's much easier to type.

--Ted