loading large *.unl file into database with logging

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

Hi, Sorry for my bad english! You can try with dbimport. dbimport can load the *.unl file into table using a command file. For example you can set the number of record to import, when to commit end more. Ciao Monica

Sorry DBLOAD Ciao Monica Futuresoft ha scritto nel messaggio <7r7u3d$le7$1@nslave1.tin.it>... >Hi, >Sorry for my bad english! > >You can try with dbimport. >dbimport can load the *.unl file into table using a command file. >For example you can set the number of record to import, when to commit end >more. >Ciao >Monica > > > >

All of the suggestions are fine but missed an obvious solution. Here is mine: 1) If you have 7.31+ create the new table with a temporary name (like origtable_new) as a TYPE RAW table. If you have an earlier version without TYPE RAW tables you will have to take the dbcopy option below to prevent a long transaction timeout or add more logical logfiles temporarily. Create no indexes, or if you want to prevent bad dups from being copied then add only a unique index. 2) Copy the rows you want from origtable to origtable_new using either: INSERT INTO origtable_new SELECT * FROM origtable WHERE ... or using my dbcopy utility with the -F option to force partialtransactions (ala dbload) so you do not get a long transaction rollback: dbcopy -d somedatabase -t origtable -T origtable_new -s 'SELECT * \\ FROM %s WHERE ....' -l errlog.unl -f 1000 -F \\ -h server_non_shared_memory_conn -p 100 which is about 3x faster than the INSERT option when run locally on the server. (Add the -i option if you are filtering for dups with a unique index and do not care about the contents of any duplicated rows.) 3) Drop the original table. 4) Rename the new table to the original name. 5) If created TYPE RAW, alter the table to TYPE STANDARD 6) Create any indexes and constraints needed. For best performance create the new table in a different dbspace than the original with chunks residing on different drives thant those in the original dbspace. The bonus is after dropping the old table you may be able to compress any remaining tables in that dbspace and drop some chunks from the original dbspace so you can add them to the new one as needed later as the new table begins to grow. Art S. Kagel Kire Prostizenovski wrote: > > 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