Fast Data Masker will not execute in certain Oracle DB situations

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

When attempting to mask using Format Encrypt function against Oracle databases, there are scenarios where the masking job will not successfully execute. The job will begin, but will hang before any DML is actually performed.

From what we've found, this seems to be an issue with FDM creating different queries, depending upon the existence of unique indexes on a table.

---------------------------------------------------------------------------------------

Scenario #1 (FDM did not execute): Attempted to run FDM on an existing table. Table had a unique index. FDM session hung with index and would not execute. Dropped the unique index after the FDM session was hanging, enabled the primary key on PK column and attempted to execute FDM job again. This session did not execute anything and just waited to be finally killed by DBA. This is the query created by FDM: update "OWNER"."TABLE" SET "MASK_COLUMN" = :1 WHERE ((:2 IS NULL AND "PK_COLUMN" IS NULL) OR "PK_COLUMN" = :3 )

---------------------------------------------------------------------------------------

Scenario #2 (FDM DID execute): Create a new table based upon the one in scenario #1, using CTAS. Created the same PK on the same column as in scenario 1. This time, FDM created a different query and successfully executed, masking 5 million records in 11 minutes. This is the query created by FDM for this second scenario (note the lack of OR statement or need for a 3rd variable): update "OWNER"."TABLE" SET "MASK_COLUMN" = :1 WHERE "PK_COLUMN" = :2

---------------------------------------------------------------------------------------

Environment

Release : 4.7

Component : CA Test Data Manager - Fast Data Masker version: 4.7.3215.0

Db Type : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

Resolution

The complex query was introduced in the code by the presence of nullable unique index.

Having said that, in later FDM versions we have disabled this behavior by forcing the use of a simple update query

(update "OWNER"."TABLE" SET "MASK_COLUMN" = :1 WHERE "PK_COLUMN" = :2)

A complex query, such as (update "OWNER"."TABLE" SET "MASK_COLUMN" = :1 WHERE ((:2 IS NULL AND "PK_COLUMN" IS NULL) OR "PK_COLUMN" = :3 )) can be used by enabling the Options setting UPDATENULLINDEXCOLUMN=Y.

Additional Information

For more information regarding Fast Data Masker Masking Options, see:

CA Test Data Manager - 4.7 Masking Options (https://docops.ca.com/ca-test-data-manager/4-7/en/reference/masking-functions-and-parameters/masking-options)
CA Test Data Manager - 4.8 Masking Options (https://docops.ca.com/ca-test-data-manager/4-8/en/reference/masking-functions-and-parameters/masking-options)