informix error code 458 - Long transaction aborted.

[suhaimy]

Hi, when i try to delete the below data i got this error Long transaction aborted.
What went wrong?


select count(*) from oas_linkline
where doccode= "BILL"
(count(*))

5765

delete from oas_linkline
where doccode= "BILL"

458: Long transaction aborted.

thanks

 

[gheist]

You should make your physical log larger to hold your transaction or:

-458
__________________________________________________________

Long transaction aborted.

The database server ran out of log space in which to record this transaction. A transaction that is not fully recorded cannot be rolled back. To preserve database integrity, the operating system ended the transaction and rolled it back automatically. All changes made since the start of the transaction have been removed. Terminate the application, and replan it so that it modifies fewer rows per transaction. Alternatively, contact the database server administrator to discuss increasing the number or the size of the logical logs. Be prepared to talk about the number of rows being updated or inserted and the size of each row.

 

[suhaimy]

Hi,
I increase the physical log from 20000 to 200000 but still got that error message.
attached my onconfig file.

# Root Dbspace Configuration

ROOTNAME rootdbs # Root dbspace name
ROOTPATH /dev/vg01/rlvol3 # Path for device containing root dbspace
ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 500000 # Size of root dbspace (Kbytes)

# Disk Mirroring Configuration Parameters

MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH # Path for device containing mirrored root
MIRROROFFSET 0 # Offset into mirrored device (Kbytes)

# Physical Log Configuration

PHYSDBS rootdbs # Location (dbspace) of physical log
PHYSFILE 20000 # Physical log file size (Kbytes)

# Logical Log Configuration

LOGFILES 25 # Number of logical log files
LOGSIZE 10000 # Logical log size (Kbytes)

# Diagnostics

MSGPATH /informix/online.log # System message log file path
CONSOLE /dev/console # System console message path
ALARMPROGRAM /informix/etc/log_full.sh # Alarm program path
SYSALARMPROGRAM /informix/etc/evidence.sh # System Alarm program path
TBLSPACE_STATS 1

# System Archive Tape Device

TAPEDEV /dev/rmt/0m # Tape device path
TAPEBLK 1024 # Tape block size (Kbytes)
TAPESIZE 4000000 # Maximum amount of data to put on tape (Kbytes)

# Log Archive Tape Device

LTAPEDEV /dev/null # Log tape device path
LTAPEBLK 1024 # Log tape block size (Kbytes)
LTAPESIZE 5000000 # Max amount of data to put on log tape (Kbytes)

# Optical

STAGEBLOB # Informix Dynamic Server/Optical staging area

# System Configuration

SERVERNUM 1 # Unique id corresponding to a Dynamic Server instance
DBSERVERNAME Y2K # Name of default database server
DBSERVERALIASES ODBC # List of alternate dbservernames
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)

MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor
NUMCPUVPS 2 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one

NOAGE 1 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors

# Shared Memory Parameters

LOCKS 700000 # Maximum number of locks
BUFFERS 300000 # Maximum number of shared buffers
NUMAIOVPS 2 # Number of IO vps
PHYSBUFF 100 # Physical log buffer size (Kbytes)
LOGBUFF 100 # Logical log buffer size (Kbytes)
LOGSMAX 25 # Maximum number of logical log files
CLEANERS 20 # Number of buffer cleaner processes
SHMBASE 0x0 # Shared memory base address
SHMVIRTSIZE 16384 # initial virtual shared memory segment size
SHMADD 16384 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 30 # Number of LRU queues
LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark percentage
LTXEHWM 60 # Long transaction high water mark (exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 32 # Stack size (Kbytes)

# System Page Size
# BUFFSIZE - Dynamic Server no longer supports this configuration parameter.
# To determine the page size used by Dynamic Server on your platform
# see the last line of output from the command, 'onstat -b'.


# Recovery Variables
# OFF_RECVRY_THREADS:
# Number of parallel worker threads during fast recovery or an offline restore.
# ON_RECVRY_THREADS:
# Number of parallel worker threads during an online restore.

OFF_RECVRY_THREADS 10 # Default number of offline worker threads
ON_RECVRY_THREADS 1 # Default number of online worker threads

