Symbiator 4 Online Help

Online Help
Vision Solutions home page


New in this version

Symbiator 4.1 includes a number of new features, functions, and enhancements:

  • Enhancements to the Manager
    At a glance the Manager provides even more pertinent information regarding your system through the addition of the Mapping Details, Mirror Tables, and Table Aliases tabs.
  • Total re-design of the interface for creating, viewing, and managing your Mappings
    This re-design incorporates the consolidation of a number of existing features and functions as well as the addition of new functionality, like the Data Capture tab, into one easily accessible and intuitive window that enables the creation and management of your mappings with unparalleled agility.
  • Enhancements to user defined padded concatenated fields
    This feature, first introduced in Symbiator 4.0, enabled you to add a single leading character of your choice to a concatenated field, now has been greatly enhanced to allow left or right side padding, customized pad strings, the repeating of pad characters, filling the data with a pre-defined number of characters, and the ability to trim concatenated fields.
  • Consolidation of Journal Management features
    The journaling features and functionality have been consolidated and streamlined within the Journal Management window to provide the wide range of journal tools with fewer mouse clicks.
  • Indexed Management
    • Set Key Field on the Field Mappings tab looks at the unique indices on the source table and recommends the Symbiator link fields.
    • Optimize Replication looks at the Symbiator link fields and recommends an index for the destination table.
    • Creating a new destination table enables you to automatically apply any indexes in the source to the new table.
  • User Defined Transformation
    This feature provides you with the flexibility to utilize the full set of SQL functionality available on a destination database.
  • Transaction Handling
    This feature introduces a new set of functionality to provide far greater control in the way transactions and errors are handled.
  • Connection Handling
    This functionality enables you to define error handling in order to reestablish lost connections.


Introducing Symbiator

With Symbiator you can distribute critical data on a real-time, or refresh on demand, basis between most ODBC compatible relational database management systems (RDBMS). Using column to column mappings you define between your source and destination database systems, Symbiator detects changes and then replicates the data to the desired database.

Supported databases

Symbiator supports data replication between any or all of the following ODBC compatible databases:

  • IBM DB2/400 V5R1, V5R2
  • IBM DB2 UDB 7.2 or higher (source), 7.1 or higher (target)
  • Microsoft SQL Server 7.0, 2000 or higher
  • Oracle 8i, 9i or higher
  • Sybase Adaptive Server Enterprise 12.0 or higher


The advantages of Symbiator

With Symbiator you have tremendous flexibility to integrate and synchronize data between multiple databases. You can replicate data from your high-volume production environment to another database server for user queries, reporting, and analysis.

This provides for highly available, enterprise wide data access. Symbiator can streamline database maintenance for remote or local processing and provide timely data for decision support processing considerations.

Using Symbiator you can replicate data from one source to one destination, one source to multiple destinations, or multiple sources to multiple destinations. While the Symbiator application requires a Windows 2000 Server or Professional/XP Server or Professional, the source and destination databases can run on any of the following platforms:

  • Intel
  • Alpha
  • iSeries (AS/400)
  • MIPS
  • Power PC
  • HP9000
  • pSeries
  • SunSolaris

What happens if a system or a network goes down?

If one of your database servers or a portion of your network goes down, Symbiator continues to update all other online databases with no slowdown in data replication. Symbiator does not have to be halted for the databases remaining online. Symbiator will continue to queue changes for the unavailable database and will resume replication of the queued transactions once this database is available.


Symbiator components

Symbiator includes the following components:

  • SymService—a Windows 2000/XP Service that performs the replication processing.
  • Symbiator Manager—the interface with which you define and manage Symbiator sources, destinations, mappings, and other replication tasks.
  • Symbiator Mass Transfer (SMT)—executes the bulk transfer of data between a source and a destination database on demand.
  • Symbiator Service Manager—allows you to start and stop SymService on any SymService host system on your network.
  • Symbiator Console—allows you to monitor transactions and application events as they occur.
  • Symbiator ODBC Drivers—the necessary intermediary between source and destination databases.
  • Source database initialization scripts—SQL scripts that create required tables and additional objects or indexes within the source database environment.
  • iSeries Module—AS/400 programs that execute replication tasks from an iSeries source.
All references to the AS/400 also refer to the new iSeries servers as well.


How Symbiator works

Symbiator replicates data in real time from tables/physical files within a source database to tables/physical files within a destination database.

You create mappings to define the relationship between the source table columns and the destination table columns. Once the mappings are saved, triggers (or journals) on the source table detect changes to the table and log them. SymService polls the log file and transmits the changes to the proper destination.

Symbiator sources (except for iSeries) do not have any additional Symbiator programs or jobs running on them. Rather, replication is driven by triggers applied to the source database by a replication script created after the mappings are saved. These triggers cause the transactions to be captured. The transactions then await SymService for transmission to the destination.

SymService uses System DSNs with the appropriate Symbiator ODBC or Client Access/iSeries Access drivers to connect to each system. Once these connections are made, changes to the source database flow through SymService as SQL statements formed specifically for the destination database type.

The following diagram illustrates the Symbiator process.

The Symbiator process


Using Symbiator Components Overview

Using Symbiator Components

You can access Symbiator tasks through the various components:

  • Symbiator Manager
  • Mapping Configuration


Symbiator Manager

You perform your configuration and replication tasks from Symbiator Manager.

  • Open Symbiator Manager from the Symbiator 4.1 program group.

The main window provides a split-pane: Navigation Pane (tree view) and Content Pane of your replication network.
Hover your mouse over an area of the
image to display a brief description.

Click to view full-size image.

Source/destination representations

The Navigation Pane is a collapsible tree view, with each data replication source listed at the top level, and each associated destination shown beneath that source.

Only sources and destinations can be made active or inactive. Individual databases on a source or destination are reliant on whether or not their system is active or inactive.

The icon to the immediate left of each item indicates whether or not it is active and what type of replication is being used (if active).

Symbiator Manager Mapping icons
Icon | Description
 | Server Alias was not found in local registry. Server was probably defined on a different machine.
 | If Source: All destinations defined to source are set to active. If Destination: Destination server is set to active.
 | Destination set to replicate based on day/time.
 | Source: No active destinations defined to source server. Destination: Destination server is inactive
 | Source Only: Some, but not all destinations are set to active, timed or metered. At least one destination is set as inactive.

Working with the Navigation Pane

Plus and minus icons, shown to the far left of each item, indicate whether or not the display for that level is completely expanded. Click the plus and minus icons (or double-click the item name) to collapse and expand your view, as needed.

The Navigation Pane displays the next level below the highlighted item in the Content Pane. For example, when you highlight a destination table mapping in the Navigation Pane, the column to column mappings display in the Content Pane.

You can resize column displays on the Content Pane by dragging the right side of the column heading to the desired size. You can change the size of the panes themselves by dragging the left side of the Content Pane to resize as desired.

Toolbar

The toolbar at the top of the window (below the menus) includes buttons that enable you to quickly and easily refresh the display, add source and destination servers, and access mapping tasks. If you position the cursor over the button briefly, a tooltip displays the function of each button.

The following summarizes the buttons' descriptions and functions.

Symbiator toolbar
Toolbar Button
Description
Click this to...

Refresh
refresh the screen

Create New Source Server
add a source server definition

Create New Destination Server
add a destination server definition

Create New Mappings
add mappings to the source/destination pair

Server Properties/Modify Mapping...
view or change the attributes of the current selection

Launch SMT
access Symbiator Mass Transfer to move data

Delete...
delete an item selected in the tree view

Start SymService...
start replication on a given connection pair or start a Journal Reader job

Stop SymService...
stop replication on a given connection pair or stop a Journal Reader job

View Errors
view replication errors for a given connection pair

File menu

