HCL Domino · SQL Integration · Domino API-Engine
Incremental Data Synchronization Between HCL Domino and SQL — Beyond INSERT and UPDATE
HCL Domino · SQL integration · Change detection · Reliable recovery
Transferring data between an HCL Domino NSF database and an SQL database is relatively straightforward. Keeping two different data models aligned over months or years is another matter. Changes must be detected and propagated in the required direction, deleted or withdrawn records must be handled correctly, and interrupted runs must be recoverable without introducing duplicate or inconsistent data.
The Domino API-Engine can implement both one-way and bidirectional synchronization workflows between Domino and SQL. To make the underlying mechanisms easier to follow, this article uses a Domino-to-SQL scenario as its main example, then examines what changes when both systems can update data. The focus is on integration design, not on suggesting that synchronization becomes automatically safe through a single function.
Integration architecture
1. An export is not a synchronization process
A one-time export reads a collection of documents and writes their values into another system. A continuously running synchronization process must do considerably more: determine what has changed since the previous successful run, identify records that should no longer exist in the target, preserve relationships, and recover from failures.
Consider a deliberately simplified example: a Domino application stores product documents and a website uses SQL tables to display a selected catalog. The source document contains a product code, descriptive fields, prices, availability status and possibly image attachments. The SQL model might use a main catalog_item table, a catalog_price table and separate image records.
For this first example, the Domino application is the authoritative source and the SQL database receives its changes. This keeps the diagram readable; it does not restrict what the API-Engine can do. A reverse SQL-to-Domino flow can also be implemented, and both directions can form part of the same integration design when data ownership and conflict rules are defined.
Illustrated direction: Domino → SQL. The API-Engine can also process SQL → Domino changes; bidirectional synchronization adds a corresponding reverse path and explicit conflict-handling rules.
Incremental change detection
2. Detecting new and modified documents
The first task is selecting the documents that may need processing. An incremental scan can use a saved synchronization timestamp and a Notes-compatible selection formula instead of reading and mapping every document on every run.
In the API-Engine, @ScanDB provides this part of the workflow. The following fragment is illustrative, using the same function-call pattern as existing implementations; it is not a complete, executable script:
ChangedIDs := @ScanDB(SourceDB; SelectionFormula; LastSyncTime; 1);
The selection formula determines which documents are relevant to the integration. The timestamp restricts the scan to the intended change window. Together, they can reduce unnecessary processing substantially when only a small proportion of the source documents changes between runs.
But the timestamp is not simply “the time the script finished.” A robust design establishes a defined upper boundary for a run, processes the corresponding changes, and advances its checkpoint only after the required operations have succeeded. Otherwise, modifications occurring during execution can fall into a gap between two runs. Timestamp precision, inclusive or exclusive comparisons and concurrent modifications must be considered; an overlap window with repeatable updates is one practical safeguard.
Deletion and visibility
3. Deletion, unpublishing and changing selection rules
A common source of synchronization errors is treating every disappearance as a database deletion. There are actually several different situations:
| Source condition | Meaning for the target |
|---|---|
| A document was physically deleted | Remove or otherwise retire its corresponding target records. |
| A document still exists but is no longer eligible | Remove it from the published selection, or mark it inactive. |
| A document remains eligible and was modified | Update its mapped values and dependent records. |
For physical deletions, the API-Engine can use @ScanDBDeleted to obtain deletion information since the previous checkpoint:
DeletedUIDs := @ScanDBDeleted(SourceDB; 1; 2; LastSyncTime);
The stable Domino document identifier provides a way to find the corresponding SQL data even when the original document can no longer be opened.
Logical removal is different. A product may remain in Domino but change from “published” to “internal,” or it may cease to satisfy a selection formula. If the change scan only returns documents that currently satisfy that formula, the record that has just become ineligible can be missed. Depending on the integration, a broader status-change scan, an explicit publication-state check or periodic reconciliation against the current eligible set is required.
There is a further operational boundary: Domino’s deletion information is not retained indefinitely. Deletion stubs are subject to the database’s purge settings. If a synchronization service is offline longer than the period for which relevant deletion records remain available, it may no longer be possible to discover every historical deletion incrementally. A full reconciliation is then the safer recovery path. See the HCL Domino documentation on deletion stubs and purge intervals.
Relational mapping
4. Mapping NSF documents to relational structures
Domino documents and relational tables organize information differently. A single document may contain multi-value fields, language-dependent descriptions, nested logical structures or attachments. The SQL destination may represent these as a main row plus several related rows.
For example, one Domino product document could map to:
| SQL table | Purpose |
|---|---|
catalog_item |
Identity, product code, title and availability |
catalog_price |
One or more price or region-specific rows |
catalog_text |
Language-specific descriptions |
catalog_image |
References to images and stored variants |
The integration therefore needs a durable relationship between the source UNID and the destination records. A database-generated SQL ID may be useful internally, but the source UNID or another stable business key should make repeated runs identify the same logical record, rather than insert a duplicate.
Prepared statements are particularly useful for repeated insert and update operations. The API-Engine provides functions such as @MySQLCreatePreparedStatement and @MySQLExecutePreparedStatement, allowing parameters — including binary buffers where supported — to be supplied separately from the SQL command text. This supports typed values and avoids rebuilding a complete SQL statement for every row.
The order of writes matters when dependent tables are involved. Replacing child rows, updating the parent and deleting obsolete relations should be designed as one consistent unit wherever the destination database supports transactions. The API-Engine offers the building blocks for these operations; the script must still implement the required consistency rules.
Attachments and binary data
5. Attachments and binary data are part of the data model
In many applications, synchronization extends beyond text and number fields. A Domino document may contain attached product images or other binary data, while the destination expects several image sizes and associated metadata.
One established API-Engine pattern reads an attachment into an in-memory buffer, uses its image-processing functions to generate the required versions, and transfers the resulting binary data to SQL using prepared statements. Metadata such as the filename, original size and modification time can help decide whether the image needs reprocessing. A change in image order may require only an update to a SQL metadata field, not a new image conversion.
This is an example of a more general rule: synchronize the relevant business object, not just its visible document fields. The object may span multiple tables, attachments and derived data. Binary processing is optional for the core SQL synchronization pattern, but it illustrates why real-world integrations often become more involved than a row-by-row export.
Checkpoint management
6. Failure handling, checkpoints and safe restarts
Suppose a job has already transferred 500 documents when the SQL connection fails. Some destination rows may have been committed and others not. Simply rerunning the entire batch can produce duplicates or inconsistent relations unless the operations were designed to tolerate repetition.
A practical synchronization workflow therefore needs several safeguards:
- Repeatable operations: Reprocessing a document should update its existing destination record, not create another copy. Stable keys and appropriate SQL constraints help.
- Explicit error tracking: Connection failures, failed statements and incomplete dependent writes must be visible to the job, not merely written to a text log.
- Controlled checkpoints: The saved
LastSyncTimeadvances only when the chosen processing window has completed successfully under the workflow’s error policy. - Transactional boundaries: Where possible, related SQL changes should commit together. A SQL transaction does not automatically make the Domino side part of the same distributed transaction.
- Reconciliation after exceptional gaps: A periodic full comparison or repair mode is valuable when change history is incomplete or synchronization has been offline for an extended period.
Recording an error and retaining the previous checkpoint allows a later run to revisit the same change window. It does not, on its own, undo earlier SQL writes. That is why idempotent updates, uniqueness constraints and appropriately scoped transactions are essential companions to checkpoint management.
For long-running jobs, it is also useful to record counts of selected, inserted, updated, removed, skipped and failed records. These numbers make unexpected behavior easier to identify and help operators distinguish “nothing changed” from “the job did not process anything.”
Operations and administration
7. From a script to an operational integration process
A synchronization script is only one part of a production workflow. In the Domino API-Engine environment, several separate Domino databases contribute to operating and maintaining the process:
| Component | Role |
|---|---|
| APICTRL | Defines scripts, batches and execution events, including scheduled and other trigger-based jobs. |
| TOOLBOX | Stores script parameters; the same script can be assigned different parameter sets for different servers, customers or scenarios. |
| APILOG | Captures Engine output in a separate log document for each task execution. |
| APIJOB | Provides the standard Mail-In entry point for mail-triggered jobs when required. |
This separation is useful in practice. A synchronization script can retain the same processing logic while a parameter set selects another NSF path, another target environment or different document-selection formulas. Execution frequency and error behavior can be administered independently of the mapping code, and each run leaves a traceable operational record.
The architecture does not remove the need for testing, access control, backups, monitoring or careful failure handling. It does, however, make it possible to organize an integration as a repeatable, configurable server-side job rather than an isolated one-off script.
Bidirectional integration
8. Extending the pattern to bidirectional synchronization
Nothing in the API-Engine restricts an integration to transferring data from Domino to SQL. The same environment can read and update both systems, so changes can also flow from SQL back into Domino. A bidirectional workflow may use a shared set of business keys or a dedicated mapping of Domino UNIDs to SQL identifiers.
The additional complexity is mainly in the synchronization rules, rather than in the basic ability to read and write data. A two-way implementation typically needs:
- Change detection on both sides: Domino changes can be discovered using Engine functions such as
@ScanDB. On the SQL side, suitable timestamps, change tables or another database-specific change-tracking mechanism are required. - Defined data ownership: Decide whether Domino or SQL owns a record, individual fields, or particular business processes. Not every field has to be writable in both directions.
- Conflict handling: Establish what happens when the same business data changes in both systems before synchronization has completed. Rules may be based on field ownership, versions or explicit review; timestamps alone are not always sufficient.
- Feedback-loop prevention: A change written by the integration must not repeatedly return as a new change from the other system. Source markers, comparison logic or similar safeguards may be needed.
- Recovery in both directions: Track progress and failures separately for each direction, and ensure retries do not duplicate or unintentionally overwrite data.
In practice, bidirectional does not necessarily mean that every field is freely editable everywhere. A controlled division of responsibilities between Domino and SQL is often easier to operate and audit than unrestricted concurrent updates.
These aspects require a more extensive script and application-specific decisions. They are nevertheless a natural extension of the same Engine-based integration architecture illustrated above.
Scope and limitations
9. Where this approach fits — and where it does not
Incremental synchronization between Domino and SQL is useful when applications need to share data despite different storage models. One-way data flows suit websites, reporting systems and other downstream consumers; bidirectional flows are relevant when both Domino and an external application must contribute updates to a shared business process. Either approach can support gradual modernization without forcing an immediate replacement of a working Domino application.
Cross-system synchronization should not be confused with native Domino replication. Standard replication is designed to synchronize Domino replicas; Domino/SQL integration connects different data models and usually requires application-specific transformation and consistency rules. The API-Engine provides the required access and processing capabilities, while each implementation defines its own synchronization directions, conflict policies and operational safeguards.
Other tools, including LotusScript, Java and integration platforms such as HCL Enterprise Integrator, can also implement parts or all of these patterns. The API-Engine’s distinguishing approach is to combine Domino-specific scanning and processing with SQL operations, transformation, scheduling, parameterization and logging in one Engine environment. The appropriate choice depends on the existing infrastructure and the needs of the project.
Conclusion
Conclusion: synchronization is about controlled state changes
Reliable synchronization is not primarily a matter of writing INSERT, UPDATE and DELETE statements. It is about detecting the correct changes, understanding why a document has disappeared from a target selection, preserving relationships across different data models and making interrupted runs recoverable.
The Domino API-Engine provides practical functions and an operational framework for one-way as well as bidirectional integration workflows. The example in this article intentionally follows one direction to keep change detection, deletions and recovery understandable. The precise data-ownership model, conflict policy, error handling, SQL schema and recovery strategy remain design decisions for each integration.
Continue exploring the API-Engine
The production examples discussed here are intentionally not published because they contain project-specific structures and business logic.