By default every job entry or step connects separately to a database. While this is typically great for performance, stability and predictability there are times when you want to manage database transactions yourself.
There are times when you only want to commit a changed database when a transformation ends without error or you want to perform a roll back when an error occurs somewhere. This is possible simply by enabling the "Make the transformation database transactional" option in the "Miscellaneous" tab of the transformation settings dialog.
Starting from version 5.0 of PDI (Enterprise Edition) you can also enable the same functionality that exists for a transformation in a job. The "Make the job database transactional" option is located in the "Settings" tab of the job settings dialog.
The rules are simple but effective for a any transformation or job which is made database transactional:
or put otherwise:
Log tables are not in any way affected by these options. If you want to learn more about configuring them for concurrent access, go to page: Configuring log tables for concurrent access
We expect database transactions to work across sub-transformations and sub-jobs in steps like Mapping, Single Threader and Job Executor. It will also work through sub-transformations and sub-jobs in job entries like Job or Transformation.
However, database transactions will not work in the following cases: