How to setup schedule job for a stored procedure

[Yan Zhang <yzhtech@xxxxxxxxx>]

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