2 tables have almost same structure but table2 has additional col - PROD_ID. How do I transfer data from table1 to table2 and set PROD_ID to 'XXX' at the same time? This what i have but i don't think it's right:

insert data into DBA.table2
from (
PROD_ID = ‘XXX’
, select * from DBA.XXX_table1);

thanks in advance

insert into table2(prod_id, a,b,c,d) select 'xxx', a, b, c, d from table1

I did not want to list all cols from table1 as there are many, but it is ok. Thanks so much

If columns of table2 are same as table1 except adding prod_id at the first or last, you may want to do like:

insert into table2 select 'xxx', t1.* from table1 t1
or
insert into table2 select t1.*, 'xxx' from table1 t1

it did work. thanks

now there are 32,000,000 rows in table1 so when i submit this statement i run into a problem:

During SQL processing it returned: SQL0964C The transaction log for the database is full. SQLSTATE=57011

I increased db cfg params:
LOGFILSIZ = 4096 (from 1024)
LOGPRIMARY) = 60 (from 20)
LOGSECOND) = 40 (from 10)

but still getting this error. And I understand I can not commit while I am doing inserts. How do I resolve this?
Thanks

You can use an EXPORT to export the data, then use IMPORT with COMMITCOUNT to insert the data.

Andy

You can use Load from cursor.

declare mycur cursor for select XXX,..... from tab1
load from mycur of cursor insert into tab2 nonrecoverable

Since V8 DB2 has "MERGE". Answers to a lot of problems, also this one