SQL20478 error when you run a query in Data Virtualization

SQL20478 error when you run a query

Last updated: Nov 26, 2024

If a data masking rule applies to TableA and you run a query that uses CREATE TABLE ... AS SELECT ... FROM TableA, you might see an error message with Reason code "30". SQLCODE=-20478, SQLSTATE=428HD.

Symptoms

This error occurs because by default, masked data cannot be written to tables or transition-variables. For example, you might see the following error message.

The statement failed because the column mask "EXTERNAL.Transform" defined for column 
"SCHEMA.TABLE_NAME.COLUMN" exists and the column mask cannot be applied or the column mask 
conflicts with the failed statement. Reason code "30". SQLCODE=-20478, SQLSTATE=428HD

Resolving the problem

To allow writing of masked data, contact IBM® Cloud support for Data Virtualization to set the DB2_ALLOW_WRITE_OF_MASKED_DATA registry variable to YES.