Stored Procedure Stage

[banactp]

I'm trying to call a DB2 Stored Procedure via the Stored Procedure stage in version 7.5.

I've set the stage up as a "Transform" type, and I'm using the following syntax to call the procedure:

Code: Select all

CALL D1K.SPKF100_XRF_TYPE(?, ?, ?, ?, ?, ?, ?, ?, ?);

The data types for the parameters are varchar or char with one exception - there is one binary(31) field that the procedure expects. I mention this because when I try to run it through DataStage, I receive the following error:

Code: Select all

APT_CombinedOperatorController,0: Fatal Error: Fatal: [IBM][CLI Driver] CLI0111E  Numeric value out of range. SQLSTATE=22003

and a conversion problem with the binary field is the only thing that makes sense to me. According to a source I Googled, this SQL error will result if the byte length of the input data does not match the expected SQL data length exactly.

So, my question is:

How can I input a value to, or specify a value within, the stored procedure stage that is exactly 31 bits long? I have tried doing this using a Row Generator stage, but have received the same error as above. For reference, the visible Row Generator output was "{00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00}", which does not work if typed directly into the Parameters tab of the stored procedure stage. I've also tried various expessions such as "0", "\0", and "0x0000000000000000000000000000000" to no avail.

Any help will be appreciated.

[roy]

Hi,
A search in this forum would have found this answer!!!

As far a I remember, SP Stage currently works with Oracle only, should work with other DB vendors in the future, concidering the unfolding events DB2 should be the next supported DB .
do read the docs on that stage (found on the install CD).

IHTH,

[banactp]

DB2 is supported, at least in the version I'm using - 7.5.1.

From the stage documentation included with the product:

With this release, the options are DB2, Oracle, and Sybase.

If I could include a screen shot, I would.

That being said, the problem appears to be specific to the issue that I raised, namely that the stored procedure I'd like to use requires a 31-bit input. I don't think PX is going to be able to do this (how would you on a 64-bit platform?) If I could just cajole one of our DBAs to redo the procedure to accept an integer input, I'd be all set.