Usability - Productivity - Business - The web - Singapore & Twins

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.

Posted by on 21 November 2008 | Comments (7) | categories: Show-N-Tell Thursday


  1. posted by David Leedy on Saturday 22 November 2008 AD:
    Doesn't DECS have a limitation where the backend data needs to be on the same box as the Domino Server?? We run most of our servers on an iSeries. But our business applications and data are on different iSeries. I thought that if I set up DECS I couldn't pull data from the foreign iSeries...

    Is that true?

  2. posted by Stephan H. Wissel on Saturday 22 November 2008 AD:
    @David: Nope. DECS doesn't need data parity. DECS only will be able to pull data it "knows" about, means: where a document with the primary key of that table exists. By not monitoring the creation events you can add missing keys.
    On the iSeries (which is not my core Domain) afaik DECS uses the data catalog, you remote data might work if it is in the local catalog.
    Emoticon smile.gif stw
  3. posted by Gerry Shappell on Saturday 22 November 2008 AD:
    Just to affirm, you dont need your data to reside on the same server. In fact, I have connected to a db2 file on a AIX box through a subnet from a wintel netfinity. Depending on OS's you made need to create connections/accts that will give you access to that other box and file. Also, the argument for DECS and LSX will really vary by application. With some apps, I found it easier to use LSX to pull back the data that I needed for a web application...other times DECS was the answer. Lots of factors to take into acct, the experience of your developers, the traffic of your application, application availability(some backup solutions require DECS to be shut down during backup), web vs notes ui, etc...
  4. posted by Slawek on Sunday 23 November 2008 AD:
    I take it that by "real live" in the third sentence you mean data that people use in their interactions.

    Once could argue that a document is an abstraction and a very high level one at that.

    I would say that people deal with summaries of vast data sets that are otherwise stored in collections or information elements (documents) or collections data elements (records). As such documents are a higher level abstraction partly constructed from relational data.
  5. posted by Stephan H. Wissel on Sunday 23 November 2008 AD:
    @Slawek: with "real live" I refer to tangible documents which predate IT and are still very much in use today. The Universal Business Language (UBL) is expressed as documents (XML in that case). An it it actually the other way around: Documents are the business artifact which we mentally split into tables and relations. A RDBMS is just one, ok - the most prevalent, way to serialize/persist documents. From a business perspective RDBMS are irrelevant, they are just the tool that helps to deliver the business functionality (I'm ending on the RDBMS developers' most hated list for that). For every example why RDBMS are needed and their related SQLs you can produce an XPath or XQuery statement with equivalent functionality.
    Don't get me wrong: RDBMS do a good job in abstracting/persisting/serializing/querying business data, so I wouldn't toss them out wholesale. However they are not the only way. You can see that in Domino's success being a document database and serving business needs very well. You also can see that in DB/2 where XPath is a valid option to ask for data. Check CouchDB, Cache, XMLdb, Perserve and friends for other approaches (which look suspiciously like document/object databases)
    Emoticon smile.gif stw
  6. posted by Slawek on Monday 24 November 2008 AD:
    Documents certainly capture the communication (flow of information) necessary to carry on a business. They are also used to summarize and embellish the data that is the life blood of the machinery of business.

    I am making a distinction between data and information. As such, I see data as machine oriented (re the machinery of business) and information as people oriented. To me the relationship between data and information is that data gets processed to become information, so that it can inform (information -> in-(a)-formation).

    Before it was possible to store vast quantities of data, information was derived from "experience". Now we are in a position to infer information from the data that we gather as part of doing business.

    UBL describes documents such as invoices, POs, etc. These are human readable documents that form part of the paper trail to record business activities. The "real" record of these activities is expressed as ledger entries. These entries then form the basis for planning and forecasting, the result of which is once again expressed in documents for the benefit of people.
  7. posted by Charles Robinson on Tuesday 25 November 2008 AD:
    My problem with DECS was always the initialization of keys. The ERP database I work with doesn't use primary keys (or indexes for that matter) and it can take upwards of a dozen fields to establish record uniqueness. This makes any work with this database extremely difficult.

    Considering DECS in light of what I know now, I can see that with a better designed database this could certainly make a lot more sense than always coding LC LSX.

    Thanks for bringing this up, I had dismissed DECS and LEI long ago since it didn't fit my needs. I'm glad you made me reconsider it. Emoticon smile.gif