MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Stored procedure on Federated thru DB2

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
arif677@gmail.com
Beginner


Joined: 31 May 2016
Posts: 7
Topics: 1

PostPosted: Wed Jun 01, 2016 10:58 am    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 from COBOL-DB2 program is through federated server and source tables are on Oracle. We were able to access all the tables on Oracle through federated server. The program runs even and updates the tables on Federated but stored procedure call is failing at bind time 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.

If you have any experience working with remote tables on Federated server... please help me. Thank you.
Back to top
View user's profile Send private message AIM Address
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Jun 01, 2016 11:43 am    Post subject: Re: Stored procedure on Federated thru DB2 Reply with quote

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


Arif,

A -440 error sql code mean that you have an incorrect routine name or schema name.

Check this link for the explanation of -440 error sql code

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/n440.html


Did you check if the procedure is listed in the catalog table sysibm.sysroutines? Did you have schema listed in the bind cards for the stored procedure? Show us the bind control cards.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
arif677@gmail.com
Beginner


Joined: 31 May 2016
Posts: 7
Topics: 1

PostPosted: Wed Jun 01, 2016 1:00 pm    Post subject: Reply with quote

Thanks for the prompt reply... Let me provide some more details how we are compile and bind.

As we are accessing the tables and procedures on the remote server (Federated) which is not mainframes, We have to add the remote location to the SQL as below:

SELECT * FROM REM_LOC.SCHEMA.TABLE_NAME

We bind the program for the package once under local DB2 location and remote location.

DSN SYSTEM(DSNT)
BIND PACKAGE(COL1) MEMBER(PGMA) -
ACTION(REPLACE) -
ISOLATION(CS) -
DEGREE(ANY) -
VALIDATE(BIND)

BIND PACKAGE(REMLOC.COL1) MEMBER(PGMB) -
ACTION(REPLACE) -
ISOLATION(CS) -
DEGREE(ANY) -
VALIDATE(BIND)

This scenario is working fine with the tables and views. We can update tables and views.

We are having issues with the remote procedures only. We call the stored procedure as follows:
CALL REM_LOC.SCHEMA.STORE_PROCEDURE(:HOST VARIABLES.....)

As we are using the qualified names, do we need to include the schema name in the bind parameter...? I have tried using the PATH(REMOTE_SCHEMA) and it did not work.

Any help is appreciated.
Back to top
View user's profile Send private message AIM Address
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Jun 01, 2016 1:12 pm    Post subject: Reply with quote

arif677@gmail.com wrote:

SELECT * FROM REM_LOC.SCHEMA.TABLE_NAME

I have tried using the PATH(REMOTE_SCHEMA) and it did not work.

Any help is appreciated.


Arif,

Shouldn't the path be REM_LOC.Schema instead of REMOTE_SCHEMA?

Also check the schema listed in the sysibm.sysroutines.

Code:

SELECT * FROM SYSIBM.SYSROUTINES
WHERE NAME = 'your sp name' ;

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu


Last edited by kolusu on Wed Jun 01, 2016 1:13 pm; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
arif677@gmail.com
Beginner


Joined: 31 May 2016
Posts: 7
Topics: 1

PostPosted: Wed Jun 01, 2016 1:16 pm    Post subject: Reply with quote

I have checked the schema from sysibm.sysroutines and it is correct. I think the PATH(SCHMA_NAME) but I have tried PATH(REM_LOC) and unfortunately it did not work.
Back to top
View user's profile Send private message AIM Address
arif677@gmail.com
Beginner


Joined: 31 May 2016
Posts: 7
Topics: 1

PostPosted: Wed Jun 01, 2016 1:22 pm    Post subject: Reply with quote

Kolusu, I have tried binding the program with PATH(REM_LOC.SCHMA_NAME) and it did not work.
Back to top
View user's profile Send private message AIM Address
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Wed Jun 01, 2016 1:36 pm    Post subject: Reply with quote

arif677@gmail.com,


When you got the sqlcode of -440, it also would have shown you the statement number of the sql which is the cause of the error. what is that statement?
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
arif677@gmail.com
Beginner


Joined: 31 May 2016
Posts: 7
Topics: 1

PostPosted: Wed Jun 01, 2016 1:45 pm    Post subject: Reply with quote

Here is the error message:
SQLCODE = -440
SQLSTATE = 42884
SQLERRMT = REM_LOC.REM_SCHEMA.PROCEDURE_NAME PROCEDURE
SQLWARN 0= ,1= ,2= ,3= ,4= ,5= ,6= ,7= ,8= ,9= ,A=
STATEMENT= 151
Back to top
View user's profile Send private message AIM Address
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

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

arif677@gmail.com wrote:
Here is the error message:
SQLCODE = -440
SQLSTATE = 42884
SQLERRMT = REM_LOC.REM_SCHEMA.PROCEDURE_NAME PROCEDURE
SQLWARN 0= ,1= ,2= ,3= ,4= ,5= ,6= ,7= ,8= ,9= ,A=
STATEMENT= 151


Look at the compiler listing for the statement 151 and see if you have specified the table name correctly.

Try removing the schema_name in the procedure as your bind should automatically find it based on the path.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
arif677@gmail.com
Beginner


Joined: 31 May 2016
Posts: 7
Topics: 1

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

Hi Kolusu, The statement 151 points to stored procedure and the name is spelled correctly. Also I have tried removing the schema in the procedure and it give the same error.
Back to top
View user's profile Send private message AIM Address
arif677@gmail.com
Beginner


Joined: 31 May 2016
Posts: 7
Topics: 1

PostPosted: Wed Jun 01, 2016 5:58 pm    Post subject: Reply with quote

Kolusu,

The issue has been resolved now.. We had to give the following:
Code:

CONNECT TO REM_LOC
CALL SCHEMA_NAME.STORED_PROCED (:HOST-VARIABLES.....)

Also, when binding the program use the option VALIDATE(RUN) for the local DB2 location.

We were able to run the program now.... Thanks for providing valuable suggestions....
Back to top
View user's profile Send private message AIM Address
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group