To perform any heterogeneous replication, replication to and from two different datasources e.g Sybase to Oracle or DB2 to Oracle, you need to use Direct Connect or as it’s now being called Enterprise Connect Data Access.
The below example is how to set up a connection to a new Oracle database but it can be applied to other databases as well obviously with some different scripts etc.
In the below example Direct connect is already installed but unloading the software is not so difficult anyway, please refer to product manuals for details.
1. First you will need to create a new Direct Connect process by creating a new config file in the $SYBASE/$SYBASE_DCO/install directory on the server where Direct Connect is installed. Just copy the existing .cfg file and RUN file and alter to them to reflect the new settings. So in the RUN file just change the servername and in the .cfg file add the new servername, username (in this case the oracle username), the path to the new errorlog file and the connect_string.
2. Add an entry for this new Direct Connect process in the local interfaces file like:
DCOCCDWP
master tcp ether p-***-tm16 ****
query tcp ether p-***-tm16 ****
3. Also add this into the interfaces file for the REPServer and the ASE server which is acting as the RSSD for the Repserver.
4. Add the Oracle connection information into the tnsnames.ora file which is in $SYBASE/$SYBASE_DCO/network/admin :
DWMU =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ***.**.***.**)(PORT = ****))
)
(CONNECT_DATA =
(SERVICE_NAME = DWMU)
)
)
5. You can now start the new Direct Connect process by running e.g startserver –f RUN_DCODWMU.
6. You can check that it is working by trying to log into the oracle tablespace with e.g isql –Udefacto –SDCOCCDWP –P<oracle password>.
7. You now need to go to the Repserver and make a copy of the following files in the $SYBASE/$SYBASE_REP/scripts directory :
hds_oracle_udds.sql
hds_clt_ase_to_oracle.sql
hds_oracle_funcstrings.sql
hds_oracle_setup_for_replicate.sql
hds_oracle_connection_sample.sql
For the first three scripts you need to edit the scripts and enter your RSSD database name and execute the scripts against the relevant ASE server housing the RSSD database for the Repserver, e.g isql –Usa – Sdhp_SOLP –DREP_dhpsolo_RSSD -i$SYBASE/$SYBASE_REP/scripts/DCOCCDWP_ hds_oracle_udds.sql
The script hds_oracle_setup_for_replicate.sql needs to be copied over to the direct connect unix box and is run via Direct Connect against the replicate database, e.g isql -Ucm –P<oracle password> -SDCODWMU -ihds_oracle_setup_for_replicate.sql
8. The last step is to create the connection to the Oracle database from the repserver and that is done by modifying the hds_oracle_connection_sample.sql script so that it looks similar to:
create connection to DCOCCDWP.defacto
set error class to rs_sqlserver_error_class
set function string class to rs_oracle_function_class
set username to *******
set password to **********
set ‘batch’ to ‘off’
go
You then run this script against the repserver.
9. To test that everything is ok you can try to connect to the oracle database form the replication server with e.g isql –Udefacto –SDCOCCDWP –P<oracle password>
If this connects then everything should work, at least in terms of connectivity. If it doesn’t work then you will probably have to open up the firewall both ways between the replication server port and the direct server port, e.g between pkg_solp (***.**.***.***) repserver port (2040) and dhp-tm16 (**.***.*.**) dco port (****).
10. You then continue to define the rep defs, subscriptions etc.