TRANS EML Function

Use the TRANS EML function to generate an email address. An email address consists of two parts, a user name followed by a domain name, separated by ‘@'. For example, user@domain.com.

TRANS EML generates an email address with a user name based on either destination data or a literal concatenated with a sequential number. The domain name can be based on an email address in the source data, a literal, or randomly selected from a list of large email service providers. The email address can also be converted to upper or lower case.

TRANS EML can generate a user name based on the values in one or two destination table columns (usually containing the name of a user). Processing options allow you to use only the first character of the value in the first column (for example, the initial letter of a first name) and separate the values from both columns using either a period or an underscore.

If the user name is based on a single destination column value or a literal, the name will be concatenated with a sequential number. If a user name is based on values in two destination table columns and a separating period or underscore is not used, the values are concatenated. If a parameter is not provided for the user name, the name will be formed by the literal “email” concatenated with a sequential number. Sequential numbers for user names are suffixes that begin with 1 and are incremented by 1.

The syntax of TRANS EML is:

TRANS EML [( ‘[=flags] , [{sourcecol | “domain” | , }
[{name1col[name2col] | “userpfx”}] ] [preserve=invalid]' )]
flags
You can specify one or more case-insensitive processing option flags.
n
Generate a random domain name from a list of large email service providers.
.
Separate the name1col and name2col values with a period.
_
Separate the name1col and name2col values with an underscore.
i
Use only the first character of the name1col value.
l
Convert the email address to lower case.
u
Convert the email address to uppercase.
sourcecol
The source column name with email addresses used to provide the domain name.

If neither the ‘n' flag nor the domain parameter are defined, the domain name in the source column is used. (If sourcecol is not defined, the source column name is based on the destination column name.)

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.

domain
A literal, up to 31 characters, that forms the domain name.
,
A comma is required if neither a sourcecol nor a domain parameter is defined and you define either a literal or column name(s) for the domain name.
name1col
A destination table column name with values used to form the first (or only) part of the user name.
name2col
A destination table column name with values used to form the second part of the user name.
userpfx
A literal, up to 31 characters, that is concatenated with a sequential number to form the user name.
preserve=invalid
If the source column contains an invalid email address, 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 column length must be from 3 to 256 characters.
VARCHAR
The column length must be from 3 to 254 characters.

If a source or destination column does not adhere to these restrictions, an error message will be issued.

Destination Processing Rules

The following rules apply to the destination email value, according to the destination data type or value:

CHAR
If the source value is spaces or a zero-length VARCHAR, the destination value will be set to spaces.
VARCHAR
If the source value is spaces or a zero-length VARCHAR, the destination length will be 0.
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:

EML01
Parm on Col ccccc ("ppp") is invalid
Explanation
The indicated 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, . , _ , i, l, u).
EML02
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.
EML03
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.
EML04
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.
EML05
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.
EML08
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.
EML09
Domain literal sssss too long
Explanation
The string specified as the domain name literal (domain) exceeds the maximum limit of 31 characters.
User Action
Specify a domain name consisting of 31 characters or fewer.
EML10
User literal sssss too long
Explanation
The string specified as the user name literal (userpfx) exceeds the maximum limit of 31 characters.
User Action
Specify a user name consisting of 31 characters or fewer.
EML11
Name1 Col ccccc not on dest
Explanation
To perform the indicated TRANS function, a name1 column must be specified on the destination table.
User Action
Verify that the specified name1 column matches the name1 column indicated in the TRANS function.

The name1col column name was not found on the destination table.

EML12
Name1 Col ccccc-aaa invalid
Explanation
To perform the indicated TRANS function, the name1 column specified must be in a valid format.
User Action
Verify that the name1 column availability, type, and length are appropriate.
EML13
Name2 Col ccccc not on dest
Explanation
To perform the indicated TRANS function, a name2 column must be specified on the destination table.
User Action
Verify that the specified name2 column matches the name2 column indicated in the TRANS function.

The name1col column name was not found on the destination table.

EML14
Name2 Col ccccc-aaa invalid
Explanation
To perform the indicated TRANS function, the name2 column specified must be in a valid format.
User Action
Verify that the name2 column availability, type, and length are appropriate.

If any other errors occur, contact Technical Support.

Example 1

The following example uses a literal (optim.com) to form the domain name and two destination table columns (NAME_FIRST and NAME_LAST) to form a user name that includes an underscore:

TRANS EML (‘=_ “optim.com” NAME_FIRST NAME_LAST')

Example 2

The following example uses a domain name from the source column and a literal (OptimUser) to form a user name that will be suffixed with a sequential number:

TRANS EML (‘, “OptimUser” ')