Re: How to setup schedule job for a stored procedure

Thanks Jim and Rob! I have figured it out. Here is my summary.

1. Create a SQL source file (only need to create once), which is used
to accommodate source members

CRTSRCPF FILE(MYLIB/QSQLSRC) TEXT('SQL scripts')
2. Create a SQL source member by using STRSEU, in which SQL
statements can be run. For example, "CALL MYLIB.MYSTOREDPROCEDURE(p1,p2)".
Here, the MYSTOREDPROCEDURE is the sp I want to schedule as a job.

3. Use WRKJOBSCDE/ADDJOBSCDE command to add a new job

ADDJOBSCDE CMD(RUNSQLSTM SRCFILE(IDIGPROC/QSQLSRC) SRCMBR(aSRCmember))


thanks,
yan


On Thu, Jul 9, 2009 at 3:56 PM, <rob@xxxxxxxxx> wrote:

These two commands are on every system:
CRTSRCPF FILE(MYLIB/SQLSRC)
EDTF FILE(MYLIB/SQLSRC) MBR(CALLMOFO)


4 minutes until VACATION!!!!

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 Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:
07/08/2009 04:33 PM
Subject:
Re: How to setup schedule job for a stored procedure
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



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
--