Question & Answer
Question
Why am I receiving the ERROR: 65565 : no more space for TXMgr transactions array
Cause
Netezza system hold the transaction information of all subsequent transactions till the oldest transaction get completed. However system can retain the information of approximately 65,000 transactions only.
Once the limit of 65,000 transaction is reached, further queries will get aborted with error "ERROR: 65565 : no more space for TXMgr transactions array". When this occurs, no new transactions can be submitted.
One of the downsides of the IBM Netezza transaction serializability model is that an old, outstanding transaction can prevent the reuse of memory for subsequent committed (and non-read-only) transactions. The accumulation of these transaction objects can result in hitting the txmgr limit of about 65,000 transactions. When this occurs, no new transactions can be submitted.
Answer
You will need to find out the oldest transaction which is holding up the transaction array and abort that session / transaction. Following are the two commands for help:
1) nzsession -activetxn ( It helps to find out the oldest unfinished transaction)
2) nzsession -abortxn ( to terminate the particular transaction within the session)
If above method does not help, you will have to find out the oldest unfinished transaction ID using NPS command "nzsqa transactions -sys | grep oldestUnfinTxIndex". The previous command will help to you sort out the oldest transaction ID. To find the corresponding session ID, you can "grep" the dbos log OR we can use the command "nzdumpmem -nzclients| grep oldestUnfinTxIndex " (found out from above command) to map the session ID.
To avoid the occurrence of issue, One can set up an event from the templates which could help to notify customer via email, if number of transactions cross a particular limit.
[nz@nzhost ~]$ nzevent add -name TransactionLimitEvent -eventType TransactionLimitEvent -on yes -notifyType email -dst -msg 'Transaction Limit Event from $HOST' -bodyText 'NPS system $HOST -current number ($CurNumTX) of transactions exceeded 90% of total limit at $eventTimestamp.' -callHome yes -eventAggrCount 0
Note: The notification repeats every three hours if the object count remains above 90%, or when the object count drops below 85% but later reaches 59,000 again.
Was this topic helpful?
Document Information
More support for:
IBM PureData System
Software version:
1.0.0
Document number:
276049
Modified date:
17 October 2019
UID
swg21981145