I designed a jobs, transformations combination, that takes a list of ids that need to be deleted from other tables, for each id at the same time. The transformation that does eventually do the deletion has 14 delete steps and two update steps. Because this is too slow for 2 million ids, I start the delete transformation with a package of 500 ids and the transformation gets run in parallel, 10 times. The amount of deletions a single started transformation does is well within the limits of the rollback log. If I track the number of deleted rows, I can see, that all rows got deleted and no errors were thrown. After the top job is finished I can see that the number of deleted rows dwindles constantly to about the original number minus 200.000 rows. So, I seem to be running into a database problem, maybe just DB2, that seems like the rollback log gets filled to the brim and almost everything gets rolled back. It seems that it does not matter whether I check "Make the transformation database transactional" or not and I would prefer to check it.
Do I run into a bug or is there a logical flaw?
We checked with the customer's database admin and we do not run into a rollback caused by a full transaction log and I do not see any problems in the Kettle log. We will rebuild the transformation from scratch in case a step is broken and try a newer database driver and see if that helps.