Preparing IDBs

This page provides detailed instructions for preparing the IDBs from which Genesys Info Mart will extract reporting data.

Important

This page assumes that connection-related settings for the RDBMS server(s) hosting the IDBs are suitable for Genesys Info Mart. If your IDBs and Info Mart database are not hosted by the same RDBMS instance, ensure that you set connection-related parameters for your IDB RDBMS server(s) as described on Database Tuning.

For each IDB in the deployment, you must run the ICON-provided SQL scripts to create the IDB after you have configured and installed ICON. However, before an IDB is used by Genesys Info Mart, the IDB schema must be modified to create indexes and views that enable the ETL jobs to work with it. During subsequent Genesys Info Mart migrations, the IDB schema might need to be updated further to work with the new Genesys Info Mart release.

In releases earlier than 8.5.006, the required modifications are performed by executing update_idb_*.sql scripts, which are run automatically by Genesys Info Mart jobs. Starting with release 8.5.006, without running the update_idb_*.sql scripts, Genesys Info Mart automatically creates any indexes the jobs need if the indexes are missing from the IDB. Starting with release 8.5.007, without running the update_idb_*.sql scripts, Genesys Info Mart also automatically creates views the jobs need when database links are used, if the views are missing from the IDB. However, in all releases, there are some circumstances in which you should consider running the update_idb_*.sql script(s) manually:

• On initial deployment
  Job_InitializeGIM automatically modifies the IDB schema(s) when Genesys Info Mart is first deployed. The job modifies all the IDBs for which there are DAPs in the Genesys Info Mart application’s connections.

  Consider running the scripts manually, in advance, to speed up the initialization process if Genesys Info Mart is being deployed long after ICON, and there is a significant amount of data in IDB.

• On migration
  If the IDB schema needs to be updated further when you subsequently migrate to a later release, Job_MigrateGIM automatically performs the required modifications.

  Consider running the scripts manually before you start Job_MigrateGIM, to reduce the risks of contention between Genesys Info Mart and ICON activity on IDB during migration. For more information, see "Preventing Deadlocks on IDB During Genesys Info Mart Migration" in the Genesys Info Mart 8.x migration procedures in the Genesys Migration Guide

• On subsequent addition of IDBs
  If you add an IDB to an existing Genesys Info Mart deployment, Genesys recommends that you manually execute the applicable update script(s) before you add the new extraction DAP to the Genesys Info Mart application’s connections. If you do not do so:
  
  • In releases earlier than 8.5.006, the ETL cycle will be interrupted as Genesys Info Mart enters the migration state, and you will have to run Job_MigrateGIM to execute the required script automatically. In release 8.5.006, the ETL cycle will still be interrupted if your deployment uses database links and you do not run the applicable update_idb_*.sql script manually in advance, to create required views.
  • Starting with release 8.5.006, when Job_ExtractICON creates missing indexes required for the applicable DAP role(s), there is the risk of deadlocks or concurrency issues delaying extraction because of contention between Genesys Info Mart and ICON activity on IDB, as well as the risk of the job failing if the IDB update fails.

• On subsequent addition of a new data domain
  If you add a new data domain to an existing IDB — for example, you add Multimedia details to a Voice details IDB (a supported configuration starting with release 8.5.007) — JobExtractICON creates any indexes or views that are missing for the new extraction DAP role.

  You do not need to run the applicable update_idb_*.sql script manually before you add the new DAP role, unless there is a very large amount of existing data for the new domain already in IDB, which might significantly increase Genesys Info Mart activity on the IDB and, therefore, increase the risk of contention with ICON.

From the point of view of executing the update, it makes no difference whether the IDB is partitioned or not. The Genesys Info Mart jobs and Genesys Info Mart–provided scripts to update IDB detect if an IDB is partitioned and automatically adjust the SQL statements as required.

Use the following procedure to execute the scripts to modify IDB.

Procedure: Preparing IDBs to work with Genesys Info Mart

Purpose: To manually prepare the IDB so that the ETL jobs are able to use it.

Prerequisites

• All IDB instances that are required for your deployment have been created by using the ICON-provided SQL scripts. (Refer to the Interaction Concentrator Deployment Guide for your release for the list of initialization scripts, their location, and the order in which to execute them.)
  Important

  For Microsoft SQL Server, you must use a case-insensitive collation for the SQL Server instance and the IDB.

• The database access account that the ETL jobs will use to access IDB data is available and has the required user account privileges.

  The user account does not have to be the same as the owner account. For more information about the rules and recommendations that pertain to database access accounts for Genesys Info Mart, see required database privileges for the ICON user account in Database Privileges.

• The update_idb_*.sql. scripts are available. The scripts are provided in the sql_scripts folder in your Genesys Info Mart installation package.

Steps

1. For each IDB from which Genesys Info Mart will extract ICON details, log in to IDB using the database access account that you used to create the IDB. Refer to your completed Database Worksheets to determine the ID to use.
2. Run the Genesys Info Mart–provided SQL script to add to IDB the views and indexes that Genesys Info Mart requires.
   • For a Voice details IDB, use update_idb_for_gim.sql.
   • For a Multimedia details IDB, use update_idb_for_gim_mm.sql.
   • For a Configuration details or an Outbound Contact details IDB, use either update_idb_for_gim.sql or update_idb_for_gim_mm.sql.
   If the IDB stores both Voice and Multimedia details (a supported configuration starting with release 8.5.007), execute both scripts, in any order.

Next Steps

Continue on to Preparing the Info Mart Database.