db-crawler, a database search utility for Documentum

Working with Documentum is often a frustrating experience, and one of the main causes is the lack of meaningful error messages. Be it an encouragment to the administrator or developer to refer to the official knowledge base or a gap in the user-friendliness department, this results in the dreaded venture of investigating the origin of the error, which can take hours if not days of laborious and boring work with little chance for a reward, all the while end users are complaining that nothing works any more.
The last time I was confronted to this situation, the error looked quite benine at first:

Just dump the above document with id 090f4517812cbdc7 and look at its permission, the parent folder’s ones, see if the current user is allowed to save the document, nothing particularly transcendent, right ? Wrong. Firstly, the given id is not the same as the current document’s that is being saved. So, where did the server pull it from ? Secondly, an attempt at dumping that id confirms that it does not exist. Now, the nightmare begins. It’s kind of like you just entered the twilight zone actually. Inconsistencies such as this one are difficult to troubleshoot and the crude error message was for sure of no help. I first hypothesized that the given id was somehow linked to the current document either through a relationship or a tbo somewhere but the developers confirmed that they had configured none. So the next obvious step is to doubt them and prove them wrong, and to do this the whole repository had to be searched for that id because somewhere in a type, deeply buried in some attribute, that id had to be contained, referencing the document to be saved, either as a standalone value or embedded in some string. This was challenging enough for me to write this little one-liner – the db-crawler – to search for that string in the whole docbase, actually the whole schema, i.e. at the database level since working from the RDBMS gives access to a considerably much richer set of functions, and this is what I’m going to show you in the next paragraph. To close this anecdotic introduction, I’ll just say that I did found a few occurrences of the given id but they lead nowhere. The actual root cause was a bad DFC cache that somehow desynchronized from the repository and went its own way. The morale of the story is that whenever one encounters weird error messages, the first thing to try is to empty the DFC cache because it probably became stale at some point (maybe the repository was upgraded, new applications deployed, doctypes modified, etc.). The good thing, as it seems there is always some benefit to even the peskiest situations, is that I had the opportunity to dust an ancient technique and share it with you here.

The crawler

As the RDBMS used by that repository was Oracle, we’ll work mainly from within the powerful command-line tool sql*plus. The trick to crawl the database schema is first to get the name of the tables of interest from the dictionary. With Oracle, the view USER_TAB_COLS is the one to consult. Here are the most useful columns:

That view contains one row for each column of each table in the user’s schema. So the crawler just has to get the names of all the columns of all the relevant tables returned by USER_TAB_COLS and generate a SELECT statement against those columns with the string to look for as the condition, e.g. the object id 080f451780000491 from the docbase. Here is how:

The relevant tables are the ones that Documentum uses to store the object’s metadata and other technical data, typically the tables ending by _S for single-valued attributes and _R for repeating attributes. All the other objects in the schema, such as the ones named like %_SP or %_SV, are views built upon the previous tables are can therefore be ignored.
For more sophisticated searches, regular expressions are better suited, as shown below:

Oracle’s regular expressions are quite complete because in addition to being POSIX-compliant, they also support the perl’s non-greedy syntax; see the SQL Reference Manual for details. In the present case, we don’t have many details about the context of the given id so we use the widest possible criteria in our search, and the traditional LIKE pattern-matching condition would be plenty sufficient but let’s switch to regular expressions for generalization (too much is better that not enough, right ?).
The above statement will output a list of SELECT statements, one per column to search, as illustrated below:

In order to execute them at once, they must be enclosed in an SQL script so let’s add some sugar. Still from within sql*plus:

Finally, let’s execute the generated sql script and capture its output into a file:

The output is redirected to the file db-crawler.out and can be searched using the familiar commands e?grep with the same search string as in the SQL statement.

Other RDBMS supported by Documentum, such as SQL Server and PostgreSQL, obviously have their own dictionary (sys.tables respectively information_schema) and official administration tools (sqlcmd respectively psql),  and thus the same technique can be applied to them.

