Managing the WFM Database

Use Workforce Management (WFM) Database Utility to configure, update, maintain, back up and restore, and if required, migrate your database. The Database Utility provides a number of functions in a single interface that enable you to perform these tasks, which are described in these sections:

• Overview
• New Database Configuration
• Update Your WFM Database
• Database Maintenance
• Back Up and Restore Your Database
• Database Migration
• ETL Database Setup
• Procedures

Overview

Use the Database Utility 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 releases to a WFM 8.5 database.

Important

In order for the Configuration Utility to work properly, you must set Microsoft SQL and Oracle database management systems to be case-insensitive.

The WFM Database Utility and the WFM Configuration Utility use the same Application object, which is typically named WFM Client Application.

Back to Top

New Database Configuration

If your first installed release of this product is Workforce Management 7.6 (or higher) or if you are migrating from release 6.x, you will use a new database. The WFM Database Utility populates and configures the new database for you, setting up the necessary tables, views, indexes, and so on. For instructions, see Installing WFM Database 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.1.

DB2 Configuration Recommendations

The settings in this section are required when you create the WFM database, and will optimize its performance. Click the + sign to display the details for each recommended setting. See also Configuring the DB2 Database.

LOGFILSIZ

[+]

Default value	LOGFILSIZ = 1000
Recommended	LOGFILSIZ = 1000
Update with this Command	UPDATE DATABASE CONFIGURATION FOR db_name USING LOGFILSIZ 1000;

LOGPRIMARY

[+]

Default value	LOGPRIMARY = 3
Recommended	LOGPRIMARY = 10
Use with this Command	UPDATE DATABASE CONFIGURATION FOR db_name USING LOGPRIMARY 10;

LOGSECOND

[+]

Default value	LOGSECOND = 2
Recommended	LOGSECOND = 2
Use with this Command	UPDATE DATABASE CONFIGURATION FOR db_name USING LOGSECOND 2;

STMTHEAP

[+]

SQL	SQL statement heap (4KB)
Description	The statement heap is used as a work space for the SQL compiler during compilation of an SQL statement. This parameter specifies the size of this work space.
IBM'S description	In most cases the default value of this parameter is acceptable. If you have very large SQL statements and the database manager issues an error (that the statement is too complex) when it attempts to optimize a statement, you should increase the value of this parameter in regular increments (such as 256 or 1024) until the error situation is resolved.
Default value	(STMTHEAP) = 2048
Recommended	(STMTHEAP) = 65535
Use with this Command	UPDATE DATABASE CONFIGURATION FOR db_name USING STMTHEAP 65535;

APPLHEAPSZ

[+]

SQL	SQL statement heap (4KB)
Description	This parameter defines the number of private memory pages available to be used by the database manager on behalf of a specific agent or subagent.
IBM'S description	Increase the value of this parameter if your applications receive an error indicating that there is not enough storage in the application heap.
Default value	(APPLHEAPSZ) = 256
Recommended	(APPLHEAPSZ) = 1000
Use with this Command	UPDATE DATABASE CONFIGURATION FOR db_name USING APPLHEAPSZ 1000;

MON_HEAP_SZ

[+]

Description	Database system monitor heap size configuration parameter.
IBM'S description	The amount of memory required for monitoring activity depends on the number of monitoring applications (applications taking snapshots or event monitors), which switches are set, and the level of database activity.
Default value	(MON_HEAP_SZ) = 66
Recommended	(MON_HEAP_SZ) = 90
Use with this Command	UPDATE DATABASE CONFIGURATION FOR db_name USING MON_HEAP_SZ 90;

SHEAPTHRES

[+]

IBM'S description	Ideally, you should set this parameter to a reasonable multiple of the largest sortheap parameter you have in your database manager instance. This parameter should be at least two times the largest sortheap defined for any database within the instance.
Default value	SHEAPTHRES = 16130
Recommended	SHEAPTHRES = 20000
Use with this Command	UPDATE DATABASE CONFIGURATION FOR db_name USING SHEAPTHRES 20000;

Back to Top

Buffer Pool

The default buffer pool Page size (4) and other values are too small. Genesys recommends you enter the following values for the settings in the Alter Buffer Pool dialog box:

Table 1: Recommended Buffer Pool Settings for DB2

Setting	Default value	Recommended value
Page Size	4	32
Bufferpool size	1000	60000
Size in 4KB pages	1000	60000

Regular Table Space	Create a Regular table space using buffer pool with 32KB Page size.
System Temporary table space	Create a System Temporary table space using buffer pool with 32KB Page size.
User Temporary Table Space	Create a User Temporary table space using buffer pool with 32KB Page size.

