Focal Point
[CLOSED] DM flows with autogenerated identity columns

This topic can be found at:

February 02, 2018, 06:21 AM
Martin vK
[CLOSED] DM flows with autogenerated identity columns
I am sometimes struggling with RDBMS tables (DB2 in my case) which have an autogenerated identity as primary key.

In the masterfile I set those to FIELDTYPE=R, so when I have DM flows with inserts I do not have to assign a value in the Target transformations. That works fine.

The problem is however then with updates and deletes. I would like to do key matching on the ID (same table is source table as well), but the FIELDTYPE=R is then preventing me to map the ID of source table to ID of target table. I can get around by removing the FILEDTYPE=R from master file, then map and save flow, and put back the FIELDTYPE=R. But it dos not feel right.
What would be another/better way to deal with this?


This message has been edited. Last edited by: FP Mod Chuck,

WebFocus 7.7.06M/8.2.03M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
February 02, 2018, 11:30 AM
DataMigrator always matches on the key field(s) as identified in the synonym. Key fields cannot be be updated and for inserts there's no need to map to the ID field when it's automatically assigned by the database and the field is identified as AUTOINCREMENT in the synonym.

You should be able to leave the ID field as read only. If not please open an IRL case and provide the flow and synonym.

BTW in Release 7.7.07 from last May we added the functionality for different expressions on insert or update.