Changing the Active-to-Historical Call Table Transfer Frequency

Article #ETM251B

The ETM^® Database stores two sets of call data: active and historical. This enables the ETM Database to function as both a transactional and data warehouse database, and improves performance for reports. The TeleWall^® Policy Log retrieves data from the active call table; the TeleAudit^® Usage Manager retrieves data from the historical call table. Once data has been copied to the historical table, it is available for reports. Once data has been deleted from the active table, it is no longer viewable in the TeleWall Policy Log. By default, the copy frequency is twice as often as the delete frequency. Note that data is never deleted from the active table unless it has been copied to the historical table and is older than the specified delete frequency. By default, data is copied every 6 hours and copied data is deleted every 12 hours. These values are specified in milliseconds.

The frequency at which data is copied from the active to the historical call table and the frequency at which data that has already been copied to the historical table is deleted from the active table are configurable. This technical bulletin explains how to change the active-to-historical call table transfer frequency.

Versions Affected

ETM^® System v4.0, as of this bulletin date.

More Information

The data governing the transfer is stored in the PROPERTIES table in the ETM Database. This table has three fields that store character data: twms_name, key, and value. Each data instance in the database has an entry in this table; the twms_name field denotes the instance to which the value applies.

The PROPERTIES table provides four values for each data instance in the database:

DeleteActionsBatchSize specifies how many records are deleted at a time during the deletion of previously copied records. The default is 10,000 records.

DeleteActionsFrequency specifies how often records that have already been copied to the historical call table are deleted from the active call table. The value must be specified in milliseconds.The default is 12 hours (43,200,000 ms).

InsertActionsBatchSize specifies how many records are copied at a time during copy of records from the active to historical call table. The default is 10,000 records.

InsertActionsFrequency specifies how often records are copied from the active to the historical call table. The value must be specified in milliseconds.The default is 6 hours (21,600,000 ms).

The process the migrates the data from the active tables to the historical tables rereads these property values every time it performs a migration task (that is, at the specified copy and delete frequencies). You do not need to restart the ETM server for changes to take effect unless you want them to take effect immediately. Note that the values in the table are characters that the ETM System must parse to convert to numbers. If the value is non-numeric or negative, the System logs an error and uses the defaults.

You can use either SQL*Plus, or the Oracle Enterprise Manager Console (Oracle 9i) or DBA Studio (Oracle 8i) to modify these values. Both methods are explained below.

Using SQL*Plus to Change the Values

1. Launch SQL*Plus.
2. Log in as follows:
   1. In the Username and Password boxes, type the username and password defined for the ETM Server.
   2. In the Host String box, type: 
3. Issue the following commands, where is the interval in milliseconds and is the name of the data instance used by the ETM Server.

Using the Oracle Enterprise Manager Console (or DBA Studio) to Change the Values

1. Start the Oracle Enterprise Manager Console or DBA Studio and select Launch Standalone.
2. In the Databases tree, double click the database. You are prompted to log in to that database.
3. In the Username and Password boxes, type sys and the associated password (by default, change_on_install).
4. In the Connect As box, click the down arrow and select SYSDBA.
5. Navigate to the PROPERTIES table in the tree. (For example, on 9.1, it is located at Schema | Tables | ETMUSER | PROPERTIES.)
6. Right-click PROPERTIES and open the Table Editor. The Table Editor appears.
7. Change the values as applicable, then click Apply. Note that you should not set the copy frequency to a very small value (a minimum of 30 minutes), or long-running reports may fail with a “stale rollback segment” error (depending on your call volume and rollback segment size).

Last Update: 11/04/02

SecureLogix Corporation

13750 San Pedro, Suite 230 • San Antonio, Texas 78232 • (210) 402-9669 • www.securelogix.com

Support (877) SLC-4HELP • EMAIL support@securelogix.com • http://support.securelogix.com

ETM, SecureLogix, SecureLogix Corporation, TeleWatch Secure, TWSA, the ETM Emblem, the SecureLogix Diamond Emblem, and the ETM Application Suite Emblems are trademarks or registered trademarks of SecureLogix Corporation in the U.S.A. and other countries. All other trademarks mentioned herein are believed to be trademarks of their respective owners.

© Copyright 2002 SecureLogix Corporation. All Rights Reserved.
U.S. Patents No. US 6,249,575 B1 and US 6,320,948 B1.
U.S. and Foreign Patents Pending.