COBOL/DB2 Stored Procedure

mad_guy · New User Joined: 15 Jan 2010 Posts: 31 Location: India/UK

My requirements are:

java front end will call the stored procedure and the COBOL-DB2 stored procedure will select (more than 10000 rows), insert ,update or delete in DB2 table.

I have some basic questions:

1) How DB2 stored procedure will get linked with the cobol program?
2) If I want to run the whole process on the mainframe only, how to do?
3) How to fetch the multiple records by stored procedure and COBOL program? In the case of running the whole process on the mainframe where to get the output?
4) How to call? Do I need to call the stored procedure, and then that will call the corresponding COBOL program? or else how?

dick scherrer · Posted: Sat Jan 16, 2010 12:44 am

Hello,

See if the SP links in this topic (provided by DBZ) help you:

ibmmainframes.com/viewtopic.php?t=44950

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

1)How DB2 stored procedure will get linked with the cobol program?

This is possible.
U should create a DB2 stored procedure, this DB2 procedure will have the create procedure ddl statements along with the input and output parameters declared. This procedure should intern call a COBOL program.In my shop we used to call it SL1 and SPS components. Ths SL1 component will have the declarations, which will be internally passing the values to the SPS component, which is actually a COBOL-DB2 program.
Language should be COBOL instead of SQL

2)If I want to run the whole process on the mainframe only, how to do?

If you want to run the whole process means, do u want to test the COBOL-DB2 stored procedure? If so we have tools like Stored Procedure builder. Else you can even check the same by coding a cobol program which internally calls the stored procedure. You should use Allocate and associate cursors to fetch the results.

3)How to fetch the multiple records by stored procedure and COBOL program? In the case of running the whole process on the mainframe where to get the output?

In case you are trying to display them in the Web screens, then as I said before you have to code the SL1, SPS components. In the SPS you have to open a cursor and leave the same open.No need to fetch. Now from your web technology , you need to get the resultsets and show them in the screen. If you are trying to show thenm in the cobol sysouts, you have to use allocate and associate cursors and iterate them using the cobol program and display in sysout or write them into a file.

4)How to call? Do I need to call the stored procedure, and then that will call the corresponding COBOL program? or else how?

As of I know, you just need to call the stored procedure(SL1). That internally will invoke the SPS component. Both SL1 and SPS will have the same name.

mad_guy · New User Joined: 15 Jan 2010 Posts: 31 Location: India/UK

Hi... how do I call a store proc from another batch cobol program in mainframe?
I have staged the store proc source (in my shop its called SRR) in CHANGEMAN... and created the store proc in DB2 region.
Now I am trying to call the store proc from another cobol progrm:- like this:-
EXEC SQL
CALL ADDD5.ADDD5UPD.SKL_CANCEL_AUDIT( :WS-AUD-ID
,:WS-RETURN-TYPE
,:WS-ERROR-CODE
,:WS-ERROR-DESC
)
END-EXEC.

but I m getting -922 (authorisation error)
in JCL i m only mentioning the calling cobol program like this:--
//SYSTSIN DD *
DSN SYSTEM(DSND)
RUN PROGRAM(CALLSTP) PLAN(ADDD5BAT)
END
/*
but I m getting -922 (authorisation error)
Pls help.

dick scherrer · Posted: Wed Jan 27, 2010 9:35 pm

Hello,

You need to talk with your manager or the security people to have your id given the needed permission(s).

mad_guy · New User Joined: 15 Jan 2010 Posts: 31 Location: India/UK

I have been given the permission to the particular db2 region and create privilege, but still getting the error.
Could you pls tell me the procedure to execute a store proc calling from batch COBOL?

dick scherrer · Posted: Wed Jan 27, 2010 10:05 pm

Hello,

daveporcelan · Posted: Thu Jan 28, 2010 2:03 am

Once you get your permission worked out...

In our shop, the actual logic of the SP is nothing more than a Cobol-DB2 program.

The creation of the SP within the DB2 region (workoad manager) is for non-mainframe access.

If we want to call the same logic from the mainframe, we simply call it like any other cobol-db2 subroutine.

I hope this helps you out....

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Hi mad_guy,

Can you please let us know the steps you followed after creating the procedure. Have you registered the stored Procedure, ie did u execute the create procedure statement. Also the GRANT access should be given for the procedure.

Just give a try calling the procedure like

EXEC SQL
CALL SYSPROC.<Stored Procedure Name>( :WS-AUD-ID
,:WS-RETURN-TYPE
,:WS-ERROR-CODE
,:WS-ERROR-DESC
)
END-EXEC.

mad_guy · New User Joined: 15 Jan 2010 Posts: 31 Location: India/UK

Thanks every one..
Finally.. I am able to see the store proc running...from another batch cobol...
now..lets see..how its work..while java invoke the stored proc..and reurn result sets...
java team is not ready yet...so..have enough time..
Thanks again...to dick scherrer,ajeshrn, daveporcelan...
I will be needing ur help..in near future also....

dick scherrer · Posted: Fri Jan 29, 2010 8:09 pm

Good to hear there is progress

Someone will be here when there are more "opportunities". . .

d