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

About the UTC Conversion Utility

This topic is part of Converting Historical Data to UTC.

To update existing date and time data in your data tables to UTC, you need to run the UTC conversion utility from the Database Configuration Wizard, as described in Running the UTC Conversion Utility. This utility helps you define the required parameters for UTC conversion.

After conversion is complete, the UTC conversion utility sets the Universal Time Coordinated system preference to TRUE. (When you launch the utility, the setting can be either TRUE or FALSE.)

Running the UTC conversion utility reads a series of input files that control the conversion of your date-time data to UTC. Several input files are provided by default. On the Siebel Server machine where you installed the Database Configuration Utilities, these input files are located in the directory SIEBEL_ROOT\DBSRVR\DB_PLATFORM, where DB_PLATFORM is DB2UDB, DB2390, MSSQL, or ORACLE. The input file names start with utc and have the extension .inp.

The file driver_utc.ucf identifies the UTC conversion input files, which contain parameters specifying the columns you are converting to UTC. Each of these input files updates the appropriate database columns to the UTC format in a single database transaction.

Each input file entry contains the table name, a WHERE clause, and a list of columns with their conversion methods. The conversion method defines how to link each record to the user record from which the default time zone is derived. The value specified as the conversion method corresponds to a column whose value identifies a unique user record.

NOTE:  It is recommended to modify the default input files, or to create additional input files, to specify all applicable date-time columns to be converted, and to specify the most suitable conversion method for each column. The conversion methods are explained below.

Each input file entry is structured as follows:

[TABLE_NAME]
Clause = WHERE_CLAUSE
Column = COLUMN_NAME, CONVERSION_METHOD
...

where:

• TABLE_NAME is the database table containing the date-time columns you will be converting.
• WHERE_CLAUSE, an optional value for Clause, can be used to specify a subset of columns to be converted. For an example, see Preparing Your Data for Conversion to UTC.
• COLUMN_NAME, the first value for Column, indicates a column to be converted (for example, CREATED).
• CONVERSION_METHOD, the second value for Column, indicates the conversion method to be used for this column. Possible values are SERVER_TIME and CREATED_BY.

Conversion Methods Used by the UTC Conversion Utility

The two conversion methods used by the UTC conversion utility are described in greater detail below:

• SERVER_TIME. Conversion will be done according to the time zone of the server, as specified in the file server_time.inp. This method is used for date and time values that are not associated with a user.

  You must use the correct syntax in the server_time.inp file. If the exact values are not specified, the UTC conversion utility will not work. Use the time zone setting for your operating system to determine the correct values for this syntax. Enter the value and region (in quotation marks), on the second line of the server_time.inp file.

  Here is an example for U.S. Central Standard Time (CST):

  [GLOBAL] SERVER_TIME: "(GMT-06:00) Central Time (US & Canada)";

• CREATED_BY. Conversion will be done according to the time zone of the user who created the record. The UTC conversion utility will look up the profile of the user who created the record being converted, and derive the corresponding time zone. In general, this conversion method is appropriate for converting data created by individual users, where creation time is relevant.

  An example of this type of data would be service requests originally created by users in multiple time zones, where for each record the time stamp before conversion is in the user's local time. In this case, you may choose to modify the input file s_srv_req.inp to use CREATED_BY instead of SERVER_TIME. Each record's time stamp will be converted to UTC based on the user's time zone.