Stored procedure on Federated thru DB2
Select messages from
# through # FAQ
[/[Print]\]

MVSFORUMS.com -> Database

#1: Stored procedure on Federated thru DB2 Author: arif677@gmail.com PostPosted: Wed Jun 01, 2016 10:58 am
    —
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.

#2: Re: Stored procedure on Federated thru DB2 Author: kolusuLocation: San Jose PostPosted: Wed Jun 01, 2016 11:43 am
    —
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.

#3:  Author: arif677@gmail.com PostPosted: Wed Jun 01, 2016 1:00 pm
    —
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.

#4:  Author: kolusuLocation: San Jose PostPosted: Wed Jun 01, 2016 1:12 pm
    —
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' ;


Last edited by kolusu on Wed Jun 01, 2016 1:13 pm; edited 1 time in total

#5:  Author: arif677@gmail.com PostPosted: Wed Jun 01, 2016 1:16 pm
    —
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.

#6:  Author: arif677@gmail.com PostPosted: Wed Jun 01, 2016 1:22 pm
    —
Kolusu, I have tried binding the program with PATH(REM_LOC.SCHMA_NAME) and it did not work.

#7:  Author: kolusuLocation: San Jose PostPosted: Wed Jun 01, 2016 1:36 pm
    —
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?

#8:  Author: arif677@gmail.com PostPosted: Wed Jun 01, 2016 1:45 pm
    —
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

#9:  Author: kolusuLocation: San Jose PostPosted: Wed Jun 01, 2016 2:27 pm
    —
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.

#10:  Author: arif677@gmail.com PostPosted: Wed Jun 01, 2016 2:57 pm
    —
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.

#11:  Author: arif677@gmail.com PostPosted: Wed Jun 01, 2016 5:58 pm
    —
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....



MVSFORUMS.com -> Database


output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1

Powered by phpBB © 2001, 2005 phpBB Group