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. 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,...])
- The specified source column character or characters are not masked
and not written to the destination. For example, if you specify TRIM=(x,y,z,1,2,3),
if any of the characters x, y, z, 1, 2, 3 appear anywhere in the source
column, they will not be masked or written to the destination.
- [\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=(x,y \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=(x,y
\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=.
Examples:
- TRANS COL UNIQUE for source value CDE-7834, could produce
a destination value of: ZWQ-4598
- TRANS COL UNIQUE SEED=RANDOM for source value CDE-7834-2008 could
produce a destination value of: SWX-3162-8451
- TRANS COL UNIQUE for source value Smith, John could produce
a destination value of: Fnxwq, Lrzp
- TRANS COL UNIQUE for source value SMITH JOHN could produce
a destination value of: FNXWQ LRZP
- TRANS COL UNIQUE (COPY=((1,3)(10,4))) for source value CDE-7834-2008 could
produce a destination value of: CDE-4032-2008
- TRANS COL HASH (LENGTH=13) for source value CDE-7834 could
produce a destination value of: ZWQ-4598RN7A0
- TRANS COL UNIQUE PRESERVE=(spaces) for source value XYZ 477
6835 could produce a destination value of: LMN 623 0972
- TRANS COL HASH TRIM=(e \u) for source value InfoSphere could
produce a destination value of: RBIWACRL