4 Replies Latest reply: Aug 28, 2009 2:20 PM by jlooker RSS

    How do I call a DB2 Stored procedure?

    jlooker Community Member

      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(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.doInTransaction(Transa ctionInterceptor.java:74)
      at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterBean.execute(EjbTr ansactionCMTAdapterBean.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(EjbTransactionProvi der.java:143)
      at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor.intercept(TransactionInt erceptor.java:72)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.intercept(InvocationStra tegyInterceptor.java:55)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept(InvalidStateIntercep tor.java:37)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept(AuthorizationInterc eptor.java:102)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterceptor.java:48)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.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(AbstractMessageReceiv er.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.jav a: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(NewConn ectionInitialReadCallback.java:214)
      at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitia lReadCallback.java:113)
      at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionLi stener.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(StoredProcedureHe lper.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.jav a:118)
      at com.adobe.idp.dsc.interceptor.impl.InvocationInterceptor.intercept(InvocationInterceptor. java:140)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor$1.doInTransaction(Transa ctionInterceptor.java:74)
      at com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionBMTAdapterBean.doBMT(EjbTran sactionBMTAdapterBean.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(EjbTransactionProvi der.java:95)
      at com.adobe.idp.dsc.transaction.interceptor.TransactionInterceptor.intercept(TransactionInt erceptor.java:72)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.interceptor.impl.InvocationStrategyInterceptor.intercept(InvocationStra tegyInterceptor.java:55)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.interceptor.impl.InvalidStateInterceptor.intercept(InvalidStateIntercep tor.java:37)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.interceptor.impl.AuthorizationInterceptor.intercept(AuthorizationInterc eptor.java:132)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.java:60)
      at com.adobe.idp.dsc.interceptor.impl.JMXInterceptor.intercept(JMXInterceptor.java:48)
      at com.adobe.idp.dsc.interceptor.impl.RequestInterceptorChainImpl.proceed(RequestInterceptor ChainImpl.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(AbstractMessage Receiver.java:91)
      at com.adobe.idp.dsc.provider.impl.vm.VMMessageDispatcher.doSend(VMMessageDispatcher.java:21 5)
      at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispat cher.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(StoredProcedureHe lper.java:105)
      ... 96 more

      at com.adobe.idp.dsc.provider.impl.base.AbstractResponseHolder.handleException(AbstractRespo nseHolder.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(SoapAxisDispatche r.java:128)
      at com.adobe.idp.dsc.provider.impl.base.AbstractMessageDispatcher.send(AbstractMessageDispat cher.java:57)
      at com.adobe.idp.dsc.clientsdk.ServiceClient.invoke(ServiceClient.java:208)
      at com.adobe.common.utils.invoke.InvokeWithProgressRunner.invokeServiceOperation(InvokeWithP rogressRunner.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

        • 1. Re: How do I call a DB2 Stored procedure?
          Jasmin Charbonneau techies

          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

          • 2. Re: How do I call a DB2 Stored procedure?
            Jasmin Charbonneau techies

            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
            • 3. Re: How do I call a DB2 Stored procedure?
              Jake Shapiro (Adobe) techies

              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?

              • 4. Re: How do I call a DB2 Stored procedure?
                jlooker Community Member

                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