Some enhancements

As we work inside sql*plus for Oracle RDBMS, let’s make the most of this tool. sql*plus allows us to factor out the searched string using a substitution variables, as follows:

It will be referenced in the script through &search_string. This way, the search string can be edited easily without directly modifying the query’s code, always a good practice in scripting and programming.
Also, some formatting of the sql*plus output is needed to remove spurious headers, line-wrapping and trailing blanks; we’ll do this using set statements and the sed command-line utility.
Before executing it, the generated sql script can be cleaned a bit by removing non executable lines such as sql*plus command output, still inside sql*plus:

Putting everything together plus some additional formatting instructions, we get:

That’s a lot of typing so look down below for an easy to use wrapper script.

One use case: looking for IP addresses

Let’s seach the whole repository’s schema for any ip v4 address expressed as a dot-separated list of 4 quadruplets of 1-byte numbers each. This is useful for example when they should be replaced by their host name counterparts for portability.
The regular expression for an IP adress is:

IP v6 addresses are similarly constructed, except the list is an octuplet of colon-separated 16-bit words expressed in hexadecimal:

So, to look for any hard-coded IP v4 address in a complete repository’s schema, we would only set the variable search_string to the above regexp and use the regexp_like condition, as follows:

Still in sql*plus, let’s grep the output file db-crawler.out using the same regexp and from the sql*plus environment:

Some manual editing is necessary here as the above regexp also matches strings such as these, which are obviously version numbers of the content server’s components. Fortunately, in the present case, those are rare.
grepping can also be done from the shell’s command-line as follows:

Factoring the search string regexp, especially such complex ones, would increase the readability and lesser the likelihood of typing or copy/pasting mistakes. See down below for a wrapper that takes into account the above point, all the sql*plus statements and the final grepping.

Looking for dates

Let’s now look for all the attributes containing a date. While the format of a displayed date depends on the current locale, dates are stored using the database’s internal format, which is likely some binary one, e.g. a counter of elapsed seconds since some reference date in the past. However, our search expression directly accesses the attribute, which is silently converted to a string by the database server using some criteria (e.g. the time zone and locale). We need therefore to force the date column to a representation that can be compared to the given search string. The easiest way to do this is to tell the sql*plus session to format dates using a conventional, common format which we will also use in the search string, as shown below:

A regular expression for this date format could be:

A popular date format in IT is the basic ISO one:

but it is less user-friendly so we won’t use it here.
However, forcing the session date format only works when comparing stand-alone values in date columns, and not always for a date embedded in strings where it may have been converted to some format by the application or the content server itself. For example, the following values are returned from my test repository:

Here a conversion to M/D/YYYY HH24:MI:SS was performed by the content server while setting DM_AUDITTRAIL_S.OBJECT_NAME. A search for the first date regexp would not be able to return those values because their day part uses one digit instead of two; similarly, the second date regexp would not match either because their month part is greater than 12. So, searching dates exhaustively may require several passes with different, more relaxed search strings until all the formats used in strings, or a slightly more flexible regexp such as the following one, are tried out:

where the date components days and months may have 1 or 2 digits, the date and time field separators can be any character (common ones are / and – for dates, : for times), the separator between the date and time part is anything (commonly a space or the letter T such as in logs produced by a java program), and the optional year component has 2 or 4 digits, although this is quite ambiguous even though the rule in Oracle “RRRR” datetime format can help solve the ambiguity.

An interesting fact is shown in the output below:

Those seemingly meaningless dates are actually NULLDATEs and show how the content server stores them in the Oracle database. This is RDBMS-dependent; for example, in PostgreSQL (and also in SQL Server), the content server uses 1/1/1753 for the NULLDATE. Therefore, the underlying RDBMS is to be considered when searching NULLDATEs and the appropriate date literal be used in such cases.

A cool wrapper

