How do I call a DB2 Stored procedure?

[jlooker]

I am having problems trying to call a DB2 stored procedure.

I am using the Service: Foundation -> JDBC 1.0 -> Call Stored Procedure.

Stored procedure I am calling is (with 4 input params):

CALL DB2D.SYSPROC.REGC1389(?, ?, ?, ?,
{$ /process_data/@Name_Full $},
{$ /process_data/@Name_Title $},
{$ /process_data/@Name_Last $},
{$ /process_data/@Name_Middle $},
{$ /process_data/@Name_First $},
{$ /process_data/@Name_Suffix $},
{$ /process_data/@Address_1 $},
{$ /process_data/@Address_2 $},
{$ /process_data/@Address_3 $},
{$ /process_data/@Address_City $},
{$ /process_data/@Address_State $},
{$ /process_data/@Address_Zip $},
{$ /process_data/@ex_Code $},
{$ /process_data/@Birthdate $},
{$ /process_data/@ID_TypeCode_1 $},
{$ /process_data/@ID_Number_1 $},
{$ /process_data/@ID_TypeCode_2 $},
{$ /process_data/@ID_Number_2 $},
{$ /process_data/@ID_TypeCode_5 $},
{$ /process_data/@ID_Number_5 $},
{$ /process_data/@ID_TypeCode_6 $},
{$ /process_data/@ID_Number_6 $},
{$ /process_data/@ID_TypeCode_7 $},
{$ /process_data/@ID_Number_7 $},
{$ /process_data/@ID_TypeCode_8 $},
{$ /process_data/@ID_Number_8 $},
{$ /process_data/@ID_TypeCode_9 $},
{$ /process_data/@ID_Number_9 $},
{$ /process_data/@ID_TypeCode_10 $},
{$ /process_data/@ID_Number_10 $},
{$ /process_data/@ID_TypeCode_11 $},
{$ /process_data/@ID_Number_11 $},
{$ /process_data/@ID_TypeCode_12 $},
{$ /process_data/@ID_Number_12 $},
{$ /process_data/@ID_TypeCode_13 $},
{$ /process_data/@ID_Number_13 $},
{$ /process_data/@ID_TypeCode_14 $},
{$ /process_data/@ID_Number_14 $},
{$ /process_data/@ID_TypeCode_15 $},
{$ /process_data/@ID_Number_15 $},
{$ /process_data/@ID_TypeCode_16 $},
{$ /process_data/@ID_Number_16 $},
{$ /process_data/@ID_TypeCode_17 $},
{$ /process_data/@ID_Number_17 $},
{$ /process_data/@ID_TypeCode_18 $},
{$ /process_data/@ID_Number_18 $},
{$ /process_data/@Return_Code $},
{$ /process_data/@SQL_RTNC $},
{$ /process_data/@SQL_StateCode $},
{$ /process_data/@SQL_Errmsg $});

(I can call this same stored proc in ColdFusion, so the procedure does work.)

The error message I get when I invoke it is:

=======================================

