Installing and configuring the Optim data privacy user-defined functions for DB2 for z/OS

Use these instructions to install and configure the Optim™ user-defined functions (UDFs) for DB2® for z/OS®. After the UDFs are installed, you can use them to dynamically mask DB2 for z/OS data.

Important: Before you can use the data privacy 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.

If you need a license, contact your IBM representative for information about how to obtain a license key that includes data privacy. Your license key identifies the Optim features you are entitled to use, such as the data privacy UDFs. For more information, see Licensing requirements for the Optim data privacy components.

Before you install the UDFs, consult your DB2 for z/OS DBA because prior experience with UDFs can help ensure a smooth installation.

The installation of the UDFs requires two primary steps:
  • Definition of the UDF names for DB2 for z/OS.
  • Installation of the provided external function IOQYMASK.
The following table lists the data privacy UDFs for masking DB2 for z/OS data. It includes the regular and specific names for each UDF and the associated input and return data types.
Note: To mask data, run the UDF using the name in the "Regular UDF name" column. Use the name in the "Specific UDF name" column to perform administrative operations, like ALTER FUNCTION.
Table 1. Data privacy UDFs for DB2 for z/OS
Regular UDF name Specific UDF name Input data type Return data type
OptimMaskStr OptimMaskStr VARCHAR(32704) VARCHAR(2048)
OptimMaskStrEBCDIC OptimMaskStrEBCDIC VARCHAR(32704) VARCHAR(2048)
OptimMaskInt64 OptimMaskInt64 BIGINT BIGINT
OptimMaskInt64HashChar VARCHAR(32704) BIGINT
OptimMaskInt64HashDate DATE BIGINT
OptimMaskInt64HashTime TIME BIGINT
OptimMaskInt64HashTs TIMESTAMP BIGINT
OptimMaskInt64HashNum18S0 NUMERIC(18,0) BIGINT
OptimMaskInt64HashDouble DOUBLE BIGINT
OptimMaskNum OptimMaskNum18S0 NUMERIC(18,0) NUMERIC(18,0)
OptimMaskNum18S2 OptimMaskNum18S2 NUMERIC(18,2) NUMERIC(18,2)
OptimMaskNum18S4 OptimMaskNum18S4 NUMERIC(18,4) NUMERIC(18,4)
OptimMaskNum18S6 OptimMaskNum18S6 NUMERIC(18,6) NUMERIC(18,6)
OptimMaskDouble OptimMaskDouble DOUBLE DOUBLE
OptimMaskDate OptimMaskDate DATE DATE
OptimMaskTime OptimMaskTime TIME TIME
OptimMaskTimestamp OptimMaskTimestamp TIMESTAMP TIMESTAMP
OptimMask OptimMasKOInt BIGINT BIGINT
OptimMaskONum18S0 NUMERIC(18,0) NUMERIC(18,0)
OptimMaskOStr VARCHAR(32704) VARCHAR(2048)

Do the following steps to install the Optim UDFs and the data privacy providers used by the UDFs to mask data.

  1. The Optim data privacy providers and UDFs for z/OS are installed using SMP/E, which is the basic tool for installing and maintaining software in z/OS systems and subsystems. (The Optim data privacy UDFs are installed along with the providers.) SMP/E is an integral part of the installation, service, and maintenance processes for z/OS software products and product packages.

    Refer to the Program Directory for FMID HAI8B30 to install the Optim providers and UDFs.

  2. Customize the IOQUCSQL member of the OPTIM.ODPP.UDF.CNTL data set with the name of the WLM address space where you want the UDFs to run. For details, see the WLM ENVIRONMENT parameter in the IOQUCSQL member.
  3. Execute the SQL statements file. There are a number of ways to execute the SQL statements that are required to create the UDFs, including the two following methods:
    • Use IOQUCSQL as input to SPUFI.
    • Submit the customized IOQUINST to execute DSNTIAD with IOQUCSQL as the input. The IOQUINST member of the OPTIM.ODPP.UDF.CNTL data set must be customized according to the requirements of your installation. This JCL file is used to run the DB2 for z/OS sample program DSNTIAD for executing SQL statements. This includes changes to the job card, data set names, DSNTIAD plan name, and other changes.
      Note: DSNTIAD is a DB2 for z/OS sample program. If it was not installed with DB2 for z/OS, it must be assembled, linked, and bound before use.

    To create the UDFs, select one of these methods as the input basis or use another method that uses the SQL statement file (IOQUCSQL).

  4. Do the following to create an environment variable data set for the WLM. The CREATE FUNCTION statements within the IOQUCSQL member contain a RUN OPTIONS parameter that identifies a sequential data set that contains the specifications for the environment variables. You must create this sequential data set with the characteristics of RECFM=V, LRECL=256, BLKSIZE=260. For the purposes of these instructions, the data set is named OPTIM.ODPPENV.
  5. After you create the environment variable data set, you must add statements that specify the environment variables. Here are the required statements in the environment variable data set for DB2 for z/OS:
    • LIBPATH=/usr/local/odpp/odppbin
    • ODPPLL=/usr/local/odpp/license
    • ODPPERRL=/usr//local/odpp/odppbin
    • ODPPTRCL=/usr/local/odpp/tracefiles

    Change these examples, as needed, to meet your requirements. Each of these statements must occur on a separate line within the data set and cannot continue over multiple lines.

    • The LIBPATH specification identifies where the Optim data privacy bin directory is located.
    • The ODPPLL specification identifies where the license files for data privacy are located.
    • The ODPPERRL specification identifies where the error message file is located.
    • The ODPPTRCL specification identifies where the trace files are located. If you omit this variable, the trace files are put in the /tmp folder.
    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.
  6. Modify the JCL for the Workload Manager (WLM) started task that is appropriate for your UDF to include a DD statement that identifies a sequential data set that contains the environment variable specifications.

    This statement is a combination of two things:

    • the DD reference from the RUN OPTIONS parameter on each of the provided CREATE FUNCTION statements in IOQUCSQL, and
    • the data set that contains the environment variable statements.

    Based on this information, insert the following DD statement in the WLM JCL:

    FOPENV DD DISP=SHR,DSN=OPTIM.ODPPENV

    The DD name of FOPENV is identified in the RUN OPTIONS parameter of the CREATE FUNCTION statements in IOQUCSQL. The DSN name of OPTIM.ODPPENV was specified when the data set was created. Modify these entries, as needed, to meet your requirements.

    Next, concatenate the Load library that contains the IOQYMASK load module to the STEPLIB of your WLM started task.

  7. The IOQUINSV member of the OPTIM.ODPP.UDF.CNTL data set is a sample script that runs a SELECT query to invoke the UDF to mask credit card numbers.

    The output is the credit card column and the output from the OptimMask UDF that runs the Credit Card Number (CCN) provider to mask the credit card column. The query results will show both the before and after entries for the credit card column, as a result of the masking process.

    There are a number of ways to execute these SQL query statements to verify the UDFs are properly installed and configured, including the following methods:

    • Use IOQUINSV as input to SPUFI
    • Submit the customized IOQUINST to execute DSNTIAD with IOQUINSV as the input.

    Select one of these methods to verify the installation of the UDF or use another method that uses the SQL query file (IOQUINSV) as the basis to run a SQL query.