glassfish
  1. glassfish
  2. GLASSFISH-2232

Exception thrown in PK Identity generation for DB2 9.1

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 9.1pe
    • Fix Version/s: 9.1pe
    • Component/s: entity-persistence
    • Labels:
      None
    • Environment:

      Operating System: All
      Platform: All

    • Issuezilla Id:
      2,232
    • Status Whiteboard:
      Hide

      HIGH

      Show
      HIGH

      Description

      AS9.1 Build 32.

      Got follow exception in server.log when run PK Identity generation test (see
      the attachment) against DB2 9.1 with DataDirector driver(3.60.7):

      [#|2007-01-25T17:03:39.958-0800|WARNING|sun-
      appserver9.1|oracle.toplink.essentials.session.file:/space1/appservers/as91ee/do
      mains/domain1/applications/j2ee-apps/ejb-cmpdatatypesApp/entity.jar-
      manager|_ThreadID=12;_ThreadName=p: thread-pool-1; w: 3;_RequestID=ea881955-
      8c3c-46c7-9c50-e822512e9af4;|
      Local Exception Stack:
      Exception [TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build b32)):
      oracle.toplink.essentials.e
      xceptions.DatabaseException
      Internal Exception: java.sql.SQLException: [sunm][DB2 JDBC Driver][DB2]
      UNACCEPTABLE SQL STATEMENTError Code: -84
      Call:VALUES IDENTITY_VAL_LOCAL()
      Query:ValueReadQuery()
      at oracle.toplink.essentials.exceptions.DatabaseException.sqlException
      (DatabaseException.java:303)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteC
      all(DatabaseAccessor.java:551)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall
      (DatabaseAccessor.java:437)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeCall
      (AbstractSession.java:675)

      When run the same PK Identity generation test against Derby, the test passed.

      1. DataTypes.java
        4 kB
        mzh777
      2. pkGenIdentity_outside.zip
        17 kB
        mzh777

        Activity

        Hide
        mzh777 added a comment -

        Created an attachment (id=708)
        PK Identity generation

        Show
        mzh777 added a comment - Created an attachment (id=708) PK Identity generation
        Hide
        mzh777 added a comment -

        Verified by executing the test with DB2 jcc driver and the test passed. It seems
        that this is a specific issue with DB2 datadirect driver (3.60.7).

        Show
        mzh777 added a comment - Verified by executing the test with DB2 jcc driver and the test passed. It seems that this is a specific issue with DB2 datadirect driver (3.60.7).
        Hide
        mzh777 added a comment -

        Created an attachment (id=763)
        Test in outside container

        Show
        mzh777 added a comment - Created an attachment (id=763) Test in outside container
        Hide
        mzh777 added a comment -

        Reattach the test in outside container form. Turned the toplink.logging.level
        value to "FINE" and here are the exceptions again:
        [java] [TopLink Fine]: 2007.02.26 02:58:25.903-ServerSession(21145613)-
        Connection(7031149)-Thread(Thread[main,5,main])-CREATE TABLE DATATYPES (ID
        INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, INTEGERDATA INTEGER, LONGDATA
        INTEGER, BYTEDATA SMALLINT, FLOATDATA FLOAT, SHORTDATA SMALLINT, UTILDATEDATA
        DATE, CHARACTERDATA CHAR(1), SQLDATEDATA DATE, BOOLEANDATA SMALLINT DEFAULT 0,
        TIMESTAMPDATA TIMESTAMP, PRIMARY KEY (ID))
        [java] The testsuiteID is : Java2DB:JavaSE-EJB30-pkGenIdentity
        [java] [TopLink Fine]: 2007.02.26 02:58:26.164-ClientSession(14600371)-
        Connection(3682584)-Thread(Thread[main,5,main])-INSERT INTO DATATYPES
        (INTEGERDATA, LONGDATA, BYTEDATA, FLOATDATA, SHORTDATA, UTILDATEDATA,
        CHARACTERDATA, SQLDATEDATA, BOOLEANDATA, TIMESTAMPDATA) VALUES
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        [java] bind => [1993, 12345, 127, 3456789.0, 32767, 1976-09-15, a,
        1976-09-15, false, 1980-03-09 15:52:00.000125]
        [java] [TopLink Fine]: 2007.02.26 02:58:26.188-ClientSession(14600371)-
        Connection(3682584)-Thread(Thread[main,5,main])-VALUES IDENTITY_VAL_LOCAL()
        [java] [TopLink Warning]: 2007.02.26 02:58:26.194-ClientSession(14600371)
        Thread(Thread[main,5,main])-Exception [TOPLINK-4002] (Oracle TopLink
        Essentials - 9.1 (Build b33d-beta)):
        oracle.toplink.essentials.exceptions.DatabaseException
        [java] Internal Exception: java.sql.SQLException: [sunm][DB2 JDBC Driver]
        [DB2]UNACCEPTABLE SQL STATEMENTError Code: -84
        [java] Call:VALUES IDENTITY_VAL_LOCAL()
        [java] Query:ValueReadQuery()
        [java] Exception in thread "main" javax.persistence.PersistenceException:
        Exception [TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build b33d-beta)):
        oracle.toplink.essentials.exceptions.DatabaseException
        [java] Internal Exception: java.sql.SQLException: [sunm][DB2 JDBC Driver]
        [DB2]UNACCEPTABLE SQL STATEMENTError Code: -84
        [java] Call:VALUES IDENTITY_VAL_LOCAL()
        [java] Query:ValueReadQuery()
        [java] at
        oracle.toplink.essentials.internal.ejb.cmp3.base.EntityManagerImpl.flush
        (EntityManagerImpl.java:338)
        [java] at
        ejb30.persistence.datatypes.client.TestOutsideContainer.createDataTypes(Unknown
        Source)
        [java] at ejb30.persistence.datatypes.client.TestOutsideContainer.run
        (Unknown Source)
        [java] at ejb30.persistence.datatypes.client.TestOutsideContainer.main
        (Unknown Source)
        [java] Caused by: Exception [TOPLINK-4002] (Oracle TopLink Essentials -
        9.1 (Build b33d-beta)): oracle.toplink.essentials.exceptions.DatabaseException
        [java] Internal Exception: java.sql.SQLException: [sunm][DB2 JDBC Driver]
        [DB2]UNACCEPTABLE SQL STATEMENTError Code: -84
        [java] Call:VALUES IDENTITY_VAL_LOCAL()
        [java] Query:ValueReadQuery()
        [java] at
        oracle.toplink.essentials.exceptions.DatabaseException.sqlException
        (DatabaseException.java:303)
        [java] at
        oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteC
        all(DatabaseAccessor.java:551)

        Show
        mzh777 added a comment - Reattach the test in outside container form. Turned the toplink.logging.level value to "FINE" and here are the exceptions again: [java] [TopLink Fine] : 2007.02.26 02:58:25.903- ServerSession(21145613) - Connection(7031149)- Thread(Thread [main,5,main] ) -CREATE TABLE DATATYPES (ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, INTEGERDATA INTEGER, LONGDATA INTEGER, BYTEDATA SMALLINT, FLOATDATA FLOAT, SHORTDATA SMALLINT, UTILDATEDATA DATE, CHARACTERDATA CHAR(1), SQLDATEDATA DATE, BOOLEANDATA SMALLINT DEFAULT 0, TIMESTAMPDATA TIMESTAMP, PRIMARY KEY (ID)) [java] The testsuiteID is : Java2DB:JavaSE-EJB30-pkGenIdentity [java] [TopLink Fine] : 2007.02.26 02:58:26.164- ClientSession(14600371) - Connection(3682584)- Thread(Thread [main,5,main] ) -INSERT INTO DATATYPES (INTEGERDATA, LONGDATA, BYTEDATA, FLOATDATA, SHORTDATA, UTILDATEDATA, CHARACTERDATA, SQLDATEDATA, BOOLEANDATA, TIMESTAMPDATA) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [java] bind => [1993, 12345, 127, 3456789.0, 32767, 1976-09-15, a, 1976-09-15, false, 1980-03-09 15:52:00.000125] [java] [TopLink Fine] : 2007.02.26 02:58:26.188- ClientSession(14600371) - Connection(3682584)- Thread(Thread [main,5,main] ) -VALUES IDENTITY_VAL_LOCAL() [java] [TopLink Warning] : 2007.02.26 02:58:26.194- ClientSession(14600371) Thread(Thread [main,5,main] ) -Exception [TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build b33d-beta)): oracle.toplink.essentials.exceptions.DatabaseException [java] Internal Exception: java.sql.SQLException: [sunm] [DB2 JDBC Driver] [DB2] UNACCEPTABLE SQL STATEMENTError Code: -84 [java] Call:VALUES IDENTITY_VAL_LOCAL() [java] Query:ValueReadQuery() [java] Exception in thread "main" javax.persistence.PersistenceException: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build b33d-beta)): oracle.toplink.essentials.exceptions.DatabaseException [java] Internal Exception: java.sql.SQLException: [sunm] [DB2 JDBC Driver] [DB2] UNACCEPTABLE SQL STATEMENTError Code: -84 [java] Call:VALUES IDENTITY_VAL_LOCAL() [java] Query:ValueReadQuery() [java] at oracle.toplink.essentials.internal.ejb.cmp3.base.EntityManagerImpl.flush (EntityManagerImpl.java:338) [java] at ejb30.persistence.datatypes.client.TestOutsideContainer.createDataTypes(Unknown Source) [java] at ejb30.persistence.datatypes.client.TestOutsideContainer.run (Unknown Source) [java] at ejb30.persistence.datatypes.client.TestOutsideContainer.main (Unknown Source) [java] Caused by: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build b33d-beta)): oracle.toplink.essentials.exceptions.DatabaseException [java] Internal Exception: java.sql.SQLException: [sunm] [DB2 JDBC Driver] [DB2] UNACCEPTABLE SQL STATEMENTError Code: -84 [java] Call:VALUES IDENTITY_VAL_LOCAL() [java] Query:ValueReadQuery() [java] at oracle.toplink.essentials.exceptions.DatabaseException.sqlException (DatabaseException.java:303) [java] at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteC all(DatabaseAccessor.java:551)
        Hide
        mzh777 added a comment -

        Reassign to Lance.

        Show
        mzh777 added a comment - Reassign to Lance.
        Hide
        mf125085 added a comment -

        Taking ownership. Evaluation was done by Lance:

        Looks like we need to update the

        DB2Platform.java.buildSelectQueryForNativeSequence() to match what is in the
        DB2MainframePlatform.java which is

        SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1

        Show
        mf125085 added a comment - Taking ownership. Evaluation was done by Lance: Looks like we need to update the DB2Platform.java.buildSelectQueryForNativeSequence() to match what is in the DB2MainframePlatform.java which is SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
        Hide
        mf125085 added a comment -

        Starting.

        Show
        mf125085 added a comment - Starting.
        Hide
        pkrogh added a comment -

        Reprioritized based on BUG triage. P4 HIGH.

        Show
        pkrogh added a comment - Reprioritized based on BUG triage. P4 HIGH.
        Hide
        mf125085 added a comment -

        The reason for this problem is that the call

        VALUES IDENTITY_VAL_LOCAL()

        is not standard SQL. Some drivers (db2jcc.jar) implement the functionality using
        "native" interaction with the database. This is not implemented in the
        DataDirect driver.

        The change has been checked in. Please see

        http://fisheye5.cenqua.com/changelog/glassfish/?cs=MAIN:mf125085:20070309201714

        Show
        mf125085 added a comment - The reason for this problem is that the call VALUES IDENTITY_VAL_LOCAL() is not standard SQL. Some drivers (db2jcc.jar) implement the functionality using "native" interaction with the database. This is not implemented in the DataDirect driver. The change has been checked in. Please see http://fisheye5.cenqua.com/changelog/glassfish/?cs=MAIN:mf125085:20070309201714

          People

          • Assignee:
            mf125085
            Reporter:
            mzh777
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: