StarQuest Data Replicator Release Notes

StarQuest Data Replicator v3.63 Release Notes

June 2010

Introduction

StarQuest Data Replicator (SQDR) is a replication software product that allows you to copy data between IBM DB2, Oracle, Microsoft SQL Server, MySQL and Informix database environments. You can replicate any table, portion of a table, or view. You can schedule replications to take place at specified intervals or on demand. Replicating data from one database management system to another allows you to:

Make the same data available to users of different database systems
Make the same data available to multiple sites
Balance network and database server loads by making the data available from two or more database servers
Create backup copies of data

StarQuest Data Replicator provides:

Incremental replication from host databases running SQDR Plus.
Full refresh replication, which copies all the source data that you specify for replication, regardless of when that data was last replicated. This provides a "snapshot" of the specified source data at the time of replication.

If you will be using SQDR for snapshot replication, refer to the Quick Start Guide for the StarQuest Data Replicator for detailed information about installing StarQuest Data Replicator for the first time. If you will be using SQDR with its companion product SQDR Plus for incremental replication, refer to the appropriate SQDR Plus Quick Start Guide at http://www.starquest.com/Supportdocs/browseQuickStarts.shtml. The SQDR Plus guides include instructions for installing both SQDR and SQDR Plus.

After you install the software, refer to the online help for information about configuring and using the Data Replicator. Press F1 or click the Help button in a dialog or on the toolbar to display the online help when using the Data Replicator Manager application. Also, refer to the SQDR Documentation Addendum for details about new features and documentation errata.

The following topics are addressed in these Release Notes.

What's New in this Release
Upgrade Considerations
System Requirements
Installing and Using the StarQuest Data Replicator Software
Known Issues
Release History
Contacting StarQuest

What's New in this Release

This release of the StarQuest Data Replicator introduces the following new features and improvements:

New and Enhanced DBMS Support:
- Support for MySQL as a destination or a snapshot source
- Support for Informix as a destination or a snapshot source
- Support for SQL Server Native Client
- Enhancements to Oracle support
Configuration Enhancements:
- Enable configuration of locale used by the SQDR Service
- Enhancements to Columns Property dialog of a subscription:
  - Ability to synchronize subscription column information with the source schema without losing previous customizations
  - Ability to sort columns
  - Support multiple select/change on destination columns data types
  - Support macro-based derived column creation based on source columns
Incremental Replication Configuration Enhancements:
- Enhanced incremental subscription destination options ("Append replicated rows to existing data" and "Manual synchronization")
- New incremental subscription options ("Ignore Deletes", "Relaxed Apply Rules", and "Use Unique Constraints")
- Ability to move an incremental subscription member between replication groups (requires SQDR Plus 3.60 or later)
- Ability to copy an incremental subscription member to another replication group
- Ability to specify a Commit Limit for Incremental Apply
Subscription Processing Enhancements:
- Ability to resume an interrupted baseline or snapshot
- Enhanced recovery of orphaned subscriptions (ability to reset an incremental replication group)
New administrative tools TargetChecker and CatalogTest

Refer to the SQDR Documentation Addendum for details about the new features and documentation additions and corrections

Upgrade Considerations

If you are upgrading from any version of StarQuest Data Replicator earlier than 3.60, you must run the Data Replicator Configuration to update your existing control database; either select the checkbox to run the Configuration on the final screen of the installation wizard, or select Data Replicator Configuration from the Program Group, and choose "Use an existing control database and tables".

For incremental replication, the host database system must be running SQDR Plus 3.60 or later.

If you wish to use some of the new incremental replication features such as copying an incremental subscription, you must update the sources and destinations by specifying a default Database and/or Object Schema on the Advanced pane of the properties of a source or destination.

If you wish to use the new restart/recovery feature, refresh each incremental subscription by opening its properties, saving, and then re-running it.

If SQDR Plus is configured to use the maxTrans or singleTrans setting, this setting should be removed from the SQ_PROPERTIES table and/or the sqagent.properties configuration file prior to upgrading; contact StarQuest support if you need assistance removing these settings. Refer to "Ability to Specify a Commit Limit for Incremental Apply" in the SQDR Documentation Addendum for information on configuring similar functionality in the Replication Manager.

If you are upgrading from StarQuest Data Replicator v2.50 or earlier, please contact StarQuest support for special instructions.

System Requirements

You can install and run the StarQuest Data Replicator on any computer with an operating system capable of running SQL Server, such as:

Windows 7
Windows Server 2008
Windows Vista
Windows Server 2003
Windows XP Professional or Home Edition
Windows 2000 Professional or Server

Refer to the readme.txt in the sqdr\x64 folder for special instructions about installing SQDR on a 64-bit operating system.

