View previous topic :: View next topic
|
Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi,
I have doubt regarding adding a column in DB2 table. A column can be added to a db2 table using ALTER TABLE statement.
But, why most of the DBA's prefer to.
Unload the table
Drop the table
Create the table dropped table with new column.
Create indexes
Load the table
Is there any reason for this process. |
|
Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005 Posts: 306
|
|
|
|
IF the column is added not at the end and if its added in between a table only the DBA'S prefer to
Unload the table
Drop the table
Create the table dropped table with new column.
Create indexes
Load the table
If the column is added at the end of the table and if the column is of NOT NULL WITH DEFAULT the tool ( PLA) will capture the change the as ALTER and Not as dropping the table and recreating the same .The case is reverse when the column need to be added as a NOT NULL column |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi Prasanth, "PLA" you refering to, is it Platinum Tool |
|
Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005 Posts: 306
|
|
|
|
YES Very much . |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
What will happen in the CASE of expanding the COLUMN size. |
|
Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005 Posts: 306
|
|
|
|
Expanding the column Size also depends on where the column lies . On analysis using the tool if the column is at the end the Alter would be the statement getting generated. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Something else that might be considered is the value of having a common process regardless of the current modification requirement.
The unload, drop, create, re-load works every time. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Quote: |
Something else that might be considered is the value of having a common process regardless of the current modification requirement. - Dick Scherrer |
Quote: |
If the column is added at the end of the table and if the column is of NOT NULL WITH DEFAULT... - Prasanth |
In this case ALTER would be enough. But, having it as a common process regardless of the current modification.
Wouldn't that be consuming time. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
Wouldn't that be consuming time. |
Typically, no.
Doing the same process repeatedly (espcially after it is automated) is safer and really takes no significant amount of extra time.
Many organizations do not permit ALTER for production work.
If you are "just playing around", alter might be ok for personal experimentation. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Oh, Thank You Dick. I didn't know that. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
You're welcome
d |
|
Back to top |
|
|
|