HADR on DB2 pureScale - Impact of Non Logged Operations on HADR
As part of Tier 1 requirement, we are implementing HADR on our Pega DB2 Databases. Our DBAs observed that the Out-of-the-Box Stored Procedure PRPC_D.sppr_defragment_table (PRPC_D is the Pega Data Schema) has NOT LOGGED enabled and since HADRs rely on logs to replicate the data between Primary and Stand-by.The defragment table procedure is used to maintain performance of table pr_sys_locks. optimal solution would be to disable the defrag-syslocks daemon and simply REORG table pr_sys_locks using DB2 native tools. There might be similar issues with sppr_purge_table procedure. It explicitly disables logging to do large deletes of stale data. It is run from the systemCleaner agent.
1. The Support article does not include modified DDL for sppr_defragment_table. It is limited to only sppr_purge_table_no_logging.
2. Currently the approved modification referred in article depicts about a larger data commit operation having impact to System Cleaner agent. But what if we do commit frequently with small number of selected data and modify the proc. In that scenario, what would be the modified version of the Procs (sppr_defragment_table, sppr_purge_table_no_logging )
3. Post analyzing the impact on System Cleaner agent and System Performance, what if during the HADR Set Up,the approved changes are not done for these two store procedures? What will be the impacts from Pega End?
4. What are the tables involved in these two Store Proc execution?
For question 1, sppr_defragment_table can be modified with the same removal of NOT LOGGED INITIALLY as
For question 2, the system cleaner purges a number of tables using the NOT LOGGED INITIALLY statement. It uses this NOT LOGGED INITIALLY to improve performance of large delete operations when it clears older records from a number of tables such as pr_perf_stats which have transient historical data.
For question 3, I don't know IBM's HADR implementation well enough to answer for certain. I believe HADR might produce an exception if a query with a NOT LOGGED INITIALLY clause is run?
For question 4, I will have to do some research a bit to answer question 4.
Bear in mind that next upgrade the procedures will be recreated so the no-logging version needs to be suppressed.
The nologging option can be disabled for DB2 by adding this line to the setupdatabase.properties file...
The setting will have this effect:
- The sppr_defragment_table procedure will NOT activate the "not logged initially" option on the table.
- The sppr_purge_table_no_logging procedure will NOT activate the "not logged initially" option on the table.
- TRUNCATE statements that are generated during upgrade/update will NOT activate the "not logged initially with empty table" option on the table.
Provided that the change to setupdatabase.properties is in place the behavior described above will persist across upgrades/updates and the customer will not have to make local changes again and again.
It is perfectly reasonable to disable the agent and use native tools to perform this process as suggested above. Please be aware that the pr_sys_locks table is used heavily while the system is running.
Number 2 is harder to comment on. The procedure was written to be as fast as possible and is not designed to commit in stages. We don’t have previous experience to draw on and make the comparison you ask for.
For number 3, are you asking the impact if leaving no-logging is left in place on the HADR system? I do not believe we have in-house experience with HADR but will ask some likely sources.
For number 4, sppr_defragment_table manipulates pr_sys_locks (though it could defragment any table).
sppr_purge_table is able to purge virtually any table. The procedure is invoked by the agents/activity SystemCleaner and UsageCleaner:
My understanding is that the procedure copies the complete data from the original table to a temp, truncates the original table, then copies the complete data back to the original table. The idea is to make the existing rows contiguous and free up fragmented space.