Guest

Cisco Unified Intelligent Contact Management Enterprise

Field Notice: ICM 4.6.2 Upgrade Considerations


August 30, 2002


Products Affected

  • ICM 2.0

  • ICM 2.5

  • ICM 3.0

  • ICM 4.0.X

  • ICM 4.1.X

  • ICM 4.5.X

  • ICM 4.6.X

Problem Description

SQL Server resources on ICM Loggers can become exhausted when upgrading the ICM from either 4.0.x or 4.1.x to either 4.5.x or 4.6.x.

Background

To accommodate ICM Database schema changes introduced in ICM version 4.5 and later, the ICM upgrade routine creates temporary database tables on the logger being upgraded and spawns several background process threads to migrate old-schema data format to new-schema data format. The ICM data migration routines will run automatically in the background until all required data is migrated to the new schema. On some systems, this can introduce significant enough overhead to cause a halt in processing.

Problem Symptoms

After completion of the upgrade to 4.6.2, upon the starting of the new 4.6.2 service, ICM Loggers could halt because of a lack of system resources. This will result in the logger process continuously cycling, but never fully initializing.

Workaround/Solution

Pre Upgrade Steps to Prevent ICM Loggers From Halting

These are the Pre Upgrade Steps to Prevent ICM Loggers From Halting

  1. Cut back ICM data retention, or expand the ICM database on Loggers so that there is at least 50% free space in the ICM database. This should be done several day's prior to actually performing the ICM upgrade to allow time for the data to purge. To determine how much space is currently occupied in the ICM logger database perform the following steps:

    1. From a command prompt, type icrdba and hit enter.

      Note: In ICM version 4.5 and greater the command is icmdba. So if you are reading this document you are probably running a pre 4.5 version of ICM and need to type icrdba.

    2. Drill down to the ICM Logger database of interest.

    3. Right click on the object named cust_sideX and select Properties.

      Refer to the Database Administration section of ICM Administrator Guide for details on Viewing Database Properties; online version for ICM 4.5 can be found at:

      http://www.cisco.com/univercd/cc/td/doc/product/icm/icm45/admin45/index.htm

  2. Ensure each ICM Logger has at least 1 Gigabyte of RAM, 2 Gigabytes preferred.

  3. Ensure that the size of the ICM database log segment is 500 MB. DO NOT increase beyond 500 MB.

  4. Ensure that the tempdb is 100 MB.

  5. Increase SQL locks to 200,000. The SQL server service will have to be cycled in order for this change to take effect.

  6. Verify that other SQL server parameters are set to specification. Refer to the following document:

    Required SQL Server 6.5 Settings for the ICMApplication

    http://www.cisco.com/warp/public/78/SQL_4config.html

You are now ready to perform the ICM upgrade.

On a Logger

Run ICM setup and select Upgrade All. When complete, run through ICM setup to disable Purge and Update Statistics prior to starting ICM services for the first time after upgrading.

  1. Cisco ICM Setup Screen�cClick Edit.

  2. Logger Properties Screen�cClick Next.

  3. In Logger Component Properties under the Logger Database Configuration section, click Purge. Highlight and cut 00:30 M,T,W,Th,F,S,Su from the Purge schedule box. The Purge schedule box should be empty. Click OK.

  4. While still in the Logger Component Properties screen under the Logger Database Configuration section, click on Statistics. Highlight and delete 04:30 M,T,W,Th,F,S,Su from the Schedule box.

The schedule box should be empty. Click OK.

On an HDS

Run ICM setup and select Upgrade all. When complete, launch the NT Registry editor to disable ICM Purge and Update Statistics prior to starting ICM services for the first time after upgrading.

Disable Purge

These are the steps to Disable Purge

  1. From a command prompt type regedt32 and hit Enter.

  2. To disable the ICM Purge feature find the following key:

    \software\geotel\icr\cust\loggera\recovery\currentversion\purge\schedule\schedule

    In the View pulldown menu, ensure that Tree and Data is checked.

    In the schedule key you will see: 00:30 M,T,W,Th,F,S,Su

    An edit dialog box will pop open. Using the mouse, select and cut this text from the schedule key and paste it into a file for later retrieval. Close the dialog box leaving the schedule field blank.

Disable Update Statistics

These are the steps to Disable Update Statistics

  1. In regedt32, find the following key:

    \software\geotel\icr\cust\loggera\recovery\currentversion\updatestatistics\schedule

    In the View pulldown menu, ensure that Tree and Data is checked.

    In the schedule key you will see: 04:30 M,T,W,Th,F,S,Su

    On the right hand side of the screen double click on the data, 04:30 M,T,W,Th,F,S,Su.

    An edit dialog box will pop open. Using the mouse, select and cut this text from the schedule key and paste it into a file for later retrieval. Close the dialog box leaving the schedule field blank.

  2. Close regedt32.

