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

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!