Rule Set Feature: Logical Key (KBA4451)

At a Glance

Description:	In-Place masking needs a Unique Row Identifier (here called Unique Key) and sometimes this needs to be manually defined. This is done using a Logical Key.
Versions:	Applicable Delphix Masking versions: 4.x, 5.x, 6.x.
Location:	UI: Rule Set > Edit Table Settings > Logical Key API: tableMetadata
Configuration:	The following parameter is used to configure this feature: Logical Key: Key or composite keys separated by a comma.
Oracle:	From 6.0.11.0 and above we automated the use of ROWID for Oracle. There is no need to set the Logical Key to ROWID. If Custom SQL is used it needs to contain the ROWID. For earlier versions, use ROWID.
UI Code:	The code in the Rule Set (UI): 'LK'.
More Info:	For information about investigating errors and the logs: KBA: How to Troubleshoot Masking Jobs and Access Masking Logs (KBA7988) More info: Docs: Managing Rule Set - Logical Key KBA: Using the Custom SQL Rule Set Feature (KBA4455)

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4
4.3	4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0
4.2	4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1
4.1	4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Unique Key

In-Place masking requires a Unique Key in order to update the masked row.

The masking job will define the Unique Key for Update according to:

Use Logical Key (LK) if specified.
If no LK, then use the Primary Key (PK) (or ROWID for Oracle).
If no PK, then use the IDENTITY (ID) column.
If no ID, then create MASKING_GENERATED_IDENTITY_TMP (for SQL Server, Sybase, and PostgreSQL).

Logical Key

At times it is required to manually set this Unique Key or to define a key other than the Primary Key. This can be if the Primary Key is Masked or there is no Primary Key but there is a set of columns where the values are unique for each row.

Another use case is if the database has a unique pseudo column that can be used as a key - for example Oracle's ROWID, DB2s ROWID, Postgres' CTID to name a few.

Special notes:

The Logical Key needs to be indexed or performance will suffer.
Oracle: Delphix has automated the use of this pseudo column on all In-Place jobs. No need to define ROWID as Logical Key.
DB2: Check with the DBA if ROWID is enabled on the table.
Heap tables: If there is a Unique Key, specify this.
Multiple columns: specify all columns needed, separated by a comma.

UI Overview

The examples below show the Rule Set page and the Edit Rule Set popup.

Rule Set page

Please note the 'LK' indicator when the table has a Logical Key defined.

Edit Table Settings popup

The example shows a single Unique Key (MY_ID). For multiple columns, separate each key with a comma.

Error Message

Version 6.0.11 introduced a validation of the columns added as the Logical Key to ensure they are valid. This was further improved in 6.0.16 which now also allows pseudo columns. This release also improved the error message.

Invalid column

If an invalid column (or columns) is added as a Logical Key, the following error is shown in the UI (popup):

Error message: "Invalid Logical Key. Specified column(s) don't exist in the table."

Older versions

No valid input errors

The Logical Key is not verified when entered. It is assumed that this key is valid and that it can be used in the Masking Job.

Tables missing from ruleset

This can happen if you have a new line/carriage return in the field. Newer versions strip out the new line/carriage return characters from the input to avoid this, but older versions are still susceptible. If encountered, this requires a support interaction to resolve.

Errors Message Logs

If an invalid column is added as a Logical Key, the following error is thrown in the logs:

6.0.16

YYYY-MM-DDTHH:MM:SS:sssZ ... ERROR c.dmsuite.dataPlatform.DataPlatform - Encountered exception while attempting to execute the following query: 'SELECT 1 FROM "DBO"."TBL" WHERE "XYZ" IS NULL'
5535:java.sql.SQLSyntaxErrorException: ORA-00904: "XYZ": invalid identifier

6.0.11

YYYY-MM-DDTHH:MM:SS:sssZ ... ERROR c.dmsuite.manager.CollectionManager - Error while updating logical key
com.dmsuite.exception.BadRequestException: Table: 'TBL' column(s): 'XYZ' contains one or more columns that don't exist in the table. Specify column(s) that exist in the table: [...]
        at com.dmsuite.manager.CollectionManager.validateLogicalKeyColumnsForTable(CollectionManager.java:2772)