# Data Replication Variables
# DRAUTO: 0 manual, 1 retain type, 2 reverse type
DRAUTO 0 # DR automatic switchover
DRINTERVAL 30 # DR max time between DR buffer flushes (in sec)
DRTIMEOUT 30 # DR network timeout (in sec)
DRLOSTFOUND /informix/etc/dr.lostfound # DR lost+found file path

# CDR Variables
CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes)
CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes)

# Backup/Restore variables
BAR_ACT_LOG /tmp/bar_act.log
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31

# Informix Storage Manager variables
ISM_DATA_POOL ISMData # If the data pool name is changed, be sure to
# update $INFORMIXDIR/bin/onbar. Change to
# ism_catalog -create_bootstrap -pool <new name>
ISM_LOG_POOL ISMLogs

# Read Ahead Variables
RA_PAGES 50 # Number of pages to attempt to read ahead
RA_THRESHOLD 20 # Number of pages left before next group

# DBSPACETEMP:
# Dynamic Server equivalent of DBTEMP for SE. This is the list of dbspaces
# that the Dynamic Server SQL Engine will use to create temp tables etc.
# If specified it must be a colon separated list of dbspaces that exist
# when the Dynamic Server system is brought online. If not specified, or if
# all dbspaces specified are invalid, various ad hoc queries will create
# temporary files in /tmp instead.

DBSPACETEMP tempdb # Default temp dbspaces

# DUMP*:
# The following parameters control the type of diagnostics information which
# is preserved when an unanticipated error condition (assertion failure) occurs
# during Dynamic Server operations.
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.

DUMPDIR /tmp # Preserve diagnostics in this directory
DUMPSHMEM 1 # Dump a copy of shared memory
DUMPGCORE 0 # Dump a core image using 'gcore'
DUMPCORE 0 # Dump a core image (Warning:this aborts Dynamic Server)
DUMPCNT 1 # Number of shared memory or gcore dumps for
# a single user's session

FILLFACTOR 90 # Fill factor for building indexes

# method for Dynamic Server to use when determining current time
USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow)

# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
DS_MAX_QUERIES # Maximum number of decision support queries
DS_TOTAL_MEMORY # Decision support memory (Kbytes)
DS_MAX_SCANS 1048576 # Maximum number of decision support scans
DATASKIP off # List of dbspaces to skip

# OPTCOMPIND
# 0 => Nested loop joins will be preferred (where
# possible) over sortmerge joins and hash joins.
# 1 => If the transaction isolation mode is not
# &quot;repeatable read&quot;, optimizer behaves as in (2)
# below. Otherwise it behaves as in (0) above.
# 2 => Use costs regardless of the transaction isolation
# mode. Nested loop joins are not necessarily
# preferred. Optimizer bases its decision purely
# on costs.
OPTCOMPIND 2 # To hint the optimizer

ONDBSPACEDOWN 0 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAIT
LBU_PRESERVE 0 # Preserve last log for log backup
OPCACHEMAX 0 # Maximum optical cache size (Kbytes)

# HETERO_COMMIT (Gateway participation in distributed transactions)
# 1 => Heterogeneous Commit is enabled
# 0 (or any other value) => Heterogeneous Commit is disabled
HETERO_COMMIT 0

# Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
OPT_GOAL -1

# Optimizer DIRECTIVES ON (1/Default) or OFF (0)
DIRECTIVES 1

# Status of restartable restore
RESTARTABLE_RESTORE off

# OmniBack Integration
BAR_BSALIB_PATH /opt/omni/lib/libob2informix.sl
CDR_LOGDELTA 30 # % of log space allowed in queue memory
CDR_NUMCONNECT 16 # Expected connections per server
CDR_NIFRETRY 300 # Connection retry (seconds)
CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max)

rgds and thanks

 

[gheist]

Physical log is not changed within onconfig - it is changed via onparams utility (or onmonitor)

 

[gheist]

why so many locks and so large rootdbs?
is it multi-terabyte-10thousand-user-db?

 

[suhaimy]

I change the onconfig file then bring-down the database using onmode -ky command then bring-up the dtabase using oninit command. I think it is the same as using onmonitor.