ALC-DSC-005-000: com.adobe.idp.dsc.DSCNotSerializableException: Not Serializable
Caused by: ALC-DSC-000-000: com.adobe.idp.dsc.DSCRuntimeException: Internal error.
at com.adobe.idp.workflow.dsc.invoker.WorkflowDSCInvoker.transientInvoke(WorkflowDSCInvoker.java:367)
at com.adobe.idp.workflow.dsc.invoker.WorkflowDSCInvoker.invoke(WorkflowDSCInvoker.java:157)
at com.adobe.idp.dsc.interceptor.impl.InvocationInterceptor.intercept(InvocationInterceptor.java:140)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.doInTransaction(TransactionInterceptor.java:74)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.execute(EjbTransactionCMTAdapterBean.java:342)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.doRequiresNew(EjbTransactionCMTAdapterBean.java:284)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EJSLocalStatelessEjbTransactionCMTAdapter_caf58c4f.doRequiresNew(Unknown Source)
at com.adobe.idp.dsc.transaction.impl.ejb.EjbTransactionProvider.execute(EjbTransactionProvider.java:143)
at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor.intercept(TransactionInterceptor.java:72)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.intercept(InvocationStrategyInterceptor.java:55)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept(InvalidStateInterceptor.java:37)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept(AuthorizationInterceptor.java:102)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterceptor.java:48)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.engine.impl.ServiceEngineImpl.invoke(ServiceEngineImpl.java:115)
at com.adobe.idp.dsc.routing.Router.routeRequest(Router.java:118)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageReceiver.invoke(AbstractMessageReceiver.java:315)
at com.adobe.idp.dsc.provider.impl.soap.axis.sdk.SoapSdkEndpoint.invokeCall(SoapSdkEndpoint.java:138)
at com.adobe.idp.dsc.provider.impl.soap.axis.sdk.SoapSdkEndpoint.invoke(SoapSdkEndpoint.java:81)
at sun.reflect.GeneratedMethodAccessor171.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:618)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1096)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1037)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:145)
at com.adobe.idp.dsc.provider.impl.soap.axis.InvocationFilter.doFilter(InvocationFilter.java:43)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:190)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:130)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:87)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:832)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:679)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:566)
at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:90)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:748)
at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1466)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:119)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:458)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:387)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:267)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:195)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:743)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:873)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1473)
Caused by: java.lang.RuntimeException: String Literal support for procedure calls to DB2/390 is disabled.  Failing SQL text CALL DB2D.SYSPROC.REGC1389(?, ?, ?, ?,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
);
at com.adobe.workflow.engine.PEUtil.invokeAction(PEUtil.java:837)
at com.adobe.idp.workflow.dsc.invoker.WorkflowDSCInvoker.transientInvoke(WorkflowDSCInvoker.java:346)
... 66 more
Caused by: java.lang.RuntimeException: String Literal support for procedure calls to DB2/390 is disabled.  Failing SQL text CALL DB2D.SYSPROC.REGC1389(?, ?, ?, ?,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
);
at com.adobe.idp.dsc.jdbc.helper.StoredProcedureHelper.callStoredProcedure(StoredProcedureHelper.java:115)
at com.adobe.idp.dsc.jdbc.JDBCService.callStoredProcedure(JDBCService.java:660)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:618)
at com.adobe.idp.dsc.component.impl.DefaultPOJOInvokerImpl.invoke(DefaultPOJOInvokerImpl.java:118)
at com.adobe.idp.dsc.interceptor.impl.InvocationInterceptor.intercept(InvocationInterceptor.java:140)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.doInTransaction(TransactionInterceptor.java:74)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapterBean.doBMT(EjbTransactionBMTAdapterBean.java:197)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EJSLocalStatelessEjbTransactionBMTAdapter_3af08fdf.doBMT(Unknown Source)
at com.adobe.idp.dsc.transaction.impl.ejb.EjbTransactionProvider.execute(EjbTransactionProvider.java:95)
at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor.intercept(TransactionInterceptor.java:72)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.intercept(InvocationStrategyInterceptor.java:55)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept(InvalidStateInterceptor.java:37)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept(AuthorizationInterceptor.java:132)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterceptor.java:48)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.engine.impl.ServiceEngineImpl.invoke(ServiceEngineImpl.java:115)
at com.adobe.idp.dsc.routing.Router.routeRequest(Router.java:118)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:91)
at com.adobe.idp.dsc.provider.impl.vm.VMMessageDispatcher.doSend(VMMessageDispatcher.java:215)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:57)
at com.adobe.idp.dsc.clientsdk.ServiceClient.invoke(ServiceClient.java:208)
at com.adobe.workflow.engine.PEUtil.invokeAction(PEUtil.java:724)
... 67 more
Caused by: com.ibm.db2.jcc.c.SqlException: String Literal support for procedure calls to DB2/390 is disabled.  Failing SQL text CALL DB2D.SYSPROC.REGC1389(?, ?, ?, ?,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
);
at com.ibm.db2.jcc.c.ig.i(ig.java:2531)
at com.ibm.db2.jcc.c.jg.b(jg.java:292)
at com.ibm.db2.jcc.c.jg.<init>(jg.java:263)
at com.ibm.db2.jcc.c.kg.<init>(kg.java:72)
at com.ibm.db2.jcc.a.fc.<init>(fc.java:91)
at com.ibm.db2.jcc.a.b.b(b.java:1959)
at com.ibm.db2.jcc.c.p.a(p.java:2317)
at com.ibm.db2.jcc.c.p.prepareCall(p.java:1909)
at com.ibm.db2.jcc.c.nc.prepareCall(nc.java:246)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.pmiPrepareCall(WSJdbcConnection.java:1832)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareCall(WSJdbcConnection.java:1959)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareCall(WSJdbcConnection.java:1914)
at com.adobe.idp.dsc.jdbc.helper.StoredProcedureHelper.callStoredProcedure(StoredProcedureHelper.java:105)
... 96 more

