• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
TSB Alfresco Cobrand White tagline

Technology Services Group

  • Home
  • Products
    • Alfresco Enterprise Viewer
    • OpenContent Search
    • OpenContent Case
    • OpenContent Forms
    • OpenMigrate
    • OpenContent Web Services
    • OpenCapture
    • OpenOverlay
  • Solutions
    • Alfresco Content Accelerator for Claims Management
      • Claims Demo Series
    • Alfresco Content Accelerator for Policy & Procedure Management
      • Compliance Demo Series
    • OpenContent Accounts Payable
    • OpenContent Contract Management
    • OpenContent Batch Records
    • OpenContent Government
    • OpenContent Corporate Forms
    • OpenContent Construction Management
    • OpenContent Digital Archive
    • OpenContent Human Resources
    • OpenContent Patient Records
  • Platforms
    • Alfresco Consulting
      • Alfresco Case Study – Canadian Museum of Human Rights
      • Alfresco Case Study – New York Philharmonic
      • Alfresco Case Study – New York Property Insurance Underwriting Association
      • Alfresco Case Study – American Society for Clinical Pathology
      • Alfresco Case Study – American Association of Insurance Services
      • Alfresco Case Study – United Cerebral Palsy
    • HBase
    • DynamoDB
    • OpenText & Documentum Consulting
      • Upgrades – A Well Documented Approach
      • Life Science Solutions
        • Life Sciences Project Sampling
    • Veeva Consulting
    • Ephesoft
    • Workshare
  • Case Studies
    • White Papers
    • 11 Billion Document Migration
    • Learning Zone
    • Digital Asset Collection – Canadian Museum of Human Rights
    • Digital Archive and Retrieval – ASCP
    • Digital Archives – New York Philharmonic
    • Insurance Claim Processing – New York Property Insurance
    • Policy Forms Management with Machine Learning – AAIS
    • Liferay and Alfresco Portal – United Cerebral Palsy of Greater Chicago
  • About
    • Contact Us
  • Blog

Documentum 5 vs. 6, Databases and Dates: Does Anybody Really Know What Time It Is?

You are here: Home / Documentum / D6 / Documentum 5 vs. 6, Databases and Dates: Does Anybody Really Know What Time It Is?

July 21, 2011

When EMC revealed Documentum 6 a few years ago, they made a subtle—yet important—change to the way Documentum would store dates and times in the database. While most clients would never even notice the change, some of us would. In particular, when it becomes necessary to access the underlying database tables, like OpenMigrate can when preserving modification dates, confusion can be the order of the day.

Documentum and Databases

Documentum supports a variety of back-end databases, and uses the database to store all document metadata, custom and built-in. Each Documentum object type is represented by several physical database tables and several views. Documentum then uses the native database datatypes, logically mapped to Documentum’s own datatypes. Most Documentum clients never need worry about any of this: it “just works”. Client systems use the DFC (or DFS) and DQL (or API calls) to query and manipulate documents and their metadata, and Documentum takes care of mapping the changes to the underlying database.

OpenMigrate and Modification Dates

OpenMigrate, TSG’s open source migration framework, normally uses the DFC for all of its communication with Documentum. Whether executing DQL or creating and working with objects, the DFC gets the job done for almost every single migration feature.
The exception is a commonly requested migration feature: When migrating into Documentum, preserve a document’s modification date from the original system. There is simply no way to do this via DFC or DFS or DQL or API. While it is syntactically correct to issue a DQL UPDATE statement to change r_modify_date, the act of executing the UPDATE tells Documentum to—you guessed it—update the r_modify_date field to the current date and time.

This is where OpenMigrate differentiates itself from other migration tools: It can preserve the modification date. It does so by stealthily executing a SQL UPDATE statement against the dm_sysobject_s table after a document has been completely migrated into Documentum. TSG had great success with numerous 5.x migrations.

Documentum 5 vs. 6: The GMT Changeover

Prior to Documentum 6.0, dates were stored in the database as local dates. If I’m in Chicago in the summer and I save a document at 10:00 a.m., DQL and SQL queries both return the time as 10:00 a.m. Simple, right?

