Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

COBOL/DB2 Stored Procedure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
mad_guy
Currently Banned

New User


Joined: 15 Jan 2010
Posts: 31
Location: India/UK

PostPosted: Fri Jan 15, 2010 11:37 pm    Post subject: COBOL/DB2 Stored Procedure
Reply with quote

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?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jan 16, 2010 12:44 am    Post subject:
Reply with quote

Hello,

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

http://ibmmainframes.com/viewtopic.php?t=44950
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Mon Jan 18, 2010 4:18 pm    Post subject: Hi
Reply with quote

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.
Back to top
View user's profile Send private message
mad_guy
Currently Banned

New User


Joined: 15 Jan 2010
Posts: 31
Location: India/UK

PostPosted: Wed Jan 27, 2010 8:50 pm    Post subject: Reply to: COBOL/DB2 Stored Procedure
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 27, 2010 9:35 pm    Post subject:
Reply with quote

Hello,

You need to talk with your manager or the security people to have your id given the needed permission(s).
Back to top
View user's profile Send private message
mad_guy
Currently Banned

New User


Joined: 15 Jan 2010
Posts: 31
Location: India/UK

PostPosted: Wed Jan 27, 2010 9:52 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 27, 2010 10:05 pm    Post subject:
Reply with quote

Hello,

Quote:
I have been given the permission to the particular db2 region and create privilege,
Either the permissions granted were not complete or you are specifying something that you do not have permission to access.

You need to resolve the permission problem(s) and only someone on your system can help with this. . .
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 662
Location: Pennsylvania

PostPosted: Thu Jan 28, 2010 2:03 am    Post subject:
Reply with quote

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....
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Thu Jan 28, 2010 5:52 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
mad_guy
Currently Banned

New User


Joined: 15 Jan 2010
Posts: 31
Location: India/UK

PostPosted: Fri Jan 29, 2010 3:15 pm    Post subject:
Reply with quote

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....
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 29, 2010 8:09 pm    Post subject: Reply to: COBOL/DB2 Stored Procedure
Reply with quote

Good to hear there is progress icon_smile.gif

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

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts RULES(NOEVENPACK) in cobol jackzhang75 COBOL Programming 5 Wed Mar 29, 2017 12:47 am
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Execute TSO ACF commands from COBOL-I... supreethi.srid TSO/ISPF 0 Tue Feb 21, 2017 5:19 pm
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts Mainframe Interview Questions CICS,CO... akshathan Mainframe Interview Questions 6 Thu Feb 09, 2017 6:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us