SEO

#1 (**permalink**) 02-27-2008, 03:27 AM

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

#2 (**permalink**) 02-27-2008, 03:27 AM

"Tuong Do" <tuongdo@hotmail.com> wrote in message
news:d3nd01$9cn$1@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

#3 (**permalink**) 02-27-2008, 03:27 AM

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

#4 (**permalink**) 02-27-2008, 03:28 AM

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" <nobody@nowhere.com> wrote in message
news:zIKdna1V7MdrosLfRVn-pw@comcast.com...
> "Tuong Do" <tuongdo@hotmail.com> wrote in message
> news:d3nd01$9cn$1@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
>

#5 (**permalink**) 02-27-2008, 03:28 AM

"Tuong Do" <tuongdo@hotmail.com> wrote in message
news:d3vm1e$kjp$1@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.

#6 (**permalink**) 02-27-2008, 03:33 AM

Simple way:

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

The NONRECOVERABLE option turns off the log for the load.

#7 (**permalink**) 02-27-2008, 03:34 AM

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).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Insert bulk data into table & temporarily disable log