Archiving and Pruning the DB
|Purpose: This page describes maintenance of the UCS database.|
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.
|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:
- correct certain problems that may exist with data integrity, and
- display statistics about the UCS database.
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. Set-based archiving requires expertise in database management. Therefore it should be performed only by a qualified database administrator.
- 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;
- 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
- Open UCS Manager.
- 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
- 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.
- Click Start.
- 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:
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,
- Stop UCS Manager and UCS.
- 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;
- 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.
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.
Using Configuration Options to Schedule Service Pruning
Starting in release 8.1.0, you can use configuration options to schedule pruning (purging) of service records. It is not possible to use UCS Manager for these operations.
- On the Annexes tab of your UCS application, create one or more sections called scheduled-job-XX, where XX is any convenient identifier.
- Create options and assign values to them, as described in the table below.
If you have multiple scheduled-job-XX sections, be careful to keep the scheduled times (specified by the cron-expression option) separate. Only one purging operation can be executed at a given time: if one operation is not finished when a second one has been scheduled, the second one will not start at all.
Service Pruning Options
These are the options that may be added to a scheduled-job-XX section. You can create multiple scheduled-job-XX sections.
|Option Name||Default Value||Valid Values||Value Changes||Description|
|enabled||false||true, false||Take effect immediately||Enables (false) or disables (true) the effects of these purging options.|
||Take effect immediately|| Specifies the type of action to be performed when the pruning process runs:
|period||5||Any positive integer from 1 to 9999||Take effect immediately||Sets the time frame for pruning. For example, with settings of period=6 and period-type=days, the purging process deletes all services older than 6 days. See also the period-type option.|
||Take effect immediately|| Specifies the units to use with the period option:
See the period option.
|cron-expression||0 0 20 ? * 6L||Any valid Cron expression, as described at Cron Expression||Take effect immediately||Specifies the schedule for pruning. The default value means that pruning takes place at 8 PM on the last Friday of the month; see the diagram in the following section. For additional documentation about Cron, see the Wikipedia entry.|
Here is an analysis of the default value of the cron-expression option:
0 0 20 ? * 6L * => Triggers at 8 PM on the last Friday of every month ┬ ┬ ┬ ┬ ┬ ┬ ┬ │ │ │ │ │ │ │ │ │ │ │ │ │ └── year (optional) │ │ │ │ │ └──────── day of week (0 - 7) (Sunday=0 or 7) │ │ │ │ └───────────── month (1 - 12) │ │ │ └────────────────── day of month (1 - 31) │ │ └─────────────────────── hour (0 - 23) │ └──────────────────────────── min (0 - 59) └───────────────────────────────── sec (0 - 59)