Insert bulk data into table & temporarily disable log

[Tuong Do]

Hi,

I am going to insert a large amount (200,000 records) of data into a table
Is there a way that I can temporarily disable the log? so that the insertion
run faster

Thanks in advance

[Mark A]

"Tuong Do" <tu*****@hotmail.com> wrote in message
news:d3**********@news-01.bur.connect.com.au...

Hi,

I am going to insert a large amount (200,000 records) of data into a table
Is there a way that I can temporarily disable the log? so that the
insertion
run faster

Thanks in advance

Create a script as follows:

alter table table-name not logged initially;
insert into table-name select * from table-name2;
commit:

Run the script as follows:

db2 +c -tvf script_name > script_results

[Ivan Popivanov]

For inserts, I think the solution is to use "alter table activate not
logged initially". Check the docs for the exact behavior.

Using the LOAD utility is probably the fastest way to load data, check
the "nonrecoverable" option, like in "load from myfile.txt of del
insert into mytable nonrecoverable".

Regards,
Ivan

Tuong Do wrote:

Hi,

I am going to insert a large amount (200,000 records) of data into a table Is there a way that I can temporarily disable the log? so that the insertion run faster

Thanks in advance

[Tuong Do]

Thank Mark,

But how do go reverse the alter statement? (after your script end)

Will it only valid for the duration of the script / current unit of work?

"Mark A" <no****@nowhere.com> wrote in message
news:zI********************@comcast.com...

"Tuong Do" <tu*****@hotmail.com> wrote in message
news:d3**********@news-01.bur.connect.com.au...

Hi,

I am going to insert a large amount (200,000 records) of data into a
table
Is there a way that I can temporarily disable the log? so that the
insertion
run faster

Thanks in advance

Create a script as follows:

alter table table-name not logged initially;
insert into table-name select * from table-name2;
commit:

Run the script as follows:

db2 +c -tvf script_name > script_results

[Mark A]

"Tuong Do" <tu*****@hotmail.com> wrote in message
news:d3**********@news-01.bur.connect.com.au...

Thank Mark,

But how do go reverse the alter statement? (after your script end)

Will it only valid for the duration of the script / current unit of work?

As soon as the commit is issued in the script, then logging is turned back
on. That is why the commit was included in the script I gave you.

[Zining Fu via DBMonster.com]

Simple way:

LOAD FROM <FNM> OF DEL INSERT INTO <TBL_NM> NONRECOVERABLE

The NONRECOVERABLE option turns off the log for the load.

[Ian]

Zining Fu via DBMonster.com wrote:

Simple way:

LOAD FROM <FNM> OF DEL INSERT INTO <TBL_NM> NONRECOVERABLE

The NONRECOVERABLE option turns off the log for the load.

No, NONRECOVERABLE prevents DB2 from putting the tablespace into BACKUP
PENDING after a load if LOGRETAIN is turned on.

Loads are inherently not logged (other than a couple of control records).