TRANS SSN Function

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:

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')