Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    2

    Unanswered: alter a row change timestamp column

    DB2 9 for z/OS

    I have a table with a ROW CHANGE TIMESTAMP column on it that was defined as GENERATED BY DEFAULT. Is there anyway to alter this column and change it to GENERATED ALWAYS? I thought the following might do it:

    ALTER TABLE "TDKGROUP"
    ALTER COLUMN "SYS_GEN_TMSTMP" SET GENERATED ALWAYS;

    But I received the following:

    DSNT408I SQLCODE = -20180, ERROR: COLUMN SYS_GEN_TMSTMP IN TABLE
    ASLDB2.TDKGROUP CAN NOT BE ALTERED AS SPECIFIED

    I think this alter may only work on generated identity columns. Any suggestions? I'm trying to avoid dropping the table.
    Last edited by gwilper; 09-10-10 at 17:07.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    gwilper, I am not sure you have any other choice. According to Info center topic DB2 V9.1 - DB2 SQL - ALTER TABLE:

    A column cannot be altered if any of the following conditions are true:

    The column is defined as a row change timestamp column

    You will have to scroll down a bit to get to the text on ALTER COLUMN column-alteration.

  3. #3
    Join Date
    Jul 2009
    Posts
    150
    You have to define column SYS_GEN_TMSTMP Timestamp not Null with default.

    Then, when you insert values into TDKGROUP use this simple query:

    Insert Into TDKGROUP
    (other columns, SYS_GEN_TMSTMP)
    values(other values, timestamp(generate_unique))
    Could be also something like this (DB2 V9):

    SYS_GEN_TMSTMP NOT NULL
    GENERATED ALWAYS FOR EACH ROW ON UPDATE
    AS ROW CHANGE TIMESTAMP
    Kara
    Last edited by DB2Plus; 09-11-10 at 02:19.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •