Re: How to setup schedule job for a stored procedure

[Yan Zhang <yzhtech@xxxxxxxxx>]

Rob,

Do you know how to create MYLIB/SQLSRC(CALLMOFO)? I am a more SQL person and
only know very limited knowledge about green screen cmmands.


thanks,
yan

On Tue, Jul 7, 2009 at 9:56 AM, <rob@xxxxxxxxx> wrote:

> CALL from the command line, CL or ADDJOBSCDE is only usable for programs.
> You're trying to call a stored procedure.  You must use something like
> ADDJOBSCDE CMD(RUNSQLSTM SRCFILE(MYLIB/SQLSRC) SRCMBR(CALLMOFO)
>
> And the contents of MYLIB/SQLSRC(CALLMOFO) may just have
> CALL MYLIB.MYSTOREDPROCEDURE
>
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:
> Yan Zhang <yzhtech@xxxxxxxxx>
> To:
> midrange-l@xxxxxxxxxxxx
> Date:
> 07/07/2009 09:43 AM
> Subject:
> How to setup schedule job for a stored procedure
> Sent by:
> midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> Vesion: V6R1 or V5R4
>
> Problem: I created a stored procedure by using SQL. I want to schedule it
> as
> a job, so that it can be run automatically, say,  every Monday 8pm.
>
> I found some information from following link:
>
> http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/cl/addjobscde.htm
>
>
> My SQL stored procedure code is:
> -------------------------------------------------------
> CREATE PROCEDURE aLibName/aStoredProcName (
>    IN TOSCHEMA VARCHAR(100) ,
>    IN FROMSCHEMA VARCHAR(100) )
>    LANGUAGE SQL
>    SPECIFIC aLibName/aStoredProcName
>    NOT DETERMINISTIC
>    MODIFIES SQL DATA
>    CALLED ON NULL INPUT
>    SET OPTION  ALWBLK = *ALLREAD ,
>    ALWCPYDTA = *OPTIMIZE ,
>    COMMIT = *NONE ,
>    DBGVIEW = *LIST ,
>    DECRESULT = (31, 31, 00) ,
>    DFTRDBCOL = *NONE ,
>    DLYPRP = *NO ,
>    DYNDFTCOL = *NO ,
>    DYNUSRPRF = *USER ,
>    RDBCNNMTH = *RUW ,
>    SRTSEQ = *HEX
>    BEGIN
>
>
> DECLARE SQLSTMT VARCHAR ( 10000 ) ;
>
> -- 1) Clear temp sales table
> SET SQLSTMT = 'delete from ' || TOSCHEMA || '.TMPSALES' ;
> EXECUTE IMMEDIATE SQLSTMT ;
>
> INSERT INTO
> aLibName / DEBUGTABLE
> VALUES ( 'temp sales table cleared' , '' , CURRENT TIMESTAMP ) ;
>
> END  ;
> ------------------------------------------------------------------
>
> This is what I did:
>
> CL: ADDJOBSCDE JOB(aJobName) SCDDATE(*NONE)
>      CMD(CALL PGM(aLibName/aStoredProcName) PARM(parameter1, parameter2))
>      SCDDAY(*WED) SCDTIME('11:30:00')
>      FRQ(*WEEKLY) RCYACN(*NOSBM)
>
>
> This job has been successfully scheduled.
>
> But when the job runs, it generates an error.
>
> If I run the stored procedure manually, it runs good. The content of this
> sp
> is to delete all the records from a table.
>
> Could someone give me some hint?
>
> many thanks,
> yan
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.