version 7 on sun box os 2.8.
I need to add new columns in the middle of the table. what is the best way to do it without restricting the user to access the table.the table is big around 3 billiion row.

regards,

mujeeb

adding this.I have a small window, in which I can drop the table, but the new table defination with data should be up as early as possible.

regards,

mujeeb

I believe you could do the following:

1) rename the table (I do not remember if this is can be done in V7)
2) add the columns to the end
3) create a view with name the name of the old table that looks like what you want.

HTH

Andy

No,I can't do that, we have specific requirements from the developers.

what I am thinking of is to do these steps.

1. db2look // get the ddl's
2. export to <table_name>.del of del select * from <table_name>
3. drop the table.
4. recreate the table with the new defination.
5. import from <table_name>.del insert into <new_table>(c1,c2,c3)
6. set the grants and etc

correct me if I am wrong

regards,

mujeeb

Yes, that is the right way to do it. Cannot say how long it would take to export then import 3 billion rows. It sounded like you wanted to minimize down time. My suggestion would make that minimal.

Andy

bmujeeb,

1. LOAD
Use load instread of import! Load is 100 times faster. I you have some foreign keys to this table then temporaly drop foreign key. After load re-create foreign key!

Be careful, export, import and load can be very time consuming. It can last some hours maybe even days!!!


2. INSERT WITH SELECT
a) Rename original table.
b) Create new table with original name with new column included.
c) use sql: INSERT INTO TABLE2 SELECT COL1, COL2, etc FROM TABLE1

Be careful this can be very time consuming.

Hope this helps,
Grofaty

If he has 3 billion rows, (2) probably isn't an option, he'll fill up his logs most likely. There's always NOT LOGGED INITIALLY, but that's too dangerous in most cases.