Commit during insert

[annecarterfredi]

I need to increase the length of CD column from char(5) to char(7) in
DB2 V8.2. I am doing it this way:

1. create new_table with CD char(7)
2. do 'insert into new_table select * from Original_table'
3. drop all constraints/dependent objects on the Original_table
4. rename Original_table to Original_table_Old
5. rename new_table to Original_table
6. create all constraints and dependent objects on the Original_table

It works. But, for the larger tables, step 2 has problem. I have a
table with more than 5 million rows and 'insert into new_table select *
from Original_table' causes 'transaction log full'. Is there a way to
commit after every 10000 inserts?
I can go for EXPORT the table and IMPORT with commit count, but I don't
want to do that now. I cannot go for stored procedure either.

Is there anything like "create table new_table like original_table with
data" in DB2?

Thanks,
A C

[annecarterfredi]

I will try this...

Alter table new_table NOT LOGGED INITIALLY ;
'insert into new_table select * from Original_table';
Commit;

and see how it works..

an*************@gmail.com wrote:

I need to increase the length of CD column from char(5) to char(7) in
DB2 V8.2. I am doing it this way:

1. create new_table with CD char(7)
2. do 'insert into new_table select * from Original_table'
3. drop all constraints/dependent objects on the Original_table
4. rename Original_table to Original_table_Old
5. rename new_table to Original_table
6. create all constraints and dependent objects on the Original_table

It works. But, for the larger tables, step 2 has problem. I have a
table with more than 5 million rows and 'insert into new_table select *
from Original_table' causes 'transaction log full'. Is there a way to
commit after every 10000 inserts?
I can go for EXPORT the table and IMPORT with commit count, but I don't
want to do that now. I cannot go for stored procedure either.

Is there anything like "create table new_table like original_table with
data" in DB2?

Thanks,
A C

[Mark A]

<an*************@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>I will try this...

Alter table new_table NOT LOGGED INITIALLY ;
'insert into new_table select * from Original_table';
Commit;

and see how it works..

The above will work if you submit it with auto-commit off. One way to set
auto-commit off is to use +c parm:

db2 +c -tvf script-name

[EpsilonAurigae]

i am not sure abt this but y dont you try

create the new table
take away permissions from the original table
export the data in an external file
load the data in the new table
drop dependencies
rename tables to old and original
recreate dependencies

i believe the export/load may be faster than inserts

thx
EA

[ateendriyo]

i am not sure abt this but y dont you try

create the new table
take away permissions from the original table
export the data in an external file
load the data in the new table
drop dependencies
rename tables to old and original
recreate dependencies
i believe the export/load may be faster than inserts
thx
EA

[Knut Stolze]

EpsilonAurigae wrote:

i am not sure abt this but y dont you try

create the new table
take away permissions from the original table
export the data in an external file
load the data in the new table

You can combine the above two steps into a single LOAD from a cursor.

drop dependencies
rename tables to old and original
recreate dependencies

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Frank Swarbrick]

I wonder if for the reload (step 2) you can use something like the
following:

declare loadcurs cursor for select * from OLD_TABLE
load from loadcurs of cursor insert into NEW_TABLE

Seems to work.

What would be really cool is if the CREATE TABLE LIKE option allowed you to
redefine, drop, or add one or more columns, but have all other columns like
the one in the original table.
Something like:
create table NEW_TABLE like OLD_TABLE with changed (COL_X CHAR(25) NOT NULL)
new (NEW_COL1 VARCHAR(255) NOT NULL, NEW_COL2 INTEGER) without (COL_Y,
COL_Z)

Any such thing exist?

Frank

---
Frank Swarbrick
Senior Developer/Analyst - Mainframe Applications
FirstBank Data Corporation - Lakewood, CO USA

>>an*************@gmail.com<an*************@gmail.co m10/05/06 5:44 PM

I need to increase the length of CD column from char(5) to char(7) in
DB2 V8.2. I am doing it this way:

1. create new_table with CD char(7)
2. do 'insert into new_table select * from Original_table'
3. drop all constraints/dependent objects on the Original_table
4. rename Original_table to Original_table_Old
5. rename new_table to Original_table
6. create all constraints and dependent objects on the Original_table

It works. But, for the larger tables, step 2 has problem. I have a
table with more than 5 million rows and 'insert into new_table select *
from Original_table' causes 'transaction log full'. Is there a way to
commit after every 10000 inserts?
I can go for EXPORT the table and IMPORT with commit count, but I don't
want to do that now. I cannot go for stored procedure either.

Is there anything like "create table new_table like original_table with
data" in DB2?

Thanks,
A C

[Knut Stolze]

Frank Swarbrick wrote:

I wonder if for the reload (step 2) you can use something like the
following:

declare loadcurs cursor for select * from OLD_TABLE
load from loadcurs of cursor insert into NEW_TABLE

Seems to work.

What would be really cool is if the CREATE TABLE LIKE option allowed you
to redefine, drop, or add one or more columns, but have all other columns
like the one in the original table.
Something like:
create table NEW_TABLE like OLD_TABLE with changed (COL_X CHAR(25) NOT
NULL) new (NEW_COL1 VARCHAR(255) NOT NULL, NEW_COL2 INTEGER) without
(COL_Y, COL_Z)

Any such thing exist?

You can create a table based on the structure/schema of a query:

CREATE TABLE ... AS ( full-select )

--
Knut Stolze
DB2 Information Integration Development
IBM Germany