REP701 : Connecting the Enterprise: Heterogeneous Replication for Various Database Sources

1. REP701 : Connecting the Enterprise: Heterogeneous Replication for Various Database Sources Nanci Reinhart Senior Consultant, Sybase reinhart@sybase.com August 15-19, 2004

2. What Will I Need? • Sybase Products Needed • Adaptive Server (ASE) • Replication Server • Replication Agent • Direct Connect • jConnect AKA: Replication Server with Options

3. in out Oracle Prim Direct Connect ASE Rep Agent Rep Server Rep Server Rep Agent out in ASE Direct Connect Oracle Rep

4. in out Oracle Prim Direct Connect ASE Rep Agent Rep Server Rep Server ASE

5. Installation • JDBC Driver for Oracle • ASE Server • Replication Server • Direct Connect for Oracle • Replication Agent for Oracle

6. Direct Connect Install (Oracle Option) • Create tnsnames.ora for Oracle connection string • Variable TNS_ADMIN – specify location • Default 12.0 - $SYBASE/DCO-12_0 • Default 12.5 - $SYBASE/DCO-12_5/network/admin

7. Direct Connect Install (Oracle Option) • Customize the serv_name.cfg file • Version 12.0 – connect string: • @user.dbname.prod • Version 12.5 – connect string: • User.dbname.prod Test connection with some sql!

8. Replication Agent Install Path/Classpath variable must include: vs 12.0 /path_to_jdbc/lib/classes111.zip vs 12.5 /path_to_jdbc/lib/classes12.zip Create the Replication Agent with ra_admin –c name –t oracle –p port_num Start the Replication Agent % administrator Configure (from worksheets)

9. Replication Agent - JDBC version 12.5 Replication Agent for Oracle requires JDBC 2.0 Compliant Driver versions of Replication Agent of 12.1 and older used JDBC 1.0 Compliant Driver

10. Heterogeneous Database Support HDS: Scripts in $SYBASE/REP-12_x/scripts

11. HDS Run on Oracle Server (Using Direct Connect or SQL Plus as the maintenance user) hds_oracle_setup_for_replicate.sql Change the use rssd_name line on the following scripts and run on ASE hds_oracle_udds.sql hds_oracle_funcstrings.sql hds_clt_ase_to_oracle.sql

12. in out Oracle Prim Direct Connect ASE Rep Agent Rep Server Rep Server Rep Agent out in ASE Direct Connect Oracle Rep

13. Connections In the Replication Server Create user that Direct Connect will use User must have permissions on the Oracle Database Grant connect source to this user Create the connection between Replication Server and Replication Agent Create the connection between Replication Server and Direct Connect

14. Routes Create route between Replication Servers

15. What About A Transaction Log? DB2 - Read from transaction Log Directly Informix, MS SQL, and Oracle: Create objects in the database to support (mimic) transaction log activity

16. What About A Transaction Log? Sybase log behavior imitated by Oracle Tables Create tables and objects using the Rep Agent: pdb_xlog create Verify: pdb_xlog (will echo back items created) Mark table for replication: pdb_setreptable tableName, mark

17. Can I See These Scripts? Scripts located in: $SYBASE/rax-12_x/ra_name/scripts

18. Replication Definitions & Subscriptions Use Standard Replication Definitions Use Standard Subscriptions Take it for a test drive!

19. Gotchas: Wide columns Version 12.5 and higher – no problem! Good up to 32K Version 12.0 – work around: Put an extra table in the mix…… LOBS and CLOBS

20. Gotchas: Is This Normal? Replication Agent and/or DSI threads are down: It is normal for the 2 DSI threads to the Rep Agent to be down or suspended The ones to Direct Connect should be up!

21. Gotchas: Down Replication Agents Replication Agent for Oracle is down: Run the “resume connection” command in the Rep Server Run the “resume” command in the Rep Agent Replication Agent for the RSSD is down: In the RSSD (ASE) Run sp_start_rep_agent

22. Gotchas: Didn’t I Drop That???? The case of the reappearing Replication Definition and/or subscription: During testing ONLY, in Rep Agent: ra_config use_rssd, false

23. What Is It Doing? (Direct Connect) Direct Connect Trace Flags: In the config file: network tracing = 1 traces = 1,2,3,4,5,6,10 Messages will be in DCO.log

24. What Is It Doing? (Replication Agent) Replication Agent Trace Use isql and execute: trace all, true Output will be in $SYBASE/rax-12_x/name/log directory

25. What Is It Doing? (Replication Server) trace “on”, dsi, dsi_buf_dump

26. Replication: Diagnostic Server Copy the Replication Server runfile Change the ../../repserver line to: ../../repserver.diag Shutdown and restart the Replication Server View execution commands by: trace ‘on’, “exec”, exec_trace_commands Output in Rep Server log file

27. What’s in the Queue? sysadmin sqt_dump_queue, 103, 1, 0 Or sysadmin dump_file, “/tmp/myDumpFile” sysadmin dump_queue, 103, 0, -1, -2, -2 sysadmin dump_file

28. How Do I Get Rid of It? Empty it out! sysadmin purge_all_open, 103, 1

29. Commands “Stuck” In Rep Agent Find the shadow table in the Oracle database and delete the entries. In the Replication Agent run: quiesce ra_locator zero

30. ??????????????????????????? Questions?

31. SYBASE Replication with Options Making connections and communications easier.