Using the Table Spaces

In the Change User dialog box, on the Table Space tab, ensure the table spaces you want to use, are checked (see figure below).

Important

Ensure you create the same user as your operating system user.

Figure: Using newly created table spaces when creating new users

Back to Top

Tuning the Transaction Log Characteristics

Before you enable a database for spatial operations, ensure that you have enough transaction log capacity. The default values for the transaction log configuration parameters do not provide sufficient transaction log capacity if your plans include:

• Enabling a database for spatial operations in a Windows environment
• Using the ST_import_shape stored procedure to import from shape files
• Using geocoding with a large commit scope
• Running concurrent transactions

If you plan to use any of these now or in the future, you need to increase the capacity of your transaction log for the database, by increasing one or more of the transaction log configuration parameters. Otherwise, you can use the default characteristics and proceed to tuning the application heap size.

Recommended Minimum Values

See the table below for the recommended minimum values for the three transaction log configuration parameters.

Table 2: Recommended minimum values for transaction configuration parameters

Parameter	Description	Default value	Recommended minimum value
LOGFILSIZ	Specifies the log file size as a number of 4-KB blocks	1000	1000
LOGPRIMARY	Specifies how many primary log files are to be pre-allocated to the recovery log files	3	10
LOGSECOND	Specifies the number of secondary log files	2	2

If the capacity of your transaction log is not adequate, the following error message is issued when you try to enable a database for spatial operations: GSE0010N Not enough log space is available to DB2.

Back to Top

Update 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 Database Utility.

Important

If you are migrating from WFM 7.x to 8.1, all you need to do to your database is to update it. You do not need to create a new database.

Do You Need to Update Your Database?

You might need to update your database, but perhaps you are not sure. Use the steps to help you decide:

1. Open the WFM Database Utility.
2. Check lower-right in the main window to see whether it indicates that your database is up-to-date. The database version number should correspond to the version number of the WFM Database Utility you are running.

To update the database, see Performing a Database Update.

Database Maintenance

To properly maintain your database, you might need to clean it up periodically to remove obsolete data. To do so, see Performing a Database Cleanup.

Back to Top

Back Up and Restore Your Database

You can use the WFM Database Utility to back up the data in your Workforce Management database to a local file and then restore it if necessary. The local files are in Microsoft Access format.

Important

An Access 2002 .MDB file has a maximum database size of about 2 GB.

To backup and/or restore your WFM database, see Performing a Backup and Restoring Your Database.

Starting in 8.1.3, you can use the beta Backup-Restore Utility (BRU), which provides an improved method of backing up and restoring the WFM Database. See Using the Backup-Restore Utility.

Database Migration

At some point, you might have to migrate data from a previous release to a current one. To do this, use the WFM Database Utility to transfer your existing data into the new database. After reading this topic, see the Migrating Data.

• If you are migrating from release 7.x to 8.1, use the Update Database feature of the Database Utility.
  

To migrate data into a WFM 8.1 database that is in use, you must update the database to the latest version required by the WFM Database Utility you are using. To do this, use the Update Database feature of the WFM Database Utility.

• If you are migrating from release 6.x to 8.1, you must migrate your 6.5 data to a new 8.1 database, which the migration utility creates for you.
  

You can migrate performance data from a WFM 6.5. database to your WFM 8.1 database after you have started using WFM 8.1. This enables you to bring performance data into your database that was collected during the database migration.

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

ETL Database Setup

You can set up an WFM ETL (Extract, Transform and Load) database schema to enable Genesys Infomart or other 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.

Back to Top

Procedures

Click the red arrow to view a list of procedures related to the topics on this page. <multistep>

|-| Configuring the DB2 Database=

Purpose: To modify the value of one or more configuration parameters in the database.

Prerequisites:

• You have system administration and installation experience and skill with the associated tools.
• The DB2 database is installed.

Start of Procedure

1. From the Configure Database window of the DB2 Control Center, or by reviewing the output from the GET DATABASE CONFIGURATION command, find the current value for the LOGFILSIZ, LOGPRIMARY, and LOGSECOND parameters.
2. Change one, two, or three of the values, based on the information in Table 2.
3. Enter a value for each parameter that you want to change.
   You can change the values by issuing one or more of the following commands, where db_name identifies your database:
   • UPDATE DATABASE CONFIGURATION FOR db_name USING LOGFILSIZ 1000
   • UPDATE DATABASE CONFIGURATION FOR db_name USING LOGPRIMARY 10
   • UPDATE DATABASE CONFIGURATION FOR db_name USING LOGSECOND 2
   If the only parameter that you change is LOGSECOND, the change takes effect immediately. In this case, proceed to tuning the application heap size.
