FDM masking issue on DB2 -- the log shows updated but the data is not changed

Products

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

Issue/Introduction

The FDM log shows no issue, but the database is not changed after mask, DBA confirms the there is no commit.

It's FDM 4.9

Environment

FDM 4.9

Cause

The table to update also appears in the From clause of the sub query. This is not allowed in DB2.

Use case:

log example:

Thread:PRDCT_ADDR - preparedUpdateStatement=update "TUSTG"."PRDCT_ADDR" SET ...

Thread:PRDCT_ADDR - Masking table PRDCT_ADDR using where clause: PRDCT_NUM IN (SELECT  A.PRDCT_NUM FROM  TUSTG.PRDCT_ADDR A

So the table to update is PRDCT_ADDR, and the where clause has a sub query : "PRDCT_NUM IN (SELECT  A.PRDCT_NUM FROM  TUSTG.PRDCT_ADDR A ..."

 

When try the same masking on TDM mainframe, it returns following error,

SQL ERROR: Check REPORT for further info

 

DSNT408I SQLCODE = -118, ERROR:  THE OBJECT TABLE OR VIEW OF THE DELETE

          OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE

DSNT418I SQLSTATE   = 42902 SQLSTATE RETURN CODE

 

From DB2 document,

https://www.ibm.com/docs/en/db2/11.5.x?topic=messages-sql0000-0999

The "SQLCODE = -118" "SQLSTATE = 42902" in DB2 means,

"The table or view specified as the target of an INSERT, DELETE, or UPDATE statement also appears in the FROM clause of a subquery within the statement.

The table or view that is the target of an INSERT, UPDATE, or DELETE cannot also be used to supply the values to be inserted or to qualify the rows to be inserted, updated, or deleted.

The statement cannot be processed."

Resolution

Use temporary table instead of the sub query.