Masking Heap Tables (KBA9742)

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 is: No Logical Key (LK) (should be indexed) No Primary Key (PK) (with Clustered index) No IDENTITY column Heap Table with Custom SQL 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.
Applies to:	This applies to all In-Place masking jobs where the automation of the IDENTITY feature failed, is not available, or if there is a database block (will result in a hang) with SELECT COUNT (counting records).
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-2840).
Oracle:	This KBA does not apply to Oracle.
Steps:	The steps are: Manually: For the best performance and execution, check and drop all existing indexes on the table. Create a temporary IDENTITY column (for example 'DLPX_TMP_ID') Add an Index to this column (the best option is Clustered Index). Refresh the Rule Set. [if needed] Add Custom SQL and add all masked columns including the above created temporary IDENTITY column. [if needed] Assign Masking Algorithms. Run the Masking Job. Manually: Drop the temporary IDENTITY column (starting with its Index). Recreate any dropped indexes (in step 1.1).
More info:	More info on how to access logs: KBA: How to Troubleshoot Masking Jobs and Access Masking Logs (KBA7988) Databases and supported features: DOCS: Data Source Support Casting using SQL: KBA: Algorithm: Casting Values Before Masking (KBA1580) 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, 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). Since 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.

Index

For best performance, remember to Index the Identity column.

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:

For the best performance and execution, check and drop all existing indexes on the table.
Create a temporary IDENTITY column and add a Clustered Index to this column.
Refresh the Rule Set.
[if needed] Add Custom SQL and add all masked columns including the temporary IDENTITY column.
[if needed] Assign Masking Algorithms.
Run the Masking Job.
Drop the temporary IDENTITY column (starting with its Index).
Recreate any dropped indexes (in step 1).

Assistance

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