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
 

 

Stored procedure on Federated thru DB2

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
arif677

New User


Joined: 22 Jun 2005
Posts: 11

PostPosted: Tue May 31, 2016 11:48 pm    Post subject: Stored procedure on Federated thru DB2
Reply with quote

We have been trying to call a stored procedure through COBOL-DB2 program and its failing at bind time with SQLCODE = -440, SQLSTATE = 42884.

The stored procedure we are calling is through federated server and source tables are on Oracle. We were able to access all the tables on Oracle through federated server but the program fails at bind time only for stored procedures with the above sqlcode. We have checked the access privileges and we have all the privileges to execute the procedure.

I have gone through the ibm documents, and could not able to find an answer. We were able to bind,run cobol-db2 and update the tables thru federated server and we have issues only with stored procedures.

If you have any experience working with remote tables... please help me. Thank you.
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Wed Jun 01, 2016 2:01 am    Post subject:
Reply with quote

Did you verify this checklist?
Code:
•The routine name was either incorrectly specified or does not exist in the database.
•A qualified reference was used, and the schema qualifier was incorrectly specified.
•The user's SQL path does not contain the schema to which the desired function belongs, and an unqualified reference was used.
•The wrong number of arguments was included.
•The right number of arguments was included in the routine reference, but the data type of one or more of the arguments is incorrect.
•The invoker of the routine is not authorized to execute the routine.
Back to top
View user's profile Send private message
arif677

New User


Joined: 22 Jun 2005
Posts: 11

PostPosted: Wed Jun 01, 2016 3:39 am    Post subject: Reply to: Stored procedure on Federated thru DB2
Reply with quote

Hi Rohit, Thanks for your response..Yes.. I have double checked. The program works well with remote tables. For remote tables we give the select statement as follows and we bind program for a package under local DB2 location and remote DB2 location.

SELECT * FROM REM_LOC.SCHEMA.TABLE_NAME.

As the stored procedure doesn't exist on the local DB2 location, call syntax is as follows:

CALL REM_LOC.SCHEMA.STORE_PROCEDURE (:Host Variables....)

And we try to bind under local DB2 location and remote DB2 location, the bind fails at remote DB2 location.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Wed Jun 01, 2016 11:10 am    Post subject:
Reply with quote

To be very honest , if I were you then first I would involve DBA's and tell them what is tried so far and then proceed further so did you do that yet ? Also check if referential integrity is still intact or corrupted during the bind.
Back to top
View user's profile Send private message
arif677

New User


Joined: 22 Jun 2005
Posts: 11

PostPosted: Wed Jun 01, 2016 7:32 pm    Post subject: Reply to: Stored procedure on Federated thru DB2
Reply with quote

Thanks Rohit for your time on this. Yes DBAs aware of this and they are working on this issue too...We are moving from z/VSE to MVS z/OS setting up new environment, so they have less knowledge on z/OS..... I think we may need to involve IBM system guys to resolve this... I will update what the solution was...Thanks.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Wed Jun 01, 2016 10:24 pm    Post subject:
Reply with quote

Quote:
I think we may need to involve IBM system guys to resolve this
yes you can always raise a ticket if none could get clue at your shop of what went wrong and please post back the solution to gets other benefited with the same situation.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1713
Location: UK

PostPosted: Thu Jun 02, 2016 1:23 am    Post subject:
Reply with quote

Locked as posted on another forum.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    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 Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Is possible to call subprogram from c... cmsmoon COBOL Programming 3 Fri Aug 05, 2016 6:46 pm
No new posts Mainframe COBOL - DB2 Stored Procedure Elixir DB2 2 Sat Mar 19, 2016 9:39 am
No new posts Exception Handling in Native Stored P... Mariraj DB2 1 Thu Feb 25, 2016 4:08 am


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