Manually Purging Data from the Stat Server Database

Stat Server provides no utility to periodically purge unwanted data from the Stat Server database and Genesys provides no defined procedure for implementing the purge. What data to purge and the purge operation itself are left to your discretion.

The steps, however, are relatively simple:

1. Back up your Stat Server data.
2. Determine your purge criteria—for example, the date beyond which to purge data.
3. For time-related purge criteria, determine the UTC-equivalent integer for the targeted date beyond which you want to purge data.
4. Write and execute an SQL script to purge data based on your criteria.

Determining the Purge Criteria

This topic provides one approach, based on time, for trimming down the data stored in the Stat Server database. You may want to purge data based on other criteria, such as deactivated resources or status. In addition, you may wish to apply different purge rules to each of the STATUS, QINFO, and LOGIN tables. Tailor the suggestions provided in this appendix to meet your business need.

Time-Related Fields in the Stat Server Database

Data in the Stat Server database is time-stamped in accordance with the time that Stat Server detected events from other servers. (The UseSourceTimeStamps feature does not pertain to data stored in the Stat Server database.) The STATUS table holds the following time-related fields to measure when the status of a particular agent or place changes:

• StartTime (and StartLocalTime)
• EndTime (and EndLocalTime)

The QINFO table holds:

• StartTime
• EndTime

Finally, the LOGIN table holds the Time time-related field.

Except for the LocalTime fields in the STATUS table, all time fields are based on Coordinated Universal Time (UTC), which measures the seconds from January 1, 1970, 12:01 AM. To purge data prior to a particular date, you must have the equivalent UTC integer value of your targeted date.

Tip

Some EndTime fields may hold 0 values for incompleted statuses. Basing a purge operation solely on this field is not advisable.

Determining the UTC Equivalent for a Selected Date

To determine the number of seconds between your targeted date and January 1, 1970, calculate the number of days between these two dates, and multiply the result by 86,400—the number of seconds in one day. There are numerous websites, such as http://www.thetimenow.com, that can help you to calculate the difference between two dates, or you can query your own RDBMS, using its date-diff functions.

Designing a Purge Script

QINFO, LOGIN, and STATUS are independent tables in the Stat Server database; there are no fields joining these tables; no parent-child inter-relationships exists between them. Therefore, when deleting records, you need not be concerned about maintaining data integrity in between these tables, such as the integrity that is preserved by cascade-update and -delete operations for some databases. The absence of data in one Stat Server table has no impact on the content or significance of data in another Stat Server table.

One consideration to weigh in your purge script’s design, however, is that of performance. If the volume of unwanted rows is large, executing one delete statement to purge this data will certainly impact RDBMS performance. Therefore, you should break up the operation so that the RDBMS purges data into whatever you determine to be manageable chunks.

The following generalized SQL statement deletes data: DELETE FROM StatServerTable WHERE criteria ;

To delete rows from the LOGIN table for resources that logged in prior to July 30, 2001, issue the following query against the database: DELETE FROM LOGIN WHERE Time < 996451200 ;

[ 996,451,200 = 11,533 days (between 1/1/70 and 7/30/01) * 86,400 sec/day ]

This assumes that the volume of data in your database prior to July 30, 2001 is of a manageable enough size to be purged by one DELETE statement without adversely impacting performance.