Creating the Teradata User Defined Functions (UDFs)

Before creating the UDFs, ensure that the following prerequisites are met:

• You have installed the Teradata Data Warehouse Protector on all the nodes.

• When installing the Teradata objects, you must specify the maximum data size to be allocated by the UDFs. This value should not exceed 500 MB.

  • When you calculate the data size, ensure that you also consider the space for the overheads.
    For example:
    • For the data that would be tokenized using non-length preserving tokens, you must add an overhead of approximately 6% to the original data size.
    • For the AES-encrypted data, with the blocks of 16 bytes, you must add an overhead of an additional 16 bytes to include CRC or IV.

• The database user that installs the UDFs must have the following privileges:

  • GRANT CREATE FUNCTION ON PROTEGRITY to USER1;
    

  • GRANT ALTER FUNCTION ON PROTEGRITY to USER1;
    

    • USER1 is the database user who install the UDFs.
    • PROTEGRITY is the name of the database where the UDFs are installed.
    • ROLE1 is the group to which the USER1 belongs.
      Ensure that the database user who installs the UDFs is part of the ROLE1 group.

• To grant privileges to a database user to perform database administration functions, run the following query:

  GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE, STATISTICS, DUMP, RESTORE, CHECKPOINT, SHOW, EXECUTE PROCEDURE, ALTER PROCEDURE, EXECUTE FUNCTION, ALTER FUNCTION, ALTER EXTERNAL PROCEDURE, CREATE OWNER PROCEDURE, CREATE TABLE, CREATE VIEW, CREATE MACRO, CREATE TRIGGER, CREATE PROCEDURE, CREATE FUNCTION, DROP TABLE, DROP VIEW, DROP MACRO, DROP TRIGGER, DROP PROCEDURE, DROP FUNCTION ON TESTDB TO ROLE1;
  

• To distribute the installation on all the nodes while installing the UDF in a multi-node environment, you can run either of the following commands:

  • UNIX commands:

    psh mkdir /opt/protegrity/
    

  • PUT utility:

    pcl -send /opt/protegrity/* /opt/protegrity/
    

To create the UDFs for Teradata:

1. Log in to the server as the user with the required permissions.

2. Navigate to the /opt/protegrity/databaseprotector/teradata/sqlscripts/ directory.

3. To view the .sql queries, run the following command:

   /opt/protegrity/databaseprotector/teradata/sqlscripts/ # ls -ltr
   

4. Press ENTER.
   The list of available queries in the .sql file format appears.

   total 164
   -rw-r----- 1 tdatuser tdtrusted 8939 createdecimalobjects.sql
   -rw-r----- 1 tdatuser tdtrusted 2560 dropobjects.sql
   -rw-r----- 1 tdatuser tdtrusted 781 dropvarcharunicode.sql
   -rw-r----- 1 tdatuser tdtrusted 67128 createobjects.sql
   -rw-r----- 1 tdatuser tdtrusted 10294 createvarcharunicode.sql
   -rw-r----- 1 tdatuser tdtrusted 8401 createdecimalobjects_a.sql
   -rw-r----- 1 tdatuser tdtrusted 793 dropvarcharunicode_a.sql
   -rw-r----- 1 tdatuser tdtrusted 1875 dropobjects_a.sql
   -rw-r----- 1 tdatuser tdtrusted 19643 createobjects_a.sql
   -rw-r----- 1 tdatuser tdtrusted 5078 createvarcharunicode_a.sql
   -rw-r----- 1 tdatuser tdtrusted 5300 testscript.sql
   -rw-r----- 1 tdatuser tdtrusted 3558 sample_tok.sql
   -rw-r----- 1 tdatuser tdtrusted 3324 sample_enc.sql
   

5. To start the bteq, run the following command:

   /opt/protegrity/databaseprotector/teradata/sqlscripts/ # bteq
   

6. Press ENTER.
   The prompt to log in to the database appears.

   Enter your logon or BTEQ command:
   

7. To log in to the database, run the following command:

   .logon < username >
   

8. Press ENTER.
   The prompt to enter the database password appears.

   Password:
   

9. Enter the database password.

10. Press ENTER.
    The connection to the Teradata database is completed successfully.

    *** Logon successfully completed.
    

11. To create the UDFs, execute the following query:
    

    .run file=createobjects.sql
    

12. Press ENTER.
    The script creates the UDFs and the following message for each of the created UDF appears.

    *** Function has been created.
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

13. To create the Varchar Unicode UDFs, execute the following query:

    .run file=createvarcharunicode.sql
    

14. Press ENTER.
    The script creates the UDFs and the following message for each of the created UDF appears.

    *** Function has been created.
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

15. To create the Decimal UDFs, execute the following query:

    .run file=createdecimal.sql
    

16. Press ENTER.
    The script creates the Decimal UDFs and the following message for each of the created UDF appears.

    *** Function has been created.
    *** Warning: 5607 Check output for possible warnings encountered in compiling and/or linking UDF/XSP/UDM/UDT.
    *** Total elapsed time was 1 second.
    

For more information about the User Defined Functions (UDFs) for Teradata, refer to User Defined Functions and API.