But starting with Documentum 6.0, dates are now stored in the database in GMT time. So using the same document saved at 10:00 a.m., DQL returns the date as 10:00 a.m.; but SQL returns the date as 3:00 p.m. Not so simple.

It is fairly straightforward to handle this case for a single time. However, modifying OpenMigrate to handle any case proved troublesome. First, our timestamp component needed to know the Documentum version on both sides of the migration, to understand whether to apply an offset. Then the simple case, applying a fixed time zone offset, was pretty straightforward. But daylight savings time complicates things. Unfortunately, Java does not provide a simple mechanism for determining the offset between and date/time and GMT while accounting for daylight savings time.

Conclusions

EMC’s change from local time to GMT in the underlying database, while invisible to most people, can cause headaches for anybody accessing the underlying database. After some painful trial and error, OpenMigrate can once again preserve modification dates during a migration.

If anyone has had similar experiences with the date changes between Documentum 5 and 6, please post your experiences here. And keep your fingers crossed that Documentum 7 won’t implement yet another scheme!

Filed Under: D6, Documentum, OpenMigrate, Tech Tip, Upgrades Tagged With: D6, Documentum, Documentum Upgrade, OpenMigrate

Reader Interactions

Comments

  1. Paras Jethwani says

    July 21, 2011 at 6:55 pm

    Hi,

    I was wondering that wouldn’t storing timestamps in GMT simplify time-conversions in a Documentum Deployment that is spread across multiple time-zones?

    Apart from the r_modify_date scenario with direct database access – moving a document from one CS to another CS in different timezones (as part of a business process)- will preserve timestamps if both servers store the timestamps in the same format. (I am not sure if DFC 5.x will do the timezone conversion automatically)

    regards

    Reply
  2. Todd Pierzina says

    July 22, 2011 at 9:21 am

    Hi Paras,

    You’re absolutely correct. If you use only DFC/API/DQL, you’ll never know the difference. It is only when we hit the database tables directly that there is an issue.

    Reply
  3. Nitin Bajaj says

    March 7, 2012 at 11:58 pm

    I think the date storage change in D6 is good, it is logical to store dates in one timezone(universal timezone) and display it according to client timezone. This really help applications having clients distributed globally

    However I found a small issues related to DST, I hope they will fix it soon. I will post the issue number soon.

    Reply
    • Nitin Bajaj says

      March 8, 2012 at 12:14 am

      the issue number is WDK-5430

      Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Search

Related Posts

  • Migrating From Documentum with OpenMigrate: Best Practices
  • Documentum Upgrade – Understanding Impact of WDK Development
  • Documentum 6.5 Upgrade – Character Encoding Issues
  • Web Content Curating and Publishing (WCM) in Documentum and Alfresco
  • Documentum to Portal Consistency Checker – Proof of Concept
  • Documentum – Top 12 Tips
  • D6.5 Upgrades and Complex Passwords
  • Documentum Full Text Search with Lucene – Honoring ACL Security
  • Documentum – What’s Next Updated for 2010
  • Documentum Client for Outlook, Notes Connector, iPhone and other thoughts on Content Management and Email

Recent Posts

  • Alfresco Content Accelerator and Alfresco Enterprise Viewer – Improving User Collaboration Efficiency
  • Alfresco Content Accelerator – Document Notification Distribution Lists
  • Alfresco Webinar – Productivity Anywhere: How modern claim and policy document processing can help the new work-from-home normal succeed
  • Alfresco – Viewing Annotations on Versions
  • Alfresco Content Accelerator – Collaboration Enhancements
stacks-of-paper

11 BILLION DOCUMENT
BENCHMARK
OVERVIEW

Learn how TSG was able to leverage DynamoDB, S3, ElasticSearch & AWS to successfully migrate 11 Billion documents.

Download White Paper

Footer

Search

Contact

22 West Washington St
5th Floor
Chicago, IL 60602

inquiry@tsgrp.com

312.372.7777

Copyright © 2023 · Technology Services Group, Inc. · Log in

This website uses cookies to improve your experience. Please accept this site's cookies, but you can opt-out if you wish. Privacy Policy ACCEPT | Cookie settings
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT