Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Stored procedure violates high availability requirements

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

Published March 18, 2016 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us