Question & Answer
Question
When a UNICA application tries to insert a large number of rows within a unit of work (UOW), it might fail with SQL0973N or SQL1476N errors.
Cause
This problem is limited to UNICA when it runs out of space in the application control heap while inserting a large volume of data.
Diagnosis details
The full error messages are as follows:
SQL1476N The current transaction was rolled back because of error "-973".
SQL0973N Not enough storage is available in the "<heap-name>" heap to process the statement.
When the problem occurs, you will see messages in the UNICA application log file similar to the following:
06/24/05 09:10:02 (PID=352298) [Audience1] DB Query (thread 506):
CREATE TABLE DB2INST1.TAB1 (CW_HH_ID INTEGER) N
OT LOGGED INITIALLY in TEMP1
06/24/05 09:10:02 (PID=352298) [Audience1] DB Query (thread 506):
Statement successful.
06/24/05 09:10:05 (PID=352298) [Audience1] DB Query: ALTER TABLE
DB2INST1.TAB1 ACTIVATE NOT LOGGED INITIALLY
06/24/05 09:10:05 (PID=352298) [Audience1] DB Query: Statement
successful
06/24/05 09:10:05 (PID=352298) [Audience1] DB Query: [BULK INSERT]
INSERT INTO DB2INST1.TAB1 ( CW_HH_ID ) VALUES( ? )
06/24/05 09:10:27 (PID=352298) [Audience1] DB Query: 100,000 records
written to DB2INST1.TAB1 ...
06/24/05 09:10:49 (PID=352298) [Audience1] DB Query: 200,000 records
written to DB2INST1.TAB1 ...
.
.
06/24/05 10:37:39 (PID=352298) [Audience1] DB Query: 12,800,000 records
written to DB2INST1.TAB1...
06/24/05 10:38:38 (PID=352298) [Audience1] DB Query: 12,900,000 records
written to DB2INST1.TAB1...
06/24/05 10:39:20 (PID=352298) Database error (5): SQL call failed.
DB vendor code: -1476
DB vendor message: 40506
Supplemental code: 1
Supplemental message: [IBM][CLI Driver][DB2/6000] SQL1476N The current
transaction was rolled back because of error "-973". SQLSTATE=40506
You will see messages in db2diag.log file similar to the following.
RETCODE : ZRC=0x8B0F0003=-1961951229=SQLO_NOMEM_APPCTLH
"No memory available in 'Application Control Heap'"
DIA8300C A memory heap error has occurred.
RETCODE : ZRC=0x8B0F0003=-1961951229=SQLO_NOMEM_APPCTLH
"No memory available in 'Application Control Heap'"
DIA8300C A memory heap error has occurred.
0x8B0F0003 corresponds to sqlcode -973
SQL0973N Not enough storage is available in the "" heap to process the statement.
Answer
Try increasing the value of the APP_CTL_HEAP_SZ configuration parameter. If this does not help, increase the value of the SAVEPOINT configuration parameter in the UNICA configuration file.
For every savepoint, memory is allocated. Memory allocated for the previous savepoints is released immediately after the savepoint is reached, instead of waiting until the UOW completes.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21225257