ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

RUNSQL, Spool Services & CL PGM

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

  • #16
    Dated yesterday or earlier:
    Create_timestamp < current date

    Dated before this time yesterday:
    Create_timestamp < current timestamp - 1 day

    Comment


    • #17
      Originally posted by Vectorspace View Post
      Dated yesterday or earlier:
      Create_timestamp < current date

      Dated before this time yesterday:
      Create_timestamp < current timestamp - 1 day
      "Create_timestamp < current date" worked as expected. Thank you!

      Comment


      • #18
        Thanks to everyone here and scouring the web, getting with IBM, I was finally able to achieve what I needed. Goal was to obtain list of spooled files in a 'held' status, then use CL pgm to release them automatically from the day before.

        Before I forget, I'm copying/pasting what I have. I plan on cleaning up it later on my own system.

        For SQL, obtaining the database file. Finding spooled files older than today:
        Code:
        CREATE TABLE MYTEST/SPLFILES AS                                      
        (SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER,CREATE_TIMESTAMP        
        FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('QUSRSYS','MYOUTQ','*NO')) A    
        WHERE STATUS IN('HELD')                                                
        AND CREATE_TIMESTAMP < CURRENT_DATE - 1 DAY)                            
        WITH DATA
        Before that, you need to clear table of course:
        Code:
        DROP TABLE MYTEST/SPLFILES CASCADE
        After that: Using SQL database file/outfile in CL pgm here - the 'release' pgm:
        Code:
        PGM                                                                            
        DCLF         FILE(MYTEST/SPLFILES) ALWVARLEN(*YES) ALWNULL(*YES)            
        
        DCL VAR(&SPOOL1) TYPE(*CHAR) LEN(10)                                          
                     DCL        VAR(&NUMBER) TYPE(*CHAR) LEN(6)                        
                     DCL        VAR(&USER) TYPE(*CHAR) LEN(10)                        
                     DCL        VAR(&NAME) TYPE(*CHAR) LEN(10)                        
                     DCL        VAR(&DATSEP) TYPE(*CHAR) LEN(1) VALUE('/')            
                     DCL        VAR(&SPOS) TYPE(*UINT) LEN(2)                          
                     DCL        VAR(&EPOS) TYPE(*UINT) LEN(2)                          
                     DCL        VAR(&LEN) TYPE(*UINT) LEN(2)                          
        READ:   RCVF                                                                  
        MONMSG MSGID(CPF0864 CPF2204 CPF3322) EXEC(GOTO CMDLBL(END))                  
        CHGVAR VAR(&SPOOL1) VALUE(%SST(&SPOOL00001 3 10))                              
        CHGVAR VAR(&NUMBER) VALUE(%SST(&JOB_NAME 3 6))                                
                     CHGVAR VAR(&SPOS) VALUE(%SCAN(&DATSEP &JOB_NAME))                
                     CHGVAR VAR(&SPOS) VALUE(&SPOS + 1)                                
                     CHGVAR VAR(&EPOS) VALUE(%SCAN(&DATSEP &JOB_NAME &SPOS))          
                     CHGVAR VAR(&LEN) VALUE(&EPOS - &SPOS)                  
                     CHGVAR VAR(&USER) VALUE(%SST(&JOB_NAME &SPOS &LEN))    
                     CHGVAR VAR(&EPOS) VALUE(&EPOS + 1)                    
                     CHGVAR VAR(&NAME) VALUE(%SST(&JOB_NAME &EPOS 10))      
        
        
        
        MONMSG MSGID(CPF0864 CPF2204 CPF3322)                              
        RLSSPLF FILE(&SPOOL1) JOB(&NUMBER/&USER/&NAME) SPLNBR(&FILE_00001)  
        MONMSG MSGID(CPF0864 CPF2204 CPF3322)                              
        GOTO CMDLBL(READ)        
        END:  ENDPGM
        I had to combine the SQL statements (using 'RUNSQLSTM') and 'release' CL pgm above into one main pgm to get this whole thing to work together.

        Been a wild ride! Does feel good to finally get this completed though.

        Comment


        • #19
          To get spool files older than 1 day, try this:
          Code:
          WHERE CREATE_TIMESTAMP < current timestamp - 1 day

          Comment


          • #20
            Oops, didn't realize that there was a second page for this thread where the question was already answered. Sure wish I could edit my posts.

            Comment


            • #21
              Originally posted by Brian Rusch View Post
              Oops, didn't realize that there was a second page for this thread where the question was already answered. Sure wish I could edit my posts.
              It's all good. I could actually do with some help nailing down the date format.

              Code:
              CREATE TABLE MYTEST/SPLFILES AS                                      
              (SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER,CREATE_TIMESTAMP        
              FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('QUSRSYS','MYOUTQ','*NO')) A    
              WHERE STATUS IN('HELD')                                                
              AND CREATE_TIMESTAMP < CURRENT_DATE - 1 DAY)                  
              WITH DATA
              This seems to be working logically as expected, grabs data that's more than 24 hrs old:
              Code:
              AND CREATE_TIMESTAMP < CURRENT_DATE - 1 DAY)
              However, what I need to do is grab data that was generated the day before no matter how new it is.

              If I have a spool file created 12/26/2018 at 11:59 PM at night, and I'm running this SQL at 12/27/2018 12:01 AM, I need to results to return that last spool file created 12/26 - if that makes sense. I'm thinking I need to convert the date perhaps getting rid of the time, leaving only YYYY/MM/DD perhaps.

              Comment


              • #22
                "Create_timestamp < current date" should work

                When you compare a date against a timestamp with SQL, the date is converted to a timestamp with time = 00:00:00
                If today is 28/12/2018, then "current date" as a timestamp is effectively 28/12/2018 00:00:00

                So "Create_timestamp < current date" resolves to "Create_timestamp < 28/12/2018 00:00:00" which covers anything dated before today. That should cover your requirement.

                Comment


                • #23
                  Originally posted by Vectorspace View Post
                  "Create_timestamp < current date" should work

                  When you compare a date against a timestamp with SQL, the date is converted to a timestamp with time = 00:00:00
                  If today is 28/12/2018, then "current date" as a timestamp is effectively 28/12/2018 00:00:00

                  So "Create_timestamp < current date" resolves to "Create_timestamp < 28/12/2018 00:00:00" which covers anything dated before today. That should cover your requirement.
                  Thanks for the response.

                  So far, I'm having better results with this one for another outq I need to monitor. Returns spool files created past 3 days:
                  Code:
                  AND CREATE_TIMESTAMP > CURRENT_DATE - 3 DAYS

                  Comment

                  Working...
                  X