DataPower 2 Merging

Question:

I am trying to update some records by importing a CSV file using a clustering key but I can't get it to work: DataPower reports the error: To use "Match sort key", you need to set the clustering key of the destination table".

Answer:

Basically you need to make sure you're importing into a layout that's attached to a base table, rather than a query, and then you need to set the clustering key (ie. the underlying sort order) of the base table records.

To check that the layout is attached to a base table, edit the layout and look at the Query/Table=>Attach to menu - one of the tables at the top should be ticked, rather than one of the queries at the bottom (probably called "Join for <layoutname>").

If your layout uses a query, make a copy of the layout using the Layout / New Layout menu command, and choose the relevant table from the Query/Table / Attach to menu.

Once the layout's attached to a base table, you can set the clustering key by using the Query/Table / Edit / Clustering key option, and set the sort order to the field(s) that identify each record (hopefully the same as the primary key, if your database is properly structured!).

Now return to browse mode and import the CSV file - you should see that the field(s) you specified in the clustering key are shown in a darker grey, and if you select the "Match sort key" option and make sure the correct fields from the CSV file are mapped onto the clustering key fields, it should all work as expected.

Note that it would really make much more sense if DataPower used the primary key of the destination table to perform this type of merge operation, but as it is the clustering key is used because this code basically hasn't changed since DataPower 1.

Note also that it may be advisable to clear the clustering key after performing the merge, especially if you have a number of secondary indexes (ie. sorts) referring to the table in question, as each one of those needs to reference the clustering key of the table, and it's usually best if this is just left as the ID field, ie. the entry order of the records.

To clear the clustering key, edit the layout again, then choose Query/Table=>Edit=>Clustering key from the menu, click on "Clear", then click on OK and OK again to return to browse mode.