Re: DB2 and Identity column

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Thu Jun 24 2010 - 06:36:07 UTC

  • Next message: Nishant Neeraj: "Re: Book"

    Hi Andrew,

    Not sure if that's related to your problem, but by default Db2Adapter
    is not setup to support identity columns (don't remember what the deal
    was back in the day when that setting was introduced... Most likely
    the driver didn't handle them correctly. Not sure if it does now).

    You may try getting a hold of Db2Adapter instance (DataDomain ->
    DataNode -> DbAdapter; if it is set to "AutoAdapter", you can change
    it in the Modeler to "Custom Adapter" with the value of
    "org.apache.cayenne.dba.db2.DB2Adapter"), and then call

    adapter.setSupportsGeneratedKeys(true);

    Let us know the outcome - we may change the Cayenne defaults.

    Cheers,
    Andrus

    On Jun 24, 2010, at 7:15 AM, Andrew Willerding wrote:

    > I am using Cayenne 3.0 and I am having trouble creating a new record
    > with a table that has an identity column on DB2 V9.1. The PK
    > Generation Strategy has been set to Database-generated and the
    > correct field (named ID), however when I first attempted to create a
    > record on this table I kept getting commit errors when cayenne was
    > executing SELECT NEXTVAL FOR ADMINISTRATOR.S_PPVSPECIAL FROM
    > SYSIBM.SYSDUMMY1. I realized that Cayenne was trying to generate a
    > value from the sequence so I created the appropriate S_PPVSPECIAL
    > sequence table which did get past the problem but now I'm getting
    > errors with the actual identity column. Here's the dump: Also why
    > are there two columns for STARTDATE in the Insert/Bind statements?
    >
    > Thanks,
    >
    > Andrew
    >
    > 2010-06-23 23:46:51,977 [http-8084-7] INFO
    > org.apache.cayenne.access.QueryLogger - SELECT NEXTVAL FOR
    > ADMINISTRATOR.S_PPVSPECIAL FROM SYSIBM.SYSDUMMY1
    > 2010-06-23 23:46:51,980 [http-8084-7] INFO
    > org.apache.cayenne.access.QueryLogger - --- will run 1 query.
    > 2010-06-23 23:46:51,980 [http-8084-7] INFO
    > org.apache.cayenne.access.QueryLogger - INSERT INTO
    > ADMINISTRATOR.PPVSPECIAL (ACTIVE, ENDDATE, EVENTID, EXPIRYDT, ID,
    > LANGUAGE, MENUMSGNUM, PROMOMSGNUM, REGION, SLOT, STARTDATE) VALUES
    > (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    > 2010-06-23 23:46:51,980 [http-8084-7] INFO
    > org.apache.cayenne.access.QueryLogger - [bind: 1->ACTIVE:'Y', 2-
    > >ENDDATE:'2010-06-30 23:46:42.664', 3->EVENTID:9036, 4-
    > >EXPIRYDT:NULL, 5->LANGUAGE:220, 6->MENUMSGNUM:'E', 7->PROMOMSGNUM:
    > 4001, 8->REGION:4000, 9->SLOT:'N', 10->STARTDATE:1, 11-
    > >STARTDATE:'2010-06-24 23:46:42.657']
    > 2010-06-23 23:46:51,985 [http-8084-7] INFO
    > org.apache.cayenne.access.QueryLogger - *** error.
    > com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -727,
    > SQLSTATE: 56098, SQLERRMC: 2;-798;428C9;ID
    > at com.ibm.db2.jcc.b.ig.e(ig.java:1594)
    > at com.ibm.db2.jcc.b.jg.a(jg.java:1826)
    > at com.ibm.db2.jcc.c.gb.a(gb.java:185)
    > at com.ibm.db2.jcc.c.gb.b(gb.java:82)
    > at com.ibm.db2.jcc.c.w.b(w.java:71)
    > at com.ibm.db2.jcc.c.wb.c(wb.java:225)
    > at com.ibm.db2.jcc.b.jg.bb(jg.java:1817)
    > at com.ibm.db2.jcc.b.jg.eb(jg.java:1878)
    > at com.ibm.db2.jcc.b.jg.jb(jg.java:1977)
    > at com.ibm.db2.jcc.b.jg.a(jg.java:1275)
    > at com.ibm.db2.jcc.b.jg.setObject(jg.java:1241)
    > at
    > org.apache.cayenne.access.types.CharType.setJdbcObject(CharType.java:
    > 169)
    > at
    > org.apache.cayenne.dba.JdbcAdapter.bindParameter(JdbcAdapter.java:542)
    > at
    > org.apache.cayenne.dba.db2.DB2Adapter.bindParameter(DB2Adapter.java:
    > 227)
    > at
    > org
    > .apache
    > .cayenne
    > .access
    > .trans
    > .InsertBatchQueryBuilder.bindParameters(InsertBatchQueryBuilder.java:
    > 61)
    > at
    > org
    > .apache
    > .cayenne
    > .access.jdbc.BatchAction.runAsIndividualQueries(BatchAction.java:219)
    > at
    > org
    > .apache
    > .cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:91)
    > at
    > org
    > .apache
    > .cayenne
    > .access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
    > at
    > org.apache.cayenne.access.DataNode.performQueries(DataNode.java:269)
    > at
    > org
    > .apache
    > .cayenne
    > .access.DataDomainFlushAction.runQueries(DataDomainFlushAction.java:
    > 226)
    > at
    > org
    > .apache
    > .cayenne
    > .access.DataDomainFlushAction.flush(DataDomainFlushAction.java:144)
    > at
    > org.apache.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:824)
    > at org.apache.cayenne.access.DataDomain
    > $2.transform(DataDomain.java:791)
    > at
    > org
    > .apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:
    > 850)
    > at
    > org.apache.cayenne.access.DataDomain.onSync(DataDomain.java:788)
    > at
    > org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:
    > 1106)
    > at
    > org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:
    > 1045)
    > at
    > com
    > .callista
    > .shaw
    > .webhotgreetadmin
    > .Windows.WindowEditPPVSpecial.clickSave(WindowEditPPVSpecial.java:362)
    > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    > at
    > sun
    > .reflect
    > .NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    > at
    > sun
    > .reflect
    > .DelegatingMethodAccessorImpl
    > .invoke(DelegatingMethodAccessorImpl.java:25)
    > at java.lang.reflect.Method.invoke(Method.java:597)
    > at
    > com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:497)
    > at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:161)
    > at
    > com.vaadin.ui.AbstractComponent.fireEvent(AbstractComponent.java:1146)
    > at com.vaadin.ui.Button.fireClick(Button.java:366)
    > at com.vaadin.ui.Button.changeVariables(Button.java:188)
    > at
    > com
    > .vaadin
    > .terminal
    > .gwt
    > .server
    > .AbstractCommunicationManager
    > .handleVariables(AbstractCommunicationManager.java:1087)
    > at
    > com
    > .vaadin
    > .terminal
    > .gwt
    > .server
    > .AbstractCommunicationManager
    > .doHandleUidlRequest(AbstractCommunicationManager.java:587)
    > at
    > com
    > .vaadin
    > .terminal
    > .gwt
    > .server
    > .CommunicationManager.handleUidlRequest(CommunicationManager.java:265)
    > at
    > com
    > .vaadin
    > .terminal
    > .gwt
    > .server
    > .AbstractApplicationServlet.service(AbstractApplicationServlet.java:
    > 482)
    > at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    > at
    > org
    > .apache
    > .catalina
    > .core
    > .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
    > 290)
    > at
    > org
    > .apache
    > .catalina
    > .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    > at
    > org
    > .apache
    > .cayenne
    > .conf
    > .WebApplicationContextFilter
    > .doFilter(WebApplicationContextFilter.java:91)
    > at
    > org
    > .apache
    > .catalina
    > .core
    > .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
    > 235)
    > at
    > org
    > .apache
    > .catalina
    > .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    > at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter
    > (MonitorFilter.java:390)
    > at
    > org
    > .apache
    > .catalina
    > .core
    > .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
    > 235)
    > at
    > org
    > .apache
    > .catalina
    > .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    > at
    > org
    > .apache
    > .catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:
    > 233)
    > at
    > org
    > .apache
    > .catalina.core.StandardContextValve.invoke(StandardContextValve.java:
    > 191)
    > at
    > org
    > .apache
    > .catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
    > at
    > org
    > .apache
    > .catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    > at
    > org
    > .apache
    > .catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:
    > 109)
    > at
    > org
    > .apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:
    > 293)
    > at
    > org
    > .apache.coyote.http11.Http11Processor.process(Http11Processor.java:
    > 849)
    > at org.apache.coyote.http11.Http11Protocol
    > $Http11ConnectionHandler.process(Http11Protocol.java:583)
    > at org.apache.tomcat.util.net.JIoEndpoint
    > $Worker.run(JIoEndpoint.java:454)
    > at java.lang.Thread.run(Thread.java:619)
    > com.vaadin.event.ListenerMethod$MethodException
    > Cause: org.apache.cayenne.CayenneRuntimeException: [v.3.0 Apr 26
    > 2010 09:59:17] Commit Exception
    > at
    > com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:507)
    > at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:161)
    > at
    > com.vaadin.ui.AbstractComponent.fireEvent(AbstractComponent.java:1146)
    > at com.vaadin.ui.Button.fireClick(Button.java:366)
    > at com.vaadin.ui.Button.changeVariables(Button.java:188)
    > at
    > com
    > .vaadin
    > .terminal
    > .gwt
    > .server
    > .AbstractCommunicationManager
    > .handleVariables(AbstractCommunicationManager.java:1087)
    > at
    > com
    > .vaadin
    > .terminal
    > .gwt
    > .server
    > .AbstractCommunicationManager
    > .doHandleUidlRequest(AbstractCommunicationManager.java:587)
    > at
    > com
    > .vaadin
    > .terminal
    > .gwt
    > .server
    > .CommunicationManager.handleUidlRequest(CommunicationManager.java:265)
    > at
    > com
    > .vaadin
    > .terminal
    > .gwt
    > .server
    > .AbstractApplicationServlet.service(AbstractApplicationServlet.java:
    > 482)
    > at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    > at
    > org
    > .apache
    > .catalina
    > .core
    > .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
    > 290)
    > at
    > org
    > .apache
    > .catalina
    > .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    > at
    > org
    > .apache
    > .cayenne
    > .conf
    > .WebApplicationContextFilter
    > .doFilter(WebApplicationContextFilter.java:91)
    > at
    > org
    > .apache
    > .catalina
    > .core
    > .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
    > 235)
    > at
    > org
    > .apache
    > .catalina
    > .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    > at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter
    > (MonitorFilter.java:390)
    > at
    > org
    > .apache
    > .catalina
    > .core
    > .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
    > 235)
    > at
    > org
    > .apache
    > .catalina
    > .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    > at
    > org
    > .apache
    > .catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:
    > 233)
    > at
    > org
    > .apache
    > .catalina.core.StandardContextValve.invoke(StandardContextValve.java:
    > 191)
    > at
    > org
    > .apache
    > .catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
    > at
    > org
    > .apache
    > .catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    > at
    > org
    > .apache
    > .catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:
    > 109)
    > at
    > org
    > .apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:
    > 293)
    > at
    > org
    > .apache.coyote.http11.Http11Processor.process(Http11Processor.java:
    > 849)
    > at org.apache.coyote.http11.Http11Protocol
    > $Http11ConnectionHandler.process(Http11Protocol.java:583)
    > at org.apache.tomcat.util.net.JIoEndpoint
    > $Worker.run(JIoEndpoint.java:454)
    > at java.lang.Thread.run(Thread.java:619)
    > Caused by: org.apache.cayenne.CayenneRuntimeException: [v.3.0 Apr 26
    > 2010 09:59:17] Commit Exception
    > at
    > org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:
    > 1134)
    > at
    > org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:
    > 1045)
    > at
    > com
    > .callista
    > .shaw
    > .webhotgreetadmin
    > .Windows.WindowEditPPVSpecial.clickSave(WindowEditPPVSpecial.java:362)
    > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    > at
    > sun
    > .reflect
    > .NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    > at
    > sun
    > .reflect
    > .DelegatingMethodAccessorImpl
    > .invoke(DelegatingMethodAccessorImpl.java:25)
    > at java.lang.reflect.Method.invoke(Method.java:597)
    > at
    > com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:497)
    > ... 27 more
    > Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE:
    > -727, SQLSTATE: 56098, SQLERRMC: 2;-798;428C9;ID
    > at com.ibm.db2.jcc.b.ig.e(ig.java:1594)
    > at com.ibm.db2.jcc.b.jg.a(jg.java:1826)
    > at com.ibm.db2.jcc.c.gb.a(gb.java:185)
    > at com.ibm.db2.jcc.c.gb.b(gb.java:82)
    > at com.ibm.db2.jcc.c.w.b(w.java:71)
    > at com.ibm.db2.jcc.c.wb.c(wb.java:225)
    > at com.ibm.db2.jcc.b.jg.bb(jg.java:1817)
    > at com.ibm.db2.jcc.b.jg.eb(jg.java:1878)
    > at com.ibm.db2.jcc.b.jg.jb(jg.java:1977)
    > at com.ibm.db2.jcc.b.jg.a(jg.java:1275)
    > at com.ibm.db2.jcc.b.jg.setObject(jg.java:1241)
    > at
    > org.apache.cayenne.access.types.CharType.setJdbcObject(CharType.java:
    > 169)
    > at
    > org.apache.cayenne.dba.JdbcAdapter.bindParameter(JdbcAdapter.java:542)
    > at
    > org.apache.cayenne.dba.db2.DB2Adapter.bindParameter(DB2Adapter.java:
    > 227)
    > at
    > org
    > .apache
    > .cayenne
    > .access
    > .trans
    > .InsertBatchQueryBuilder.bindParameters(InsertBatchQueryBuilder.java:
    > 61)
    > at
    > org
    > .apache
    > .cayenne
    > .access.jdbc.BatchAction.runAsIndividualQueries(BatchAction.java:219)
    > at
    > org
    > .apache
    > .cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:91)
    > at
    > org
    > .apache
    > .cayenne
    > .access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
    > at
    > org.apache.cayenne.access.DataNode.performQueries(DataNode.java:269)
    > at
    > org
    > .apache
    > .cayenne
    > .access.DataDomainFlushAction.runQueries(DataDomainFlushAction.java:
    > 226)
    > at
    > org
    > .apache
    > .cayenne
    > .access.DataDomainFlushAction.flush(DataDomainFlushAction.java:144)
    > at
    > org.apache.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:824)
    > at org.apache.cayenne.access.DataDomain
    > $2.transform(DataDomain.java:791)
    > at
    > org
    > .apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:
    > 850)
    > at
    > org.apache.cayenne.access.DataDomain.onSync(DataDomain.java:788)
    > at
    > org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:
    > 1106)
    > ... 34 more
    >
    >



    This archive was generated by hypermail 2.0.0 : Thu Jun 24 2010 - 06:36:45 UTC