— Various parameters that can improve the performance of the Replication server rs_configure memory_limit, ‘200’ — dsi_sqt_cache_size alter connection to DCOCCDWP.defacto set dsi_sqt_max_cache_size to ‘62914560’ go suspend connection to DCOCCDWP.defacto go resume connection to DCOCCDWP.defacto go — Cache system tables configure replication server set sts_full_cache_rs_classes to ‘on’ configure replication server set sts_full_cache_rs_databases to ‘on’ configure replication server set sts_full_cache_rs_columns to ‘on’ configure replication server set sts_full_cache_rs_config to ‘on’ configure replication server set sts_full_cache_rs_datatype to ‘on’ configure replication server set sts_full_cache_rs_functions to ‘on’ configure replication server set sts_full_cache_rs_objects to ‘on’ configure replication server set sts_full_cache_rs_publications to ‘on’ configure replication server set sts_full_cache_rs_queues to ‘on’ configure replication server set sts_full_cache_rs_repdbs to ‘on’ configure replication server set sts_full_cache_rs_repobjs to ‘on’ configure replication server set sts_full_cache_rs_diskaffinity to ‘on’ configure replication server set sts_full_cache_rs_routes to ‘on’ configure replication server set sts_full_cache_rs_systext to ‘off’ configure replication server set sts_full_cache_rs_sites to ‘on’ configure replication server set sts_full_cache_rs_version to ‘on’ configure replication server set sts_full_cache_rs_users to ‘on’ configure replication server set sts_full_cache_rs_translation to ‘on’ go rs_configure sts_cachesize, ‘3500’ go /* REP15 extra settings: — Parallel dsi alter connection to DCOCCDWP.defacto set parallel_dsi to ‘on’ go suspend connection to DCOCCDWP.defacto go resume connection to DCOCCDWP.defacto go rs_configure exec_sqm_write_request_limit, ‘983040’ go rs_configure md_sqm_write_request_limit, ‘983040’ go rs_configure smp_enable, ‘on’ go */ — Also a good idea to use disk affinity to bind connections to their own partitions: — e.g alter connection to DCO.ProductCatalog_new set disk_affinity to ‘P08’

1.       First you need to create a new directory on Unix for the installation, e.g /progs/fps/sybase15, and copy the installation tar file to the unix machine, and uncompress and untar it. If there is no room any other place on the machine just place it into a subdirectory under sybase15. 2.       Make sure that the os has the necessary patches, if not arrange for them to be installed. 3.       You will need to ask the Unix sa to create a new raw device for you,  sybsystemdbdev of about 128MB for the new sybsystemdb database, e.g /dev/vg_fps/rlv_systemdbdev and also check the size of the sybsystemprocs device, it should be at least 160MB in size, if it’s less ask the Unix sa to create a new raw device for you, e.g /dev/vg_fps/rlv_sysprocsdev2 4.       Run setup to install ASE15 into its own separate directory (setup -console ), e.g sybase15, answer no to the question of licenses, we will fix this later on. So specify: –          Change the installation directory to e.g /progs/fps/sybase15 –          Select Full installation –          If asked about licenses select Continue installation without a license key –          Enterprise Edition –          SR :  Server License –          The answer yes to continue without license. –          No to configure email alerts Check that all the options are correct, especially the install directory, and click 1 to install the files. –          Answer NO to the remember password feature question. –          Deselect all the servers from configuration, we are not building the server now. 5.       You should install the latest patch for this version of ASE which can be downloaded from sybase.com, just remove the original installation files and replace them with the patch files then run the patch. Do the patching after the sybase server is upgraded. 6.       Make sure the existing Sybase server is sourced and is displayed in DSQUERY etc then run the script /progs/fps/sybase15/ASE-15_0/upgrade/preupgrade specify the sa password for the existing server to check that everything is in place for an upgrade. This programme will let you know if you need to increase any parameters etc and also if you need to expand any databases. Answer NO at the end when it asks if you want to proceed with the upgrade, this is just a precheck. You will most probably need to extend sybsystemprocs and sybsystemdb onto the new unix devices created earlier and also extend model to at least 4MB, in fact all user databases must be at least 4MB. If everything is fien then the last two lines should say: Preupgrade of Adaptive Server to 15.0 is complete. Upgrade eligibility test succeeded. 7.       You now need to add the following 3 environment variables to the SYBASE.csh file: OLDSYBASE= current SYBASE variable OLDSYBASE_ASE=current SYBASE_ASE OLDSYBASE_OCS=current SYBASE_OCS 8.       Source the new SYBASE.csh and create a resource file in $SYBASE/$SYBASE_ASE/init/sample_resource_files from the template where you fill in the values for the old $SYBASE path,  Sybase server name, Login (sa), and the password for current sa login on existing server. 9.       Turn off auditing on the existing sybase server and completely uninstall the sybsecurity database and sybaudit and reboot the Sybase server. Do an sp_configure “auditing”, 1 to make sure the auditing subsystem is switched off, VERY IMPORTANT!! 10.   You then run the upgrade with $SYBASE/$SYBASE_ASE/bin/sqlupgraderes –r $SYBASE/$SYBASE_ASE/init/sample_resource_files/<resource file>

