Thursday, 12 June 2008

Offline Data Move DATEs

During the offline data move, scripts are created to use BCP to dump out your data in SQL Server or Sybase to .dat files. Other scripts are created to use Oracle SQL*Loader to load the .dat files into the Oracle tables.

Your SQL Server/ Sybase database will dump out the DATETIME and SMALLDATETIME values in formats which may not be recognized by Oracle. So you have to specify what the format mask is.

SQL Server / Sybase DataTypes
DATETIME has millisecond precision.
SMALLDATETIME has only second precision.

Oracle DataTypes
DATE has only second precision
TIMESTAMP has millisecond precision

You can specify their format mask in the migration preferences.

The preferences work like this
  • The Timestamp Mask is applied to the DATETIME Sybase or SQL Server values
  • The Date Mask is applied to the SMALLDATIME Sybase or SQL Server values.
So now the full date value is recognized by SQL*Loader.

The best bit is that even if you map Sybases/ SQL Servers DATETIME to Oracles DATE (which is the default mapping). The TO_TIMESTAMP that happends in SQL*Loader will be implicitly converted to a DATE when inserted, and the milliseconds automatically truncated.

Here are some Defaults which you should specify in the Migration preferences
SQL Server
Timestamp Mask Preference = DATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss.ff3'
Date Mask Preference= SMALLDATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss'

Timestamp Mask Preference = DATETIME in Sybase = 'Mon dd yyyy hh:mi:ss:ff3AM'
Date Mask Preference = SMALLDATETIME in Sybase = 'Mon dd yyyy hh:mi:ss'

You should check your .dat files to make sure the format is correct. You can regenerate the scipts after you change the preference. The formats are applied in the generated Oracle/*.ctl files.