at com.adobe.idp.dsc.provider.impl.base.AbstractResponseHolder.handleException(AbstractResponseHolder.java:136)
at com.adobe.idp.dsc.provider.impl.soap.axis.sdk.SoapSdkBindingStubUtil.deSerializeResponse(SoapSdkBindingStubUtil.java:122)
at com.adobe.idp.dsc.provider.impl.soap.axis.sdk.SoapAxisDispatcher.doSend(SoapAxisDispatcher.java:128)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispatcher.java:57)
at com.adobe.idp.dsc.clientsdk.ServiceClient.invoke(ServiceClient.java:208)
at com.adobe.common.utils.invoke.InvokeWithProgressRunner.invokeServiceOperation(InvokeWithProgressRunner.java:170)
at com.adobe.common.utils.invoke.InvokeWithProgressRunner.run(InvokeWithProgressRunner.java:97)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:113)

=======================================

I am using the JDBC Provider: DB2 Universal JDBC Provider

Implementation class name : com.ibm.db2.jcc.DB2ConnectionPoolDataSource

For the Data source : DB2D

Data store helper class name:Data store helper classes provided by WebSphere Application Server

This is my first attempt at calling a DB2 stored procedure, so any tips on how to make it work would be appreciated.

Thanks

Jim

[Jasmin_Charbonn]

You get this error:

Caused by: java.lang.RuntimeException: String Literal support for procedure calls to DB2/390 is disabled.

I'm not familiar with DB2 call procedures. Is there something you can set on the database side?

Jasmin

[Jasmin_Charbonn]

Just found that on the IBM website:

"

The CALL LITERAL support is disabled by default, but can
be enabled using a new DB2 Universal Driver property.  The
new property is only configurable as a global driver
configuration property, and has the following syntax:
    db2.jcc.supportZosSpLiterals=yes"

JAsmin

[Jake_Shapiro__A]

Thanks, Jasmin!

Is there anything we could do on the LC side of things to format the stored procedure call differently, so that we are not passing string literals?

[jlooker]

Jasmin,

Thanks for the "db2.jcc.supportZosSpLiterals=yes" configuration property suggestion.

I worked with our WebSphere support team to set this property.  We set it as a Custom Property in the data source.

The DB2 driver version is higher then the APAR which supports this property, but it doesn't seem to recognize it.

[8/28/09 11:15:42:775 CDT] 0000003f DSConfigurati W DSRA8200W: DataSource Configuration: DSRA8020E: Warning: The property 'supportZosSpLiterals' does not exist on the DataSource class com.ibm.db2.jcc.DB2ConnectionPoolDataSource.

[8/28/09 11:15:43:337 CDT] 0000003f InternalDB2Un I DSRA8203I: Database product name : DB2

[8/28/09 11:15:43:353 CDT] 0000003f InternalDB2Un I DSRA8204I: Database product version : DSN08015

[8/28/09 11:15:43:353 CDT] 0000003f InternalDB2Un I DSRA8205I: JDBC driver name : IBM DB2 JDBC Universal Driver Architecture

[8/28/09 11:15:43:353 CDT] 0000003f InternalDB2Un I DSRA8206I: JDBC driver version : 2.11.24

[8/28/09 11:15:43:369 CDT] 0000003f InternalDB2Un I DSRA8212I: DataStoreHelper name is: com.ibm.websphere.rsadapter.DB2UniversalDataStoreHelper@1aca1aca.

[8/28/09 11:15:43:384 CDT] 0000003f WSRdbDataSour I DSRA8208I: JDBC driver type : 4

Are we setting the property in the right place?  What version of the DB2 driver is needed for this property?  Any other tips?

Thanks

Jim