Use the TRANS SSN function to generate a valid and unique
U.S. Social Security Number (SSN). By default, TRANS SSN algorithmically
generates a consistently altered destination SSN based on the source
SSN. TRANS SSN can also generate a random SSN when the source data
does not have an SSN value or when there is no need for transforming
the source SSN in a consistent manner.
An SSN is made of 3 subfields. The first
3 digits (area) represent an area generally determined by the state
in which the SSN is issued. The next 2 digits (group) define a group
number corresponding to the area number. The last 4 digits (serial)
are a sequential serial number. Regardless of the type of processing,
default or random, TRANS SSN will generate an SSN with a group number
appropriate to the area number.
The default processing
method generates an SSN that includes the source area number as well
as altered group and serial numbers based on the source SSN.
The random processing method generates an SSN that
can include the source area number and uses a group number most recently
issued by the Social Security Administration for the destination area
number. Serial numbers begin with 0001 and are incremented by 1 for
each additional SSN generated for the area number. When the serial
number exceeds 9999, the serial number will be reset to 0001 and the
group number preceding the number most recently issued for the area
number will be used.
The syntax of TRANS SSN
is:
TRANS SSN [( ‘[=flags] [sourcecol [preserve=invalid]' )]
- flags
- You can specify one or more case-insensitive processing
option flags.
- n
- Generate a random SSN that is not based on a source
value.
- m
- Use the maximum group of all SSN area values, including values
from 773 through 899, and excluding invalid area numbers.
- r
- Generate a random SSN that includes the source
area number.
- v
- Validate the source group number by comparing it
with numbers used by the Social Security Administration.
- -
- The destination SSN should include dashes separating
the fields (for example, 123-45-6789). Requires a character-type destination
column at least 11 characters long.
- sourcecol
- The source column name. If a source column name
is not specified, the destination column name will be used. If a source
column name is not specified and the destination column name does
not match a column name in the source table, an error will occur during
processing.
- preserve=invalid
- If the source column contains an invalid SSN, do not replace it
with a generated value. The source column value will be used in the
destination column.
Data Types Allowed
The following source and destination data types are
permitted:
- CHAR
- The length of data in the column must be from 9
to 256 characters.
- DECIMAL
- The precision of the column must be 9 - 20 and
the scale 0.
- INTEGER
- No restrictions.
- VARCHAR
- The length of data in the column must be from 9
to 254 characters.
If a source or destination column
does not adhere to these restrictions, an error will occur during
processing.
Destination Processing Rules
The following rules apply to the destination SSN value,
according to the destination data type or value:
- CHAR
- If the source value is 0, spaces, or a zero-length
VARCHAR, the destination value will be set to spaces.
If
a source value is 11 characters or more and includes embedded dashes
(-), or if the ‘-' flag is specified, the destination value will include
dashes if the destination column length is 11 characters or more.
- DECIMAL,
INTEGER
- If the source value is 0, spaces, or a zero-length
VARCHAR, the destination value will be 0.
- VARCHAR
- If the source value is 0, spaces, or a zero-length
VARCHAR, the destination length will be 0.
If a
source value is 11 characters or more and includes embedded dashes
(-), or if the ‘-' flag is specified, the destination value will include
dashes if the destination column length is 11 characters or more.
- NULL
- If the source value is NULL, the destination value
will be NULL.
Skipped Rows
The
following conditions may cause a source row to be skipped and not
written to the destination:
- The source value is NULL, and the destination column
does not allow a NULL value.
- The source column is CHAR or VARCHAR, and the source
value is less than 9 characters, contains a non-numeric character
(other than dashes between the 3 subfields), or is too large.
- The source area number has not been used by the
Social Security Administration.
- The source group number has not been used with
the area number by the Social Security Administration (only if the
‘v' flag has been specified).
- The source serial number is 0000, or the SSN is
a reserved value not issued by the Social Security Administration
(for example, 078-05-1120).
- The source value cannot be converted to a format
TRANS SSN supports.
Error Messages
The
following error messages may be issued:
- SSN01
- Parm on Col ccccc ("ppp") is invalid
- Explanation
- The column contains a TRANS function with a processing option
flag that is not valid.
- User Action
- Ensure that the TRANS function on the column specified uses a
valid processing option flag (n, r, v, -).
- SSN02
- Col ccccc not on source
- Explanation
- The column that was entered as a sourcecol parameter or the destination
column name (if the sourcecol parameter was omitted) was not found
on the source table.
- User Action
- Check the source table and resolve any discrepancies or missing
columns.
- SSN03
- Source Col ccccc-aaa invalid
- Explanation
- The format of the source column is not supported because the attribute
indicated is not valid.
- User action
- Check the source column and ensure the values for type, length,
precision, and scale are appropriate.
- SSN04
- Dest Col ccccc-aaa invalid
- Explanation
- The format of the destination column is not supported because
the indicated attribute is not valid.
- User Action
- Check the destination column to ensure the values for type, length,
precision, and scale are appropriate.
- SSN05
- Get col ccccc data-rc=nnn
- Explanation
- An unexpected internal error has occurred while getting the value
from the source column.
- User Action
- Check the values of the source and destination columns and ensure
the values for type, length, precision, and scale are appropriate.
If the problem persists, contact IBM® Software
Support.
- SSN08
- Put col ccccc data-rc=nnn
- Explanation
- An unexpected internal error has occurred while setting the value
on the destination column.
- User Action
- Check the values of the source and destination columns and ensure
the values for type, length, precision, and scale are appropriate.
If the problem persists, contact IBM Software
Support.
If any other errors occur, contact
Technical Support.
Example 1
The
following example uses a source column name that matches the destination
column and generates a random SSN that is not based on the source
value:
TRANS SSN (‘=n')
Example 2
The
following example uses a source column name (NATIONAL_ID) that differs
from the destination column and generates an SSN using the default
processing method and including dashes:
TRANS
SSN (‘=- NATIONAL_ID')