On 21-Oct-2014 13:55 -0500, 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.
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.
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. 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.
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.
midrange.com
