Jump to: navigation, search

Managing the WFM Database

Use Workforce Management (WFM) Backup-Restore Utility (BRU) to configure, update, maintain, backup, restore, and if required, migrate your database. Using a command line console, the Backup-Restore Utility provides a number of functions, enabling you to perform the tasks described in this topic.

Tip
Genesys does not support direct access to the WFM Database to create custom reports. The WFM Database structure often changes and any custom reports created by directly accessing the database could stop working after any update. Use the supported methods described in Using ETL Database Schema and Integration API Developer's Guide to generate custom reports.

Overview

Use the BRU to:

  • Create and configure a new database.
  • Update your database to that latest release.
  • Perform other database updates as needed.
  • Perform regular maintenance, such as cleanup of obsolete data.
  • Migrate data from a previous WFM release to a WFM 8.5 database.
Important
To ensure the WFM Web Application works properly:
  • Set the Microsoft SQL and Oracle database management systems (DBMS) to be case-insensitive.
  • If your WFM database is deployed on the Microsoft SQL DBMS, the WFM schema requires you set READ_COMMITTED_SNAPSHOT to ON, by executing the following DB statement:
    ALTER DATABASE <WFM_DB> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

New Database Configuration

The WFM Backup-Restore Utility populates and configures the new database for you, setting up the necessary tables, views, indexes, and so on. For instructions, see Using the Backup-Restore Utility.

If you are already using WFM 7.x, its not necessary to install a new database. Simply perform a database update to transition your database to release 8.5.

Updating Your WFM Database

From time to time, Genesys issues Maintenance Releases (MR) of its products. Some of the Workforce Management updates require database updates. If so, you can perform them using the WFM BRU.

Performing a Database Update

Execute WFM BRU by using two options:

  • -DB <database connection information>
  • -UPDATE
Example:
WFMBRU.EXE -DB "mssql;MSSQL2012_SERVER;WFM_DB;sa;password" -UPDATE
Important
If you are migrating from WFM 7.6 to 8.5, all you need to do to your database is to update it. You do not need to create a new database.

Backing Up and Restoring Your Database

The BRU provides an improved method of backing up and restoring the WFM Database. Use the following procedures to backup and restore your database:

Backing Up the Database

Execute WFM BRU by using three options:

  • -DB <database connection information>
  • -BACKUP
  • -FILE <backup file name>
Example:
WFMBRU.EXE -DB "mssql;MSSQL2012_SERVER;WFM_DB;sa;password" -BACKUP -FILE "WFM_BACKUP.db"

Restoring the Database

Execute WFM BRU by using three options:

  • -DB <database connection information>
  • -RESTORE
  • -FILE <backup file name>
Example:
WFMBRU.EXE -DB "mssql;MSSQL2012_SERVER;WFM_DB;sa;password" -RESTORE -FILE "WFM_BACKUP.db"

For more information about BRU, see Using the Backup-Restore Utility.

Using Stored Procedures to Purge Data

To properly maintain your WFM Database, you might need to periodically clean up or purge obsolete data. The procedures Performing Database Cleanup (Oracle) and Performing Database Cleanup (MSSQL) describe how to do this by using stored procedures. For the access privileges required to perform maintenance and other tasks on the database, see Database Access Privileges.

Starting in 8.5.214, the WFM Database schema has 2 sets of stored procedures in 6 distinct groups. The latest procedures are improved, cleaning up data much faster than the previous procedures. You can use the latest or previous purge procedures to cleanup your database. In the table below, the latest procedures are in bold.

Each of the previous procedures has only one date parameter and deletes specific data up to (but not including) that date.

Each of the latest procedures has three parameters, as follows:

  1. The number of days back that the data should be cleaned up
  2. The transaction size
  3. The output parameter that returns the deleted records count

Here are the default values for these parameters:

  • In MSSQL scripts:
    @DAYS_BACK INT = 90,
    @MAX_PURGE_CHUNK INT = 10000,
    @DEL_COUNTER INT OUTPUT
  • In Oracle scripts:
    DAYS_BACK IN NUMBER DEFAULT 90,
    MAX_PURGE_CHUNK IN NUMBER DEFAULT 10000,
    DEL_COUNTER OUT NUMBER

Table: Purge Procedures

Group Procedure

(latest version in bold)

Description
Calendar
WMP_DEL_CAL_ITEMS2


WMP_DEL_CAL_ITEMS

Purges Calendar items of all types for dates earlier than the provided date. Also purges Meetings that end earlier than the provided date.
Configuration WMP_DEL_CONF_T_AG2


WMP_DEL_CONF_T_AG

Purges agents whose termination date is earlier than the provided date and who do not have any Schedules, Calendar items, Agent Real Time State changes, are not included in any Schedule Scenarios, and are not assigned to any Meetings.

Before purging Terminated Agents, you can delete any related data by using other procedures. For example:

WMP_DEL_CONF_EXCE2


WMP_DEL_CONF_EXCE

Purges Exception types previously deleted on dates earlier than the provided date.
WMP_DEL_CONF_ACTI2


WMP_DEL_CONF_ACTI

Purges Activities previously deleted on dates earlier than the provided date.
WMP_DEL_CONF_REPO2


WMP_DEL_CONF_REPO

Purges reports previously created on dates earlier than the provided date.
Schedule
WMP_DEL_SCH_MASTE2


WMP_DEL_SCH_MASTE

Purges Master Schedules for dates earlier than the provided date.
WMP_DEL_SCH_SCENA2


WMP_DEL_SCH_SCENA

Purges Schedule Scenarios that ended earlier than the provided date.
WMP_DEL_SCH_TRADE2


WMP_DEL_SCH_TRADE

