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:
Define an ODBC systen DSN for Teradata
Launch Sql Assistant utility and Connect to the above created ODBC Datasource.
CREATE DATABASE scramble_db
PERM = 1000000000
CREATE USER scrambledb_user AS
PERM = 100000000 -- 100 MB
SPOOL= 100000000 -- 100 MB
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;
- 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.
- 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.
- 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
- Launch gtdatamaker executable as OS administrator,connect to the repository and connect to the source/target DB.
- Press Cntrl+Alt+M key combination to launch the Datamaker administrator logon wizard
- Expand the Datamaker connection maintenance tree structure on the left pane and expand the Repository item as shown below.
- Select masking functions tree item which would show the supportedDatabases on the right side.
- Click on "TeraData" and then click on "Process" button at the right corner which would import the masking functions freshly.
- Confirmation message would appear as below on successful import.
- Close the administrator wizard.
- Generation of Transformation Maps would now show the new HashDOB function in the dropdown as shown in the screenshot below.
- GtSubset would now be able to generate Teradata scrambled data export scripts for the new HASHDOB function.
3. Generate scrambled HASHDOB data using GTSubset:
- 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 );
- Launch GTDatamaker, create a new project with version and register the above table
Create a transformation map using the above registered table.
- Launch GTSubset and create a new profile for Teradata by providing the connection information of the database created in the above steps.
- Connect to the Repo and choose the above created project,version,schema and Table.
- click on the Database actions button - "Build Teradata Scrambled Windows Export/load" to generate the scripts.
Navigate to the "extractandload" directory in the export directory.
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
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
hoshDOB_fix6_export.bat 192.168.153.128 scrambledb_user scrambledbpwd
hoshDOB_fix6_load.bat 192.168.153.128 scrambledb_user scrambledbpwd
- Post this we should be able to notice scrambled data in the date columns.