File Menu Command | Click this to...
New | create a new source, new destination, new mapping or a new table alias (options depend on what you select in the tree view on Symbiator Manager
Delete | delete a selected source, destination, or mapping
Exit | terminate the Symbiator Manager application

Edit menu

Edit Menu Command | Click this to...
Server Properties | display or modify source or destination properties and access the Authorization window to authorize the SymService host and iSeries source systems
Modify Mapping... | display or modify mappings

View menu

View Menu Command | Click this to...
View Errors | display replication errors for a given connection pair
iSeries Jobs | display the iSeries Jobs tab on Symbiator Manager
Mappings View | switch to the Mapping view on Symbiator Manager
Refresh | refresh the information displayed on Symbiator Manager
Status Bar | toggle the Status Bar on and off

Tools menu

Tools Menu Command | Click this to...
iSeries Journal Reader | start or stop the iSeries journal reader job or launch the Symbiator Journal Management interface
Replication Status | set your replication preferences (Never, Always, Daily, etc.) for a particular server
Symbiator Mass Transfer | access the Symbiator Mass Transfer component to populate or refresh data
Authorize SymService on Local Machine | enter an authorization code (temporary or permanent)
Launch Symbiator Console | launch a Symbiator Console window to monitor the replication process
Launch Symbiator Service Manager | launch the Symbiator Service Manager application
Start SymService | start SymService as a server process (Any active source destination pairs will start replication.) Note: If SymService is running the Stop SymService command is displayed.
SymService Messaging... | start or stop sending Transaction Log and/or Activity Monitor messages to the Symbiator Console
Resume Replication | resume replication after an error causes replication to halt
ODBC Administrator | launch the Windows 2000/XP ODBC Administrator to create, delete, or edit system DSNs

Shortcut menus

The shortcut menus are available when you right-click a selected item. Which options are available depends on what item you right-click. Most of the menu items above are available through the shortcut menus.

Vision Solution home page


iSeries Jobs tab

This view gives you a central point to monitor your individual iSeries jobs.

This tab only displays if you have an iSeries source.

Click to view full-size image.
Click to view full-size image.

The iSeries Jobs tab provides the following information:

Heading | Description
Host | machine name with active job
Job | type of job active on host
Start Time | time active job started
Status | current status of job (stopped or running)
Journal | name of journal active
Journal Seq # | last journal sequence number processed

Auto Refresh—Selecting this option enables you to control the amount of time the monitor refreshes.

The maximum amount of time is 300 seconds (five minutes). Setting the option to zero disables the option.


Content Pane tabs

The Content Pane of Symbiator contains three new tabs: Mapping Details, Mirror Tables, and Table Aliases. These provide easy access to pertinent information on a source or destination selected in the Navigation Pane.

Mapping Details tab

The Mapping Details tab enables you to quickly add or drop triggers:

To use the Mapping Details tab to add triggers:

  1. Access the Symbiator Manager.
  2. In the Navigation Pane, click the database containing the mappings you want to add triggers to.
  3. Click the Mapping Details tab on the Symbiator Manager.
    The Mapping Details are displayed.
  4. In the Mapping Details tab, right-click the table you want to add triggers to.
  5. On the shortcut menu that appears, point to Add Triggers, and then click the type of trigger to add: Update Trigger, Insert Trigger, Delete Trigger or All Triggers.

To use the Mapping Details tab to drop triggers:

  1. Access the Symbiator Manager.
  2. In the Navigation Pane, click the database containing the mappings you want to drop triggers from.
  3. Click the Mapping Details tab on the Symbiator Manager.
    The Mapping Details are displayed.
  4. In the Mapping Details tab, right-click the table you want to drop triggers from.
  5. On the shortcut menu that appears, point to Drop Triggers, and then click the type of trigger to drop: Update Trigger, Insert Trigger, Delete Trigger or All Triggers.

Mirror Tables tab

For each mapping you create, Symbiator creates two "mirror tables" for the source table. These mirror tables are used as staging areas for the before and after images of the transactions. When a mapping is deleted the mirror tables remain until they are deleted using the Mirror Tables tab. Also, if the source table is modified (i.e., a field is added, removed or modified), it will be necessary to replace the mirror tables.

Within the grid of the Mirror Tables tab, there will be one row for each table that is or was mapped. The text of each row will be colored based on the recommended action by Symbiator:

BLACK: | Mapping and mirror tables are fine. No action necessary.
RED: | Mapping no longer exists or was deleted. Delete the mirror tables. See: To use the Mirror Tables tab to delete the mirror tables.
BLUE: | Mapping exists but mirror tables are missing. Create the missing mirror tables. See: To use the Mirror Tables tab to replace the mirror tables.

To use the Mirror Tables tab to delete the mirror tables:

  1. Access the Symbiator Manager.
  2. Click the mapping you want to display in the Mirror Tables tab.
  3. Click theMirror Tables tab on the Symbiator Manager.
    The mirror tables are displayed.
    The mirror tables displayed in red denote those mappings that have been deleted, those in black have not been deleted and should not be removed from the Mirror Table tab.
  4. Right-click the mirror table you want to remove and on the shortcut menu that appears, click Delete.
    The Confirm Delete dialog box appears.
  5. Click OK. By dropping the mirror tables, any pending transactions will be deleted.

To use the Mirror Tables tab to replace the mirror tables:

  1. Access the Symbiator Manager.
  2. Click the mapping you want to display in the Mirror Table tab.
  3. Click the Mirror Tables tab on the Symbiator Manager.
    The mirror tables are displayed.
    The mirror tables displayed in red denote those mappings that have been deleted, those in black have not been deleted and should not be removed from the Mirror Table pane.
  4. Right-click the row for the tables you want to replace and on the shortcut menu that appears, click Replace Existing Tables.
    The Confirm Delete dialog box appears.
  5. Click OK. By replacing the mirror tables, any pending transactions will be deleted. Once the tables have been replaced, you should resynchronize your source and destination tables using SMT.

Table Aliases tab

The Table Aliases tab enables you to quickly and easily work with your table aliases from within the Manager. In this tab you can add, rename and delete aliases.

To use the Table Aliases tab to add a table alias:

  1. Access the Symbiator Manager.
  2. Click the Source or Destination server you want to display in the Table Aliases tab.
  3. Click the Table Aliases tab on the Symbiator Manager.
  4. Right-click in the Table Aliases list and click Add Aliases on the shortcut menu that appears.
    The Create Table Aliases dialog box appears.
  5. Select the Library (for an iSeries server).

    —OR—

    Select the Owner and Database (for a non-iSeries server).
  6. In the list that appears click the check box(es) next to the table alias(es) to be created.
  7. Click OK.
    The newly created alias(es) are displayed in the list.

To use the Table Aliases tab to rename a table alias:

  1. Access the Symbiator Manager.
  2. Click the Source or Destination server you want to display in the Table Aliases tab.
  3. Click the Table Aliases tab on the Symbiator Manager.
  4. Right-click the Table Alias you wish to rename and click Rename on the shortcut menu that appears.
    The Rename Alias dialog box appears.
  5. Enter the new alias name desired.
  6. Click OK.

To use the Table Aliases tab to delete a table alias:

  1. Access the Symbiator Manager.
  2. Click the Source or Destination server you want to display in the Table Aliases tab.
  3. Click the Table Aliases tab on the Symbiator Manager.
  4. Right-click the Table Alias you wish to delete tab and click Delete on the shortcut menu that appears.
    The Confirm Delete dialog box appears.
  5. Click OK to delete the Table Alias.
As the confirmation warns, deleting a table alias also invalidates any mappings associated with it resulting in making SymService and SMT inoperable for those mappings.


Filtering

Adding table aliases for an entire database

Symbiator 4.1 enables you to add table aliases for an entire database from the Manager.

To add table aliases:

  1. In the Manager's Navigation Pane select the Source or Destination for which you want to create an alias.
  2. In the Contents Pane, click the Table Aliases tab.
  3. Right-click in the Table Alias list and then click Add Aliases on the shortcut menu that appears.
    The Create Table Aliases dialog box opens.
Click to view full-size image.
Click to view full-size image.

  2. Select the Database.
  3. Select the Owner.
  4. In the list that appears, click each table's check box that you want to create an alias for. To select all of the tables listed, right-click in the list and then click Select All on the shortcut menu that appears.
  5. Click OK.
    The newly created alias(es) are displayed in the list.


Mapping Configuration Overview

Mapping Configuration

Symbiator 4.1 provides an entirely redesigned Mapping Configuration window. The single source in which to manage all of your system's Aliases, Joins, Filters, Field Mappings, and Data Capture.
  • Aliases tab
  • Joins tab
  • Filters tab
  • Field Mappings tab
  • Data Capture tab


Aliases tab

The Aliases tab provides you with the ability to quickly and easily create Source and Destination Table Aliases.

Click to view full-size image.
Click to view full-size image.

Source Table Alias

This group provides the functionality to create source table aliases.

Feature | Function
Library | Provides a list of available libraries that can be selected to create a source table alias.
File | Provides a list of available files that can be selected to create a source table alias.
Member | Provides a list of available members that can be selected to create a source table alias. Note: This functionality is only available with iSeries sources.
Database | Provides a list of available databases that can be selected to create a source table alias (non-iSeries server).
Owner | Provides a list of available owners that can be selected to create a source table alias (non-iSeries server).
Table | Provides a list of available tables that can be selected to create a source table alias (non-iSeries server).
Filter Source Table Alias List | Filters the source table alias list to display only the tables available based on the selections made in the drop-down lists.
Source Table Alias | Displays the name of the source table alias derived from the choices made in the drop-down lists above. Note: The name is editable within this text box.

Destination Table Alias

This group provides the functionality to create destination table aliases.

Feature | Function
Library | Provides a list of available libraries that can be selected to create a destination table alias.
File | Provides a list of available files that can be selected to create a destination table alias.
Member | Provides a list of available members that can be selected to create a destination table alias. Note: This functionality is only available with iSeries sources.
Database | Provides a list of available databases that can be selected to create a destination table alias (non-iSeries server).
Owner | Provides a list of available owners that can be selected to create a destination table alias (non-iSeries server).
Table | Provides a list of available tables that can be selected to create a destination table alias (non-iSeries server).
New Table | Opens the New Table dialog box used to create a new table.
Filter Destination Table Alias List | Filters the destination table alias list to display only the tables available based on the selections made in the drop-down lists.
Destination Table Alias | Displays the name of the destination table alias derived from the choices made in the drop-down lists above. Note: The name is editable within this text box.

Vision Solution home page


Joins tab

The Joins tab provides you with the ability to create and manage all of your table joins in one place.

Click to view full-size image.
Click to view full-size image.

Feature | Function
Filter Join Table Alias List | Filters the list to display only the tables available based on the selections made in the drop-down lists.
Library | Provides a list of libraries available for joins.
File | Provides a list of files available for joins.
Member | Provides a list of available members that can be selected to create a join. Note: This functionality is only available with iSeries sources.
Database | Provides a list of databases having tables available for joins (non-iSeries server).
Owner | Provides a list of available owners (non-iSeries server).
Table | Provides a list of available tables that can be selected to create a join (non-iSeries server).
Tables Available for Joins | Displays the names of the tables available to create a join based on the selections made in the drop-down lists.
Existing Joins | Displays the existing joins.
Table Alias | Provides a list of available table aliases.
Join Fields | Displays the fields within the selected table alias available for joining.
Join Script | Displays the editable join script.
Update | Updates the selected and edited join script.

Vision Solution home page


Filters tab

The Filters tab enables you with a means to develop filters in order to work with particular source fields.

Click to view full-size image.
Click to view full-size image.

Feature | Function
Source Table Alias | Provides a list of available source table aliases.
Source Fields | Provides a list of available source fileds of the selected source table alias.
Filter Script | Displays the editable filter script.
Synchronize Filtered Data | Enables the source and target systems to be kept in sync with one another based on the rules specified in the Filters tab.

Vision Solution home page


Field Mappings tab

The Field Mappings tab provides you with the ability to create and manage all of your mappings in one easily accessible window.

Click to view full-size image.
Click to view full-size image.

Feature | Function
Source Table Alias | Provides a list of available source table aliases.
Destination Table Alias | Provides a list of available destination table aliases.
Source Fields | Provides a list of available source fields.
Destination Fields | Provides a list of available destination fields.
Field Mappings | Displays all information for each field mapping.
Set Key Fields | Sets unique fields as keys fields. Note: This functionality is only available if a unique index exists on the source.

Vision Solution home page


Data Capture tab

The Data Capture tab provides you with the ability to set field monitoring, journaling, and triggers.

Click to view full-size image.
Click to view full-size image.

Field Monitoring

This group of features allows you to control how the update triggers capture changes at the field level by specifying whether a field is monitored or unmonitored.

Feature | Function
Unmonitored Source Fields | If an update is made to one of these fields (and only one of these fields), the update trigger will not write a transaction to the Symbiator staging tables (SYMTARGET and mirror tables).
Monitored Source Fields | If an update occurs on at least one of these fields, the update trigger will write a transaction out to the Symbiator staging tables (SYMTARGET and mirror tables).
Add Button | Adds the selected fields to the Monitored Source Fields list box. Clicking [image] provides you with the ability to add all or just the selected items. The button is disabled if there are no items displayed in the Unmonitored Source Fields list box.
Remove Button | Removes the selected fields from the Monitored Source Fields list box. Clicking [image] provides you with the ability to remove all or just the selected items. The button is disabled if there are no items displayed in the Monitored Source Fields list box.

Triggers

This group of features allows you to view the status of the Symbiator triggers and list triggers attached to the source table.
Feature
Function
Existing Source Table Triggers
 The list displays:

Trigger Event – Which database event causes the trigger to fire (i.e., insert, update, or delete).

Trigger Name – The name of the trigger.

Owner – The owner of the trigger. This column name may vary based on database type. For example, with iSeries sources, the term is "Trigger Library".

Trigger Time – Whether the trigger event occurs on the before image or after image.

Note: Sybase sources will only have the "Trigger Name" column.
Note: Right-clicking in the list provides the option to drop all selected triggers.
Legend
The legend displayed below the Existing Source Table Triggers list indicates which triggers were created by Symbiator ("Symbiator triggers are listed in bold.").

The legend also indicates whether or not the Symbiator triggers are active for the selected mapping (if a source table has multiple destinations, the Symbiator triggers may not capture changes for all of them).

Execute/Save Options

This group of features enables you to add Symbiator triggers to source tables.

Feature | Function
Insert/Update/Delete Check boxes | If the check box next to trigger type is selected Symbiator attaches the trigger for that event when [image] is clicked.
Replace Existing Symbiator Replication Triggers Check box | Configures whether or not Symbiator should first drop any existing Symbiator triggers before attaching the trigger for each event. If there are Symbiator triggers already attached and you do not select this option, an error will occur.
Execute Trigger | Applies the selected triggers to the source table.
Save | Enables you to save the SQL script used to generate the trigger to a file.
Copy | Enables you to copy the SQL script used to generate the trigger to a file.
Print | Enables you to print the SQL script used to generate the trigger to a file

iSeries sources only have the Execute Trigger button available.

Journaling

This group of features allows you to view and modify your journaling status on the iSeries.

While this group is visible at all times, the features only apply to iSeries sources. The buttons and text boxes are disabled for non-iSeries sources.

Feature | Function
Journaling Status | Indicates whether or not the file is currently journaled on the iSeries.
Journal Reader Status | Indicates whether or not the Journal Reader job is currently active for the journal.
Journal Management | Opens the Journal Management window to the Create Journal tab. Note: This button is only enabled with an iSeries source.
Start Journal Reader | Opens the Journal Management window to the Start Journal Reader tab. Note: This button is only enabled with an iSeries source.

Vision Solution home page


Symbiator Mass Transfer (SMT)

You use Symbiator Mass Transfer (SMT) to move large amounts of source data into destination databases, providing an initial synchronization point for real-time replication. You can also use SMT to periodically refresh a destination table in lieu of continuously sending each transaction.

  • Open SMT by clicking [image] on Symbiator Manager, by clicking Symbiator Mass Transfer on the Symbiator Manager Tools menu, or by selecting SMT from the Symbiator 4.1 program group.

Click to view full-size image.
Click to view full-size image.

About SMT

SMT uses the mapping information and source and destination server characteristics you configure to transfer your data from source to destination tables.

Selecting the Truncate Destination Table option clears the destination table of all existing data and repopulates it with the transferred data.

Saving SMT settings

You can save an SMT session and retrieve your definitions for later use.

For example, if you have a destination that you refresh daily with a snapshot of your source data, saving the SMT settings can simplify this task.


Symbiator Console

Symbiator Console allows you to monitor transactions as they are processed by SymService. The information displayed by Symbiator Console is limited to the events that occur during a Symbiator Console session.

Opening Symbiator Console

  • Access the Symbiator Console by clicking Launch Symbiator Console on the Tools menu on the Symbiator Manager.
Click to view full-size image.
Click to view full-size image.

The Transactions tab provides the following information:

List Heading | Description
Type | the type of message
Date | the date/time the message was received
Category | the category to which the message belongs
SubCategory | the subcategory of the message
Server Pair | the server pair to which the message applies
Tables | the source and destination tables for the message
Message ID | the message ID associated with the particular message
Description | a short description of the message
Source | the application that sent the message (SymService or SMT)
Host | the SymService host machine that sent the message

Viewing Transaction Details

To view a transaction detail:

  • Double-click the item in the Transactions tab list for which you want to view it's details. The Detail dialog box opens.

Click to view full-size image.
Click to view full-size image.

To scroll through transaction details:

  • Click the [image] or [image].

To copy a transaction detail:

  • Click [image] to copy the detail to the clipboard that can then be used to paste the information into Notepad or any word processing or spreadsheet application.

File menu

File Menu Command | Click this to...
Open Log File… | open a saved console log • After the log file is opened, a tab is added to the Console (the name on the new tab is the name of the open log file). • Only one log can be open at a time. If you attempt to open another log, you are prompted to close the open log first.
Close Log File... | close an open log file • This menu option is only available if there is a log file open
Save Log File As... | save messages from the transaction log screen to a file • This option is only available if the current tab is the Transaction Log tab.
Exit | close the Console

Edit menu

Edit Menu Command | Click this to...
Show Columns | choose which columns are visible within the grid • The column settings are specific to the tab currently selected.
Options | open the Console Options dialog box

Messages menu

Messages Menu Command | Click this to...
Transactions | turn on/off the transaction log (checked = on, unchecked = off).
Activity Monitor | turn on/off the transaction log (checked = on, unchecked = off)
Browse Mode | scroll vertically through the log without being automatically placed on new items as they are added

Help menu

Help Menu Command | Click this to...
Contents | open the Symbiator Online Help to the Contents tab
Index | open the Symbiator Online Help to the Index tab
Search | open the Symbiator Online Help to the Search tab

Symbiator Console toolbar
Toolbar Button
Description
Click this to...
Start and stop the monitor

• The icon for the button will change depending on whether the monitor is on or off (flashlight with light = on, flashlight without light = off).

• The icon will also change when a different tab is selected depending on whether or not that service is on or off.

turn on/off either the Activity Monitor or the Transaction Log depending on which tab is currently active

Clear screen

• The icon will change depending on whether or not there are any items in the list (trashcan = no items in the list, full trashcan = items available in the list).

• This button is disabled when viewing an open log file.

clear all events currently in the main display area

Toggle browse mode
set in browse mode (the focus will not move to any new messages, allowing you to "browse" to previous messages without losing focus on each event displayed)
Console options
opens the Console Options dialog box

Activity Monitor

This view gives you a central point to monitor the status of each active replication pair. You can easily see all of your source and destination servers, view summary replication data, monitor performance, and start and stop SymService.

  • Access the Activity Monitor by clicking Launch Symbiator Console on the Tools menu on the Symbiator Manager and then clicking the Activity Monitor tab.

Click to view full-size image.
Click to view full-size image.

The Activity Monitor tab provides the following information:

List Heading | Description
Last update | the last time a message was received for the displayed server pair
Host name | the SymService host machine that sent the message
Pair | the server pair for which the information applies
Mappings | total number of mappings for the server pair
Start Time | the time the Server Pair was originally started
Transactions | total number of transactions processed for the server pair since SymService was last started
Pending | total number of pending transactions for the server pair
Errors | total number of outstanding replication errors for the server pair

System Tray Icon

When the Console is minimized, it is hidden from the taskbar. It's icon [image] is displayed in the system tray. Double-clicking this icon restores the application to the desktop.

Right-clicking the icon opens a shortcut menu with the following options:

Command | Description
Messages | enables you to start and stop the monitor
Open | restores the Console to the desktop
Exit | closes the Console


Symbiator Console Options

Console Options

Symbiator Console nows offers the ability to configure a range of information.

Opening Console Options

  • Click [image] on the Symbiator Console toolbar.

General tab

The General tab provides the following information and functionality:

Feature
Function
Symbiator Host Machines

this drop-down list enables you to choose from which host machines the Console displays messages

The choices:

  • Host Filter Off: All messages are displayed.
  • Include Hosts: Only messages from the hosts in the list are displayed.
  • Exclude Hosts: Messages from hosts other than the ones listed are displayed.
Browse Button
opens a dialog that allows you to specify a computer from the network

Add Button

Adds the machine name displayed in the text box

Note: The button is only enabled if text is displayed in the text box.
Remove Button

Removes the selected host from the host list

Note: Only available if an item in the list is selected and there are at least two items in the list.

Transaction Log Tab

The Transcation Log tab provides the following information and functionality:

Feature
Function
Message Filter

enables you to select which messages the console displays
Maximum Log Entries

sets the maximum number of items listed in the transaction log

Note: Once the total number of items exceeds this setting, the oldest entries are deleted.
Archive Transaction Log to File

turns on the setting to automatically write transaction log entries to a file

The path can be specified in the text box below the check box.

The file name will always be in the following format:

  • SymLog_YYYYMMDD_hhmm.xml
  • YYYYMMDD = Date the file was created
  • Hhmm = Time the file was created
Maximum Log File Size

allows you to specify the maximum size in kilobytes of each log file

Note: If the file exceeds the specified size, the current file will be closed and a new one will be started.

Archive Frequency

indicates how frequently the messages are written to file

Example: If the setting is 5000, then the file will be saved every 5000 messages.

Port Settings tab

The Port Settings tab provides the following information and functionality:

Feature | Function
Transaction Log Port | specifies the UDP port the Console should receive the transaction log messages on
Activity Monitor | specifies the UDP port the Console should receive the activity monitor messages on

Alerts tab

The Alerts tab provides the following information and functionality:

Feature
Function
Status Thresholds

the total number of minutes that must past without receiving a message for a particular pair before it changes to the next alert level

Example, if Yellow Alert Status is set to 60, then if an Activity Monitor message is not received for a particular pair in 60 minutes, the status changes to red.

The Send Email option configures the console to send an email out to the designated address when a status changes.

Email Address

is the address of the alert email recipient. Multiple addresses can be entered by separating them by semicolons

Note: The email settings options are only available if at least one of the Send Email options is checked.
SMTP Server

designates the server used to send the alert email
Send Test Email

sends a sample email to the specified address through the specified SMTP server

This can be used to verify that the correct email settings have been entered.


Symbiator Service Manager

Symbiator Service Manager allows you to start and stop SymService for any SymService host on your network. Symbiator Service Manager consists of the following components:

  • The Symbiator Service Manager window
    • Using Symbiator Service Manager you can start and stop SymService on local and remote SymService host servers.
  • The Symbiator Service Manager task bar tray icon
    • Double-clicking the tray icon opens Symbiator Service Manager.
    • Right-clicking the tray icon opens a contextual menu from which you can start and stop SymService locally and access the Symbiator Service Manager Options dialog box.
  • The Symbiator Service Manager Options dialog box
    • Use the Symbiator Service Manager Options dialog box to set the polling interval for SymService.


Defining Data Sources and Destinations Overview

Defining Data Sources and Destinations

This topic describes how to define your replication environment.

For specific instructions, refer to the following topics:

  • Configuration Process
  • Data Source Names
  • Preparing a server to be a Symbiator source
  • Defining sources
  • Utilizing switched disks
  • Defining destinations
  • Authorizing Symbiator
  • Editing and deleting definitions

Configuration process

Once Symbiator is installed, configuring data replication is fairly straightforward. The phases to setting up replication are outlined below.

Configuration Phases
Phase
Description
Prepare source systems
Before a server can be defined as a Symbiator data source, some initial preparation may be necessary.
Define your source server
Define a system to be a Symbiator data source. Choose from existing DSNs or create a new one.
Define your destination
Choose a system to receive data from a specific Symbiator data source. Choose from existing DSNs or create a new one.
Define mappings for each table
The mappings are the actual relationships of source to destination columns and define how and where you want your data to be replicated.
Prepare the source to respond to changes in the database
If your source is an iSeries:
Trigger (or journal) the physical file from which you wish to replicate.
If your source is not an iSeries:
Run the replication script for your mappings.
Authorize Symbiator to run on your Windows 2000/XP server
Symbiator must be authorized with an appropriate authorization code before you can activate replication.
All iSeries sources must be authorized as well.
Populate destination table
Before your replication environment can go into production, the data in your source tables must be copied into the destination tables.
Before you populate your destination tables you may want to test replication to make sure that there are no incompatible mappings.
Activate a connection pair
Using Symbiator Manager, activate the SymService connection pairs you want to replicate.
Start the replication process
All sources-
• start SymService on the Windows 2000/XP server.
If your source is an iSeries-
• start the Journal Reader jobs (if you are using journaling instead of triggers.)

Data Source Names

System DSNs are configured using the ODBC Administrator on the Windows 2000/XP server. You can configure DSNs before you create your source and destination definitions (launch the ODBC administrator from the Tools menu), or you can access the ODBC Administrator directly from the Create New Source/Destination Server dialog box to create new DSNs.

All DSNs for Symbiator must use the appropriate specific driver.

Symbiator provides ODBC drivers for the following RDBMSs:

  • Microsoft SQL Server
  • Oracle
  • Sybase ASE
  • DB2 Universal Database
All source and destination DSNs must be System DSNs.
If your system utilizes any iSeries machines you must use the Client Access/iSeries Access driver, which can be downloaded and installed from IBM’s website.

The Symbiator ODBC drivers are only for use with Symbiator.


Preparing a server to be a Symbiator source

Some RDBMSs require some initial preparation prior to creating source definitions. Complete the appropriate steps below for each system you are preparing.

We recommend that you include the Symbiator database, libraries, and objects in your system backup strategy.

Microsoft SQL and Sybase ASE Servers

For all Microsoft SQL Server and Sybase ASE data sources, the following initial steps are required:

  • Create a database named Symbiator of at least 30 MB.

iSeries

Your iSeries must be configured to meet the following criteria:

  • The Allow multiple threads parameter of QDFTJOBD is set to *NO.

To set QDFTJOBD to Allow multiple threads *NO:

  1. Sign on to your iSeries source system using a profile with *ALLOBJ authority.
  2. Enter the following command:
    CHGJOBD JOBD(QDFTJOBD) ALWMLTTHD(*NO)

Once the install has been completed the Allow multiple threads option can be set to *YES again if desired.


Defining sources

Defining a source server identifies the RDBMS through the ODBC data source name, and also defines the database.

The steps below cover adding a source. To modify or delete a source refer to the following topics:

  • To edit a source definition
  • To delete a source

To add a source:

The RDBMS on your source server must be active in order to create the source definition.

iSeries only: The QSECOFR user profile is recommended for the source creation process.
  1. On Symbiator Manager, click [image].

    The New Source Server dialog box appears.

Click to view full-size image.
Click to view full-size image.

  2. Enter the following information:
    Access additional fields by clicking the tabs along the top of the dialog box.

New Source Server dialog box tab information
Tab | Field Name | Description
Connect | Driver | Select the Symbiator specific driver appropriate for the RDBMS of your source system.
 | System DSN | Select the name of a System data source name (DSN). If you have not yet defined a DSN for the system, click New.
 | Alias Name | Enter an alias to identify the system.
 | User Name | Enter an appropriate user name for the source database identified above. Make sure the user has adequate privileges to perform the necessary actions on Symbiator and non-Symbiator objects.
 | Password | Enter the appropriate password for the User Name specified in the field above.
Database | Symbiator Objects | iSeries sources: The name of the library where the Symbiator objects are installed. This library cannot previously exist. The default is "SYMBIATOR". Click Retrieve to refresh the list of libraries. Oracle sources: The schema where Symbiator objects will be created. The default schema is "SYMUSER", which is created during intilialization. It can also be any pre-existing schema. Microsoft SQL Server and Sybase ASE sources: The name of the database where the Symbiator objects are installed. The database must already exist. Click Retrieve to refresh possible choices.
Advanced | Retry time | Should SymService lose the connection, this is the time interval between automatic retries.
 | Poll time | Defines how frequently the source is polled.
 | Cleanup Interval | Defines how frequently transaction data on the source server is deleted.
 | Database Attributes | Allows you to configure the attributes of the source database. It is recommended that no changes are made.
Connection Handling | Define Error Handling | Define the SQL States, Native Error Codes, and Return Codes for which Symbiator reestablishes lost connections.
  3. After entering the values listed above, click OK.

    If this is the first data source defined for the system, the Symbiator 4.1 Initialization Program dialog box displays, otherwise, Symbiator Manager displays with your new source definition.
  4. Enter the information requested on the Symbiator 4.1 Initialization Program dialog box.
The user profile that you designate must have enough authority to create tables.

iSeries only: *SECADM and *ALLOBJ authority is required for the installation.
  5. Click Next then click OK.

The Symbiator 4.1 Initialization Program runs a script which prepares your server to be a Symbiator data source.

When the source creation process is complete, Symbiator Manager displays with your new definition. The source database appears on the Navigation Pane of Symbiator Manager. If it does not, click [image] to refresh the display.

Vision Solution home page


Utilizing switched disks

Symbiator 4.1 enables you to use the switched disk functionality now available in the iSeries.

To add a switched disk:

  1. On the Tools menu on the Manager, click ODBC Administrator.
    The ODBC Data Source Administrator dialog box appears.
  2. On the User DSN tab, click Add... .
    The Create New Data Source dialog box appears.
Click to view full-size image.
Click to view full-size image.

  3. Select iSeries Access ODBC Driver.
  4. Click Finish.
    The iSeries Access for Windows ODBC Setup dialog box appears.
Click to view full-size image.
Click to view full-size image.

  5. Enter the desired Data source name.
  6. Select the desired System.
  7. Click the Server tab.
Click to view full-size image.
Click to view full-size image.

  8. Click the Override default database with the following check box and enter the desired database.
  9. Click OK.
  10. Click OK on the ODBC Data Source Administrator dialog box.
  11. Click [image] on the Symbiator Manager to add a new Source Server.
    The New Source Sever dialog box appears.
Click to view full-size image.
Click to view full-size image.

  1. Select iSeries Access ODBC Driver from the Driver drop-down list.
  2. Select the desired System DSN.
  3. Enter the User Name.
  4. Enter the Password.
  5. Click Test.
    If "Success" is displayed continue with step 17. If "Fail" is displayed correct the error before proceeding.
  6. Click the Database tab.
Click to view full-size image.
Click to view full-size image.

  18. Select the desired Library.
  19. Click Add Node.
Click to view full-size image.

  20. Select the desired System Name.
  21. Enter the IP Address.
  22. Enter the Model Number.
  23. Enter the Serial Number.
  24. Enter a Description (optional).
  25. Click OK.
  26. Click OK to effect the changes and close the New Source Server dialog box.

Vision Solution home page


Defining destinations

This topic contains instructions for defining and editing destination databases.

The steps below cover adding a destination. To modify or delete a destination refer to the following topics:

  • To edit a destination definition
  • To delete a destination
The user profile defined in Symbiator Manager must have insert, update, and delete rights to all affected files.

To add a destination:

  1. Select the source database on Symbiator Manager, and click [image].

    Alternatively, you can right-click a source and click New Destination Server on the shortcut menu.

    The New Destination Server dialog box appears.

Click to view full-size image.
Click to view full-size image.

  2. Enter the following information:
    Access additional fields by clicking the tabs along the top of the dialog box.

New Destination Server dialog box tab information
Tab | Field Name | Description
Connect | Driver | Select the Symbiator specific driver appropriate for the RDBMS.
 | System DSN | Select the name of a System data source name (DSN). If you have not yet defined a DSN for the system, click New to create a new DSN.
 | Alias Name | Enter an alias to identify the system.
 | User Name | Enter an appropriate User Name for the database identified above. This user should have Select, Insert, Update, and Delete privileges for the tables and databases.
 | Password | Enter the appropriate password for the User Name specified in the field above.
Database | Symbiator Objects | iSeries sources: The name of the library where the Symbiator objects are installed. This library cannot previously exist. The default is "SYMBIATOR". Click Retrieve to refresh the list of libraries. Oracle sources: The schema where Symbiator objects will be created. The default schema is "SYMUSER", which is created during intilialization. It can also be any pre-existing schema. Microsoft SQL Server and Sybase ASE sources: The name of the database where the Symbiator objects are installed. The database must already exist. Click Retrieve to refresh possible choices.
Advanced | Symbiator Objects | Replication User Designates the user for bidirectional replication. Changes made under this profile are not replicated.
 | Automatically load database information | Loads database information automatically.
 | Recycle SymService after mapping change | Causes SymService to recycle aftre any mapping change.
 | Transaction Handling | Opens the Transaction Handling dialog box.
 | Database Attributes | Opens the Database Attributes dialog box.
Mapping Defaults | Left Trim Data | Removes characters from the left side of the data within a field
 | Right Trim Data | Removes characters from the right side of the data within a field
 | Source Field Date/Time Format | Default format for a source field’s data/time
 | Output Date/Time Format | Default format for output’s data/time
 | Default Value | Default value to be used in event of error in data
Connection Handling | Define Error Handling | Defines the SQL States, Native Error Codes, and Return Codes for which Symbiator reestablishes lost connections.
  3. Click OK to save the destination database definition.


Authorizing Symbiator

Symbiator requires an authorization code for each SymService host server and each database type used as a source. An Authorization Code must be entered before replication can begin, and a separate unique code is required for each server and database type.

To authorize Symbiator on a Windows 2000/XP server:

  1. Your first step in authorizing Symbiator is to contact Vision Solutions CustomerCare to be provided with your authorization number(s).

    You will need to provide CustomerCare with the following information for each source database type:
  • The exact name of the host server
  • The database type of the source
  • The version of Symbiator running

    CustomerCare will then provide you, either via phone or email, with a 32-character Authorization Code for each host and database type.
  2. On the Tools menu click Authorize SymService on local machine.
    The Symbiator Authorization dialog box appears.

Click to view full-size image.
Click to view full-size image.

  3. Enter the Server Name.
  4. Select the Database Type.
  5. Enter the Authorization Code.
  6. Click Authorize.
    Repeat steps 3 through 6 for each server and database type.
  7. Click OK.


Editing and deleting definitions

If after creating your definition you need to modify it, you can change all values except for the driver, system DSN, and alias name.

Editing or deleting a source

To edit or delete a source definition, proceed as follows.

To edit a source definition:

  1. Right-click the source database on Symbiator Manager, and click Server Properties on the shortcut menu.

    The Source Server Properties dialog box appears.

Click to view full-size image.
Click to view full-size image.

  2. Make the desired changes.

    Click the tabs to display additional settings.
  3. Click OK to save your changes.

To delete a source definition:

  1. On Symbiator Manager right-click the source definition, and click Delete Source Server on the shortcut menu.

    Alternatively, you can select the source and click [image] on the Symbiator Manager toolbar.

    You are prompted to verify your actions.
If there are no remaining data sources defined to the system, the Symbiator triggers will be dropped from the mapped tables.

Editing or deleting a destination

You can change all destination values other than driver, system DSN, and alias name.

To edit a destination definition:

  1. Right-click the destination database on Symbiator Manager, and click Server Properties on the shortcut menu.

    Alternatively, you can select the destination and click [image] on the toolbar.

    The Destination Server Properties dialog box appears.

Click to view full-size image.
Click to view full-size image.

  2. Make the desired changes.
  3. Click OK to save your changes.

To delete a destination definition:

Deleting the destination also deletes any mappings to the destination tables and clears any pending replication transactions.

  1. Right-click the destination definition you want to delete on Symbiator Manager, and click Delete Destination Server on the shortcut menu.

    Alternatively, you can select the destination and click [image] on the toolbar.

    A dialog box appears, asking for confirmation of the removal.
  2. Click OK to delete the destination.
Vision Solution home page


Mapping Tables for Replication Overview

Mapping Tables for Replication

Mappings, the actual relationships between source and destination table columns, define how and where you want your data to be replicated. The mapping process is as follows:

  • Creating mappings
  • Running Replication Scripts
  • Triggering a source
  • Journaling files for replication
  • Modifying and deleting mappings
  • Altering mapped tables


Creating mappings

The following steps lead you through the process of creating column to column mappings between source and destination tables.

You can create a new destination table based on the source database table from the mapping window, or you can map to an existing destination table.

To create a source table alias:

  1. Select the destination server on Symbiator Manager, and click [image] on the toolbar.

    Alternatively, right-click the destination server and then click New Mapping on the shortcut menu.

    The Mapping Configuration window appears.

Click to view full-size image.
Click to view full-size image.

If an iSeries source

  2. In the Source Table Alias group, select the desired source Library.
  3. Select the source File.
  4. Select the source Member.

    To display all Source Table Aliases uncheck the Filter Source Table Alias List checkbox.
  5. Edit the name of the Source Table Alias displayed in the text box if desired.
  6. Click [image] to the right of the Source Table Alias text box to create the Source Table Alias. The configured alias is displayed in the Source Table Alias list.
  7. Click the Base check box to designate the alias as that from which replication will occur.
Only one Base table can be selected for each mapping. All triggers are attached to the Base table.

If the Base table you select is a SQL view, then you will not be able to perform real time replication and the Data Capture tab will be disabled.
  8. Select or create the desired Destination Table Alias.

If an SQL, Sybase or an Oracle source

  2. In the Source Table Alias group, select the desired source Database.
  3. Select the source Owner.
  4. Select the source Table.

    To display all Source Table Aliases uncheck the Filter Source Table Alias List checkbox.
  5. Edit the name of the Source Table Alias displayed in the text box if desired.
  6. Click [image] to the right of the Source Table Alias text box to create the Source Table Alias. The configured alias is displayed in the Source Table Alias list.
  7. Click the Base check box to designate the alias as that from which replication will occur.
Only one Base table can be selected for each mapping. All triggers are attached to the Base table.

If the Base table you select is a SQL view, then you will not be able to perform real time replication and the Data Capture tab will be disabled.
  8. Select or create the desired Destination Table Alias.

To create a destination table alias:

If an iSeries destination

  1. In the Destination Table Alias group, select the desired destination Library.
  2. Select the destination File.
  3. Select the destination Member.

    To display all Source Table Aliases uncheck the Filter Source Table Alias List checkbox.
  4. Edit the name of the Destination Table Alias displayed in the text box if desired.
  5. Click [image] to the right of the Destination Table Alias text box to create the Destination Table Alias.
    The configured alias is displayed in the Destination Table Alias list.
  6. Click OK.
  7. Proceed to define column mappings below to continue with the mapping process.

If an SQL, Sybase or an Oracle destination

  1. In the Destination Table Alias group, select the desired destination Database.
  2. Select the destination Owner.
  3. Select the destination Table.
    The default Destination Table Alias name appears in the editable text box.
  4. Edit the name of the Destination Table Alias displayed in the text box if desired.
  5. Click [image] to the right of the Destination Table Alias text box to create the Destination Table Alias.
    The configured alias is displayed in the Destination Table Alias list.
  6. Click OK.
  7. Proceed to define column mappings below to continue with the mapping process.

To create a new destination table:

  1. After selecting a source File or Table, click New Table (next to the Member drop-down list box).
    The Create New Table dialog box appears.

Click to view full-size image.
Click to view full-size image.

  2. If you wish to modify the table, right-click on a column value and click one of the following options, otherwise continue with step 5:

    • Add New Column-displays the Field Property dialog box
    • Edit Column-displays the Field Property dialog box
    • Remove Column-removes the selected column from the table
    • Fill Down Null-makes all columns nullable
    • Fill Down Not Null-makes all columns not nullable
    • Change Names to Upper Case - Uses the source table’s field names in all upper case for the new table’s field names
    • Change Names to Lower Case - Uses the source table’s field names in all lower case for the new table’s field names

      The columns can be reordered using the [image] and [image] buttons.

Click to view full-size image.
Click to view full-size image.

  3. Change any of the following properties:

    • name of the column
    • data type of the column
    • size of the column
    • scale of numerical entries
    • whether the column accepts a NULL value
  4. Click OK.
  5. Click the Apply indexes from source to new table check box to automatically apply any indexes in the source to the new table.
  6. When you are done modifying the table, click Create.
Once Symbiator creates a table, you cannot edit the table structure with Symbiator Manager. Any subsequent table structure changes must be made directly through the database application.

The Mapping Configuration window displays, showing the source and new destination table fields.

  7. Proceed to define column mappings to continue with the mapping process.

Mapping to an existing table

If your destination table already exists, use the following process to select the source and destination tables and map the source to destination columns.

To map to an existing table:

  1. On the Symbiator Mapping Configuration window, enter the desired destination database or library in the corresponding text field.
  2. Click the corresponding arrow to populate the Destination Tables or File Name drop-down lists.
    The database tables or files appear in the lists.
  3. Select the source Owner and Table from the lists (or the File Name for the iSeries).
  4. Proceed to define column mappings below to continue with the mapping process.

To define column mappings:

  1. Click the Field Mappings tab on the Mapping Configuration window.
Click to view full-size image.
Click to view full-size image.

  2. Select a source field.
  3. Select the destination field.
Symbiator does not check mappings for compatibility. Make sure that the data types of your source are supported by Symbiator (see Appendix A-Supported data types for details) and that destination columns are compatible. In general text, image, and LOB data types are not supported for any Symbiator supported database.
  4. Click [image].
    The selection is added to the Field Mappings list.
    Alternatively, all fields can be added by clicking [image] to the right of the Plus button and clicking Add All on the menu that appears. Fields are paired in the order in which they appear in the Source and Destination Fields lists.
  5. If your table has any unique index it is displayed when you click the Set Key field drop-down list.
  6. Select the desired index.
    The key field(s) within the index display in red in the Source Fields list.
  7. Click [image].
    The field(s) displayed in red are denoted key field(s) in the Field mappings list.
  8. To substantially improve performance, on the Tools menu click Optimize Replication.
    This functionality automatically places an index on the designated destination based on the key field(s) and the Create New Index dialog box appears.

The Optimize Replication functionality can only be applied to a saved mapping.
  9. Click Yes.
  10. When you have made all of you selections, proceed with configuring date/time fields below.

Setting Field Mappings View

Symbiator 4.1 provides the capability of viewing the Field Mappings tab in three configurations: Normal View, Expand Fields View, and Expand Mapping View. These new view commands are available on the Tools menu on the Mapping Configuration window or they can be toggled using the keystroke combinations noted below on the graphic and in the table.

View
Displays
Normal View
(Ctrl+N)
  • Source Table Alias drop-down list
  • Destination Fields drop-down list
  • Source Fields list
  • Destination fields list
  • Field Mappings list
Expand Fields View
(Ctrl+F)
  • Source Table Alias drop-down list
  • Destination Fields drop-down list
  • Expanded Source Fields list
  • Expanded Destination fields list
Expand Mapping View
(Ctrl+M)
  • Source Table Alias drop-down list
  • Destination Fields drop-down list
  • Expanded Field Mappings list

Configuring date/time fields

If your mappings include any date/time fields, you need to specify the date/time field format.

The formatting for the input and the output are different.
Date/time fields mapped from all sources are configured automatically.

Input Date/Time format syntax

The input date/time format is specified by means of a string time pattern. In this pattern, all ASCII letters are reserved as pattern letters, which are defined as the following:

Symbol | Meaning | Presentation | Example
C | Century | (Number) | 1
Y | Year | (Number) | 1996
M | Month in year | (Text & Number) | 07
D | Day in month | (Number) | 10
H | Hour in day (0~23) | (Number) | 0
M | Minute in hour | (Number) | 30
s | Second in minute | (Number) | 55
S | Millisecond | (Number) | 978
F | Microsecond | (Number) | 978321
a | AM/PM marker | (Text) | PM

The count of pattern letters determine the digits in the input.

Specifying an input format is done without any separators. The separators are removed upon parsing the date field and therefore do not need to be included in the format.

Examples using the US locale:
Format Pattern | Input Date/Time
YYYYMMDDhhmmss | 2003-02-05 13:30:32
YYMMDD | 03-02-05
hhmmss | 13:30:32 (24 Hour Time)
hhmmaa | 01:30 PM (12 Hour Time
YYDDD | 03036 (Julian)
CYYDDD | 103036 (Julian with Century)
Output Date/Time format syntax

The output date/time format is specified by means of a string time pattern. In this pattern, all ASCII letters are reserved as pattern letters, which are defined as the following:

Symbol | Meaning | Presentation | Example
G | era designator | (Text) | AD
y | year | (Number) | 1996
Y | year/week of year | (Number) | 1996
M | month in year | (Text & Number) | July & 07
d | day in month | (Number) | 10
h | hour in am/pm (1~12) | (Number) | 12
H | hour in day (0~23) | (Number) | 0
m | minute in hour | (Number) | 30
s | second in minute | (Number) | 55
S | millisecond | (Number) | 978
f | microsecond | (Number) | 978321
E | day of week | (Text) | Tuesday
e | day of week/local (1~7) | (Number) | 2
D | day of year | (Number) | 189
F | day of week in month | (Number) | 2 (2nd Wed in July)
w | week in year | (Number) | 27
W | week in month | (Number) | 2
a | am/pm marker | (Text) | PM
k | hour in day (1~24) | (Number) | 24
K | hour in am/pm (0~11) | (Number) | 0
z | time zone | (Text) | Pacific Standard Time
' | escape for text |  |
'' | single quote |  |

The count of pattern letters determine the format.

(Text): 4 or more, use full form, <4, use short or abbreviated form if it exists. (e.g., "EEE" produces “Mon”).

(Number): the minimum number of digits. Shorter numbers are zero-padded to this amount (e.g. if "m" produces "6", "mm" produces "06"). Year is handled specially; that is, if the count of 'y' is 2, the Year will be truncated to 2 digits (e.g., if "yyyy" produces "1997", "yy" produces "7").

(Text & Number): 3 or over, use text, otherwise use number (e.g., "M" produces "1", "MM" produces "01", "MMM" produces "Jan", and "MMMM" produces "January").

Any characters in the pattern that are not in the ranges of ['a' … 'z'] and ['A' ...'Z'] will be treated as quoted text. For instance, characters like ':', '.', ' ', '#' and '@' will appear in the resulting time text even they are not embraced within single quotes.

Examples using the US locale:
Format Pattern | Result
yyyy.MM.dd G 'at' HH:mm:ss z | 1996.07.10 AD at 15:08:56 PDT
EEE, MMM d, ''yy | Wed, July 10, '96
h:mm a | 12:08 PM
hh 'o''clock' a, zzzz | 12 o'clock PM, Pacific Daylight Time
K:mm a, z | 0:00 PM, PST
yyyyy.MMMMM.dd GGG hh:mm aaa | 1996.July.10 AD 12:08 PM

To configure date/time fields:

  1. Right-click on a mapping pair that includes a date field, and click Date Format on the shortcut menu.
    The Date/Time Format dialog box appears.

  2. Provide the following values:

Date/Time format information
Field | Description
Input Date Format | Select the appropriate format from the list box or enter a custom value.
Output Date Format | Select the appropriate format from the list box or enter a custom value.
Default Output Value | If the data to be replicated is not a valid value for the date/time field indicated above the default value will be substituted.
  3. Click OK to apply your settings and return to the Mapping Configuration window.

    If you entered a custom date format, you will be prompted to save the value for future use.
  4. Repeat the date field configuration steps above for each date field mapped.
  5. Once all the date fields have been properly configured, continue with Defining linked key fields below.

Defining linked key fields

The linked key fields specify the source and corresponding destination field or fields that uniquely identify a specific record. You can link multiple fields as key fields, although you should only link the minimum number of fields necessary to define a unique record.

For best replication performance, we strongly recommend you create a unique index on your destination table using all of the fields you have defined as linked.
  1. Double-click a mapping to define it as a key field.

The linked key fields are preceded in the Mappings list by the key icon as shown below.

To unlink a pair, double-click the mapping.
  2. When you have completed linking key fields, click Apply.
    Your mappings are saved in the tables files located on the Source Server schema that was defined when the Source Server was defined. The mapping tables/files are consistent over all Symbiator platforms. The mapping tables/files are: SYMSERVER, SYMTABLE, and SYMFIELD.
  3. Once you have defined your key field or fields, review the additional mapping options outlined below.

Setting replication support for iSeries tables with multiple members

This new feature enables you to take advantage of the unique architecture within the iSeries which allows each physical file to have multiple "member" files associated to it.

To configure multiple member support:

  1. Access the Mapping Configuration window for an existing iSeries Mapping definition that contains members.

Click to view full-size image.
Click to view full-size image.

Select Source Table Alias
  2. In the Library list, click the desired source Library.
  3. In the File list, click the desired source File.
  4. In the Member list, click the desired source Member.
  5. To display all Source Table Aliases uncheck the Filter Source Table Alias List checkbox.
  6. Edit the name of the Source Table Alias displayed in the text box if desired.
  7. Click [image] to the right of the Source Table Alias text box to create the Source Table Alias.
  8. Click the Base check box to designate the alias as that from which replication will occur.

Select Destination Table Alias
  2. In the Library list, click the desired destination Library.
  3. In the File list, click the desired destination File.
Clicking New Table creates a new destination file generated using SQL code and therefore will not include the members contained in the source file.
  3. In the Member list, click the desired destination Member.
  4. To display all Destination Table Aliases uncheck the Filter Destination Table Alias List checkbox.
  5. Edit the name of the Destination Table Alias displayed in the text box if desired.
  6. Click [image] to the right of the Destination Table Alias text box to create the Destination Table Alias.
  7. Click OK.

Additional mapping options

If you do not require any of the advanced mapping features, continue with Running Replication Scripts (non-iSeries sources) or Triggering an iSeries source to complete the mapping process, otherwise review the following additional mapping options:

  • Substring—replicate a portion of the source data string based on position and length. See To create a substring of the source data.
  • Left/Right Trim Data—trim leading and trailing spaces from data prior to replication. See To configure Left/Right Trim source data.
  • COM Procedure Call—call a *.dll file to manipulate or transform data. See Calling a COM procedure for details on configuring this option.
  • Replicate a Constant—use the Advanced Mappings feature to replicate a user defined or system constant. See Replicating a Constant value for details on configuring this option.
  • Filtering—filter transactions based on configured criteria. See Filtering for details on configuring this option.
  • Join Table Lookup—choose values that are linked to Lookup tables external to the actual table being mapped and then replicate the "lookup value" to the destination. See Setting cross-referenced table lookups to substitute field values for details on configuring this option.

To create a substring of the source data:

  1. Right-click the mapping, point to Advanced Replication on the shortcut menu, and then click Substring.
    The Substring window appears.
For fields that are concatenated, access the Concatenation window to set the substring properties. See To substring the concatenated source data.
  2. Enter the values for Substring Start Position and Substring Length and click OK.
If you wish to substring to the end of a field from a given start position, enter MAX (be sure to use all capital letters) as the Substring Length.

To configure Left/Right Trim source data:

  • Right-click the mapping and click Right Trim Data or Left Trim Data on the shortcut menu.

You can configure the mapping to right trim, left trim, both left and right trim, or neither left nor right trim.

For a matrix illustrating the various advanced mapping feature combinations, see Advanced Mapping Feature combinations and order of precedence.
Vision Solution home page


Running Replication Scripts

After defining your mapping options, your next task is preparing your source database environments for data replication by running a replication script which attaches triggers to the table that you mapped.

Using the source and destination information you defined in the Mapping Configuration window, Symbiator creates a replication SQL script which must be run prior to replication on all non-iSeries sources.

For iSeries sources see Triggering an iSeries source or Journaling files for replication.

Executing the replication script

To execute the replication script:

  1. Click the Data Capture tab on the Mapping Configuration window.
  2. Click [image] on the Script toolbar.
  3. Click OK to exit the Mapping Configuration window.

To execute the script with your SQL utility:

If you wish to retain existing (non-Symbiator) triggers, you will need to run the script with your interactive SQL utility, otherwise see To execute the replication script.

  1. Click the Data Capture tab on the Mapping Configuration window.
  2. Click Save to save your replication script file.
  3. Edit the script if necessary to incorporate your existing triggers.
  4. Open your replication script in your interactive SQL utility and execute it against the source database.
This will attach three triggers to the source table. The source table will now write data to the Symbiator Transaction Log whenever a delete, update, or insert occurs.


Triggering a source

You will need to specify one of the following methods to enable Symbiator to detect and process the source file changes into SQL statements:

  • Trigger the file (See To add a trigger to a file)
  • Journal the file (See Journaling files for replication)

Adding a trigger to a source file

You add the Symbiator triggers to physical files from the Mapping Configuration window.

Before you begin

You must have exclusive use of the file and have a user profile with sufficient authority to add the trigger to the file.

Symbiator Manager will detect the existence of triggers and disable the Add Triggers menu option. Contact Vision Solutions Technical Support if you already have triggers on your source files.

To add a trigger to a file:

  1. Access the Mapping Configuration window.
  2. Click the Data Capture tab.
  3. Click [image].
    The Insert, Update, and Trigger fields are populated.

    If your source table has multiple destinations the Available Mappings dialog box appears.

    Click to view full-size image.
    Click to view full-size image.

    Click the check boxes next to the mappings you want to apply the triggers to.
  4. Click OK to save the mapping and exit the Mapping Configuration window.


Journaling files for replication

If you wish to use journaling for replication, Symbiator can be configured to check the iSeries journal receivers for data that is to be replicated.

One Symbiator job runs for each journal that is used as a replication source.

If your journal contains transactions for files that are not to be replicated, the Symbiator Journal Reader job must read through many irrelevant transactions.

To minimize system overhead, you can consolidate the journaling of all files that you want as replication sources into a single journal. If you restrict this journal to only the files that are replicated, a single Symbiator Journal Reader job is accessing a journal that is dedicated to replication.

This will improve throughput to the destination, reduce DASD usage, and help minimize the impact of the replication process on your system.

In high authority environments, Symbiator obeys the iSeries authority when setting up journaling, but bypasses this authority for starting and ending the Symbiator Journal Reader. With journals whose authority is very restrictive, Symbiator is able to read from the journals, but while setting up replication (creating mappings, attaching the file to the journal, defining the journal to Symbiator, or starting and stopping journaling) a profile with proper authority is required.

Before you begin

If you will be creating mappings for iSeries sources that will use journaling for replication, you may wish to review all the functionality available from the Journal Management interface (see Symbiator Journal Manager for details). If you will be creating new journals, defining them to Symbiator, starting or stopping journaling on files, or starting or stopping Journal Reader jobs, you can perform all of these tasks using the Symbiator Journal Manager.

Create a new journal for replication

If you are currently journaling the file, see To use an existing journal (file already journaled).

If you are creating a new journal for Symbiator to use, follow the procedure below.

To create a new journal:

  1. In Symbiator Manager, select the iSeries source server that you wish to create a new journal.
  2. On the Tools menu, point to iSeries Journal Reader, and then click Journal Management.
    The Symbiator Journal Management window appears.

Click to view full-size image.
Click to view full-size image.

  3. Configure the fields using the following table as a guide:

Create Journal information required
Field | Value
Journal Library | an existing library on your system
Journal Name | the name of the journal you wish to create
Receiver Library | an existing library on your system
Journal Receiver | the name of the receiver you wish to use (if the receiver does not yet exist on the system be sure to select Create Receiver below)
User Managed / System Managed | if you select system managed, the journal will be created with the Delete receiver parameter set to *NO and you will need to provide a receiver change threshold below
Receiver Threshold | for system managed journals, the threshold receiver size in Kilobytes before a receiver change is initiated
  4. Once you are satisfied with your journal configuration, click Create to create the journal.

To start journaling on a file:

  1. In Symbiator Manager, select the iSeries source server that you wish to create a new journal.
  2. On the Tools menu, point to iSeries Journal Reader, and then click Journal Management.
    The Journal Management window appears.
  3. Click the Start/Stop Journaling tab on the Journal Management window.
  4. Configure the fields using the following table as a guide:

Start Journaling fields and values
Field | Value
File Library | the library which contains the file
Physical File | the name of the file on which you wish to start journaling
Journaling Status | indicates whether or not the file is currently journaled
Journal Name | the name of the journal
Journal Library | the name of the library containing the journal
Before/After Image | indicate whether you want just after images or both before and after images journaled (before images are required to replicate update transactions)
Define journal to Symbiator | select to define the journal to Symbiator
  5. Once you are satisfied with your journal configuration, click Start Journaling to start journaling on the file.
  6. Click OK to return to the Mapping Configuration window.
  7. Click OK to save the mappings and exit the Mapping Configuration window.

Using an existing journal

Before using Symbiator with a file that is already being journaled make sure that both before and after images are journaled for the file (DSPFD).

If both before and after images are not journaled for the file to be replicated, you will need to end journaling on the file and restart journaling with the Record Images parameter set to *Both.

To use an existing journal (file already journaled):

  1. Access the Mapping Configuration window for an iSeries source.
  2. Click the Data Capture tab.
  3. Click the Journal Readers tab on the Journal Management window that appears.
    The Journal Readers tab of the Journal Management.
  4. Select the desired Journal Library, Journal Name, and Sequence Number.
  5. Select the desired Receiver Library, Journal Receiver, and Before/After Image.
  6. Click Start.
  7. Click OK to return to the Mapping Configuration window.
  8. Click OK to save the mappings and exit the Mapping Configuration window.

To define a previously existing journal:

  1. Access the Mapping Configuration window for an iSeries source.
  2. Click the Data Capture tab.
  3. Click the Journal Definitions tab on the Journal Management window that appears.
  4. Enter the Journal Library.
  5. Enter the Journal Name.
  6. Select Auto start journal reader if you want the journal reader to start automatically.
  7. Select Automatically delete journal receivers if you want the journal receivers to be deleted automatically.
  8. Click Update Definitions to add the journal to the Defined Journals list.
  9. Select the newly defined Journal in the Defined Journals list and click Apply.
  10. Click OK to return to the Mapping Configuration window.
  11. Click OK to save the mappings and exit the Mapping Configuration window.
Vision Solution home page


Modifying and deleting mappings

If you want to delete all the mappings for one source/destination table pair, using Symbiator Manager is the easiest method. If you want to add, modify, or delete individual column mapping pairs, you must use the Mapping Configuration window. Both procedures are below.

To modify or delete individual column mapping pairs:

  1. Right-click the relevant replication pair on Symbiator Manager, and click Modify Mapping... on the shortcut menu.
    The Mapping Configuration window appears.
  2. Add, remove, or redefine the mappings as desired.
  3. Click OK to save your mappings and exit the Mapping Configuration window.

To remove a substring definition:

  1. On the Mapping Configuration window, right-click the mapping pair with the substring definition you wish to remove, point to Advanced Replication on the shortcut menu, and then click Substring.
    The Substring dialog box appears.

  2. Click Reset.
    The length and start position values are set to zero.
  3. Click OK to return to the Mapping Configuration window.

To remove a right/left trim definition:

  • On the Mapping Configuration window, right-click the mapping pair with the right/left trim definition you wish to remove and de-select Right Trim Data or Left Trim Data on the shortcut menu.

To delete an entire mapping definition:

  1. On Symbiator Manager, right-click the appropriate mapping, and click Delete Mapping on the shortcut menu.
    A confirmation dialog box appears asking if you’re sure you want to delete the mapping.
  2. Click Yes to delete the mapping, or click No to close the dialog box without deleting the mapping.
    If triggers are attached to the mapping a confirmation dialog box appears asking if you would like to remove the triggers from the mapping.
  3. Click Yes to delete the triggers attached to the mapping, or click No to close the dialog box and delete the mapping without deleting the attached triggers.
    If no other replication pairs are using the source table, a confirmation dialog box appears stating all existing triggers have been successfully dropped on the source table.
  4. Click OK.


Altering mapped tables

If, after defining your mappings, you change the structure, column names, data type, or other attributes of a source or destination table, Symbiator will be unable to apply SQL statements from or to the modified table.

If you need to alter your tables, perform the following steps before making any changes to your tables.

To prepare a mapped table to be altered:

  1. Prior to altering the table, restrict access to the table so that no new transactions are generated.
If transactions are generated during this process, the source and destination tables will no longer be identical. If this happens you may need to use SMT to re-synchronize your data.
  2. Prior to altering the table, set the replication status to Never for any connections containing the table.
  3. Access the activity monitor and refresh the view (F5) until there are no pending transactions for any connection using the table.
  4. Once all pertinent connections are current, stop SymService.
    No transactions will be lost by stopping SymService.
  5. Make the changes to your table.
  6. Right-click the relevant replication pair on Symbiator Manager, and click Modify Mapping... on the shortcut menu.
    Your column to column definitions as well as column names that are not mapped appear on the Mapping Configuration window.
  7. Add, remove, or redefine the mappings for columns that are affected by the structural changes.
  8. On the Mappings menu, click Apply.
  9. Click the Data Capture tab on the Mapping Configuration window.
  10. Click Execute to run the replication script.
  11. Click OK to save your mappings.
  12. Access the Replication Property dialog box to reactivate the replication pairs.
  13. Once all relevant connection pairs are set to active, start SymService.


Using Advanced Mapping Features Overview

Using Advanced Mapping Features

The following Symbiator advanced mapping features are discussed:

  • Concatenating source columns
  • Filtering
  • Manipulating and transforming data
  • Removing advanced mapping configurations


Concatenating source columns

You can combine source columns into a single destination column. For example, if your source database has separate zipcode and zipcode extension fields, you could concatenate them into a single destination field.

To concatenate source columns:

  1. Access the Mapping Configuration window.
  2. Select the source and destination tables as described in Creating mappings.
  3. Do one of the following:

    • To select multiple adjacent columns click the first column you want to add, and then click the last column while pressing the Shift key at the same time.
    • To select multiple nonadjacent columns click the first column you want to add and then click the other desired columns while pressing the Control key at the same time.
  4. Select a destination column.
  5. Click [image].

    Your definition is added, displaying <CONCATENATED> in the From column. The fields are concatenated in the order in which they are listed. If you need to re-order the fields, see To reorder concatenated source columns.
  6. Repeat steps 3 through 5 to add additional concatenated columns.
  7. Click OK to save your mappings.

Modifying concatenated definitions

Once you have created your concatenated definition, you can modify the order of the concatenated columns and add or remove columns from the definition via the Concatenation window.

To access the concatenation window:

  1. Access the Mapping Configuration window for an existing Mapping definition.
  2. Right-click the concatenated column mapping you wish to edit, point to Advanced Replication on the shortcut menu, and then click Concatenation.
    The Concatenation window appears as shown below.

Click to view full-size image.
Click to view full-size image.

To add or remove concatenated source columns:

  1. Access the Concatenation window.
  2. Add or remove source columns as desired using the arrow buttons located between the lists.

Concatenation modification buttons
Click this... | To do this...
 | Add all fields to the concatenation
 | Add the selected field to the concatenation
 | Remove the selected field from the concatenation
 | Remove all of the fields from the concatenation
  3. Click OK to close the Concatenation window and return to the Mapping Configuration window.
  4. Click OK to save your mappings.

To reorder concatenated source columns:

  1. Access the Concatenation window.
  2. Click on a source column name in the Concatenated Fields pane.
  3. Reorder source column as desired using the up and down arrow buttons to the right of the Concatenated Fields list.
  4. Click OK to return to the Mapping Configuration window.
  5. Click OK to save your mappings.

To substring the concatenated source data:

  1. Access the Concatenation window.
  2. In the Substring Source Data group, enter the values for Start Position and Length.
  3. Click Set Substring.
  4. Click OK to exit the Concatenation window and return to the Mapping Configuration window.

For a matrix illustrating the various advanced mapping feature combinations, see Advanced Mapping Feature combinations and order of precedence.

Setting padding to concatenated fields

Setting padding to concatenated fields enables you to add characters to the value in the destination field. The best example is when working with dates.

Example: Setting padding to concatenated field
Value | Without Padding | With Padding
Source1 INT = 1999 (Containing Year) | 1999 | 1999 Pad String: 1 Fill With Padding Until Times: 4
Source2 = 9 (Containing Month) | 9 | 09 Pad String: 0 Fill With Padding Until Times: 2
Source3 = 2 (Containing Day) | 2 | 02 Pad String: 0 Fill With Padding Until Times: 2
Destination Date Format = MMDDYYY | 291999 (Invalid date value) | 02091999 (Valid date value)
The padding string will not be inserted into the resulting data if the length of the source data is equal to or exceeds the times parameter. If you set the times to 2 and the source data is equal to or greater than 2, no padding will occur. If the source data is less than 2, padding will occur.

To set padding to a concatenated field:

  1. Access the Concatenation window.
  2. Click the Source Field on which you want to set the pad.
  3. In the Pad Data section of the window, enter the value for Pad String.
  4. Select Repeat Padding.
    This causes the pad character entered in step 3 to repeat in the field that number of times entered in step 5.

    —OR—

    Fill With Padding Until.
    This causes the pad character entered in step 3 to fill the field until the total number of characters in the field equals that set in step 5. The total number includes all data captured in addition to the padding set.

  5. Enter the number of Times.

  6. Select the desired Pad Side.
  7. Click Set Pad.
  8. Click OK to exit the Concatenation window and return to the Mapping Configuration window.

Setting concatenated values as a linked field

To set concatenated values as a linked field:

  1. Access the Mapping Configuration window for an existing Mapping definition.
  2. Double-click the concatenated column mapping you want to set as a linked field.

The linked concatenated values are preceded in the Mappings list by the key icon as shown below.

For a matrix illustrating the various advanced mapping feature combinations, see Advanced Mapping Feature combinations and order of precedence.

Calling a COM procedure on concatenated fields

This feature enables you to manipulate concatenated fields through the use of any COM enabled method written in VB, C++, Delphi or Java.

For example:

  1. Source Field1=ABC
  2. Source Field2=DEF
  3. Concatenate(Field1+Field2)=ABCDEF
  4. COM Call: Reverse(ABCDEF)=FEDCBA
  5. DestinationField1=FEDCBA
To use the COM call feature make sure that the DLL file that contains the COM object is registered on the machine that Symbiator is installed on. To register the DLL file you must use the REGSVR32.EXE program that ships with Windows 2000/XP.

To register a .dll file
  1. Locate the full path of your DLL file that you wish to register.
    Example: c:\Program Files\My DLL\MyDLL.dll
  2. Click START, click RUN, type cmd, and then click OK.
    The Command Prompt opens.
  3. Change to the directory where your DLL is located, for example type:
    cd C:\Program Files\My DLL\
  4. Execute the REGSVR32.EXE program with the name of your DLL File, for example type:
    REGSVR32 MyDLL.dll
  1. Access the Mapping Configuration window for an existing Mapping definition.
  2. Right-click the concatenated column mapping you wish to edit, point to Advanced Replication on the shortcut menu, and then click COM Procedure Call.
    The COM Procedure Call window appears.

Click to view full-size image.
Click to view full-size image.

  3. Click [image] next to the File text box.
  4. Using the dialog that appears, navigate to the file containing COM procedure you want to call and click Open.
  5. In the list of available classes, click the desired class.
    The methods associated with the class display in the Methods panel.
  6. In the list of methods, click the appropriate method.
    Once you have selected a method, the In and Out Parameter lists are populated with the associated parameters.
  7. In the In Parameter list, click the appropriate parameter.
  8. In the Out Parameter list, click the appropriate parameter.
    Once you are satisfied with your configuration, click OK to close the window and return to the Mapping Configuration window.

    The concatenated column displays <DLL> to denote calling a COM procedure.
For a matrix illustrating the various advanced mapping feature combinations, see Advanced Mapping Feature combinations and order of precedence.


Setting Trim

Setting trim to concatenated fields

Symbiator 4.1 provides you with an easy way to trim off leading and/or trailing blanks within a concatenated field.

To set trim to concatenated fields:
  1. Access the Concatenation window containing the fields you want to trim.
  2. In the Concatenated Fields list, right-click the Source Field to trim, and click either Left Trim or Right Trim from the shortcut menu the appears.

    The selected setting is displayed in the Trim column in the Concatenated Fields list.
Both Left Trim and Right Trim can be selected to trim off leading and trailing blanks.
  3. Click OK to return to the Mapping Configuration window.


Filtering

Using this option you can implement replication criteria for specific data source columns. For example, you could use this option if you want to perform replication when a specific source column value equals red, but not if that source column has any other value. In the filter, you can use any operators, functions and expressions that are supported within the "WHERE" clause of a standard ANSI SQL statement. The operators, functions and expressions that can be used are limited to those that are supported by the source database.

Setting filtering criteria using SQL expressions

To define filtering:

  1. On the Mapping Configuration window, click the Filters tab.

Click to view full-size image.
Click to view full-size image.

  2. Select the Source Table Alias.
  3. Select the Source Field.
  4. Click [image]. The automatically generated string is displayed in the text box.
  5. Type the desired comparison operator in the text box.
In entering the text string you are writing a where clause of an SQL query that is compatible with the source.

Operators can include:

• Equal to [=]
• Not equal to [<>]
• Less than [<]
• Less than or equal to [<=]
• Greater than [>]

• Greater than or equal to [>=]
• Between [Between x and y]
• In [x in (1,2,3,4)]
• Is null [x is null]
• Is not null [x is not null]
For example, suppose you were replicating a table that contains personal information for your customers, however, you only want to replicate those customers who met EITHER of the following criteria:

  1. People who live in either California, New York or Texas AND are over the age of 21.
  2. People who live in any state other than California, New York or Texas AND are over the age of 18.
    AND
    People whose first name is either John or Joan.
The example above is illustrated in the following graphic:
Click to view full-size image.
Click to view full-size image.
    If you wish to add additional criteria, repeat steps 1 through 5 for each criterion.

  6. On the Tools menu, click Test SQL.
  7. Click OK in the Message that appears.
  8. Click OK to save the Filtering and close the Mapping Configuration window.


Clear a filter script

Clearing a filter script

You can easily clear a script from the Filter Script text box.

To clear a script:

  1. Access the Filters tab on the Mapping Configuration window.
  2. In the Filter Script text box, select the Filter Script you wish to clear, and press the Delete key.
Vision Solution home page


Manipulating and transforming data

Occasionally you may want to transform or manipulate data prior to replicating it from a source to a destination database.

Symbiator provides several optional methods that you can use to accomplish this task:

  • Defining a constant value
  • Calling a COM procedure
  • Defining a table join
  • Setting the "Synchronize Filtered Data" option
  • Configuring Filtered Joins
  • Configuring multiple member support for iSeries source and destination

You define these options on the Mapping Configuration window.

To access the Mapping Configuration window:

  • On Symbiator Manager, right-click on an existing mapping, and then click Modify Mapping on the shortcut menu.
    The Mapping Configuration window appears.

Click to view full-size image.
Click to view full-size image.

Defining a constant value

Using this option you can replicate a constant value (user defined or a system value) to a destination column rather than replicating the actual source data.

The constant value must be compatible with the data type of the destination field to which it is mapped.

To define a constant value:

  1. Select the field you wish to define as a constant value in the “Destination Fields” list on the Field Mappings tab of the Mapping Configuration window.
  2. Click the down arrow next to the plus button, and click Add Constant from the drop-down menu the appears.
    The Define a Constant dialog box opens.

    The Define a Constant dialog box opens.

Click to view full-size image.
Click to view full-size image.

  2. Click the desired Constant Type.
  3. Select the desired value in the Key Value list.

    iSeries values
    Non-iSeries values
    Date
             Date
    Date/Time
             Date/Time
    Library Name
             Database Name
    File Name
             Owner
    Member Name
             Table Name
    Transaction Timestamp
             Transaction Timestamp
    Source Transaction Type
             Source Transaction Type
    Modification User
             Modification User
    Server Alias | Server Alias
    Program Name | Program Name
    Job Name
    Relative Record Name

  4. Click OK to return to the Mapping Configuration window.
  5. Repeat steps 1 through 4 to define additional destination fields.
  6. On the Mapping Configuration window, click OK to save your mappings and exit the Mapping Configuration window.
A constant can be defined as a key field, see Defining linked key fields.

Calling a COM procedure

Utilizing the COM (Component Object Model) procedure interface, you can create custom functions to transform or manipulate data using a full featured development tool.

The COM Objects can be written in any language that can compile a COM or DCOM *.DLL file and should be in the following format:

  • Input/Output properties-The source data gets passed into and will be read from standard COM properties. These properties can have any name but must accept and return data as a STRING (VB) or BSTR (C++)
  • Transformation Routines-The routines that perform the data transformation should be in a standard COM method and should have no compulsory parameters.
To use the COM call feature make sure that the DLL file that contains the COM object is registered on the machine that Symbiator is installed on. To register the DLL file you must use the REGSVR32.EXE program that ships with Windows 2000/XP.

To register a .dll file
  1. Locate the full path of your DLL file that you wish to register.
    Example: c:\Program Files\My DLL\MyDLL.dll
  2. Click START, click RUN, type cmd, and then click OK.
    The Command Prompt opens.
  3. Change to the directory where your DLL is located, for example type:
    cd C:\Program Files\My DLL\
  4. Execute the REGSVR32.EXE program with the name of your DLL File, for example type:
    REGSVR32 MyDLL.dll

To call a COM procedure:

  1. On the Mapping Configuration window, right-click a mapping pair, point to Advanced Replication on the shortcut menu, and then click COM Procedure Call.
    The COM Procedure Call window appears.

Click to view full-size image.
Click to view full-size image.

  2. Click [image] next to the File text box.
An example of a COM Procedure Call can be found on the Symbiator Installation CD in the COM Example directory.
  3. Using the dialog that appears, navigate to your *.dll file and click Open.
  4. In the list of available classes, click the desired class.
    The methods associated with the class display in the Methods panel.
  5. In the list of methods, click the appropriate method.
    Once you have selected a method, the In and Out Parameter lists are populated with the associated parameters.
  6. In the In Parameter list, click the appropriate parameter.
  7. In the Out Parameter list, click the appropriate parameter.
  8. Once you are satisfied with your configuration, click OK to return to the Mapping Configuration window.
For a matrix illustrating the various advanced mapping feature combinations, see Advanced Mapping Feature combinations and order of precedence.

Defining a table join

This feature enables you to choose values linked to tables external to the actual table being mapped and then replicate the "joined value" to the destination. This functionality has been expanded to now enable utilization of the full extent of the SQL language in defining table joins.

To define a table join:

  1. On the Mapping Configuration window, click the Joins tab.

Click to view full-size image.
Click to view full-size image.

    1. In the Owner list, click the desired library.
    2. In the Table list, click the desired table.
    1. In the Database list, click the desired database.
    2. In the Owner list, click the desired library.
    3. In the Table list, click the desired table.
    1. In the Library list, click the desired library.
    2. In the Table list, click the desired table.
    You can choose to filter the Join Table Alias List by selecting from the available drop-down lists:

    Oracle

    Microsoft SQL Server and Sybase ASE

    iSeries

  3. Select the desired Table Available for Joins.
  4. Click [image].
  5. Select the desired Join Field.
  6. Click [image].
  7. Edit the Join Script as needed.
  8. Click [image].
    The newly created table join is now available to be utilized in either the Filters tab to create a filtered join or in the Field Mappings tab to create a field mapping join.

Setting the "Synchronize Filtered Data" option

This new feature enables the source and target systems to be kept in sync with one another based on the rules specified in the Filters tab.

This option only affects update transactions when an advanced data filter is applied. All updates leading to data previously outside of the data filter, to now fall within the filter criteria, will be converted to inserts. This adds the records to the destination that was previously filtered out by the data filter. Conversely, all updates leading to data previously within the advanced data filter and available on the destination, to now fall outside of the filter criteria, will be converted to deletes. This removes the records from the destination that was previously within the filtered criteria before the update.

To set the synchronize filtered data option:

  1. Access the Filters tab on Mapping Configuration window for an existing Mapping definition.
  2. Select the desired filters from the drop-down list boxes, see Setting filtering criteria using SQL expressions.
  3. Select the Synchronize Filtered Data check box.

Configuring Filtered Joins

This new features allows you to choose databases, owners, tables, and fields to use for SQL Joins in standard ANSI SQL syntax. The join is used in conjunction with the advanced data filter to provide faster access to reference tables when the user requires transactions to be filtered.

Fields from the mapped or other tables on the source system are used in conjunction with ANSI SQL Operators to create a ANSI SQL Statement to exclude certain rows of data from the mapped table.
Joins work fastest if the columns in the join condition have been indexed. In some cases, joining on un-indexed columns can result in a slow query.

To configure a filtered join:

  1. Access the Mapping Configuration window for an existing Mapping definition.
  2. On the Alias tab choose Database, Owner, and Table in Source Table Alias group.
  3. Click [image] to the right of Source Table Alias.
  4. Repeat for additional tables.
  5. Select Base.
  6. In Destination Table Alias group choose Database, Owner, and Table.
  7. Click [image] to the right of Destination Table Alias.
  8. Click Joins tab.
  9. Select the desired table from Tables Available for Joins list.
  10. Click [image] to add the selected table to the Existing Joins list.
  11. Select the desired Table Alias from the drop-down list.
  12. Select the desired Field and click to add it to Join Script.
  13. Repeat steps 11 and 12 for the field to join.
  14. On the Tools menu, click Test SQL. If successful proceed to step 15, if unsuccessful correct the script error.
  15. Click OK.

Configuring multiple member support for iSeries source and destination

This new feature enables you to take advantage of the unique architecture within iSeries which allows each physical file to have mulitple "member" files associated to it. Multiple member support configures one of the associated member files and assigns it as the defualt member for the physical file. Every time any transaction takes place on the physical file that doesn't specify a member, the default member is used.

To configure multiple member support:

  1. Access the Mapping Configuration window for an existing iSeries Mapping definition that contains members.

Click to view full-size image.
Click to view full-size image.

Create a Source Table Alias
  2. In the Library list, click the desired source library.
  3. In the File list, click the desired source file.
  4. In the Member list, click the desired source member.
  5. Click [image].
    The new source table alias displays in the list.
    Repeat steps 2 through 5 to create additional source table aliases.
  6. Select the source table alias you wish to be the Base alias.

Create a Destination Table Alias
  7. In the Library list, click the desired destination library.
  8. In the File list, click the desired destination file.
  9. In the Member list, click the desired destination member.
Clicking New Table... creates a new destination file generated using SQL code and therefore will not include the members contained in the source file.
  10. Click [image].
    The new destination table alias displays in the list.
    Repeat steps 7 through 10 to create additional destination table aliases.
  11. Click OK.


User Defined Transformation

Symbiator 4.1 provides you with the flexibility to utilize the full set of SQL functionality available on the destination database.

To use transformation:
  1. Select the destination field you wish to transform in the "Destination Fields" list on the Field Mappings tab of the Mapping Configuration window.
  2. Click the down arrow next to the plus button, and click Add Transformation from the drop-down menu the appears.

    The User Defined Transformation dialog box opens.

    Click to view full-size image.

    Click to view full-size image.
  3. Select the desired Source Field from the drop-down box.
  4. Click [image] to add the alias to the Transformation Script text box.
  5. Edit the script using any of the SQL functions supported by the database.
  6. Click OK to effect the script and close the dialog box.


Removing advanced mapping configurations

If you wish to remove any of your advanced mapping configurations use the procedures below.

To remove a concatenation definition:

  1. On the Mapping Configuration window, click the concatenated field you wish to remove in the Fields Defined for Replication pane, and click [image].
  2. Click OK to save your changes and exit the Mapping Configuration window.

To remove a COM procedure call:

  1. On the Mapping Configuration window, right-click the field with the COM procedure call you wish to remove in the Fields Defined for Replication pane, bpoint to Advanced Replication on the shortcut menu, and then click COM Procedure Call.
    The COM Procedure Call dialog box appears.
  2. Click Clear.
  3. Click OK.

To remove a transaction filtering definition:

  1. Click the Filters tab on the Mapping Configuration window.
  2. Select the text in the Filter Script you want to delete.
  3. Press Delete.
  4. Click OK to save your changes and exit the Mapping Configuration window.

To remove a constant definition:

  1. On the Mapping Configuration window, click the constant field you wish to remove in the Fields Defined for Replication pane, and click [image].
  2. Click OK to save your changes and exit the Mapping Configuration window.
Vision Solution home page


Transferring Source Data with SMT

You have defined your replication environment, perhaps tested your mappings and replication, and now you are ready to go into production.

You want to synchronize your source and destination tables by populating the destination table with all the current source table data.

Symbiator provides Symbiator Mass Transfer (SMT) to copy all data from a source file or table to your destination.

About Symbiator Mass Transfer (SMT)

You use Symbiator Mass Transfer (SMT) to initially replicate data from a source table to any mapped destination. Using SMT, you can also refresh tables on demand.

SMT uses the defined mappings

Because SMT transfers selected data as defined by Symbiator mappings, these mappings must be created prior to using SMT.


Using Symbiator Mass Transfer

Before you begin

Before you transfer data to your destination you may want to test replication. See To start SymService for information on how to activate replication. Once you have tested your connection pairs and are ready to go into production, proceed as follows:

If the purpose of this SMT transfer is to begin production replication, you need to be aware of the following restrictions.

  • In order for the source and destination tables to be exact duplicates, you must make sure that all users are out of the source table, and no changes are made to it.
  • Users should remain out of the source table for the duration of the transfer process and until replication is active.

About the SMT window

SMT window uses the server aliases, DSNs, and associated mappings and allows you to select a transfer method for your specified source and a loading option for your selected destination. All of the transfer and loading methods supported by SMT are compatible with all RDBMSs supported by Symbiator.

To configure an SMT transfer:

  1. On Symbiator Manager, select the desired mapping.
  2. On the Tools menu, click Symbiator Mass Transfer, or alternatively click [image] on the toolbar. The SMT window displays with the default transfer values.

Click to view full-size image.
Click to view full-size image.

  3. Work from the top down to select source information in the SMT window.

The Source section requires the following information:

Source requirements
Field | Description
Server | Select the name of the Source Server (the login information is defined in the registry by Symbiator Manager.)
Library/Database Name | Select the name of the library or database where the source file/table is located.
Owner | Owner of the table to be replicated.
File/Table Name | Enter the name of the source file/table to be transferred.
Member | Select the default member.
  4. Select the corresponding properties for the destination table or file.
  5. Choose a destination transfer method using the following table as a guide:

Destination transfer methods
Transfer method
Description
ODBC
Records are transferred in blocks of a given size. Configure the block size on the Advanced tab of the SMT window.
Bulk Copy Protocol
This functionality provides a faster copy method, but is only available on SQL destinations and requires the Microsoft SQL driver installed on the SMT machine.
File
Click Browse to select the file location.
This option will create an operating system ASCII text file and then load the data into it in a delimited format. The default character for delimiting fields is commas, and the default for delimiting records is a carriage return. You can specify alternate values for these defaults (Click Delimiter on the Advanced tab).
This file can also be loaded into the database by loading utilities provided with the RDBMS.
For Oracle destinations see Using SMT with Oracle SQLLoader for additional information on using control files.

  6. Configure the options using the following table as a guide:

Options
Option | Description
Truncate Destination Table Before Inserting | When this option is checked, SMT will clear all data from the destination table prior to inserting records or creating an ASCII file.
Clear Pending Transactions Before Transfer | To clear all pending transactions, select the Clear Pending Transactions Before Transfer option box.
Close SMT When Transfer Completes | When this option is checked, SMT closes automatically when the transfer is completed.

Advanced tab
Feature
Description
Bulk Fetch/Insert Row Size
Number of rows fetched from the source server at a time
Before/After Transfer Execute...

Allows the user to execute a SQL statement on the destination server or run a program before and after the transfer.

NOTE: When using long files the path must be entered within quotes. Example:

"c:\Program Files\DataMachine.exe"

Delimiters
Provides you with the ability to set the field, row and string delimiters used in transfers to text files.

  7. Once you are satisfied with your settings, click [image] to execute the data transfer.

    During the transfer a status dialog box displays the progress on how many records have been sent.

    When the transfer is complete, the SMT Report dialog box displays.

The source/destination names, record count, bytes transferred, elapsed time, and the transfer rate are reported.

If there is an error during the transfer, an Error Message window appears indicating the failure.

If the SMT session is successful, you may want to save it for future use as follows.

To save your SMT session:

  • Click [image].

The file save dialog box appears. Browse for a location or accept the default folder and give the session a name.

The session is stored using the name you gave it with an extension of *.smt.

Resubmitting an SMT session

An SMT session can fail for a number of reasons. Errors may be related to your ODBC configuration, lack of authority for the designated user ID on the destination database, incompatible field types, or other configuration problems. Once you have remedied the cause of the error submit the transfer again.

Vision Solution home page


Running a saved SMT session

After an SMT session is successful, it can be saved for future use. Then you can open these saved sessions and run them as you wish within the SMT window.

To run a saved SMT session:

  1. On the SMT window, click [image].
    The Open dialog box appears.
Only files with .smt as an extension can be selected.
  2. Select the desired saved SMT session and click Open to retrieve it.
    The saved SMT session is loaded. You can either re-run the session as is, or change source or destination values as you wish.
  3. Click [image] to initiate the data transfer.


Using SMT with Oracle SQLLoader

SMT has the ability to achieve very fast data transfers into Oracle using the SQLLoader program. To use the SQLLoader function with SMT you must create a control file based on the mappings.

To use SMT with SQLLoader:

  1. Open an SMT session.
  2. Configure the source and Oracle destination values as appropriate.
  3. Under Dest. Transfer Method, select the File option.
    The default file name and location for the import file appear. This is where SMT writes the data which is subsequently loaded into Oracle with SQLLoader.
  4. Click Open to the right of the file name to select a different file location.
The location that you select will be reflected in the control file. If, after creating the control file, you choose a different location for the data file, you will need to recreate the control file to reflect the new location.

Setting import file options

The default values for separators are commas to separate fields, a new line for each record, and double quotes to enclose fields. If you have data fields that include any of these characters, SQL Loader would encounter problems loading these fields. If any of your fields contain the default delimiter characters, change the delimiter values as follows. Otherwise, proceed with Creating the control file.

  5. Click the Advanced tab on the SMT window.
  6. Click Delimiter.
    The Delimiter dialog box displays.
  7. If necessary, select alternate field delimiters, line delimiters, and enclose characters.
  8. Click OK.
The options that you select will be reflected in the control file. If after creating the control file, you choose to change the delimiter options for the data file, you will need to refresh the control file to reflect the new delimiters.

Creating the control file

  9. On the Tools menu, click Oracle Control File, or click [image].
    The Oracle Control File screen appears.

Click to view full-size image.
Click to view full-size image.

  10. Specify the following settings:
  11. SQLLoader option-select Direct or Conventional
  12. Truncate Destination Table-clear the existing data from the destination table
  13. Click [image] to save the control file.
    A save dialog box appears.
  14. Designate a file name and location or accept the defaults, and click Save.
    The control file is saved.
If, after creating the control file, you change the location of the data file you must recreate the control file to reflect the new location.

Specifying SQLLoader options

  12. On the Advanced tab of the SMT window, in the After Transfer Execute text box, type the path to the SQLLoader application and the required parameters.

    Make sure you include the following (just as you would when calling the application from a command line):

    • user ID
    • password
    • Oracle Service
    • path and name of the control file.

    Example: SQLLDR.exe MyUserID/MyPassword@MyOracleServiceName CONTROL= C:\MyPath\control_file_name.CTR

Oracle SQLLoader will not find long file or directory names. Use MS/DOS (8.3) names only.
  13. Select .Exe for the After Transfer Execute option.
  14. Click [image] to initiate the transfer.
Vision Solution home page


Running SMT from a command line

You can also run an SMT session from a command line (or create a batch file of SMT calls that you can schedule to execute later). You can run an existing saved SMT session (*.smt file) using the /f switch or you can configure a new session using the /Ls switch.

To run an SMT session from a command line:

  1. Open the DOS prompt on your SymService host.
  2. Change to the directory where the SMT application is installed.
  3. Using the following table as a guide, enter the SMT executable name, the switches, and the associated parameters (separate the parameters with semicolons).

Information required to run SMT from a command line
Executable | Switch | Parameter | Description
SMT.exe | /f | file-name.exe | the path to your saved SMT session
 | /NC | none | no confirmation
 | /nm | none | suppresses error message boxes from appearing
 | /q | none | quit


Changing the SMT defaults

You can configure the default values that an SMT session will have. This is useful if you choose to run SMT sessions directly from Symbiator Manager without accessing the SMT application to configure the transfer options.

  1. On the Tools menu on the SMT window, click Options.
    The SMT Options dialog box appears.

  2. Configure the Options as desired and click OK.


Using an SMT batch file to call several SMT transfers

To use an SMT batch file to call several SMT transfers:

  1. Create an SMT file for each mapping and save them in a directory that can be easily accessed:

    1. In the Symbiator Manager, right-click one of your mapping pairs and the click [image].
    2. Ensure all the options you want to use are checked (Truncate, Clear Pending, etc).
    3. On the File menu, click Save.
    4. Repeat steps a through c for all of your mappings.
  2. Create a batch file that will run these SMT processes:

    1. Open up a text editor, such as Notepad.
    2. Inside of the editor, for each of the mappings you would like to SMT type the following in one line:
      "c:\program files\symbiator\smt.exe" /f "c:\smt\mapping.smt" /q /nc
    3. Save the text file as a *.bat file (i.e. SMTFiles.bat).
Modify the path information to correspond to the directories for both the installation of Symbiator and the saved mapping file. Quotes should be used if the path contains long filenames.

  3. Run the batch file:
    • When you double-click the batch file from Windows, the SMT processes will begin.
    The above method will run one SMT process at a time (the next step will not start until the first is completed). If you would like to run multiple sessions at the same time, you will need to do the following:
    • Change the command line inside of the batch file to:

      • START /REALTIME "c:\program files\symbiator\smt.exe" /f "c:\smt\mapping.smt" /q /nc
The REALTIME option tells the program to continue running the other commands and not to wait for the previous program to complete.

SMT COMMAND LINE INFO (the following is information on what the SMT parameters mean):
/f: This specifies the SMT file you wish to use for your transfer.
/q: This tells the SMT program to close down when it is done.
/nc: This suppresses the confirmation window that normally pops up when the SMT process completes.


Setting up a scheduled task

You can set up scheduled tasks to run automatically at designated times and days.

To set up a scheduled task:

  1. Click Start, point to Programs, then point to Accessories, then point to System Tools, and then click Scheduled Tasks.
    The Scheduled Tasks window appears.
Click to view full-size image.
Click to view full-size image.
  2. Double-click Add Scheduled Task.
    The Schedule Task Wizard appears.
Click to view full-size image.
Click to view full-size image.
  3. Click Next to begin the Wizard. Follow the steps of the Wizard to set up your Scheduled Task.


Setting up a tab delimited log file

To set up a tab delimited log file:

  1. On the SMT toolbar, click [image].
    The options window appears.

  2. Click Log Events and Messages to a Text File.


Replicating Data Overview

Replicating Data

Once you have created your mappings and transferred your source data to the destination table, you can begin replicating your data.

These topics contains information on starting and monitoring the replication process, including:

  • About SymService
  • Starting replication
  • Transaction Handling
  • Symbiator iSeries processes
  • Monitoring replication
  • Stopping replication
If you want an initial copy of your source database on the destination system, first use Symbiator Mass Transfer (SMT) for the initial transfer of data before starting replication.


About SymService

Once configured and activated, replication runs in the background. The SymService component of Symbiator on the Windows 2000/XP server manages the replication tasks.

Active connections

SymService supports numerous active connections; each source to destination definition comprises a connection pair. SymService does not process statements from the source to the destination unless the appropriate connection pair is activated.

When SymService is stopped, transactions occurring on the source are still captured as long as the source table or file is triggered or journaled. Once SymService has been restarted, the transactions will be replicated to the destination starting with the oldest transaction.

Running multiple SymServices

SymService can be run on multiple Windows 2000/XP servers within the LAN environment. When running more than one SymService, be careful to prevent the same source/destination connection pair from being defined on different SymService hosts.

How SymService handles errors

SymService reads database changes from a source database and sends that data as SQL statements to a destination database. Depending on the environment, errors can occur if there is a problem with either of the databases or the network between them. Symbiator 4.1 offers more advanced flexibility and control in handling errors.

If a source database becomes unavailable

SymService polls the source database Symbiator log files at a set interval (defined during setup in Symbiator Manager). If the Source database becomes unavailable or goes offline, SymService retries the connection according to the interval defined for the source database. The loss of connection affects only the one source/destination connection pair and no others (unless another pair shares the same offline source server).

If a destination database becomes unavailable

When SymService tries to replicate to an unavailable destination database, it will identify that the connection is down. It immediately stops all replication statements related to that source/destination connection pair and begins accumulating them up in the Symbiator log files.

SymService will retry the connection according to the value set for the destination server (the default is five seconds). When the connection is re-established, SymService will execute all the queued statements, replicating the changes that occurred to the source while the destination database was down.

If the replication statement is incompatible with the destination

SymService reads from the Symbiator tables on the source, and formats and sends the SQL statements to the destination database. The SQL statements are built according to the mappings, which provide all the data type and table structure information for the source and destination tables that are selected. However, Symbiator does not check that the mappings are compatible.

If you inadvertently created an incompatible mapping, you must return to the Mapping Configuration window and redefine the column or columns.

See Common replication troubles and solutions for information on errors and Error Logs.


Starting replication

To begin the replication process, you need to activate the replication pairs and start SymService.

If you are using journal based replication for iSeries source systems, you will also need to start Journal Reader jobs for each of your journals configured to Symbiator. See Symbiator iSeries processes for details.

To activate connection pairs:

  1. Open Symbiator Manager.
  2. Right-click the desired destination server, and click Configure Replication on the Replication Status menu.
    The Replication Property dialog box appears.

Click to view full-size image.
Click to view full-size image.

  3. Configure replication properties using the following table as a guide:

Replication properties
Value | Description
Never | Replication is not active.
Daily | Transactions are replicated once a day at a selected time.
Always | All transactions are replicated as they occur.
Day of Week | Transactions are replicated on a selected day of the week.
Day of Month | Transactions are replicated on the selected day of the month.
Start Time | Indicates the time of day for replication to start if Daily, Always, Day of Week, or Day of Month are selected.
End Time | Indicates the time of day for replication to end if Daily, Always, Day of Week, or Day of Month are selected.
Halt replication on errors | When an update transaction occurs and the record cannot be found on the destination, a new record is created (the update transaction becomes an insert transaction).
  4. Click OK.
  5. Repeat steps 2 through 4 for each destination you wish to activate.

To start SymService:

  1. Launch Symbiator Manager.
  2. Select a source or destination in the List View.
  3. On the Tools menu, click Start SymService.
    Alternatively, you can start SymService using Symbiator Service Manager.

To start SymService from the command line:

  1. On the Windows taskbar, click the Start button.
  2. Click Run... .
    The Run dialog box opens.
  3. Type net start symservice.
  4. Click OK.

To set SymService to start automatically:

  1. On the Windows taskbar, click the Start button.
  2. Point to Settings.
  3. Click Control Panel.
    The Control Panel window opens.
  4. Double-click Administrative Tools.
    The Administrative Tools window opens.
  5. Double-click Services.
    The Services window opens.
  6. Double-click SymService.
  7. Click Automatic on the Startup type list.
  8. Click OK.
  9. Close the Services window.
  10. Close the Administrative Tools window.

Vision Solution home page


Transaction Handling

Symbiator 4.1 introduces a new set of functionality to provide far greater control in the way transactions and errors are handled.

To configure the way transactions are handled:

  1. From the Manager, access the mapping for which you want to configure transaction handling.
  2. On the Tools menu on the Mapping Configuration window, click Transaction Handling.
    The Transaction Handling dialog box opens.

Click to view full-size image.
Click to view full-size image.

The options on this tab of the dialog box enable you to configure how Inserts, Updates, and Deletes are processed for the selected mapping.

  3. In the Inserts group choose between:
  • Normal Processing (Default)—all Inserts are processed normally
  • Never Process—all Inserts are never processed
  4. In the Updates group choose between:
  • Normal Processing (Default)—all Updates are processed normally
  • Process as Insert—all Updates are processed as Inserts rather than as Updates
  • Never Process—all Updates are never processed
  5. In the Deletes group choose between:
  • Normal Processing (Default)—all Deletes are processed normally
  • Process as Insert—all Deletes processed as Inserts rather than as Deletes
  • Never Process—all Deletes are never processed
  6. Click OK to effect the selected changes and close the dialog box.

    —OR—

    Click Cancel to close the dialog box without making any changes.

    —OR—

    Click the Error Handling tab and configure the way errors are handled for the selected mapping by following the steps outlined below.

You can restore Inserts, Updates and Deletes to their default setting simply by clicking Restore Defaults on the Normal Handling tab.

To configure the way errors are handled:

  1. If the Transaction Handling dialog box is already open skip to step 3 below, otherwise, from the Manager, access the mapping for which you want to configure error handling.
  2. On the Tools menu on the Mapping Configuration window, click Transaction Handling.
    The Transaction Handling dialog box opens.
  3. Click the Error Handling tab.

Click to view full-size image.
Click to view full-size image.

The options on this tab of the dialog box enable you to create error handling definitions.

  4. Select the type of Error you want to create a definition for:
  • SQL State
  • Native Error Code
  • Return Code
  4. Enter the enter the specific State or Code in the text box.
  5. Select the Transaction Type:
  • Insert
  • Update
  • Delete
  • All
  4. Select the Action:
  • Convert to Update
  • Convert to Insert
  • Convert to Delete
  • Ignore Transaction
  • Retry Transaction, and then select the number of Retry Attempts
  • Halt Replication
  4. Enter a Description of the definition.
  5. Click [image].
    The new definition displays in the Defined Error Handling list.
  6. Click OK to effect the selected changes and close the dialog box.

    7. —OR—

    Click Cancel to close the dialog box without making any changes.

To delete a error handling definition:

  1. If the Transaction Handling dialog box is already open skip to step 3 below, otherwise, from the Manager, access the mapping for which you want to configure error handling.
  2. On the Tools menu on the Mapping Configuration window, click Transaction Handling.
    The Transaction Handling dialog box opens.
  3. Click the Error Handling tab.
  4. In the Defined Error Handling list, select the definition you wish to remove.
  5. Click [image].


Symbiator iSeries processes

If you are using journal based replication, you must start a Journal Reader job for each journal defined to Symbiator. Journal Readers configured as autostart start automatically when the Symbiator subsystem starts. The Symbiator subsystem starts automatically when a Journal Reader job starts (i.e. if your Journal Readers are configured as autostart, you need only start one Journal Reader and the rest will start automatically).

To start the Journal Reader job from Symbiator Manager:

  1. Access the Start Journal Reader dialog box in one of the following ways:

    • On the Tools menu, click iSeries Journal Reader => Start Journal Reader.
    • Right-click the iSeries source, and then click Start Journal Reader on the shortcut menu.

    The Journal Management screen appears.

Click to view full-size image.

Click to view full-size image.

  2. Configure the fields using the following table as a guide:

Start Journal Reader fields
Field | Value
Journal | the name of the journal for which you wish to start the Journal Reader
Library | the library that contains the journal
Sequence Number | Number: type a specific sequence number (must also specify journal receiver below) First: the first sequence number in a specified receiver or receiver chain (must also specify journal receiver below) Next: the next transaction in the currently attached receiver Continue: continue with the next sequence number after the last read by the Journal Reader
Journal Receiver | Current: the currently attached receiver Current Chain: the current receiver chain Name: the name of a specific receiver (must also specify the receiver library below)
Receiver Library | the library that contains the specified receiver
Last sequence number read by Symbiator | provided for informational purposes only
Auto start journal reader | select to have the Journal Reader start automatically when the Symbiator subsystem starts
  3. Once you are satisfied with you configuration, click Start to start the Journal Reader.

The Journal Reader job on the iSeries starts. If the Symbiator subsystem is not already running, it starts and triggers all inactive Journal Readers configured as autostart to start as well.

If there are journals to be replicated that are not defined as autostart, you must start a Symbiator Journal Reader job for each of those journals. Starting a Journal Reader job automatically starts the Symbiator subsystem. When the subsystem starts, all Journal Readers that are defined as autostart will start as well.

To start the Journal Reader from an iSeries command line:

  1. Access your iSeries system.
  2. Prompt (F4) the STRSYMJRN command and configure the parameters using the following table as a guide:

Information required to run Journal Reader from an iSeries command line
Field | Value
Journal | the name of the journal for which you wish to start the Journal Reader
Library | the library that contains the journal
Sequence Number | Number: type a specific sequence number (must also specify journal receiver below) First: the first sequence number in a specified receiver or receiver chain (must also specify journal receiver below) Next: the next transaction in the currently attached receiver Continue: continue with the next sequence number after the last read by the Journal Reader
Journal Receiver | Current: the currently attached receiver Current Chain: the current receiver chain Name: the name of a specific receiver (must also specify the receiver library below)
Receiver Library | the library that contains the specified receiver
Last sequence number read by Symbiator | provided for informational purposes only
Auto start journal reader | select to have the Journal Reader start automatically when the Symbiator subsystem starts
Vision Solution home page


Monitoring replication

You can monitor SymService by using the Activity Monitor, Symbiator Console, Error log, or Windows Event Viewer.

Activity Monitor

Once you have activated a connection pair, you can use the Activity Monitor to retrieve performance and status information about your replication environment.

To launch the Activity Monitor:

  1. On the View menu on Symbiator Manager, click Launch Symbiator Console.
  2. Click the Activity Monitor tab.

Click to view full-size image.
Click to view full-size image.

Your active source to destination pairs appear in the top panel, and SymService and Symbiator iSeries job information appears in the bottom panel.

For each connection pair the following performance data appears in the Monitor:
  • Last update
  • Host name
  • Pair
  • Start Time
  • Transactions per hour replicated
  • Pending transactions
  • number of SQL errors generated

From the Activity Monitor, you can also start and stop SymService as well as start and end iSeries jobs.

Symbiator Console

Symbiator Console displays basic information about application events relating to Symbiator and about transactions as they are processed by SymService.

Console messages do not display over terminal services.

To launch Symbiator Console:

  • On the View menu on Symbiator Manager, click Launch Symbiator Console.
    Symbiator Console displays, showing transactions as they are processed by SymService.

Click to view full-size image.
Click to view full-size image.

Windows Event Viewer

To launch Event Viewer:

  1. Click Start, point to Settings, and then click Control Panel.
  2. Double-click Administrative Tools, and then double-click Event Viewer.
Vision Solution home page


Stopping replication

To stop replication you deactivate any active connection pairs and/or stop the SymService. You can stop SymService from the Tools menu, from the Activity Monitor, or by using Symbiator Service Manager. You deactivate replication pairs by setting the replication status to Never Replicate.

To deactivate a connection pair:

  1. Open Symbiator Manager.
  2. Right-click the desired Source Server, and click Never Replicate on the Replication Status shortcut menu.
    All replication pairs for the selected source are deactivated.

To stop SymService from the Tools menu:

  1. Open Symbiator Manager.
  2. Select a source or destination in the list view on Symbiator Manager.
  3. On the Tools menu, click Stop SymService.

To stop SymService using Symbiator Service Manager:

  1. On the Windows taskbar, click the Start button.
  2. On the Symbiator program group, click Symbiator Service Manager.
    Symbiator Service Manager appears.

    Alternatively, if the Symbiator Service Manager system tray icon [image] is present, you can launch Symbiator Service Manager by double-clicking the icon.
  3. Click Stop.
    A message appears indicating that SymService has been stopped.

To stop SymService from the Activity Monitor:

  1. On Symbiator Manager, click the Activity Monitor tab.
    The Activity Monitor displays.
  2. Select SymService from the Active Jobs panel.
    Once SymService is selected, Stop SymService [image] on the toolbar is activated.
  3. Click Stop to end SymService.

To stop SymService from the command line:

  1. On the Windows taskbar, click the Start button.
  2. Click Run... .
    The Run dialog box opens.
  3. Type net stop symservice.
  4. Click OK.

Ending Symbiator iSeries jobs

If you need to end the Journal Reader job on an iSeries source you can end the job via Symbiator Manager or by using the ENDSYMJRN command on the iSeries. You do not need to end the Journal Reader job to stop replication. Setting the replication status to Never Replicate in Symbiator Manager stops replication while allowing the Journal Reader job to keep processing transactions.

To end the Journal Reader job:

  1. End the Journal Reader job in one of the following ways:

  • On the Tools menu, click iSeries Journal Reader => End Journal Reader.
  • Right-click the iSeries source, and then click End Journal Reader on the shortcut menu.
  • On an iSeries command line, enter the following command:
    ENDSYMJRN JRN(library/journal name)


Bidirectional Replication Overview

Bidirectional Replication

Replication in which changes from a source table are applied to a destination table, and changes originating on the destination table are applied to the source is known as bidirectional replication. Symbiator fully supports bidirectional replication however, there are some special configuration issues you need to be aware of. This section explains the mapping needs and user name usage conventions related to bidirectional replication.

Mapping Considerations

Mappings must be set up for each direction. You cannot use the mappings from one source to one destination for more than one direction. Set up each leg of your replication loop following the directions for each specific source you wish to replicate from.

User name usage

When setting up bidirectional replication, it is very important to configure specific user names strategically. These are the names used by SymService to log in and make changes to the destination database.

Consider the following scenario:

You have set up bidirectional replication on a file on your iSeries and a table on an Microsoft SQL Server. A user logs in to the iSeries as JOHNDOE and makes a change to the database.

Symbiator replicates the change to the destination Microsoft SQL Server. The MS SQL Server, now a replication source, picks up the change and, if not configured correctly, sends it back to the iSeries. In turn, the iSeries again detects a change to the database and replicates that change back to the MS SQL Server and so on, resulting in an infinite loop, or numerous replication errors. Obviously, this is not a good situation.

The solution to this problem is through the strategic use of user profile names (iSeries) and logins (Microsoft SQL Server, Oracle, IBM DB/2 UDB, Sybase, and so on).

About the SYMUSER profile

The SYMUSER user profile is the default Replication User which identifies updates flowing through SymService to the destination. As such, the changes made under this user profile are not replicated.

In a bidirectional replication environment, this prevents circular updates.

Do not use SYMUSER as an interactive login to make database changes. Any changes made by SYMUSER are ignored for replication to your destination database.
When using DB2 as a source SYMUSER is not automatically created as the default. If you want to use SYMUSER as the default you will have to create it manually.
It is strongly recommended that you change the default password “symuser” upon installation of Symbiator 4.1.

Note: The password must be in lower case.

The following diagram illustrates bidirectional replication processing:

Setting up bidirectional servers

The following procedure walks you through setting up a bidirectional environment between two servers.

To set up the first source/destination pair:

  1. Define a new data source.

    (See Defining sources)
  2. Define the destination server associated with the source using the following values:

Definitions of destination server associated with source
Field | Value
User Name (Connect tab) | SYMUSER
Password (Connect tab) | SYMUSER (It is strongly recommended that you change the default password.)
Rep User (Advanced tab) | SYMUSER

Refer to Defining destinations.

  3. Using your interactive SQL utility, grant SYMUSER insert, update, and delete authority on the destination table.

To set up the other direction:

  1. Using the destination server from the first source/destination pair, create a new source server definition.

    (See Defining sources)
  2. Using the source server from the first source/destination pair, create a new destination server definition with the following values:

New destination server definitions
Field | Value
User Name (Connect tab) | SYMUSER
Password (Connect tab) | SYMUSER
Rep User (Advanced tab) | SYMUSER

Refer to Defining destinations.

  3. Using your interactive SQL utility, grant SYMUSER insert, update, and delete authority on the destinations table.
    You can now create mappings for your Bidirectional replication environment.


Administration and Troubleshooting Overview

Administration and Troubleshooting

The following administration and troubleshooting topics are discussed:

  • Monitoring Symbiator
  • Symbiator Journal Manager
  • Common replication problems and solutions
  • ODBC troubleshooting


Monitoring Symbiator

SymService is a Windows 2000/XP Service that, once configured, runs completely in the background. However, the System Administrator should monitor Symbiator daily to verify that the replication process is functioning. To monitor the replication environment use the Windows 2000/XP Event Viewer, the Activity Monitor, the Symbiator Error Log, and Symbiator Console as described below.

Using the Activity Monitor

From the Activity Monitor, you can view the status of all your active SymService connections, see at a glance if errors have occurred, and view the status of your iSeries jobs.

To use the Activity Monitor to monitor replication:

  1. On the View menu on Symbiator Manager, click Launch Symbiator Console.
  2. Click the Activity Monitor tab.

Click to view full-size image.
Click to view full-size image.

  2. If a connection pair indicates associated errors, select it in the display panel, then click View Errors [image].

    Alternatively, on the View menu, click View Error Log.
    An error window opens, listing any errors that have occurred.

    • You can delete errors, change updates to inserts, and resend the transaction.
    Refer to Common replication problems and solutions for details on resending transactions.
  3. Review the number of Queued or Pending Transactions.

    The Activity Monitor summarizes the number of unprocessed transactions that are queued to be replicated.

    • If there are no transactions listed, or if only a few are listed for a large number of daily transactions, you need do nothing.
    • If numerous transactions are listed, investigate possible reasons why transactions are not going through.
    • Verify that SymService is running.
      The status of SymService appears in the bottom panel of the Activity Monitor.
  4. Verify that all Journal Reader jobs are running (if applicable).
    The status of each Journal Reader job appears in the bottom panel of the Activity Monitor.

Working with Error logs

If you are experiencing errors during replication, you can review and resubmit transactions using the Replication Error Log window. Error logs should be reviewed regularly to ensure proper replication.

To review and resend a transaction:

  1. On the Activity Monitor, click a connection pair that shows errors, and click [image].
    You can also access replication errors from Symbiator Manager by selecting a source server and then clicking View Errors... on the View menu.
    The Symbiator Error Log window appears.

Click to view full-size image.
Click to view full-size image.

  2. Click on any error transaction to select it.
  3. Click Resend [image] or if the error was caused by an update to a record that could not be found, click Change Update to Insert [image].
  4. Close the window to return to the manager.

To view details of a transaction:

  1. On the Activity Monitor, click a connection pair that shows errors, and click [image].
    You can also access replication errors from Symbiator Manager by selecting a source server and then clicking View Errors... on the View menu.
    The Symbiator Error Log window appears.

Click to view full-size image.
Click to view full-size image.

  2. Double-click the error transaction you want to view.

    —OR—

    Click View Error Detail on the View menu.
    The Error Detail window appears.

Click to view full-size image.
Click to view full-size image.

Using the Windows 2000/XP server Application Log

When SymService encounters communication problems with the source or destination servers, error messages are sent to the Windows 2000/XP server Application Event Log.

To view the Windows 2000/XP server Application Log:

  1. On the Administrative Tools program group, click Event Viewer.
  2. On the Log menu, click Application to display the Application Log.
    If Symbiator is not running successfully or has failed during an operation, a stop sign message is indicated.
  3. Double-click on a message to display the event detail.
    You may be able to glean useful information from the event detail.

Using the Symbiator Console

Much of the information in an Event Viewer record can also be seen in the Symbiator Console, but where as the Event Viewer provides you with a history of events, Symbiator Console provides information for only those events which occur during a Symbiator Console session.

To open a Symbiator Console session:

  • On the Tools menu on Symbiator Manager, click Launch Symbiator Console.

A Symbiator Console window opens, displaying transactions and Symbiator application events as they occur.

Vision Solution home page


Symbiator Journal Manager

Symbiator provides a central point of journal management for users who use iSeries journaling extensively in their replication environment. You can use the Symbiator Journal Manager to easily create and manage your journals and Symbiator Journal Readers.

The following section outlines the functionality available through the Journal Manager and how to use it.

Create a journal

Use the Create Journal tab on the Journal Management window to create journals and receivers for replication.

To create a journal:

  1. Click the Create Journal tab on the Journal Management window.
  2. Configure the fields using the following table as a guide:

Create Journal fields
Field | Value
Journal Name | the name of the journal you wish to create
Journal Library | an existing library on your system
Journal Receiver | the name of the receiver you wish to use (if the receiver does not yet exist on the system be sure to select Create Receiver below)
Receiver Library | an existing library on your system
Create Receiver / Receiver Exists | select as appropriate depending on whether or not the receiver already exists on the system
User Managed / System Managed | if you select system managed, the journal will be created with the Delete receiver parameter set to *NO and you will need to provide a receiver change threshold below
Receiver Threshold | for system managed journals, the threshold receiver size in Kilobytes before a receiver change is initiated
Define Journal to Symbiator | if the journal is to be used for replication by Symbiator select this option
  3. Once you are satisfied with your journal configuration, click Create to create the journal.

Start a Journal Reader

Use the Start Journal Reader tab on the Journal Management window to start Journal Reader jobs.

To start a Journal Reader:

  1. Click the Start Journal Reader tab on the Journal Management window.
  2. Configure the fields using the following table as a guide:

Start Journal Reader fields
Field
Value
Journal
the name of the journal for which you wish to start the Journal Reader
Library
the library that contains the journal
Sequence Number
Number: type a specific sequence number (must also specify journal receiver below)
First: the first sequence number in a specified receiver or receiver chain (must also specify journal receiver below)
Next: the next transaction in the currently attached receiver
Continue: continue with the next sequence number after the last read by the Journal Reader
Journal Receiver
Current: the currently attached receiver
Current Chain: the current receiver chain
Name: the name of a specific receiver (must also specify the receiver library below)
Receiver Library
the library that contains the specified receiver
Last sequence number read by Symbiator
provided for informational purposes only
Auto start journal reader
select to have the Journal Reader start automatically when the Symbiator subsystem starts

  3. Once you are satisfied with your configuration, click Start to start the Journal Reader.

View or end active Journal Readers

Use the Active Journal Readers tab on the Journal Management window to view active Journal Reader jobs or end any of the active Journal Reader jobs.

To end a Journal Reader:

  1. Click the Active Journal Readers tab on the Journal Management window.
  2. Click on a Journal Reader job listed.
  3. Click End.

View or remove journals defined to Symbiator

Use the Active Journal Readers tab on the Journal Management window to view active Journal Reader jobs or end any of the active Journal Reader jobs.

To remove a journal definition from Symbiator:

  1. Click the Defined Journals tab on the Journal Management window.
  2. Click a journal definition listed.
  3. Click Remove.
Removing the journal definition from Symbiator does not remove the journal from the system or affect journaling in any way.

To change the auto start preference for a journal:

  1. Click the Defined Journals tab on the Journal Management window.
  2. Select or deselect the Auto start check box as appropriate.

Start/Stop journaling

Use the Start/Stop Journaling tab on the Journal Management window to start and stop journaling on files.

To start journaling:

  1. Click the Start/Stop Journaling tab on the Journal Management window.
  2. Configure the fields using the following table as a guide:

Start Journaling fields and values
Field | Value
File Library | the library which contains the file
Physical File | the name of the file on which you wish to start journaling
Journaling Status | indicates whether or not the file is currently journaled
Journal Name | the name of the journal
Journal Library | the name of the library containing the journal
Before/After Image | indicate whether you want just after images or both before and after images journaled (before images are required to replicate update and delete transactions)
Define journal to Symbiator | select to define the journal to Symbiator
  3. Once you are satisfied with your journal configuration, click Start Journaling to start journaling on the file.

To stop journaling:

  1. Click the Start/Stop Journaling tab on the Journal Management window.
  2. Configure the fields using the following table as a guide:

Stop Journaling fields
Field | Value
Physical File | the name of the file on which you wish to stop journaling
File Library | the library which contains the file
Journaling Status | indicates whether or not the file is currently journaled
The Journal Name/Library fields should already contain the correct journal information.
  3. Once you are satisfied with your journal configuration, click End Journaling to end journaling on the file.

Add a journal definition

Use the Add Journal Definition tab on the Journal Management window to add a journal definition to Symbiator.

To add a journal definition to Symbiator:

  1. Click the Add Journal Definition tab on the Journal Management window.
  2. Enter a Journal Name and Journal Library.
  3. If you wish to have the Journal Reader job for this journal to start automatically when the Symbiator subsystem starts, click Auto start journal reader.
  4. Click Add.


Common replication problems and solutions

This table lists a number of potential reasons why errors are being generated, why replication is not starting, or other problems and some possible solutions.

Common replication problems and potential solutions
Problem | Possible Solution
SymService not running | Check status in Services. Check the Windows 2000/XP Event log for errors.
Connection pair deactivated | Activate the source and destination pair using Symbiator Manager. Check to see if replication is configured to halt on errors.
ODBC data source name is no longer valid | Use ODBC Administrator to confirm that the ODBC driver settings for source and destination are correct. Test the ODBC connection with the appropriate database client.
Someone is logged on as SYMUSER and is making database changes | Only SymService should use this user ID. Users should never log in as SYMUSER.
No mappings have been defined | SymService requires a mapping between each source and destination.
The replication script has not been run against the source database. | After mappings have been defined, the replication script must be run against the source database.
Symbiator has not been authorized | The Symbiator programs must be authorized on iSeries sources and the SymService host prior to replication.
Error messages | Check for error messages in Symbiator Manager.


ODBC troubleshooting

If you are having trouble with a source or destination ODBC connection, you may want to review your DSN configuration. This may help you determine whether your System DSN for the database is configured correctly.

Symbiator ODBC drivers

If you open the 32-bit ODBC Administrator, click the ODBC Drivers tab to see all of the drivers that are loaded on the Windows 2000/XP server. The actual file names, versions, and dates of creation are listed.

ODBC configuration guidelines

The ODBC Administrator program includes comprehensive online help and is the source for most field configuration information. The following section includes some tips for configuring the Symbiator ODBC drivers for the various data sources supported.

To add an ODBC DSN:

Create System DSNs with the ODBC Administrator as follows:

  1. Click ODBC Administrator on the Symbiator 4.1 program group.
  2. Click the System DSN tab and click Add.
    The list of installed drivers displays in a list box.
  3. Browse the list, click the appropriate driver, and click Finish.
    A multi-tab property sheet displays in which you enter the configuration values.
  4. Enter the required information on all tabs and click OK to create the DSN with the selected driver.

iSeries

iSeries required information
ODBC setup tab | Notes
General | Data source name—use a meaningful name System—select the desired system

Microsoft SQL Server

Microsoft SQL required information
ODBC setup tab | Notes
General | Data Source Name-use a meaningful name Server name -SQL Server name

Oracle Server

Oracle server required information
ODBC setup tab | Notes
General | Data Source Name-use any meaningful name Server name -actual Oracle server name
Advanced | Default User Name-leave blank, or enter a valid Oracle user name for the selected server

Sybase Adaptive Server

Sybase Adaptive server required information
ODBC setup tab | Notes
General | Data Source Name-use any meaningful name Server name -Sybase server name


Supported Data Types Overview

Appendix A—Supported Data Types

Symbiator supports the following data types:


Appendix A—Supported Data Types

Supported non-iSeries data types

Microsoft SQL Server 7.0 & 2000
RDBMS | Supported Data Type
Microsoft SQL Server 7.0 & 2000 | binary
 | bit
 | char
 | datetime
 | decimal
 | float
 | int
 | money
 | nchar
 | numeric
 | nvarchar
 | real
 | smalldatetime
 | smallint
 | smallmoney
 | timestamp
 | tinyint
 | uniqueidentifier
 | varchar

Oracle 8i Database
RDBMS | Supported Data Type
Oracle 8i or higher | varchar2
 | char
 | date
 | nchar
 | number
 | float (number)
 | nvarchar2

Sybase Adaptive Server Enterprise
RDBMS | Supported Data Type
Adaptive Server Enterprise (version 12.0 or higher) | binary
 | bit
 | char
 | datetime
 | decimal
 | float
 | int
 | money
 | nchar
 | numeric
 | nvarchar
 | real
 | smalldatetime
 | smallint
 | smallmoney
 | timestamp
 | tinyint
 | uniqueidentifier
 | varchar

DB/2 UDB 7.2i
RDBMS | Supported Data Type
DB/2 UDB 7.21 | int
 | char
 | date
 | decimal
 | double
 | real
 | smallint
 | time
 | timestamp
 | varchar


Supported AS/400 data types

Appendix A—Supported Data Types

Supported iSeries data types

The supported SQL data types are converted to the corresponding native DB/2 400 data types as follows:

DB/2 400
RDBMS | Supported SQL Data Type | Native iSeries Data Type
DB/2 400 (V5R1 or higher) | char | char
 | datetime | timestamp
 | decimal | packed
 | float | fltsng
 | int | binary
 | money | packed
 | numeric | zoned
 | real | fltsng
 | smalldatetime | timestamp
 | smallint | binary
 | smallmoney | packed
 | timestamp | timestamp
 | varchar | varchar


Appendix B-Hints and Tips

Appendix B—Hints and Tips

Hints

Using Symbiator Mass Transfer to refresh a table when changes are actively occurring on the source

There often are situations where a resynchronization is needed between source data tables and their respective destination table, either for initial load or because some change has occurred where the two are out of synch. Under the best of circumstances, a Symbiator administrator can perform a Symbiator Mass Transfer (SMT) synchronization when there are no changes occurring on the source and be assured that the source and destination tables are in sync. However, data integrity problems can occur when attempting to resynchronize tables while insert, update and delete operations are actively occurring on the source.

By following the steps below, the Symbiator administrator can use a combination of SMT, Real-Time replication and error monitoring to synchronize an actively changing table. This procedure is performed at a point when all the mappings for a particular source-destination table combination have been saved. In order to capture any changes that occurred on the source table, Symbiator replication triggers must be applied to the source, or journaling must be started on the source table. Finally, to prevent duplicates from being written, unique key constraints must be applied on the destination table. When all of these requirements have been met, SymService should be stopped and the SMT transfer started. SMT will then delete the records from the destination table, delete any queued transactions associated with the destination table and then refresh the destination table with new data. After the transfer completes, SymService should be started in order to process any new transactions that occurred during the transfer. Any transactions that are duplicates will be rejected by the destination table unique key constraints. Any deletes of records that were never sent by SMT (because they were deleted prior to SMT reading them) will fail with a "No rows affected" message and any updates will be processed normally.

To use Symbiator Mass Transfer to refresh a table when changes are actively occurring on the source

  1. Stop SymService.
  2. Set the Symbiator replication triggers on the source table.

    —OR—

    Start Journaling on the source table as normal for Symbiator replication and start the Symbiator journal reader job.
  3. Add appropriate unique indexes or primary keys to the destination table to reject any duplicates.
  4. Start the SMT transfer. Make sure to click the Truncate Destination Table Before Inserting and the Clear Pending Transactions Before Transfer checkboxes.
  5. Allow the SMT transfer to finish completely.
  6. Start SymService, allow it to process any transactions that occurred since the transfer.
    View Symbiator Error log. Check for errors related to the table that was previously refreshed. Examine any errors that occurred.

    7. "Insert" transaction errors | If the error message is because of a duplicate key violation, the record was already sent by the SMT transfer. Therefore the error can be deleted.
"Delete" transaction errors | If the error message is because the row could not be found, the record was not sent by SMT, therefore it doesn't exist on the destination table. This is correct and the error can be deleted.
"Update" transaction errors | Due to the fact that update transactions should simply overlay a destination record with the same data, no error should occur.
"Other Errors" | Any errors that have different messages than the ones explained above should be viewed as separate problems and dealt with accordingly.
"No Errors occurred" | It is possible that no changes occurred to the source table while the SMT transfer was running. Therefore, no action is necessary. However, if it is expected that delete or insert transactions should have occurred while the transfer was running, a verification should be made that Symbiator data replication is occurring properly. Also, check to make sure that the destination table has the proper unique indexes applied and that the source and destination table counts are in sync.

Advanced Mapping Feature combinations and order of precedence

 | Constant | Left Trim | Right Trim | COM Call | Concatenation* | Substring | Used as key
Constant | 1 | 2 | 3 | N/A | N/A | N/A | Yes
COM Call | N/A | 2 | 3 | 1 | N/A | N/A | Yes
Concatenation | N/A | 3 | 4 | 2 | 1 | N/A | Yes
Substring | N/A | 2 | 3 | N/A | N/A | 1 | Yes

*Concatenation is the only feature that has additional embedded features that
can be set, the order of precedence is as follows:

Substring | Padding | Concatenation
1 | 2 | 3


Appendix C-Error Messages

Appendix C—Error Messages

Error messages

Issue
Possible Cause
Resolution
Software or Hardware Exception occurred for thread in SymService
Possible bad mapping.
Check mapping of connection that created error then delete and remap.
Length or start position is out of range for the string operation replicating from iSeries
Possible change in mappings or table structure.
Refresh mappings and table structure.
Function Sequence Errors
Performing an insert of a null field to a non-nullable field.
All fields aren't mapped on the destination or the unmapped field isn't nullable.
Ensure that both source field and destination fields are nullable.
Ensure that all fields are mapped on the destination.
Unhandled Exception occurred in Manager
Problem with the Symbiator iSeries Optimized driver.
Copy driver from Symbiator (5.01.03.02 - 10/24/2000).
Run-time error 380, Invalid property value in Manager
Lost mappings during a resynchronization process.
Refresh mappings.
"INVALID DESCRIPTOR INDEX" error
This error happens occasionally when launching SMT and one of the defined sources or target ODBC DSN's is either the Microsoft SQL Server driver or the Symbiator SQL Server ODBC driver version 3.5 or later.
Create an ODBC connection to SQL source using Symbiator SQL driver. (Another connection). The go to Manager add new source server for new ODBC source and it will pickup the existing destinations and mappings.
[Monitor]Error Processing Transaction(s). See event log for details
Error appears to be specific to SybaseASE but due in part to a space in the DSN.
Remove the space in the DSN from within the ODBC Administrator then restart replication process.
Mapped fields not in same order on source as on destination when viewed in mapping window
If an AS400 is the source and a file name, in excess of ten characters is being used, the issue will occur.
Rename or create library file containing 10 or less characters.
Error occurs after opening console window, "ODBC SQL Server Drives Shared Memory"
Possible corrupted SQL Server software on the server machine.
Un-install Symbiator, Uninstall SQL Server, then install SQL Server and install Symbiator.
Trusted TCP/IP Socket Connection May Fail with Error Message
Under certain situations, a client may fail to connect to Microsoft SQL Server 7.0 when using TCP/IP sockets and Microsoft Windows NT authentication (trusted connection), and the following error message is displayed.
Ensure the authentication is set for Windows and SQL by doing the following:
1. | Right click on Microsoft SQL Server.
2. | Select 'Properties' option.
3. | Select 'Security' tab.
4. | Click on Windows and Microsoft SQL Server option.
When trying to do SMT received message, "[Vision Solutions][Symbiator iSeries]Wrong number of parameters"
Issue seems to be specific to DBCS AS400 machines in that if the library isn't identified in the ODBC connection this error will occur.
On the General tab of the ODBC connection specify the library name in the Libraries field.
Installation errors, "An error occurred during the initialization of iSeries source. This may have occurred because the iSeries system files are out of date"
This error occurs during the installation of Symbiator onto the AS400 and is cause by out of date system files.
Have user refer to APAR#SA87360.


Activity Monitor tab

Click to return to the previous page.

<< Back


Filters tab

Click to return to the previous page.

<< Back


COM Procedure Call

Click to return to the previous page.

<< Back


Mirror Table Manager

For each mapping you create, Symbiator creates two "mirror tables" for the source table. These mirror tables are used as staging areas for the before and after images of the transactions. When a mapping is deleted the mirror tables remain until they are deleted using the Mirror Table Manager. Also, if the source table is modified (i.e. a field is added, removed or modified), it will be necessary to recreate the mirror tables.

Within the grid of the Mirror Table Manager, there will be one row for each table that is or was mapped. The text of each row will be colored based on the recommended action by Symbiator:

BLACK: | Mapping and mirror tables are fine. No action necessary.
RED: | Mapping no longer exists or was deleted. Delete the mirror tables.
BLUE: | Mapping exists but mirror tables are missing. Recreate the mirror tables.

To use the Mirror Table Manager to delete the mirror tables:

  1. Access the Symbiator Manager.
  2. Click the mapping you want to display in the Mirror Table tab.
  3. Click the Mirror Tables tab.
    The mirror tables displayed in red denote those mappings that have been deleted, those in black have not been deleted and should not be removed from the Mirror Table Manager.

Click to view full-size image.
Click to view full-size image.
  4. Select the mirror table(s) you want to remove. By dropping the mirror tables, any pending transactions will be deleted.
  5. Click [image].

To use the Mirror Table Manager to recreate the mirror tables:

  1. Access the Symbiator Manager.
  2. Click the mapping you want to display in the Mirror Table tab.
  3. Click Mirror Tables tab.
    The mirror tables displayed in red denote those mappings that have been deleted, those in black have not been deleted and should not be removed from the Mirror Table tab.
  4. Select the tables you want to recreate. By recreating the mirror tables, any pending transactions will be deleted. Once the tables have been recreated, you should resynchronize your source and destination tables using SMT.
  5. On the Edit menu, click Create/Recreate Mirror Tables.


Concatenation window

Click to return to the previous page.

<< Back


Symbiator Console

Click to return to the previous page.

<< Back


Define a Constant Value dialog box

Click to return to the previous page.

<< Back


Create New Table dialog box

Click to return to the previous page.

<< Back


Data Capture tab

Click to return to the previous page.

<< Back


Next steps

Once you have created your source and destination definitions, you are ready to create your mappings and begin the replication process as follows:

  • Create mappings
  • Apply replication scripts
  • Transfer data to the destination
  • Replicating Data


Destination Server Properties dialog box

Click to return to the previous page.

<< Back


Detail window

Click to return to the previous page.

<< Back


Editing a join

You can edit any Join you have created and saved.

To edit a join:

  1. Access the Joins tab.
  2. Click the Join you want to edit in the Existing Joins list.
  3. Edit the Join in the Join Script text box.
Click to view full-size image.
  4. Click [image].
  5. Click OK to effect the edit and close the mapping Configuration window.
Vision Solution home page


Error Handling tab

Click to return to the previous page.

<< Back


Error Log

<< Back


Error Detail window

<< Back


Field Mapings tab

Click to return to the previous page.

<< Back


Field Property dialog box

Click to return to the previous page.

<< Back


SQL Script window

Click to return to the previous page.

<< Back


iSeries Jobs tab

Click to return to the previous page.

<< Back


iSeries Access for Windows ODBC dialog box

Click to return to the previous page.

<< Back


Join tab

<< Back
Click to return to the previous page.


Create Journals tab

Click to return to the previous page.

<< Back


Journal Readers tab

Click to return to the previous page.

<< Back


Mapping Configuration Aliases tab

Click to return to the previous page.

<< Back


Filters tab with script

<< Back


Mirror Tables tab

Click to return to the previous page.

<< Back


Aliases tab

<< Back


Aliases tab with multiple members

Click to return to the previous page.

<< Back


New Destination Server dialog box

Click to return to the previous page.

<< Back


New Source Server dialog box

Click to return to the previous page.

<< Back


Normal Handling tab

Click to return to the previous page.

<< Back


Oracle Control File

Click to return to the previous page.

<< Back


Replication Status Summary

Click to return to the previous page.

<< Back


Replication Property dialog box

Click to return to the previous page.

<< Back


Viewing Replication Status Summary

Symbiator enables you to display a summary of the current replication status of a selected source server.

To view the replication status summary:

  1. In the Symbiator Manager right-click the source server you want to view the replication status.
  2. Point to Replication Settings on the shortcut menu, and then click Replications Settings Summary.
    The Replications Settings Summary dialog box opens and displays:

    • Destination Alias—All destinations mapped from the source server
    • Replication Status—Always, Never or Daily
    • Day of Week—Day of the week to replicate
    • Day of Month—Day of the month to replicate
    • Replication Start Time—Time replication starts
    • Replication End Time—Time replication ends
Click to view full-size image.
Click to view full-size image.

  3. Click OK to close the dialog box.

Vision Solution home page


Resetting filters

You can quickly and easily remove all joins and scripts.

To reset filters:

  1. Access the Filters tab.
  2. Click Reset Filter.
    All joins and scripts are removed from the Filters tab.
Vision Solution home page


Scheduled Task Wizard

Click to return to the previous page.

<< Back


Scheduled Tasks Window

Click to return to the previous page.

<< Back


Showing Symbiator SQL Script

You can easily view the Symbiator SQL Script written automatically when Joins are created.

To show Symbiator SQL Script:

  1. Access the Filters tab.
  2. On the Tools menu, click Show SQL.
    The Symbiator SQL Script window opens.

Click to view full-size image.
Click to view full-size image.

The script cannot be edited in the Symbiator SQL Script window. To edit the script, see Editing a join.
Vision Solution home page


SMT window

Click to return to the previous page.

<< Back


Source Server Properties dialog box

Click to return to the previous page.

<< Back


Next steps

These topics will you to initiate data replication through the following tasks:


Symbiator Manager - Mappings

Click to return to the previous page.

<< Back