2

I would like to append two timestamp columns to an existing table. CREATED_TSTMP would be populated with the current timestamp when a record is inserted and LAST_UPD_TSTMP would be updated automatically when a record in the table gets updated.

I would like to do this without having to modify existing queries.

I have the following DDL statements:

ALTER TABLE XXX ADD CREATED_TSTMP TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ;
alter table XXX add column LAST_UPD_TSTMP timestamp not null generated by default for each row on update as row change timestamp ;

However once the columns are appended, this will cause an existing query with the following syntax:

INSERT INTO XXX VALUES(?,?,?,?,?,?,?,?,?,?)

to fail with

The number of values assigned is not the same as the number of specified or implied columns or variables..

Is there any way around this problem without having to inspect all of the existing queries (there are hundreds of them...)

soulfly1983
  • 177
  • 1
  • 8
  • Generally speaking, you shouldn't include the type in column names - it's the "bad" form of Hungarian notation. In particular, if the type changes, things can get.... strange. Here, you should likely be using something like `created_At` and `last_updated_at` (you also shouldn't be abbreviating if you don't need to). – Clockwork-Muse Jul 22 '14 at 06:58
  • Good points, thanks :-) – soulfly1983 Jul 22 '14 at 09:59

1 Answers1

3

If you add the IMPLICITLY HIDDEN option when creating the columns, they will be ignored by the SQL statements unless you explicitly mention these columns.

PS. I'm assuming you are on a sufficiently recent version of DB2, because of your using row change timestamp. To avoid ambiguity, you should indicate the DB2 version and platform in question.

mustaccio
  • 18,234
  • 16
  • 48
  • 57