SA-20991

Summary

DBAs cannot setup the Pega database for High Availability (HA) replication due to the fact the stored procedure sppr_purge_table_no_logging altering tables with the “NOT LOG INITIALLY” flag. This creates a scenario where those tables could not be recovered or replicated and would generate an error the next time the tables were accessed.

Error Messages

Not Applicable

Steps to Reproduce

Examine the code of stored procedure sppr_purge_table_no_logging.

Root Cause

These stored procedures were written this way because they are intended to delete large chunks of data from specific tables and therefore can have a heavy impact on the transaction log. The system cleaner agent uses this stored procedure to clear out expired requestors, rule usage content, and other expired data.

Resolution

Replace the stored procedure sppr_purge_table_no_logging with the following to address this issue, disabling the activation of the NOT LOGGED INITIALLY statement and using normal logged deletes to purge the table instead.

*NOTE* this will negatively impact performance of the System Cleaner agent to a degree.  You should monitor the performance of the System Cleaner agent before and after making these changes.

create procedure YOURSCHEMA.sppr_purge_table_no_logging
--
-- $Id: sppr_purge_table_no_logging.sql,v 1.2 2010/06/09 19:10:19 reecr Exp $
--
-- Copyright (c) 2010  Pegasystems Inc.
-- All rights reserved.
--
-- This  software  has  been  provided pursuant  to  a  License
-- Agreement  containing  restrictions on  its  use.   The  software
-- contains  valuable  trade secrets and proprietary information  of
-- Pegasystems Inc and is protected by  federal   copyright law.  It
-- may  not be copied,  modified,  translated or distributed in  any
-- form or medium,  disclosed to third parties or used in any manner
-- not provided for in  said  License Agreement except with  written
-- authorization from Pegasystems Inc.
--
 
-- Delete rows from a table using an optional where predicate.  If predicate is null, no where clause will be used.
-- This stored procedure issues an alter table command, so you must have those privs.
 
 
(
IN tablename varchar(64),
IN predicate varchar(1024))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
 
P1: BEGIN
  DECLARE sqlstmtAlterTableForNoLog varchar(250);
  DECLARE sqlstmtDelete VARCHAR(1500);
 
  A: BEGIN
    DECLARE NO_PRIVS CONDITION FOR SQLSTATE '42501';
    DECLARE CONTINUE HANDLER FOR NO_PRIVS
    BEGIN
      -- do nothing
    END;
   
   -- set sqlstmtAlterTableForNoLog = 'ALTER TABLE ' || tablename || ' activate not logged initially';
   -- prepare s1 from sqlstmtAlterTableForNoLog;
   -- execute s1;
  END A;
 
  set sqlstmtDelete = 'DELETE FROM ' || tablename;
  if predicate IS NOT NULL then
    set sqlstmtDelete = sqlstmtDelete || ' where ' || predicate;
  end if;
  prepare s1 from sqlstmtDelete;
  execute s1;
END P1