Unanswered: How to mask a DB2 column while extracting production data.
I want to know how to mask a DB2 column while extracting production data. For this what kind of SQL I can use. Actually we want to copy some production data into a test region and some secured columns like SSN, bank account numbers should be masked before we load the data into test region. Appreciate the help!!
If you're using export/import command in order to migrate data from prod to test systems, you can use any sql function you like.
db2 export to file.ixf of ixf messages file.msg select a,b,substr(c,1,4)||'****'||substr(c,8,4) from table_name
will create a file which has only first and last 4 chars of c columns and stars(*) in between them.
then you can import this file into the test system as
db2 IMPORT FROM file.ixfl OF ixf MESSAGES mes.msg
CREATE INTO table_name IN tablespace_name;
this will create a new table.But there are also other options in import which you can find from the db documentation.
Just to update this thread, DB2 z/OS users can use the fast database copy utility BCV5 with the Data Masking option turned on for particular fields/columns. The advantage is masking / anonymizing sensitive data at high speed. BCV5 saves CPU and wall clock time as it does the copy and mask in one pass compared slower multiple step/pass procedures. See ESAi's website www.esaigroup.com for more details.