Discussion:
DB2 SQL table change efficiency
(too old to reply)
Matt Olson
2014-10-21 18:55:55 UTC
Permalink
Raw Message
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?

Anyway to bring this automation back using some tool?

Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
r***@public.gmane.org
2014-10-21 19:23:21 UTC
Permalink
Raw Message
Sounds like you're trying to do

CREATE TABLE ROB/MO (MYDATE CHAR (10 ) NOT NULL WITH DEFAULT)

INSERT INTO ROB/MO (MYDATE) VALUES('2014-10-21')

alter table rob/mo
alter column mydate set data type date

Message ID . . . . . . : SQL0190 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : Attributes of column MYDATE in MO in ROB not
compatible.
Cause . . . . . : The attributes specified for column MYDATE in MO in
ROB
are not compatible with the attributes of the existing column. Either
the
data type, the length, or the clause is not valid.
-- A character column cannot be changed to a DATE, TIME, TIMESTAMP, or a
numeric column.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Matt Olson <Matt.Olson-***@public.gmane.org>
To: "midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org" <midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Date: 10/21/2014 02:56 PM
Subject: DB2 SQL table change efficiency
Sent by: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>



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?

Anyway to bring this automation back using some tool?

Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
r***@public.gmane.org
2014-10-21 19:37:24 UTC
Permalink
Raw Message
It could be done by doing something like
create table convert as (
select cast(charToDate(mydate) as date) as mydate

or something like that.
Where charToDate is some function you have that handles nulls, poorly
formatted or invalid dates, etc.
Rebuilding indexes, views, etc becomes your responsibility.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: rob-***@public.gmane.org
To: Midrange Systems Technical Discussion <midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Date: 10/21/2014 03:23 PM
Subject: Re: DB2 SQL table change efficiency
Sent by: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>



Sounds like you're trying to do

CREATE TABLE ROB/MO (MYDATE CHAR (10 ) NOT NULL WITH DEFAULT)

INSERT INTO ROB/MO (MYDATE) VALUES('2014-10-21')

alter table rob/mo
alter column mydate set data type date

Message ID . . . . . . : SQL0190 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : Attributes of column MYDATE in MO in ROB not
compatible.
Cause . . . . . : The attributes specified for column MYDATE in MO in
ROB
are not compatible with the attributes of the existing column. Either
the
data type, the length, or the clause is not valid.
-- A character column cannot be changed to a DATE, TIME, TIMESTAMP, or a
numeric column.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Matt Olson <Matt.Olson-***@public.gmane.org>
To: "midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org" <midrange-l-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>
Date: 10/21/2014 02:56 PM
Subject: DB2 SQL table change efficiency
Sent by: "MIDRANGE-L" <midrange-l-bounces-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org>



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?

Anyway to bring this automation back using some tool?

Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Buck Calabro
2014-10-21 19:24:58 UTC
Permalink
Raw Message
Post by Matt Olson
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.
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.
--
--buck

'I had nothing to offer anybody except my own confusion' - Jack Kerouac
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Charles Wilt
2014-10-21 20:32:24 UTC
Permalink
Raw Message
Are you sure about that?

Per the docs, http://msdn.microsoft.com/en-us/library/ms190273.aspx, ALTER
TABLE requires "The previous data type must be implicitly convertible to
the new data type."

Charles
Post by Matt Olson
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
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?
Anyway to bring this automation back using some tool?
Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Matt Olson
2014-10-22 20:06:37 UTC
Permalink
Raw Message
Yes, as long as there is not bad data it succeeds. Thus the "must be implicitly convertible" succeeds.

Now if I had bogus dates, it doesn't succeed.



-----Original Message-----
From: Charles Wilt [mailto:charles.wilt-***@public.gmane.org]
Sent: Tuesday, October 21, 2014 3:32 PM
To: Midrange Systems Technical Discussion
Subject: Re: DB2 SQL table change efficiency

Are you sure about that?

Per the docs, http://msdn.microsoft.com/en-us/library/ms190273.aspx, ALTER TABLE requires "The previous data type must be implicitly convertible to the new data type."

Charles
Post by Matt Olson
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
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?
Anyway to bring this automation back using some tool?
Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
a moment to review the archives at
http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Raul A. Jager W.
2014-10-22 14:21:33 UTC
Permalink
Raw Message
En easy one-step-change will require all character fields to contain
valid dates, often very unlikely.

You can add a date field before the char, convert and copy then delete
the char
If conversion fails, you will need to clean your char-dates.

The i is more strict with errors, other systems are more tolerant, and
less reliable.
Post by Matt Olson
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.
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?
Anyway to bring this automation back using some tool?
Thanks
-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
CRPence
2014-10-22 15:34:25 UTC
Permalink
Raw Message
Post by Matt Olson
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.
Huh. I figured that restriction would have been removed by now; even
if only with forcible loss [per DROP] of any dependents.

FWiW, if all of the character column data is valid for making that
change [valid dates or the NULL value], then CREATE VIEW suffices quite
well. A logical VIEW having become an even a more legitimate
alternative, since the addition of derived [expression] INDEX support.
Post by Matt Olson
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
1. Renames table to some temporary name
2. Recreates the table with new data types, fields, etc
3. Copy 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?
Both ALTER TABLE and Change Physical File (CHGPF) with the Source
File (SRCFILE) parameter specified will perform those above steps;
conspicuously, with certain restrictions.
Post by Matt Olson
Anyway to bring this automation back using some tool?
If the sqlcode -190 [msg SQL0190] still prevents that implicit CAST
and the physical data must be modified [versus alternatively leaving the
physical data unchanged while using a logical VIEW of the data], then
perform two ALTER requests instead of just one. For example, make the
TABLE change with the following script. While that script could be
created as tooling [probably best run under isolation if the number of
updated rows can be contained by journaling across the script], there is
likely to be more work required as a side effect of the change; i.e. the
change will likely drop most dependents, unless the pseudo-date field
was relatively unused:

ALTER TABLE The_Char10_File
ADD COLUMN newDateField for ndf DATE
;

UPDATE The_Char10_File
set originalDateField = newDateField
;

ALTER TABLE The_Char10_File
drop column originalDateField
;

COMMIT
; -- if the script were run under isolation

; -- DDL to recreate any dependent files; e.g. any INDEX that had
the dropped column as a KEY column or that column referenced in a VIEW.

The above scripted work is hardly as efficient as being able to make
just the one change with just one ALTER request, but possibly simpler
than ensuring that a Roll Your Own (RYO) variant of the ALTER feature
does everything that is required and as efficiently. Note that primary
advantages of the ALTER over RYO is that the Keyed Access Paths can be
preserved versus being rebuilt and that additional techniques to prevent
overflowing the ASP storage are utilized; large files that can be
altered but not copied within the same ASP will benefit [copying the
data offline or increasing disk capacity might be required instead], and
the overall required CPU from not having to maintain\build indexes is
reduced [thus less impact to other work on the system]. Establishing
all the necessary ownership\authority of the re-created objects, if not
part of the existing DDL scripts, might best be effected by having
retrieved\stored that information prior to the ALTER activity to prevent
the loss; some of the DDL sources would need to be changed anyhow, to
correct references which must be made instead to a DATE data type column.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/***@public.gmane.org
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
Loading...