By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,117 Members | 1,714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,117 IT Pros & Developers. It's quick & easy.

Insert bulk data into table & temporarily disable log

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/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
Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/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.
Nov 12 '05 #5

P: n/a
Simple way:

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

The NONRECOVERABLE option turns off the log for the load.
Nov 12 '05 #6

P: n/a
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).
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.