Siebel Global Deployment Guide > Deploying 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, you need to add them to driver_utc.ucf. A default set of input files is provided. You may 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
- 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 you copied.
- 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:
to reflect the new input file name:
- 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:
to reflect the new log file name:
- Repeat Step 1 through Step 3 for each new input file.
- 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
For Oracle databases, 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
- Connect to the database server as the tableowner.
- 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 may 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 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 may 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 require a different partitioning method, or if you want reduced partitioning in order to optimize performance, contact Siebel Technical Support or Expert Services for assistance. If you create additional partitioned files, you may decrease performance.
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 records created in the month of January (month=1) or February (month=2).
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.