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.
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!
Paras Jethwani says
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)
Todd Pierzina says
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.
Nitin Bajaj says
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.
Nitin Bajaj says
the issue number is WDK-5430