Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Doubt in Adding a Column to a DB2 Table ?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Sep 08, 2008 10:48 am    Post subject: Doubt in Adding a Column to a DB2 Table ?
Reply with quote

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
View user's profile Send private message

Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Mon Sep 08, 2008 11:54 am    Post subject: Reply to: Doubt in Adding a Column to a DB2 Table ?
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Sep 08, 2008 11:58 am    Post subject:
Reply with quote

Hi Prasanth, "PLA" you refering to, is it Platinum Tool
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Mon Sep 08, 2008 12:04 pm    Post subject: Reply to: Doubt in Adding a Column to a DB2 Table ?
Reply with quote

YES Very much .
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Sep 08, 2008 12:09 pm    Post subject:
Reply with quote

What will happen in the CASE of expanding the COLUMN size.
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Mon Sep 08, 2008 12:18 pm    Post subject: Reply to: Doubt in Adding a Column to a DB2 Table ?
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Sep 08, 2008 8:56 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Sep 09, 2008 8:16 am    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 09, 2008 8:45 am    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Sep 09, 2008 9:51 am    Post subject:
Reply with quote

Oh, Thank You Dick. I didn't know that.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 09, 2008 11:34 am    Post subject: Reply to: Doubt in Adding a Column to a DB2 Table ?
Reply with quote

You're welcome icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us