Skip navigation
jlooker
jlooker Calculating status...
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

1279 Views   4 Replies   Latest reply: jlooker, Aug 28, 2009 2:20 PM
 
Replies
  • Jasmin Charbonneau
    Currently Being Moderated
    1. Jasmin Charbonneau,
    Aug 28, 2009 5:41 AM   in reply to jlooker
    Report

    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:
  • Jasmin Charbonneau
    Currently Being Moderated
    2. Jasmin Charbonneau,
    Aug 28, 2009 5:45 AM   in reply to Jasmin Charbonneau
    Report

    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:
  • Jake Shapiro (Adobe)
    Currently Being Moderated
    3. Jake Shapiro (Adobe),
    Aug 28, 2009 7:55 AM   in reply to Jasmin Charbonneau
    Report

    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:
  • jlooker
    Currently Being Moderated
    4. jlooker,
    Aug 28, 2009 2:20 PM   in reply to Jasmin Charbonneau
    Report

    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

     
    |
    Mark as:
Actions

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