Link to home
Start Free TrialLog in
Avatar of Jim Youmans
Jim YoumansFlag for United States of America

asked on

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

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
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Hi Jim,

You can't change a TIMESTAMP column to VARCHAR(n) or vice versa due to the different nature of the data types. The only thing that you could do is either a OFFLINE reorg or DROP the column following a reorg.
What version of 11.5 are you on? GA or have you applied any fixpacks ?

Regards,
    Tomas Helgi

Avatar of Jim Youmans

ASKER

if I drop the column I would imagine I would need to do a reorg and then runstats afterwards, correct?  I have not applied any Fix Packs yet.

Jim
ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Jim,

You've been quiet lately.  :)   That's probably a good thing!!!

Is there a primary key on the column?  If so, you might try copying the table to another table (where DW_LASTTIME is just a timestamp with no special actions).  They you should be able to drop the column, do whatever you need, recreate the column, and populate it from the saved data.

That may also be more trouble than it's worth.....
I have been teaching myself MySQL on Linux.  Different world.

I don't need that column any longer so I will just drop it, the the online reorg, and runstats.

Not hard but on a prod system I hate to do that kind of stuff unless I have to.

Thank you and thanks Tomas!!!!
MySQL is indeed a different world.  There's a lot to like in their interpretation of the SQL though.  It takes some getting used to, but I think you'll come to like it!
The MySQL is not bad and it has some nice features and some really dangerous ones.  You can set it to insert a 500 character sting into a VARCHAR(50) field and it won't even blink.  It will just truncate the string and keep going.  Ran into that during an upgrade.  The developer told me "it is not the database's job to verify data, just store it".  I had a fit.
But I am learning to like Linux, believe it or not.  Much different than what I am use to (Windows).
I get a chuckle out of some of the people I chat with about UNIX.  I've now got 38 years of it on my resume.  People think I'm making it up.  :)

(Yeah -- I'm an old fart....)

Check out aggregation in MySQL.

SELECT user, date, Organization, sum(gross_pay) FROM sometable
GROUP  BY user;

DB2, Oracle, and SQL Server will stick their tongues out at you and rebel.  MySQL will say, "OK" and show you the results.  :)
YES!  I found that in settings also.  Blows my mind.  You have about 10 years on me but we are not "old farts".  I prefer "seasoned veterans".  :)
Hi,
Have a look at MariaDB (fork of MySQL).
I find that DBMS way better than MySQL.
Also take a look at the SQL_modes that you can set in MySQL/MariaDB to make queries such as this
SELECT user, date, Organization, sum(gross_pay) 
FROM sometable
GROUP  BY user;

Open in new window

invalid. Note that MariaDB has some modes set by default in 10.x that MySQL does not.
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
https://mariadb.com/kb/en/sql-mode/

Best regards,
    Tomas Helgi
We are stuck with what we have.  But thank you!!!
Avatar of waynezhu
waynezhu

FYI: GENERATED BY DEFAULT presents challenge to inplace reorg. For GENERATED BY ALWAYS, inplace reorg will work fine.
In addition, do not have to drop the column. Reorg can be done online via admin_move_table which outperforms inplace reorg nicely.