I am doing an INSERT in a table and I am getting a transaction logs full type error. Is there a way to do this INSERT committing every, say, 1000 rows? I don't care if it goes wrong or the target table gets corrupted. I won't want to roll anything back. I don't want to increase my transaction logs just to store transactions I do not want to store.

TIA.

Try using the LOAD command. It doesnt require log files.

Newbie

I would have to do an EXPORT and then a LOAD, just to simulate a "carefree" INSERT. Does not make a lot of sense to me, especially from a performance perspective.

I guess, if there's no other solution...

The other solution is to to use 'Not Logged Initially' ...

db2 "CREATE TABLE TAB1(I INT) NOT LOGGED INTIALLY" ;

db2 +c " ALTER TABLE TAB1 NOT LOGGED INTIALLY"
db2 +c "INSERT INTO TAB1 VALUES(1)"
db2 commit

Here, until you commit or rollback , the inserts are not logged ...

But you are at a risk of loosing the table data if there is a rollforward operation or the above operation fails before committing ...
If the data can be (easily) recreated, this is a good option

Cheers
Sathyaram

I thought of NOT LOGGED INITIALLY, but how would that work if I am not creating the table? Instead, this is an existing table I am inserting table into?

TIA

I may be wrong ...
But, I remember V7 required explicit 'NOT LOGGED INITIALLY' when creating but V8 accepted ALTER anyways ...

If you are on V8, can you check ?

Cheers
Sathyaram

Sathyaram,

I am not quite following. Sorry.

From what I understand, you can use NOT LOGGED INITIALLY when creating or altering a table and this will cause that no transactions will be logged within that job or until you implicitly COMMIT.

However, I am not creating nor altering this table. This is a database table I am simply inserting a large amount of rows into.

By the way, you don't have to specify NOT LOGGED INITIALLY in either version when creating a table, if that's what you mean. We work with both v7 and v8 and none of our CREATE TABLE statements are using that at the time.

Thanks again!

Is a cursor load an option?

------------------------------------------------------------------
declare C01 cursor for
..(select A.*
.....from schema.table
...where something_is_true);

load from C01 of cursor messages "./message_file.msgs" replace into
.....schema.target_table nonrecoverable;

COMMIT;
------------------------------------------------------------------
store the above in a file whatever.sql (the . above represents a space for viewing purposes only)

then call with clp

db2 -tvf whatever.sql > whatever.log

In order to disable logging, a table should be declared with "not logged initially" as the option. If not, you can not turn off logging.

If you wish to INSERT without logging then you will have to ALTER the table and ACTIVATE NOT LOGGED INTIALLY in the same UOW as the INSERTs

Version 7
db2 "create table tnli(i int)"
DB20000I The SQL command completed successfully.
db2 "alter table tnli activate not logged initially"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1749N The NOT LOGGED INITIALLY attribute cannot be activated for table
"TNLI" since it was not created with NOT LOGGED INITIALLY. SQLSTATE=429AA


Version 8 FP 5 :
db2 "create table tnli(i int)"
The SQL command completed successfully.
db2 "alter table tnli activate not logged initially"
DB20000I The SQL command completed successfully.

I think this could be another solution, although I fear it might be slower. Keep in mind we're talking millions of rows.

I need to try it as well.

Thanks!

Another reason to go v8 in production soon!

Thanks!