0

On Db2 11.5 on Linux I have (simplified example) the following table:

CREATE TABLE ADMIN.TAB1 ( ID INTEGER NOT NULL, COL_TEXT CHAR(10), DATE_TIME TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP IMPLICITLY HIDDEN );

Some data:

INSERT INTO ADMIN.TAB1 (ID, COL_TEXT) VALUES (1, 'A'); INSERT INTO ADMIN.TAB1 (ID, COL_TEXT) VALUES (2, 'A'); INSERT INTO ADMIN.TAB1 (ID, COL_TEXT) VALUES (3, 'A');

Display the data:

SELECT ID, COL_TEXT, DATE_TIME FROM ADMIN.TAB1;

Now I am required to update table with:

UPDATE ADMIN.TAB1 SET COL_TEXT = 'B' WHERE ID = 1;

But REQUIREMENT is only column COL_TEXT should be changed. According to "generated always" definition also DATE_TIME column is going to be changed, but it is now one time business requirement that this column should NOT! change, because some application actions are scheduled to executed according to DATE_TIME field and if new data is found in this DATE_TIME column then some actions are performed in some other tables.

I was thinking:

  1. Dropping "generated always".
  2. Do the update.
  3. Add back from "generated by default" to "generated always" --> but can't figure it out how to do this?

How to update table without affecting timestamp column?

Improve this question

1 Answer 1

Reset to default
0

As far as I know, there's no easy way to do it. I don't believe you can alter an existing column to ROW CHANGE TIMESTAMP, but you can ADD a column with it.

You could try (I haven't tested this)

ALTER TABLE ADMIN.TAB1 ALTER COLUMN DATE_TIME DROP GENERATED;
UPDATE ADMIN.TAB1 SET COL_TEXT = 'B' WHERE ID = 1;

Export the data to DEL or IXF format.

DROP COLUMN DATE_TIME;

ALTER TABLE ADMIN.TAB1 ADD COLUMN DATE_TIME TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP IMPLICITLY HIDDEN;

LOAD the data into ADMIN.TAB1 with the the rowchangetimestampoverride modifier

See https://www.ibm.com/support/pages/db2-preserving-existing-timestamp-data-when-moving-tables-which-contain-row-change-timestamp-column

Take a backup of the DB or just the tablespace(s) involved

Alternatively, (also untested)

CREATE TABLE ADMIN.TEMP_TAB1 ( ID INTEGER NOT NULL,
COL_TEXT CHAR(10), DATE_TIME TIMESTAMP NOT NULL );

Insert data from ADMIN.TAB1 into ADMIN.TEMP_TAB1 (INSERT from SELECT, export/import, whatever)
UPDATE the data in ADMIN.TEMP_TAB1 as needed

Export data from original ADMIN.TAB1 in case this this goes pear-shaped, then:

Export data from ADMIN.TEMP_TAB1

LOAD the data exported from ADMIN.TEMP_TAB1 into ADMIN.TAB1 with the the rowchangetimestampoverride modifier

Take a backup of the DB or just the tablespace(s) involved

DROP TABLE ADMIN.TEMP_TAB1

Improve this answer
1
  • Greg, thank you for your answer. This is exactly what I have found in the IBM official article: ibm.com/support/pages/… The only option like it looks like is using "load". Ordinary SQL-s will not work and also "import" will not work (because it is using the same interface as ordinary SQLs). By the way, I have also found article, but this is for ORACLE (not Db2 that I need): dermotoneill.blogspot.com/2017/04/… It looks like Db2 limitation. Commented Jun 13, 2024 at 5:32

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.