Query on Data masking

[Frank.Voge]

Hi Team,

Suppose I have a column called  'A' with 10 digits in length 
I also have a column called 'B'  

      A                                          B
1234567890                           SAM
2345678912                           HELLO

I want to mask column A and replace the first 5 digits with column B.

Post masking :
SAM   67890  (with 2 spaces)
HELLO78912

Could you please suggest what masking algorithm / approach can this be achieved?

Thank you

[Gene Howard]

Which tool and verison are you using as it might make a difference?
DataMaker?
FDM?
ETC

------------------------------
Gene Howard
Principal Support Engineer
Broadcom
------------------------------

[Frank.Voge]

It is FDM and the version is 4.9 .
And this approach should be for both SQL and Mainframe DBs for maintaining consistency.

[Gene Howard]

I looked at FDM 4.9 and I am not seeing a function that I think will allow you to do this.
Maybe others here will have some suggestions.

------------------------------
Gene Howard
Principal Support Engineer
Broadcom
------------------------------

[Scott Schmitz]

FILL the first 5 characters of A with a space, followed by SQLFUNCTION left(B,5) (select the "use masked values" checkbox)
FDM allows you to specify multiple mask routines on the same field.
Datamaker/Transformation Maps only let you specify 1;   You'll have to manually update the exported .csv file to reflect the 2nd function execution.

[Gene Howard]

Hi @Scott Schmitz would you happen to have an example of this?
​

------------------------------
Gene Howard
Principal Support Engineer
Broadcom
------------------------------

[Scott Schmitz]

You'll have to provide your own Database reflecting the structure/content that John provided.   FDM mask & options files attached.

Attachment(s)

john-mask-a-b.csv   378 B 1 version

john-mask-a-b_options.txt   84 B 1 version

[Frank.Voge]

​Hi Scott,

Looks like SQLFUNCTION is not working. Please see attached file.

Thanks

Attachment(s)

samplelicense1.SCRAMBLE.txt   753 B 1 version

SUMMARY.csv   365 B 1 version

AUDIT.csv   2 KB 1 version

[Scott Schmitz]

Hi John,

As you indicated to Gene that your targets were SQL & Mainframe DBs, I provided a solution for those platforms.   SQLFUNCTION doesn't apply to flat files as there is no database behind it to allow FDM to pass thru the SQL command to get a result.

Scott

[Frank.Voge]

Hi Scott,

By bad. I have Oracle, SQL and Mainframes as DB types. I just tried SQLFUNCTION on oracle DB (as I have to start with Oracle as per the requirement)  and look likes I have to use some other parameter? Or  this solution doesn't work on Oracle DBs?

 Error executing SQLFUNCTION select SQL:select  left('Lopez', 5 )  from dual
>2020-10-19 19:17:18.541 ORA-00904: "LEFT": invalid identifier
​

[Scott Schmitz]

Yeah, Oracle is the outlier in that it doesn't have LEFT defined (unlike SQL Server and DB2).  You have to use SUBSTR.

[Frank.Voge]

Appreciate you help. Thank you so much.​