Re: DB2 SQL table change efficiency

On 10/21/2014 2:55 PM, Matt Olson wrote:

Anyone know of a product in DB2 that allows for easier table changing?

If you have a table with a character column that contains dates and I want to change the fields to actual DATE columns it doesn't work using Alter Table.

In Microsoft SQL it is a couple click affair, change column type and hit the save button and it will retain all data. I think it does all these developer efficiency things in the background:


1. Renames table to some temporary name

2. Recreates the table with new data types, fields, etc

3. Copys all the data from the old table to the new table

4. Removes the temporary table

It seems in DB2 in IBM i I lose all these efficiencies?

I don't know of a tool that will do this, but I also don't know of a
tool that will do this... with my data. I have 'dates' stored in
numeric columns and some of them are MMDDYY, some are YYMMDD. Some are
zero and some are pure garbage, taken raw from whatever system my
trading partners use to (not) validate dates (at all). If I had a tool
that would convert from this helter-skelter mess, what would happen to
the columns with unusable dates?

The first time I encountered this problem, I was in fact tempted to
write a tool - it's only a few lines of code to convert from MMDDYY to
*ISO. After a few passes through the table I quickly realised that I
was going to need some reporting in order to get the data cleaned up
before a conversion was attempted. The particulars of the reporting
depend quite a lot on the table. Sure, I could print the primary key
and the raw contents of the 'date' column, but that usually isn't enough
for someone to address the problem record. They want the name and
address, or the product number and description... you get the idea.