Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Doubt in MFS abdulrafi IMS DB/DC 2 Fri Oct 21, 2016 3:09 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us