1932132 - SAP HANA : Large time difference between application server and HANA database.

Symptom

SAP applications are terminated due to time difference between application servers and database server with an ABAP runtime error, ZDATE_LARGE_TIME_DIFF or ZDATE_ILLEGAL_LOCTIME.
HANA Database SQL Function CURRENT_TIME, CURRENT_TIMESTAMP returns wrong value which is an hour earlier than the actual value.

Other Terms

SAP HANA Database, Timezone, timestamp, current_timestamp, ZDATE_ILLEGAL_LOCTIME

Reason and Prerequisites

1. Prerequisites
  - SAP HANA Database Revision 65 or above
  - Affected Period
    Dailyight saving time start date and end date
  - Affected Location
    The location where the new DST rule changed after 2007 is applied.
 2. Reason
  - Incorrect daylight saving time handling in HANA Database during the affected period mentioned in prerequisites.

Solution

Please maintain timezone tables on your databases regularly. Attachments with updated timezone data will be provided with this note. Updated data is applied by the following:

1. HANA Database Revision 69 or above
1. Create HANA timezone tables in SYSTEM schema.
1. Apply SAP Note 1791342 if ABAP application servers run on top of HANA database.   
2. Import attached timezone configuration tables into SYSTEM schema for HANA standalone database. 
• In a scale-out system, need to import the tables into the master node.
• Copy the attached file "HANATimezoneTables_2015_V08.zip" to the work directory of master node ( /usr/sap/<SID>/HDB<INSTNO>/work )
• cd /usr/sap/<SID>/HDB<INSTNO>/work
• unzip HANATimezoneTables_2015_V08.zip
• In HANA Studio, execute the import query as SYSTEM user 
• IMPORT "TTZZ","TTZD","TTZDF","TTZDV" AS CSV FROM '/usr/sap/<SID>/HDB<INSTNO>/work/HANATimezoneTables_2015_V08' WITH REPLACE AT LOCATION '<master_node_name>:<indexserver_port>';
• In a single-node system, import the attached timezone configuration tables into SYSTEM schema.
3. Set the configuration parameter use_startup_timezone to true in indexserver.ini, statisticsserver.ini, xsengine.ini files if this parameter value had been set to false in previous releases. Be informed that the default value of this parameter is true, so there is no additional configuration needed if it was not set to false in previous releases. 

                Note: HANA Rev69 or above, it is required to apply either the solution specified in step 1.i.1 or 1.i.2 depending on whether a HANA standalone system or a HANA with ABAP application servers is present.

2. HANA Database Revision from Rev65 to Rev68
1. Set the configuration parameter use_startup_timezone to false in indexserver.ini, statisticsserver.ini, xsengine.ini files, the parameter need to be set to false before the DST starts, and will need to be set back to true after DST ends.
2. To set the parameter:
   

   alter system alter configuration ('indexserver.ini', 'SYSTEM') SET ('sql', 'use_startup_timezone') = 'false' with reconfigure; alter system alter configuration ('statisticsserver.ini', 'SYSTEM') SET ('sql', 'use_startup_timezone') = 'false' with reconfigure; alter system alter configuration ('xsengine.ini', 'SYSTEM') SET ('sql','use_startup_timezone') = 'false' with reconfigure;

                Note: HANA Rev65 to Rev68, it is required to apply the solution specified in step 2.i. and 2.ii. for both HANA standalone and HANA with ABAP application servers.    
   3. HANA Database Revision 64 or below
      There should not be any problem in current_timestamp value. However, if DST rules are not up-to-date, then it is possible for the SQL Function utctolocal does not return correct value. 
      To resolve this, 

1. Create HANA timezone tables in SYSTEM schema.
1. Apply SAP Note 1791342 if ABAP application servers run on top of HANA database.   
2. Import attached timezone configuration tables into SYSTEM schema for HANA standalone database. 
• In a scale-out system, need to import the tables into the master node.
• Copy the attached file "HANATimezoneTables_2015_V08.zip" to the work directory of master node ( /usr/sap/<SID>/HDB<INSTNO>/work )
• cd /usr/sap/<SID>/HDB<INSTNO>/work
• unzip HANATimezoneTables_2015_V08.zip
• In HANA Studio, execute the import query as SYSTEM user 
• IMPORT "TTZZ","TTZD","TTZDF","TTZDV" AS CSV FROM '/usr/sap/<SID>/HDB<INSTNO>/work/HANATimezoneTables_2015_V08' WITH REPLACE AT LOCATION '<master_node_name>:<indexserver_port>';
• In a single-node system, import the attached timezone configuration tables into SYSTEM schema.
2. The configuration parameter use_startup_timezone does not exist in Revision 64 or below.

   4. Restart or reconfigure HANA Database to apply the changes made in HANA timezone configuration. 
      To reconfigure the system execute as SYSTEM user:

•             ALTER SYSTEM RECONFIGURE SERVICE( 'indexserver', '', 0 )
•             ALTER SYSTEM RECONFIGURE SERVICE( 'statisticsserver', '', 0 )
•             ALTER SYSTEM RECONFIGURE SERVICE( 'xsengine', '', 0 )

Special considerations with multiple databases:

For multi-tenant systems, each tenant database and the SystemDB are completely separate systems. They can and should have separate tables each, although the contents can be identical.

If cross-database queries are used, it is strongly recommended that all databases involved use the same set of timezone data, otherwise unforeseen issues may occur.

How to check the current timezone:

Current timezone value in HANA is taken from <sidadm> user's OS environmental variable, TZ, during startup.

You can check the current timezone information using the following query:

SELECT * FROM M_HOST_INFORMATION WHERE KEY = 'timezone_name';

If the timezone environmental variable is found from HANA timezone tables in SYSTEM schema, then HANA timezone conversion tables are used. If no timezone information is found in timezone tables in SYSTEM schema, then HANA internal data structures are used as fallback. If the system timezone cannot be found in the timezone information, operating system conversion functionality will be used for some time functionality (e.g. CURRENT_TIME/CURRENT_TIMESTAMP but not LOCALTOUTC/UTCTOLOCAL). Since HANA timezone tables are compatible with ABAP timezone tables, please check if the timezone information is defined in TZONE in TTZZ table.

Presence of the timezone can be checked using the following query:

SELECT TZONE FROM TTZZ;

If the local OS timezone is found in TTZZ but produces unexpected results (e.g. due to identical identifiers for different timezones, different application of DST rules, ...), you can set use_startup_timezone = false to get the timezone data from OS. This applies to HANA revisions 65 or newer.

After applying the change, please check with the following queries:

SELECT CURRENT_TIMESTAMP FROM DUMMY;

SELECT LOCALTOUTC('2014-11-02 01:00:00', '<Timezone>') FROM DUMMY;

DISCLAIMER:
The SAP HANA configuration parameter [sql] use_startup_timezone is an internal parameter and may only be used in the context of this SAP Note. Using such a parameter requires that the SAP HANA system has the revision specified in the SAP Note, the described error situation has occurred, and that SAP support or services has instructed the customer to set/change the parameter. Using internal parameters outside of this given scope is not supported.

Header Data

Released On	18.12.2015 18:21:55
Release Status	Released for Customer
Component	HAN-DB SAP HANA Database
Other Components	HAN-DB-ENG-BW SAP HANA BW Engine
Priority	Recommendations / Additional Info
Category	Program error