iWay DataMigrator User's Guide

DataMigrator

Relational and Non-relational Lookups*

The DB_LOOKUP function looks up values in a table and returns the value from another column of the row where the first value was found.

Using Functions

Parameters in Stored Procedures*

Parameters for stored procedures can be set from the DMC.

How to Use a User-defined Variable at Run Time and Grouping and Controlling Simultaneous Execution of Flows

Sending the Log When a Flow Completes Successfully*

E-mail notification now includes the log file as an attachment and is available at both Flow End and Failure of Flow. This option is set from the E-Mail Notification pane of the Flow Properties dialog box.

In previous releases, the log was embedded in the e-mail message and available for failed flows only.

Flow Properties Window - E-Mail Notification Page

Support for BLOB Format*

BLOB represents a Binary Large Object. A BLOB is used to store an image, sound or some other large, unstructured block of data. The corresponding database data types are:

• ORACLE - BLOB
• MS SQL Server - Image
• Informix - Byte
• DB2 - BLOB

Column Formats and Mapping

Changing the Schedule of Flows from the Web Console*

You can now change the schedule of flows from the Web Console.

How to Schedule Flows in the Web Console

Making Schedules Inactive from the Schedule Pane or Web Console*

You can stop a scheduled flow without removing the Schedule object.

How to Disable a Schedule Object

Passing Parameters to All Data Flows in a Parallel Group*

You can pass parameters to all the procedures in a parallel group using the flow's Properties dialog box.

Grouping and Controlling Simultaneous Execution of Flows

Support for Mixed Case in Column Names*

You can use mixed case in column names.

General Guidelines for Synonyms

Specifying a Delimiter for Bulk Loading a Sybase IQ Target*

The bulk load options for Sybase IQ targets allow you to specify the delimiter to use for the intermediate file.

Sybase IQ Target Bulk Load Options

Bulk Loading MySQL Targets via JDBC*

Bulk load is now supported for MYSQL targets.

Load Options

Extending Teradata Bulk Load Support with Upsert*

Bulk load options for Teradata targets now include Upsert.

Teradata Target Bulk Load Options

Predefined XML File Targets*

Predefined XML files can now be used as targets, and you can map to them as with a database.

Note: Statistics are not available for XML files described by multi-segment synonyms.

Creating a Data Flow Using a Target Based on a Pre-defined XML Schema

and

Data Target Types

Support for New Delimited Flat File Targets*

Delimited flat files can now be used as data targets.

Data Target Types

and

Target Properties Window for Delimited Flat File Targets

Specifying a Code Page for New Flat File Targets*

You can now set a codepage for a new flat file targets.

Target Properties Window for Flat File Targets

Creating a Synonym for a Relational Stored Procedure

If you create a synonym for a relational stored procedure, you can use that stored procedure as a data source in a flow. When you create the synonym, you'll be prompted for values for the procedure's parameters.

How to Create a Synonym for a Relational Stored Procedure

Using a Relational Stored Procedure as a Data Source

You can use a relational stored procedure as a data source in a flow if you've created a synonym for that stored procedure. This is supported for:

• DB2 (via CLI)
• Oracle (via CLI)
• SQL Server (via OLE DB and JDBC)
• Teradata (via ODBC)
• Sybase IQ (via ODBC)

How to Use a Relational Stored Procedure as a Data Source

DBMS SQL Flows

A DBMS SQL flow allows you to enter dialect-specific SQL SELECT statements for extracting data from a relational database. It only contains a specialized SQL object, and one or more target objects.

Creating a DBMS SQL Flow

Direct Load Flows

A Direct Load flow is a simplified flow that uses a single flat file source. It loads data directly from the flat file into an existing target, without the need to create an intermediate file.

Creating a Direct Load Flow

Real Time Flows

Using the server's file listener, you can launch a Direct Load flow in real time. A real time data flow is executed when a source file appears in a directory specified to the listener.

Creating a Real Time Process Flow

Support for DISTINCT

A Distinct option has been added to the Column Selection dialog box which removes duplicate rows from the results of your query. There are also three new aggregation options: Sum Distinct, Count Distinct, and Avg Distinct.

Columns Selection Window

and

How to Aggregate a Column

Data Management Console

Displaying Synonym and Column Lists in a Grid or Tree View*

Columns can be displayed in trees or grids using their Name, Title, or Description. You can also change the way columns, variables and functions display.

Working With Columns, Variables, and Functions

Columns/Variables Tab*

The columns and variables tabs have been combined. The tab displays a hierarchical tree of available columns and the DM Variables, System Variables, and Constants folders, or a list of available columns, variables, and constants.

Transformation Calculator,Join Calculator, SQL Calculator, and Filter Calculator.

Function Assist*

When you use a function in one of the calculators, the Assist dialog box provides the syntax and helps you set the parameters.

How to Use Function Assist

Relational Expression Builder

