• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Quick Query Over a Database File

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    iTera:  High availability - Are you prepared in case of tornado? One customer is.
    Patrick Townsend & Associates:  Alliance AES/400 - database field encryption
    COMMON:  Join us at the Fall 2006 conference, September 17-21, in Miami Beach, Florida

    SaaS Is Real: Salesforce.com Boasts of 500,000 Subscribers Bang for the Buck: Raising the System iQ

    Leave a Reply Cancel reply

Volume 6, Number 31 -- August 23, 2006
THIS ISSUE SPONSORED BY:

WorksRight Software
Advanced Systems Concepts
CenturioDB

Table of Contents

  • Quick Query Over a Database File
  • iSeries Navigator Performance Advisor, At Your Service
  • Joblogapalooza, Its Possible Causes, and a Call for Input

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle