Siebel Global Deployment Guide > Deploying Siebel CRM with Global Time Zone > Converting Historical Data to UTC >

Preparing Your Data for Conversion to UTC

This topic is part of Converting Historical Data to UTC.

Before you convert your data to UTC, you need to prepare it.

• Configure custom fields and columns (if necessary) for UTC. For more information, see Enabling Custom Date-time Fields and Columns for UTC.
• Set time zones for each of your users.
• Save a report of your user time zones.
• Edit the driver_utc.ucf file to specify more input files.
• Modify the default input files as needed for your UTC deployment.
• Modify input files for partitioned tables, to assure sufficient log space.
• Allocate maximum database transaction log space.

NOTE:  Log space is controlled through transaction logs or database log files.

Set Time Zones for Users

For each of your users, specify the time zone in the Contacts screen. This data is stored in the S_CONTACT table. You must also specify a value for the Default Time Zone system preference. For more information, see Setting UTC System Preferences.

Save a Report of User Time Zones

Prior to running the UTC conversion utility, save a report of your user time zones as a record of the input data used during the conversion.

Edit the driver_utc.ucf File to Specify More Input Files

The file driver_utc.ucf identifies the input files for UTC conversion. If you create additional input files, then you need to add them to driver_utc.ucf. A default set of input files is provided. You might need to create additional files to specify more columns to convert.

An example from the driver_utc.ucf file appears as follows:

[File Execute Entry 7]

Type = FileExecute

File Name = $SiebelRoot\bin\utcupgd

Check Return Code = 1

Return Code Compliance = 0

16 Bit App = 0

Command Line = /u $UserName /p $Password /c "$ODBCDataSource" /d $DatabaseOwner /n "$RepositoryName" /g $Language /x $DatabasePlatform /j $SiebelRoot/bin/s_camp_con_01.inp /l $SiebelRoot/log/s_camp_con_01.log /s $SiebelRoot/bin/server_time.inp

Number of 10 Second Wait Loops = 2000

Prompt User For Status = 0

Parallelizable Item = 0

Title Message Num = 0

Estimated Disk Space = 0

Backup Db = 0

To edit driver_utc.ucf file parameters to accommodate additional input files

1. Specify a new input file in the driver_utc.ucf file by copying a complete step from the driver_utc.ucf file and pasting the copied step immediately after the step that you copied.
2. In the new step, change the input file parameter (which follows /j in the command line) to the name of your new input file.

   Using the preceding example in the driver_utc.ucf file, change the name of the input file in the new step from this:

   /j $SiebelRoot/bin/s_camp_con_01.inp

   to reflect the new input file name:

   /j $SiebelRoot/bin/new_file_name.inp

3. In the new step, change the log file parameter (which follows /l in the command line) to the name of the log file that corresponds to your new input file.

   Using the preceding example in the driver_utc.ucf file, change the log file name from this:

   /l $SiebelRoot/log/s_camp_con_01.log

   to reflect the new log file name:

   /l $SiebelRoot/log/new_file_name.log

4. Repeat Step 1 through Step 3 for each new input file.
5. Renumber the file execute entry numbers for your new step and for each subsequent step, in order.

   Using the preceding example in the driver_utc.ucf file (and assuming no preceding steps have changed), you would change the execute entry number in the new step from this:

   [File Execute Entry 7]

   to reflect the next step in the sequence:

   [File Execute Entry 8]

   and so on, for each subsequent step.

Modify Default Input Files as Needed

Modify the default input files for the UTC conversion utility as appropriate for your deployment, or create additional input files. An example of customizing input files to partition data into separate input files is provided later in this topic. See also About the UTC Conversion Utility.

Partitioning Prerequisite for Oracle Database

If you are using Oracle Database, some tables are partitioned by default, by using month (in the CREATED column). Make sure that the user running the UTC conversion utility has execute privilege on month. The following task describes how to give the execute permission to users who do not have it.

To grant execute permission

1. Connect to the database server as the tableowner.
2. Execute the following command:

   grant execute on month to SSE_ROLE

Modify Input Files for Partitioned Tables

As the UTC conversion utility processes input files, it typically processes each table as a whole. However, tables with very large record counts might encounter errors due to constraints on log space at the database level.

The UTC conversion utility prevents errors that could occur due to insufficient log space at the database level, by using multiple input files to partition large tables into subsets of records for processing. The utility updates each record set individually to convert all of the rows in a partitioned table.

The UTC conversion utility uses partition keys to control how a table is divided into record sets. For example, large tables might be divided based on the calendar month in which each record was created, resulting in twelve approximately equal-sized partitions.

Partition keys are supplied for tables that are typically very large and that generally use a lot of log space if updated as a single input file. The tables that are delivered with partition keys are:

The default value for each key is customizable. You determine the way that your tables are partitioned, and you can partition your own tables that you know to have large record counts by adding or modifying the input files.

NOTE:  If you create additional partitioned files, then you might decrease performance. If you require a different partitioning method, or if you want reduced partitioning in order to optimize performance, then create a service request (SR) on My Oracle Support. Alternatively, contact your Oracle sales representative for Oracle Advanced Customer Services to request assistance from Oracle's Application Expert Services.

The input file includes a WHERE clause, which defines the parameters that will be used as partition keys to divide large tables into appropriately sized sections. This WHERE clause represents standard SQL that will be used to filter which records are to be updated by each input file. Verify that you are using the correct SQL syntax.

The following example is from the file s_evt_act_00.inp. This particular file is used to define one partition of the activities table that includes all of the records created in the month of January (the month indicated by 1) or February (the month indicated by 2).

[S_EVT_ACT]
Clause = where month(CREATED) = 1 or month(CREATED) = 2
Column = APPT_START_DT, CREATED_BY
Column = TODO_ACTL_END_DT, CREATED_BY
Column = TODO_ACTL_START_DT, CREATED_BY
Column = TODO_AFTER_DT, CREATED_BY
Column = TODO_DUE_DT, CREATED_BY
Column = TODO_PLAN_END_DT, CREATED_BY
Column = TODO_PLAN_START_DT, CREATED_BY

NOTE:  If the WHERE clause is blank, then the table will not be partitioned, and will be processed as a whole.

Allocate Maximum Log Space

Prior to running the UTC conversion utility, set the log space parameters on the database server to the maximum. The utility requires a large amount of log space in order to run properly.