By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

Do I miss anything?

P: n/a
DB2 8.2

I try to add a column into a table. I think the procedure inside DB2
is:

1. Create a temporary table which has the old table stucture and data.
2. Drop the original table
3. Create the new table with the added column
4. Insert data back into the new table from that temporary table.

I have to say I am shocked by this procedure.

What about if the table has 100 mils rows? If so, it will take much
time to finish step 1 and 4. Also it requires extra storage.

Do I miss anything in DB2?

Thanks,

Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
I may be wrong, but I do not think this is done.

My system has very large tables with 30+ M rows and I often add columns
to it.

The procedure you mentioned only applies when you are dropping a
column. Also, you have to perform these steps manually.

-Michel

ib****@yahoo.com escreveu:
DB2 8.2

I try to add a column into a table. I think the procedure inside DB2
is:

1. Create a temporary table which has the old table stucture and data.
2. Drop the original table
3. Create the new table with the added column
4. Insert data back into the new table from that temporary table.

I have to say I am shocked by this procedure.

What about if the table has 100 mils rows? If so, it will take much
time to finish step 1 and 4. Also it requires extra storage.

Do I miss anything in DB2?

Thanks,


Nov 12 '05 #2

P: n/a
ib****@yahoo.com wrote:
DB2 8.2

I try to add a column into a table. I think the procedure inside DB2
is:

1. Create a temporary table which has the old table stucture and data.
2. Drop the original table
3. Create the new table with the added column
4. Insert data back into the new table from that temporary table.

I have to say I am shocked by this procedure.

What about if the table has 100 mils rows? If so, it will take much
time to finish step 1 and 4. Also it requires extra storage.

Do I miss anything in DB2?

Which version/platform of DB2 are you using?
In DB2 for LUW check out the ALTER TABLE .. ADD COLUMN statement
It is INSTANTANEOUS and under transaction control. There isn't even
storage needed to extend the existing rows with the new column.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
"I may be wrong, but I do not think this is done.

My system has very large tables with 30+ M rows and I often add columns
to it.

The procedure you mentioned only applies when you are dropping a
column. Also, you have to perform these steps manually. "

I performed the 'adding column' procedure from control center. In the
end of it, I got the pop windows which showed all the steps in my post.
Also I did see there's a temp table which has the same structure and
data during the process.

The version is the latest one: 8.2
You can give a try also.

Nov 12 '05 #4

P: n/a
"Which version/platform of DB2 are you using?"

8.2 on AIX

Nov 12 '05 #5

P: n/a
ib****@yahoo.com wrote:
"Which version/platform of DB2 are you using?"

8.2 on AIX

CREATE TABLE T(C1 INT);
INSERT INTO T VALUES (1), (2), (3);
ALTER TABLE T ADD COLUMN C2 INTEGER WITH DEFAULT -1;
ALTER TABLE T ALTER COLUMN C2 DROP DEFAULT;
INSERT INTO T VALUES (10, 11), (12, 13);
SELECT * FROM T;

C1 C2
----------- -----------
1 -1
2 -1
3 -1
10 11
12 13

5 record(s) selected.

What am I missing?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

P: n/a
Serge Rielau wrote:
ib****@yahoo.com wrote:
"Which version/platform of DB2 are you using?"

8.2 on AIX

CREATE TABLE T(C1 INT);
INSERT INTO T VALUES (1), (2), (3);
ALTER TABLE T ADD COLUMN C2 INTEGER WITH DEFAULT -1;
ALTER TABLE T ALTER COLUMN C2 DROP DEFAULT;
INSERT INTO T VALUES (10, 11), (12, 13);
SELECT * FROM T;

C1 C2
----------- -----------
1 -1
2 -1
3 -1
10 11
12 13

5 record(s) selected.

What am I missing?

Cheers
Serge

99,999,995 rows :)

--
Anton Versteeg
IBM Netherlands
Nov 12 '05 #7

P: n/a
Control Center:
<left window>=>All Cataloged
Systems=><server>=>Instances=><instance>=>Database s=><database>=>Tables=><table>

<right window>=><table>=><right click>=>Alter=>Add=>Column Name typed
in A=>OK=>Show SQL:

CONNECT TO <database>;
ALTER TABLE <schema>.<table> ADD COLUMN A CHARACTER (10) ;
CONNECT RESET;

Is that what you tried?

Nov 12 '05 #8

P: n/a
Anton Versteeg wrote:
Serge Rielau wrote:
ib****@yahoo.com wrote:
"Which version/platform of DB2 are you using?"

8.2 on AIX

CREATE TABLE T(C1 INT);
INSERT INTO T VALUES (1), (2), (3);
ALTER TABLE T ADD COLUMN C2 INTEGER WITH DEFAULT -1;
ALTER TABLE T ALTER COLUMN C2 DROP DEFAULT;
INSERT INTO T VALUES (10, 11), (12, 13);
SELECT * FROM T;

C1 C2
----------- -----------
1 -1
2 -1
3 -1
10 11
12 13

5 record(s) selected.

What am I missing?

Cheers
Serge

99,999,995 rows :)

I _guarantee_ that it will be as fast!

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.