The Data Replicator requires a control database in which to store definitions for sources, destinations, and subscriptions. If you do not already have a SQL Server installation, you can obtain the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) or SQL Server 2005 or 2008 Express free of charge from http://www.microsoft.com/downloads to use as a control database for the Data Replicator.

If you are using Windows Server 2003, Windows XP or Windows 2000, you may use one of the following for the SQDR control database:

SQL Server 2000 Desktop Engine (MSDE 2000) Release A, or MSDE 2000 SP3a
SQL Server 2000 with SP3a
SQL Server 2005 (including Express Edition)
SQL Server 2008 (including Express Edition)

If you are using Windows 7, Windows Vista or Windows Server 2008, you should use one of the following for the SQDR control database:

SQL Server 2005 sp3
SQL Server 2005 Express Edition sp3
SQL Server 2008
SQL Server 2008 Express Edition

To use SQL Server 2008 or SQL Server 2008 Express Edition for the control database, install SQL-DMO, which is one of the Backward Compatibility components of the SQL Server 2008 Feature Pack.

Note that SQL Server 2005 and 2008 Express do not include the SQL Server Agent component, which is what allows SQDR subscriptions to run automatically. Use the MSDE 2000 version or one of the full SQL Server products for the SQDR control database if you want to schedule snapshot subscriptions to run automatically.

You can replicate data to and from any of the following database systems:

DB2 for OS/390 v5.1 and later
DB2/400 (DB2 UDB for iSeries) OS/400 v3r2 and later
DB2 Universal Database (UDB) v6.x and later for UNIX and Windows
SQL/DS (DB2 for VM & VSE) v3.3 and later
SQL Server 2008
SQL Server 2005
SQL Server 2000 Desktop Engine (MSDE 2000) Release A, or MSDE 2000 SP3a
SQL Server 2000 with SP3a
SQL Server v7.0 with at least SP1 (v7.00.699) (SP4 or the current service pack recommended)
Oracle 8i or later
MySQL 4.1 or later
Informix: any version of Informix supported by v3.50 of the IBM Informix ODBC driver:

IBM Informix Dynamic Server Enterprise or Express Edition 10.0, 11.10, 11.50
IBM Informix Extended Parallel Server 8.50 and higher
IBM Informix SE 7.25
IBM Informix OnLine 5.20 and higher

For incremental replication, the host database system must be running SQDR Plus 3.60 or later.

Access to DB2 databases requires the StarSQL ODBC/DRDA driver, v5.10 or later.

Access to Oracle databases requires the Oracle Client software, including the ODBC driver from either Oracle or Microsoft.

Access to MySQL databases requires Connector/ODBC 5.1.5; do not use Connector/ODBC 5.1.6. Specify use of ANSI_QUOTES in the MySQL database configuration if the Quote SQL Identifiers option is enabled for the SQDR service.

Access to Informix databases requires that the database be configured to support Transactions. The database must use the "Delimited" environment variable if the Quote SQL Identifiers option is enabled for the SQDR service.

In addition, you can use the Data Replicator with other databases if they can be accessed through an ODBC driver that complies with Level 2 of the ODBC 3.51 specification.

Installing and Using the StarQuest Data Replicator Software

If you will be using SQDR for snapshot replication, refer to the Quick Start Guide for the StarQuest Data Replicator for detailed information about installing StarQuest Data Replicator for the first time.
If you will be using SQDR with its companion product SQDR Plus for incremental replication, refer to the appropriate SQDR Plus Quick Start Guide at http://www.starquest.com/Supportdocs/browseQuickStarts.shtml. The SQDR Plus guides include instructions for installing both SQDR and SQDR Plus.
After you install the software, refer to the online help for information about configuring and using Data Replicator. Press F1 or click the Help button in a dialog or on the toolbar to display the online help when using the Data Replicator Manager application.
Browse the StarQuest Data Replicator technical documents at http://www.starquest.com/Supportdocs/techSQDR/browseSQDR.shtml for specialized information, such as using the StarQuest Data Replicator in a particular environment or to accomplish a special task.

Known Issues

MySQL Considerations

Do not use SQDR's connection pooling with MySQL sources and destinations. On the Advanced Pane of the properties for a source or destination, change the value of Maximum Connection Pool Size to 0.
Indexes and primary keys on a MySQL source table may appear incorrectly in the subscription wizard; verify that only valid indexes are selected for replication purposes.

Error replicating to a UTF8 database

When replicating from a non-Unicode database to a UTF8 target, you may encounter the following error for certain data:

ODBC message: SQLSTATE 22001, native error -302,

[StarSQL][StarSQL ODBC Driver][DB2 Universal DataBase]The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.

This may occur when some characters are converted into multi-byte characters and the size of the target table's character columns is inadequate. Modify the subscription and increase the size of the affected columns on the target table.

Mapping NCHAR and NVARCHAR Column Types

