TRANS COL Data Privacy Function

Resolving The Problem

TRANS COL

The TRANS COL function can mask data that has no inherent format or a format that is not widely known. TRANS COL maintains the format and character type of the source data at the destination.

If the source data is upper case, alphabetic characters, TRANS COL generates upper case, alphabetic characters at the destination. This function masks alphabetic and numeric characters, but other characters in the source data are copied to the destination without being changed. You can use TRANS COL to mask CHAR, VARCHAR, and non-float numeric data types. You can generate unique values, a different value for each occurrence of the same source, and you can generate values with a length different from the source.

The syntax of TRANS COL is:

TRANS COL ( '{ unique | hash }

Generate a unique destination value. The length of the destination value will be the same as the source value length.

Generate a destination value by hashing the source value. When hash is used, different source values can produce the same destination values each time the process is run.

Note: For the same source value, it is possible to obtain the same destination value when either the unique parameter or the hash parameter is used. Use hash with the seed parameter to produce different destination values each time the process is run.

Use this parameter to specify the name of the source column if the destination column is different from the source column name. The value you specify will be converted to uppercase; to prevent the value from conversion to uppercase, enclose the value in double quotation marks.

One or more pairs of substrings to be copied to the destination without being masked. If you supply a literal string, the source characters in the specified positions are replaced. The copy= parameter is valid only for a character data type column.

Value used to alter the behavior of the masking algorithms. Specify a literal string, reference to an environment variable, or RANDOM.

"lit“
To specify a literal string, enclose the string in double quotation marks.

var (variable)
Specify an environment variable enclosed in parentheses. The variable name and its value cannot include double quotation marks.

RANDOM
Generate a random seed value from the current system date and time.

Generate a destination value with a length different from the source value length. Use length=max to generate a destination value that will fill the column completely. Specifying a length shorter than the source value causes the source value to be truncated when it is written to the destination. The value you select for n cannot exceed the defined length of the destination column. The length= parameter is valid only with hash=.

List one or more source values that should not be replaced at the destination. Allowable values are null, spaces, or zero_len.

null
If the source column has a null value do not replace the value at the destination.

spaces
If the source column has a value of spaces do not replace the value at the destination. For CHAR columns only.

zero_len
If the source column has a zero-length VARCHAR value do not replace the value at the destination.

char1[charn...]
The specified source column character or characters are not masked and not written to the destination. For example, if you specify TRIM=(xyz123,), if any of the characters x, y, z, 1, 2, 3, or , appear anywhere in the source column, they will not be masked or written to the destination. Note that , is not a separator but a character in the column that is looked at to not be masked

[\u]
Use this parameter to convert the character(s) to upper case before masking. If a character has no upper case representation, it remains unchanged. For example, specifying TRIM=(xy \u) does not mask the characters x and y if they appear anywhere in the source column, and changes any other source column characters to upper case before masking them.

[\r]
Use this parameter to remove trailing spaces. For example, TRIM=(xy \u \r) does not mask the characters x and y if they appear anywhere in the source column, changes any other source column characters to upper case before masking them, and removes any trailing spaces before masking.

Use this parameter to cause the transformation of integers in a character data type column to be identical to that of a numeric data type column. The num parameter is valid only on numeric values in a character data type column. When used in this way, Foreign Key integrity is maintained across differing data types. If you use this parameter, do not specify copy= or length=.

• TRANS COL ('UNIQUE')
  for source value CDE-7834
  could produce destination value: XVT-0361
• TRANS COL ('UNIQUE')
  for source value CDE-7834-2008
  could produce destination value: XVT-0361-1123
• TRANS COL ('UNIQUE')
  for source value Smith, John
  could produce destination value: Kadom, Osnm
• TRANS COL ('UNIQUE')
  for source value SMITH JOHN
  could produce destination value: KADOM OSNM
• TRANS COL ('UNIQUE,COPY=(1,3)(10,4),preserve=(spaces null)')
  for source value CDE-7834-2008
  could produce destination value: CDE-0361-2008
• TRANS COL ('HASH,LENGTH=13')
  for source value CDE-7834
  could produce destination value: XVT-0361VEH-1
• TRANS COL ('HASH,LENGTH=13')
  for source value Smith, John
  could produce destination value: Kadom, OsnmYf
• TRANS COL ('HASH,LENGTH=13')
  for source value SMITH JOHN
  could produce destination value: KADOM OSNMLQN
• TRANS COL ('HASH,LENGTH=4')
  for source value Smith, John
  could produce destination value: Kado
• TRANS COL ('UNIQUE')
  for source value XYZ 477 6835
  could produce destination value: AEX 889 8450
• TRANS COL ('HASH,TRIM=(e)')
  for source value InfoSphere
  could produce destination value: XeshAnnf
• TRANS COL ('UNIQUE,SEED=RANDOM')
  for source value CDE-7834-2008
  could produce destination value: RDI-9796-7980