I copy the onconfig file from the financial database.This database is used by financial application system which have 15 users, the parameter is set by other person which is left from our company my duties is to upgrade the performance which is slow complaint by users.


rgds

 

[gheist]

Not always - ie not with both logging
You may try reading $INFORMIXDIR/release/*/*/*
like perfdoc or IDS documents or performance guides from

http://www.informix.com/answers/

anyway consider splitting informix along many disks:
a plog on disk1
a llog on disk2
a complicated database on raid1
etcetc

execute a statement in dbaccess &quot;update statistics high&quot; - this will tell if your tempdb is too small
etcetcetc

what is your platform - ie output of following
$ uname
$ onstat -u ;;; only first/last lines out of it
.. some info - how many Procs/Ram/HDD does system employ

to get more exact improvements to onconfig, as most of them is platform-specific

--ok try setting these

PDQPRIORITY 90 #selects dont suppress one-row-updates

CPUVPS 4 # who knows might be of no help

AIO #blank
and make OS allocate more KAIO/AIO kernel processes ant trace them - how many are needed

if checkpints are long try lowering
CKPTINTVL 300
to something smaller so they become 0..2sec

 

[suhaimy]

OS- HP-UX 10.20
CPU -2 (PA-RISC 8200 Processor)
Total Memory 512 MB
HDD - 20 GB (4GB X 5 slot)SCSI -2 Hot swap disk drive

onstat -u
Informix Dynamic Server Version 7.31.UC2 -- On-Line -- Up 16:14:51 -- 256008 K
bytes

Userthreads
address flags sessid user tty wait tout locks nreads nwrites
cf4d9018 ---P--D 1 informix - 0 0 0 42 6262
cf4d9504 ---P--F 0 informix - 0 0 0 0 36234
cf4d99f0 ---P--F 0 informix - 0 0 0 0 793
cf4d9edc ---P--F 0 informix - 0 0 0 0 0
cf4da3c8 ---P--F 0 informix - 0 0 0 0 0
cf4da8b4 ---P--F 0 informix - 0 0 0 0 0
cf4dada0 ---P--F 0 informix - 0 0 0 0 0
cf4db28c ---P--F 0 informix - 0 0 0 0 0
cf4db778 ---P--F 0 informix - 0 0 0 0 0
cf4dbc64 ---P--F 0 informix - 0 0 0 0 0
cf4dc150 ---P--- 5 informix - 0 0 0 0 0
cf4dc63c ---P--B 6 informix - 0 0 0 0 0
cf4dd500 ---P--D 9 informix - 0 0 0 0 0
13 active, 128 total, 30 maximum concurrent

rgds

 

[gheist]

then i am sure that youll need:

dbaccess dbname << marker
update statistics high
marker
and put it in your crontab for saturdays evening or so

if that doesnt help - check usual day-to-day sql statements - could be they need new index on some columns

NUMAIOVPS can be way too small -set them to blank and check how HPUX can allocate aio subservers as needed

RA_PAGES 4
RA_THRESHOLD 4
will lower load on your disks

NETTYPE soctcp,2,30,NET
changes your informix to use normal tcp sockets only, so systemwide tcp tuning can improve your connection's response time and speed (and stability)
note that all servers in sqlhosts need to be set to onsoctcp to use tcp only

What your users' complain of: lengthy updates/slow selects/excessive and different errors? - i've tried to solve 2nd one

if there are database client programs ran locally - you may need to lower Informix's memory usage - like a bit fewer locks (300000) and smaller buffers

 

[myms]

Finally i solved problem for long transaction aborted, I add up 12 logical log
using onparam -a -d logspc. Originally the logical log
onstat -l
PHYSICAL LOG:
Buffer Bufsize Bufused Numpages Numwrites pages/IO
P-1 500 0 118 3 39.33
Phybegin Physize Phypos Phyused % Used
101007 10000 117 0 0.00


LOGICAL LOG:
Buffer Bufsize Bufused Numrecs Numpages Numwrites Recs/Page Pages/IO
L-2 500 0 12112 479 13 25.29 36.85

