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:
- The source value is NULL, and the destination column
does not allow a NULL value.
- The source value is a VARCHAR less than 3 characters
long.
- The source email value does not contain a ‘@'.
- The source value cannot be converted to a format
TRANS EML supports.
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”
')