Long transaction aborted in Informix IDS 7.31
A user loading data into a logged database on IDS 7.31 (Solaris 9) hit "458: Long transaction aborted" partway through the load, and asked which ONCONFIG logical-log parameters to change. Respondents said the real issue was doing the whole load in one transaction: use dbload instead of the LOAD statement so you can commit every N (e.g. 1000) rows. An alternative suggestion was to drop indexes/constraints, ALTER the table TYPE(RAW) so it isn't logged, load, then switch back to STANDARD and rebuild. No confirmation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I am running under Informix IDS 7.31 under Solaris 9 a data load on a
database with logging mode actived (transactional).
Qhen I execute my load query appears the following message:
458: Long transaction aborted.
847: Error in load file line 329334.
It seems a problem with the configurable properties of my ONCONFIG
file. Some of the properties from the ONCONFIG file are:
# Logical Log Configuration
LOGFILES 100 # Number of logical log files
LOGSIZE 2000 # Logical log size (Kbytes)
# Shared Memory Parameters
LOCKS 500000 # Maximum number of locks
BUFFERS 20000 # Maximum number of shared buffers
NUMAIOVPS 1 # Number of IO vps
PHYSBUFF 80 # Physical log buffer size (Kbytes)
LOGBUFF 16 # Logical log buffer size (Kbytes)LOGSMAX 500 # Maximum number of logical log files
CLEANERS 8 # Number of buffer cleaner processes
Any ideas which properties I have to modify?
Thank you.
Alan
What tool are you using to do the load? You need to be committing every 1000 rows.
I am using load. Qhich toot do you recommend me to use?
Could you tell me the syntax? dbload?
Thanks.
Massoroto wrote:
> I am using load. Qhich toot do you recommend me to use?
>
> Could you tell me the syntax? dbload?
>
> Thanks.
>
Well, you seem to have some pretty good toot already :O
Have a look at dbload, and then you can specify loads of things
(including commit every n rows)
You could try to turn off logging at the table level. A 'raw' table is not logged even though the database has logging... * drop any indexes and constraints, then * ALTER TABLE tab_name TYPE(RAW); * proceed with load statement for table * ALTER TABLE tab_name TYPE(STANDARD); * recreate indexes and constraints
dbload.
It's in the manuals.