View previous topic :: View next topic |
Author |
Message |
arif677@gmail.com Beginner
Joined: 31 May 2016 Posts: 7 Topics: 1
|
Posted: Wed Jun 01, 2016 10:58 am Post subject: Stored procedure on Federated thru DB2 |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
|
Posted: Wed Jun 01, 2016 11:43 am Post subject: Re: Stored procedure on Federated thru DB2 |
|
|
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 |
|
|
arif677@gmail.com Beginner
Joined: 31 May 2016 Posts: 7 Topics: 1
|
Posted: Wed Jun 01, 2016 1:00 pm Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
|
Posted: Wed Jun 01, 2016 1:12 pm Post subject: |
|
|
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 |
|
|
arif677@gmail.com Beginner
Joined: 31 May 2016 Posts: 7 Topics: 1
|
Posted: Wed Jun 01, 2016 1:16 pm Post subject: |
|
|
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 |
|
|
arif677@gmail.com Beginner
Joined: 31 May 2016 Posts: 7 Topics: 1
|
Posted: Wed Jun 01, 2016 1:22 pm Post subject: |
|
|
Kolusu, I have tried binding the program with PATH(REM_LOC.SCHMA_NAME) and it did not work. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
|
Posted: Wed Jun 01, 2016 1:36 pm Post subject: |
|
|
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 |
|
|
arif677@gmail.com Beginner
Joined: 31 May 2016 Posts: 7 Topics: 1
|
Posted: Wed Jun 01, 2016 1:45 pm Post subject: |
|
|
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 |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12360 Topics: 75 Location: San Jose
|
Posted: Wed Jun 01, 2016 2:27 pm Post subject: |
|
|
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 |
|
|
arif677@gmail.com Beginner
Joined: 31 May 2016 Posts: 7 Topics: 1
|
Posted: Wed Jun 01, 2016 2:57 pm Post subject: |
|
|
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 |
|
|
arif677@gmail.com Beginner
Joined: 31 May 2016 Posts: 7 Topics: 1
|
Posted: Wed Jun 01, 2016 5:58 pm Post subject: |
|
|
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 |
|
|
|
|