Technical Blog Post
Abstract
In DB2LUW why am I getting SQL0955C rc=2 in HADR standby only
Body
I have a Db2 HADR setup with Read On Standby (DB2_HADR_ROS).
That way, I use the setup to allow the applications to access the standby side of the HADR to query the database in addition to the primary side of the HADR.
Though the query types are similar at primary and standby I am receiving SQL0955C rc=2 at the standby side only.
SQL0955C Sort memory cannot be allocated to process the statement.
Reason code = "<reason-code>".
2 -> Insufficient shared memory in the database-wide shared memory
area designated for sort processing.
Most common reason of hitting this kind of insufficient sort memory error on standby only or, even other kind of database memory area insufficiency errors are due to the reason that the setup might be running with Self Tuning Memory Manager (STMM).
When the STMM is ON in the database and the memory areas are tuned by Db2 on it's own it tune the memory areas nicely at the primary side of the HADR.
However, at the standby side STMM don't work when it's running as standby resulting no STMM intelligence in the standby side. And, that cause a out of tune database situation at the standby.
It's documented in the Kowledge Center as part of the "Restrictions for high availability disaster recovery (HADR)"
It says,
You can run the self-tuning memory manager (STMM) only on the current primary database. After you start the primary database or convert the standby database to a primary database by takeover, the STMM EDU might not start until the first client connection is made.
Also, it's documented in the page "Reads on standby restrictions",
It says,
The self tuning memory manager (STMM) is not supported on the standby. If you want to tune the standby (either to suit running the read-only workload or to perform well after takeover), you must do so manually.
So, following could be common suggestions in a read on standby setup,
1) Don't use STMM in a Db2 HADR setup, specially when read on standby is being used. And, tune the database manually. Most of the cases similar config at both sides of HADR will be preferred.
2) If STMM is still used then failover to standby side time to time and let the query load run over period of time when the STMM will be activated and the memory areas will be tuned. However, that might affect the query performances and other issues while the STMM tunes the memory areas over the time.
UID
ibm11139890