Documentum : Locale, Date Storage, Update of Last Modification Date / Last Modifier (r_normal_tz, r_tz_aware, r_modify_date)

Hi,
Just a post concerning the locale, date storing and updating of r_modify_date/r_modifier fields in Documentum, indeed, these fields are overridden automatically during the document’s saving. Thus, it’s necessary to store and set the previous values of these fields. An other solution could be the creation of a custom attribute ‘processing_date’ corresponding to the date of action/request on objects.

Locale / Date Storage :

• The dm_docbase_config.r_normal_tz property of docbase explains how Content Server stores dates in the repository. Its value is set as seconds. If set to 0, all dates are stored in UTC time. If set to an offset time zone offset from UTC time, then, Content Server stores all date values based on the time identified by the time zone offset. For example, if the offset represents the Paris Time Zone, the offset value is +1*60*60 that to say +3600 seconds.

  The dm_docbase_config.r_tz_aware if set to FALSE, then, the Content Server is not aware of the time zone.

  The default settings is r_normal_tz=0, r_tz_aware=T for DCTM 6+. If the property r_normal_tz is set to an offset value, this value was not set manually but certainly during a repository upgrading.
   

• Content Server returns the dates with taking account of r_normal_tz property of docbase config corresponding to Locale of date storing dm_docbase_config.r_normal_tz.
   
• All dates extracted at DFC/DQL level are DATE_FROM_DB + XXX seconds – r_normal_tz seconds where XXX is the number of seconds between the CS Locale and the UTC Locale.
   
• For example, if the CS server has a Locale UTC+1 (PARIS) and the dates are stored in repository in UTC time (LONDON), then, the dates returned by DFC/DQL level will be “DATE_FROM_DB + 3600 – 0” with 3600seconds/1hour = it’s the difference between our Locale UTC+1 (PARIS) and UTC Time (LONDON).
   
• So, for updating in DATABASE it’s also necessary to consider the dm_docbase_config.r_normal_tz property of docbase with the formula DATE_TO_STORE_IN_DB – XXX seconds + r_normal_tz seconds where XXX is the number of seconds between the CS Locale and the UTC Locale.
   
• For example, if the Locale of CS has a Locale UTC+1 (PARIS) and the dates are stored in repository in UTC time (LONDON), then, the dates must be stored “DATE_TO_STORE_IN_DB -3600 + 0” with 3600seconds/1hour = it’s the difference between our Locale UTC+1 (PARIS) and UTC Time (LONDON).
   

Example of a document:

---- In Web-based client application :
090xxxxxxx60 - 29/02/2006;17:57

---- In dqMAN:
090xxxxxxx60 - 29/02/2006 17:57:37

---- In DA:
090xxxxxxx60  2/29/2006 5:57 PM

---- In database:
SELECT r_object_id, TO_CHAR(r_modify_date,'DD/MM/YYYY HH24:MI:SS') FROM dm_sysobject_s where r_object_id in ('090xxxxxxx60');
090xxxxxxx60	29/02/2006 16:57:37

 
Last Modification Date : r_modify_date
The use of DFC/DQL/API resets and doesn’t override this field r_modify_date. So, the unique solution to modify the r_modify_date field by bypassing completely Documentum is to modify directly this r_modify_date field in the dm_sysobject_s table:

EXECUTE exec_sql WITH query= 'update dm_sysobject_s set r_modify_date= TO_DATE(''12/01/2016 08:39:17'',''DD/MM/YYYY HH24:MI:SS'') where r_object_id= ''090xxxxxxxxxxxxxx''';
OU
EXECUTE exec_sql WITH query= 'update dm_sysobject_s set r_modify_date= TO_DATE(''12/01/2016 10:39:17'',''DD/MM/YYYY HH24:MI:SS'')-2/24 where r_object_id= ''090xxxxxxxxxxxxxx''';

 
 
EXAMPLE 1: JAVA / SQL

// Return the "dm_docbase_config.r_normal_tz" parameter from docbase config
public int geNormalTzFromDocbaseConfig(IDfSession session) throws Exception{
	IDfQuery query = new DfQuery();
	query.setDQL("select r_normal_tz  from dm_docbase_config;");
	IDfCollection collection = query.execute(session, IDfQuery.DF_READ_QUERY);
	int r_normal_tz = 0;
	while(collection.next()) {
		r_normal_tz = collection.getInt("r_normal_tz");
	}
	return r_normal_tz;
}
	

…
int r_normal_tz = geNormalTzFromDocbaseConfig(session);
Calendar valueDateCalendar4DB = GregorianCalendar.getInstance();
valueDateCalendar4DB.setTimeInMillis(otherVersionDocumentModifyDate.getDate().getTime());
valueDateCalendar4DB.add(Calendar.SECOND, r_normal_tz); // r_normal_tz 
valueDateCalendar4DB.add(Calendar.SECOND, -3600); // - 3600s => -1h
//
DateFormat df = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
String modifyDateString = df.format(valueDateCalendar4DB.getTime());
String queryDql = "EXECUTE exec_sql WITH query='update dm_sysobject_s set r_modify_date=TO_DATE(''"+modifyDateString+"'',''DD/MM/YYYY HH24:MI:SS'') where r_object_id=''"+otherVersionId+"''';";

IDfQuery query = new DfQuery();
query.setDQL(queryDql);
query.execute(getSession(), IDfQuery.DF_EXEC_QUERY);

 
 
EXAMPLE 2: JAVA / SQL

StringBuilder sql = new StringBuilder();
sql.append("UPDATE ").append(dfSession.getDocbaseOwnerName()).append(".DM_SYSOBJECT_S");
sql.append(" SET ");
sql.append(" R_MODIFIER = '").append(modifier).append("',");

DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");		
dateFormat.setTimeZone(TimeZone.getTimeZone("UTC"));

sql.append(" R_MODIFY_DATE = to_date('").append(dateFormat.format(modifyDate.getDate())).append("', 'DD-MM-YYYY HH24:MI:SS')");
sql.append(" WHERE R_OBJECT_ID = '").append(dfSysObject.getObjectId().getId()).append("'");

IDfApplyExecSQL dfApplyExecSQL = (IDfApplyExecSQL) DfAdminCommand.getCommand(DfAdminCommand.APPLY_EXEC_SQL);
dfApplyExecSQL.setQuery(sql.toString());
IDfCollection coll = dfApplyExecSQL.execute(dfSession);
try {
	if (coll.next()) {
		IDfValue dfValue = coll.getValueAt(0);
		if (!dfValue.asBoolean()) {
			throw new DfException("Unable to rollback modifier/modify date with sql statement : " + sql);
		}
	} else {
		throw new DfException("EXEC_SQL return no result !");
	}
} finally {
	coll.close();
}

That’s all!!!

Huseyin OZVEREN