CODEWITHSUNDEEP
sqldb2
Levijatanu
Levijatanu

Reputation: 418

Autogenerated timestamp column( primary key) to existing table DB2

Is it possible to add an autogenerated primary key column ( timestamp) to the existing table with alter table?

Something like this but it doesn't compile

ALTER TABLE DB2ADMIN.xxxyyyy ADD COLUMN ID TIMESTAMP NOT NULL WITH DEFAULT timestamp(generate_unique())@

Error during Prepare 42601(-104)[IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "timestamp" was found following "OT NULL WITH DEFAULT". Expected tokens may include: "CHECK". SQLSTATE=42601

Upvotes: 1

Views: 1796

Answers (2)

mao
mao

Reputation: 12267

It is unwise to use a fake (from generate_unique) timestamp datatype as a primary key because it makes setting values for pre-existing rows more awkward, and makes date arithmetic impossible.

The datatype TIMESTAMP is better suited for real dates/times because then you can use date arithmetic, which is practical for business. If the values in your fake timestamp-column are from generate-unique then you cannot sensibly use date arithmetic.

If you try to use a real timestamp value , (instead of generate_unique) , such as current timestamp then you are likely to get collisions, depending on the insert-rate. Usually that's a bad idea. Also this makes setting values for any pre-existing rows more difficult.

It is usually much easier and faster to use an autogenerated identity column as a surrogate primary key, especially if the table already has existing data.

Here is a typical way to do this which works with Db2-LUW and also on older versions of Db2. Other ways are possible with later versions of Db2.

Firs you need to verify that the table does not already have a primary key, as there can only be at most one of these per table.

Next, check if the table already has a unique index on a NOT NULL column, because if such a column exists then it can be promoted to be the primary key column.

If neither of the above exist, then you can use logic like this below to add an autgenerated column, set unique values in any existing rows, and ensure that any future inserts automatically get a unique value in the column without application intervention.

alter table myschema.mytab add column id bigint not null default 0 ;

alter table myschema.mytab alter column id drop default ;

alter table myschema.mytab alter column id set generated always as identity ;

update myschema.mytab set id = default ;

alter table myschema.mytab add constraint pkey primary key(id) ;

reorg table myschema.mytab ;

runstats on table myschema.mytab with distribution and detailed indexes all;

Upvotes: 4

Brahim Ha
Brahim Ha

Reputation: 11

You can use CURRENT_TIMESTAMP instead of timestamp(generate_unique())

ALTER TABLE sellers ADD COLUMN ID TIMESTAMP NOT NULL WITH DEFAULT CURRENT_TIMESTAMP

You can test here

Upvotes: 0

Related Questions