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
 

 

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 how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 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 Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm


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