ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Program to count spooled files

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Program to count spooled files

    I have a database file XYZ which stores some specific spooled files. I want to write a program which read each row from XYZ and returns how many times this row (file) occurs in the spool.

    I used the system API List Spooled Files (QUSLSPL) to extract all the spool files and count the number of occurences. However I encountered a problem - the system I'm working has too many spooled files and the result is too large to store in a user space (limit 16mb).

    Is there a way to circumvent this limit? Or anyone have idea how to solve this problem?

    Thank you

  • #2
    You can use the QGYOLSPL API, it does not have the 16mb limit. You can call it for a group of entries (like 1000 at a time) and have it continue building the list (i.e. loading the next 1000) in the background while you're processing the first group, etc. Its more complex than the QUSLSPL API, but also much more versatile.

    Comment


    • #3
      The QGYOLSPL API seems what I'm looking for. However when I call the API it returns the error: MCH1210 - Receiver value too small to hold result. I tried other input parameters combinations without success. I'm using V6R1.

      Code:
           
           D* 
           D wsreceiver      s          32000
           D* 
           D wserror         ds           256
           D  wnbytespr                    10i 0 inz(%size(wserror))
           D  wnbytesav                    10i 0 inz(0)
           D  wamsgid                       7
           D  wamsgdata                   132
           D* Variabels
           D wnlength        s             10i 0 Inz(%Size(wsreceiver))
           D walistinfo      s             80
           D wnnrec          s             10i 0
           D wasortinfo      s           1024
           D wafinfo         s           1024
           D waqualname      s             26
           D waformat        s              8
            *---------------------------------------------------------------------------------------------
           C                   exsr      clqgyolspl                                                                            
           *---------------------------------------------------------------------------------------------
            * Open List of Spooled Files
            *---------------------------------------------------------------------------------------------
           C     clqgyolspl    begsr
           C                   call      'QGYOLSPL'
           C                   parm                    wsreceiver
           C                   parm      32000         wnlength                
           C                   parm                    walistinfo
           C                   parm      10            wnnrec 
           C                   parm      *blanks       wasortinfo
           C                   parm      *blanks       wafinfo
           C                   parm      *blanks       waqualname
           C                   parm      'OSPL0300'    waformat
           C                   parm      *blanks       wserror
           C                   endsr

      Comment


      • #4
        Gosh, haven't seen ancient parameter lists or fixed format rpg in ages.
        I suspect the error is coming from your error code variable. You have set it up correctly in the D specs and set the size of the WNBYTESPR field, however you are overwriting the WNBYTESPR parameter in the parameter list of your call. Why are you setting it to blanks? That is placing hex "40"s into the WNBYTESPR variable which is a very large value and likely causing the error. This is very bad as if the API accepted the large value (and many do), it could cause an error to overwrite memory.

        Comment


        • #5
          Originally posted by Carpiem View Post
          I have a database file XYZ which stores some specific spooled files. I want to write a program which read each row from XYZ and returns how many times this row (file) occurs in the spool.

          I used the system API List Spooled Files (QUSLSPL) to extract all the spool files and count the number of occurences. ...
          Passing blanks in the error code is almost certainly the problem, so that's probably solved.

          But can you clarify? You want to count database rows and you also want to count user space entries (or other API results)? It's not clear if your "database" paragraph has any relationship to the problem or it's merely secondary info. Also, when you list spooled files, are they listed so that you want to know the total? Or do you process the list in order to count only a specific subset? (If so, how do you know which ones to 'count'?)

          I ask because it might be possible to get the count more or less automatically without 'counting' at all.
          Tom

          There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

          Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

          Comment


          • #6
            If you are on at least v7r1, why not use the DB2 for i Services?

            Code:
            Select
            A.OBJNAME As LIB_NAME, B.OBJNAME As OUTQ_NAME,
            C.CREATE_TIMESTAMP,C.SPOOLED_FILE_NAME,
            C.USER_NAME,C.USER_DATA,C.STATUS,SIZE,C.TOTAL_PAGES,
            C.COPIES,C.FORM_TYPE,C.JOB_NAME,C.DEVICE_TYPE,
            C.OUTPUT_PRIORITY,C.FILE_NUMBER
            From
            Table(QSYS2.OBJECT_STATISTICS('*ALL', '*LIB')) As A
            Cross Join
            Table(QSYS2.OBJECT_STATISTICS(A.OBJNAME, 'OUTQ')) As B
            Cross Join
            Table(QSYS2.OUTPUT_QUEUE_ENTRIES(A.OBJNAME, B.OBJNAME, '*NO')) As C
            Stacking the UDTF functions will most likely prevent any issues exceeding the maximum sizes.
            UDTF #1 Object_Statistics generates a list of all libraries on the system.
            UDTF #2 Object_Statistics generates a list of all output queues in each library separately.
            UDTF #3 Output_Queue_Entries generates a list of all spoolfiles in each output queue separately.

            The statement could be joined to file XYZ to perform aggregate summary functions to count the number of spoolfiles which exist.

            Jim

            Comment


            • #7
              I corrected passing *blanks to the error code but it still returns the MCH1210 error.

              Originally posted by tomliotta View Post
              But can you clarify? You want to count database rows and you also want to count user space entries (or other API results)? It's not clear if your "database" paragraph has any relationship to the problem or it's merely secondary info. Also, when you list spooled files, are they listed so that you want to know the total? Or do you process the list in order to count only a specific subset? (If so, how do you know which ones to 'count'?)
              Sorry I think I didn't explained the problem very well.

              Basically I want to know how many times a spool file occurs in the spool of the system. I need to repeat this process for some predefined spool files which btw are stored in a database file.

              Hope I clarified the problem.

              Originally posted by Jim_IT View Post
              If you are on at least v7r1, why not use the DB2 for i Services?
              I'm in v6r1 so I think this won't work.

              Comment


              • #8
                That does indeed clarify. How many rows in the table? (How many spooled file names?) Are there any other useful characteristics that can help, such as a date range? (If you have spooled files going back a year or more, but you know ones you're looking for are only 6 months old or younger, it could help.)
                Tom

                There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

                Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

                Comment


                • #9
                  There's around 300 rows in the table but this number can vary. There isn't filtering options like date ranges. If the spooled file X is in the table I want to know how many times it occurs in the spool of the system, that is, the total you see when you execute the WRKSPLF command for the X file for all users.

                  I managed to develop a solution using the List Spooled Files (QUSLSPL) API. The problem occurred when we deployed the code in a client with a lot of spooled files. It exceeded the maximum capacity of the user space (16mb).

                  The QGYOLSPL API doesn't seem to have a space limit however I can't make it work =/. If you've any other suggestion I would highly appreciated.


                  Comment


                  • #10
                    Ah, a function for various clients. Makes it more difficult in the sense that it must work in many different environments. The QGYOLSPL API is probably the way to go (though I like the OBJECT_STATISTICS() suggestion). Post any details of the API problem; it should be easy enough to fix if we can see code extracts.
                    Tom

                    There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

                    Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

                    Comment


                    • #11
                      I wrote a really simple program just to check if I could get any values returned but I'm getting the MCH1210 - Receiver value too small to hold result Error. Here's the code:

                      Code:
                           D* DS receiver
                           D wsreceiver      s          10000
                           D* DS error
                           D wserror         ds           256
                           D  wnbytespr                    10i 0 inz(%size(wserror))
                           D  wnbytesav                    10i 0 inz(0)
                           D  wamsgid                       7
                           D  wamsgdata                   132
                           D*---------------------------------------------------------------------------------------------
                           D wnlength        s             10i 0 Inz(%Size(wsreceiver))
                           D walistinfo      s             80
                           D wnnrec          s             10i 0
                           D wasortinfo      s           1024
                           D wafinfo         s           1024
                           D waqualname      s             26
                           D waformat        s              8
                            *---------------------------------------------------------------------------------------------
                            * MAIN LINE
                            *---------------------------------------------------------------------------------------------
                           C                   exsr      clqgyolspl
                           C                   eval      *inlr = '1'
                           C                   return
                            *---------------------------------------------------------------------------------------------
                            * Chama a API de sistema Open List of Spooled Files
                            *---------------------------------------------------------------------------------------------
                           C     clqgyolspl    begsr
                           C                   call      'QGYOLSPL'
                           C                   parm                    wsreceiver
                           C                   parm                    wnlength
                           C                   parm                    walistinfo
                           C                   parm      5             wnnrec
                           C                   parm      *blanks       wasortinfo
                           C                   parm      *blanks       wafinfo
                           C                   parm      *blanks       waqualname
                           C                   parm      'OSPL0300'    waformat
                           C                   parm                    wserror
                           C                   endsr
                      The QGYOLSPL is a Process Open List API meaning I can request additional entries later using the Get List Entries (QGYGTLE) API. However I can't make a first call =/

                      Comment


                      • #12
                        Looking at the API, you haven't defined the sort info correctly, nor the filter information. You should have something like this:
                        Code:
                         * Prototype for QGYOLSPL API.                                
                        DQGYOLSPL         PR                  ExtPgm('QGYOLSPL')
                        D prRecevier                          Like(splfrcvr)
                        D prRcvrLen                     10i 0 Const
                        D prListInfo                          LikeDS(listinfo)        
                        D prRecs2Ret                    10i 0 Const                   
                        D prSortInfo                          LikeDS(sortinfo) Const  
                        D prFilterInfo                        LikeDS(ospf0100) Const  
                        D prQualJobName                 26a   Const                   
                        D prFormat                       8a   Const                   
                        D prErrorCode                         LikeDS(errorcode)       
                        D prFltrFmt                      8a   Options(*nopass) Const  
                        
                         * Prototype for QGYCLST API.                                 
                        DQGYCLST          PR                  ExtPgm('QGYCLST')       
                        D prReqHandle                         Like(listinfo.reqhandle)
                        D prErrorCode                         LikeDS(errorcode)      
                        
                         * ListInfo.                                   
                        DListInfo         DS                  Qualified
                        D TotRecs                       10i 0          
                        D RecsRet                       10i 0          
                        D ReqHandle                      4a            
                        D RecLen                        10i 0          
                        D InfoCompInd                    1a            
                        D DateTime                      13a            
                        D ListInd                        1a            
                        D Reserved1                      1a            
                        D LenInfoRet                    10i 0          
                        D FirstRec                      10i 0          
                        D Reserved2                     40a           
                        
                         * Details returned - OSPL0300 format.
                        DOSPL0300         DS                  Qualified
                        D JobName                       10a            
                        D UserName                      10a            
                        D JobNum                         6a            
                        D SpoolName                     10a            
                        D SpoolNum                      10i 0          
                        D FileSts                       10i 0          
                        D CrtDate                        7a            
                        D CrtTime                        6a            
                        D SplFSch                        1a            
                        D JobSysName                    10a            
                        D UserData                      10a            
                        D SplFForm                      10a            
                        D OutQ                          10a            
                        D OutQLib                       10a            
                        D ASP                           10i 0          
                        D SplFSize                      10i 0          
                        D SplFSizeMult                  10i 0         
                        D TotPages                      10i 0           
                        D CopiesLeft                    10i 0           
                        D Priority                       1a             
                        D Reserved                       3a             
                        D IPPrtJobID                    10i 0           
                        
                         * Sort Info.                                   
                        DSortInfo         DS                  Qualified
                        D NumKeys                       10i 0 Inz(0)    
                        D KeyStart                      10i 0           
                        D KeyLen                        10i 0           
                        D KeyDtaType                     5i 0 Inz(x'00')
                        D Order                          1a   Inz(x'00')
                        D Reserved                       1a   Inz(x'00')
                        
                         * Filter Info - OSPF0100 format.
                        DOSPF0100         DS                  Qualified     
                        D NumUserNames                  10i 0 Inz(1)        
                        D UserName                      10a   Inz('*ALL')   
                        D Reserved1                      2a   Inz(*ALLx'00')
                        D NumOutQs                      10i 0 Inz(1)        
                        D OutQName                      10a   Inz('*ALL')   
                        D OutQLib                       10a                 
                        D FormType                      10a   Inz('*ALL')   
                        D UsrDta                        10a   Inz('*ALL')   
                        D NumSts                        10i 0 Inz(1)        
                        D SplFSts                       10a   Inz('*ALL')   
                        D Reserved2                      2a   Inz(*ALLx'00')
                        D NumPrtDevs                    10i 0 Inz(1)        
                        D PrtDevName                    10a   Inz('*ALL')   
                        D Reserved3                      2a   Inz(*ALLx'00')
                        
                         * Error code structure.                         
                        DErrorCode        DS                  Qualified            
                        D BytesPr                       10i 0 Inz(%Size(errorcode))
                        D BytesAv                       10i 0                      
                        D MsgID                          7a                        
                        D Reserv                         1a                        
                        D MsgDta                       256a                        
                        
                         * Stand alone variables                                   
                        DSplFRcvr         S          32767a                        
                        
                         *-----------------------------------------------------------------------------------
                         *                                                                                   
                         /FREE                                                                               
                        
                          QGYOLSPL(splfrcvr : %Size(splfrcvr) : listinfo : -1 :                              
                                   sortinfo : ospf0100 : *BLANKS : 'OSPL0300' : errorcode);
                        
                          // Your code in here
                        
                          QGYCLST(listinfo.reqhandle : errorcode);
                        
                          *INLR = *ON;                            
                        
                         /END-FREE
                        You can use the QGYGTLE API to return additional information to the receiver and if you use a basing pointer for the OSPL0300 receiver, you can just increment that to get each entry. Or I guess you could just use the QGYGTLE API to get each entry.

                        Comment


                        • #13
                          This works perfectly and it was exactly what I was looking for. Many thanks for your help!

                          Comment

                          Working...
                          X