DataMigrator Parameters

How to:

The following are some of the parameters that affect DataMigrator:

MSG={ON|OFF}

Turns on iWay messaging for the log.

JOINTYPE={SORTMERGE|NESTEDLOOP}

Affects SORTMERGE or NESTEDLOOP logic.

JOINOPT={NEW|OLD|GNTINT}

Affects how SQL is handed to remote servers.

CDN={ON|OFF}

Turns on continental decimal notation for flat file copies.

MISSINGTEST={SPECIAL|OLD}

Controls whether NULL values in a comparison affect the nullability of the result.


Top of page

x
Syntax: How to Set Parameters in a Profile or Procedure
SET parameter = option

where:

parameter

Is the setting you wish to change.

option

Is a valid value for the parameter.



Example: Setting a Parameter in a Profile or Procedure

The following sets the CDN SET parameter to ON.

SET CDN = ON

Top of page

x
Syntax: How to Set Target Columns as NULLABLE

When new target tables are created, by default, the resulting target columns inherit the NULLABLE setting of their corresponding source columns.

To set the nullability of target columns, use the following syntax:

SET TARGET_ATTR=ON|PROPAGATE

where:

ON

Makes all non-key target columns NULLABLE (MISSING=ON).

PROPAGATE

All non-key target columns inherit the nullability of their source columns. This is the default.

This setting can be used in a profile, where it will affect all new target tables.


Top of page

x
Syntax: How to Limit the Number of DBMS Errors That Appear in the Log Table

When DataMigrator attempts to insert rows into a target relational database table, they may be rejected due to a unique index, not null, or other constraint violations. When rows are rejected, the error messages are written to the Log Table. This can fill up the log, and it may not be necessary to see the message for every rejected row to determine the problem.

The maximum number of errors written to the log can be restricted with the following command:

SET DBMSMSGLIMIT=nnnn

where

nnnn

Is the maximum number of errors.

Note: The processing of the job continues, although further errors are not logged.

This setting can be used in a profile, where it will affect all new target tables, or in a stored procedure that is executed before the data flow loads the target table.


Top of page

x
Syntax: How to Set the Source for Oracle Timestamps

The DATETIME_PROCESS setting allows you to specify the source for the current date and time in SQL SELECT statements from Oracle tables using CURRENT_TIMESTAMP(0).

The syntax is:

SQL SET DATETIME_PROCESS=SERVER|DBMS
END

where:

SERVER

Obtains the current date and time from the iWay server. This is the default.

DBMS

Obtains the current date and time information from the relational database. This is only effective for queries that can use automatic pass through and only those that use the function CURRENT_TIMESTAMP(0).

This syntax should be used even if it is included in a profile, which affects all queries run on the server.

If the query cannot use APT (for example, if the source is a flat file) the following message is generated:

(FOC14070) Query cannot go APT to evaluate CURRENT_TIMESTAMP

Top of page

x
Procedure: How to Use Core Engine Settings

Selected server settings can be set from the Data Management Console Workspace Configuration.

  1. In the DMC, using an Administrator user ID, right-click Workspace and click Settings.
  2. Click Core Engine Settings.

    The Change Core Engine Settings dialog box opens, as shown in the following image.

    Core Engine Setting dialog box

  3. Select edasprof from the Select profile drop-down menu to make changes for all users. You can also select a single user ID.
  4. Enter or select new values for the rest of the applicable fields.
  5. Click Save.

iWay Software