Re: How to setup schedule job for a stored procedure

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.