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:
StarQuest Data Replicator provides:
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.
The following topics are addressed in these Release Notes.
This release of the StarQuest Data Replicator introduces the following new features and improvements:
If you are upgrading from any version of StarQuest Data Replicator earlier than 3.66, 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".
If the SQDR service is configured for autostart, Data Replicator Configuration will configure the SQDR service to automatically restart if the service should fail for any reason. Use the Services Control Panel to change the values on the Recovery tab after running Data Replicator Configuration if you wish to use different settings.
When upgrading from any version of StarQuest Data Replicator earlier than 3.70 or when uninstalling SQDR, if the installer appears to stall after displaying "Unregistering modules", move the main installer window to see if a hidden dialog is waiting for input.
Advanced users using the key logging function (enablekeylog.vbs) may use the truncate command to ensure that ir_keylog is empty when upgrading from any version of StarQuest Data Replicator earlier than 3.65. For example, if the
control database is called ControlDB, you can run the following from SQL Server Management Studio: TRUNCATE TABLE [ControlDB].[dbo].[ir_keylog], where [ControlDB] should contain the actual name used for SQDR in the deployment.
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 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 "Specifying Commit Limits for Incremental Apply" in the online help 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.
You can install and run the StarQuest Data Replicator on any computer with an operating system capable of running SQL Server, such as:
Considerations for Windows 64-bit operating systems: SQDR is a 32-bit application. When installed on a 64-bit operating system, create 32-bit ODBC datasources for source, destination, and control databases using the 32-bit ODBC Administrator (e.g. C:\WINDOWS\SysWOW64\odbcad32.exe). The SQDR program group contains a shortcut to the 32-bit ODBC Administrator.
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 or Windows XP, you may use one of the following for the SQDR control database:
If you are using Windows 7, Windows Vista or Windows Server 2008, you should use one of the following for the SQDR control database:
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 can be used to run SQDR subscriptions 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. Alternatively, you can use Windows Scheduled Tasks to automate SQDR activities such running snapshot subscriptions.
You can replicate data to and from any of the following database systems:
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.
If you choose to use IBM i Access for accessing a DB2 for i database as a destination, use either iAccess 7.1 (5770-XE1) with service pack SI42424 (May 2011) or later, or System i Access for Windows v6r1 (5761-XE1) with service pack SI42423 (May 2011) or later.
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.
MySQL Considerations
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.
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:
Solution:
Columns with a DEFAULT value
If the source table has columns that are defined with a DEFAULT value, that information is not preserved when the destination table is created using SQDR.
Solutions:
e.g. ALTER TABLE MYTABLE ALTER COLUMN LOG_DATE SET WITH DEFAULT 'CURRENT TIMESTAMP'
or
Kognitio WX2 Issues
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.73.1116 | Nov 2011 | 9749 Support SQL_BIGINT data type as a column type in SQDR mappings 11667 Manager: enable Help button for new dialogs 11669 Manager: use different Help context ID's for copy vs convert IR group member 11728 Option use of Unicode for intermediate character conversions 11736 Add support for replication using wide characters and "LONG" SQL types 11738 Add snapshot support for XML data type 11745 change default of "Create LOB columns as not logged" to off 11760 Use "native" ODBC C types for binding integer types 11761 Bind parameters just once per replication if possible 11772 Add explicit support for XML for Apply processing 11787 Utilize "use Unicode" service setting when suggesting column defaults 11788 Implement support for Snapshot using modified version of GetData 11791 3.73 uses default of multiRowFetch enabled 11833 Subscription Option "use RRN" will result in RRN as Unique Index (if SQL Server, will be CLUSTERED) 11849 ApplySubscriber fires assert if Snapshot indication recieved before Baseline window closes 11850 Installer: update vcredist (VS2008 redistributable installer) to latest 11857 multiRowFetch does not replicate all data when StarSQL is used for destination |
| V3.72.0408 | Apr 2011 | 11610 Add Virtual Constructors 11620 Failed to write replication statistics (SQL Server Native Client) 11633 Catch attempts to use stale connections when running baselines 11635 Service ending unexpectedly without a trace (crash dump) 11649 Incomplete implementation of 'M' source event 11654 Deleting columns after sort produces incorrect results 11655 Avoid retry if BCP commit fails to agree with inserted count 11660 Baselines race condition 11662 Config: configure Service recovery tab if service is set to autostart |
| V3.71.0306 | Mar 2011 | 11585 Manager: Column picker sort of destination type should include precision 11589 Copy/Convert Member dialog - cosmetic 11603 Copy/Convert: Copy member when dest table exists, and using replace, svc crashes 11604 Copy/Convert Member dialog - cosmetic 11605 Column source and destination data types not displayed for some datatypes 11609 Manager memory leak |
| V3.70.0302 | Mar 2011 | 11557 Setting "useUniqueConstraint" raises assert in multi-subscription mode of Wizard 11561 Build using VS2008 (VS9) 11564 provide mechanism to "replace" or "delete" a prefix/suffix when copying subs 11566 Copy/Convert should utilize the "destination" table name of the subscription being copied/converted, not the "source" table name 11567 RESET Group does not appear to reset the ss_state of member subscriptions 11568 Eliminate assert when switching back and forth between S/S & I/R selection 11569 Installer and VCPROJ changes for VS2008 11570 Copy member subscription does not copy constraints 11581 SQDR should default to multiRowFetch off 11584 Inconsistent results when creating a single subscription or editing existing subscription 11599 Insure WYSIWYG for decorator using Copy/Convert 11600 Installer: SQDR now requires WinXP and later |
| V3.67.0121 | Jan 2011 | 11553 SQDR Plus/iSer on V5R2 - erroneously reports sub dropped, when IR group paused/resumed 11556 TargetChecker.bat: improve Java lookup on 64-bit systems |
| V3.66.1312 | Jan 2011 | 9068 Sync up Capture Agent and SQDR at startup 10431 Config: display error message if osql is not found 11298 IR Options: Modify message displayed for I/R subscription index creation 11334 RM: Correct group defaults When inserting a subscription and building I/R group 11475 Snapshot Replication failure feedback is obscured by retry message 11478 Allow I/R Group member subscriptions to be reordered 11499 Support Kognitio WX2 as a destination and as a snapshot source 11501 Correct precison of TIMESTAMP 11508 Provide "convert" to S/S option when "copying" I/R subscription(s) 11509 Do not permit I/R subscriptions to be "DUPLICATED" 11510 Create RRN as a Unique Index if User Specifies "Use Unique" subscription option 11511 Check unique index on destination does not work with SQL Server 11512 Config: use Servername rather than DSN when invoking osql on 64-bit system 11515 RM: Selecting "Create target unique index using source table RRN" causes problems 11522 Improve baseline & snapshot performance by increasing size and use of buffers 11539 Make multiRowFetch=1 the default program behavior 11540 Improve error message when Orphaned subscription is detected |
| V3.65.1025 | Oct 2010 | 9917 Config: Error creating new control tables in existing database on SQL Server 2005 10414 Config: register x64/sqdrxp.dll if using 64-bit SQL Server for controlDB 10713 Installer: check for SQL-DMO 11059 Config: Upgrade should preserve all service properties 11483 conversion error (invalid datetim format) on ir_position - non US ControlDB |
| V3.65.0923 | Sept 2010 | 11319 Improve source table existance check performance 11367 Baseline fails for source-event flagged subs with confusing error message 11394 Latch END-TO-END LATENCY per Subscription from Apply processing 11403 RM, x64 Environment: Incomplete column list saved with subscription 11404 RESETTABPOS: Detect/react to RESETTABPOS failure 11412 RM - should not try to re-baseline a sub that reports that a source file has been altered or deleted 11428 Group Run leaves I/R subscriptions in final state of baseline processing 11434 support backlevel sqdrplus/udb capture agents 11435 Replicator: Group operations may leave I/R member in ss_state=0, instead of non zero state 11438 Copy: Permit destination table name to be altered using subscription prefix, suffix 11453 Trap additional error text if returned by COPYDONE 11459 - Sub stays green after failure on run for a table that is off-line, should go yellow |
| V3.64.0811 | Aug 2010 | 11415 Recovery of Baselines may fail under some circumstances |
| V3.63.0611 | Jun 2010 | 11353 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 |
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