If you create a subscription that maps SQL Server 7.0 NCHAR or NVARCHAR column types to CHAR or VARCHAR types on a DB2 or Oracle destination, you must take an extra step to ensure that the destination column type is assigned the correct precision. If you omit the following step, the Columns wizard screen will display the correct precision values for the applicable destination columns, but those columns will be created with double the displayed precision values.

After specifying the information requested on the Columns screen of the Add Subscription Wizard, select each NCHAR or NVARCHAR source column, then click the Modify Column button to display the Column Properties dialog box. Make any required changes and then click OK to return to the Columns wizard screen.
After performing the above step for each NCHAR or NVARCHAR source column, click Next to proceed to the next wizard screen.

Precision Limitation for Oracle CHAR and RAW Column Types

The Oracle driver and the Microsoft ODBC for Oracle driver have a limitation that affects replicating from SQL Server or DB2 columns to an Oracle column of data type CHAR or RAW. Although the Oracle CHAR and RAW data types allow larger values, you must set the precision for the destination column that uses these types to 255 or less. If the source column precision is greater than 255, you can avoid data truncation by changing the destination column to a data type that allows a larger precision value. For character data, change the Oracle destination column data type to VARCHAR or LONG. For binary data, change the data type to LONG RAW.

BCP error when replicating to SQL Server

Replication to SQL Server may fail in certain situations, such as when there is at least one LOB column n the source table and more than one ''image" column in the target table. You can work around this problem by specifying "Insert using ODBC" rather than BCP in the destination options of the subscription.

Memory usage of Service increases when communicating with Manager

Leaving the Data Replicator Manager running for extended periods of time could cause a memory management problem in the Replicator Service. To avoid this, we recommend running the Replicator Manager only when you need to configure the service or subscriptions or monitor the replication operations. In addition, the Replication Manager will automatically exit after running for twelve hours.

Saving Subscriptions to AS/400 Member Files

If you encounter a subscription validation error while saving a new subscription that will replicate to a member of a physical file on an AS/400, you may need to cancel the subscription wizard and completely redefine the subscription in order to specify a member for the destination file.

AS/400 Function check when recreating an incremental subscription from OS/400 v5r2

The following scenario will cause an AS/400 Function check:

Create an incremental subscription from a OS/400 v5r2 server
Delete the group (which deletes the subscription)
Immediately attempt to recreate the subscription

Solution:

Before recreating the group, check the Connections pane of the Data Replicator Manager and make sure that there are no open connections to the AS/400.
OR Edit the StarSQL ODBC data source to the AS/400 using ODBC Data Source Administrator. On the Expert Page, set the option UseDSCRDBTBL to No.

Release History

This section provides information about problems or enhancements that are made available as "point releases" and major releases of the StarQuest Data Replicator software.

