Skip to main content
Solved

DB2 - Use SQLCreator to call stored procedure

oscard
Influencer
Forum|alt.badge.img+21

Good morning!

I need to call a stored procedure of DB2 from FME. If I use the IBM Data Studio this script works fine:

CALL TESTING.TEST_PROCEDURE('input1','input2',?,?,?,?,?);

It has 2 input parameters and 5 output parameters.

But when I use the same script in the SQLCreator of FME i always get this error:

 

Literal replacement parsing failed for procedure call to DB2 for z/OS. Failing SQL text {my_script}. ERRORCODE=-4463, SQLSTATE=42601

 

Does anyone have any experience calling DB2 stored procedures in FME? Any help would be appreciated.

I'm using FME 2021.1.

 

Best answer by david_r

oscard wrote:

But those are the Output parameters. They have to be included in the call, but they don't have value.

 

Maybe this kind of procedures can't be called from FME.

Thanks, that wasn't clear to me. In that case you'll probably have to wrap the procedure call into something that returns a table-like object, similar to a SELECT statement.

This was the first result I found on Google, but I can't judge if it's relevant in your case or not: https://www.ibm.com/docs/en/db2/11.5?topic=functions-invoking-user-defined-table

View original
Did this help you find an answer to your question?

4 replies

david_r
Evangelist
  • david_rEvangelist
  • October 10, 2023

The SQLCreator / SQLExecutor does not support bind variables (signaled by the question marks), so you'll have to reference the dynamic parameters using the @Value(name) function. For example, given the FME attributes named "foo", "bar" and "baz":

CALL TESTING.TEST_PROCEDURE('input1','input2',@Value(foo),@Value(bar),@Value(baz));

Notice the upper case V in @Value().

oscard
Influencer
Forum|alt.badge.img+21
  • oscardInfluencer
  • Author
  • Influencer
  • October 10, 2023
david_r wrote:

The SQLCreator / SQLExecutor does not support bind variables (signaled by the question marks), so you'll have to reference the dynamic parameters using the @Value(name) function. For example, given the FME attributes named "foo", "bar" and "baz":

CALL TESTING.TEST_PROCEDURE('input1','input2',@Value(foo),@Value(bar),@Value(baz));

Notice the upper case V in @Value().

But those are the Output parameters. They have to be included in the call, but they don't have value.

 

Maybe this kind of procedures can't be called from FME.

david_r
Evangelist
  • david_rEvangelist
  • Best Answer
  • October 10, 2023
oscard wrote:

But those are the Output parameters. They have to be included in the call, but they don't have value.

 

Maybe this kind of procedures can't be called from FME.

Thanks, that wasn't clear to me. In that case you'll probably have to wrap the procedure call into something that returns a table-like object, similar to a SELECT statement.

This was the first result I found on Google, but I can't judge if it's relevant in your case or not: https://www.ibm.com/docs/en/db2/11.5?topic=functions-invoking-user-defined-table

oscard
Influencer
Forum|alt.badge.img+21
  • oscardInfluencer
  • Author
  • Influencer
  • October 10, 2023
oscard wrote:

But those are the Output parameters. They have to be included in the call, but they don't have value.

 

Maybe this kind of procedures can't be called from FME.

Thanks for your answer.

Yeah, something like that I will have to do. Instead of calling the stored procedure directly, using an intermediary. I'll ask the database manager for possibilities about this.

Thanks again!

Reply


Helpful Members This Week

Community Stats

30,958
Posts
117,330
Replies
38,745
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.