Time in Documentum

Since D6 release EMC changed the manner of storing dates in database – now CS stores dates in UTC by default, the problem is the new settings are totally undocumented.

Misleading documentation

Powerlink states:

  1. The r_normal_tz property, in the docbase config object controls how Content Server stores dates in the repository. If set to 0, all dates are stored in UTC time. If set to an offset value, dates are normalized using the offset value before being stored in the repository. If set to an offset value, the property must be set to a time zone offset from UTC time, expressed as seconds. For example, if the offset represents the Pacific Standard Time zone, the offset value is -8*60*60, or -28800 seconds. When the property is set to an offset value, Content Server stores all date values based on the time identified by the time zone offset.
    Refer to the Content Server Administration Guide V6.0 for more information about how the value set for this attribute is used to set the timestamp, depending on whether the client is 6.0 and up or pre-6.0.
  2. To answer the question on how this value is set:
    In a new Documentum 6 or later repository, r_normal_tz is set to 0. In a repository upgraded from a release prior to Version 6, r_normal_tz is set to the offset representing Content Server local time. Therefore, if set and this value was not set manually, this was probably an upgrade from a pre-6.0 version Docbase.
  3. The r_tz_aware set to FALSE makes the Content Server not aware of the time zone.
    This attribute is not documented presently (I do not know why), but if the customer’s r_normal_tz is set to a non-zero value, then they probably upgraded their docbase and possibly FALSE is the default value for this attribute in the case of an upgrade.

This KB article is absolutely incorrect, lets explain.

r_normal_tz

At first glance it is a totally stupid idea to normalize dates using static offset, how are they going to manage daylight saving time? Change r_normal_tz every half of year and restart server? Lets check what happens if we change r_normal_tz in docbase config:

Default settings (r_normal_tz=0, r_tz_aware=T):

Connected to Documentum Server running Release 7.0.0100.0603  Linux.Oracle  
1> select r_normal_tz, r_tz_aware, r_creation_date from dm_docbase_config  
2> go  
r_normal_tz   r_tz_aware    r_creation_date  
------------  ------------  -------------------------  
           0             1  10/31/2013 13:29:41  
(1 row affected)
SQL> ALTER SESSION set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';  
  
Session altered.  
  
SQL> SELECT r_creation_date AS r_creation_date_utc,  
       CAST (  
          (FROM_TZ (CAST (r_creation_date AS TIMESTAMP), '+00:00')  
              AT TIME ZONE 'Europe/Moscow') AS DATE)  
          AS r_creation_date  
  FROM dm_docbase_config_sp;  
  
R_CREATION_DATE_UTC R_CREATION_DATE  
------------------- -------------------  
2013-10-31 09:29:41 2013-10-31 13:29:41 

UTC+10 offset:

Connected to Documentum Server running Release 7.0.0100.0603  Linux.Oracle  
1> select r_normal_tz, r_tz_aware, r_creation_date from dm_docbase_config  
2> go  
r_normal_tz   r_tz_aware    r_creation_date  
------------  ------------  -------------------------  
       36000             1  10/31/2013 09:29:41  
(1 row affected) 

UTC-10 offset:

Connected to Documentum Server running Release 7.0.0100.0603  Linux.Oracle  
1> select r_normal_tz, r_tz_aware, r_creation_date from dm_docbase_config  
2> go  
r_normal_tz   r_tz_aware    r_creation_date  
------------  ------------  -------------------------  
      -36000             1  10/31/2013 09:29:41  
(1 row affected)  

So, value of r_normal_tz parameter has nothing in common with timezone: if r_normal_tz=0 CS converts dates to UTC before storing in database, if r_normal_tz!=0 CS stores dates without conversion, e.g. database dates are local. Actually this new behavior has some issues:

  1. When you use some reporting software and write reports against database you should take into account this “feature” and cast dates to local time
  2. UTC timescale is always straightforward but localtime not due to DST. So, there are some ambiguities in converting localtime to UTC and backward, i.e. 2010-10-30 22:30:00 UTC and 2010-10-30 23:30:00 UTC dates have the same representation for Moscow tmezone, so, if you were “lucky” and created document between 2010-10-30 22:30:00 UTC and 2010-10-30 23:30:00 UTC you can’t find it by creation date, because DFC will convert dates you input in dates after 2010-10-30 23:30:00 UTC

r_tz_aware

My dctmpy library is able to emulate two major versions of DFC (e.g. pre-D6 and post-D6) and allows to view traffic passed between CS and client easier than capture network traffic or parse dfc logs. Test script:

#!python  
from dctmpy.docbase import Docbase  
  
  
def main():  
    session = Docbase(host="192.168.2.56", port=12000)  
    session.authenticate("dmadmin", "dmadmin")  
    for e in session.query("SELECT r_creation_date as dt FROM dm_docbase_config"):  
        print e.__buffer__  
  
  
if __name__ == "__main__":  
    main() 

little patch to show serialized data:

Index: dctmpy/obj/collection.py  
===================================================================  
--- dctmpy/obj/collection.py    (revision 34)  
+++ dctmpy/obj/collection.py    (working copy)  
@@ -110,7 +110,9 @@  
  
class CollectionEntry(TypedObject):  
     def __init__(self, **kwargs):  
+        b = kwargs.get("buffer")  
         super(CollectionEntry, self).__init__(**kwargs)  
