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

Domino and RDBMS

The question "What is the best way to integrate Domino with an RDBMS" surfaces quite regularity. With the impending demise of NSFDB2 these are your options:
  1. Domino Enterprise Connectivity Service (DECS - part of Domino)
  2. Lotus Enterprise Integrator (LEI - separate product)
  3. Custom code using LCLSX
  4. Custom code using Java in XPages

So the interesting question is: when to use what?

To get the best performance you need to revisit architecture rather than find solutions for a specific coding problem. When it comes to RDBMS code we find that a lot of developers like to create their own code not taking advantage of optimised code (caching, pooling etc.) provided by the platform. You need to clearly be aware why your code *MUST* talk to a RDBMS. These are the guidelines:
  1. Don't use an RDBMS (1)
    Domino's multi-value field capability can model master-detail records without the need for a relation. the NotesSQL driver makes Notes databases available for report generators that require tables as input. Webservice and XML capabilities can provide many of the alternate use cases. I can have a look at specs to make suggestions how to implement them in standard Notes. We have databases in production with more than a million records and many GB in size. Some performance tuning works wonders:
    • Does my application really need a RDBMS in the background? If you are looking for performance that is probably a no, if you need to integrate into existing systems that is probably a yes. also if you look at transactional applications that would be a yes
    • Does my application need real-time lookup into an RDBMS? In a lot of cases that is a NO. You might need to lookup person information in the RDBMS. In this case you better use that information and TDI (provided with R8) to synchronize the RDBMS with the Domino Directory (there are enough fields for most information *and* there is a customization API build into the directory) and use @NameLookup which beats any RDBMS connection (for the caching have a look here: http://www.ibm.com/developerworks/lotus/library/ls-D6_Dir/ -- and that was just R6). For other databases DECS/LEI is a good option. So instead of RDBMS code you use normal lookups in Notes. A neat trick here: store the UNID of the new document into the person's record and instead a @DBLookup or getDocumentByKey you can use @NameLookup/@GetDocfield @NameLookup / getDocumentbyUNID for high performance
    • Consider well: would a "on document change" agent be sufficient? Is the result of the transaction needed for the user directly? If not: Use a Java Threat for the connectivity so a transaction can continue without the user waiting
    Don't use an RDBMS (2)
    With XPages (introduced in Domino 8.5) an application now can use data from more than one document or database in a single form easily.
  2. Use DECS
    Does my application CODE need real-time access into a RDBMS? Domino provides DECS out of the box and LEI for a fee. DECS/LEI provide a robust fast way to access RDBMS data without a developer needing to write (and maintain) ODBC/JDBC/LCLSX code. It also takes advantage of connection pooling. in DECS Domino forms are configured to map to relational tables including mapping multi-value fields to master-client tables. Entries exist in Domino and the RDBMS. Any RDBMS would work: DB/2 MySQL, Oracle and other vendors. DECS is a sufficient solution if creation/deletion of records happens through the Domino front-end (date changes, short of the primary key) can happen anywhere. There are thoughts about advanced uses of DECS.
  3. Use LEI
    Similar to DECS but with more options: records can be created/deleted from Domino and/or the backend. Data can be stored in the RDBMS only (Virtual views/documents). Data operations can be scheduled (great for reporting). I like the virtual views and probably will write about them soon(er or later).
    Domino doesn't do transaction(s) handling in an RDBMS. If you need that functionality you need to ensure that on the RDBMS side. A best practice for write access: write documents back into a "command database" that is linked to the RDBMS using DECS/LEI. The table it is linked to would be a auxiliary table with a "on create" trigger. That trigger does on the RDBMS site what it needs to do (and the developer doesn't need to understand anything about Domino. They only need to understand the RDBMS). The trigger would update the "command record" so on the Domino side you can process the result
  4. Custom code using LCLSX
    Developers love this, but I haven't seen a good use case for that. Typically this is used for small transactions where the configurability of DECS/LEI is not appreciated. One case: an existing RDBMS with a lot of triggers/stored procedures can't be amended to incorporate a neat connection to Domino (the best solution: let DECS write in a temp table and have the onInsert trigger in the RDBMS pick the value and call the stored procedure. It separates your Lotus(Script) code from SQL 100%. This way you don't need to mix code and SQL, makes maintenance muuuch easier, saver and cheaper). If you have to code: use LCLSX. Do not use ODBC direcly (especially SQLServer sucks on ODBC, it is build for OLEDB which LCLSX is using for it). Encapsulate into classes.
  5. Custom code using XPages/Java
    You can use any Java class in XPages to connect where ever you want to connect. Suitable approach when you have *huge* RDBMS databases and need to display/render small portions of it at a time (e.g. P/O database with a Domino based approval system). Maintain the connection in one of the contexts (application / session) and use a pool manager (but double-check: is DECS/LEI/TDI a better option?)
As usual YMMV.

Posted by on 07 September 2009 | Comments (4) | categories: Show-N-Tell Thursday


  1. posted by Stephan H. Wissel on Tuesday 08 September 2009 AD:
    @Michelle: thx for stopping by. DECS works very well for import, you just need to use it a little creative. Follow the link for "advanced DECS" usage. E.g. if you know the keys through some different measures you can use a DECS task that doesn't monitor creation. Create empty documents with the key and just refresh them. This way your code never needs to be changed if you want to add just another field from the database, just add them to the form and the DECS config. Or hack the code that DECS uses to import keys - you can use that to populate keys without much change - and others have debugged that in great detail.
    Of course having LEI makes things easier. We ran a number of post-project analysis and found that LEI in almost all cases would have lowered time-to-completion, risk and cost. There seem to be that stubborn prejudice out there, that coding yourself is cheaper than buy a suitable tool.
    Emoticon biggrin.gif stw
  2. posted by Patrick Kwinten on Tuesday 08 September 2009 AD:
    option: Mayflower Sentinel Data Integrator
  3. posted by David Leedy on Tuesday 08 September 2009 AD:
    I've never gotten to far with DECS, probably because it doesn't fit for us. We use Axceler's Notrix product to pump thousands of records from an iSeries into Notes each day. It's expensive but a great product. I have to pump data and can't do any live lookups because our heaviest users are disconnected and just replicate the data.

    I use ODBC calls all the time (Uselsx "*lsxodbc") to call stored procedures on the iSeries to either get or manipluate data from Notes. I do this on a website for reporting as well in various maintanence screens. Years ago I modified all my calls so it's done via agents that run on a server so I wouldn't need the Desktop PC's to have ODBC settings.

    These methods have worked well for us for over 10 years. If there's better or alternative techniques then I'm just not aware of them.

  4. posted by Michelle O'Rorke on Tuesday 08 September 2009 AD:
    The reason LCLSX is preferred by developers is not that DECS is not appreciated, but that it doesn't work as required. The most common use-case I encounter for coding using LCLSX is importing data for lookups - information such as contacts, part tables, etc. The lookups do not normally need to be real time (just a once a day import). The information is always created in the RDBMS so DECS doesn't work. LEI costs money so doesn't get approved.

    I think the new TDI entitlement may make some of these use-cases redundant in future but I haven't had time to explore that yet.

    I personally have never encountered a real-life use for DECS. Most RDBMS admins won't allow data to be sent from Notes / Domino - they don't trust it (or us as Notes developers?). If the data is created in Notes is stays in Notes. The best the Notes team can usually get is read access to the back end. As DECS requires the keys to already exist in Notes, it is useless for importing reference data / lists / tables that are maintained elsewhere.