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 describes 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 (available on selected databases).

This KBA should be used if your database is not supported or there are issues with the automation and some manual steps are needed.
Solutions: There are two options, create a unique key or use On-The-Fly masking. 
1 Create Key Follow these steps to manually add a Unique Key for masking:
  1. Complete the following steps manually:
    1. For the best performance and execution, check and drop all existing indexes on the table.
    2. Create a temporary IDENTITY column (for example 'DLPX_TMP_ID')
    3. Add an Index to this column (the best option is Clustered Index).
  2. Refresh the Rule Set.
  3. Run the Masking Job.
  4. Complete the following steps manually:
    1. Drop the Index.
    2. Drop the temporary IDENTITY column.
    3. Recreate any dropped indexes (in step 1a). 


Important: The steps above cannot be added to the Pre/Post SQL Scripts. The reason for this is that 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:
Automated: The following databases have the automated Column Feature. This will create an Indexed IDENTITY column called MASKING_GENERATED_IDENTITY_TMP. If Custom SQL needs to be used you still need to use the steps described above. 
  • MS SQL Server
  • SAP ASE (Sybase)
  • PostgreSQL
  • MariaDB

Note: Oracle is not in the list as it uses ROWID.
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
Jun-21-2023 12.0.0.0
May 25, 2023 11.0.0.0
Apr 13, 2023 10.0.0.0
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.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, 6.0.17.0, 6.0.17.1, 6.0.17.2

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
 

In-Place Masking 

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

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 that is always unique, this column can be added as a Logical Key. Check and ensure that this column has an Index for optimal performance. 

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, improving performance and significantly reducing 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.

  • MS SQL Server
  • SAP ASE (Sybase)
  • PostgreSQL
  • MariaDB

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 successful completion of the masking job. 

There is one exception, Custom SQL (see below).

Custom SQL

The Custom SQL feature will not work when the IDENTITY Column Feature is used. Please follow the procedure in this KBA when Custom SQL is used.

A Feature is planned to automate this (IDEA-3104) in the future. 

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 index, it is recommended to consult the documentation for the database.

  1. Complete the following steps manually:
    1. For the best performance and execution, check and drop all existing indexes on the table.
    2. Create a temporary Identity column (for example 'DLPX_TMP_ID')
    3. Add an Index to this column (the best option is Clustered Index).
  2. Refresh the Rule Set.
  3. Run the Masking Job.
  4. Complete the following steps manually:
    1. Drop the Index.
    2. Drop the temporary IDENTITY column.
    3. Recreate any dropped indexes (in step 1.1). 

 

important

Important:

The steps above cannot be added to the Pre/Post 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