Auto Generate Primary Key

[Raj Mehta]

I am working on DB2 database, which doesnt have support for Auto Increment prirmary keys.

Is there a standard solution to this problem when inserting a new record in the DB using CMP EJB.

[dimple sharma]

Hi,

u can use Select query to get Max of that column and then add +1 in that that value to generate next id. As it is select query, u hv ejbSelect abstract method in bean if u r using CMP, in case BMP u have to write ur own method to select max. Select query is fast bcoz it is not running in pool and they never exposed to a client. They can returns entities and value of cmp fields.

I think it would solve ur problem.

regards

[Eric Schumacher]

A few DB's I have worked with have a separate table to keep other table 'sequences'. The sequence table has a row for each table with a column for the sequence or key. Each time a new record is added to one of the tables the application increments the sequence number for that table in the sequence table. I'm not sure if this is actually faster than doing a count, but I think it probably would be. The added benifit is that you don't mess up your keys when someone deletes some rows. If you use the count method you could have duplicates.

[dimple sharma]

Hi,

ejbSelect is always faster than ejbFind method. If you have separate table then you might have to be write ejbFind method and in your session bean first you have to look up that bean ond have to call find method. Instead ejbSelect give you fast result as it don't need to have look up and morevore it's not runnning in pool.

As far as duplication concern you have to use MAX keyword not COUNT.

Regards

[Eric Schumacher]

Yes, My bad. I still wonder about MAX() with regards to performance on very large tables. Additonally there could be concurency issues when using MAX() in this way. You will need to lock for the MAX() and any insert or you could have bad data..how much time between might cause issues.

Check out an example for using sequences:

http://dbforums.com/showthread.php?threadid=570125&highlight=SEQ_NAME