Skip to main content
Delphix

Masking Heap Table (i.e. table without PK) (KBA9742)

  1. Last updated
  2. Save as PDF
 

 

KBA

KBA# 9989

At a Glance

Summary This page describes masking a Heap table using In-Place (IP) masking. The page also describes how to mask when there are:
  • No Logical Key (LK) 
  • No Primary Key (PK)
  • No IDENTITY column
A Heap Table is technically a table without a Clustered Index. The Continuous Compliance Engine (aka Masking) has automated masking of these scenarios, but there are some edge cases where manual steps are needed. 

The In-Place masking requires a Key and the values in this key have to be Unique and there can't be any NULL values.
Applies to  This applies to all In-Place masking jobs where the automation of the IDENTITY Column Feature failed or is not available. 
Custom SQL Due to incompatibility between the Identity Column Feature and Custom SQL, the following databases need to use these steps when using Custom SQL: 
  • MS SQL Server
  • SAP ASE (Sybase)
  • PostgreSQL
  • MariaDB
A feature is planned to automate this in the future (IDEA-3104). 
Oracle This KBA does not apply to Oracle. 
Steps The steps are:
  1. Manually:
    1. Create a temporary IDENTITY column (for example 'DLPX_TMP_ID')
    2. Add an Index to this column (the best option is Clustered Index).
  2. Refresh the Rule Set.
  3. If necessary, select Add Custom SQL and add all masked columns including the above created temporary IDENTITY column.
  4. If necessary, assign Masking Algorithms.
  5. Run the Masking Job.
  6. Manually:
    1. Drop the temporary IDENTITY column (starting with its Index).
Alternative An alternative is to use On-The-Fly (OTF) masking job.
More info More info on how to access logs: 

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
Mar 13, 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

Heap Table

A Heap Table is a table without a Clustered Index (a unique key). If the table does not have a unique key it is not possible to use In-Place Masking. If In-Place masking is needed a temporary unique column needs to be added.

IDENTITY Column

The best way to add a Unique Key is to add an Identity column. This is a column that will automatically generate a unique value per record in the table.

Clustered Index 

For best performance and to avoid blocks or deadlocks - the best option is Clustered Index. 

IDENTITY Column Feature 

The Continuous Compliance Engine (aka 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 on Heap Tables.

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

Procedure

The steps are:

  1. Manually:
    1. Create a temporary IDENTITY column (i.e. 'DLPX_TMP_ID')
    2. Add an Index to this column (the best option is Clustered Index).
  2. Refresh the Rule Set.
  3. If necessary, click Add Custom SQL and add all masked columns including the above created temporary IDENTITY column.
  4. If necessary, assign Masking Algorithms.
  5. Run the Masking Job.
  6. Manually:
    1. Drop the temporary IDENTITY column (starting with its Index).

 

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