SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 04:38 AM
| ||||
| ||||
 Do I miss anything? 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,      |
|
02-27-2008, 04:38 AM
| |||
| |||
| Re: Do I miss anything? 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 ibm_97@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, |
|
02-27-2008, 04:38 AM
| |||
| |||
| Re: Do I miss anything? ibm_97@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 |
|
02-27-2008, 04:39 AM
| |||
| |||
| Re: Do I miss anything? "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. |
|
02-27-2008, 04:39 AM
| |||
| |||
| Re: Do I miss anything? ibm_97@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 |
|
02-27-2008, 04:39 AM
| |||
| |||
| Re: Do I miss anything? Serge Rielau wrote: > ibm_97@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 |
|
02-27-2008, 04:39 AM
| |||
| |||
| Re: Do I miss anything? 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? |
|
02-27-2008, 04:39 AM
| ||||
| ||||
| Re: Do I miss anything? Anton Versteeg wrote: > Serge Rielau wrote: > >> ibm_97@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 |
Linear Mode
