0

Environment: Websphere 8.5, OpenJPA 2.0, DB2 z/OS

There are two tables: one with verified data and another with draft data (staging table) + View that display information from both tables.
To avoid Primary Key clash I've decided that staging table will have negative values as a primary key. It was working in plain SQL, but my approach failed when I tried to define a generator for staging table in Java code

Generator for negative key was defined like this: 

CREATE SEQUENCE X AS INTEGER START WITH -1 INCREMENT BY -1
MINVALUE -999999 MAXVALUE 0

On entity side:

@Id
@SequenceGenerator(name="X", sequenceName="X")
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="X")
@Column(name = "ID")`

First element was created successfully (with value -1), but insertion of second element failed with

THE RANGE OF VALUES FOR THE IDENTITY COLUMN OR SEQUENCE IS EXHAUSTED. SQLCODE=-359, SQLSTATE=23522

Can you help me define @SequenceGenerator? Is it possible under Open JPA 2.0? Maybe sequence definition was wrong (MINVALUE/MAXVALUE)

mustaccio
  • 18,234
  • 16
  • 48
  • 57
user2706534
  • 99
  • 7

1 Answers1

0

First, I think your option to do a 'START WITH -99999 INCREMENT BY 1' is the best option. I'm not sure why you feel it is "not pretty". If you do this:

CREATE SEQUENCE SEQ_MYSEQ AS INTEGER START WITH -99999 INCREMENT BY 1 MINVALUE -999999 MAXVALUE 0

You are still range bound between -99999 and 0, right?

As I'll explain below, I think OpenJPA and EclipseLink like to count up. So I think you'll have better luck with this.

That said, let me answer your opening question. I have ran a test on OpenJPA and EclipseLink (since WebSphere uses Ecliplselink in WAS v9 and Liberty). I can not get your scenario to work with EclipseLink, but could get it to work with OpenJPA (but it wasn't pretty). Let me state what I did: this is the SQL I defined my sequence with (just as you listed in the description):

CREATE SEQUENCE SEQ_MYSEQ AS INTEGER START WITH -1 INCREMENT BY -1 MINVALUE -999999 MAXVALUE 0;

I defined my sequence generator in my entity as:

@Id @SequenceGenerator(name = "IDGENERATOR", sequenceName = "SEQ_MYSEQ", allocationSize = 1, initialValue = -1) @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "IDGENERATOR") private int id;

Notice that I'm telling JPA to start at -1, and to use an allocationSize of 1. The JavaDocs state the default is 50. One bummer (but not a show stopper) with allocationSize of 1 is that the JPA provider will go to the database for each sequence value (i.e. local caching will not be used). However, if this is not used, it seems both OpenJPA and EclipseLink want to count up by the allocation size. It is hard coded to count up. That is, either one will ask the DB for the next value, and then count up from there by allocationSize, NOT count down. On OpenJPA, you need to use this property:

Otherwise, by default OpenJPA executes an 'ALTER SEQUENCE' to make certain the INCREMENT BY defined in the SequenceGenerator matches what is in the database. If I don't add this property, I get the same exception about the range exhaustion. Anyway, with this, all works well on OpenJPA. On EclipseLink, I get this exception:

Exception Description: The sequence named [SEQ_MYSEQ] is setup incorrectly. Its increment does not match its pre-allocation size. at org.eclipse.persistence.internal.jpa.EntityManagerImpl.persist(EntityManagerImpl.java:510) at hat.test.MySeqTest.main(MySeqTest.java:28)

I didn't dig enough into EclipseLink to figure this out, but I did play around with the Sequence a bit and it seems like EclipseLink doesn't like negative values????

Thanks,

Heath Thomann

Heath Thomann
  • 136
  • 4