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.
After reviewing the content of this article (https://community.pega.com/support/support-articles/stored-procedure-violates-high-availability-requirements) on HADR implementation, we have come across with below questions-
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 )
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.
After reviewing the content of this article (https://community.pega.com/support/support-articles/stored-procedure-violates-high-availability-requirements) on HADR implementation, we have come across with below questions-
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?
An expert suggestion would be much appreciated.
Thanks,
Jayanta
Accepted Solution
Pegasystems Inc.
US
For number one, the modified DDL for sppr_defragment_table:
Replace
SET stmt = 'ALTER TABLE '|| origTableName || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';
With
SET stmt = 'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';
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...
pega.override.enable.nologging=false
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.
For number one, the modified DDL for sppr_defragment_table:
Replace
SET stmt = 'ALTER TABLE '|| origTableName || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';
With
SET stmt = 'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';
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...
pega.override.enable.nologging=false
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:
Log-Usage - pegadata.pr_perf_stats
Log-RuleUsage - pegadata.pr4_log_rule_usage
Log-RuleUsage-Details - pegadata.pr4_log_rule_usage_details
Pega-Event - pegadata.pc_events
PegaTask - pegadata.pc_pegatask
System-Status-Details - pegadata.pr_sys_statusdetails
Log-System-State- pegadata.pr_log_systemstate
Log-System-Schema-SelectQuery - pegarules.pr_selectquerylog
Data-TestCaseResults - pegadata.pr_data_testcaseresults
Data-TestSuiteResults - pegadata.pr_data_testsuiteresults
Pegasystems Inc.
US
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.
Accepted Solution
Pegasystems Inc.
US
For number one, the modified DDL for sppr_defragment_table:
Replace
SET stmt = 'ALTER TABLE '|| origTableName || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';
With
SET stmt = 'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';
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...
pega.override.enable.nologging=false
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.
For number one, the modified DDL for sppr_defragment_table:
Replace
SET stmt = 'ALTER TABLE '|| origTableName || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';
With
SET stmt = 'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';
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...
pega.override.enable.nologging=false
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:
Log-Usage - pegadata.pr_perf_stats
Log-RuleUsage - pegadata.pr4_log_rule_usage
Log-RuleUsage-Details - pegadata.pr4_log_rule_usage_details
Pega-Event - pegadata.pc_events
PegaTask - pegadata.pc_pegatask
System-Status-Details - pegadata.pr_sys_statusdetails
Log-System-State- pegadata.pr_log_systemstate
Log-System-Schema-SelectQuery - pegarules.pr_selectquerylog
Data-TestCaseResults - pegadata.pr_data_testcaseresults
Data-TestSuiteResults - pegadata.pr_data_testsuiteresults
CVS CareMark
US
Could you please confirm, whether sppr_defragment_table stored procedure is meant to load the data as-is, after doing truncate? or the intention is to retain the subset of the data in these tables?
Regards,
Jayanta
Pegasystems Inc.
US
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.