SimpleJdbcCall doesn't work for a stored function

Question

I am trying to execute a stored function on DB2 from Java. The function and code that calls it are very similar to those from Spring documentation's example https://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html#jdbc-simple-jdbc-call-3. Here's the function declaration:

CREATE FUNCTION ST_CLIENT_SEGMENT( p_oib VARCHAR(11) )
    RETURNS VARCHAR(10)
    NO EXTERNAL ACTION

Here's the code:

//version1
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
    .withSchemaName("VLIB").
    .withFunctionName("ST_CLIENT_SEGMENT")
SqlParameterSource in = new MapSqlParameterSource().addValue("p_oib", "123");
return simpleJdbcCall.executeFunction(String.class, in);

//version2
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
    .withSchemaName("VLIB")
    .withFunctionName("ST_CLIENT_SEGMENT");
simpleJdbcCall.declareParameters(new SqlParameter("p_oib", Types.VARCHAR));
return simpleJdbcCall.executeFunction(String.class, "123");

//the same exception
//Caused by: java.sql.SQLException: [SQL0440] Routine ST_CLIENT_SEGMENT in VLIB not found with specified parameters.

Does anyone know why this happens? When I call the function from my SQL client everything works fine.

Answer 1

A colleague of mine have found a solution. It seems this is a DB2 problem http://www.itjungle.com/fhg/fhg102506-story01.html. I try another SQL that also gave me an exception

jdbcTemplate.queryForObject("values vlib.ST_CLIENT_SEGMENT(?)", new Object[] {oib}, String.class);
//Caused by: java.sql.SQLException: [SQL0418] Use of parameter marker not valid.

After this change everything works fine

jdbcTemplate.queryForObject("values vlib.ST_CLIENT_SEGMENT( CAST( ? as CHAR) )", new Object[] {oib}, String.class);