Problems with Hibernate and 10.2B

1) Can't insert into an empty table
If the table is empty Hibernate cannot insert a new record. Once a record has been inserted from another source we can insert records from Hibernate without problems.

this is because the getSequenceNextValString returns a string that does a  select on the table itself (for which you want to get the sequence next  value) which if the table is empty won't return anything.

not having any experience with hibernate I'll say you can try to change that to simple return sequence.nextval

public String getSequenceNextValString(String sequenceName) throws MappingException {

  // TODO Auto-generated method stub

    return getSelectSequenceNextValString( sequenceName ); 

}

or make a 'dual' table with a single record to be sure you get the sequence value if you do a select on it

public String getSequenceNextValString(String sequenceName) throws MappingException {

  // TODO Auto-generated method stub

    return "select " + getSelectSequenceNextValString( sequenceName ) + " from dual";

}

2) Record IDs don't match the Sequence.
Hibernate has big gaps in the record IDs: 400, 550, 750, 800

that could be very much related to the first issue... on each insert the  sequence number is increased not by one but by the number of records  found in that table (it's a select on that table with no clause, no  limit)

it's a select on that table with no clause, no  limit

If that means what I think it does, it sounds like a performance problem.

tamhas wrote:
it's a select on that table with no clause, no  limit
If that means what I think it does, it sounds like a performance problem.

well yes, doing a full table scan on each insert might add-up something  but not really noticeable at first sight... the largest id mentioned was  800 and given the almost exponential grow of sequence value (because of  the way it was retrieved) means that there weren't too many records to  start experiencing performance problems

just for the record, seems that each table got his own sequence  "

Tom Bascom did a search a while back for anyone doing substantial update activity from SQL against an OE database and my impression is that he didn't find much and that performance was a potential problem.

"but there is something else that bugs me... the simple use of hibernate  makes me think that the business logic is not in ABL but in Java, is  that something that uses the Progress database only as an SQL one, is  that the first case of this kind, and (the obviously) WHY??? "

(Sorry, couldn't figure out how the Quote works)

Yes, we're working with an OpenEdge DB from Java via Hibernate. Not our first choice of DB for this task (and seems justified given the problems we're having). The background is:

- We are a Progress shop with an ERP written in Progress.

- We are replacing an existing ESB with Sonic

- We are loosely coupling the ESB with our ERP (which isn't Progress marketing policy)

- The ESB needs a DB to support Conitnuation, Logging and Message control support

- Our Progress Sales Rep went ballistic when we looked like using another DB for the job.

- It made some sense for the ESB to use an OpenEdge DB because we'll be able to access and maintain it from the ERP.

- No, we are not writing Business logic in Java. The ESB will only communicate with the ERP via messaging.

- We have good reasons for not passing the tasks off to an AppServer

Thanks Marian

The method you suggested appears to have worked. We created a dummy file with a single column and a single record. and modified the Hibernate Dialect.

To insert a record into a file called LogHeader the SQL for the sequence generation is now:

SELECT pub.LogHeader_Sequeuce.nextval from pub.dummyFile

I guess a better solution would be to refer to an OpenEdge dictionary file that we knew would only ever have exactly one record so that the Hibernate Dialect would be robust and not specific to our implementation. Any suggestions?

I note that OpenEdge has a Column type of RECID, although the documentation suggests that this is only for backward compatibility. This sounds to be more like the MySQL auto-generated ID that Hibernate implements as "IDENTITY", rather than "SEQUENCE". We didn't go down that path because:

- We didn't have any idea whether OpenEdge would honour the SQL to update it.

- It would have meant  changing the Dialect to implement IDENTITY support.

If anyone wants to try out that path we's be more than interested in the outcome.

Regards

Steve

SELECT pub.LogHeader_Sequeuce.nextval from pub.dummyFile

have you tried to use only pub.LogHeader_Sequeuce.nextval instead of that select from 'dual'?

not sure how hibernate will build the insert statement but instead of

insert into table values (select seq.nextval from dual, val, val...)

will be something like

insert into table values (seq.nextval, val, val...)

Record IDs are now being generated in steps of 50, which is still a little odd.

how are the sequences defined, there might just have a 'step' set to 50

I note that OpenEdge has a Column type of RECID, although the documentation suggests that this is only for backward compatibility. This sounds to be more like the MySQL auto-generated ID that Hibernate implements as "IDENTITY", rather than "SEQUENCE". We didn't go down that path because:
- We didn't have any idea whether OpenEdge would honour the SQL to update it.
- It would have meant  changing the Dialect to implement IDENTITY support.
If anyone wants to try out that path we's be more than interested in the outcome.

you should never use recid or rowid as identifier... it will blow everything in case of a dump/load.

Yes, we're working with an OpenEdge DB from Java via Hibernate. Not our first choice of DB for this task (and seems justified given the problems we're having). The background is:
- We are a Progress shop with an ERP written in Progress.
- We are replacing an existing ESB with Sonic
- We are loosely coupling the ESB with our ERP (which isn't Progress marketing policy)
- The ESB needs a DB to support Conitnuation, Logging and Message control support
- Our Progress Sales Rep went ballistic when we looked like using another DB for the job.
- It made some sense for the ESB to use an OpenEdge DB because we'll be able to access and maintain it from the ERP.
- No, we are not writing Business logic in Java. The ESB will only communicate with the ERP via messaging.
- We have good reasons for not passing the tasks off to an AppServer

all this means that the OE database is used for persistence by the ESB? which is ESB Sonic or something else?

if that is true then it might make sense for the Progress Sales Rep to use a Progress DB but not as much for me, the ESB will probably require a very fast update rate and I don't think Progress SQL engine is the best choice for that

Solved the mystery of the steps in the sequence:

http://stackoverflow.com/questions/2243091/hibernate-and-strange-behavior-with-db2-sequences

It seems that Hibernate does that as a performance saver. I ran a test inserting one Header and two Detail records:

- The Header ID was 50 after the previous one
- The first Detail ID was 50 after the previous one.
- The second Detail ID was incremented by 1.

The SQL command log shows that Hibernate only did one nextval for the Header and one nextval for the Detail sequences.

This could be something to watch for if we were updating with anything but Hibernate.

Regards

Steve

RECID and ROWID are values which relate to the physical position of the record on disk, not sequential identifiers.

I think the 50 interval comes to support adding batches of records without having to get a new sequence for each one.

Solved the mystery of the steps in the sequence:

http://stackoverflow.com/questions/2243091/hibernate-and-strange-behavior-with-db2-sequences

It seems that Hibernate does that as a performance saver. I ran a test inserting one Header and two Detail records:

- The Header ID was 50 after the previous one
- The first Detail ID was 50 after the previous one.
- The second Detail ID was incremented by 1.

The SQL command log shows that Hibernate only did one nextval for the Header and one nextval for the Detail sequences.

This could be something to watch for if we were updating with anything but Hibernate.

Regards

Steve

The only remaining question is whether we can use a file from the catalogue which will always contain exactly one record, instead of creating our own dummy table.

sedge wrote:
The only remaining question is whether we can use a file from the catalogue which will always contain exactly one record, instead of creating our own dummy table.

I'll go for _Db system table, it should have only one record but just to be safe use top 1

select top 1 * from PUB."_Db"

I'll go for _Db system table, it should have only one record but just to be safe use top 1

select top 1 * from PUB."_Db"

Didn't work but from the Progress SQL Manual

SYSCALCTABLE   A single row with a single column set to the value 100.

so it looks like we can use: SELECT * FROM sysprogress.syscalctable