Automating Query every 10 minutes

pts.
Tags:
AS/400
Hope everyone is doing well, I'll start off by saying I'm new to this forum and rather wet behind the ears when it comes to AS/400. I've been using the AS/400 system in general for several years however since about a month ago I have been put in charge of programming a web interface to help streamline the company. This program requires that I keep a MySQL database up-to-date every 10 minutes. I have so far managed to automate the process of ftping the "finished" query off of the as/400 and onto the mysql server and imported into MySQL. The part I'm getting stuck at is automating the query itself using wrkjobscde, that function although great only does daily jobs not one for every 10 minutes. Right off the bat I've decided not to create 48 different schedules to accomplish this update over an eight hour period. Which brought me to the conclusion of looking up how to create loop program.... Well that's about the point that I get lost. Any help of creating a program to loop a query every 10 minutes would be fantastic, or if there is some other way to accomplish this task please fill me in :) if you need any additional information please feel free to PM me. Thank you ! - Jason

Answer Wiki

Thanks. We'll let you know when a new response is added.

Write a CL program to run the query. Use the DLYJOB command to wait for 10 minutes (600 seconds) after the query has run, then loop round (goto) to run the query again.

Submit the job from its own job queue, otherwise you may have problems with other jobs on the queue not being able to run until this one is fininshed (single-threading job queue), or else run it from a job queue which is allowed to run multiple jobs concurrently.

How do you end this job? Well, you could use brute force – when the subsystem is shut down, or if the operator actively ends the job. But you can do it from a menu option. Create a data area with the same name as the program. Create a CL to set the data area (say, to “STOP”). In the batch CL program, before the DLYJOB, test the data area. If it is “STOP”, set it to blank and end the program. If it is not “STOP”, perform the DLYJOB and loop.

