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

    How do I call a DB2 Stored procedure?

    jlooker Level 1

      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 Level 4

          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 Level 4

            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) Level 1

              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 Level 1

                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