troubleshooting Question

DB2 11.5 Change GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP column

Avatar of Jim Youmans
Jim YoumansFlag for United States of America asked on
DB2SSIS
12 Comments1 Solution17 ViewsLast Modified:
DB2 11.5 on Windows.

We added this column to most of our tables to see when last update was made for a replication project in SSIS.  
DW_LASTTIME   TIMESTAMP   NOT NULL   GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

Open in new window

The project died on the vine and I just found out that my online reorg script has been failing with this error.
Online Reorg fails with SQL2219N Reason code: "13"
https://www.ibm.com/support/pages/online-reorg-fails-sql2219n-reason-code-13

Open in new window

I can't start doing offline reorgs due to the nature of the business so I would like to change that column to just a regular timestamp or date or varchar.
ALTER table TESTTABLE  ALTER COLUMN DW_LASTTIME SET DATA TYPE VARCHAR(30);

Open in new window

But when I try I get
ERROR [42837] [IBM][DB2/NT64] SQL0190N  ALTER TABLE "AE" specified attributes for column "DW_LASTTIME" that are not compatible with the existing column.

Open in new window

I know I can drop it but I was trying to be less intrusive.  Is there any way to change this sort of dropping the column?
Thanks!
Jim
ASKER CERTIFIED SOLUTION
Tomas Helgi Johannsson
Database Administrator / Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 12 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 12 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004