To simplify the usage of the db crawler and include the date considerations above, the bash wrapper script below can be used:

Let’s apply this wrapper to the use case below.

Looking for a given host name

Let’s suppose now that we want to move or clone a docbase from a host to another one, maybe running a different O/S with some differences due to the implementation. After we applied to procedure, we suspect that the former host name is still referenced elsewhere in the new repository but we have no idea where. We changed it in the well-known places such as DM_SERVER_CONFIG and DM_MOUNT_POINT but there may still be references to it elsewhere. Clearly, we must search all the columns in all the tables of the repository’s schema for the given host name string, which is very easy now by using the wrapper:

Example of execution:

We can see that the host name docker was referenced in the attributes DM_AUDITTRAIL.HOST_NAME, DM_CLIENT_REGISTRATION.HOST_NAME, DM_CLIENT_RIGHTS.HOST_NAME, DM_MOUNT_POINT.HOST_NAME, DM_SERVER_CONFIG.R_HOST_NAME but also in DM_SYSOBJECT.OBJECT_NAME and R_LOCK_MACHINE (there were likely locked documents in the source repository), DM_SERVER_CONFIG.WEB_SERVER_LOC, and DMR_CONTENT.SET_CLIENT. Some of those attributes such as DM_MOUNT_POINT.HOST_NAME could explain a potential issue in the new repository if not set adequately, while others such as DMR_CONTENT.SET_CLIENT should have no impact if carried over as-is into the new repository.

A note about DM_SYSOBJECT_S

Since DM_SYSOBJECT sits at the top of the type hierarchy of most persistent objects, it is linked by most repository objects and search results such as the ones below may be confusing:

Clearly, due to their technical apparence, these rows may belong to objects deriving from DM_SYSOBJECT and belonging to different types; in order to determine which ones, further digging is necessary, e.g.:

We can see for example that lines 8 and 9 from the crawler’s output correspond to repository’s types dm_cont_transfer_config and dm_client_rights. Thus, if something needs to be corrected in the repository, that’s where it should be done preferably in order to minimize the likelyhood of introducing corruptions.

Renaming a docbase/changing its docbase id

Another use case, albeit a far fetched and fragile one, is when a repository must be renamed or its id changed. While the safe, traditional approach is to create a new docbase with the new name/id and exporting/importing the content, a more acrobatic approach would use the crawler to identify all the places in the underlying tables where those values are referenced, and change them.
Clearly, in addition to the docbase’s schema, there are several locations in configuration files where such information is referenced too that should be edited. As most of those files are text files, the find command with a sed invocation can quickly do that in one pass. As to the schema’s tables, a search with the regexp ‘[0-9]{2}docbase_id[0-9a-f]{8} can identify all the ids to change. Something similar could be used for the repository’s name to change. Of course, a great deal of testing should be done, and maybe a call to OpenText to discuss this approach wouldn’t hurt either. Moreover, since they also distribute their own docbase renaming/id changing tool, MigrationUtil (see the following blog articles Documentum – MigrationUtil – 1 – Change Docbase ID and Documentum – MigrationUtil – 2 – Change Docbase Name), it could be interesting to compare both techniques.

Conclusion

The db-crawler has a lot of potential to help troubleshoot puzzling problems, and identify inconsistencies and corruptions in repositories. As all the RDBMS have a dictionary, it is easy to adapt it to a different underlying one. It can even be used outside the context of Documentum repositories, directly in the context of databases.
As a typical repository’s schema contains more than 2’000 tables (more if customized doctypes have been introduced), with possibly millions of documents (corresponding to one or more rows spread over several  database tables), a possible enhancement is to speed the search up by parallelizing the execution of the generated SELECT statements, an easy to do task using a divide-and-conquer technique or a tool such as GNU Parallel.
Currently, the tool is very basic as it addresses a simple need but it does not have to stay this way so feel free to add useful features and optimizations.