Using Synonyms for Data Sources and Data Targets

• 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:
  • Names containing capital letters.
  • 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 128 characters.

  See the Adapter Administration 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.

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.

Prior versions of DataMigrator used application directories to group synonyms and requests (flows) into logical groups, and by default did not provide support for duplicate or multiple synonyms or requests with the same name, even if they were in different application directories.

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

ibisamp
demo
baseapp

it would 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 would be 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 a warning, which is shown in the following image.

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

To include application directory names in data flows, on the Home tab, in the Tools groups, click Options. Click Data Flow and 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.

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

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

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.