AUTOINCREMENT without the DEFAULT

Question: How do I use DEFAULT AUTOINCREMENT for a legacy table I can't change?

It's common to encounter a certain [cough] reluctance to modify the schema of tables that have been around forever and ever. Sometimes, it's almost impossible... you can modify the application code to your heart's content, even add new tables, but not modify any of the old ones... even if you're suffering from a performance problem.

For example, let's say there are concurrency problems caused by a flawed primary key generation process, and you want to use DEFAULT AUTOINCREMENT to fix those problems, but you can't.

Answer: It is possible to obtain the concurrency benefits of DEFAULT AUTOINCREMENT without modifying the schema; you use empty shadow tables instead, and call GET_IDENTITY().

First, here are a couple of legacy tables t1 and t2, followed by corresponding shadow tables:

CREATE TABLE t1 ( pkey INTEGER NOT NULL PRIMARY KEY, data INTEGER NOT NULL ); CREATE TABLE t2 ( pkey INTEGER NOT NULL PRIMARY KEY, data INTEGER NOT NULL ); CREATE TABLE empty_shadow_t1 ( pkey INTEGER NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT ); CREATE TABLE empty_shadow_t2 ( pkey INTEGER NOT NULL PRIMARY KEY DEFAULT AUTOINCREMENT );

Here's how the application code can be modified to call GET_IDENTITY() before each INSERT:

BEGIN DECLARE @pkey1 INTEGER; DECLARE @pkey2 INTEGER; SET @pkey1 = GET_IDENTITY ( 'empty_shadow_t1', 1 ); INSERT t1 VALUES ( @pkey1, 0 ); COMMIT; SET @pkey2 = GET_IDENTITY ( 'empty_shadow_t2', 1 ); INSERT t2 VALUES ( @pkey2, 0 ); COMMIT; SET @pkey1 = GET_IDENTITY ( 'empty_shadow_t1', 1 ); INSERT t1 VALUES ( @pkey1, 0 ); COMMIT; SET @pkey2 = GET_IDENTITY ( 'empty_shadow_t2', 1 ); INSERT t2 VALUES ( @pkey2, 0 ); COMMIT; SET @pkey1 = GET_IDENTITY ( 'empty_shadow_t1', 1 ); INSERT t1 VALUES ( @pkey1, 0 ); COMMIT; SET @pkey2 = GET_IDENTITY ( 'empty_shadow_t2', 1 ); INSERT t2 VALUES ( @pkey2, 0 ); COMMIT; SELECT * FROM t1 ORDER BY pkey; SELECT * FROM t2 ORDER BY pkey; END;

If that code is executed twice, by two different connections, the tables look like this:

pkey data ----------- ----------- 1 0 2 0 3 0 4 0 5 0 6 0 pkey data ----------- ----------- 1 0 2 0 3 0 4 0 5 0 6 0

Here's how SET @pkey1 = GET_IDENTITY ( 'empty_shadow_t1', 1 ); works:
xxx

• It uses the DEFAULT AUTOINCREMENT specification on the column empty_shadow_t1.pkey to generated the next auto-increment value.
  

• It modifies the corresponding SYSTABCOL.max_identity column value to reflect the fact that generated value has been reserved.
  

  SELECT SYSTAB.table_name, SYSTABCOL.column_name, SYSTABCOL.max_identity FROM SYSTAB INNER JOIN SYSTABCOL ON SYSTAB.table_id = SYSTABCOL.table_id WHERE SYSTAB.table_name LIKE 'empty%' ORDER BY SYSTAB.table_name; table_name column_name max_identity ----------------- ----------------- ----------------- empty_shadow_t1 pkey 6 empty_shadow_t2 pkey 6

• It returns the generated value as the GET_IDENTITY() result.
  

After that's done, @pkey1 contains the value for the INSERT, and empty_shadow_t1 is still empty.

Which is a good thing, because most DBAs don't mind it if you ask for an empty table :)