+        self.__buffer__ = b[0: len(b) - len(self.buffer)]  
  
     def readHeader(self):  
         pass 

post-D6 traffic (note that now my server thinks that all previous dates are local though they was stored initially in UTC):

OBJ QR 0 0 0 1  
B S 4 2013-10-31T05:29:41Z  
0  
0  

pre-D6 traffic:

OBJ QR 1  
xxx Oct 31 09:29:41 2013  
0

So, D6 clients use ISO 8601 format to transfer dates while old clients use some “proprietary” protocol. Now what will happen if we switch r_tz_aware to false?

DFC:

1> select r_normal_tz, r_tz_aware, r_creation_date from dm_docbase_config  
2> go  
r_normal_tz   r_tz_aware    r_creation_date  
------------  ------------  -------------------------  
      -36000             0  10/31/2013 09:29:41  
(1 row affected)  

python:

OBJ QR 0 0 0 1  
B S 4 xxx Oct 31 09:29:41 2013  
0  
0

So, setting r_tz_aware to false switches “date protocol” to pre-D6 version. In practice this means that if you have DFC-clients, that use timezone different from CS, those clients will send and receive wrong data:

~]$ cat > Test.java  
import com.documentum.com.DfClientX;  
import com.documentum.fc.client.IDfSession;  
import com.documentum.fc.common.DfException;  
import com.documentum.fc.common.DfLoginInfo;  
  
/** 
* @author Andrey B. Panfilov <andrew@panfilov.tel> 
*/  
public class Test {  
  
    public static void main(String[] argv) throws DfException {  
        IDfSession session = new DfClientX().getLocalClient().newSession(  
                "ssc_dev", new DfLoginInfo("dmadmin", "dmadmin"));  
        System.out.println(session.getDocbaseConfig()  
                .getTime("r_creation_date")  
                .asString("yyyy.MM.dd G 'at' HH:mm:ss z"));  
        session.disconnect();  
    }  
  
}  
~]$ javac Test.java  
~]$ java Test  
2013.10.31 н.э. at 09:29:41 MSK  
~]$ java -Duser.timezone=Asia/Vladivostok Test  
2013.10.31 н.э. at 09:29:41 VLAT  

DFC

DFC-clients as all java-based clients use their own calendar instead of operating system calendar, also EMC introduced a dfc.time_zone parameter:

# The timezone of this DFC instance.
#
# This value is initialized from the Java Virtual Machine at startup time and
# normally doesn’t need to be specified. Legal values are the timezone IDs
# supported by the Java Virtual Machine.
#
dfc.time_zone =

Now we know that CS and DFC interact with each other using ISO 8601 date format, so what is the purpose of dfc.time_zone parameter? It just helps to initialize instances of SimpleDateFormat, used internally by DFC, with predefined timezone but does not change dates. It’s useful when you want to display dates casted to specific timezone but not able to setup that timezone for current environment (UNIXes has TZ environment variable, Windows – not) or set -Duser.timezone property for java-based application (like IDQL or IAPI):

Connected to Documentum Server running Release 7.0.0100.0603  Linux.Oracle  
Session id is s0  
API> get,c,apiconfig,dfc.time_zone  
...  
Europe/Moscow  
API> get,c,docbaseconfig,r_creation_date  
...  
10/31/2013 09:29:41  
API> set,c,apiconfig,dfc.time_zone  
SET> Asia/Vladivostok  
...  
OK  
API> connect,ssc_dev,dmadmin,dmadmin  
...  
s1  
API> get,c,docbaseconfig,r_creation_date  
...  
10/31/2013 16:29:41

Switching to r_normal_tz=0 from r_normal_tz!=0

Following SQL scenario will help you to generate SQL updates for all date fields:

SET LINES 300
SET PAGES 0
SET TRIMSPOOL ON

  SELECT    CASE
           WHEN ROW_NUMBER ()
                OVER (PARTITION BY utc.table_name ORDER BY utc.column_name) =
                   1
           THEN
              'UPDATE ' || utc.TABLE_NAME || ' SET '
        END
     || utc.COLUMN_NAME
     || ' = DECODE('
     || utc.COLUMN_NAME
     || ', NULL, NULL'
     || ', TO_DATE(''0001/01/01'', ''YYYY/MM/DD''), TO_DATE(''0001/01/01'', ''YYYY/MM/DD'')'
     || ', CAST ((FROM_TZ (CAST ('
     || utc.COLUMN_NAME
     || ' AS TIMESTAMP), ''Europe/Moscow'') AT TIME ZONE ''UTC'') AS DATE))'
     || CASE
           WHEN ROW_NUMBER ()
                OVER (PARTITION BY utc.table_name
                      ORDER BY utc.column_name DESC) <> 1
           THEN
              ','
           ELSE
              ';' || CHR (10) || 'COMMIT;'
        END
FROM user_tab_columns utc, user_tables ut
   WHERE utc.data_type = 'DATE' AND utc.table_name = ut.table_name
ORDER BY utc.table_name, utc.column_name;

2 thoughts on “Time in Documentum

  1. Pingback: Здравствуй, зимнее время! | Documentum in a (nuts)HELL
  2. Pingback: Time in Documentum. MSSQL challenge | Documentum in a (nuts)HELL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s