Purges Schedule Trade Proposals that expired earlier than provided date together with the related responses and trades.
Forecast WMP_DEL_FOR_MASTER2


WMP_DEL_FOR_MASTER

Purges the Master Forecasts and Master Forecast Comments that were created on dates earlier than the provided date.
WMP_DEL_FOR_SCENAR2


WMP_DEL_FOR_SCENAR

Purges the Forecast Scenarios that ended earlier than the provided date, and Forecast Scenarios Comments created on dates earlier than the provided date. Comments are deleted, but not counted.
History
WMP_DEL_H_AGENT_ST2


WMP_DEL_H_AGENT_ST

Purges the history of Agent Real Time State changes that occurred earlier than the provided date.
WMP_DEL_H_ACT_PERF2


WMP_DEL_H_ACT_PERF

Purges the historical Performance information for all activities in all sites and business units for dates earlier than the provided date.
WMP_DEL_H_OV_TEMPL2


WMP_DEL_H_OV_TEMPL

Purges Overlap templates that end earlier than the provided date.
WMP_DEL_H_NOTIFICA2


WMP_DEL_H_NOTIFICA

Purges Calendar, Schedule, and Schedule Trade notification acknowledgments generated earlier than the provided date.
Audit WMP_DEL_CAL_AUDIT2


WMP_DEL_CAL_AUDIT

Purges all Calendar audit records generated earlier than the provided date.
WMP_DEL_CONF_AUDIT2


WMP_DEL_CONF_AUDIT

Purges all Configuration audit records generated earlier than the provided date.
WMP_DEL_SCH_AUDIT2'


WMP_DEL_SCH_AUDIT

Purges Schedule audit records generated earlier than the provided date.
WMP_DEL_FOR_AUDIT2


WMP_DEL_FOR_AUDIT

Purges Forecast audit records generated earlier than the provided date.
WMP_DEL_ALL This procedure is added for convenience only. It calls all of the latest procedures (in this table) one by one and returns the sum (count) of the deleted records.
Tip
WFM counts directly deleted records only. However, since records in related tables are also deleted by database triggers, the total number of deleted records in tables can be higher than reported.

Purging Procedures

Use the procedures below to perform cleanup on Oracle or MSSQL databases. Although these procedures describe how to use the Oracle and MSSQL utilities, you can use other utilities to execute stored procedures, if you wish.

The latest WFM purging procedures (see table above) enable you to purge data faster than the previous procedures. You can still use the previous procedures to purging data, but cleanup will take longer to complete.

Performing Database Cleanup (Oracle)

Purpose: To remove obsolete data from your Oracle database.
Prerequisite: Your database is up-to-date and the database tool used to execute SQL statements is available. The sqlplus.exe utility is available in Oracle Client installation.

Warning
Double-check your choices before you execute the commands. You cannot retrieve deleted data.

Start of Procedure

  1. Add statements to the SQL script file using the required date. For example, to purge Calendar Audit data:
    • Using the latest procedure (fast cleanup) up to 100 days back from the current date:
      SET SERVEROUTPUT ON
      DECLARE
      AMOUNT NUMBER;
      BEGIN
      WMP_DEL_CAL_AUDIT2(100, 10000, AMOUNT);
      DBMS_OUTPUT.PUT_LINE('Finished DEL_CAL_AUDIT2. Deleted total records:' || AMOUNT);
      END;
      /
    • Using the previous procedure up to (but not including) 01/01/2015:
      EXEC WMP_DEL_CAL_AUDIT(TO_DATE('01-01-2015 0:0:0', 'DD-MM-YYYY HH24:MI:SS'));
      EXIT
  2. Obtain the Oracle username, password, and other database connection information for WFM Server, and use this information in the next step.
  3. Execute the command sqlplus.exe <user name>/<password>@<Oracle database server alias> @<input SQL script file name from step 1>.

End of Procedure

Performing Database Cleanup (MSSQL)

Purpose: To remove obsolete data from your MSSQL database.
Prerequisite: Your database is up-to-date and the database tool used to execute SQL statements is available. The sqlcmd.exe utility is available in MSSQL Client installation.

Warning
Double-check your choices before you execute the commands. You cannot retrieve deleted data.

Start of Procedure

  1. Add statements to the SQL script file using the required date. For example, to purge Calendar Audit data:
    • Using the latest procedure (fast cleanup) up to 100 days back from the current date:
      DECLARE @amount INT
      EXEC WMP_DEL_ALL 100, 10000, @DEL_COUNTER = @amount OUTPUT
      PRINT 'Finished WMP_DEL_ALL. Deleted total records: ' + CAST(@amount as NVARCHAR(30))
      GO
    • Using the previous procedure up to (but not including) 01/01/2015:
      1. EXEC WMP_DEL_CAL_AUDIT '2015-01-01 00:00:00'
      2. GO
  2. Obtain the MSSQL username, password, and other database connection information for WFM Server and use this information in the next step.
  3. Execute the command sqlcmd.exe -U <user name> -P <password> -S <MSSQL database server name> -i <input SQL script file name from step 1> -d <MSSQL database name>

End of Procedure

Database Migration

For migration instructions, see the "Workforce Management Migration Procedures” chapter in the Workforce Management Migration.

ETL Database

You can set up an WFM ETL (Extract, Transform and Load) database schema to enable third-party reporting applications to easily create reports, by incorporating WFM data. Previously, the only way to build customer reports was to use the WFM API.

ETL functionality obtains Schedule, Adherence and Performance information from WFM and stores it into a documented relational database schema. For more information about this functionality or to set up an ETL database schema, see Setting up an ETL Database.

This page was last modified on August 21, 2019, at 07:00.

Feedback

Comment on this article:

blog comments powered by Disqus