Impact of the 2007 USA Daylight Saving Time Changes on the DirectConnect for Oracle and the Oracle database.
The purpose of this document is to outline the impact of the 2007 USA Daylight Saving Time (DST) changes on the DirectConnect for Oracle (DCO), and the Oracle databases the DCO connects to. This document outlines the problem, is a guide to determine if a problem will be present, and details how Sybase suggests the problem be resolved.
Oracle Note: 397281.1: USA 2007 Daylight Saving Time (DST) Compliance for Oracle Server Technologies Products.
Oracle Note: 359145.1: Impact of 2007 USA daylight saving changes on the Oracle database.
Oracle Note: 402742.1: USA 2007 DST Changes: Frequently Asked Questions for Oracle RDBMS.
Oracle note: 402316.1: Database Time Zone Patching Strategy in the Enterprise.
Oracle Patch Id: 4680059: DST Rule Change in US, Need Patched Timezone Files.
The Energy Policy Act of 2005 was signed into law in August of 2005 to extend the daylight saving time of the United States. Beginning on the second Sunday in March 2007 and ending the first Sunday in November 2007, rather than beginning the first Sunday in April and ending the last Sunday in October.
Because of this change, the Oracle database may report incorrect timezone data between 3/11/2007 - 4/01/2007 and between 10/28/2007 - 11/04/2007 (and on different dates in subsequent years), unless the required patch is applied to the database, and clients who connect to that database.
This change also affects Canada as they use the same DST rules as the USA.
The Oracle database has two datatypes, TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE, and the TZ_OFFSET function. These datatypes and function take their timezone information from Oracle's timezone files. These files are part of the Oracle database installation and also the DCO installation. These files need to be updated to a ‘version 3' level or higher when these datatypes are used with US time zones through the DCO.
Other datatypes, including DATE, are not affected by changes to DST rules.
To determine if the DCO needs to be patched, all of the following questions must be true:
- The DCO software connects to a database for which the timezone files are (or will be) updated.
- Timezone datatypes are used through the DCO to a particular database.
- The affected timezones are used through the DCO.
Keep in mind, the DCO has one set of timezone files it uses and has the ability to connect to several Oracle databases. Therefore, if one Oracle database requires the DCO to be patched, there is a possibility the other Oracle databases for which the DCO connects may require a patch as well. To summarize, there is the possibility of a cascading effect, so apply the criteria above with this in mind.
Because the DST problem affects the entire Oracle enterprise, of which the DCO is only a part of, the patched files must be obtained from the Oracle DBA of the database the DCO is connecting to. Sybase will not provide the patched files, but will provide as much information as possible to insure you obtain the correct patched files.
The DCO is built using Oracle 220.127.116.11 OCI client software, therefore, if a patch for the Version-3 timezone files are required, one-off patch 4689959 for 18.104.22.168 or 22.214.171.124 will work with the DCO, and the timezone files should be obtain from the Oracle DBA. DO NOT apply Oracle 10 timezone files to the DCO installation.
The affected DCO files are $SYBASE/DCO-12_6/oracore/zoneinfo/readme.txt, timezlrg.dat and timezone.dat.
To apply the patched files outlined above do the following:
- shutdown the DCO server.
- make a backup copy of the files listed above.
- copy the new patched files into the directory listed above.
- restart the DCO server.
To determine what level the timezone files of a database are at, issue one of the following queries to the DCO:
Oracle 10 target database -
1> select version from v$timezone_file
(1 row affected)
Oracle 9 target database -
1> select case COUNT(DISTINCT(tzname))
2> when 183 then 1
3> when 355 then 1
4> when 347 then 1
5> when 377 then 1
6> when 186 then case COUNT(tzname) when 636 then 2 when 626 then 3 else 0 end
7> when 185 then 3
8> when 386 then 3
9> when 387 then case COUNT(tzname) when 1438 then 3 else 0 end
10> else 0 end VERSION
11> from v$timezone_names
(1 row affected)