Advanced DECS usage

Relational data models are a very popular abstraction used in IT. And a abstraction they are rather than a mapping. I haven't come across a relational table in real live, but only documents (the ones signed by a national bank president are my personal favorites). So while document databases, objects and attributes are a better fit to the real world, RDBMS are well understood, come with a powerful query language and are reasonably standardized. Naturally you will come across the requirement to connect Notes and Domino applications to a relational back-end. The options are plenty: ODBC (bad idea), JDBC, LCLX, DECS, LEI or DB2NSF (not mentioning the 3rd party tools). Typically I see the RDBMS connections entangled in code creating more of this. A better way to separate concerns is to remove RDBMS connections from your code and let the server handle that. In Domino you can use DECS (Domino Enterprise Connection Service) and LEI (Lotus Enterprise Integrator) for that. DECS comes with Notes since some R5 version, LEI is happily sold to you by your local IBM sales rep. I will focus on DECS for this post.
The typical DECS use is to define a data connection (tip use OLEDB not ODBC to connect to MS-SQL), a data-form mapping and import the primary keys. In the data form mapping (a.k.a Activity in DECS terminology) you set what events you want to monitor: create, read, update, delete. Typically works like a charm. Any data that is updated on the RDBMS is automatically pulled into the Notes form when opened. The biggest drawback: DECS doesn't monitor record creation or deletion on the RDBMS side (that is what besides other capabilities LEI is made for). So DECS seems to be confined to cases where creation/deletion is limited to a Domino side activity. Also DECS can't trigger stored procedures. With a little creativity however you can push the use of DECS far beyond that. I'll describe some use cases I came across where DECS was used to avoid mixing Domino and RDBMS code in a function or an agent:

• Employee information is stored in a RDBMS as part of the HRMS. The employee ID is populated into the Domino Directory (yes it has a field for that). In an Notes application data is needed from the RDBMS. Instead of writing LCLSX code the application simply looks up the empPara document that is linked to the RDBMS using DECS. The document might not exist yet (if the user never had used that application before). If it does not exist it is created and populated just with the EmpID. When closed and reopened it will pull the employee information from the RDBMS. This is possible since the DECS task only monitors read/update. A scheduled agent removes documents when there is no more match in the Domino directory. In summary: if you can anticipate or know the primary key of a record you need, you can use DECS by not monitoring creation/deletion.
• Based on a workflow stored procedures in a Oracle database need to be triggered. Here some work both on the RDBMS and the Domino side was done, clearly separating both. An auxiliary table was created in Oracle with an INSERT trigger, that would execute the stored procedure using parameters given in that table and write back the success of the operation. Initially it was planned to purge the table regularly (using a Domino agent deleting the documents) but then audit loved the additional documentation, so just archival was established. During the project there were a lot of changes on both ends, however the approach of using a trigger driven table proved to be very efficient to separate the two environments minimizing interference. E.g. one stored procedure would generate a unique identifier according to some obscure, constantly changing rule. By storing that result into the aux table and creating (after reading the document linked to that table) a corresponding Notes document (again create was not monitored in that scenario) application flow was seamless.
• Enterprise parameter management [Updated] was created in a RDBMS application. Many Domino applications would need to use these parameters. Instead of having LotusScript code in every application doing RDBMS lookups DECS was used to populate and update a parameter NSF. In the activity the option "leave values in documents" was selected, so fast selections (like @DBColumn or NotesView.getColumnValues) would work. Since parameter changes happened rarely a copy of the populate keys agent from the DECSAdm database was created that would periodically shut down the activity for the parameter NSF (only the activity not the whole DECS server), pull the keys from the RDBMS based on the activity definition but only insert new keys (the original agent duplicates keys when you run it twice). Lastly it restarts the activities in DECS.

You of course can rightfully ask: why shouldn't I just use some LCLX code to connect. It isn't much more trouble. The answer is short: DECS allows separation of concern. Your Domino developers deal with what they know best: Domino. You retain the interface (and interfaces are the pieces that can make and brake an upgrade of any system) configurable outside of your own code. The whole UI for configuring and mapping has been tried and tested for years and you can open a PMR (the IBM lingo for: bug report) against it for IBM to deal with problems. You can't do that with your own code. DECS also deals with format translation and relives you from the temptation to write inefficient SQL, last not least DECS taking care of connections and connection pooling.