Upgrade the RSSD database, start with the ID server.

• Install the new software for replication server version 15 on your system. Put it in a new directory e.g /progs/torigodb/syb_rs15. After gunzipping and untarring the rep server tar ball run ./setup –console:

–          Accept the license agreement and select Full installation. –          Answer no to “Will licenses be obtained from the License server”. –          No to configure email alerts –          No to start a sample rep server

• You can then install any required Sybase patches, e.g ESD1
• Generate a license key (through sybase.subscribenet.com) and store it in the SYSAM-2_0/licences directory.
• Copy any custom scripts over from the old repserver, e.g .cshrc, repscripts etc, and modify as necessary, i.e change SYBASE path etc.
• Run “rs_helpsub” from the rssd database to verify that all subscriptions are valid. The status only needs to be Valid at the replicate replication server.
• Copy the interfaces file (or interfaces) from the pre version 15 to the version 15 installation.
• Log on the RSSD ASE server and grant the primary user the sa_role “grant role sa_role to <username>” if it doesn’t already have it. You can determine the name of the primary user by looking into the configuration file, at the keyword RSSD_primary_user.
• Log on to the replication server and run “admin health” to see if everything is ok.
• Shutdown the replication server, backup the RSSD database then restart the replication server.
• Leave repserver running.
• Set the environment variable $SYBASE to the new REP15 installation directory
• Set the environment variable SYBASE_REP to REP-15_0
• Start $SYBASE/$SYBASE_REP/install/rs_init (rs_init)
• Select “Configure a Server product” -> “Replication server” -> “Upgrade an existing replication server”.
• Answer all the questions. The configuration file should be the path and filename of the old config file.
• Do Ctrl-A to start.

After a succesfull upgrade

• Repoint the replication server paths to the new installation and copy the old runserver file to it’s new location. Make sure to change the paths in the runserver file to pick up the new version 15 executable and correct config files etc. Also copy over the old config file.
• Fix or copy any other files bespoke files, You need to update the file $SYBASE/config/objectid.dat file and add a line similar to

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.

How to add/update a table for replication

