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
 
algo for altering a table

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

New User


Joined: 22 May 2008
Posts: 56
Location: hyderabad

PostPosted: Tue Oct 07, 2008 11:00 am    Post subject: algo for altering a table
Reply with quote

Hi
I have following querries, it will be kind of you, if i can have your help.
We have a table(containing data) in production environment, and we have following situations, in which we need to add columns to the exsisting table:
1. A column is to be added at the end.
2. A column is to be added in between.
3. A column of type CHAR(05) NOT NULL is to be added at the end.
What will be the steps and considerations we need to follow before and after making such alterations to the table.
Also which utilities will be required.

Thank You
Regards
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Oct 07, 2008 1:09 pm    Post subject:
Reply with quote

Hello Saurabh,

Please find below answers for your question
1. A column is to be added at the end.
You can write altercommnad to add that col like
Code:

ALTER TABLE tabname
ADD colname CHAR(50) NOT NULL WITH DEFAULT  'default value'


or if it can contain NULL then

Code:
ALTER TABLE tabname
ADD colname CHAR(50)


2.A column is to be added in between: To add a col in between you need to drop & recreate the table for this you need to follw steps
a) Unload the data of the table using UNload utility
b) Drop & recreate the table with new col
c) Grant the privillages to user
d) Load the table with data you unload in step a
e) Rebind all the invalidate packages
7) Create new DCLGEN for modified table

Here I assumed that for drop table we do not have any view s but if we are having it then please put the ddl to create them & run it step b


3.A column of type CHAR(05) NOT NULL is to be added at the end.
You can use query as mention at 1 but you need to provide default value.

BUt if all this changes need to made in single table then follow the step mention in second & in that case default value is not needed for not null col as we are droping & recreating the table.

Hope it will helpful
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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm

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