How do I get the next value that will be used on an IDENTITY column

views:

211

answers:

+3 Q:

How do I get the next value that will be used on an IDENTITY column

I am using DB2 v9 on LUW.

I have a column defined like this:

"ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),

I would like to know the best way to determine what the next value will be for the ID column next time a record is inserted into the table.

I will use this information to write a script to do a "sanity" check on the table that IDENTITY is still intact and that its next value is one greater than the highest value in the ID column.

I do not want to just reset the value blindly. If the table does not pass the sanity check I want to be notified so I can determine what is causing the IDENTITY to be "wacked".

You cannot determine the next identity. Even if you could you run the risk of the data being out of sync by the time you try to create a new record. The only thing to do is to create a new record and get the new identity, do your check, and then update the record with the rest of the data.

You could use SELECT IDENT_CURRENT('yourtablename') to get the last one generated. This has the same caveat as the one above. That works in T-SQL, not sure in DB2 flavor.

ddill65 2010-03-16 17:53:25

I don't think the previous identity will work because in the case of a fresh load there may not have been a previous identity. Unless a reseed also sets previous identity?

Michael Potter 2010-03-16 18:21:14

Your idea of creating a new record might work. I do not want to populate the record though. This is just a check and not involved in creating records. I could create a new record, get the value of the identify column, then do a rollback. Does anyone know if doing a rollback also does a rollback on the seed value? I would rather not have a seed value increment every time I run the test.

Michael Potter 2010-03-16 18:23:59

This is the way to go. Don't worry about the gaps. Even with an unsigned 32-bit integer, you can insert 1000 rows per second, 24 hours a day, for 136 years before running out.

Marcus Adams 2010-03-17 22:17:31

+1 A:

I don't think this will work as you expect. Consider the case where an row is inserted, then before another row is inserted, that row is deleted. At that point, the autogenerated id will be (at least) 2 greater than the highest value in the DB AND it will be correct. If you can guarantee that no deletes take place, it might work, but I'm not sure what use it would be.

Essentially, you're checking if the very basic operations of the DB software are working and, if they aren't, what are you going to do? Change vendors?

If the case is that you simply want to reseed the identity column, then do a select max(id) and reseed the column within the same transaction. You can be sure that no new records are inserted while the column is being reseeded by enforcing serializable isolation level transaction semantics.

tvanfosson 2010-03-16 17:55:14

I am not checking if the the basic operations of DB2 are working. An example of what I might catch is an improperly executed load operation. I can run my script every day before beginning of business. If I find a problem, then I can track down who/what was changing the affected table and have a discussion of the proper way to load data. I will be happy to get a few false positives for the case of data being deleted. If I get too many false positives I can just test for a bigger range than 1.

Michael Potter 2010-03-16 18:16:36

@Michael - so what you're saying is that someone might turn on identity insert, then forget to reseed the column to set it higher than the last id they inserted afterwards? You could test this by simply reading the max id value, doing an insert with "valid" data, checking if it succeeds or fails and has an id greater than the one you observed, then deleting that row -- all within a single transaction. This would introduce a few "empty" ids in the sequence, but would detect the sort of problem I've described.

tvanfosson 2010-03-16 18:21:56

Yes, that is type of problem I am trying to trap. I am hoping someone has a solution that will not create empty IDs. It would not be the end of the world if I ended up with a empty ID every time I ran the script, but ideally I would like to avoid that.

Michael Potter 2010-03-16 20:17:20

If ID column is set to GENERATED BY ALWAYS you would not have a problem with improper load/import. Also, IDENTITY_VAL_LOCAL function can be used to get the identity value.
More about this function here

Peter 2010-03-22 19:32:22

ansaurus

tags:

views:

answers:

How do I get the next value that will be used on an IDENTITY column

related questions