UDB STored Procedure Call

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
JPalatianos
Premium Member
Premium Member
Posts: 306
Joined: Wed Jun 21, 2006 11:41 am

UDB STored Procedure Call

Post by JPalatianos »

Hi,
I am trying to call the following UDB stored procedure from an ODBC stage:
VOBA.SP_GET_DB_LIABILITIES(IN varRptDt DATE, OUT SQLCODE_OUT INT)

I am using user defined SQL and when I try
call VOBA.SP_GET_DB_LIABILITIES('2006-03-31',?);
I get
LOAD_GPFRI_DATA..ODBC_14.DSLink2: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:call VOBA.SP_GET_DB_LIABILITIES('2006-03-31',?)
SQLSTATE=S1009, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC Driver Manager] Invalid argument value

What should I be using for the OUT parameter?

P.S. when I leave it out I get

call VOBA.SP_GET_DB_LIABILITIES('2006-03-31');
LOAD_GPFRI_DATA..ODBC_14.DSLink2: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:call VOBA.SP_GET_DB_LIABILITIES('2006-03-31')
SQLSTATE=S1000, DBMS.CODE=-440
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for Windows, UNIX, and Linux]No function or procedure was found with the specified name (VOBA.SP_GET_DB_LIABILITIES) and compatible arguments.

Thanks - - John
JPalatianos
Premium Member
Premium Member
Posts: 306
Joined: Wed Jun 21, 2006 11:41 am

Post by JPalatianos »

I also tried using the Stored Procedure Stage but I get the following: LOAD_GPFRI_DATA..Stored_Procedure_27: [IBM][CLI Driver] SQL1001N "PRIAC_DEV" is not a valid database name. SQLSTATE=2E000

Seems like it is not recognizing the Data Source. I have used this data source in other ODBC stages where I am extracting data from tables directly.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Check your .odbc.ini file in the $DSHOME directory and the uvodbc.config file in the project for existence of an entry for this DSN.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply