CODEWITHSUNDEEP
sqldatabasedb2db2-400
Davide Briscese
Davide Briscese

Reputation: 1563

DB2/400 - Auto generated timestamp on change (error)

I'm trying to create a table with a timestamp column that autogenerates with 'current timestamp' on each update of the record. I'm on DB2/400 (version V5R3) using ODBC driver.

That's the query:

CREATE TABLE random_table_name ( 
ID                   integer not null generated always as identity,
USERS_ID             varchar (30),
DETAILS              varchar (1000),
TMSTML_CREATE        timestamp  default current timestamp ,
TMSTMP_UPDATE        timestamp not null generated always for each row on update as row change timestamp,
PRIMARY KEY ( ID )

)

I get this error (translated):

ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token EACH not valid. Valid tokens: BIT SBCS MIXED.

Without the 'TMSTMP_UPDATE' row the query works. How can i solve this?

EDIT: Ok, i understand that in my DB2 version, the only way is to use triggers, but today AS400 seems to be evil with me. I'm trying with this:

CREATE TRIGGER random_trigger_name
AFTER UPDATE ON random_table_name
REFERENCING NEW AS NEW_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    SET NEW_ROW.TMSTM_UPDATE = CURRENT TIMESTAMP;
END

Error (translated): 

ERROR [42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0312 - Variable TMSTM_UPDATE not defined or not available.

The column TMSTM_UPDATE exist and it's a normal timestamp.

EDIT 2: I've solved the trigger problem by replacing 'after' with 'before'. Now everything works as expected. Thank you all!

Upvotes: 2

Views: 1231

Answers (1)

Mita
Mita

Reputation: 422

There is a standard way to do it in iSeries DB2. It is documented here: IBM Knowledge center - Creating a row change timestamp column

You should change your table definition to:

TMSTMP_UPDATE TIMESTAMP NOT NULL FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

I am using it in tables in production over V7R2 and it works like a charm :) Hope it will be available for V5R3

EDIT

As Charles mentioned below unfortunately this feature is available since DB2 for i V6R1

Upvotes: 1

Related Questions