The Relational Expression tab of the Transformation Calculator allows you to build an expression that can be used as a filter.

How to Create a Relational Expression in the Transformation Calculator

Automatic Calculation of Formats in Transformations

If you add a transformation or a virtual column without supplying a format, the DMC will automatically provide the format based on the expression. Similarly, if you supply the wrong format, the DMC will replace it with the correct format.

How to Create a Source Transformation in a Data Source Object and

Unicode Support*

Unicode support has been added to the DMC. You can connect to different servers and see data in different languages.

Shortcut Keys for Navigating the Workspace*

Shortcut keys enable you to move around the DMC interface using key strokes instead of a mouse.

Shortcut Keys for Navigating the Workspace

Source Management Support for CVS*

CVS (Concurrent Versions System) is now supported to provide source management for DataMigrator files through the DMC.

Source Management

Filtering the Application Directories

You can limit the procedures that appear in the Procedures Tab's application directory tree or the synonyms that appear in the Metadata Tab's application directory tree by applying a filter. The filter can be based on the procedure's name or description or the synonym's name, adapter, or remarks. You can also apply a filter from the Web Console.

How to Filter Application Directories in the Metadata Tree

and

How to Filter Application Directories in the Procedures Tree in the Data Management Console

Data Flow Designer

Slowly Changing Dimensions

Slowly Changing Dimension processing is used to load Dimension tables with values that change over time. Each column of the target table is assigned an SCD type. The parameters for Slowly Changing Dimensions are set through the Column/Field Attributes available in the Synonym Editor.

Target Properties Window for Relational Targets

Find for Synonyms and Columns

A find function is available from the Select Synonym, Join Properties, Transformation, and Select Column dialog boxes, as well as from their calculators. You can search for synonyms or columns by name or description.

How to Search for Existing Synonyms to Use as Sources or Targets

Adjusting Expressions

The Transformation window provides a option for automatically adjusting transformations to reconcile different format sizes.

How to Adjust a Transformation Mapping

Reports

Summary Flow Reports*

You can run a Summary Flow Report that displays basic information on all the flows on the server or in a specific application directory.

How to Run a Summary Flow Report

Viewing and Printing Detailed Flow Reports*

Detailed reports for all the flows on the server or in a specific application directory are available for viewing and printing from the Web Console.

How to View and Print a Set of Detailed Flow Reports From the Web Console

Data Profiling reports*

Data Profiling provides the data characteristics for a synonym's columns.

For alphanumeric columns, Data Profiling provides the segment, format, count of distinct values, total count, maximum, minimum, and average length, and number of nulls.

For numeric columns, Data Profiling provides the segment, format, count of distinct values, total count, maximum, minimum, and average value, and number of nulls.

There are also a number of different reports available for individual columns: Statistics shows the column’s distinct values and their counts.

• Statistics shows the column’s segment, format, count of distinct values, total count, maximum, minimum, and average length or value, and number of nulls.
• Patterns, which is only available for alphanumeric columns, shows patterns of letters, digits, and special characters, as well as counts and percentages.
• Values shows unique values and percentages.
• Outliers shows the ten highest and lowest unique values and their percentages.

Viewing Data Profiling Characteristics

Printing Graphical Data/Process Flow and Model View*

You can print a graphical representation of an open flow from the print icon on the toolbar or from the Print option on the File menu.

Printing a Graphical Representation of a Data or Process Flow

Data Lineage

Data lineage provides the ability to see how a column was derived. It traces the column back to the data source(s) it came from, breaking down all of its components along the way.

To see a column's lineage, open a synonym in the Synonym Editor, right-click a column, and select Data lineage. It could show, for example, that a target column is the result of a SQL calculation that uses source columns from other synonyms.

This feature is a Candidate for Release.

Viewing Data Lineage for a Column

Target-specific Statistics

Statistics are generated for each target in a multi-target flow.

Statistics Report Columns

Log and Statistics Support for 64-character Flow Names

Log and Statistics reports support flow names of up to 64 characters.

Log Report Columns

and

Statistics Report Columns

THIS TYPEFACE

or

this typeface

Denotes syntax that you must enter exactly as shown.

this typeface

Represents a placeholder (or variable) in syntax for a value that you or the system must supply.

underscore

Indicates a default setting.

this typeface

Represents a placeholder (or variable), a cross-reference, or an important term. It may also indicate a button, menu item, or dialog box option you can click or select.

this typeface

Highlights a file name or command.

Key + Key

Indicates keys that you must press simultaneously.

{  }

Indicates two or three choices; type one of them, not the braces.

[  ]

Indicates a group of optional parameters. None are required, but you may select one of them. Type only the parameter in the brackets, not the brackets.

|

Separates mutually exclusive choices in syntax. Type one of them, not the symbol.

...

Indicates that you can enter a parameter multiple times. Type only the parameter, not the ellipsis points (...).

.
.
.

Indicates that there are (or could be) intervening or additional commands.