Teradata Database Data Warehouse Sample Scripts

---------------------------------------------------------------------
-- Protegrity User Defined Functions sample script.
--
-- NOTE: Please change the following 'tags' before executing the script:
--      - REPLACE_DATABASE- Specify the testdatabase.
--      - <data element1> - dataelement used for protecting varchar
--      - <data element2> - dataelement used for protecting integer
--      - <data element3> - dataelement used for protecting date
--
-- This script should be run in BTEQ.
--
-- Copyright (c) 2025 Protegrity USA, Inc. All rights reserved
--
---------------------------------------------------------------------
DATABASE REPLACE_DATABASE;
.IF ERRORCODE != 0 THEN .QUIT 99

BT;
  DROP TABLE SAMPLE1_BAK;
ET;

BT;
  DROP TABLE SAMPLE1_PTY;
ET;

DROP VIEW SAMPLE1;

-----------------------------------------------------------------------
--
-- SAMPLE1: Table with no protection. Contains sample data.
--
-----------------------------------------------------------------------
BT;
  CREATE MULTISET TABLE SAMPLE1 (
    CCN     VARCHAR(32)               NOT NULL,
    LNAM    VARCHAR(32)               NOT NULL,
    RATING  INTEGER                   NOT NULL,
    REFN    INTEGER                   NOT NULL,
    BIRT    DATE FORMAT 'YYYY/MM/DD'  NOT NULL,
    LUPD    DATE FORMAT 'YYYY/MM/DD'  NOT NULL
  );
ET;

BT;
  INSERT INTO SAMPLE1 ('4000567834561233', 'PTY_IVP_FPRTEST_LNAME', 123456789, 987654321, CAST( '2013/02/15' AS DATE ), CAST( '2013/02/15' AS DATE ));
ET;

BT;
RENAME TABLE SAMPLE1 to SAMPLE1_BAK;
ET;

-----------------------------------------------------------------------
--
-- SAMPLE1_PTY: This table is similar to SAMPLE1 will contain protected data.
--              The 'LNAM','REFN', and 'LUPD' columns now are of type VARBYTE.
--              Data is migrated from the 'SAMPLE1_BAK' table using UDF calls.
--
-----------------------------------------------------------------------
BT;
  CREATE MULTISET TABLE SAMPLE1_PTY (
    CCN     VARCHAR(32)               NOT NULL,
    LNAM    VARBYTE(48)               NOT NULL,
    RATING  INTEGER                   NOT NULL,
    REFN    VARBYTE(16)               NOT NULL,
    BIRT    DATE FORMAT 'YYYY/MM/DD'  NOT NULL,
    LUPD    VARBYTE(16)               NOT NULL
  );
ET;

BT;
  INSERT INTO SAMPLE1_PTY("CCN", "LNAM", "RATING", "REFN", "BIRT", "LUPD") SELECT
    "CCN",
    TESTDB.PTY_VARCHARLATINENC("LNAM",'<data element1>',50,0,0),
    "RATING", 
    TESTDB.PTY_INTEGERENC("REFN",'<data element2>',34,0,0),
    "BIRT",
    TESTDB.PTY_DATEENC("LUPD",'<data element3>',34,0,0)
  FROM SAMPLE1_BAK;
ET;

-----------------------------------------------------------------------
--
--  SAMPLE1: This is a view that shows how data is unprotected using the UDFs.
--           Data is selected from the 'SAMPLE1_PTY' table.
--           The name of this view is the same as the original table
--
-----------------------------------------------------------------------
BT;
  CREATE VIEW SAMPLE1 ("CCN", "LNAM", "RATING", "REFN", "BIRT", "LUPD") AS SELECT
    "CCN",
    CAST(TESTDB.PTY_VARCHARLATINDEC("LNAM",'<data element1>',32,0,0) AS VARCHAR(32)),
    "RATING",
    CAST(TESTDB.PTY_INTEGERDEC("REFN",'<data element2>',0,0) AS INTEGER),
    "BIRT"
    ,
    CAST(TESTDB.PTY_DATEDEC("LUPD",'<data element3>',0,0) AS DATE)
  FROM SAMPLE1_PTY;
ET;