Start ICM services and let them run until data migration is complete.

Steps for Determining the Progress of ICM Data Migration

These are the Steps for Determining the Progress of ICM Data Migration

  1. Using isql_w submit the following query selecting the Logger or HDS database:

    sp_spaceused v51_Termination_Call_Detail

    Note: The v51_Termination_Call_Detail table is a temporary table created by the ICM To facilitate data migration.

  2. From the query output divide the number of rows by 10,000. The number 10,000 is used because the RCV and RPL process report migration progress every 10,000 rows.

    name                 rows        reserved           data               index_size 
        -------------------- ----------- ------------------ ------------------ ------------------ 
        v51_Termination_Call 3863648     1067924 KB         805604 KB          262560 KB
    

    In this example, the number of rows (3863648) divided by10,000 = 386.

  3. In the rcv log (rpl on an HDS) find the average time between 10000 rows migrated messages:

    10:08:24 lb-rcv Trace: 10000 Rows migrated from table v51_Termination_Call_Detail 
        to table t_Termination_Call_Detail
        .
        .
        .
        10:08:39 lb-rcv Trace: 10000 Rows migrated from table v51_Termination_Call_Detail 
        to table t_Termination_Call_Detail
    

    So in this example 10:08:39 - 10:08:24 = 15 sec average time to migrate 10,000 rows of data.

  4. Calculate the estimated minutes to completion.

    (15 sec/10000 rows) x 386 x 10000 rows = 5790 sec / 60 sec = 96.5 mins

  5. When data migration completes the following messages will appear in the rcv or (rpl log on an HDS):

    10:10:23 lb-rcv Trace: Dropped table v51_Termination_Call_Detail
          10:10:23 lb-rcv Trace: Migration of table v51_Termination_Call_Detail to t_Termination_Call_Detail is complete
          10:10:23 lb-rcv The logger has completed migration of the large historical tables.
    

As an alternative to the above five steps, you can run the following query from within isql_w on the ICM logger being upgraded until 0 rows are returned.

select * from sysobjects where name = ''v51_Termination_Call_Detail'

Steps for Returning The ICM to Normal Operating Condition

These are the Steps for Returning The ICM to Normal Operating Condition

  1. Set SQL Locks to 50,000. Cycle the SQL server.

  2. Enable ICM Purge and Update Statistics processes.

On a Logger:

These are the steps to follow on a Logger

Run ICM setup.

  1. On the Cisco ICM Setup screen, click Edit.

  2. On the Logger Properties Screen, click Next.

  3. In the Logger Component Properties under the Logger Database Configuration section, click Purge.

    Paste 00:30 M,T,W,Th,F,S,Su into the Purge schedule box, and click OK.

  4. Still in the Logger Component Properties Screen, under the Logger Database Configuration section, click Statistics and paste 04:30 M,T,W,Th,F,S,Su into the Schedule box.

    Click OK.

  5. Continue clicking Next until setup completes.

On an HDS

Steps to enable purge

From a command prompt type regedt32 and hit enter.

  1. To enable the ICM Purge feature find the following key:

    \software\geotel\icr\cust\loggera\recovery\currentversion\purge\schedule\schedule

    In the View pulldown menu, ensure that Tree and Data is checked.

    In the schedule key you will see no data.

    On the right hand side of the screen double click on schedule.

    An edit dialog box will pop open.

    Paste 00:30 M,T,W,Th,F,S,Su into the Purge schedule box and click OK.

    Enable Update Statistics

  2. To enable the ICM Update Statistics feature find the following key:

    \software\geotel\icr\cust\loggera\recovery\currentversion\updatestatistics\schedule

    In the View pulldown menu, ensure that Tree and Data is checked.

    On the right hand side of the screen double click on Schedule.

    An edit dialog box will pop open.

    Paste 04:30 M,T,W,Th,F,S,Su into the Schedule box and click OK.

Complete The Task

Start ICM services and verify all processes start successfully.

For More Information

If you require further assistance, or if you have any further questions regarding this field notice, please contact the Cisco Systems Technical Assistance Center (TAC) by one of the following methods:

Receive Email Notification For New Field Notices

Product Alert Tool - Set up a profile to receive email updates about reliability, safety, network security, and end-of-sale issues for the Cisco products you specify.