CODEWITHSUNDEEP
db2ebean
Matthew I
Matthew I

Reputation: 1123

Ebean Annotations - Using sequences to generate IDs in DB2

I'm trying to use sequences to generate incremented IDs for my tables in DB2. It works when I send SQL statements directly to the database, but when using ebean the statement fails. Here's the field in Java:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "TABLENAME_IDNAME_TRIG")
@SequenceGenerator(name = "TABLENAME_IDNAME_TRIG", sequenceName = "TABLENAME_IDNAME_SEQ")
@Column(name = "IDNAME")
private Long id;

Here's the column in SQL (From TOAD):

Name            Data type   Not Null    Default Generated   Bit Data    Scope   Identity    
IDNAME          INTEGER     Yes                             No                  No

And here's the sequence definition in SQL:

CREATE OR REPLACE SEQUENCE SCHEMA.TABLENAME_IDNAME_SEQ 
  AS INTEGER CACHE 50 ORDER;

And the trigger:

CREATE OR REPLACE TRIGGER SCHEMA.TABLENAME_IDNAME_TRIG
  NO CASCADE BEFORE INSERT
  ON TABLENAME
  REFERENCING 
    NEW AS OBJ
  FOR EACH ROW
BEGIN 
  SET obj.IDNAME=NEXT VALUE FOR SCHEMA.TABLENAME_IDNAME_SEQ;
END;

What is the issue with my annotations here? As a(n important) side note - when I set GenerationType to AUTO, TABLE, or IDENTITY, it works, even though it shouldn't, because I'm also using this object to represent a parallel oracle table which also uses sequences for ID generation.

Edited to include error message: 

javax.persistence.PersistenceException: Error getting sequence nextval
...
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-348, SQLSTATE=428F9, SQLERRMC=NEXTVAL FOR SCHEMA.TABLENAME_IDNAME_SEQ, DRIVER=4.19.49

EDIT 2: The specific Sql statement that is failing is:

values nextval for QA_CONNECTION_ICONNECTIONI_SEQ union values nextval for QA_CONNECTION_ICONNECTIONI_SEQ union values nextval for QA_CONNECTION_ICONNECTIONI_SEQ

Which is SQL generated by Ebean. This is a smaller version of the real statement, which is repeated 20 times, so I'm guessing something screws up when generating the caching query.

EDIT 3: I believe this might be a bug in Ebean's use of DB2 sequences. This function generates SQl that returns an error for me when used with db2

public DB2SequenceIdGenerator(BackgroundExecutor be, DataSource ds, String seqName, int batchSize) {
  super(be, ds, seqName, batchSize);
  this.baseSql = "values nextval for " + seqName;
  this.unionBaseSql = " union " + baseSql;
}

EDIT 4: Based on this SO link I think it is a bug. Can't insert multiple values into DB2 by using UNION ALL and generate IDs from sequence The correct class probably looks like this? Though I haven't ever tried building the library, so I couldn't test it. Time to learn how to open a defect I guess.

public class DB2SequenceIdGenerator extends SequenceIdGenerator {

  private final String baseSql;
  private final String unionBaseSql;
  private final String startSql;

  public DB2SequenceIdGenerator(BackgroundExecutor be, DataSource ds, String seqName, int batchSize) {
    super(be, ds, seqName, batchSize);
    this.startSql = "values "
    this.baseSql = "(nextval for " + seqName);
    this.unionBaseSql = ", " + baseSql;
  }

  public String getSql(int batchSize) {
    StringBuilder sb = new StringBuilder();
    sb.append(startSql);
    sb.append(baseSql);
    for (int i = 1; i < batchSize; i++) {
      sb.append(unionBaseSql);
    }

    return sb.toString();
  }
}

Upvotes: 1

Views: 769

Answers (1)

Matthew I
Matthew I

Reputation: 1123

Temporary workaround for those interested: in ebean.properties, set

ebean.databaseSequenceBatchSize=1

Upvotes: 1

Related Questions