Installing and configuring the Optim data privacy user-defined functions for Teradata in Microsoft Windows

Use these instructions to install and configure the Optim™ user-defined functions (UDFs) and user-defined table functions (UDTFs) for Teradata in a Microsoft Windows environment. After the UDFs are installed, you can use them to dynamically mask Teradata data.

1. In the data privacy UDF installation media, locate and download the Optim-DPP-v.v.v.v_yyyymmdd-BUILDnnnn.zip file, where v.v.v.v is the version number for the Optim data privacy solution, yyyy is the four-digit year, mm is the two-digit month, dd is the two-digit day, and nnnn is the four-digit build number.
2. Extract the zip file into a known location on the machine where the Teradata server is installed. After you extract the .zip file, the location includes the following directories:

   Bldnnnn
   Doc
   Images31
   Images32
   Images64
   Replacement Data
   Samples
   Scripts

3. Create a target directory for the data privacy UDF software on the Teradata server node. For example, you can use the following command:

   mkdir C:\IBM\InfoSphere\Optim\dp

   Note: If you are installing on a multi-node Teradata server, you must use the same target directory path on each node.
4. Copy the files from the directory for your operating system and Teradata version (32-bit or 64-bit) to the target directory, as the following table describes.

   If	Then
   Either operating system or Teradata is 32-bit	Copy all files from Images32\win_udf_tera
   Both operating system and Teradata are 64-bit	Copy all files from Images64\win_udf_tera

   The target directory will contain the following directories:

   bin
   icufiles
   include
   src

5. In the target directory, create the following new directories, udftrc and license, for the UDF trace and license files.
6. Before you can use the UDFs, you must license them. If you have an Optim license key that includes data privacy, use the Optim license program, ODPPLicp, to license the UDFs. The license program is located in the bin folder for the UDFs. For more information about using the license program, see Optim Data Privacy solution licensing information.
7. Within the Teradata server, you must set up the following system environment variables, and then reboot the Teradata server to propagate these settings throughout Teradata. The following paths are examples. Change these settings, as needed, to reflect your environment.

   ODPPERRL=C:\IBM\InfoSphere\Optim\dp\include
   ODPPICUDIR=C:\IBM\InfoSphere\Optim\dp\icufiles
   ODPPLIBL=C:\IBM\InfoSphere\Optim\dp\bin
   ODPPLL=C:\IBM\InfoSphere\Optim\dp\license

   Optionally, you can set the ODPPINSTMGR variable to specify the Instance Manager usage mode. The Instance Manager can be used to improve performance.

   Note: For more information about these variables, see Specifying environment variables for data privacy. For Optim release 11.3.0.6 and higher, environment variable ODPPERRL is not required because ODPP messages are no longer contained in file ODPPErrMsgs_EN.xml; these messages are now contained internally in the ODPP software. Therefore, the ODPPERRL environment variable, which was formerly used to point to this file, is no longer needed. For reference, ODPP return codes and messages are stored in file ODPPMessages.txt in the Doc directory.
8.  (Primary node)  The Teradata UDF installation requires the use of a C compiler to compile and link the UDFs. See the Teradata installation manuals for your version of Teradata for information about the appropriate C compiler. After you install the UDF software, you must configure your Teradata server with the registration of the UDFs. In the bin folder where you installed the UDF software, run the Teradata UDF installation program, ODPPTeraUDFInstaller.
   Execute the following command from a command prompt in the directory where you installed the UDF for Teradata software:

   .\ODPPTeraUDFInstaller

   When you are asked whether you want to install, uninstall, or quit, type the number 1 to install the UDFs.

   A series of prompts are then displayed. When prompted for

   • the Teradata server, specify the name of your Data Base Computer (DBC).
   • a User, specify the Teradata user name under which the functions will be created.
   • a Password, specify the password for the Teradata user.
   • a Database, specify the database name in which the functions are to be created. (The default is SYSLIB.)
   • an ODPP installation directory, specify the path to the directory where the UDF software was installed.
   • an ODPP version, specify the Optim data privacy solution version. (The default version number is included in the prompt.)

   The UDFs are then installed, created, and registered with Teradata.

9.  (Primary node)  Execute the following Teradata SQL queries to verify that the UDFs and UDTFs are properly installed and configured.

   SELECT OptimMaskStrLatin('180027485132398', 'PRO=CCN,
   FLDDEF1=(NAME=COL1, DT=CHAR)');
   
   SELECT OptimMaskInt64(180027485132398, 'PRO=CCN,
   FLDDEF1=(NAME=COL1, DT=U_LONG_LONG)');
   
   CREATE TABLE ODPP_US_PERSON
   ( SEQ INTEGER NOT NULL
   , FIRSTNAME VARCHAR(60) CHARACTER SET LATIN NOT NULL
   , LASTNAME VARCHAR(60) CHARACTER SET LATIN NOT NULL
   , EMAIL_ADDRESS VARCHAR(100) CHARACTER SET LATIN NOT NULL
   ) UNIQUE PRIMARY INDEX ( SEQ );
   
   INSERT INTO ODPP_US_PERSON VALUES (1, 'Andrew', 'Duke',
   'Andrew.S.Duke@pookmail.com'); INSERT INTO ODPP_US_PERSON VALUES (2,
   'John', 'Johnson', 'John.J.Johnson@spamit.com');
   
   INSERT INTO ODPP_US_PERSON VALUES (3, 'David', 'Eaton',
   'David.S.Eaton@spamit.com'); 

   When you use a provider that requires multiple arguments, group the arguments in a dynamic user-defined type (UDT) by using the NEW VARIANT_TYPE function. Here is an example:

   SELECT
   SEQ,
   EMAIL_ADDRESS,
   FIRSTNAME,
   LASTNAME,
                  OptimMaskStrLatin(NEW VARIANT_TYPE(EMAIL_ADDRESS,
                                    FIRSTNAME, LASTNAME),'PROVIDER=EML,
                                    PARTS="(2,3)",
                                    FLDDEF1=(NAME=EMAIL_ADDRESS,
                                    DT=VARCHAR_SZ),
                                    FLDDEF2=(NAME=FIRSTNAME,
                                    DT=VARCHAR_SZ),
                                    FLDDEF3=(NAME=LASTNAME,
                                    DT=VARCHAR_SZ)')
   FROM ODPP_US_PERSON;

   When you use a table-type function (UDTF), the first argument is a dynamic UDT containing the set of keys to be returned for use in the join predicate. The second argument is a dynamic UDT containing the set of columns to be masked. Here is an example:

   SELECT
   SEQ,
   EMAIL_ADDRESS,
   MASKVALUE
   FROM ODPP_US_PERSON t1,
                      TABLE (OptimMaskTable(NEW
                      VARIANT_TYPE(ODPP_US_PERSON.SEQ),
                      NEW VARIANT_TYPE(ODPP_US_PERSON.EMAIL_ADDRESS),
                      'PROVIDER=EML, FLDDEF1=(NAME=EMAIL_ADDRESS, DT=CHAR)' )
   RETURNS (KEYVALUE INT, MASKVALUE VARCHAR(100)) )
   AS t2
            WHERE t1.SEQ = t2.KEYVALUE;