FDM: DB2 masking error DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We are trying to mask legacy DB2 tables using FDM 4.11.71.0, where some of the columns being masked have 'NOT NULL' constraints. In the past, we enabled 'KEEPNULLS=Y', 'BLANLASNULL=Y',  'EMPTYASNULL=Y', and used a WHERE clause with 'NOT NULL' or 'NOT IN ('')' to skip the null values being masked. However, it's not working for this table. 

Environment

FDM 4.11.71.0

Cause

The DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502 indicates that the problem is clearly due to FDM attempting to do an insert a NULL value in a column that is not allowed to contain a NULL.

Because the problem is with inserting NULL values in a not-NULL column, we need to enable the auditing, by setting the AUDIT=ALL option, and rerun the job to see where NULL values are getting inserted, and what the old value is for that column/row.

Resolution

Looking at the audit file, it seems like the problem could be caused by setting 'BLANKASNULL=Y' and/or 'EMPTYASNULL=Y'. 
For every instance where the old value is either blank or empty, we are inserting a NULL.

Disable those options, by setting 'BLANKASNULL=N' and 'EMPTYASNULL=N', and rerun the job.

Additional Information

For more information regarding masking options, see TDM Masking Options