...

It can be surprisingly tricky to use a database to generate a unique ID.  The problem is mostly caused by inadequate or incorrect locking by the various databases.  To make matters worse, every database behaves different and even within the same database you can use different database engines (like MySQL) that behave differently when it comes to locking tables.

Note: We are working on an easier solution for future releases, see PDI-5501

The problem

All that Kettle wants to do is generate a unique integer ID at the very start of the transformation or job.  That "batch" ID is then being used in various places throughout the life of the transformation or job.   Unfortunately, generating a unique ID is not something that is handled in any sort of standard fashion by the various database vendors.  Some database don't offer any support for it, some offer "Sequences" to do it and others have special identity or auto-increment columns that get automatically assigned a value upon insertion of a record.

...