Re: loading large *.unl file into database with logging
Kire Prostizenovski wrote 9 Sep 99, 14:51:
> I have this problem where we have a table with millions of rows we no
> longer need, we only need the last 500,000 rows or so. I planned on
> writting a script for our operators to run out of hours which would;
>
> 1. unload the data we want to retain.
> 2. drop the existing table
> 3. create the table with no indexes
> 4. load the *.unl file into the empty table
> 5. add the indexes.
>
> The problem I have is that we have unbuffered logging enabled on this
> database, and loading this one file causes the log space to be
> exhausted.
>
> I know that I can use ontape -n <database name> to turn logging off,
> and later enable it again using ontape -s -l 0 -n <database name>. I
> was wondering if there was a way to perform this load without
> disabling logging only for that particular table, similar to the WITH
> NO LOG option for temp tables.
>
> Thanks,
>
> Kire
>
> Kire Prostizenovski
> Business Analyst
> J Blackwood and Son Limited
> 13 Cooper Street
> Smithfield NSW 2164
> Australia
> Phone +61 2 9203 0133 Fax +61 2 9203 0160
>
>
You can't turn off the loggin for a table. :-((
To resolve the problem on large load from flat files try to use the
dbload utility. This have a flag (-n) with you can set the number of
rows in a transaction. With this feature you log the inserts but
never obtain a "long transaction abosted".
Take care.
---------------------------------------
Isidre PONS ROCA
BASE - Gesti' d'Ingressos Locals
(Diputacio de Tarragona)
Servei de Sistemes de Informacio
Av President Lluis Companys 12-C
43005 - Tarragona
SPAIN
Tel # +34 977 236731
Fax # +34 977 227302
http://www.altanet.org
ipons@dtgna.altanet.org
---------------------------------------