4. If you change the LOGFILSIZ, LOGPRIMARY parameter, or both:
   1. Disconnect all applications from the database.
   2. If the database was explicitly activated, deactivate the database.

   Changes to the LOGFILSIZ or LOGPRIMARY parameters take effect the next time either the database is activated or a connection to the database is established.
   

   Table 3: Parameter Recommendations Comparison

   Parameter	Description	Default value	Developer's value	QA value
   (LOGFILSIZ)	Log file size (4KB)	1024	10000	10000
   (LOGPRIMARY)	Number of primary log files	3	64	128
   (LOGSECOND)	Number of secondary log files	0	16	72

End of Procedure

|-| Performing a Database Update=

Purpose: To update WFM database.

Start of Procedure

1. Open the WFM Database Utility.
2. Select the Database Update radio button.
3. Click Next.
4. If you enabled the AllowLessUpdates option in the WFM Client Application object, from the drop-down list, choose the database version to which you wish to update. If not, your database is updated to the latest available version.
   Important

   In some cases you might not choose the most recent version. For example, you might need to restore from a backup. You must select the same version as the database in the backup file. After restoring, you can then update your database version.
5. Click Finish.
   The WFM Database Utility runs the appropriate scripts and displays a message indicating the results of the update.

End of Procedure

|-| Performing a Database Cleanup=

Purpose: To remove obsolete data from your database.

Prerequisite: Your database is up-to-date.

Start of Procedure

1. Open the WFM Database Utility.
2. Select the Cleanup Database radio button and then, click Next.
   Important

   Your database must be up-to-date to perform a database cleanup.
3. From the drop-down list, select a date.
   Data up to (but not including) this date will be deleted.
4. In the dialog box that appears, select the items you want to remove.
   Warning

   Double-check your choices before you click Finish. You cannot retrieve deleted data.
5. Click Finish.
   The WFM Database Utility removes all data up to the date you selected in the Wizard. Data for the selected day is not deleted.

End of Procedure

|-| Performing a Backup=

Purpose: To back up the data in your WFM database.

Start of Procedure

1. Open the WFM Database Utility.
2. Select the Backup Database to .MDB File radio button.
3. Specify the file name and the location into which data is to be written.
4. Select the type(s) of data you want to back up.
   By default, all data types (except Audit Data) are selected. You must always back up Core data. In addition, you can choose to back up all data (by selecting Entire Database) or select from Audit Data, Performance, Forecast, or Schedule data.
5. Click Next.
   The next dialog box shows that tables that are to be backed up.
6. Click Finish to complete the process or Back to change your selections.
   The WFM Database Utility performs the backup and presents a results message at the end.

End of Procedure

|-| Restoring Your Database=

Purpose: To restore the data in your WFM database.

Start of Procedure

1. Open the WFM Database Utility.
2. Select the Restore Database from .MDB File radio button.
3. Specify the file name and the location from which data is to be retrieved and then, click Next.
4. Click Next to create the database into which files are to be restored.
   Important

   You do not need to create a database ahead of time. The restore utility creates the correct database version for your data restoration.
5. Select the type(s) of data you want to restore.
   By default, all data types that were backed up in the selected backup file are selected, and you cannot select data types that were not backed up. You must always restore Core data. In addition, you can choose to restore all data (by selecting Entire Database) or select from Audit Data, Performance, Forecast, or Schedule data.
6. Click Next.
   The next dialog box displays the tables that are to be backed up or restored.
7. Click Finish to complete the process or Back to change your selections.
   Warning

   Alternatively, you could select Cancel to cancel a database restoration in progress. However, if you do so, your database is corrupted and you must clear up the affected sections or create a new database.
   The WFM Database Utility performs the restoration and presents a results message at the end.

End of Procedure

|-| Migrating Data=

Purpose: To migrate data from a previous release to a current one.

Start of Procedure

1. Open the WFM Database Utility.
2. Select the Migrate Performance Data radio button.
3. When the Database Migration Wizard opens, follow the prompts.
   Important

   If you are migrating from release 6.x to 8.1, and you enabled the AllowMigratePerformance option in the WFM Client Application object, the Wizard prompts you to transfer performance data from your 6.x database to your new WFM database. When you migrate data after you start using the WFM database, all performance data of the type(s) you select is overwritten by the performance data from the 6.x database.

End of Procedure

</multistep>

Back to Top