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:

0. 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.
1. 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.
2. 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
3. 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.
4. 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.