|
View previous topic :: View next topic
|
| Author |
Message |
mailsaurabh.tripathi
New User
Joined: 22 May 2008
Posts: 56
Location: hyderabad
|
|
|
|
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 |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
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 |
|
|
|
|