–          First you need to create two scripts one to drop the existing subscription and replication definition and one to create the new table, replication definition and subscription. –          You also need to extract the exiting replicant table and also a user, e.g.  origomw, if requested and save this info. –          Before stopping replication send an email to operations to let them know of the planned downtime. –          At this point you should suspend the replication server connection from DB2, if it’s not already down and wait 5 minutes to make sure everything is applied. –          Do a select count(*) against an existing replicant table and compare this with a wc –l of the bcp file to make sure they match in number. For a new table this is not relevant. –          Bcp out the existing table just in case something goes wrong. –          Drop a user if that was requested –          Drop subscription, rep def in rep server: o   drop subscription DB2_ASN2_RESOURCE_NB_s2 for DB2_ASN2_RESOURCE_NB_d2 o   with replicate at origo_test.deFaktoReplica o   without purge o   go o o   drop replication definition “DB2_ASN2_RESOURCE_NB_d2″ o   go –          Drop the table in ASE o   Use database deFaktoReplica o   Go o   Drop table ASN2_RESOURCE_NB o   Go –          Create the table again –          Bcp the data in with a command like: o   bcp deFaktoReplica.dbo.ASN2_RESOURCE_NB  in /home/origo/deFaktoReplica/init/data/SYS3.ASN2.DDX -r ” \n” -t “\t” -c -Jiso_1 -Sorigo_test –Usa –P<password> -e bcplog.txt -z -b 10000 -m 100000 o   If you get problems check that the number of columns etc match and also check to see whether the row/tab delimiter is correct, sometimes there is an extra tab in the row delimiter, in that case try using –r “\t \n”. –          Add the primary key, triggers and other indexes etc into the table, and possibly also a rep server specific column like changed_date. –          Add the origomw user to the database. –          Add the replication definition and subscription. o   create replication definition “DB2_ASN2_RESOURCE_NB_d2” o   with primary at TSTA.P825RAD1 o   with primary table named “DB2_ASN2_RESOURCE_NB” o   with replicate table named “ASN2_RESOURCE_NB” o   ( o   “RESR_ELMT_ID”          int, o   “RECORD_EFF_END_DT”     datetime, o   “RECORD_EFF_END_TM”     datetime, o   “RECORD_EFF_STR_DT”     datetime, o   “RECORD_EFF_STR_TM”     datetime, o   “SRVC_LOC_ID”           numeric, o   “RESR_TYPE”             char(6), o   “RESR_ID”               char(22), o   “ACCT_ID”               numeric, o   “RESR_EFF_STR_DT”       datetime, o   “RESR_EFF_END_DT”       datetime, o   “ORD_ITEM_ID”           numeric, o   “FST_USG_DT”            datetime, o   “REINSTATE_CD”          char(4), o   “REINSTALL_DT”          datetime, o   “DISCN_CD”              char(4), o   “ASN_RESR_DISCN_DT”     datetime, o   “VERBAL_TRANSLATION”    char(20), o   “COMMENT_ID”            numeric, o   “REF_SEQ_NUM”           numeric, o   “INIT_INSTALL_DT”       datetime, o   “LAST_CHANGE_DT”        datetime, o   “RESR_GRP_TYP”          char(6), o   “ORD_ITEM_SEQ”          smallint, o   “PRIORITY_CD”           char(1), o   “SUB_STATUS_CD”         char(2), o   “PRMRY_COMP_CD”         char(6), o   “SECNDRY_COMP_CD”       char(6), o   “NUFS_NET_SRVC_TYP”     char(3), o   “NUFS_NUM_CAT”          char(2), o   “USER_ID”               char(8), o   “PREV_PHONE_NUM”        char(8), o   “RSU”                   char(6), o   “PRMRY_GRP”             smallint, o   “FSL”                   char(6), o   “CALL_INTERCEPT”        char(1), o   “RESR_SUB_GRP”          char(1), o   “OWNER_ACCT_ID”         numeric, o   “OWNER_SRVC_LOC_ID”     numeric, o   “PAYER_REF”             char(30), o   “PAYER_KURT_ID”         int, o   “CREATORS_REFERENCE”    char(25), o   “UPDT_LAST_MOD_TS”      datetime o   ) o   primary key o   ( o   “RESR_ELMT_ID”, o   “RECORD_EFF_END_DT”, o   “RECORD_EFF_END_TM” o   ) o   searchable columns (RECORD_EFF_END_DT) o   go o o o   define subscription DB2_ASN2_RESOURCE_NB_s2 o   for DB2_ASN2_RESOURCE_NB_d2 o   with replicate at origo_test.deFaktoReplica o   where RECORD_EFF_END_DT = ’31 dec 9999′ o   go –          Activate the subscription with: o   activate subscription DB2_ASN2_RESOURCE_NB_s2 o   for DB2_ASN2_RESOURCE_NB_d2 o   with replicate at origo_test.deFaktoReplica o   go –          Validate subscription with: o   validate subscription DB2_ASN2_RESOURCE_NB_s2 o   for DB2_ASN2_RESOURCE_NB_d2 o   with replicate at origo_test.deFaktoReplica o   go –          Resume replication and ask DB2 admin to start the Repagent on their side.

Installing RepServer 15

–          Untar the repserver installation files into a seperate directory and install the binaries etc by running setup –console. Answer no to email alerts and don’t enter license information. –          If you are migrating from an earlier version of Repserver and you have already migrated the ASE server then you need to make sure that the rep server logins are not expired, (e.g REP_dhtsolo_RSSD_prim and REP_dhtsolo_RSSD_maint). If they are expired, just try to login to the ase with them, you will need to change the passwords twice, first to a temp password and then back to the original. –          If you are using Norwegian language then you will also need to add the nocase_iso_1_nor.srt file into $SYBASE/charsets/iso_1 and tweak the $SYBASE/config/objectid.dat file by adding the following or similar line at the bottom of the collate section: o           1.3.6.1.4.1.897.4.9.3.148 = nocase_iso_1_nor Find the final number 148 by doing an sp_helpsort in the ase server and looking for the number associated with nocase_iso_1_nor –          Follow the regular steps for installing a repserver, i.e rs_init which should be fairly straight forward. When configuring the new rep server set the sort order to nocase_iso_1_nor