INDIVIDUAL LOG FILES:
Number Flags Uniqid Dbspace Pages Used % Used
1 U---C-L 2707 rootdbs 250 183 73.20
2 U-B---- 2702 rootdbs 250 250 100.00
3 U-B---- 2703 rootdbs 250 250 100.00
4 U-B---- 2704 rootdbs 250 250 100.00

then currently;
$ onstat -l

Informix Dynamic Server Version 7.31.UC2 -- On-Line -- Up 17:57:06 -- 255976 K
bytes

Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 116 500 2273 26 87.42
phybegin physize phypos phyused %used
101007 10000 2156 116 1.16

Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-1 68 500 233431 8977 129 26.0 69.6


Subsystem numrecs Log Space used
OLDRSAM 233431 18102816

address number flags uniqid begin size used %used
c2039e20 1 U-B---- 2719 100233 250 250 100.00
c2039e3c 2 U-B---- 2720 10032d 250 250 100.00
c2039e58 3 U-B---- 2721 100427 250 250 100.00
c2039e74 4 U-B---- 2722 100521 250 250 100.00
c2039e90 5 U-B---- 2723 10061b 250 250 100.00
c2039eac 6 U-B---- 2724 100715 250 250 100.00
c2039ec8 7 U---C-L 2725 200035 5000 4181 83.62
c2039ee4 8 F------ 0 2013bd 5000 0 0.00
c2039f00 9 F------ 0 202745 5000 0 0.00
c2039f1c 10 F------ 0 203acd 5000 0 0.00
c2039f38 11 F------ 0 204e55 5000 0 0.00
c2039f54 12 F------ 0 2061dd 5000 0 0.00
c2039f70 13 F------ 0 207565 5000 0 0.00
c2039f8c 14 F------ 0 2088ed 5000 0 0.00
c2039fa8 15 F------ 0 209c75 5000 0 0.00
c2039fc4 16 F------ 0 20affd 5000 0 0.00
c2039fe0 17 F------ 0 20c385 5000 0 0.00
c2039ffc 18 F------ 0 20d70d 5000 0 0.00

I appreciate your help , and move to another problem to increase the performance of the financial system , the data is quite big i need to recalculate the extent size. You know how to calculate the extent size,attached the one of the schemas:
{ TABLE oas_docline row size = 1053 number of columns = 61 index size = 7
0
}
{ unload file name = oas_d00282.unl number of rows = 671678 }

create table oas_docline
(
cmpcode varchar(12) not null ,
doccode varchar(12) not null ,
docnum varchar(12) not null ,
doclinenum integer not null ,
tstamp smallint not null ,
moddate date not null ,
usrname varchar(12) not null ,
el1 varchar(72) not null ,
el2 varchar(72) not null ,
el3 varchar(72) not null ,
el4 varchar(72) not null ,
el5 varchar(72) not null ,
el6 varchar(72) not null ,
el7 varchar(72) not null ,
el8 varchar(72) not null ,
duedate date,
valdate date,
valuehome money(15,2) not null ,
valuehome_dp smallint not null ,
valuedoc money(15,2) not null ,
valuedoc_dp smallint not null ,
docrate money(15,2),
valuedual money(15,2) not null ,
valuedual_dp smallint not null ,
dualrate money(15,2),
statuser varchar(1) not null ,
statuserint integer not null ,
statrec smallint not null ,
statrecint integer not null ,
statpay smallint not null ,
statpayint integer not null ,
descr varchar(36) not null ,
ref1 varchar(32) not null ,
ref2 varchar(32) not null ,
ref3 varchar(32) not null ,
linetype smallint not null ,
deb_cred_ind smallint not null ,
taxlinecode varchar(12) not null ,
doctaxturn money(15,2) not null ,
doctaxturn_dp smallint not null ,
hometaxturn money(15,2) not null ,
hometaxturn_dp smallint not null ,
docsumtax money(15,2) not null ,
docsumtax_dp smallint not null ,
homesumtax money(15,2) not null ,
homesumtax_dp smallint not null ,
custsupp smallint not null ,
ival integer not null ,
paydate date,
remdate date,
statrem smallint,
severity smallint,
lettersev smallint,
matchlevel smallint,
flags integer,
ref4 varchar(32),
ref5 varchar(32),
ref6 varchar(32),
temporaryid integer,
docflags smallint,
dualflags smallint
) in dbspace1 extent size 4096 next size 4096 lock mode row;
revoke all on oas_docline from &quot;public&quot;;

