Call Stored Procedure on iSeries/AS400 System in ODBC Stage

boolseye · Post by **boolseye** » Wed Oct 28, 2015 3:11 pm

Hi,
We have requirement to call a stored procedure on iSeries/AS400 system.
We are connecting target system via ODBC Stage. The drivers we use to connect is VMdb200.so in .odbc.ini file
Issue:
We are trying to call a remote store procedure with first 3 as input column and last 2 column as "inout" parameter.

I tried to call the stored proc as below.
1) CALL dbschema.SP('a','b','c',?,?); - Error
[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Invalid argument value

2) CALL dbschema.SP('a','b','c',' ',' '); - Error
SQLSTATE=S1000, DBMS.CODE=-469
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver][UDB DB2 for iSeries and AS/400]Unknown error: SQLCODE -469

3) {CALL dbschema.SP('a','b','c',?,?)}; - Error
SQLSTATE=S1009, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Invalid argument value

4) {CALL dbschema.SP('a','b','c',' ',' ');} - Error
SQLSTATE=S1000, DBMS.CODE=-469
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver][UDB DB2 for iSeries and AS/400]Unknown error: SQLCODE -469
------------------------------
Then I asked the developer for AS400 to drop the inout parameters since I read somewhere ODBC Stage is not capable of handling inout OR Out parameters.

The procedure worked fine with only 3 input parameters.

Do we really have a limitation to use inout or Out parameter with ODBC stage?
Note: I'm not able to use store procedure stage since it is not able to connect to target db.

chulett · Post by **chulett** » Wed Oct 28, 2015 4:27 pm

That sounds like a support question to me. Of course, someone here may have direct experience with it but in your shoes (while you wait) I'd ping support.

qt_ky · Post by **qt_ky** » Thu Oct 29, 2015 5:56 am

Looks like it doesn't like your ? value. I'm not sure here, do the arguments need to be prefixed with a colon, as in :?

boolseye · Post by **boolseye** » Thu Oct 29, 2015 7:32 am

i tried colon with inout params earlier.. No luck..

boolseye · Post by **boolseye** » Mon Nov 02, 2015 11:32 am

Hi Craig .. did you hear back from IBM support on possibility?

chulett · Post by **chulett** » Mon Nov 02, 2015 11:42 am

Me saying "in your shoes I'd ping support" was a suggestion for you to contact them.