On 4/30/2014 4:21 PM, Stone, Joel wrote:
Can you please point me in the right direction for creating and calling a stored procedure on a remote iseries?
I read thru much of
http://www.redbooks.ibm.com/redbooks/SG246503/wwhelp/wwhimpl/js/html/wwhelp.htm
and it is quite complex.
A Redbook from 2004. With iSeries in the title. Not my first choice for
new production code, but different strokes. Chapter 6, external stored
procedures, is where you want to focus.
The cool thing about DB2 for i is that a stored procedure can be written
in SQL (like other platforms) or in an HLL like RPG. If you were to
choose an HLL, you'd write the program in accordance with the parameter
style you chose (6.3) and then register the program with DB2 via the SQL
DLL command CREATE PROCEDURE.
Parameter styles are introduced in 6.2. For your application, General
should be fine. The impact that parameter styles have on your external
(meaning external to DB2) program code is that DB2 needs to somehow
execute the stored procedure and pass parameters back and forth. The
exact layout of those parameter lists depends on the parameter type. If
you need to pass NULL database columns, you probably need the SQL
parameter type, but otherwise, General shoud work fine for you.
The easiest way to create a stored procedure on a remote machine is to
create it on the local machine and either move the source to the remote
and re-create (my choice) or to save and restore.
In order to run a TAATOOL command on a remote iseries, I am thinking that the Stored Procedure must be CL.
Not really. You can run a CL command from any language. In RPG you
could use QCMDEXEC or system(). You can even call a CL command from SQL
if you choose.
The redbook doesn't mention much of CL based stored procedures.
It sounds like I want to use an "External" stored procedure, as that supports CL.
Yes, you want an external stored procedure. CL will probably be the
simplest for your application.
Will an external stored procedure support the remote system calling of TAATOOL command CHKACTJOB and return a "yes" or "no" flag?
For example a job is running on SYSA and I would like to detect if APACHEDFT1 job is running on remote system SYSB.
Generally speaking, a stored procedure on SYSA isn't going to be able to
reach into another system and execute commands. So the way I'd approach
this is to create a stored procedure on SYSB. Then from SYSA, CONNECT
TO SYSB and CALL ISAPACHEUP (your stored proc).
Will this do the trick?
Any examples?
So the oldest useful reference for CREATE PROCEDURE is probably 5.4:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fdb2%2Frbafzmstcrtpef.htm
CLP SP_CLP:
pgm (&returnval)
dcl &returnval *char 8
rtvsysval qsrlnbr &returnval
endpgm
CREATE PROCEDURE
BUCK/SP_CLP(OUT srlnbr CHAR (8 ))
LANGUAGE CL
DETERMINISTIC NO SQL
CALLED ON NULL INPUT
EXTERNAL NAME BUCK/SP_CLP
PARAMETER STYLE GENERAL;
call buck.sp_clp (' '); // returns my serial number
This isn't your exact need; I don't have TAATOOL on my machine to test
with. But it is an example of an external SP written in CL that
executes a command and returns a result to the caller.
The key here is the parameter list. GENERAL passes only the input and
output parameters between DB2 and the external program. Look at the SQL
CREATE PROCEDURE statement. There is only one parameter, and it's
declared OUT. So the parameter list has zero input parameters and one
output parameter. GENERAL isn't concerned with NULL values, so the only
thing the CLP needs for parameters is the output parameter.
Hope this gets you started.
--buck
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, April 30, 2014 12:12 PM
To: Midrange Systems Technical Discussion
Subject: Re: How to check if a specific job is running on a different iSeries box
Call a stored procedure which executes something on the remote system and
returns an aye or nay.
Rob Berendt
midrange.com
