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
midrange.com