*** load table ***
create unique index oas_docline_ind1 on oas_docline
(cmpcode,doccode,docnum,doclinenum);

rgds and thanks

 

[gheist]

select
systables.tabname table ,
(trunc(systables.nrows/(trunc(2020/systables.rowsize))))*2 kbytes
from
systables
where
(not tabname like 'sys%') and (not tabname like ' %')

4Kpage..2Kpage
2020->4068 (usable space in page)
2->4 (pagesize/1k)

this gives a size of table data,so extent should be smaller than that and next size reasonably large, so table consists of few fragments, not one, as index structure becomes larger in fragmented table, so you might consider having fragmented table at the begining, note that index fragments are chosen by IDS automatically

 

[myms]

I always got this message appear in oline.log please comment....
Process exited with return code 139:

onstat -m
15:10:46 Checkpoint Completed: duration was 1 seconds.
15:15:50 Checkpoint Completed: duration was 1 seconds.
15:20:54 Checkpoint Completed: duration was 1 seconds.
15:22:40 Logical Log 2733 Complete.
15:23:01 Process exited with return code 139: /bin/sh /bin/sh -c /informix/etc/
log_full.sh 2 23 &quot;Logical Log 2733 Complete.&quot; &quot;Logical Log 2733 Complete.&quot;
15:25:58 Checkpoint Completed: duration was 1 seconds.
15:31:02 Checkpoint Completed: duration was 1 seconds.
15:36:06 Checkpoint Completed: duration was 1 seconds.
15:38:54 Logical Log 2734 Complete.
15:39:15 Process exited with return code 139: /bin/sh /bin/sh -c /informix/etc/
log_full.sh 2 23 &quot;Logical Log 2734 Complete.&quot; &quot;Logical Log 2734 Complete.&quot;
15:41:23 Checkpoint Completed: duration was 14 seconds.
15:46:27 Checkpoint Completed: duration was 1 seconds.
15:51:31 Checkpoint Completed: duration was 1 seconds.
15:56:35 Checkpoint Completed: duration was 1 seconds.
16:01:39 Checkpoint Completed: duration was 1 seconds.
16:03:20 Logical Log 2735 Complete.
16:03:42 Process exited with return code 139: /bin/sh /bin/sh -c /informix/etc/
log_full.sh 2 23 &quot;Logical Log 2735 Complete.&quot; &quot;Logical Log 2735 Complete.&quot;
16:06:43 Checkpoint Completed: duration was 1 seconds.
16:11:47 Checkpoint Completed: duration was 1 seconds.

SAM error: logfile table overflow.

The OnLine administrator sees this error. The OnLine database server is configured to handle a certain number of logical logs. The limit has been reached, so a log could not be added. Increase the LOGSMAX parameter of the TBCONFIG or ONCONFIG file and initialize shared memory.

is it i need to increase the LOGSMAX parameter?

 

[gheist]

Nope - you can add logical logs with onmonitor or onparams, LOGSMAX works only assuming you have sufficient space in your logical log dbspace

 

[gheist]

or just a guess - increase in performance allows more transacrions to be performed simultaneously and your physical log needs to be increased in size to at least 20 times 128 or even more

 

[calyanram]

NO PROBLEM DYNAMIC LOGS INTRODUCED IN INFORMIX 9.30

 

[szabogabor]

Hello Suhaimy and Gheist,

A long transaction situation has nothing to do with the
size of the physical log area.
The physical log is a technical area storing the so called
&quot;before images&quot; of data pages between checkpoints.


It is the &quot;logical log&quot; that stores the records about your
modifications within the transaction.
So you must either add more logical log files with the
onparams utility or modify the high water mark parameters.

A long transaction is a very simple thing.
In your case you do more modifications in a transaction that the logical log area can record.
Logical log records for all the modifications done whithin a transaction must have room on the logical log area.


Regards,
Gabor