UNIQUE COLUMN VALUES fetching NULL and leaving the column unmasked

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

While reviewing the FDM Audit log for a masking job, we noticed that for some reason, FDM is picking up NULL values in the Unique Column, and when this happens the masking for that column is skipped.

Environment

Release : 4.10

Cause

The "nulls" found in the audit log are returned by Java and not the database. FDM writes them to the audit log on purpose. Anytime there are no rows updated for a table, Java reports it as a "null", and we record it in the log.
A review of the FDM debug log, Shows why some columns were not masked:

2023-04-02 19:02:30.754 pool-3-thread-1 - At least one restart column in table <TABLE_1> is not unique - with multiple WHERE conditions its recommended to use unique restart columns to avoid partially masked rows. Either specify unique restart column explicitly for each WHERE condition or enable UNIQUERESTARTCOLUMNS option.

2023-04-02 19:02:30.754 pool-3-thread-3 - At least one restart column in table <TABLE_2> is not unique - with multiple WHERE conditions its recommended to use unique restart columns to avoid partially masked rows. Either specify unique restart column explicitly for each WHERE condition or enable UNIQUERESTARTCOLUMNS option.

Anytime there are multiple WHERE conditions for one table and there's not a specific unique restart column for each condition, or the UNIQUERESTARTCOLUMNS option isn't enabled, or the WHERE conditions are disjoint (the returned rows don't overlap), every database row which satisfies more than one WHERE condition will be masked only by the first masking rule - the rest of the masking rules will see the row as already masked and will not touch it.

Resolution

Enhancements have been made to FDM in FastDataMasker-4.10.111.0 or greater, which can be downloaded from the Test Data Manager (TDM) Support Patches page.

With this release of FDM, there should not be any more Java "nulls" in the audit log, as the logging has been changed to no longer record the "nulls" returned by Java, since this was creating confusion, and being mistaken as database values.
FastDataMasker-4.10.111.0 provides an additional masking option; UNIQUERESTARTCOLUMNSREQUIRED.
By default, this option is disabled, so you will need to explicitly enable the option by setting the value to Y.

When this option is enabled (set to Y) and multiple WHERE conditions with non-unique restart columns are detected (UNIQUERESTARTCOLUMN = N), then the masking will be aborted with exit code 1. Therefore, if the option UNIQUERESTARTCOLUMNSREQUIRED = Y is set, you will also need to ensure UNIQUERESTARTCOLUMN = Y is also set in the masking options.

NOTE: The FDM masking option defaults can be overridden, by creating a global_options.txt file inside the FDM installation directory (C:\Program Files\Grid-Tools\FastDataMasker). By placing it in the global_options.txt file, it will then be enabled for all masking jobs, including those executed by TDM Portal, unless they are overridden explicitly by the local options settings.

For Example, if you want to globally set enable the RESTART, UNIQUERESTARTCOLUMNSREQUIRED, and UNIQUERESTARTCOLUMN options, you would create the global_options.txt file and place each option on a separate line.

RESTART = Y
UNIQUERESTARTCOLUMNSREQUIRED = Y
UNIQUERESTARTCOLUMN = Y