DM239 Replication Strategies for Heterogeneous, Bi-directional Data Movement

1. DM239Replication Strategies for Heterogeneous, Bi-directional Data Movement • Greg Carter • Staff Software Engineer • Enterprise Solutions Division • greg.carter@sybase.com

2. Familiarity with Sybase’s transaction replication solution Require replication to/from non-ASE data servers Familiarity with the functions of non-ASE Replication Agents Familiarity with the functions of DirectCONNECT Open Server data server gateways Assumptions

3. Basic ConceptsComponent Overview RSMC RSMS RDS RRS PRS PDS DirectCONNECT RepAgent PDS PRS RRS RDS RepAgent DirectCONNECT RSSD RSSD

4. Basic ConceptsPrimary-side Components • PDS • “Marked” Objects • Transaction Log • Transaction Log Markers • RepAgent • TranLog positioning and scanning • Maintenance User transaction filtering • LTL Generation RepAgent PDS

5. Basic ConceptsReplication Server • Connection Management • Object Publication/Subscription • RepAgent Control Information • Last received transaction • Maintenance User • RepDef information? • Function Interpretation (SQL Generation) • Datatype Translation RS RSSD

6. Basic ConceptsReplicate-side Components • DirectCONNECT • Non-ASE data server connectivity for RepServer and RSMS • Some localization • RDS • Replicate Objects • RepServer control information • Character-set/Sort-order • Last committed transaction • Parallel DSI thread synchronization RDS DirectCONNECT

7. Basic ConceptsManagement Components • RSMC • GUI monitoring and management of replication components • RepServer script generation • RSMS • System configuration/meta-data collection and interpretation • Route upgrade RSMC RSMS

8. Datatypes Native Datatypes “Natively” supported by RepServer Largely based on OCS Datatypes Generally speaking, attributes of these datatypes are unalterable The Details RepServer Issues

9. User Defined Datatypes Defined in terms of - and so inherits attributes from - a Native Datatype Some attributes are user alterable, hence “User Defined” Delimiters Length/precision & scale Max/min boundaries Length and boundary error actions Content description - masks The Details RepServer Issues

10. User Defined Datatypes (cont) Some may require a length specification text, image, rs_address and raw may not be used as Native Datatypes for a UDD Associated with a Datatype Class Install the supplied UDDs for the data servers involved The Details RepServer Issues

11. Database Connections One or two? In or out or both? The DetailsRepServer Issues RRS DirectCONNECT PRS RepAgent

12. Know your DirectCONNECT TransactionMode DelimitSQLRequests SQLTransformation TargetDecimalSeparator Duplication Detection rs_lastcommit rs_get_lastcommit Database Information rs_get_charset, rs_get_sortorder The Details RDS Issues

13. Database Selection rs_usedb Transaction Control rs_begin rs_commit Updates rs_lastcommit Uses System Defined Variables - binary and datetime values rs_rollback The Details RDS Issues

14. Database Connections (again) Error and Function String Classes Class-level Translations Defined for a Function String Class Not inheritable Only way to define System Variable translations Refreshed when DSI suspended and resumed A bit of a performance hit The DetailsRepServer Issues

15. Maintenance User Database specific configurations batch dsi_cmd_separator parallel_dsi dsi_sql_data_style The DetailsRepServer Issues

16. Know your RepAgent Transaction Filtering Datatype Translations Using the RSSD Marking Objects The Details PDS Issues

17. Replication Definitions Declaring columns Multiple repdefs Column filtering Column-level translations Defined per column in the “create replication definition” or “alter replication definition” command using “map to” option Not valid for function repdefs The Details RepServer Issues

18. Subscriptions Availability of UDDs Which “where”: Row filtering Automatic vs. Manual Materialization rs_marker Used to place markers in the PDB Transaction log Markers are currently used for Indicating when a subscription can be activated during atomic materialization Indicating when a subscription can be validated during non-atomic materialization The Details RepServer Issues

19. Performed when Both source and target are Native Datatypes Source is a trivial UDD and target is a ND (source is trivial if it does not have a user specified mask) Source is a ND and target is a trivial UDD (target is trivial if it only has user defined delimiters) Performed by cs_convert (see OCS doc for valid conversions) A Closer Look at Datatype TranslationTrivial Translations

20. Performed when either the source or the target is a non-trivial UDD. Four different types: Format only Base Datatype* 1-to-1 Temporal* A Closer Look ..Non-Trivial Translations

21. Declared Datatype refers to the datatype - Native or UDD - used to declare a column in a repdef If multiple repdefs for one table, all Declared Datatypes for a given column must be identical RepAgent must deliver data delimited as for the Base Datatype of the Declared Datatype Used for subscription resolution A Closer Look ..Declared vs. Published vs. Delivered

22. Published Datatype refers to the datatype - Native or UDD - defined for a column in the “map to” clause If multiple repdefs for one table, Published Datatypes for a given column may be different Defaults to the Declared Datatype A Closer Look ..Declared vs. Published vs. Delivered

23. Delivered Datatype is the datatype that is generated by the DSI for use in the RDB command May be same as Declared Datatype if there is no Column or Class-level translations May be same as Published Datatype if there is no Class-level translation A Closer Look ..Declared vs. Published vs. Delivered

24. Very little validation of data as delivered by the RepAgent Column and Class-level translations are performed in the DSI - in that order - just prior to message delivery A Closer Look ..When and Where

25. Function String Classes added for each data server for which we have or plan to have a RepAgent These are both default classes and inherit from rs_default_function_class Class-wide Function Strings are provided that will work with default DirectCONNECT installation MiscelleaneousFunction String Classes and Function Strings

26. Automatic de-materialization not allowed if subscription “where” clause references a column subject to Column or Class-level translations Translations not performed for request functions - UDD delimiters used, however Translations not performed for WSB No HDS in rs_subcmp MiscelleaneousExceptions

27. Use admin translate command Use trace=dsi,dsi_cmd_dump Translation engine traces include trnsl,general_1 trnsl,general_2 trnsl,consistency_1 trnsl,consistency_2 rsfeature,rsfeature_trnsl MiscelleaneousTrouble Shooting Translations

28. User interface to create UDDs User interface to create Class-level translations Better handling of System Variables Enable support for char and binary values greater than 255 bytes Support for more RDBs as primary - DB2 UDB MiscelleaneousFutures

29. Multi-Vendor Replication with Sybase Replication Technology A white paper available at http://sdn.sybase.com/sdn/esd/search_keyword.stm?keyword=esd_prod_move More Information