Archiving and Pruning the DB

This page describes maintenance of the UCS database.

Overview

To prevent your UCS database from expanding to an unmanageable size, you may wish to perform archiving and pruning.

• Archiving is the process of removing selected threads from the main database and storing them in the archive database.
• Pruning (sometimes also called purging) is the process of removing threads from either the main or the archive database.
• Maintenance is a cover term for pruning and archiving.

For both archiving and pruning you have a choice of two processes, as laid out in the following table.

Comparison of Maintenance Processes

Process	Configuration options (section/option)	Speed	Complexity	Availability	Objects accessible
UCS Manager archiving	cview/enabled = false	Slower	Simple, can stop midway	All releases of UCS	Interactions only
UCS Manager pruning	cview/enabled = false	Slower	Simple, can stop midway	All releases of UCS	All
Set-based archiving	cview/enabled = true archiving/use-np = true	Very fast	Several steps, cannot stop midway	UCS 8.0.2 and later	Interactions only
Prune using options	cview/enabled = true	Fast	Cannot stop midway	UCS 8.1.0 and later	All

Using UCS Manager Only

For all releases of UCS, you can use UCS Manager to configure and run the complete process of maintaining the UCS database, as described in the online Help that is delivered with UCS Manager. UCS Manager can also:

1. correct certain problems that may exist with data integrity, and
2. display statistics about the UCS database.

Set-Based Archiving

Beginning with release 8.0.2 of UCS, you can also use set-based archiving. One way to characterize the difference between the new set-based archiving and the existing archiving via UCS Manager only is that the former moves data table by table while the latter moves it interaction by interaction.

Important

Set-based archiving requires expertise in database management. Therefore it should be performed only by a qualified database administrator.

Prerequisites

Disk Space

Set-based archiving requires temporary space in the main database constituting about 90% of the space occupied by the archivable interactions. For example, if one million interactions, including 350,000 attachments, take up 10.2 GB in the main database, the temporary space needed is 9 GB.

User rights

UCS must create and drop tables during the archiving process. These rights must be granted to the UCS user in the main DB during the archiving process. Once this process is completed these rights can be revoked for normal operation of UCS. Consult your RDBMS documentation for directions on granting and revoking these rights.

The user will have to execute special queries to transfer data from temporary table to archive DB. These queries are particular to MS SQL Server and Oracle.

For Oracle, the user must be able to create and drop database links using the following queries:

create database link arch using 'ucsarch';

drop database link arch;

For MS SQL Server, the user must be able to execute the following stored procedure:

EXEC sp_addlinkedserver @server = N'suite801', @srvproduct=N'SQL Server'

EXEC sp_dropserver 'suite801', null;

Important
The queries presented here describe the minimum needed to create the database link between the main and archive UCS databases. Depending on the configuration of your database, you may need to pass more parameters, such as usernames, password, schemas, tablespaces, and so on. Consult your RDBMS documentation for guidance.

Configuration

Open your UCS Application object and:

• In the cview section, set the enabled option to true.
• Create a section called archiving. In it create an option called use-np with the value true.

Start the Archiving from UCS Manager

1. Open UCS Manager.
   
   
   
   
2. Select one of the following tabs:
   • Manual task for performing one-time maintenance
   • Scheduled task on Main DB for scheduling periodic maintenance on the main database
   • Scheduled task on Archive DB for scheduling periodic maintenance on the archive database

   Note: If you select either of the scheduled tasks, you must also be able to schedule the execution of the SQL queries described lower down on this page.

3. Click Start.
4. When UCS Manager displays <task> <database> done, in the area indicated by the red arrow in the screenshot below,
   • For pruning, stop here.
   • For archiving, proceed to the next section below.

   

Continue The Process

Continue by issuing the SQL queries described for the two supported RDBMs on these pages:

• Oracle databases
• MicroSoft SQL databases

Failure Recovery

The steps to recover from a failure depend on whether the failure occurred during the archiving process, or when data was being moved. Both possibilities are described below. Failure During Archiving If a failure occurs during archiving, the archiving process must be restarted from the beginning. To do so,

1. Stop UCS Manager and UCS.
2. Execute the following queries:

   drop table docid_temp;
   drop table ixnid_temp;
   drop table interaction_arch;
   drop table emailin_arch;
   drop table emailout_arch;
   drop table phonecall_arch;
   drop table callback_arch;
   drop table cobrowseurl_arch;
   drop table chat_arch;
   drop table attachment_arch;
   drop table ixncontent_arch;
   drop table ixnContentSentReceived_arch;
   drop table document_arch;
   

3. Restart both UCS Manager and UCS, and restart the archiving process.

Failure During Data Movement If a failure occurs during data movement, roll back all movement operations. The archiving procedure does not need to be executed again. Just restart the "Transferring Data into UCS DB Archive" procedure, described here for Oracle and here for MS SQL.

Multiple Attachment of a Single Document

In order to save space, UCS re-uses the same document object in the database if it is attached multiple times to an interaction. This is, for example, the case when using Standard Responses with attachments, either for agent use or for automatic replies. Like archiving using UCS Manager alone, set-based archiving does not remove unused documents from the main database because it would require an SQL operation that could take several hours to execute on large databases. For the same reason, the archiving mechanism cannot check if a document has already been inserted into the archive database. If a certain document is used multiple times, insertion of the document object in the archive database will fail with a Primary Key Constraint Violation during the execution of the following query: Oracle:

insert into document@arch select * from document_arch;

Microsoft SQL Server:

insert into bsgenucsdb.UCSArch.dbo.document select * from document_arch;

There are two possible workarounds:

• Skip this operation and avoid copying documents into the archive database.
• Use database-specific commands to merge the data into the archive database. Consult your database documentation for instructions on executing this operation.

Limitations

Set-based archiving has the following limitations:

• DB2 is not supported.
• The progress indicators in UCS Manager do not function.
• As with archiving using UCS Manager only, set-based archiving does not remove documents from the main database. Use UCS Manager's Data Integrity Correction tool to remove the orphan documents.
• If you stop the archiving from UCS Manager, processing will stop only when the current operation is finished. Depending on the size of the database, this can take from minutes to hours.
• If you stop the archiving process, you must restart the process from the beginning, first ensuring that no temporary tables are left (see Failure During Archiving). Unlike the existing archiving using UCS Manager only, set-based archiving does not support resuming the process from the point that it stopped.
• If an error occurs at any level during archiving process, you must restart the process from the beginning, first removing any temporary tables.
• You must ensure that enough space is available in the main database before starting the process. If there is insufficient space the process will fail and must be started over.
• Pruning is supported on the main database only, not on the archive database.