Using Synonyms for Data Sources and Data Targets

iWay Servers, including the DataMigrator Server, access local and remote data sources and data targets using synonyms, which contain the metadata for a data source. This metadata consists of the attributes that describe the data source or target.

Since DM processing depends upon the appropriate metadata being available to the DataMigrator Server and any remote servers in your configuration, you must create synonyms for all data sources and any existing data targets before you can use them in a flow. For information about providing metadata to servers, see DataMigrator Setup At a Glance and Remote Server Setup At a Glance.

The Data Management Console application directory tree lists the synonyms available to the DataMigrator user for data sources and data targets. This structure is also known as the users Application Path on the server.

Reference: General Guidelines for Synonyms

The first character in a synonym name must be a letter. The remaining characters can be any combination of letters, numbers, and underscores (_). The synonym name cannot exceed 64 characters. When naming a synonym, you must avoid the following:
- A name beginning with the letters ETL or SYS. These prefixes are reserved for DataMigrator internal use.
- In a FOCUS/FDS or XFOCUS data source, a non-unique field name.
- Names that are used as commands within DataMigrator. These are HOLD, SQLIN, SQLGET, and MODIFY.
- Names containing spaces, dashes, and other non-standard characters in both the source and target data columns (*, &, #, $, %, @).
Synonyms must not exceed any of the following:
- Approximately 3,000 columns.
- 30K of total column name character length.
- 16K for single LRECL (total length of field formats, one instance of a row).
- Column names cannot exceed 64 characters.
See the Adapter Administration for UNIX, Windows, OpenVMS, IBM i, and z/OS manual for suggestions on how to access your structures without pressing these limits.
For column names, avoid the following:
- Special characters. Only alphanumeric characters and underscores are permitted in column names.
- Names beginning with the capital letter E followed by a two or three digit number, for example, E01 - E999.
- Reserved words, including:
  ALIAS, ALL, ALTER, AND, ANY, AS, ASC, AUTHORIZATION, AVG, BEGIN, BETWEEN, BINARY, BIT, BOTH, BY, CALL, CASE, CAST, CHAR, CHARACTER, CHECK, CLOSE, COALESCE, COMMIT, CONNECT, CORRESPONDING, COUNT, CREATE, CROSS, CURRENT, CURSOR, DATE, DEALLOCATE, DEC, DECIMAL, DECLARE, DEFAULT, DELETE, DESC, DISTINCT, DO, DOUBLE, DROP, ELSE, ELSEIF, END, ESCAPE, EXCEPT, EXECUTE, EXISTS, FETCH, FLOAT, FOR, FOREIGN, FROM, FULL, FUNCTION, GET, GRANT, GROUP, HAVING, IF, IN, INDEX, *INDICATOR, INNER, INOUT, INSERT, INT, INTEGER, INTERSECT, INTO, IS, ITERATE, JOIN, KEY, LEADING, LEAVE, LEFT, LIKE, LOOP, MAX, MIN, NATURAL, NOT, NULL, NULLIF, NUMERIC, OF, ON, ONLY, OPEN, OPTION, OR, ORDER, OUT, OUTER, PRECISION, PREPARE, PRIMARY, PROCEDURE, REAL, REFERENCES, REPEAT, RETURN, RETURNS, REVOKE, RIGHT, ROLLBACK, ROW, ROWS, SCHEMA, SELECT, SET, SMALLINT, SOME, SUM, TABLE, THEN, TIME, TIMESTAMP, TO, TRAILING, UNION, UNIQUE, UNTIL, UPDATE, USER, USING, VALUES, VARCHAR, VARYING, VIEW, WHEN, WHERE, WHILE, WITH, WORK, END, COLFETCH, CONCAT, DATABASE, DATETIME, DAY, DAYS, EXPLAIN, GRAPHIC, HOUR, HOURS, IMAGE, INCLUDE, INDEX, LOCK, LOGICAL, LONG, MICROSECOND, MICROSECONDS, MILLISECOND, MILLISECONDS, MINUTE, MINUTES, MONEY, MONTH, MONTHS, NUMBER, OPTIMIZE, PACKAGE, PERCENT, PLAN, PROGRAM, PURGE, QUERYNO, RAW, RESET, SECOND, SECONDS, SERIAL, SMALLFLOAT, STOGROUP, SYNONYM, SYSNAME, TABLESPACE, TEXT, TRUNCATE, USER_TYPE_NAME, VARBINARY, VARGRAPHIC, YEAR, YEARS
- Synonyms created using the Dynamic Columns option (NOCOLS parameter) should not be used with DataMigrator because their columns and formats will not display when building flows in the DMC.

Reference: Support for Inclusion of Application Directory Names With Flow Components

As of Version 5.3, DataMigrator Server supports inclusion of application directory names in flow component labels. This means that data and process flows can store information about the application directories where the synonyms or procedures that they use are located.

Although Version 5.2 of ETL Manager used application directories to group synonyms and requests (flows) into logical groups, it did not provide support for duplicate or multiple synonyms or requests with the same name, even if they were in different application directories. While requests could not be saved with the same name, they could be copied to different directories on the server.

For example, if you had two synonyms or requests with the same name and your application path was

ibisamp
demo
baseapp

it did not matter which synonym or request you selected, you would get the one from the application directory that appeared first in the application path, since the earlier version of the server did not support inclusion of the application directory. If you had synonyms or requests with the same name in more than one application directory, there was no way to distinguish them, and the server would always use the one in the first application directory in the application path. Some users take advantage of this capability by constructing a run-time application path to control which synonym or flow is actually used.

For upward compatibility, when you build data flows, by default, they will not include application directory names. That means that the flow will not save the application directory name with the source or existing target. If you use a synonym that exists in more than one application directory, you will get the following warning:

Duplicate Synonym Warning

The flow will pick up the source from the first application directory in the application path.

For existing targets, unless you specify the use of application directories, the flow will not save the application directory name, and when the flow executes it will pick up the target synonym from the first application directory in the application path.

While previous releases created the synonyms for new targets only in the baseapp application directory, new targets now default to include application directories, and the synonym for the new target table will be created in the specified application directory.

To include application directory names in data flows, select Tools and then Options. Select the Use application directory name with flow components check box from the Data Flow Designer page. This way your flow will use exactly the synonym you specify.

Data Flow Designer Options

For process flows, the default is to include application directory names.

Reference: Guidelines for Variable Length Character and Text Columns in Synonyms

If you are using variable length character or text columns in your synonyms, you should be aware of the following special considerations.

Variable Length Character Columns

DataMigrator supports variable length character columns that are described in a relational database as VARCHAR. This attribute enables you to copy such columns without padding them with spaces to the full column width or removing trailing spaces. It enables you, for example, to copy a column containing a single space.

This support is enabled by default.

When new synonyms are created, they are created with variable length columns described as

AnV

where:

n: Is the maximum column width.

Note: For flat file data targets, the length of the value precedes the data value itself.

TEXT (TX) Columns

DataMigrator also supports columns described with ACTUAL=TX for Text columns. This includes data types MS SQL Server VARCHAR(MAX) and Text, ORACLE LONG, and DB2 LONG VARCHAR. Sample Data displays only the first 50 characters of these columns.

Note: Columns described as TX cannot be used as a key column.