SQDR Plus provides a mechanism for monitoring backlogs and will send an error notice and suspend log readers if necessary to avoid excessive usage of the table space where the staging tables reside. The default mechanism uses a simple row-count based throttling mechanism, but is user-extensible.
By default, the Storage Monitor uses a simple SQL statement to count the number of rows in complete transactions available in the staging tables. You can define alternate criteria with the smSQL property in sqagent.properties; this should be specified as an SQL statement which returns an integer number.
When the number of rows exceed a certain value (defined as smWarningLevel), then a SEVERE error notification is issued.
When the number of rows exceeds the smThrottleLevel value, another SEVERE error notification is issued and all log readers (replication workers) are paused. The log readers resume when the number of rows drop below the smThrottleLevel value. When the "storage monitor" has throttled staging, pruning runs at a higher frequency than usual (using the "storage monitor" interval).
The functionality is controlled by the following sqagent.properties settings:
SmSQL - a SQL statement used to monitor staging activity; it should return a single integer or bigint value. Note that the default SQL statement (used when smSQL is not specified) is valid only when SQDR Plus is monitoring only one journal.
Examples:
SELECT SUM(ROWCOUNT) FROM SQDR.SQ_TRANSACTIONS WHERE ROWCOUNT > 0 AND TXID IS NOT NULL AND TXID <= (SELECT MIN(MAXTX) FROM SQDR.SQ_READERS)
smInterval (default 30) – Frequency in minutes of when to run the storage monitor. 0 means "off".
smWarningLevel (default 40000000 i.e. 40M pending rows) - Any integer value above this value will cause a notification/log message (SEVERE level). The default is based upon an assumption of 250K rows per hour of activity, average row size of 1K - so 40M rows represents a 160 hour (6d 16h) backlog, consuming approximately 40GB of disk space.
smThrottleLevel (default 50000000 i.e. 50M staged rows) - Any integer value above this value will pause all log readers (replication workers) and cause a notification/log message (SEVERE level). Using the same assumptions (250K rows per hour of activity, average row size of 1K), the default of 15M staged rows represents a 200 hour (8d 8h) backlog, consuming about 50GB of disk space.
Note that in some cases this feature may have undesirable side effects. For example, if staging is suspended because of the "storage monitor" condition, the storage used by the journal receivers or logs on the source system will grow instead; this may be less desirable, especially if SQDR Plus is staging only a small portion of the data in the staging database. You should analyze your system and compare the amount of available storage for the SQDR Plus staging database with the amount of available storage for the journal receivers or logs on the source system to determine whether or not this feature is suitable for your environment.