Skip navigation
Currently Being Moderated

How do I call a DB2 Stored procedure?

Aug 26, 2009 8:07 AM

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(Workflow DSCInvoker.java:157)
at com.adobe.idp.dsc.interceptor.impl.InvocationInterceptor.intercept(In vocationInterceptor.java:140)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.do InTransaction(TransactionInterceptor.java:74)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapt erBean.execute(EjbTransactionCMTAdapterBean.java:342)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapt erBean.doRequiresNew(EjbTransactionCMTAdapterBean.java:284)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EJSLocalStatelessEjbTr ansactionCMTAdapter_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.inte rcept(TransactionInterceptor.java:72)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.inte rcept(InvocationStrategyInterceptor.java:55)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept( InvalidStateInterceptor.java:37)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept (AuthorizationInterceptor.java:102)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterc eptor.java:48)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.engine.impl.ServiceEngineImpl.invoke(ServiceEngineI mpl.java:115)
at com.adobe.idp.dsc.routing.Router.routeRequest(Router.java:118)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageReceiver.invoke(A bstractMessageReceiver.java:315)
at com.adobe.idp.dsc.provider.impl.soap.axis.sdk.SoapSdkEndpoint.invokeC all(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(DelegatingMethodAcces sorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:618)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.j ava: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(InvocationStrateg y.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:69 9)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBas e.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(WebAppFilte rChain.java:145)
at com.adobe.idp.dsc.provider.impl.soap.axis.InvocationFilter.doFilter(I nvocationFilter.java:43)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterI nstanceWrapper.java:190)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilte rChain.java:130)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilt erChain.java:87)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFil terManager.java:832)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFil terManager.java:679)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletW rapper.java:566)
at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(Servle tWrapper.java:478)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(Cac heServletWrapper.java:90)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java: 748)
at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.jav a:1466)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.jav a:119)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimina tion(HttpInboundLink.java:458)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInforma tion(HttpInboundLink.java:387)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboun dLink.java:267)
at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDi scriminators(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(AbstractAsyncFutu re.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChanne lFuture.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.j ava: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.callStoredProcedu re(StoredProcedureHelper.java:115)
at com.adobe.idp.dsc.jdbc.JDBCService.callStoredProcedure(JDBCService.ja va:660)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl. java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces sorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:618)
at com.adobe.idp.dsc.component.impl.DefaultPOJOInvokerImpl.invoke(Defaul tPOJOInvokerImpl.java:118)
at com.adobe.idp.dsc.interceptor.impl.InvocationInterceptor.intercept(In vocationInterceptor.java:140)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.do InTransaction(TransactionInterceptor.java:74)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapt erBean.doBMT(EjbTransactionBMTAdapterBean.java:197)
at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EJSLocalStatelessEjbTr ansactionBMTAdapter_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.inte rcept(TransactionInterceptor.java:72)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.inte rcept(InvocationStrategyInterceptor.java:55)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept( InvalidStateInterceptor.java:37)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept (AuthorizationInterceptor.java:132)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterc eptor.java:48)
at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.procee d(RequestInterceptorChainImpl.java:60)
at com.adobe.idp.dsc.engine.impl.ServiceEngineImpl.invoke(ServiceEngineI mpl.java:115)
at com.adobe.idp.dsc.routing.Router.routeRequest(Router.java:118)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageReceiver.routeMes sage(AbstractMessageReceiver.java:91)
at com.adobe.idp.dsc.provider.impl.vm.VMMessageDispatcher.doSend(VMMessa geDispatcher.java:215)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(A bstractMessageDispatcher.java:57)
at com.adobe.idp.dsc.clientsdk.ServiceClient.invoke(ServiceClient.java:2 08)
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(WSJdbcConne ction.java:1832)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareCall(WSJdbcConnecti on.java:1959)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareCall(WSJdbcConnecti on.java:1914)
at com.adobe.idp.dsc.jdbc.helper.StoredProcedureHelper.callStoredProcedu re(StoredProcedureHelper.java:105)
... 96 more

at com.adobe.idp.dsc.provider.impl.base.AbstractResponseHolder.handleExc eption(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.doSe nd(SoapAxisDispatcher.java:128)
at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(A bstractMessageDispatcher.java:57)
at com.adobe.idp.dsc.clientsdk.ServiceClient.invoke(ServiceClient.java:2 08)
at com.adobe.common.utils.invoke.InvokeWithProgressRunner.invokeServiceO peration(InvokeWithProgressRunner.java:170)
at com.adobe.common.utils.invoke.InvokeWithProgressRunner.run(InvokeWith ProgressRunner.java:97)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(Modal Context.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

 
Replies
  • Currently Being Moderated
    Aug 28, 2009 5:41 AM   in reply to jlooker

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 28, 2009 5:45 AM   in reply to Jasmin Charbonneau

    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
     
    |
    Mark as:
  • Currently Being Moderated
    Aug 28, 2009 7:55 AM   in reply to Jasmin Charbonneau

    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?

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points