Skip to main content
Delphix

Masking a table without a unique Key (KBA10125)

  1. Last updated
  2. Save as PDF
 

 

KBA

KBA# 10125

At a Glance

Summary: This page provides guidance on masking a table without a Unique Key. A key signature of these tables is that there are:
  • No Primary Key (PK) 
  • No IDENTITY column
Applies to:  The Continuous Compliance Engine (formerly Masking) has automated steps for masking these tables (feature "Identity Column Support"). This will create an Indexed IDENTITY column called MASKING_GENERATED_IDENTITY_TMP

This KBA should be used if your database is not supported, or there are issues with the automation and some manual steps are needed.

For the supported database please see:
IDEA: A feature request to set the Clustered Unique Index from the Continuous Compliance Engine exists as IDEA-2840.

If affected, please request to be added through support, services, or the CSM.
Solutions:

There are two options:

  • Create a unique key
  • Use On-The-Fly masking. 
1 Create Key Follow these steps to add a Unique Key manually:
  1. Complete the following steps manually:
    1. Create a temporary IDENTITY column (for example 'DLPX_TMP_ID')
    2. Add a Clustered (Unique) Index to this column.
  2. On the Masking Engine 
    1. Refresh the Rule Set.
    2. Configure the job to Drop/Disable Constraints/Indexes. 
    3. Run the Masking Job.
  3. Complete the following steps manually:
    1. Drop the Index.
    2. Drop the temporary IDENTITY column.


Important: Step 1 above cannot be added to the Pre SQL Scripts because the added key needs to be in the Rule Set before the job starts.

Note: If Custom SQL is used, the SELECT needs all masked columns including  'DLPX_TMP_ID'. 
2 OTF Masking Another way to mask a table without a Unique Key is to use On-The-Fly (OTF) masking:
More info: More useful info: 

Databases and supported features:

Casting using SQL:

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Date Release
Aug 21, 2024 26.0.0.0
July 29, 2024 25.0.0.0
Jun 20, 2024 24.0.0.0
May 22, 2024 23.0.0.0
Apr 17, 2024 | May 8, 2024 22.0.0.0 | 22.0.0.1
Mar 20, 2024 | Apr 2, 2024 21.0.0.0 | 21.0.0.1
Feb 21, 2024 20.0.0.0
Jan 25, 2024 19.0.0.0
Dec 20, 2023 | Jan 10, 2024 18.0.0.0 | 18.0.0.1
Nov 21, 2023 17.0.0.0
Oct 18, 2023 16.0.0.0
Sep 21, 2023 15.0.0.0
Aug 24, 2023 14.0.0.0
Jul 24, 2023 13.0.0.0
Jun 21, 2023 12.0.0.0
May 25, 2023 11.0.0.0
Apr 13, 2023 10.0.0.0 | 10.0.0.1
Mar 13, 2023 | Mar 20, 2023 9.0.0.0 | 9.0.0.1
Feb 13, 2023 8.0.0.0
Jan 12, 2023 7.0.0.0
Releases Prior to 2023
Major Release All Sub Releases
6.0

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, 6.0.17.0, 6.0.17.1, 6.0.17.2

In-Place Masking 

In-Place (IP) masking will require a column with a 100% Unique Key (unmasked) to identify the correct row when updating a masked value.

If there is no key on the table (or the key is masked), a temporary unique column needs to be added.

Identity Column

Adding an Identity column is the best and easiest way to add a Unique Key. This is a column that is automatically generated with a unique value for each record.

Logical Key

If there is a column (or set of columns) that is always 100% unique and has no NULLs, this column can be added as a Logical Key. Check and ensure that this column has an Index for optimal performance. 

Clustered and Unique Index 

For performance, remember to index the Identity column. The best option is to add a Clustered Index. With a Clustered Index, most databases will arrange each row in a logical order and the Clustered Index is 100% unique.

A Clustered Index will also improve performance and significantly reduce the risk of hangs and deadlocks. 

IDENTITY Column Feature 

The Continuous Compliance Engine (formerly Masking) has a built-in feature to automate the creation of an Indexed IDENTITY column on the following databases.

This feature will automate the steps in this KBA and automatically create a column called MASKING_GENERATED_IDENTITY_TMP before masking. This column will be removed after the completion of the masking job. 

Note: Canceled or Terminated Jobs - If the job is canceled or terminated for some reason, the IDENTITY column will not be removed. Next time the job starts, the masking engine will reuse the existing column and then remove it.

Custom SQL

On older versions (before 16), the Custom SQL feature will not work when the IDENTITY Column Feature is used. Consider upgrading or use this procedure if Custom SQL is required. 

How to

There are two ways to mask a table without a key:

  1. Add a unique key
  2. Use On-The-Fly masking

Adding a unique key

These are the steps to add a unique key. For specific steps on adding a column and creating the unique index, it is recommended to consult the documentation for the database.

  1. Complete the following steps manually:
    1. Create a temporary IDENTITY column (for example 'DLPX_TMP_ID')
    2. Add a Clustered (Unique) Index to this column.
  2. On the Masking Engine 
    1. Refresh the Rule Set.
    2. Configure the job to Drop/Disable Constraints/Indexes. 
    3. Run the Masking Job.
  3. Complete the following steps manually:
    1. Drop the Index.
    2. Drop the temporary IDENTITY column.
important

Important:

Step 1 above cannot be added to the Pre SQL Scripts. The reason for this is that the key needs to be in the Rule Set before the job starts.

 

Note

Note:

If Custom SQL is used, the SELECT needs all masked columns including  'DLPX_TMP_ID'.

 

On-The-Fly masking

Another way to mask a table without a Unique Key is to use On-The-Fly (OTF) masking:

Assistance

For assistance with scripting this, please contact your Customer Success Manager to engage Services or a Partner. 

 

 

 

 

Privacy Policy Terms of Use Legal About
© 2024 Delphix Corp