Resolving RLV Store Out of Memory Error
Issue: RLV Store has run out of memory. SQLCODE= -1013129, ODBC 3 State="HY000"
This error can be raised due to any one of the following causes.
1. Too much data being loaded/updated by RLV transactions
Solution: Reduce size of loads/updates. Keep size of updates per transaction small by issuing frequent commits.
2. Automated merge is not able to keep up with RLV workload.
- Review SYQIQRLVMERGEHISTORY, merge history system view. A log entry for each RLV enabled-table is added each time a mege between RLV Store and IQ Main Store is started and is updated when a merge completes.
- Adjust database option RV_AUTO_MERGE_EVAL_INTERVAL, for configuring the evalution period in minutes to determine an automated merge of RLV and IQ Main Stores should occur. Default : 15 minutes
- Set database options RV_MERGE_TABLE_NUMROWS, RV_MERGE_TABLE_MEMPERCENT, and RV_MERGE_NODE_MEMSIZE for adjusting automated merge thresholds
3. Long running transaction(s) might be holding old RLV store fragments in memory.
- Run sp_iqrlvmemory System Stored Procedure to determine whether old RLV store fragments exist.
- Execute sp_iqtransaction System Stored Procedure to locate old active transaction, then use drop connection <conn Id> to terminate connection.
4. Maximum RLV Memory configured values is too low.
- increase max RLV memory, using IQ server startup switch -iqrlvmem.
In some scenarios, if you are loading lot of data and load and manual merge both are failing with 'out of RLV memory error' and you can not restart IQ server to increase max RLV memory. In that scenario:
- If you physical RAM available on machine, Increase RLV memory run time using sa_server_option.
sa_server_option 'rlv_memory_mb', <maximum memory in mb>
- Manual blocking merge to free up RLV memory.
sp_iqmergerlvstore (merge_type, table_name, [table_owner])
- Merge_type can be Blocking | Non_Blocking. For manual merge use Blocking merge, because Non_blocking merge need additional in-memory table fragments, which wiil not be possible, if RLV memory is already low/out of memory.