Hi,
on "DB2 Enterprise v8.2 FP11" on Linux I would like to create such a identity column that would prevent having missing numbers. For example: identity column should have values 1, 2, 3, etc and not 1, 3, 4 etc (missing 2).

Sample:

application 1:
db2 create table admin.tab (col1 int not null GENERATED BY DEFAULT AS IDENTITY, col2 int)
db2 +c insert into admin.tab values (default, 100)

application 2:
db2 +c insert into admin.tab values (default, 200)
db2 +c commit

application 1:
db2 +c rollback

Executing select on table returns the following data:
COL1 COL2
----------- -----------
2 200

Application 1 rolled back so I have a missing number 1 in table. Requirement in my case is to have no missing values. This probably means that no insert should be allowed by application 2 before application 1 executes commit or rollback.

Is there any way I could make this numbering without missing numbers in identity columsn?

Thanks,
Grofaty

I don't think it is feasable to do that with identity columns if you are concerned about rollbacks. You can just use a next-available-number table and make the updating of that in the same unit of work as the insert.

Another problem with identity columns is that cache values are lost if the database is deactivated. So you would want to set the cache to zero.

A next-available-number table has the drawback that it pretty much serializes all transactions that need this table. And that is rather bad performance-wise. (Any decent database admin will be seriously tempted to shoot you for doing things like that.) That's one of the reasons why sequences and identity column were introduced.

But let's step back a little: Grofaty, why do you believe you have a "requirement" to not have missing values? Is it really a hard requirement or just a "I would like to have it that way, but technically it doesn't matter at all" thing?

Hi,
it is hard requirement, because law in over country restricts there should be no missing values for example account system that needs strict sequence with no missing values.

For now this kind of problem is solved to have two tables. One has account data and one table has one-record table with max account value. When new account appears then this one-record table is locked new account is inserted into account table and one-record table has max+1 number then commit is executed.

This way of solving problem has a performance impact because transactions has to wait to unlock one-record table.

Any idea how to make this problem solved. Identity is good choice but no missing values is requirement.

P.S. Can you please point me out to some document about "next-available-number table"? I haven't heard for this functionality yet.
Thanks,
Grofaty

The following should do that:So no need for an identity column. If the first column is indexed, this query should be sufficiently performant.

Peter,
this is good solution but it has one weakness: how to find out which value I have inserted into table.

My problem is little more complex. I need to insert new identity value and get info about the value and in the same unit of work I need to update another table with this value.

So if using identity I can get this value by executing command:
SELECT INT(IDENTITY_VAL_LOCAL()) FROM SYSIBM.SYSDUMMY1
or VALUES INT(IDENTITY_VAL_LOCAL())

Peter, is there any simple way I could get info about max(col1) info from insert with select command?
Thanks,
Grofaty

Select COL1 from final table
(INSERT INTO admin.tab
SELECT max(col1) + 1, 300
FROM admin.tab)

This assumes COL1 is the PK of the inserted table that you want the value for. You can include any of the columns in the table (after the insert takes place) in the select.

Indeed, this is the only way to do this.
"select from insert" can of course also return any other column from the just inserted row(s).

Unfortunately, this does not take into account concurrent transactions, which may generate duplicate keys or gaps when using such a scheme.

I think the only reliable way to ensure generation of a unique key without gaps is to use a key table where the record corresponding to the next key value is locked during the entire transaction. Further to that, there may also be a problem of deleted key values, which will require renumbering. Clearly this creates the performance bottleneck, but that's the price you pay for such a requirement.

n_i, can you please provide more info about why this solution is not good? I have tested it from db2cmd and it looks like it does what I want

application 1:
db2 create table admin.tab (col1 int not null primary key, col2 int)
db2 +c select col1 from final table (insert into admin.tab select max(col1) + 1, 300 from admin.tab)

application 2:
db2 +c select col1 from final table (insert into admin.tab select max(col1) + 1, 300 from admin.tab)

application 1:
db2 +c rollback

application 2:
db2 +c commit

Application 2 gets the right numbering with no gaps. Am I missing something?

BTW, delete is not a problem, because there should be no delete statement (law requirement).

Regards,
Grofaty

Try committing application 1 instead of rolling back.

I'm still with Knut's first response about the "HARD" requirement. Are you sure this isn't being misinterpreted here? This sounds uttlerly ridiculous to have to jump through so many hurdles. We were just sitting in office discussing your situation and we all find it very hard to believe that lawmakers in some country have put it forth that every number in a table of data that is owned by a company all have sequential numbers with none missing. If it is truly the case then your lawmakers really need more to do.
Dave

Much will ultimately depend on that part of the requirements to which we're not privy.

For example, the ID in question is a bank account number, and these numbers are required to be consecutive. However, only an average of 30 accounts are opened each day, in which case it is perfectly acceptable from the concurrency point of view to use the key table solution.

Or may be these are online order numbers, which also must be consecutive. Orders come at the rate of 50 per second at peak times but the order consolidation job and reports run once per day, at 18:30. In such case you could assign internal order IDs from a sequence and keep a separate table containing pairs of the internal order IDs and "lawful" order numbers. That latter table is updated daily, just before the batch run, to generate consecutive "lawful" order numbers for those orders created during the day. No concurrency issues and just a slight delay for the batch.

Hi,
I have tried this and application 2 gets number of application 1 risen by one. I can't see the problem here. Can you please write how do you see problem?
Regards,
Grofaty

dav1mo, this gap-free numbering is required at billing-system. So bills has to be numbered without gaps. If some bill has a mistake the record from table must not be deleted but "reversal event" has to appear. So there always has to be trace what happened with no-valid bills.

So if there would be bill numbers 1, 2, 3, 5, etc (missing 4) tax-government-man would ask what happened with bill no. 4? Are you trying to avoid country tax by deleting bill no. 4 and not paying the tax? If this bill was "reversal" tax-man can check at customer if this bill was really reversed or was just a tax-avoidance.

So if tax-government-man comes to inspection he/she checks all the bills in particular year and checks if all taxes have been paid. Missing numbers of bills would tell tax-government-man that there is high possibility tax was avoided.

Hope this helps explaining numbering without gaps.

So my requirement is not gap free numbering in all cases but in some special cases like billing that a law says there should be no gaps.

Hope this helps explaining. By the way are in your country bills order numbers not required to have no gaps? Don't you have difficulties to persuade tax-man what happened with missing bills?

Regards,
Grofaty