Converting Object Model String Columns to Time

I migrated metadata and content out of a proprietary content management system into Documentum using custom-developed migration code.  Unfortunately, sometime after I was done, the development team realized that one of the document properties that was migrated as a String should have been migrated as a Time.   This realization came when they discovered that to implement a business rule, the value represented by the String needed to be manipulated mathematically.  Sure, they could have coerced the value in the application code, but we decided that to make the design clearer, we should change the type of the property in the Documentum object model to better represent the reality of the data.  (By the way, double-checking the original content management system revealed that the property really was persisted in the database as a String.  The propriety content management system must have been doing some coercion also.)  So, we were faced with the question of how to change the type of a property in the Documentum object model while retaining its values and name.

The answer turned out to be pretty simple.

  1. First, add a temporary property to the Documentum object model. In our case, a Time property named temp.
  2. Run a DQL pass-through query to update this temporary property with a cast of the original property, like this:

    execute exec_sql with query = ‘update sr_document_s set temp = cast(rpt_date as datetime) where cat_no is not null’

    (cast is the ANSI SQL-compliant coercion command.)

  3. Delete the values in the rpt_date property using DQL.

    update sr_document objects set rpt_date = null

  4. Remove the property from the object model using DA. Save the update to the object model.
  5. Using DA, recreate the rpt_date property of type Time.
  6. Use DQL to copy the values in the temp property back to the rpt_date property (now of type Time).

    update sr_document objects set rpt_date = temp where temp is not null

  7. Delete the values in temp and remove the temp property like you did in step #3.

Now the values contained in the rpt_date property are stored as Time values.  Note that due to Documentum clients’  automatic time conversion based upon timezone, the time conversion may appear to be off by serveral hours.  Check the actual database tables to ensure they are not.

Advertisements

About Scott
I have been implementing Documentum solutions since 1997. In 2005, I published a book about developing Documentum solutions for the Documentum Desktop Client (ISBN 0595339689). In 2010, I began this blog as a record of interesting and (hopefully) helpful bits of information related to Documentum, and as a creative outlet.

One Response to Converting Object Model String Columns to Time

  1. doquent says:

    Scott,

    Thanks for sharing this useful post.

    Some time ago, I had to deal with a similar situation. The difference was that we were getting date in the string attribute on an ongoing basis (rather than a one-time migration) from a scanning system. Further, the date string was sometimes malformed. So we used a job to convert the string value to a date value in another attribute. Further, we intercepted and reported malformed date strings, which were rectified manually by viewing the scanned document.

    Pawan

    Like

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

%d bloggers like this: