MVSFORUMS.com

MFdigger · Posted: Thu Feb 19, 2009 1:58 pm Post subject: Stored proc - thru JCL?

Hi All,

Can I execute a stored procedure thru the JCL? Please clarify

Thank you
_________________
Tx
Digger

jsharon1248 · Posted: Thu Feb 19, 2009 2:32 pm Post subject:

I suppose that depends. If your stored proc has no SQL, in theory, I suppose you could code a JCL EXEC to execute it. If there's any SQL, you need to establish a DB2 connection somehow first, usually IKJEFTxx.

rk_pulikonda · Posted: Thu Feb 19, 2009 11:43 pm Post subject:

Do you want to execute the Stored Procedure directly or through another program ?

If it is through another program we can call.

Here is an exmple.

1. Working section Declaration.

01 PGM-LOC1 USAGE SQL
TYPE IS RESULT-SET-LOCATOR VARYING.

2. Call the Stored procedure

EXEC SQL
CALL :STOR-PROC (:PARM1,
:PARM2,
:PARM3,
:PARM4
)
END-EXEC

3. Using ASSOCIATE command point the locator to the result set.

EXEC SQL
ASSOCIATE LOCATOR (:PGM-LOC1)
WITH PROCEDURE :STOR-PROC
END-EXEC

4. Using ALLOCATE command attach the locator to the cursor.

EXEC SQL
ALLOCATE PGM_CSR1 CURSOR
FOR RESULT SET :PGM-LOC1
END-EXEC

5. The result set is attached to cursor PGM_CSR1 .
_________________
Thanks
-Ram

MFdigger · Posted: Fri Feb 20, 2009 8:42 am Post subject:

Hi Ram,

Thank you very much for the details, I never know that there is some thing called ALLOCATE command and we can use the STORED PROCEDURE as you mentioned above.

Can it be possible to run the stored procedure directly from the JCL, just like we execute any other program?

Thank you Rolling Eyes

_________________
Tx
Digger

jsharon1248 · Posted: Fri Feb 20, 2009 10:55 am Post subject:

MFdigger

Set it up and try it in your test environment. See what happens. But think about this. If you're executing SQL statements, won't you need a connection to DB2? Do you get a connection to DB2 just by coding EXEC PGM=<pgm> in JCL? How do you normally establish a connection to DB2? If your stored proc returns a results set, what is going to receive it? The job?

Stored procs are integrated into the DB. They're designed to be called and executed within the DB. You can get creative and try to execute them outside the DB, but you need to have some really good reasons to do that. And if you're executing the stored procs outside the DB engine, why do you need a stored proc in the first place?

rk_pulikonda · Posted: Fri Feb 20, 2009 6:44 pm Post subject:

MFdigger

One small question ?

What is the difference between stored procedure written in COBOL accessing DB2 tables and normal COBOL program accessing DB2 tables..?

Just check the below link for any help.
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNITH11/2.3.8?SHELF=DSNSHHA1&DT=20010710161352
_________________
Thanks
-Ram