Support for Sorting Key Columns in the Index Order

In SAP Replication Server Data Assurance Option (SAP DA) version 15.7.1 SP202, the create compareset and alter compareset commands allow you to specify the order of compareset key columns in order by clauses using an ordinal integer number in addition to a Boolean value.

Valid values for setting compareset key columns are:

An integer value ranging from 1 to 2147483647 or 0 to indicate not a key column
True or false

Note: SAP DA interprets true as 1 or greater, and false as zero (0).

Each key column must be a unique ordinal number within in the compareset. When a compareset has multiple key columns, ordinal integer numbers are used to decide the order of the keys in the order by clause. The order by clause is included in the select statement, which SAP DA issues to the source and target data servers when selecting data to compare.

For example, when you compare an existing person table with these key columns:

create table person (
  first_name varchar(50),
  last_name  varchar(50),
  dob        datetime,
  primary key(last_name, first_name)
)

The primary keys places the last_name column ahead of the first_name column, and the data server is optimized for sorting and retrieving rows in this order. A compareset can be created to select the rows in the optimal order:

create compareset person
with source conn1 dbo person s
target conn2 dbo person t
map s.first_name = t.first_name set key = 2
and s.last_name = t.last_name set key = 1
and s.dob = t.dob
go

When SAP DA selects rows from the “person” table for a comparison, this SQL query is generated:

select first_name,last_name,dob from dbo.person order by last_name,first_name

When you run the create compareset command with the map all parameter, SAP DA sets the compareset key column ordinal numbers in the optimal order itself.

The pkey column is renamed to key and Y/N values are replaced with numbers in the show compareset command output:

show compareset person columns
go

 TYPE CONNECTION OWNER TABLE  MAPID COLUMN     KEY
 ---- ---------- ----- ------ ----- ---------- ----
 S    conn1      dbo   person 1     first_name 2
 T    conn2      dbo   person 1     first_name 2
 S    conn1      dbo   person 2     last_name  1
 T    conn2      dbo   person 2     last_name  1
 S    conn1      dbo   person 3     dob
 T    conn2      dbo   person 3     dob

See Compareset Commands in the SAP Replication Server Data Assurance Option Users Guide.