---------------------------------------------------------------------
-- Protegrity User Defined Functions.
-- Copyright (c) 2025 Protegrity USA, Inc. All rights reserved
--
-- This script should be run in BTEQ
--
-- NOTE: Please change the following 'tags' before executing the script:
--      - TESTDB - database where the protegrity UDF's are installed
--      - REPLACEDB - Database where you have testdata
--      - <data element1> - dataelement used for protecting varchar
--      - <data element2> - dataelement used for protecting integer
--      - <data element3> - dataelement used for protecting date
--NOTE: Use datetime dataelement to protect and unprotect YMD date data
---------------------------------------------------------------------
DATABASE REPLACE_DATABASE;
.IF ERRORCODE != 0 THEN .QUIT 99

-----------------------------------------------------------------------
--
-- SAMPLE - Two tables and one view is created as follows:
--
-- Run the sample_tok.sql job to verify protect / unprotect of datatypes
-- VARCHAR, DATE and INTEGER.
--
-----------------------------------------------------------------------
BT;
  DROP TABLE SAMPLE1_BAK;
ET;

BT;
  DROP TABLE SAMPLE1_PTY;
ET;

DROP VIEW SAMPLE1;

-----------------------------------------------------------------------
--
-- SAMPLE1  Base table with no protection
--
-----------------------------------------------------------------------
BT;
  CREATE MULTISET TABLE SAMPLE1 (
    CCN     VARCHAR(32)               NOT NULL,
    LNAM    VARCHAR(32)               NOT NULL,
    RATING  INTEGER                   NOT NULL,
    REFN    INTEGER                   NOT NULL,
    BIRT    DATE FORMAT 'yyyy-mm-dd'  NOT NULL,
    LUPD    DATE FORMAT 'yyyy-mm-dd'  NOT NULL
  );
ET;

BT;
  INSERT INTO SAMPLE1 ('4000567834561233', 'PTY_IVP_FPRTEST_LNAME', 123456789, 987654321, CAST( '2013/02/15' AS DATE ), CAST( '2013/02/15' AS DATE ));
ET;

BT;
RENAME TABLE SAMPLE1 to SAMPLE1_BAK;
ET;

-----------------------------------------------------------------------
--
-- SAMPLE1_PTY Same as SAMPLE1 but with protection added fo 
--             columns, which are encrypted / tokenized when the 
--             table is loaded from SAMPLE1_BAK.
--
-----------------------------------------------------------------------
BT;
  CREATE MULTISET TABLE SAMPLE1_PTY (
    CCN     VARCHAR(32)               NOT NULL,
    LNAM    VARCHAR(32)               NOT NULL,
    RATING  INTEGER                   NOT NULL,
    REFN    INTEGER                   NOT NULL,
    BIRT    DATE FORMAT 'yyyy-mm-dd'  NOT NULL,
    LUPD    DATE FORMAT 'yyyy-mm-dd'  NOT NULL
  );
ET;

BT;
  INSERT INTO SAMPLE1_PTY("CCN", "LNAM", "RATING", "REFN", "BIRT", "LUPD") SELECT
    TESTDB.PTY_VARCHARLATININS("CCN",'<data element1>',32,0,0),
    "LNAM",
    TESTDB.PTY_INTEGERINS("RATING",'<data element2>',32,0,0),
    "REFN",
    CAST(TESTDB.PTY_VARCHARLATININS(CAST("BIRT" AS VARCHAR(32)),'<data element3>',32,0,0) AS DATE),
    "LUPD"
  FROM SAMPLE1_BAK;
ET;

-----------------------------------------------------------------------
--
--  SAMPLE1 Same as SAMPLE1_PTY. But data is decrypted /detokenized
--              when SAMPLE1 is loaded from SAMPLE1_PTY.
--
-----------------------------------------------------------------------
BT;
  CREATE VIEW SAMPLE1 ("CCN", "LNAM", "RATING", "REFN", "BIRT", "LUPD") AS SELECT
    TESTDB.PTY_VARCHARLATINSEL("CCN",'<data element1>',32,0,0),
    "LNAM",
    TESTDB.PTY_INTEGERSEL ("RATING",'<data element2>',0,0),
    "REFN",
    CAST(TESTDB.PTY_VARCHARLATINSEL(CAST("BIRT" AS VARCHAR(32)),'<data element3>',32,0,0) AS DATE),
    "LUPD"
  FROM SAMPLE1_PTY;
ET;