Error 458: Long transaction aborted.
Tan Chin Yeow hit error 458 (long transaction aborted) deleting all rows of a table and reloading it from a text file. Tan Huay Tek suggested turning off buffered logging around the operation or committing every ~1000 rows. Art S. Kagel said LTXHWM was exceeded and advised dbload with commit-every-N and his dbdelete.ec utility. Helmut Leininger suggested drop table instead of delete, unless referential constraints exist. No confirmation of a fix.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, Recently, I've encounter this error 458 when trying to - delete all records in a particular table - load data from a text file into the same table Can anybody out there help me?? Thanks in advance! TCY
This should be due to number of locks exceeded and/or logical logs full.
Check if you have buffered log turn on the database.
If you have control, remove the buffered logging. Assume xxx is your
database name,
SE - sql: start database xxx with no log
ONLINE - # ontape -s -N xxx
Perform your operation that affects large number of records.
SE - sql: start database xxx with log in "xxx.log"
ONLINE - # ontape -s -B xxx
Tip: if you symlink /dev/null to /dev/nulltape and specifying "TAPEDEV
/dev/nulltape" in onconfig, the ontape will be very fast. However,
remember to set it back to your actual tape device after.
Otherwise, you have to commit after every certain number of records, say
1000, depending on your logical log size and/or no of lock record
parameter.
Hope this helps.
Tan Chin Yeow wrote:
>
> Hi,
>
> Recently, I've encounter this error 458 when trying to
> - delete all records in a particular table
> - load data from a text file into the same table
>
> Can anybody out there help me??
>
> Thanks in advance!
> TCY
Tan Chin Yeow wrote:
>
> Hi,
>
> Recently, I've encounter this error 458 when trying to
> - delete all records in a particular table
> - load data from a text file into the same table
Yup, your logical log usage during the transaction exceeded the LTXHWM
and the transaction was rolled back. Do not try to execute HUGE data
loads and deletes in a single transaction for this reason. For the
data load use dbload which has an option to commit every N rows. For
deletes get my dbdelete.ec utility which has the same option and is
VERY fast. Dbdelete.ec is part of the package I submitted to the IIUG
Software Repository named utils2_ak. Go to the IIUG site and download
it. There are several superb, if I say so myself, utilities in that
package.
Art S. Kagel
Tan Chin Yeow wrote: > Hi, > > Recently, I've encounter this error 458 when trying to > - delete all records in a particular table > - load data from a text file into the same table > > Can anybody out there help me?? > > Thanks in advance! > TCY Without going into technical details about the error message, in most cases the easiest way to avoid long transactions when deleting all rows of a table is to "drop table" instead of "delete * from table". It probably will not work when there are referential constraints on this table. Regards -- Helmut Leininger Bull AG / Vienna Unix Support Email: h.leininger@bull.at helmut.leininger@bull.net This opinion is mine and not necessarily that of my employer. No guarantees whatsoever.