Version Number	Release Date	Significant Changes
V3.63.0611	Jun 2010	Sub to SQL Server target table fails to save for certain table names
V3.62.0521	May 2010	11320 Ignore partition Detach signal requiring new baseline, if ignoreDelete is specified
V3.61.0517	May 2010	11315 IR: forcing use of RRN for existing I/R Subscription results in invaild (empty) index name 11316 Implement Group-level useSequenceTX option 11318 I/R Goup reset should clear restart checkpoints
V3.60.1710	May 2010	9449 Support for MySQL database 10023 Support for Informix database 10333 Add feature to optionally retry failed baseline 10380 Flush baseline window "close" event - do not cache 10385 WRITEDB issues 10388 SQDR Config - modify panel to include later versions of SQL Server 10503 Change default DATE mapping for DB2 -> Oracle 10636 Sub Wizard: Truncate and Append options are not mutually exclusive after selecting Append 10688 Installer: Removing controldb and tasks fails with error against SQL Server 2005 and later 10692 Implement DELBYSUBSCRIBER from "Source" for I/R followed-by auto add 10753 IR subs from iSeries to SQL Server fail when getting change data (bad syntax) 10788 Installer: during uninstall, removing control tables and scheduled tasks fails with error 10799 Disable Destination Object name if View is being replicated 10832 Implement "move I/R subscription" command 10844 Strip information domain qualifier, etc. from schema/owner default value 10855 Specify a Commit Limit for I/R Apply - throttle transaction blocking dynamically 10870 Import of mixed case names to subscription wizard fails to iSeries 10872 Restrict view of source tables if "requirePublication" is specified on Host 10877 Allow multiple select/change on destination columns datatypes 10879 Support macro-based derived column creation based on source columns 10891 Enable configuration of locale used by SQDR Service 10894 Duplicate Group names appear when run time is identical 10895 Provide full support for SQL Server Native Client 10913 Honor quote identifier setting when creating Indexes 10928 Verify Criteria with lower/mixed case table names 10930 Changing sub properties to copy DDL only causes error and "hung" RM 10931 Saving subscription with DB2 source column timestamp mapped to SQL Server datetime2 10937 NPE when displaying existing I/R group in new sub wizard 11038 ControlDB DSN name not displayed on Connection Status 11078 Provide "Copy" I/R subscription member action 11090 Invalid Where Clause when Capture-Apply disagree on schema 11095 SQDR Apply/Capture sync when processing Baseline 11125 Deleting multiple members from group generates "No such subscription" error 11132 Bundle TargetChecker and Catalog tools in SQDR installation 11159 BCP Insert counts not matching Select counts - not flagged as error 11160 Sort, Multiple column modify columns 11161 Snapshot subscription "loop" if not authorized to the source 11166 SQDR should use destination column names to create indexes 11191 Disable "use" RRN on subscription wizard if subscription does not support 11192 Config doesn't launch after upgrade if installed into non-default directory 11198 Column Properties: Keep Modify Columns selected as default and move selection 11201 Refresh Subscription Action 11214 PK, FK: Use destination column names to create primary keys and constraints 11242 Replicator: Enhance I/R Subscription Destination Option to include "Append to existing" 11253 Implement support for I/R subscription options: No Delete, Relaxed Apply, & Unique Constraints
V3.43.0415	Apr 2009	10597 Use TRUNCATE instead of DELETE operation with I/R subs if supported 10657 Open window processing of files using RRN 10670 Convert readme to HTML
V3.42.0728	Dec 2009	Repackage Add SQL Server 2008 information to readme 10485 Starlic: accept node-locked license based on IP address 10495 Starlic: online licensing with SOAP web-service 10513 Starlic: provide explicit user feedback when interacting with license service 10514 Starlic: warning when using online licensing to request license - permanent effect 10500 Starlic: if License Request fails, focus should not switch to the Licenses tab
V3.42.0728	Jul 2008	10429 Destination table not deleted and re-created when IR sub is re-saved 10447 Apply Open Window processing of "H" transaction not using "After Key" values
V.3.41.0605	Jun 2008	10411 Add 12 hour timeout to SQDR RM sessions
V3.40.0421	Apr 2008	10332 Installer: support Windows Server 2008 and Vista 10360 Installer: more changes for Windows Server 2008 and Vista 10357 unable to create incremental subscription with iSeries Access v6r1 10359 Move Crash Dumps directory to COMMON_DOCUMENTS 10363 Documentation: recommend SQL Server 2005 Express sp2 in addition to MSDE 2000 10371 Add event log extract and crashdump copy to WRITEDB.CMD 10373 Unable to get table list in sub wizard from case-sensitive SQL Server 9740 Config: SET AUTO_CLOSE OFF when creating new ControlDB on SQL2005
V3.35.0128	Jan 2008	10316 Text(SQL Server) to LOB (UDB) results in invalid CREATE TABLE (must specify NOT LOGGED) 10320 Default mapping for SQL_GUID should be binary, not char 10324 wrap capture SQL with case logic for null columns in ADD, DELETE record types
V3.34.0402	Apr 2007	10033 Extended Stored Proc should block after starting replication 10034 WRITEDB.CMD enhancements 9981 Email notification should not send bare linefeeds 10036 Installer: add 64 bit sqdrxp.dll & instructions
V3.33.0124	Jan 2007	9972 Scheduled IR group run should work like menu command Run Group
V3.32.1304	Jan 2007	9925 last baseline statistic not showing up under I/R group/subscription member display 9937 SQDR RM - If you don't view properties of the GROUP - force use of RRN is not set
V3.31.1130	Nov 2006	9932 Treat I/R Group target DBMS outage as polling error

Contacting StarQuest

If you need to contact technical support, please provide the following information to help the support engineers address your issue. You can contact StarQuest via phone, email, or facsimile as indicated at the bottom of this page.

Company Information	Address Phone Fax
Contact Information	First and Last Name of individual contact Email Address
Host Type	Hardware and Operating System (i.e., AS/400 V5R4)
Network Protocol/Gateways	Protocol and/or Gateways used (i.e., TCP/IP, HIS 2000)
Client Type	Client Type and Operating System (i.e., Windows 2008 SP2)
StarQuest Product, Version, and Source	Which StarQuest product and what version is installed (i.e., StarSQL for Windows v5.50.02.18) Where you obtained the software (i.e., direct from StarQuest, name of specific reseller)
Problem Information	Provide as much detail as possible, including information about any application that is using the StarQuest product when the problem occurs and the exact error message that appears.

StarQuest Ventures, Inc.
PO Box 1076
Point Reyes Station, CA 94956
Telephone: 415-669-9619
FAX: 415-669-9639
Sales & Support: contact@starquest.com
URL: http://www.starquest.com

SQDR Resources