DataPower 2 Merging
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".
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.