Re: How to setup schedule job for a stored procedure

Yan,

Rob is referring to a source physical file on the IBM i created with the
command CRTSRCPF called SQLSRC in library MYLIB. You can name the file
anything you want, but SQLSRC makes sense. You can also put the file in any
library you would like. MYLIB is just an example. Use an edit utility like
SEU to create a source member called CALLMOFO (or a name of your choice that
makes sense to you) and put the SQL statement required within the source.
Rob is suggesting "CALL MYLIB.MYSTOREDPROCEDURE" to invoke your stored
procedure.

Does that help?

Jim


On Wed, Jul 8, 2009 at 2:21 PM, Yan Zhang <yzhtech@xxxxxxxxx> wrote:

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

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