If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > How to add generated timestamp column definition on existing table?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,098
I think what you need is this:

Code:
ALTER TABLE ADMIN.TAB3
    ALTER COLUMN TS
    SET GENERATED ALWAYS AS ROW BEGIN
Andy
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
===========
Reply With Quote
  #4 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,333
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On