How to mask functions for Teradata in Datamaker ?

Products

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

Issue/Introduction

How to mask functions for Teradata in Datamaker ? 

Environment

Release : 4.8

Component : CA Test Data Manager

Resolution

This article would explain  the steps to add a new Datamaker masking function for Teradata.

Pre-req:  Install Teradata https://downloads.teradata.com/
Test to see if DB came up  and that you can log on successfully.
Also download and install Teradata Tools and Utilities from https://downloads.teradata.com/ which would install the  Teradata client utilities, JDBC driver jar(TeraJdbc4.jar), SQL Assistant etc.

Use Sql Assistant utility to execute the below SQL statements.

1. Step-by-step guide to install Scramble Database in Teradata:

1. Define an ODBC systen DSN for Teradata

2. Launch Sql Assistant utility and Connect to the above created ODBC Datasource.

3. CREATE DATABASE scramble_db
   FROM DBC
   AS
   PERM = 1000000000
   ;

4. CREATE USER scrambledb_user AS
   PASSWORD=scrambledbpwd
   PERM = 100000000 -- 100 MB
   SPOOL= 100000000 -- 100 MB
   ;

5. GRANT ALL ON scramble_db TO scrambledb_user;
   GRANT SELECT ON dbc TO scrambledb_user;

   GRANT CREATE PROCEDURE ON SQLJ TO scrambledb_user;
   GRANT EXECUTE PROCEDURE ON SQLJ TO scrambledb_user;

6. The TDM product download comprises of a Teradata scramble DB (Ex: GEN0000000000263\TERADATA_MASKING.zip)
   Extract this zip and run the below command to install the DB and the functions.
   The extracted zip comprises of a READ_BEFORE_INSTALL.txt. Perform the prerequisite actions as instructed.
7. Execute the below command to install the DB
   functions_install.bat TERADATA_HOST scrambledb_user scrambledbpwd scrambleDBName
   Ex: functions_install.bat 192.168.153.128 scrambledb_user scrambledbpwd scramble_db
   Ensure that the command should run without any errors.

 

2. Step-by-step guide to add a new Datamaker masking function for Terdata:

Below steps would describe how to add HASHDOB function for Teradata.

1. Edit the file dmdatafunc_TERADATA.txt under the GT Datamaker Home directory(C:\Program Files (x86)\Grid-Tools\GTDatamaker) and add the new line 
   TERADATA DATE HASHDOB Hash a date keeping original age
2. Launch gtdatamaker executable as OS administrator,connect to the repository and connect to the source/target DB.
3. Press Cntrl+Alt+M key combination to launch the Datamaker administrator logon wizard
4. Expand the Datamaker connection maintenance tree structure on the left pane and expand the Repository item as shown below.
5. Select masking functions tree item which would show the supportedDatabases on the right side.
6. Click on "TeraData" and then click on "Process" button at the right corner which would import the masking functions freshly.
7. Confirmation message would appear as below on successful import
8. Close the administrator wizard.
9. Generation of Transformation Maps would now show the new HashDOB function in the dropdown as shown in the screenshot below
10. GtSubset would now be able to generate Teradata scrambled data export scripts for the new HASHDOB function. 

11. 3. Generate scrambled HASHDOB data using GTSubset:

    1. Create a table in the TeraData Database "scramble_db" created above and insert values.
       CREATE SET TABLE scramble_db.EMPLOYEE,
       FALLBACK( EmployeeNo INTEGER, FirstName VARCHAR(30), LastName VARCHAR(30), DOB DATE FORMAT 'YYYY-MM-DD', JoinedDate DATE FORMAT 'YYYY-MM-DD', DepartmentNo BYTEINT )
       UNIQUE PRIMARY INDEX ( EmployeeNo );
    2. Launch GTDatamaker, create a new project with version  and register the above tabl

    3. Create a transformation map using the above registered table.

    4. Launch GTSubset and create a new profile for Teradata by providing the connection information of the database created in the above steps.
    5. Connect to the Repo and choose the above created project,version,schema and Table.
    6. click on the Database actions button - "Build Teradata Scrambled Windows Export/load" to generate the scripts.

    7. Navigate to the "extractandload" directory in the export directory.

    8. Follow the instruction in the README.txt file in the directory
       Complete the following steps to perform a Teradata Scrambled Extract and Load step by step

       Ex:

       1) Run hoshDOB_fix6_export.bat
       This creates the Scrambled Fast Export files
       2) Check any *_export.log files for errors
       3) Run hoshDOB_fix6_load.bat
       4) Check any *_load.log files for errors
       Ex: 

       hoshDOB_fix6_export.bat 192.168.153.128 scrambledb_user scrambledbpwd
       hoshDOB_fix6_load.bat 192.168.153.128 scrambledb_user scrambledbpwd

    9. Post this we should be able to notice scrambled data in the date columns.