Handy DQL: Find DocApp containing a Type

If several customizations are present in a Documentum repository, it can get tricky to identify where a particular object type comes from. The following DQL can be used to identify the DocApps that contain an object type. Just replace my_object_type with the actual type name below.

select object_name from dm_application
where r_object_id in
(select c.parent_id
from dmr_containment c, dm_app_ref ar, dm_type t
where c.component_id = ar.i_chronicle_id
and ar.application_obj_id = t.r_object_id
and t.name = 'my_object_type')

It is straightforward to modify this DQL to identify containing DocApps for things other than object types.

[UPDATE] The DQL in the original form would only work with DocApps that haven’t been installed more than once in the same repository. The DQL has been corrected by replacing ar.r_object_id with ar.i_chronicle_id.

Cleaning up Locales

DISCLAIMER: The following ideas may not be considered as advice for any purpose, whatsoever. I assume absolutely no liability if any of the following ideas are used for any purpose. Any experimentation related to the following ideas is best done in a completely disposable test environment.

EMC has a an excellent document about Adding Locales to Documentum WDK-based Applications. It explains how to add locales when you want them and how they are used. It also explains what happens in the repository and what needs to be done on the WDK side.

However, it didn’t help when we encountered some weird issues. First, we noticed that on the File > Import screen, the type dropdown had lost all the labels. So the values looked like “dm_document (dm_document)” rather than “Document (dm_document)“. Research on the issue led us to locales in the repository. We found “en,en_GB” on dm_docbase_config.dd_locales. Further, the data dictionary had en_GB records which had labels set to be the same as the type names. We had no information about how en_GB got added in the first place since there hadn’t been a business need to do so. We suspected one or more more docapps related to a product originating in Europe to be the source of the additional en_GB locale.

It got more interesting when we removed en_GB from dm_docbase_config.dd_locales. Webtop File Import continued to behave as if it didn’t matter what dm_docbase_config.dd_locales was set to. It turned out that we had discovered a Webtop bug! Content Server clients must first look at what is set on dm_docbase_config.dd_locales before looking at the data dictionary contents.

It may take a service pack or hotfix to get the bug fixed. In the meantime, we could fix the problem if there was a way to clean up data dictionary for the unwanted locale.

There are multiple ways to address this, with varying degrees of effort and risk involved. These are described below.

Before using any ONE OF THE FOLLOWING options, dm_docbase_config.dd_locales needs to be set to 'en'. This can be done through DA using the UI or through DQL/API.

1. [WDK] Get EMC to provide a hotfix for Webtop. This may take a while and the fix will need to be applied to all the Webtop instances, customized or otherwise.

2. [WDK] Implement a WDK customization to address this problem. Other than the development effort, this option is similar to 1.

3. [SQL] THIS IS A DB CHANGE AND A DATA BACKUP IS THE SMART FIRST STEP.
Copy the label_text from 'en' records (dmi_dd_type_info_sp, dmi_dd_attr_info_sp) to the corresponding 'en_GB' records where label_text = type_name. This will lead to the same label on the UI no matter which locale is used – the exception will be the labels for any type that are already set in en_GB. For completeness, both dmi_dd_type_info_sp and dmi_dd_attr_info_sp should be updated.

Publish data dictionary.

3. [SQL] THIS IS A DB CHANGE AND A DATA BACKUP IS THE SMART FIRST STEP. THIS IS PROBABLY NOT SUPPORTED BY EMC.
Rename the nls_key attribute from 'en_GB' to something like 'xx_XX' (dmi_dd_type_info_sp, dmi_dd_attr_info_sp).

Publish data dictionary.

4. [SQL] THIS IS A DB CHANGE AND A DATA BACKUP IS THE SMART FIRST STEP. THIS IS PROBABLY NOT SUPPORTED BY EMC.

Delete records where nls_key = 'en_GB' (dmi_dd_type_info_sp, dmi_dd_attr_info_sp).

Publish data dictionary.

4. [SQL] THIS IS A DB CHANGE AND A DATA BACKUP IS THE SMART FIRST STEP. THIS IS PROBABLY NOT SUPPORTED BY EMC.

This option is relatively cleaner (probably “too clean” in certain ways) but creates additional work. The first step will cause you to lose data dictionary records for custom types and custom attributes. These can be restored by reinstalling the docapps containing this information. Watch out for couple of risks:
a) If it was one of these docapps that introduced the undesired locale, that will be reintroduced.
b) Make sure that any lifecycles, workflow templates etc. in the docapp are not versioned unintentionally.

Truncate tables:
dmi_dd_common_info_s
dmi_dd_common_info_r
dmi_dd_type_info_s
dmi_dd_type_info_r
dmi_dd_attr_info_s
dmi_dd_attr_info_r

Publish data dictionary. It is probably a good idea to do this through the job and wait for the job to finish. It may take some time but it will restore the default 'en' locale.

Reinstall any custom docapps carefully.

How big is my document?

Of course, I could retrieve the document using one of the available mechanisms (Webtop, DFC, API, etc.) and save it locally and then inspect the file size on the filesystem.

However, if all I wanted to know was the size of the document in the repository one would think it should be easy to tell from the metadata. Well, it is in most cases but if you really wanted to be sure it would take some effort.

The document object (of type dm_sysobject or a subtype) has two attributes r_content_size and r_full_content_size. Most of the time, you could check either of these to get the number of bytes in the content file. However, if the file size is more than 2GB, only r_full_content_size could tell you the correct size.

Is this always true? If you look up the object reference for these attributes, you will find the following in the definition:

Size, in bytes, of the first content file associated with the document.

The key word is “first” and it really means first ever. How can a document have a second content file associated with it. A common scenario is by adding a rendition. For example, you may have a Word document and then you add a PDF rendition. However, this is not a problem since your primary document is still of the size being reported by these atributes.

Now, consider the more interesting scenario. Suppose, you check out the document, make some changes or choose a different local file such that the file size is different and check it in as the same version. Since you are checking in as the same version no new sysobject will be created. However, a new content object (type dmr_content) will be created and associated with the sysobject. The first content object will be disassociated from the sysobject. So the file size is now different but the size attributes on the sysobject are still the same and potentially incorrect!

So where can we get the right information about the size? We need to get closer to the file and look on the content object (dmr_content). The content object is directly associated with the stored file and captures the size in content_size and full_content_size fields. As before, only full_content_size is accurate if the size is more than 2GB.

Identify the content object associated with the sysobject and look at its full_content_size attribute. The following DQL should do it:

select c.full_content_size
from dm_sysobject s, dmr_content c
where s.i_contents_id = c.r_object_id
and s.r_object_id = 'object_id_of_doc'

That’s it, right? Well, most of the time! There is one more exception to consider – when the content file is stored on external storage (It is not clear to me what identifies a storage as external). When the content file is stored on an external storage, the size attributes on the content object are not set. In this case, you cannot really tell the size of the content file without retrieving the file itself.

So how do you know whether the content file is stored on external storage? Look at dmr_content.data_ticket. It is 0 for external storage and for turbo storage. Thus if data_ticket=0 and storage_id does not indicate dm_turbo_store then it is using external storage.