I'm relatively new to Data Migrator. We had IBI Professional Services come in and get our project started for us. Now, I need to finish it and maintain it. Here is what I am trying to do...

I have a table in DB2 that contains a unique list of terms. This table has a surrogate key (generated by database) that is the primary key. However, there are 4 other columns that make up the natural key.

I have a SQL Server table that is populated on a monthly basis and contains thousands of rows and it has data that matches the natural key from the DB2 table. After the SQL Server table is populated, I want to see if there are any new combinations in the SQL Server table that are not in the DB2 table and put them into the DB2 table.

I created a flow that uses the SQL Server table as the source. The SQL Select component grabs the 4 columns that make up the key and does a distinct on them. I then try to insert the rows into the DB2 table as the target. New rows are added and matching rows are ignored. However, since my surrogate key is the primary key, it tries to match on just that.

Again, I am new to this. So, I tried to edit the code and changed it so that it is matching on the 4 different columns instead of the primary key column. This worked, however, any changes to the synonym or flow changes the match back to the primary key.

So, I created a new synonym for my DB2 target table. I went into the Text View and moved my primary key field to the last field. I then went into the Access File Text View and changed the KEYS=1 to KEYS=4 and also moved the primary key field down again (not sure if that matters). When I use this new synonym as the target, this seems to be working.

Is this the correct way to do this? I don't want it to somehow change on me later. It looks like the Professional Services contractor that worked on this joined to the source synonym to the target synonym to get the surrogate key values. I would do that, but I am going across two different databases. When he did it, it was the same database and the joins could be done at the database level.

Any help would be greatly appreciated.

Thanks

That is also the way I would do it, change the synonym to reflect the natural key in stead of the surrogate key. The risk is however that is someone by accident (or ignorance) would generate the synonyms anew, it would fail again.

The other solution would like the contractor did to join to get the surrogate key, but you are right, this causes performance issues when the tables are on different databases, causing full tables to be transferred to Webfocus so that Webfocus does the join in stead of the database.

Another approach I have used is making a temporary DB2 table in the target database, fill it with the select distinct attributes of the source table (load replace), and then do the join just like the contractor did (which is now within 1 database, so DB2 can do it) to get the surrogate key. In this case you do not have to change synonyms. Even with thousands of rows this should be relatively fast.

Martin.

The simplest way to accomplish this would be to use the built-in support for Slowly Changing Dimensions in DataMigrator. In the synonym editor specify the SCD type for the key columns as Logical Key, leave the surrogate key column blank, and identify all the other columns as Type I. Then use load type Slowly Changing Dimensions.

Clif,

This sounds very promising, however, I don't really want the other columns to be Type I. This table that I am populating is basically a mapping table. The table contains the surrogate key column, logical key columns, a mapping column, create time stamp, create user, update time stamp and update user.

There is an application where a user can change the mapping for a logical key. If they do, the mapping column will change, the update time stamp will change and the update user will change (assuming it is someone else). A user can't add brand new mappings (i.e. new logical key combinations). That is done through the Data Migrator process. If we receive a new combination, we want to add a new row to the table. The surrogate key will automatically be populated by the database. The logical key columns will be populated on the new information we received from the SQL Server table. The mappings and other columns will be assigned default values. The user will then need to go enter a valid mapping in the mapping application.

So, as you can see, I don't really want to call all the other columns at Type I. In fact, I really don't want to do updates. I only want to insert new logical key combinations with default values for the other columns. Is there a way to do that with SCD.

Or, is there another way to specify a logical key and be able to use it when loading a table?

Thanks again for your response. And also thanks to Martin vK.

For columns that you never want to update you can identify them a "Permanent" by leaving the SCD type as blank. (DataMigrator distinguishes them from the Surrogate key column becasue they are not key columns).

In Release 7.7.05 there is a new type "Change Flag" that you can use to identify the update date and user.