× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.