Do I miss anything?

[ibm_97]

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,

[Michel Esber]

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.co m 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,

[Serge Rielau]

ib****@yahoo.co m 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

[ibm_97]

"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.

[ibm_97]

"Which version/platform of DB2 are you using?"

8.2 on AIX

[Serge Rielau]

ib****@yahoo.co m 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

[Anton Versteeg]

Serge Rielau wrote:

ib****@yahoo.co m 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

[Brian Tkatch]

Control Center:
<left window>=>All Cataloged
Systems=><serve r>=>Instances=> <instance>=>Dat abases=><databa se>=>Tables=><t able>

<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?

[Serge Rielau]

Anton Versteeg wrote:

Serge Rielau wrote:

ib****@yahoo.co m 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