Random lookup privacy provider
Use the random lookup privacy provider to select values at random from a lookup table and insert them into a destination field. The provider generates a random number between 1 and the limit or number of rows in the lookup table to use as a subscript into the table. The field value or values from the row that correspond to the subscript are inserted in the destination field. The value that is selected from the lookup table is not based on the source value.
The provider can mask values in multiple fields. You can specify the number of rows in the lookup tables that are used to select replacement value.
Examples
- Single replacement value
The following example uses replacement data that is taken from the lastname fields in the otheruser.optim_us_person lookup table, which is based on a randomly selected row.
pro=random_lookup rep="lastname", id=otheruser.optim_us_person, lib=db2luw, conn=odpplkpu, user=testuser, pass=testpass, flddef1=(name=firstname, dt=varchar_sz), flddef2=(name=lastname, dt=varchar_sz)
This example uses the following parameters:
- REP="lastname"
- This parameter specifies the lookup table fields (lastname) that provide replacement values.
- ID=otheruser.optim_us_person
- This parameter specifies the name of the lookup table, emp_lkp.
- LIB="db2luw"
- This parameter specifies the DBMS of the lookup table, DB2® for Linux®, UNIX, and Windows.
- Multiple replacement values
The following example uses replacement data that is taken from the firstname and lastname fields in the otheruser.optim_us_person lookup table, which is based on a randomly selected row. The syntax specifies a limit of 500,000 for the number of lookup table rows that are used to select replacement values.
pro=random_lookup rep="firstname, lastname", id=otheruser.optim_us_person, lib=db2luw, conn=odpplkpu, user=testuser, pass=testpass, flddef1=(name=firstname, dt=varchar_sz), flddef2=(name=lastname, dt=varchar_sz)
This example uses the following parameters:
- REP="firstname, lastname"
- This parameter specifies the lookup table fields (firstname and lastname) that provide replacement values.
- LIMIT=500000
- This parameter specifies a limit (500,000) for the number of lookup table rows that are used to select replacement values.
- ID=otheruser.optim_us_person
- This parameter specifies the name of the lookup table, emp_lkp.
- LIB="db2luw"
- This parameter specifies the DBMS of the lookup table, DB2 for Linux, UNIX, and Windows.
Syntax
The random lookup privacy provider uses the following syntax:
- Masking parameters
PROVIDER = RANDOM_LOOKUP ,
- Source parameters
[ PRESERVENULL = “fld1-name , fldn-name“ ] , [ PRESERVESPACES = “fld1-name , fldn-name“ ] , [ PRESERVEZEROLENGTH = “fld1-name , fldn-name“ ] ,
- Lookup parameters
REPLACE = “fld1-name , fldn-name“ , [ LIMIT = row-limit-value ] ,
- Connection parameters
[ TMPDIR = temp-directory-location ] , ID = identifier-string , LIB = { DB2LUW | DB2ZOS | ORACLE } , USER = user-name , PASS = password , CONN = connection-string ,
- Processing parameters
[ DISCARDLIMIT = discard-limit-value ] ,
- Data definition parameters
FLDDEFn = ( NAME = field-name, DATATYPE = datatype-value, [ PRECISION = field-precision-value ], [ SCALE = field-scale-value ], [ LENGTH = field-length-value ], [ CODEPAGE = codepage-value ], [ CPTYPE = { DB2ZOS |DB2LUW | ORACLE |SYBASE |ODBC | INFORMIX |NETEZZA |SQLSERVER |TERADATA |ANY |NONE } ] ) , [ CODEPAGE = codepage-value ] , [ CPTYPE = { DB2ZOS | DB2LUW | ORACLE | SYBASE | ODBC | INFORMIX | NETEZZA | SQLSERVER | TERADATA | ANY | NONE } ]
Masking parameters
Parameters that determine how to mask data.
- PROVIDER (or PRO)
- Required. Enter the provider name, RAN_LKP (or RANDOM_ LOOKUP).Note: The PRO parameter must be first in the masking string. All other parameters can appear in any order.
Source parameters
Parameters for managing source values.
- PRESERVENULL (or PRENULL)
- Specifies source fields in which NULL values are preserved and
not replaced with lookup table values.
Use commas to separate a list of source field names. If a field name has spaces or commas, enclose the name in double quotation marks. If a field name has a double quotation mark character, precede the double quotation mark character with a backslash (\) escape character.
- PRESERVESPACES (or PRESPACES)
- Specifies source fields in which blank values are preserved and
not replaced with lookup table values.
Use commas to separate a list of source field names. If a field name has spaces or commas, enclose the name in double quotation marks. If a field name has a double quotation mark character, precede the double quotation mark character with a backslash (\) escape character.
- PRESERVEZEROLENGTH (or PREZL)
- Specifies source fields in which zero length values are preserved
and not replaced with lookup table values.
Use commas to separate a list of source field names. If a field name has spaces or commas, enclose the name in double quotation marks. If a field name has a double quotation mark character, precede the double quotation mark character with a backslash (\) escape character.
Lookup parameters
Parameters for managing lookup table values.
- REPLACE (or REP)
- Required. Specifies the replacement fields in the lookup table.
Use commas to separate a list of replacement field names. If a field name has spaces or commas, enclose the name in double quotation marks. If a field name has a double quotation mark character, precede the double quotation mark character with a backslash (\) escape character.
- LIMIT (or LIM)
- Specifies a limit on the number of lookup table rows that are used to select replacement values. Specify an integer up to a maximum value of 2,000,000,000. If no limit is specified, all rows are used.
Connection parameters
Parameters for connecting to a lookup table.
- TMPDIR
- Specifies the location of the cache file when CACHE=MEMFILE.
- ID
- Required. Specifies the name of the lookup table.
For DB2 for Linux, UNIX, and Windows and DB2 for z/OS®, specify the schema-name and table-name. Use the format schema.table.
- LIB
- Specifies the name of the lookup data source (LDS) library. Enter
one of the following options:
- DB2LUW
- DB2 for Linux, UNIX, and Windows
- DB2ZOS
- DB2 for z/OS
- MSS
- (Release 11.3.0.5 and later) Microsoft SQL Server
- ORACLE
- Oracle
- lds-library-name
- The name of a user-written LDS library.
- USER
- Required. The user name that is used to access the lookup table database.
- PASS
- Required. The password for the user name that is used to access
the lookup table database.
Use double quotation marks to enclose passwords with special characters. For example, "ABC$".
- CONN
- Required. The connection string for the lookup table database.
Processing parameters
Parameters for managing provider processes.
- DISCARDLIMIT (or DLIM)
- Specifies the number of failed rows to discard before the provider stops processing.
Data definition parameters
Parameters for defining source and target data. For further information see, supported data types.
- FLDDEFn
- Required. Specifies the attributes of input values to use for processing. See Field definition parameter.
- CODEPAGE (or CP)
- An integer value that specifies the codepage or character-set identifier of the source fields. The default is UTF-8. The CP parameter within the FLDDEFn parameter overrides this value.
- CPTYPE (or CPT)
- The codepage type of the source fields. The CPT parameter within the FLDDEFn
parameter overrides this value.
When the origin of the data is DBMS-specific but not tied to any one DBMS, specify the value as ANY. When the origin of the data is from a non-DBMS source, specify the value as NONE. As there are no DBMS-specific code pages for Netezza®, a specification of NONE is implied when Netezza is specified.
Enter one of the following values:
Value Description DBZ (or DB2zOS) DB2 for z/OS DB2 (or DB2LUW) DB2 for Linux, UNIX, and Windows IFX (or INFORMIX) Informix® MSS (or SQLSERVER) Microsoft SQL Server NZ or NETEZZA Netezza ODBC ODBC ORA (or ORACLE) Oracle SYB (or SYBASE) Sybase TD or TERADATA Teradata ANY Any DBMS NONE No DBMS
Supported data types
Data type | Description |
---|---|
CHAR | Fixed size character data that is left justified and space padded. |
WCHAR | Fixed size wide character data that is left justified and space padded. |
VARCHAR | Character data starting with a short integer value that indicates the length, in bytes, of the character data to follow. |
WVARCHAR | Wide character data starting with a short integer value that indicates the length, in bytes, of the wide character data to follow. |
VARCHAR_SZ | Character data string which is terminated by a NULL character. |
WVARCHAR_SZ | Wide character data string that is terminated by a NULL character. |
ODBC_DATE | A data type that is used when the source value is a ODPP_ODBC_DATE structure of the Optim™ data masking API. |
ODBC_TIME | A data type that is used when the source value is a ODPP_ODBC_TIME structure of the Optim data masking API. |
ODBC_TIMESTAMP | A data type that is used when the source value is a ODPP_ODBC_TIMESTAMP structure of the Optim data masking API. |
DATETIME_CHAR | A fixed size character data value that is left justified with space padded and contains date-time values. To format this data type value, the caller must supply a format string using parameter ODPP_OPR_SOURCE_DATE_FORMAT of the Optim data masking API. |
DATETIME_VARCHAR | Character data starting with a short integer value that indicates the length, in bytes, of the character data containing date-time values to follow. To format this data type value the caller must supply a format string using parameter ODPP_OPR_SOURCE_DATE_FORMAT of the Optim data masking API. |
DATETIME_WCHAR | Fixed size wide character data that is left justified with space padded and contains date-time values. To format this data type value the caller must supply a format string using parameter ODPP_OPR_SOURCE_DATE_FORMAT of the Optim data masking API. |
DATETIME_WVARCHAR | Wide character data starting with a short integer value that indicates the length, in bytes, of the wide character data containing date-time values to follow. To format this data type value the caller must supply a format string using parameter ODPP_OPR_SOURCE_DATE_FORMAT of the Optim data masking API. |
DATETIME_SZ | A character data string containing date-time values and is terminated by a NULL character. To format this data type value, the caller must supply a format string using parameter ODPP_OPR_SOURCE_DATE_FORMAT of the Optim data masking API. |
DATETIME_WSZ | A character data string containing date-time values and is terminated by a NULL character. To format this data type, value the caller must supply a format string using parameter ODPP_OPR_SOURCE_DATE_FORMAT of the Optim data masking API. |
DOUBLE | Double precision floating point number in the range 1.7E +/- 308 (15 digits). |
FLOAT | Floating point number in the range 3.4E +/- 38 (7 digits). |
LONG_LONG | An 8 byte signed numeric value in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. |
U_LONG_LONG | An 8 byte unsigned numeric value in the range 0 to 18,446,744,073,709,551,615. |
INTEGER | A 4 byte signed integer value in the range -2,147,483,648 to 2,147,483,647. |
U_INTEGER | A 4 byte unsigned integer value in the range 0 to 4,294,967,295. |
SMALLINT | A 2 byte signed integer value in the range -32,768 to 32,767. |
U_SMALLINT | A 2 byte unsigned integer value in the range 0 to 65,535. |
TINYINT | A single byte signed integer value in the range -128 to 127. |
U_TINYINT | A single byte unsigned integer value in the range 0 to 255. |