TRANS COL Function
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 }
[,source=["]colname["] ]
[,copy=(start,len [, "lit" ] )... ]
[,seed= {"lit" | var (variable )| RANDOM} ]
[,length={n | max }]
[,preserve=( [null] [spaces] [zero_len] ) ]
[,TRIM=(char1[charn...] [\u] [\r] ) ]
[,num]
' )
- unique
- Generate a unique destination value. The length of the destination value will be the same as the source value length.
- hash
- 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.
- source=colname
- 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.
- copy=
- 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.
- seed=
- 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.
- length={n | max }
- 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=.
- preserve=
- List one or more source values that should not be replaced at the destination. The values
can be separated by a comma or a space; 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.
- TRIM=(char1 [ charn...] [\u] [\r] )
- 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 operand 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.
- num
- 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=.
Note: The parameter names are case insensitive and can therefore be entered in
either lower or upper case.
Examples:
Source Data | TRANS COL function | Destination Data |
---|---|---|
CDE-7834 | TRANS COL ('UNIQUE') | XVT-0361 |
CDE-7834-2008 | TRANS COL ('UNIQUE') | XVT-0361-1123 |
Smith, John | TRANS COL ('UNIQUE') | Kadom, Osnm |
SMITH JOHN | TRANS COL ('UNIQUE') | KADOM OSNM |
CDE-7834-2008 | TRANS COL ('UNIQUE,COPY=(1,3)(10,4),preserve=(spaces null)') | CDE-0361-2008 |
CDE-7834 | TRANS COL ('HASH,LENGTH=13') | XVT-0361VEH-1 |
Smith, John | TRANS COL ('HASH,LENGTH=13') | Kadom, OsnmYf |
SMITH JOHN | TRANS COL ('HASH,LENGTH=13') | KADOM OSNMLQN |
Smith, John | TRANS COL ('HASH,LENGTH=4') | Kado |
XYZ 477 6835 | TRANS COL ('UNIQUE') | AEX 889 8450 |
InfoSphere | TRANS COL ('HASH,TRIM=(e)') | XeshAnnf |
CDE-7834-2008 | TRANS COL ('UNIQUE,SEED=RANDOM') | RDI-9796-7980 |