Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    1,595

    How to add generated timestamp column definition on existing table?

    Hi,
    using DB2 v10.1 on Windows I would like to create automatically generated timestamp column for exiting table.
    I tried many things but can't figure it out if this is supported or not. I have looked at official 'alter table' syntax but it looks like this is not supported: IBM DB2 9.5 Information Center for Linux, UNIX, and Windows
    I did several tests...

    Test 1 - create generated timestamp column when table is created (works fine):
    Code:
    CREATE TABLE ADMIN.TAB1
    (
    ID INT NOT NULL,
    TS TIMESTAMP NOT NULL
        GENERATED ALWAYS FOR EACH ROW
        ON UPDATE AS ROW CHANGE TIMESTAMP
    )
    ;
    Test 2 - create table without timestamp column and then add column in alter table command (works fine):
    Code:
    CREATE TABLE ADMIN.TAB2
    (
    ID INT NOT NULL
    )
    ;
    
    ALTER TABLE ADMIN.TAB2
        ADD COLUMN TS TIMESTAMP NOT NULL
        GENERATED ALWAYS FOR EACH ROW
        ON UPDATE AS ROW CHANGE TIMESTAMP
    ;

    Test 3 (I need this) - create table with timestamp column and then alter column generation by alter table:
    Code:
    CREATE TABLE ADMIN.TAB3
    (
    ID INT NOT NULL,
    TS TIMESTAMP NOT NULL
    )
    ;
    
    ALTER TABLE ADMIN.TAB3
        ALTER COLUMN TS
        SET GENERATED ALWAYS FOR EACH ROW
        ON UPDATE AS ROW CHANGE TIMESTAMP
    ;
    Note: Above alter table command returns error:
    ==========
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "ALTER TABLE ADMIN.TAB3 ALTER COLUMN TS SET" was
    found following "BEGIN-OF-STATEMENT". Expected tokens may include:
    "<values>". SQLSTATE=42601
    ==========

    Is there a way to create a table including timestamp column and then with alter table just assign 'generation' syntax (the same way as generating primary key identity)?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,130
    I think what you need is this:

    Code:
    ALTER TABLE ADMIN.TAB3
        ALTER COLUMN TS
        SET GENERATED ALWAYS AS ROW BEGIN
    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    1,595
    @Andy, suggested command returns error:
    ===========
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0683N The specification for column, attribute, user-defined type or
    function "TS" contains incompatible clauses. SQLSTATE=42842
    ===========

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,431
    Quote Originally Posted by grofaty View Post
    ===========
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0683N The specification for column, attribute, user-defined type or
    function "TS" contains incompatible clauses. SQLSTATE=42842
    ===========
    In many cases one can benefit from reading the error explanation provided by DB2. In this particular example it contains this note that you may find useful:

    " For a CREATE or ALTER TABLE statement, "<data-item>" for a ROW BEGIN,
    ROW END, or TRANSACTION START ID column must be TIMESTAMP(12)."
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2003
    Posts
    1,595
    n_1, ROW BEGIN, ROW END or TRANSACTION START ID require TIMESTAMP(12), I do agree, but this functionalites you suggest is for "time travel" tables - new feature in v10 e.g. https://www.ibm.com/developerworks/d...ata/index.html

    But, I am NOT!!! using any kind of this new time-travel functionalities, I am just using a v9.5 feature of getting AUTOMATICALLY GENERATED TIMESTAMP column when row is inserted/updated.

    As stated in my first post, there are two options available to add new column with auto-generated timestamp column, but third option - adding column with create table and in separate alter table alter column add auto-generation - it looks like it is not supported by DB2. This is little bit surprising to me, because creating a column and then specify auto-generated identity for primary key purposes can be added with alter table command.

    P.S. I would like to see a working sample of SQL code.
    Last edited by grofaty; 08-13-12 at 02:53.

Posting Permissions

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