So one menu option can run the program that sets the data area, and another menu option can submit the query job to the queue (the menu option can also check to see if there is already a job with that name active).

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ddune3566
    Create a new jobq. (newjobq) submit a CL Program Example (Mytran) Mytran: Loop: chgvar count = count + 1 dlyjob 10 minutes Run sql data gathering and queries FTP data if count
    0 pointsBadges:
    report
  • TomLiotta
    Rather than read a data area, read a data queue with a ten-minute wait time. When you want the job to end, send a *END message. You get the delay, you get real-time reaction to end-job requests and you get a potential mechanism for sending any future commands you might want other than "*END". If you use a DLYJOB, there's an average wait-time of five minutes before the job reacts to end-job requests with a potential delayed reaction up to 10 minutes. Tom
    125,585 pointsBadges:
    report
  • DanTheDane
    I prefer to use the jobscheduler for this kind of proces. The idea is to create my cl so that i includes the proces of adding an entry to the jobcheduler. Here is a samle solution for your task: 1. add an entry to the jobscheduler to initiate te proces in the morning:
    ADDJOBSCDE JOB(myjob) CMD(mylib/mycmd) FRQ(*WEEKLY) SCDDATE(*NONE)  +  
     SCDDAY(*MON *TUE *WED *THU *FRI) SCDTIME(070000)
    
    As can be seen, have put my proces to execution at seven o'clock all working-days. The cl behinf my command -mycmd- is this
                 PGM
    
                 DCL        VAR(&CMD)      TYPE(*CHAR) LEN(256)
                 DCL        VAR(&TIME)     TYPE(*CHAR) LEN(6)
                 DCL        VAR(&HOUR)     TYPE(*CHAR) LEN(2)
                 DCL        VAR(&MINUTE)   TYPE(*CHAR) LEN(2)
                 DCL        VAR(&ENDHOUR)  TYPE(*DEC)  LEN(2 0) VALUE(19) /*01->23*/ 
                 DCL        VAR(&INTERVAL) TYPE(*DEC)  LEN(2 0) VALUE(10) /*01->59*/ 
                 DCL        VAR(&WHOUR)    TYPE(*DEC)  LEN(2 0)
                 DCL        VAR(&WMINUTE)  TYPE(*DEC)  LEN(2 0)
                 DCL        VAR(&DUMMY)    TYPE(*DEC)  LEN(3 0)
    
                 /* what is the time ? */
                 RTVSYSVAL  SYSVAL(QTIME)   RTNVAR(&TIME)
                 RTVSYSVAL  SYSVAL(QHOUR)   RTNVAR(&HOUR)
                 RTVSYSVAL  SYSVAL(QMINUTE) RTNVAR(&MINUTE)
    
                 /* make numeric */
                 CHGVAR     VAR(&WHOUR)   VALUE(&HOUR)
                 CHGVAR     VAR(&WMINUTE) VALUE(&MINUTE)
    
                 /* Stop if time has passed end-time */
                 IF         COND(&WHOUR > &ENDHOUR) THEN(RETURN)
      
                 /* Prepare values for calculation of next execution */
                 CHGVAR     VAR(&DUMMY) VALUE(&WMINUTE + &INTERVAL)
                 IF         COND(&DUMMY > 59) THEN(DO)
                   CHGVAR     VAR(&WHOUR) VALUE(&WHOUR + 1)
                   CHGVAR     VAR(&WMINUTE) VALUE(&DUMMY - 60)
                   ENDDO
                 ELSE       CMD(CHGVAR VAR(&WMINUTE) VALUE(&WMINUTE + &INTERVAL))
     
                 /* Calculate scheduled time for next execution */
                 CHGVAR     VAR(&HOUR) VALUE(&WHOUR)
                 CHGVAR     VAR(&MINUTE) VALUE(&WMINUTE) 
                 CHGVAR     VAR(&TIME) VALUE(&HOUR *TCAT &MINUTE *TCAT '01')
               
                 /* Put a scheduled job into the job-scheduler */ 
                 ADDJOBSCDE JOB(myjob) CMD(mylib/mycmd) FRQ(*ONCE) +
                              SCDDATE(*CURRENT) SCDTIME(&TIME)
    
                 /* Now we are ready for execution of our CL's */
     PROCES: ....[insert your RUNQRY..-statements to process here]
    
    ENDPGM:      ENDPGM
    
    The cl simply looks at current time, adds the interval (10 minues) and creates an entry into the jobscheduler for next pass, if time has not passed the processing-periodsend-time (&endhour is set to 19 in the dcl stmt ). Create a cmd to initiate the above cl. Voila - thats all there is to it. DanF
    2,555 pointsBadges:
    report
  • Splat
    I was faced with a similar issue and decided to make the job self-submitting - when processing is complete the current date/time is retrieved, incremented by the appropriate interval, and submitted with a new SCDDATE/SCDTIME.
    6,815 pointsBadges:
    report
  • TomLiotta
    Here's how a data queue wrapper for the process might look:
    pgm
    
    
       dcl   &dtaqe       *char   10     value( ' ' )
       dcl   &lendtaqe    *dec (   5 0 ) value( 0 )
       dcl   &timWait     *dec (   5 0 ) value( 60 )
    
    
       dountil ( &lendtaqe *gt 0 *and &dtaqe *eq '*ENDJOB' )
    
          sndmsg  'Running query process here...'  tousr( *REQUESTER )
    
          call    QRCVDTAQ   ( +
                               'TSTDLYLOOP'           +
                               '*LIBL     '           +
                               &lendtaqe              +
                               &dtaqe                 +
                               &timWait               +
                             )
    
       enddo
    
       sndmsg     'Running *ENDJOB'  tousr( *REQUESTER )
    
       return
    
    endpgm
    The "query" would be inserted where the first SNDMSG is coded. The loop is controlled by whatever result comes back from the attempt to receive a data queue entry. When the length of the returned data queue entry is greater than zero and the value of the returned entry is "*ENDJOB", the loop terminates. A zero-length happens when the timeout results from no entry on the data queue. If there was no entry, the "query" (sndmsg in this case) is executed again. The data queue might be created this way:
    CRTDTAQ DTAQ(TSTDLYLOOP)
            MAXLEN(10)
            AUTORCL(*YES)
    The data queue name and library will be whatever you need as long as it matches what's in the program. The maximum entry length in the example is 10 bytes. The data queue is defined to reclaim any extra space that might accumulate from some large number of entries that some unknown process might put on the queue. (Probably not needed, but you never know what you'll want the queue to do next month or next year.) The only other piece is sending an entry that tells the job to end:
    call QSNDDTAQ   ( 'TSTDLYLOOP'  
                      '*LIBL     '  
                      x'00010F'     
                      '*ENDJOB   ' )
    If that was in a CL program (or if a *CMD was created for it), the third parm would be a *DEC ( 5 0 ) with a value of 10. That simply tells how many bytes of the next parm should be put on the queue. The value could be 7 if you only wanted those seven characters to be sent. You can create the data queue and send the first entry immediately. Then call the example program. The program should only be in the loop once because it will find the "*ENDJOB" entry as soon as it looks into the queue. If you then call the program again, there won't be an entry still queued up -- the wait-time kicks in. If you wait a minute, you'll see that the loop happens a second time. It will happen every 60 seconds because that's how many seconds were coded for the wait-time parm. You'd use a value of 600 if you wanted ten minutes. But as soon as you send another "*ENDJOB" entry, the program responds. Since an "*ENDJOB" entry that is already queued will cause an immediate end of job, you might want to make sure the queue is cleared before doing any work. Adding that piece is easy. The result then looks like:
    pgm
    
    
       dcl   &dtaqe       *char   10     value( ' ' )
       dcl   &lendtaqe    *dec (   5 0 ) value( 0 )
       dcl   &timWait     *dec (   5 0 ) value( 60 )
    
    
       call       QCLRDTAQ   ( +
                               'TSTDLYLOOP'           +
                               '*LIBL     '           +
                             )
    
       dountil ( &lendtaqe *gt 0 *and &dtaqe *eq '*ENDJOB' )
    
          sndmsg  'Running query process here...'  tousr( *REQUESTER )
    
          call    QRCVDTAQ   ( +
                               'TSTDLYLOOP'           +
                               '*LIBL     '           +
                               &lendtaqe              +
                               &dtaqe                 +
                               &timWait               +
                             )
    
       enddo
    
       sndmsg     'Running *ENDJOB'  tousr( *REQUESTER )
    
       return
    
    endpgm
    That clears the queue right off the bat. The loop will then respond only to entries that were put on the queue after the program started. Note that you can still run a normal ENDJOB command to end it. It's still a regular job after all. You could have this started by a job scheduler entry if you wanted it to start looping at some time of day. You could have another job scheduler entry that sent the "*ENDJOB" data queue entry if you wanted it to end at some time of day. Have the looping job submitted to job queue QUSRNOMAX (or QSYSNOMAX) to keep it out of the way of any other jobs. If you need it to start after IPL, then add an autostart job entry to QCTL or to whatever subsystem starts up when you think your job should start. Lots of alternative ways to get things done. Tom
    125,585 pointsBadges:
    report
  • DanTheDane
    Tom, As I read your dtaq-code, the execution of the query-proces, will simply repeat jus after having ended. Thus, if processing time is fx. 4 minutes, you will not get the originally requested timeinterval of 10 minutes. Am I reading wrong ? DanF
    2,555 pointsBadges:
    report
  • Batman47
    We have a query that starts running at 8:00 each day. Loops every 10 seconds and ends by the program checking for the time of day (180000).
    1,050 pointsBadges:
    report
  • TomLiotta
    if processing time is fx. 4 minutes, you will not get the originally requested timeinterval of 10 minutes. If processing time is important and significant in length, then the "query" process simply becomes a SBMJOB. I'd mostly expect it to be a SBMJOB in any case. The control code in the example has a different function than any process that it controls. A separation of function is how modularization is achieved. With a little more thought, it might be seen that the example should be generalized into a complete "timer" app that could control as many different processes as desired on different periods. What it really should be doing is sending "heartbeats" on a schedule to waiting apps. For the single "query" in this question, the "timer" app would send an entry to the "query's" data queue once every ten minutes. The "query" app would wait on its data queue with no timeout at all; it should simply do its job whenever a "heartbeat" arrives. If the query takes four minutes, it doesn't matter. The next "heartbeat" will arrive six minutes after the query finishes. If the query takes one minute or nine minutes, the next "heartbeat" will be nine minutes or one minute later. Now, if it all must be in the same app, then the wait-time can simply 600 seconds minus the time that the query took to execute. The CEE* date/time APIs make time durations easy if that degree of precision is needed. I'm not clear if a four-minute query is appropriate to be running every ten minutes on most production systems. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following