Home

View this document as PDF

Contents

1. 000086 50 Configuring Replication Agent cceee 54 Testing Replication a wiiseterccrintinccciees enue oa 66 Resetting the Primary Microsoft SQL Server Database for Replication eee 67 IBM DB2 Universal Database Replication Setup 68 SYSTEM ALCHITECTUNE oreraa niiret iteraria 68 Prerequisites set sede ee ete is i lead eth eh hed ee 69 Planning Your Installation eeeeeeeeeeeeeeeeeeeeeeeee 69 System Requirement cccceceeeeeeseeeeesees 69 Identify the Sybase Installation Directory 70 Accessing and Downloading Products from SP OEE E cons obasmenceers cand ene tone Gree humetiee 70 Installing Replication Component cccccceeeeeeees 72 Installing ECDA Option for ODBC 68 72 Installing Replication Server ceeeeeeeeceeeereeees 75 Installing Replication Agent cecceeeeeeeees 79 Configuring Replication Component c ccccccee 82 Setting IBM DB2 Universal Database Environment Variables cccecceeeeeeeeeeeeees 83 Node and Database Cataloging in an IBM DB2 Universal Database Client eeee 84 Configuring ECDA Option for ODBC 87 Configuring Replication Server 0 0006 91 Configuring Replication Agent ceee 96 Testing RepliCatiOnl cctsay icant ee tweet net eee bee 105 Resetting the Primary IBM DB2 Universal Database for Re
2. 1 On UNIX or Linux navigate to the server or client sq11ib directory which is located in the IBM DB2 Universal Database instance owner user home directory For example on UNIX cd db2home db2inst1 sqllib 2 On UNIX or Linux set the IBM DB2 Universal Database environment variables by sourcing the appropriate script 84 Replication Server Options IBM DB2 Universal Database Replication Setup e For C shell source db2cshre For Bourne or Korn shell db2profile If necessary log in to the operating system as the IBM DB2 Universal Database instance owner Logging in sets up all the IBM DB2 Universal Database environment variables Note On Windows the IBM DB2 Universal Database installation sets up all necessary environment variables 3 Start the IBM DB2 Universal Database command line processor CLP e On UNIX or Linux enter db2 On Windows select Start gt Programs gt IBM DB2 gt Command Line Tools gt Command Line Processor 4 Determine if the desired IBM DB2 Universal Database node has already been cataloged At the CLP prompt enter list node directory If a node has already been cataloged depending on whether the node is local first entry below or remote second entry below you see results similar to this ode 1 entry Node name DB2NODE1 Comment Directory entry type LOCAL PROmOCOM LOCAL Instance name DB2_01 Node 2 entry Node name DB2NODE2 Comment Directory e
3. In a command window change to the directory where you installed Replication Agent 2 Change to 3SYBASE RAX 15_5 bin Obtain the Replication Agent version string 1EGh WE If the Replication Agent installation completed successfully you see the Sybase copyright and the Replication Agent version string 46 Replication Server Options Microsoft SQL Server Replication Setup Configuring Replication Components Review the procedures for configuring each of the replication components in the replication system See also e Testing Replication on page 66 Configuring ECDA Option for ODBC Review the procedures for configuring ECDA Option for ODBC Configuring the Microsoft SQL Server ODBC Driver Configure the ODBC driver to connect to the replicate database The ODBC driver is installed with the installation of Microsoft SQL Server Note If you already have a data source configured for your replicate database skip to Configure and Start the DirectConnect Server 1 Open the ODBC Driver Manager by navigating to Control Panel gt Administrative Tools and double clicking the Data Sources ODBC icon 2 Click the Drivers tab and verify that the Microsoft SQL Server driver is version 2000 86 1830 00 If it is an earlier version go to the Microsoft Web site to download and install the correct version 3 Click the System DSN tab click Add and select the Microsoft SQL Server driver Click Finish 4 Inthe Name f
4. x setup exe where x is your CD drive e On Linux or UNIX if auto mount is not available on your machine follow local procedures for mounting the CD ROM and enter setup bin The Welcome window appears 3 Click Next You may see the following error message Error writing file There may not be enough temporary disk space Try using is tempdir to use a temporary directory on a partition with more disk space If so set the temporary directory to another directory that has more disk space by entering the following at the command line e Windows x setup ex is tempdir lt directory_name gt e UNIX or Linux setup bin is tempdir lt directory_name gt where lt directory_name gt is the name of the temporary directory to which the installation program will write its temporary files 4 Read the License and Copyright Agreement Using the drop down list at the top of the window select the geographic location where you are installing ECDA to display the agreement appropriate to your region Select I agree and click Next You must agree to the license and copyright agreement before you can continue 5 In the installation directory window click Next to accept the default directory for the installation or enter a different directory where you want to install ECDA One of the following occurs e Ifthe installation directory you chose does not exist the installation program prompts The directory does not ex
5. 4 Start the installation program On Microsoft Windows platforms the installation program should start automatically If it does not select Start gt Run and enter x setup exe where x is your media drive You can also start the installation program from Microsoft Windows Explorer by double clicking the setup exe file 5 Click Next Note You can click Cancel to stop the installation at any point 6 Select your geographic location 7 Read the Sybase license agreement and select I agree to the terms of the Sybase license for the install location specified Note You must agree to the terms of the software license before you can continue 44 Replication Server Options Microsoft SQL Server Replication Setup Click Next 8 Specify an installation directory The default installation directory is the existing SYBASE or c sybase Click Next to accept the default installation directory or e Click Choose to select an installation directory in the file browser Click Next e Enter a directory name Click Next If the directory name does not exist click Yes when prompted to create it The directory does not exist Do you want to create it If the directory you specified exists you see Warning You have chosen to install into an existing directory If you proceed with this installation any older versions of the products you choose to install that are detected in this directory will be replaced
6. 4 Verify the Replication Server version admin version go 5 Exit the isql session with the quit command Installing ExpressConnect for Oracle Install ExpressConnect for Oracle using the setup program 1 Launch the setup program e On Windows the setup program should start automatically If it does not select Start gt Run and browse to setup exe e On UNIX at the command prompt enter cdrom setup bin The Welcome window appears Click Next to proceed 2 Select the geographic location where you are installing to display the agreement appropriate to your region Read the End user License and Copyright Agreement Select I agree to the terms of the Sybase license for the install location specified and click Next Quick Start Guide 11 Oracle Replication Setup 3 Enter the directory where you want to install ECO Note ECO must be installed in the same directory as Replication Server Accept the default installation directory or click Choose to select another directory If the directory does not exist the installation program prompts you to create it Click Yes Ifthe installer does not detect an existing installation of Replication Server in the specified directory you see an error message Click Previous to go back and choose another installation directory and then click Next Review the information and click Install When the installation is complete you see a message indicating that ECO has
7. Creating an IBM DB2 Universal Database User and Granting Permissions Create an IBM DB2 Universal Database user and grant the necessary permissions Replication Agent requires an IBM DB2 Universal Database login that has permission to access data and create new objects in the primary database The IBM DB2 Universal Database login must have SYSADM or DBADM authority to access the primary database transaction log 1 Create a new operating system user named ra_userusing commands appropriate for your operating system For example this command can be used to create a user named ra_useron the UNIX and Linux operating systems useradd gusers Gmgmt s bin shell psybase d home ra_user m ra_user where password is the password corresponding to the ra_user user name 2 Select Start gt Programs gt IBM DB2 gt Control Center 3 Configure the Control Center administration tool to connect to the primary IBM DB2 Universal Database according to the IBM documentation Once the Control Center administration tool has been configured the primary database appears in the All Databases view 4 Inthe Control Center administration tool display window right click the primary database name and select Authorities from the context menu 5 Inthe Database Authorities window click Add User 6 Inthe Add User window select ra_user from the list of displayed operating system users and click OK 7 Click Grant All to add necessary authorit
8. REPLICATING Ready to replicate data Replication Agent goes to the REPLICATION DOWN state if an error has occurs 1 gt ra_status 2 CO State Action REPLICATION DOWN Waiting for operator command Validate that both primary and replicate connections are active isql Usa P SSAMPLE_RS admin who go Note Be aware that e The DSI connection for the primary database connection is usually down because you are not replicating data back to the primary database e The Replication Agent connection if established for the replicate database connection is usually down because you are not replicating data from the replicate database Do not proceed until admin who returns thread status similar to Spid Name State Info 13 DSI EXEC Awaiting Command 101 1 SAMPLE _RS_ERSSD SAMPLE_RS_ERSSD 9 DSI Awaiting Message 101 SAMPLE_RS_ERSSD SAMPLE_RS_ERSSD 8 SQM Awaiting Message 101 0 104 Replication Server Options IBM DB2 Universal Database Replication Setup SAMPLE _RS_ERSSD SAMPLE_RS_ERSSD 54 DSI EXEC Awaiting Command 102 1 DCService rdb 53 DSI Awaiting Message 102 DCService rdb 17 SQM Awaiting Message 102 0 DCService rdbDSI EXEC Suspended 103 1 NY NYudb9 DSI Suspended 103 NY NYudb9 24 DIST Awaiting Wakeup 103 NY NYudb9 25 SOT Awaiting Wakeup 103 1 DIST NY NYudb9 23 SQM Awaiting Message 103 1 NY
9. Replication Server Options Oracle Replication Setup EJ LOG_MOD ARCHIVELOG 3 To turn on log archiving shutdown SEa C OCINE e alter database archivelog alter database open See the Replication Agent Primary Database Guide Verifying the Supplemental Logging of Primary Key Data Verify that Oracle logs the values of the primary keys and unique indexes By default Oracle does not log primary keys and unique indexes For successful replication of all table values you must log these values 1 Use SQLPLUS to connect to Oracle as a system administrator SELECT SUPPLEMENTAL LOG DATA MIN SUPPLEMENTAL _LOG_DATA_PK SUPPLEMENTAL LOG DATA UI FROM VSDATABASE If logging of primary key and unique index values is enabled the return values are SUP SUP SUP SIRS ATS ARS 2 Ifthe result is different turn on supplemental logging by executing ALTER DATABASE ADD SUPPLEMENTAL LOG DATA PRIMARY KEY UNIQUE INDEX COLUMNS ALTER DATABASE ADD SUPPLEMENTAL LOG DATA Creating Oracle User and Grant Permissions Create an Oracle database user for use by Replication Agent when connected to the primary database Note Permission to grant access to objects owned by SYS may require the command to be executed by an Oracle user with sysdba privileges Quick Start Guide 21 Oracle Repli
10. Open Control Panel gt System click the Advanced tab and click Environment Variables In the System Variables window if the CLASSPATH variable is not listed click New Otherwise select it and click Edit Enter the full path of the JDBC driver using a semicolon to separate it from any other drivers Click OK three times Configuring and Validating the Replication Agent Resource File A single installation of the Replication Agent can support replication from multiple databases However one Replication Agent instance is needed for each Microsoft SQL Server database that is to be replicated 1 2 Locate the resource file template The majority of configuration values required to create and initialize a Replication Agent can be recorded and stored in a resource file Using a resource file provides a means to record or retain the configuration information for a Replication Agent instance allowing an instance to be removed and re created The Microsoft SQL Server resource file template is at 3SYBASE RAX 15_5 init mssql rs Create an instance resource file 58 Replication Server Options Microsoft SQL Server Replication Setup Copy the resource file template 3 SYBASE RAX 15_5 init mssql rs to another file for example myra rs Change the values assigned to properties in the resource file so they match the values required for your environment The majority of these values are host port user and
11. Set the IBM DB2 Universal Database environment variables For UNIX and Linux the IBM DB2 Universal Database installation provides two scripts for setting up the IBM DB2 Universal Database environment variables db2cshrc for C shell and db2profile for Bourne or Korn shell These scripts set the library path environment variable based on the bit size of the installed server or client Note On Windows the installation sets all necessary environment variables For UNIX and Linux platforms the 32 bit and 64 bit versions of the driver and API libraries are located in SHOME sqllib 1ib32 and SHOME sqllib 1ib64 respectively where HOME is the home directory of the IBM DB2 Universal Database instance owner If Replication Agent is installed on AIX Solaris or HP Itanium the library path environment variable must point to the 64 bit libraries For all other platforms the library path environment variable must point to the 32 bit libraries Note If Replication Agent is installed on Solaris AIX or HP Itanium you must configure a 64 bit IBM DB2 Universal Database server or client instance To correctly set the IBM DB2 Universal Database environment variables for Replication Agent and ECDA you may need to customize either db2cshrc or db2profile However make any changes to saved copies of the original scripts because the scripts may be changed when you apply a FixPak to the IBM DB2 Universal Database server or client Note Replication
12. Agent If your resource file has initialize_instance set to no open a new command window log in to the Replication Agent instance and initialize the Replication Agent instance and the primary Microsoft SQL Server database using this Replication Agent command pablo Gg mike Your Replication Agent instance is now running and has been initialized Verifying the Replication Agent Replicating State Verify the replicating state of Replication Agent 1 Enter resume go If the Replication Agent successfully transfers to a replicating state you see State Action REPLICATING Ready to replicate data The Replication Agent goes to the REPLICATION DOWN state if an error occurs 1 gt resume 2 gt go Msg 32000 Level 20 State 0 Server rao Procedure resume Line 1 Command lt resume gt failed Desired state lt REPLICATE gt could not be achieved Current state lt REPLICATIO DOWN gt The ra_status command returns the state of the Replication Agent It is good practice to verify that the Replication Agent remains in replicating state even after the resume command executes successfully To detect an error that occurred after replication start up execute ra_status go If the Replication Agent is in replicating state ra_status returns State Action 64 Replication Server Options Microsoft SQL Server Replication Setup REPLICATING Ready to replicate data Replica
13. Agent instance In your resource file if you also configured initialize_instance to yes this step also initializes the Microsoft SQL Server and the Replication Agent instance After the resource file has been validated use the ra_admin utility to create the Replication Agent instance using the r parameter For example SSYBASE RAX 15_5 bin ra_admin r myra rs Execution results are returned as either e Response file processing completed Or e Response file processing completed with errors Quick Start Guide 61 Microsoft SQL Server Replication Setup See the Replication Agent Administration Guide Ifyour resource file set start_instance to no change to the SYBASE directory and start the Replication Agent instance cd SYBASE RAX 15_5 myra Execute the RUN file for example RUN_myra Use the dsedit utility to update the Replication Server sql ini file with the Replication Agent location and verify the connection to the Replication Agent a Open a command window in the SYBASE directory of your Replication Agent installation b Set the environment variables by executing the SYBASE bat file c Log in to Replication Agent LSC SUSE 2 SAE where myra is the name of Replication Agent Ifyour resource file has initialize_instance set to no initialize the primary data server and the Replication Agent instance a Open a command window and log in to the Replication Agent
14. Agent requires 32 bit libraries for Linux and Windows and 64 bit libraries for UNIX Because ECDA may have different requirements you may want to maintain two separate customized copies of the aforementioned scripts one copy for Replication Agent and one for ECDA 1 Navigate to the IBM DB2 Universal Database server or client sq11ib directory which is located in the database instance owner user home directory For example cd db2home db2inst1 sqllib 2 Based on the type of UNIX shell you are using copy either the db2cshrc or db2profile script e For C shell cp db2cshre mydb2cshre For Bourne or Korn shell cp db2profile mydb2profile 3 Edit your copy of the file Add a statement at the very end of the file that sets the platform specific library path variable to point to the 32 bit IBM DB2 Universal Database libraries For example on Solaris or Linux add these lines e For C shell inmydb2cshrc ip ence S2 onic MWallorencokes Quick Start Guide 83 IBM DB2 Universal Database Replication Setup setenv LD_LIBRARY_PATH HOME sqllib 1ib32 LD_LIBRARY_PATH where HOME is the home directory of the IBM DB2 Universal database instance owner e For Bourne or Korn shell in mydb2profile ip conga SAAoiie Wasllorencakes LD_LIBRARY_PATH SHOME sqllib 1ib32 LD_LIBRARY_PATH export LD_LIBRARY_PATH 4 Before starting either Replication Agent or ECDA always source your file if you have customized it Otherwise source t
15. CO State Action REPLICATION DOWN Waiting for operator command 4 Validate that both primary and replicate connections are active isql Usa P SSAMPLE_RS admin who go Note Be aware that e The DSI connection for the primary database connection is usually down because you are not replicating data back to the primary database The Replication Agent connection if established for the replicate database connection is usually down because you are not replicating data from the replicate database Quick Start Guide 31 Oracle Replication Setup Do not proceed until admin who returns status for threads similar to this Spid Name State TACO S DS TEREKE Awaiting Command 101 1 SAMPLE_RS_ERSSD SAMPLE_RS_ERSSD 9 IDSA Awaiting Message 101 SAMPLE _RS_ERSSD SAMPLE_RS_ERSSD 8 SQM Awaiting Message 101 0 SAMPLE_RS_ERSSD SAMPLE_RS_ERSSD 54 DSI EXEC Awaiting Command 102 1 DCOServer oratest2 53 DII Awaiting Message 102 DCOServer oratest2 7 SQM Awaiting Message 102 0 DCOServer oratest2DSI 20 DSI EXEC Suspended 10341 NY NYoragz alk DSi Suspended 103 NY NYora92 24 DIKS Awaiting Wakeup 103 NY NYora92 25 SON Awaiting Wakeup 103 1 DIST NY NYora92 ZS SQM Awaiting Message 103 1 NY NYora92 22 SQM Awaiting Message 103 0 NY NYora92 62 REP AGENT Awaiting Command NY NYora92 Testing Replication When you
16. DirectConnect service name 5 Verify the connection to the replicate Microsoft SQL Server database by obtaining the DBMS name and version number select sqldbmsname go Quick Start Guide 49 Microsoft SQL Server Replication Setup SQLDbmsName Microsoft SQL Server select sqldbmsver go SQLDbmsVer O02 00 1399 6 Exit the isql session with the quit command Creating a Maintenance User for Replication in Microsoft SQL Server The maintenance user is a val id Microsoft SQL Server user that the Replication Server uses to apply commands to the replicate Microsoft SQL Server database Replication Server requires one maintenance user to be defined for each connection 1 Connect to the replicate database and enter this command to create the maintenance user login CREATE LOGIN maintuser WITH PASSWORD password DEFAULT_DATABASE replicate_db_name where password is the maintenance user login password and replicate_db_name is the replicate database name 2 At the replicate database enter this command to create a maintenance user CREATE USER maintuser FOR LOGIN maintuser 3 Grant database owner permission to the maintenance user EXEC sp_addrolemember db_owner maintuser Configuring Replication Server Review the procedures for configuring Replication Server for Microsoft SQL Server database Configuring Replication Server for Replica
17. Granting create object Permission to the rs_username User Creating a Database Replication Definition Creating a Database Replication Subscription 16 Replication Server Options Oracle Replication Setup Creating a Replication Server Connection to the Primary Database Create a Replication Server connection to the primary database 1 Go to the SSYBASE RAX 15_5 scripts oracle UNIX or Linux or SYBASE RAX 15_5 scripts oracle directory Windows 2 Make a copy of the oracle_create_rs_primary_connection sq l script cp oracle_create_rs_primary_connection sql my_oracle_create_rs_primary_connection sql 3 Before executing the my_oracle_create_rs_primary_connection sql script against your Replication Server change all occurrences of pds pdb to the name of the Replication Server connection used for the connection from Replication Agent for Oracle where e pdsis the name of the primary Oracle server e pdbis the primary Oracle System ID SID For example NY NYora92 Note Save these values to be used later for the rs_source_ds and rs_source_db parameters in the Replication Agent oracle rs file 4 Change sys and sys_pwd to the user ID and password of the Oracle user who must have permission to apply DML operations against all user tables to be replicated where e sys is the user ID of the Oracle user sys e sys_pwdis the password of the Oracle user change_on_install Note This user must be a valid
18. IBM DB2 Universal Database is correctly configured to provide archived logging and that the IBM DB2 Universal Database user ID used by the Replication Agent has the necessary permissions The pdb_xlog init command also creates objects in the primary database to support replication Note If in your resource file you set initialize_instance to yes skip step 1 and continue to step 2 1 Initialize the Replication Agent instance pdb_xlog init go You see a message indicating that the procedure was successful 2 Enter resume go If the Replication Agent successfully transfers to a replicating state you see State Action REPLICATING Ready to replicate data Replication Agent returns REPLICATION DOWN state if an error occurs 1 gt resume 2 gt go Msg 32000 Level 20 State 0 Server rao Procedure resume Line 1 Command lt resume gt failed Desired state lt REPLICATE gt could not be achieved Current state lt REPLICATIO DOWN gt Quick Start Guide 103 IBM DB2 Universal Database Replication Setup 3 The ra_status command returns the state of the Replication Agent It is good practice to verify that the Replication Agent remains in replication state even after the resume command executes successfully To detect an error that occurred after replication start up execute ra_status go If the Replication Agent is in replicating state ra_status returns State Action
19. Installed Objects on page 95 Creating a Database Replication Subscription Create database replication subscription 1 Make a copy of the rs_create_test_db_sub sql script named my_udb_rs_create_test_db_sub sql Before executing the my_udb_rs_create_test_db_sub sql script change the pds pdb and rds rdb to the appropriate connection name where e pds name of the data server of the primary IBM DB2 Universal Database e pdb name of the database of the primary IBM DB2 Universal Database e rds name of the DirectConnect service 94 Replication Server Options IBM DB2 Universal Database Replication Setup e rdb any valid identifier Sybase recommends that you use the IBM DB2 Universal Database replicate database name Note Save these values to be used later in the Replication Agent ibmudb rs file Here is an example of create database replication subscription create subscription NY_subl for database replication definition NY_repdef1l with primary at NY NYudb9 with replicate at DCService udb_db without materialization go 3 Connect to Replication Server and execute isql Usa P SSAMPLE_RS i my_udb_rs_create_test_db_sub sql You see a message indicating that the subscription is being created See also Verifying the ECDA Installed Objects on page 95 Verifying the ECDA Installed Objects Verify the ECDA installed objects 1 Log in to IBM DB2 Universal Database throug
20. SQL Server Replication Setup LogSvcLibStatistics 0 my_mssql_access_service ACS Required ConnectionSpecl my_mssql_datasource_name Client Interaction EnableAtStartup yes TransactionMode long Note TransactionMode long indicates that the client session will manually issue the commit command for transactions 8 Start the DirectConnect server a Open a command window and navigate to the DirectConnect installation directory For example cd c sybase DC 15_0 b Execute DC_SYBASE bat to set the DirectConnect environment variables c Navigate to the DirectConnect bin directory Gel ora d Start the DirectConnect server This also starts all of the configured access services DCStart Sservername where servername is the name of the DirectConnect server 9 Click Finish See also Verifying that you can Connect to Microsoft SQL Server on page 49 Verifying that you can Connect to Microsoft SQL Server Verify the connection to the replicate Microsoft SQL Server database 1 Open a command window in the SYBASE directory of your ECDA installation 2 Change to the 3SYBASE DC 15_0 directory 3 Set the environment variables by sourcing the 3SYBASE DC_SYBASE bat file 4 Log in to Microsoft SQL Server through the DirectConnect service isql Uvaliduser Ppassword Smy_mssql_access_service where validuser and password are any valid user and password and my_mssql_access_service is the
21. To install using the console mode or a response file see the ECDA Installation Guide for Linux and UNIX or the ECDA Installation Guide for Microsoft Windows 1 Verify that the drive on which you will install the products has enough free disk space for the products being installed and at least 10MB extra disk space for the installation program The program frees this extra space after the installation is complete 2 Insert the ECDA CD ROM into the CD ROM drive e On Microsoft Windows the installation program should start automatically If it does not select Start gt Run and enter x setup exe where x is your CD drive e On Linux or UNIX if auto mount is not available on your machine follow local procedures for mounting the CD ROM and enter setup bin The Welcome window appears 3 Click Next You may see the following error message 38 Replication Server Options Microsoft SQL Server Replication Setup Error writing file There may not be enough temporary disk space Try using is tempdir to use a temporary directory on a partition with more disk space If so set the temporary directory to another directory that has more disk space by entering the following at the command line e Windows x setup ex is tempdir lt directory_name gt e UNIX or Linux setup bin is tempdir lt directory_name gt where lt directory_name gt is the name of the temporary directory to which the installation program
22. Usa P SSAMPLE_RS 2 Enter grant create object to SAMPLE _RS_ra go where SAMPLE_RS ra is the value for the rs_username parameter you entered in the resource file 3 Log in to the RSSD isql USAMPLE_RS_RSSD_prim PSAMPLE_RS_RSSD_prim_ps SSAMPLE_RS_RSSD where SAMPLE_RS_RSSD SAMPLE_RS_RSSD_prim SAMPLE_RS_RSSD_prim_ps are the RSSD database name user ID and password needed to log in to the RSSD 4 Verify that the rs_username user has create object permission select from rs_users where username SAMPLE_RS_ra go The rs_users system table should indicate that the rs_username user has create object permission See also e Creating a Database Replication Definition on page 19 e Creating a Database Replication Subscription on page 19 18 Replication Server Options Oracle Replication Setup Creating a Database Replication Definition Create a database replication definition 1 Navigate to the SSYBASE RAX 15_5 scripts sybase UNIX or Linux or SYBASE RAX 15_5 scripts sybase Windows directory 2 Make a copy of the rs_create_test_db_repdef sql script named my_rs_create_db_repdef sql cp rs_create_test_db_repdef sql my_rs_create_db_repdef sql 3 Before executing the my_rs_create_db_repdef sql script change all occurrences of pds pdb to the name of the connection string you defined for the primary database where e pds is the name of the pr
23. a replication system for Oracle e A primary Oracle data server e A replicate Oracle data server e A Replication Server e The Replication Server Options components e Replication Agent e ExpressConnect for Oracle ECO Figure 1 Sybase replication system for Oracle Oracle Replication Replication Server Oracle primary database Agent with replicate database ExpressConnect for Oracle Quick Start Guide 3 Oracle Replication Setup Prerequisites Before you begin setting up replication for Oracle data servers review the prerequisites Make sure e You are familiar with Oracle data servers and have an understanding of Sybase replication e You have a valid license for Replication Server and Replication Server Options e Oracle Database 10g or 11g Enterprise Edition is installed and configured to serve as the source primary database from which Replication Agent replicates transactions and as the target replicate database to which Replication Server with ECO replicates transactions e If Oracle 10g or 11g Release 1 is the primary database install the Oracle JDBC thin driver for Oracle 10g or 11g and for JDK 1 4 and 1 5 If the primary database is Oracle 11g Release 2 install the Oracle JDBC thin driver 11 2 for JDK 1 6 e TCP IP connectivity is available e Any OS patches required for Java 6 0 has been installed e The Replication Agent has direct access to the Oracle online and archived redo logs Do not use Repl
24. and reinsert the CD 2 Start the installer cd cdrom Volume Label setup bin e On Linux and Linux on POWER 1 Log in as sybase and mount the CD mount t iso9660 dev cdrom mnt cdrom 2 Start the installer cd mnt cdrom setup bin where e cdrom and mnt cdromis the directory mount point you specified when mounting the CD drive e device_name is the name of the CD device drive e setup bin is the executable file name to install Replication Server If there is not enough disk space in the temporary disk space directory set the environment variable IATEMPDIR to tmp_dir before running the installer again where mp_diris the temporary directory to which the installation program writes the temporary installation files When specifying the mp_dir include the full path 4 In the Introduction window click Next 5 Indicate where to install Replication Server e To select an installation directory click Choose A window appears and from that window browse for and select the directory e To create a new directory enter a new directory path e Torestore to the default directory if you do not want to use the directory that you have entered click Restore Default Folder One of these occurs e If the installation directory you chose does not exist you see The directory directory name does not exist Do you want to create it Click Yes e If the installation directory you selected exists and alread
25. each parameter described See the Replication Agent Administration Guide For a complete list of configuration parameters see the Replication Agent Reference Manual Warning The rs_source_ds and the rs_source_db values must match the pds pdb values of your Replication Server primary connection name that you configured in step 3 in Creating a Replication Server connection to the primary database on page 17 Table 1 Resource File Parameters Description Example values instancename name Any valid name admin_port Port number that Replication Agent uses 9030 if in use select a different port number pds_tns_connection Connection name found in the ORA102 JDOE_HOST COM tnsnames ora file which identifies the connection information for the primary da tabase pds_tns_filename File name identifying the Oracle A valid Oracle tnsnames ora file to be used to iden tnsnames ora file For ex tify the connection information for the pri ample mary database opt oracle net work admin tnsnames ora pds_username User ID that Replication Agent uses to ac RA_USER cess primary data server pds password password Password for pds_username Machine where Replication Server is instal jdoe_host1 rs rs_port number number Port where Replication Server is installed 11752 Replication Server user with connect SAMPLE_RS_ra source and create object permissions 26 Replication Server Options Or
26. finish setting up the replication system test that replication works as intended 1 Connect to the primary Oracle instance as a regular user rather than as the maintenance user Make sure the regular user also exists in the replicate database 2 Create a test table to replicate in the primary database CAgSeiee ieelolkey wal i alin pr char 110 6 3 Grant permissions to any new or existing object to be replicated in the primary database Gremie eli lt i Wil ico oTe 4 Connect to the replicate Oracle instance as a regular user rather than as the maintenance user 5 Create a test table in the replicate database create table T1 aint b char 10 6 Grant permissions to any new or existing object to be replicated in the replicate database so that the Replication Server maintenance user can update this table grant all oa Wile cto jowlollakes 32 Replication Server Options Oracle Replication Setup 7 Connect to the Replication Agent and mark the table pdb_setreptable T1l mark 8 After the table is marked and the Replication Agent is in a replicating state insert test data into the test table and commit it By default the work performed by the maintenance user of the primary connection is not replicated The user ID used to insert transactions cannot be the same as the maintenance user defined in the primary connection Replication Agent applies only committed transactions to a replicate databa
27. in the database to support stored procedure replication the pdb_xlog init command also initializes the Replication Agent System Database RASD by reading schema information and redo log location information from the primary Oracle database To initialize the Replication Agent instance run pdb_xlog init go You see a message indicating that the procedure was successful 2 Enter resume go If the Replication Agent successfully transfers to a replicating state you see State Action 30 Replication Server Options Oracle Replication Setup REPLICATING Ready to replicate data The Replication Agent goes to the REPLICATION DOWN state if an error occurs 1 gt resume 2 gt go Msg 32000 Level 20 State 0 Server rao Procedure resume Line 1 Command lt resume gt failed Desired state lt REPLICATE gt could not be achieved Current state lt REPLICATION DOWN gt 3 The ra_status command returns the state of the Replication Agent It is good practice to verify that the Replication Agent remains in replication state even after the resume command executes successfully To detect an error that occurred after replication start up execute ra_status go If the Replication Agent is in replicating state ra_status returns State Action REPLICATING Ready to replicate data The Replication Agent goes to the REPLICATION DOWN state if an error occurs 1 gt ra_status 2 gt
28. install that are detected in this directory will be replaced Click Next Select the type of installation Typical installs the default components This is recommended for most users Full installs every Replication Server component including all the supported language modules Custom lets you select the components to install Some components are automatically installed if they are required to run your selected components Click Next Read the License and Copyright Agreement a b c Select the geographic location from where you are installing to display the agreement for your region Select I agree to the terms of the Sybase license for the install location specified You must agree to the license and copyright agreement before you can continue with the installation Click Next 8 On the Sybase Software Asset Management License Server window select one of 42 Replication Server Options Microsoft SQL Server Replication Setup e Specify License Keys click Browse to select the license file To select multiple license files use Shift Click or Ctrl Click The license pane displays the license information Alternatively copy and paste the license information directly in the license pane Click Next If you specify a served license key the installer prompts you to install a new SySAM license server Click one of e Next to install the new SySAM license server and follow the installation
29. installs the components in the installation directory you specified and displays an installation progress indicator If errors occur during the installation the installation program displays error messages Exit the installation program wizard to correct the cause of the error then restart the installation program If the software is installed successfully you see a window confirming the successful installation 13 Click Done to complete the installation and close the installation program Verifying the Installation Verify that Replication Agent has been successfully installed 1 2 Change to SSYBASE RAX 15_5 bin UNIX or Linux or SSYBAS Change to the directory where you installed Replication Agent Set the environment variables by sourcing the SYBASE csh file UNIX or Linux or by executing the SYBASE bat file Windows Sa ol RAX 15_5 bin Windows Obtain the Replication Agent version string e For Windows te Aak For UNIX or Linux ra sh v If Replication Agent installs successfully you see the Sybase copyright and the Replication Agent version string Configuring Replication Components Review the procedures for configuring each of the replication components in the replication system See also Testing Replication on page 105 82 Replication Server Options IBM DB2 Universal Database Replication Setup Setting IBM DB2 Universal Database Environment Variables
30. is available Because Replication Agent for Microsoft SQL Server is limited to the Microsoft Windows platform all examples shown are for Windows Note On Windows Vista and Windows Server 2008 you must be logged in as an Administrator Do not use Replication Server reserved words for object names and connection names A complete list of reserved words is in the Replication Server Reference Manual Planning Your Installation Before installing the replication components verify the system requirements identify the Sybase installation directories and obtain the installation software System Requirements Make sure the target Windows computer on which you are installing the replication components meets the minimum memory and disk space requirements ey RAM Replication Server 512MB 380MB ECDA 512MB 440MB Replication Agent 512MB 300MB Quick Start Guide 35 Microsoft SQL Server Replication Setup To improve performance by avoiding multiple network hops install Replication Server ECDA for ODBC and the target database on the same machine Identify the Sybase Installation Directory Determine the directory in which to install the replication components Replication Agent Replication Server and ECDA are installed in the same base directory which is identified by the SYBASE environment variable Select a path on the host drive to be the recipient of the Sybase installation and configuration activities Replication Agen
31. license for a grace period of 30 days To continue using Replication Agent after the end of the grace period obtain a valid license from the Sybase Product Download Center at Attp www sybase com detail 7id 1025266 and install it Note Replication Agent is licensed statically when installed with RSHE If you are installing Replication Agent with RSHE locate the file that contains a valid SysAM license for the Replication Agent component in RSHE 15 6 Specify that license file here See the Sybase Software Asset Management Users Guide Click Next until you see the Sybase Software Asset Management Notification window 11 On the Sybase Software Asset Management Notification window configure your server for e mail notification When configuration is enabled you receive information about license management events that require attention Select Yes and either accept the default values that are supplied or enter values for e SMTP server host name e SMTP server port number e E mail return address e Recipients of the notification e Message severity level of an event that triggers e mail notification e Informational e Warning e Error If you choose not to have e mail alerts or severity messages logged select No Quick Start Guide 81 IBM DB2 Universal Database Replication Setup Click Next 12 Review the product features or components listed on the Preinstallation Summary window Click Install The installation program
32. prompts e Previous and select Use previously deployed license server if you have an existing SySAM license server on the same host e Use Previously Deployed License Server if you have a previously deployed license server enter the host name of the machine where the license server is running and the port number if the port number you are using is not default Click Next e Continue installation without a license key if you do not have licenses for any of the Replication Server components you can select this option and click Next to proceed The installer allows you to install and use the Replication Server components without a license for a grace period of 30 days To continue using these components after the end of the grace period obtain valid licenses from the SPDC and install these licenses using the Replication Server License Installer See the SySAM Users Guide 9 Configuring your server for e mail notification enables designated users to receive information about license management events requiring attention Provide e SMTP server host name e SMTP server port number e E mail return address e Recipient e mail addresses e Message severity that triggers e mail messages Click Next 10 The installation summary window displays the selections you have made Review the information and click Install 11 On the Start Sample Replication Server window select Yes to configure and start a sample Replication Server The i
33. refreshed instead of overwritten pdb_xlog init force Note If you prefer to delete and replace all the information in the Replication Agent repository issue the pdb_xlog remove force command followed by a normal pdb_xlog init command without the force option 3 Reset the locator stored in Replication Server isql USAMPLE_RS_RSSD_prim PSAMPLI SSAMPLE_RS_ERSSD DSAMPLE_RS_ERSS RS_RSSD_prim_ps S rs_zeroltm NY NYmss5 go You see Locator has been reset to zero return status 0 Quick Start Guide 67 IBM DB2 Universal Database Replication Setup IBM DB2 Universal Database Replication Setup Implement a sample replication system for IBM DB2 Universal Database Note Use the tasks in the Quick Start Guide to set up replication only for proof of concept POC or testing not for production System Architecture There are multiple components required to implement a replication system for IBM DB2 Universal Database e A primary IBM DB2 Universal database e A replicate IBM DB2 Universal database e A Replication Server e The Replication Server Options components e Replication Agent e ECDA Option for ODBC Figure 3 Sybase replication system for IBM DB2 Universal Database s Primary IBM Replication Agent DB2 Universal Database Replication Server ECDA Option Replicate IBM for ODBC DB2 Universal Database 68 Replication Server Options IBM
34. AX_15 5 scripts sybase directory 2 Make a copy of the rs_create_test_db_repdef sql script named my_mssql_rs_create_test_db_repdef sql 3 Before executing the my_mssql_rs_create_test_db_repdef sql script change the value of pds pdb to the name of the connection string you defined for the primary database where e pdsis the name of the primary Microsoft SQL Server data server e pdbis the name of the primary Microsoft SQL Server database For example NY NYmss5 Note Save these values to be used later for the rs_source_ds and rs_source_db parameters in the Replication Agent mssql rs file This is a database replication definition example create database replication definition NY_repdefl with primary at NY NYmss5 go 4 Connect to the Replication Server and enter isql Usa P SSAMPLE_RS i my_mssql_rs_create_test_db_repdef sql You see a message indicating that the database replication definition has been created See also e Creating a Database Replication Subscription on page 53 Creating a Database Replication Subscription Create database replication subscription 1 Make a copy of the rs_create_test_db_sub sql script named my_mssql_rs_create_test_db_sub sql 2 Before executing the my_mssql_rs_create_test_db_sub sql script change pds pdb and rds rdb to the appropriate connection name where Quick Start Guide 53 Microsoft SQL Server Replication Setup e pdsis the name of the primary M
35. Agent 512MB 300MB For Linux ay RAM Replication Server 512MB 400MB ECDA 256MB 590MB Replication Agent 512MB 300MB To improve performance by avoiding multiple network hops install Replication Server ECDA for ODBC and the target database on the same machine See Replication Server Options 15 6 Release Bulletin Identify the Sybase Installation Directory Determine the directory in which to install the replication components Replication Agent Replication Server and ECDA are installed in the same base directory which is identified by the SYBASE environment variable Select a path on the host drive to be the recipient of the Sybase installation and configuration activities Replication Agent must be installed on the same host as either the DB2 UDB server or the DB2 Administration Client Replication Agent 15 6 Installation Directory Replication Agent 15 6 uses the RAX 15_5 directory name If you have an existing Replication Agent 15 5 installation and do not want to overwrite it install Replication Agent 15 6 in a different location Accessing and Downloading Products from SPDC If you are not using the installation CD download replication components from the Software Product Download Center SPDC Prerequisites You must have a login ID and password to download software from the SPDC Task Note If you are installing Replication Server Options RSO this task requires you to perform several steps twice as you ar
36. C Launch the setup program Note If the installer does not start automatically double click setup exe or select Start gt Run and enter the following where x is your CD drive x setup exe If there is not enough disk space in the temporary disk space directory set the environment variable TMP to directory_name before running it again where directory_name is the Quick Start Guide 41 Microsoft SQL Server Replication Setup name of the temporary directory to which the installation program writes the temporary installation files When specifying the directory_name include its full path In the Introduction window click Next Indicate where to install Replication Server To select an installation directory click Choose A window appears and from that window browse for and select the directory To create a new directory enter a new directory path To restore to the default directory if you do not want to use the directory that you have entered click Restore Default Folder One of these occurs If the installation directory you chose does not exist you see The directory directory name does not exist Do you want to create it Click Yes If the installation directory you selected exists and already contains an Replication Server installation you see Warning You have chosen to install into an existing chireecorcys Dr you oroe wich this Tascalilet Lon any older versions of the products you choose to
37. DB2 Universal Database Replication Setup Prerequisites Before you begin setting up replication for IBM DB2 Universal Database review the prerequisites Make sure e You are familiar with IBM DB2 Universal Database and have an understanding of Sybase replication e You have obtained the licenses for the Replication Server and the Replication Server Options e IBM DB2 Universal Database 9 1 or 9 5 must be installed and configured to serve as the source primary database and as the target replicate database e TCP IP connectivity must be available e Any OS patches required for Java 6 0 have been installed Do not use Replication Server reserved words for object names and connection names A complete list of reserved words is in the Replication Server Reference Manual Planning Your Installation Before installing the replication components verify the system requirements identify the Sybase installation directories and obtain the installation software System Requirements Make sure the target computer on which you are installing the replication components meets the minimum memory and disk space requirements For Windows mony A Replication Server 512MB 380MB ECDA 512MB 440MB Replication Agent 512MB 300MB For UNIX components memor AN Replication Server 512MB 520MB ECDA 256MB 700MB Quick Start Guide 69 IBM DB2 Universal Database Replication Setup Components Memory Ra Replication
38. DC Service is the DirectConnect service name Verify the connection to the replicate IBM DB2 Universal Database select sqldbmsname go SQLDbmsName DB2 SUN select sqldbmsver go SQLDbmsVer 09 01 0005 Exit the isql session with the quit command Creating a Maintenance User for Replication in IBM DB2 Universal Database The maintenance user is a valid IBM DB2 Universal Database user that the Replication Server uses to apply commands to the replicate IBM DB2 Universal Database Replication Server requires one maintenance user to be defined for each connection A user must already exist on the operating system before you can add it to an IBM DB2 Universal Database To add an existing operating system user to the database use the Control Center administration tool which is included with either the server or client installation of IBM DB2 Universal Database on Microsoft Windows You may choose to install an IBM DB2 Universal Database client on a Windows machine and use it to administer the IBM DB2 Universal Databases 1 Select an existing operating system user to be added to the replicate database as a maintenance user 2 Select Start gt Programs gt IBM DB2 gt Control Center Configure the Control Center administration tool to connect to the replicate IBM DB2 Universal Database according to the IBM documentation Once the Control Center administration tool has been configured the replicate database appears in
39. ECT TO dbalias USER db2_user USING db2_user_ps where dbalias is the cataloged alias of the primary database db2_useris the primary database user and db2_user_ps is the password 2 Determine the LOGARCHMETH setting GET DB CFG FOR dbalias 2 If the results do not show that LOGARCHMETHL is set to LOGRETAIN or to the path name of the directory to which logs are archived set it e To use the default archive location UPDATE DATABASE CONFIGURATION USING logarchmeth1 LOGRETAIN e To use a specific archive location UPDATE DATABASE CONFIGURATION USING logarchmeth1 DISK path where path is the full path name of the directory where the archive logs are to be stored Setting the IBM DB2 Universal Database Connectivity Autocommit Parameter Replication Agent requires that the IBM DB2 Universal Database connectivity autocommit parameter be turned on autocommit 1 The autocommit parameter is specified in the DB2 call level interface CLI configuration file for the primary database If the autocommit parameter is not turned on a deadlock problem may occur To set the autocommit parameter edit the db2cli ini file Itis located in lt HOME gt sqllib where lt HOME gt is the home directory of the IBM DB2 Universal Database instance owner UNIX or Linux or the IBM DB2 Universal Database client or server installation directory Windows 96 Replication Server Options IBM DB2 Universal Database Replication Setup
40. Embedded Replication Server System Database which manages replication system information for a Replication Server See also Replication Server failback a procedure that restores the normal user and client access to a primary database after a failover procedure switches access from the primary database to a replicate database See also failover failover a procedure that switches user and client access from a primary database to a replicate database particularly in the event of a failure that interrupts operations at the primary database or access to the primary database Failover is an important fault tolerance feature for systems that require high availability See also fai back function a Replication Server object that represents a data server operation such as insert delete or begin transaction Replication Server distributes operations to replicate databases as functions See also function string function string a string that Replication Server uses to map a function and its parameters to a data server API Function strings allow Replication Server to support heterogeneous 110 Replication Server Options Glossary replication in which the primary and replicate databases are different types with different SQL extensions and different command features See also function e gateway connectivity software that allows two or more computer systems with different network architectures to communicate e inbound q
41. If you choose not to have e mail alerts or severity messages logged select No Click Next 12 Review the product features or components listed on the Preinstallation Summary window Click Install The installation program installs the components in the installation directory you specified and displays an installation progress indicator If errors occur during the installation the installation program displays error messages Exit the installation program wizard to correct the cause of the error then restart the installation program If the software is installed successfully you see a window confirming the successful installation 13 Click Done to complete the installation and close the installation program Verifying the Installation Verify that Replication Agent has been successfully installed 1 Change to the directory where you installed Replication Agent 2 Set the environment variables by sourcing the SYBASE csh file UNIX or Linux or by Change to SSYBASE RAX 15_5 bin UNIX or Linux or SSYBAS executing the SYBASE bat file Windows Gl o9 RAX 15_5 bin Windows Obtain the Replication Agent version string 14 Replication Server Options Oracle Replication Setup e For Windows HES AWE For UNIX or Linux ra sh v If Replication Agent installs successfully you see the Sybase copyright and the Replication Agent version string Configuring Replication Componen
42. If you continue and the older products were not installed with Replication Agent 15 6 the installation program overwrites the common files Note On Microsoft Windows platforms if you are prompted to overwrite a DLL click Yes only if the version of the new DLL is newer than the one the installation program is attempting to overwrite 9 Select the primary database the Replication Agent will connect to Click Next 10 Select one of these options to enter the license e Specify license keys browse to or specify the license file Use previously deployed license server use a previously deployed license server Enter the host name of the machine where the license server is running and the port number if the port number you are using is not the default e Continue installation without a license key install and use Replication Agent without a license for a grace period of 30 days To continue using Replication Agent after the end of the grace period obtain a valid license from the Sybase Product Download Center at Attp www sybase com detail 7id 1025266 and install it Note Replication Agent is licensed statically when installed with RSHE If you are installing Replication Agent with RSHE locate the file that contains a valid SysAM license for the Replication Agent component in RSHE 15 6 Specify that license file here See the Sybase Software Asset Management Users Guide Quick Start Guide 45 Microsoft SQL Server
43. NYudb9 22 SQM Awaiting Message 103 0 NY NYudb9 62 REP AGENT Awaiting Command NY NYudb9 Testing Replication When you finish setting up the replication system test that replication works as intended 1 Connect to the primary IBM DB2 Universal Database instance as a regular user rather than as the maintenance user Make sure the regular user also exists in the replicate database a To connect to the database with CLP use CONNECT TO dbalias USER db2_user USING db2_user_ps where dbalias is the cataloged alias of the primary database db2_useris the primary database user and db2_user_ps is the password Create a test table in the primary database create table T1 aint b char 10 Grant permissions to any new or existing object to be replicated in the primary database Geane Aull Cin Wil ee jowlollae Connect to the replicate IBM DB2 Universal Database instance as a regular user rather than as the maintenance user Create a test table in the replicate database create table T1 aint b char 10 Grant permissions to any new or existing object to be replicated in the replicate database so that the Replication Server maintenance user can update this table Quick Start Guide 105 IBM DB2 Universal Database Replication Setup grame al oa Romp ull mie Connect to the Replication Agent and resume replication resume Mark the table pdb_setreptable Tl mark After the table is marked and
44. Note This table lists parameters in the order they appear in the resource file not alphabetically 98 Replication Server Options IBM DB2 Universal Database Replication Setup Table 3 Resource File Parameters Description Example values instancename name Any valid name admin_port Port number that Replication Agent uses 9030 if in use select a different port number pds_host_name Machine host where IBM DB2 Universal NY Database is installed pds_port_number Port number for IBM DB2 Universal Data 50007 base pds_database name database_name Name of the primary database NYudb9 pds_username User ID that Replication Agent uses to ac ra_user cess primary data server pds password password Password for pds_username pds_datasource_name Data source name or database alias for the DBALIAS primary database a Machine where Replication Server is instal jdoe_host1 led rs rs_port number number Port where Replication Server is installed 11752 Replication Server user with connect SAMPLE_RS_ra source and create object permissions eE _ os name representing data server of pri NY mary IBM DB2 Universal Database rs_source_db Valid name representing primary IBM DB2 NYudb9 Universal Database Character set that Replication Server is us Windows cp850 ing e UNIX iso_1 Note The value defined for the rs_charset configuration parameter must match the RS_charset value in the Replicatio
45. On UNIX or Linux SSYBASE RAX 15_5 bin ra_admin sh vr myra rs where myra rsis the path and name of the resource file Validation results are returned as either e Response file processing completed Or e Response file processing completed with errors If any validation fails the ra_admin utility returns an error message and information about the failure You can repeat the validation process as many times as necessary until it executes without error No entities are changed or created Create and configure the Replication Agent instance e On Windows SSYBASE RAX 15_5 bin ra_admin bat r myra rs e On UNIX or Linux SSYBASE RAX 15_5 bin ra_admin sh r myra rs where myra rs is the path and name of the resource file Note If in your response file you set start_instance to yes your instance is also running If you set initialize_instance to yes your instance is also initialized Results are returned as either e Response file processing completed e Response file processing completed with errors See the Replication Agent Administration Guide If you set start_instance to yes in your resource file and if the log indicates that the Replication Agent instance was started skip this step and continue with the next section Change to the instance directory and run Replication Agent in the background On Windows cd SYBASE RAX 15_5 myra On UNIX or Linux cd SSYBASE RAX 15_5 myra Exec
46. Oracle instance locate the JDBC driver that was available when the Oracle release was originally created e For Oracle 10g SORACLE_HOME jdbc lib ojdbcl14 jar e For Oracle 11g Release 1 SORACLE_HOME jdbc lib ojdbc5 jar e For Oracle 11g Release 2 SORACLE_HOME jdbc lib ojdbc 6 jar Replication Agent may require a newer version of the JDBC driver which can be found at http www oracle com technetwork indexes downloads index html 2 Add the JDBC driver to the CLASSPATH environment variable For example you can append the existing CLASSPATH to the new JDBC driver and add this command to the login script of a UNIX and Linux user setenv CLASSPATH path_name driver jar CLASSPATH where driverjaris your JDBC driver ojdbc14 jar ojdbc5 jar or ojdbc6 jar For Windows add this to the CLASSPATH environment variable set CLASSPATH path_name jdbc lib driver jar sCLASSPATH where driverjaris your JDBC driver ojdbc14 jar ojdbc5 jar or ojdbc6 jar 3 Record the location of the tnsnames ora file and record the connection name from that file for the Replication Agent to use to connect to the Oracle primary database You can find these values from any machine from which you can use an Oracle SQLPLUS session to connect to the desired Oracle instance The ORACLE_SID is from the environment variable ORACLE_SID Record the ORACLE_SID value for the instance you want to connect to 24 Replicatio
47. RANT SELECT ON SYS LOBS TO RA_USER RANT SELECT ON SYS COLLECTIONS TO RA_USER RANT SELECT ON SYS CONS TO RA_USER RANT SELECT ON SYS COLTYPES TO RA_USER RANT SELECT ON SYS COL TO RA_USER RANT SELECT ON SYS CDEF TO RA_USER RANT SELECT ON SYS SNAPS TO RA_USER RANT SELECT ON SYS TS TO RA_USER RANT SELECT ON SYS USERS TO RA_USER 22 Replication Server Options RA RA RA RA RA RA RA RA RA RA RA RA RA RA RA RA RA RA RA Aa Maa awiagaaetwaate Gf wf RA See the Replication Agent Primary Database Guide Verifying the Oracle User Roles Created by the Replication Agent Validate the required Oracle settings Use SQLPLUS to run this command as the new Oracle user select GRANTED_ROLE GRA TED_ROLE ECHN RESOURCE SEL ECT_CATALOG_ROLE 1S WSR INO PRIS Oracle Replication Setup US BE CRON ROMS OHO nel Om RASUISHR T SELECT ON SYS INDS TO RA_USER iS HH CRON OMVCReLAB mats Om Au USHIRT T SELECT ON SYS TABPARTS to RA_USER T SELECT ON SYS TABCOMPARTS to RA_USER T SELECT ON SYS TABSUBPARTS to RA_USER T SELECT ON
48. Replication Setup Click Next until you see the Sybase Software Asset Management Notification window 11 On the Sybase Software Asset Management Notification window configure your server for e mail notification When configuration is enabled you receive information about license management events that require attention Select Yes and either accept the default values that are supplied or enter values for e SMTP server host name e SMTP server port number e E mail return address e Recipients of the notification e Message severity level of an event that triggers e mail notification e Informational e Warning e Error If you choose not to have e mail alerts or severity messages logged select No Click Next 12 Review the product features or components listed on the Preinstallation Summary window Click Install The installation program installs the components in the installation directory you specified and displays an installation progress indicator If errors occur during the installation the installation program displays error messages Exit the installation program wizard to correct the cause of the error then restart the installation program If the software is installed successfully you see a window confirming the successful installation 13 Click Done to complete the installation and close the installation program Verifying the Installation Verify that Replication Agent has been successfully installed 1
49. SE environment variable Select a path on a host drive to be the recipient of the Sybase installation and configuration activities Replication Agent must be installed on the same type of host as the primary Oracle instance and must be able to directly access the primary Oracle redo and archive logs Replication Agent 15 6 Installation Directory Replication Agent 15 6 uses the RAX 15_5 directory name If you have an existing Replication Agent 15 5 installation and do not want to overwrite it install Replication Agent 15 6 in a different location Accessing and Downloading Products from SPDC If you are not using the installation CD download replication components from the Software Product Download Center SPDC Prerequisites You must have a login ID and password to download software from the SPDC Quick Start Guide 5 Oracle Replication Setup Task Note If you are installing Replication Server Options RSO this task requires you to perform several steps twice as you are downloading Replication Server and other products 1 Go to Attps sybase subscribenet com control sybs login and enter your login ID and password 2 Select Replication Server From the high level Product Information page select Replication Server again If you are installing Replication Server Heterogeneous Edition RSHE skip to Step 7 If you are installing RSO select the Replication Server software you need by version and platform
50. SYBASE EY company Quick Start Guide Replication Server Options 15 6 Microsoft Windows UNIX and Linux DOCUMENT ID DC00766 01 1560 01 LAST REVISED November 2010 Copyright 2010 by Sybase Inc All rights reserved This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or technical notes Information in this document is subject to change without notice The software described herein is furnished under a license agreement and it may be used or copied only in accordance with the terms of that agreement To order additional documents U S and Canadian customers should call Customer Fulfillment at 800 685 8225 fax 617 229 9845 Customers in other countries with a U S license agreement may contact Customer Fulfillment via the above fax number All other international customers should contact their Sybase subsidiary or local distributor Upgrades are provided only at regularly scheduled software release dates No part of this publication may be reproduced transmitted or translated in any form or by any means electronic mechanical manual optical or otherwise without the prior written permission of Sybase Inc Sybase trademarks can be viewed at the Sybase trademarks page at Attp www sybase com detail id 1011207 Sybase and the marks listed are trademarks of Sybase Inc indicates registration in the United States of America SAP and other SAP products and
51. SYS NTABS to RA_USER T SELECT ON SYS INDPARTS to RA_USER T SELECT ON SYS INDCOMPARTS to RA_USER T SELECT ON SYS INDSUBPARTS to RA_USER T SELECT ON SYS LOBCOMPPARTS to RA_USER T SELECT ON SYS LOBFRAGS to RA_USER T SELECT ON SYS MLOGS to RA_USER T SELECT ON SYS PROCEDUREINFO to RA_USER T SELECT ON SYS ARGUMENTS to RA_USER ISH CRON CM Sr ele ES ete Om RAGRUIS Hine DoE EE CRON GMS As WR EB UE Sm Om RAMUS ERG TSE ak CONS MS 6 COMSMEOn RAMU SER I GURL ON Wis KOREC NAC ILIMSSIEIN Sy LO IVA JUIS IRS T SELECT ON SYS OPQTYPES TO RA_USER The user who starts the Replication Agent instance must also have read access to the Oracle redo log files and the Oracle archive directory that contains the archive log files to be accessed Quick Start Guide 23 Oracle Replication Setup for replication If the Replication Agent is configured to remove old archive files the user must have update authority to the directory and the archive log files Creating the Replication Agent Instance A single installation of the Replication Agent can support replication from multiple databases however one Replication Agent instance is needed for each Oracle database that is to be replicated Prepare and create a Replication Agent instance for each Oracle database being replicated 1 Obtain the Oracle JDBC driver from Oracle and update the CLASSPATH environment variable To find the JDBC connection that communicates with the primary
52. The Software Terms and Conditions page appears listing countries and regions Either e Select I Agree go to the Product Download Page and continue with step 6 or e Select your country or region to read the terms The software licence agreement for the software appears Read the terms and either e Select I Agree to accept the terms go to the Product Download Page and continue with step 6 or e Select Cancel to return to the high level Product Information page The Product Download Page lists the software and documentation for the product Select FTP Download or Advanced Download Options for the Replication Server software to download and save the compressed files to your local drive Note For each product the documentation download contains installation related documents and the rest of the documentation set can be accessed online Return to the detailed Product Information page that you accessed in step 3 and select e The RSO software by primary database version and platform or e The RSHE software The Software Terms and Conditions page appears listing countries and regions Navigate the Software Terms and Conditions pages as you did in step 5 From the Product Download Page select FTP Download or Advanced Download Options for the components of RSO or RSHE to download and save the compressed files to your local drive For RSO download e Replication Agent 15 6 e ExpressConnect for Oracle 15 5 ESD 1 For RSHE dow
53. _user_pwdare the maintenance user and password created while configuring ECDA Option for ODBC For example create connection to DCService my_udb_db using profile rs_udb_to_udb standard set username maintuser set password password go Note In the above example password is a Replication Server reserved word and therefore must be enclosed in double quotes and should match the password specified in the resource file 4 Create the connection to the replicate database woo Usa le SySVAMN2 IIIa IRS ak my_udb_create_rs_standby_connection sql 5 Log in to SAMPLE RS and verify the Replication Server connection to the replicate database isgql Usa P SSAMPLE_RS admin who go Quick Start Guide 91 IBM DB2 Universal Database Replication Setup 6 You should see a message returned for the DSI connection rds rdb Verify that the status is Awaiting Message or Awaiting Command Exit the isql session with the quit command Configuring Replication Server for Replication from the Primary Database Configure Replication Server for replication from the primary database Creating a Replication Server Connection to the Primary Database Create a Replication Server connection to the primary database 1 Go to the SSYBASE RAX 15_5 scripts sybase UNIX or Linux or SYBAS RAX 15_5 scripts sybase Windows directory Gl Make a copy of the rs_create_test_primary_connec
54. ace Include the full path to this directory Quick Start Guide 79 IBM DB2 Universal Database Replication Setup Log in to the Replication Agent host machine using an operating system user account with authority to start stop and administer the Replication Agent instance for example the sybase user Close all nonessential applications and minimize any open windows Insert the Replication Agent 15 6 distribution media in the appropriate drive Start the installation program e On Microsoft Windows platforms the installation program should start automatically If it does not select Start gt Run and enter x setup exe where x is your media drive You can also start the installation program from Microsoft Windows Explorer by double clicking the setup exe file e On UNIX platforms enter cd cdrom setup bin Click Next Note You can click Cancel to stop the installation at any point Select your geographic location Read the Sybase license agreement and select I agree to the terms of the Sybase license for the install location specified Note You must agree to the terms of the software license before you can continue Click Next Specify an installation directory The default installation directory is e Existing ZS YBASE or c sybase on Microsoft Windows platforms e Existing SYBASE or opt sybase on UNIX platforms Click Next to accept the default installation directory or e Click Choose to
55. acle Replication Setup Parameter Description Example values Character set that Replication Server is us Windows cp850 ing e UNIX iso_1 Note The value defined for the rs_charset configuration parameter must match the RS_charset value in the Replication Server configuration file SSYBASE REP 15_5 install lt serv er gt cfg UNIX or Linux or SYBAS REP 15_5 install lt serv er gt cfg Windows rs_source_ds Valid name representing data server of Ora NY cle primary database Valid name representing Oracle primary da NYora92 tabase rssd_password Password for rssd_username SAM PLE_RS_RSSD_maint_ps pdb_timezone_file Path of the Oracle timezone file software oracle Oral0g oracore zo neinfo timezone dat pdb_include_archives Enables or disables the use of Oracle archive USE_DEFAULT log files pdb_archive_path Identifies directory path where Replication A valid directory path on the Agent expects to find archived Oracle redo machine hosting Replication log files Agent that points to a location where Oracle puts the archived redo log files 8 Create and execute the new instance resource file Note Be sure your CLASSPATH environment variable points to the correct Oracle JDBC driver before proceeding Quick Start Guide 27 Oracle Replication Setup a b Validate the settings in the resource file e On Windows SSYBASE RAX 15_5 bin ra_admin bat vr myra rs e
56. ation and Start up Create configure and start a DirectConnect service Before you begin to create and configure the DirectConnect service make sure that e The DB2 client has been configured as described in Catalog a node and a database in an IBM DB2 Universal Database client on page 84 e The port to be used by the DirectConnect service is unused and available See also e Creating a Maintenance User for Replication in IBM DB2 Universal Database on page 90 Quick Start Guide 87 IBM DB2 Universal Database Replication Setup Creating and Configuring a DirectConnect Service Use the Create Server wizard to create a DirectConnect service 1 Start the Create Server wizard e For UNIX or Linux DCWizard sh e For Windows DCWizard bat 2 When the Welcome Create Server wizard appears click Next Select the ECDA Option for ODBC option and click Next 4 Enter the server name for the new DirectConnect service and the port number that you want the server to listen on Click Next Select the unixODBC Driver Manager without a Driver Manager option and click Next Select e The path to the IBM DB2 Universal database ODBC Driver file for example software IBM db2_09_01 lib libdb2 so Note Version 9 1 of the HP Itanium IBM DB2 Universal database ODBC 64 bit driver is named libdb2o0 so e On UNIX or Linux the DB2 script file that must be sourced before the DirectConnect service is started If you have custo
57. bStatistics 0 Client Interaction SvclibDescription Access Service Library for ODBC my_udb_access_service ACS Required ConnectionSpecl my_udb_datasource_name 11 Start the DirectConnect service a Navigate to the DirectConnect installation directory for example cd software sybase DC 15_0 b Execute DC_SYBASE csh UNIX or Linux or DC_SYBASE bat Windows to set the DirectConnect environment variables c Navigate to the DirectConnect bin directory d Start the DirectConnect service in the background which also starts all the configured access services DCStart Sservicename amp where servicename is the name of the DirectConnect service Verifying that you can Connect to IBM DB2 Universal Database Verify the connection to the replicate IBM DB2 Universal Database 1 Open a command window in the SYBASE UNIX or Linux or 3SYBASE Windows directory of your ECDA installation 2 Change to the SSYBASE DC 15_0 UNIX or Linux or 3SYBASE DC 15_0 Windows directory 3 Set the environment variables by sourcing the SYBASE DC_SYBASE csh file UNIX or Linux or by running the 3SYBASE DC_SYBASE bat file Windows Quick Start Guide 89 IBM DB2 Universal Database Replication Setup 4 6 Log in to the IBM DB2 Universal Database through the DirectConnect service isql Uvaliduser Ppassword SDCService where validuser and password are any valid user and password and
58. been successfully installed Click Done Installing Replication Agent Install Replication Agent using the GUI wizard Note If there is not enough disk space in your default temporary directory set the IATEMPDIR Linux or UNIX or TMP Windows environment variable to a directory that has enough space Include the full path to this directory 1 Log in to the Replication Agent host machine using an operating system user account with authority to start stop and administer the Replication Agent instance for example the sybase user 2 Close all nonessential applications and minimize any open windows Insert the Replication Agent 15 6 distribution media in the appropriate drive Start the installation program e On Microsoft Windows platforms the installation program should start automatically If it does not select Start gt Run and enter x setup exe where x is your media drive You can also start the installation program from Microsoft Windows Explorer by double clicking the setup exe file e On UNIX platforms enter cd cdrom setup bin Click Next Note You can click Cancel to stop the installation at any point Select your geographic location Read the Sybase license agreement and select I agree to the terms of the Sybase license for the install location specified 12 Replication Server Options Oracle Replication Setup Note You must agree to the terms of the so
59. cation Setup Use SQLPLUS to connect to the primary database as a system administrator and run these commands to create an Oracle user named RA_USER specified by pds_username with the password sybase and grant permissions to the user REATE USER RA_USER PROFILE DEFAULT IDENTIFIED K sybase DEFAULT TABLESPACE USERS ACCOUNT LOCK RANT CONNECT TO RA_USER RANT RESOURCE TO RA_USI irl De Se RANT SELECT_CATALOG_ROLI zj ve a G cD ira Z RANT ALTER SESSION TO RA_USER RANT ALTER SYSTEM TO RA_USI irl pe ns RANT EXECUTE ON SYS DBMS_FLASHBACK To RA_USER RANT EXECUTE ON SYS DBMS_LOCK TO RA_USER RANT ALT GI R ANY PROCEDURE TO RA_USER RANT CREATE SESSION TO RA_USER RANT CREATE TABLE TO RA_USER A 7e E EEEE EEEE ROR ORO SR CBO e ROBO ROBO CR ORCS ROBO Re RCeRae Ro RANT ALTER ANY TABLE TO RA_USER RANT DROP ANY TABLE TO RA_USER RANT CREATE PROCEDURE TO RA_USER RANT DROP ANY PROCEDURE TO RA_USER RANT CREATE PUBLIC SYNONYM TO RA_USER RANT DROP PUBLIC SYNONYM TO RA_USER RANT SELECT ON SYS OBJ TO RA_USER
60. cccceeeeeeeeeeeeeees 7 Installing ExpressConnect for Oracle 11 Installing Replication Agent ceeceeeeeetees 12 Configuring Replication Components cccce 15 Configuring Replication Server 000068 15 ExpressConnect for Oracle Configuration 20 Configuring Replication Agent ceeeee 20 Testing Replication cccsscccccccccceeeeeeeeessseseeeeesseseeees 32 Resetting the Primary Oracle Database for Replication Sethe tect oncne tats Santancthaee ta tenietade betes 33 Microsoft SQL Server Replication Setup eceeeee 34 SYSIEMUATCHINGCLUN Gs ici cecheviateiantdveed intaxysnaredarenrtieteinles 34 PYCICOWISIIOS s kiinni etch aan She 35 Planning Your Installation eeeeeeeeeeeeeeeeeeeeeeeeee 35 System Requirements cscccceceeeeeeseeeseees 35 Identify the Sybase Installation Directory 36 Accessing and Downloading Products from S d D1 OANE ctiha iets E Sbedesetuhis cecn te 36 Installing Replication Component cccccecsseeeees 38 Installing ECDA Option for ODBC 068 38 Installing Replication Server 0 eeeeeees 41 Quick Start Guide Contents Installing Replication Agent cceeceeeeeeeeeees 44 Configuring Replication Component ceeeee 47 Configuring ECDA Option for ODBC 47 Configuring Replication Server
61. cense Installer See the SySAM Users Guide Configuring your server for e mail notification enables designated users to receive information about license management events requiring attention Provide SMTP server host name SMTP server port number E mail return address Recipient e mail addresses Message severity that triggers e mail messages Click Next 10 Replication Server Options Oracle Replication Setup 10 The installation summary window displays the selections you have made Review the information and click Install 11 On the Start Sample Replication Server window select Yes to configure and start a sample Replication Server The installer displays the configuration information for the sample Replication Server Write this information down Click Next 12 If you chose to configure and start a sample Replication Server the installer proceeds to configure and start the sample Replication Server Click Next when done 13 When the final window appears indicating a successful installation click Done to exit the installer Verifying the Installation Verify that Replication Server has been successfully installed 1 Change to the directory where you installed Replication Server 2 Set the environment variables by sourcing the SYBASE csh file UNIX or Linux or by executing the SYBASE bat file Windows 3 Log in to Replication Server absKepll lUisiel le SSVI NInID RUS
62. d with the installation by selecting Yes to All 6 Select one of the two installation types e Custom which allows you to select the ECDA options and components to install Quick Start Guide 39 Microsoft SQL Server Replication Setup e Full which installs all ECDA products and components from the CD Click Next 7 Based on your selection e Ifyou selected Custom the next window displays all the ECDA options and all the components checked or selected You must uncheck or deselect the components that you do not want to install Note Be aware that if you deselect a component that is required it is automatically installed if it is needed to run other selected components After you have selected either the ECDA Option for ODBC or ECDA Option for Oracle and have selected the desired components click Next e Ifyou selected Full install the next window displays all the ECDA options including ECDA Option for ODBC and ECDA Option for ODBC and all the components checked or selected Click Next Before proceeding to the next window the installation program verifies the selections and checks for dependencies and available disk space The Product Summary window displays every component that is to be installed and the total disk space required for all the selected components Note If the target directory does not have enough free space the space required and the space available information is displayed If you c
63. ddresses e Message severity that triggers e mail messages Click Next 10 The installation summary window displays the selections you have made Review the information and click Install 11 On the Start Sample Replication Server window select Yes to configure and start a sample Replication Server The installer displays the configuration information for the sample Replication Server Write this information down Click Next 12 If you chose to configure and start a sample Replication Server the installer proceeds to configure and start the sample Replication Server Click Next when done 13 When the final window appears indicating a successful installation click Done to exit the installer Verifying the Installation Verify that Replication Server has been successfully installed 1 Change to the directory where you installed Replication Server 2 Set the environment variables by sourcing the SYBASE csh file UNIX or Linux or by executing the SYBASE bat file Windows 3 Log in to Replication Server isgl Usa P SSAMPLE_RS 4 Verify the Replication Server version admin version go 5 Exit the isql session with the quit command Installing Replication Agent Install Replication Agent using the GUI wizard Note If there is not enough disk space in your default temporary directory set the IATEMPDIR Linux or UNIX or TMP Windows environment variable to a directory that has enough sp
64. der system or setup specific values follows in text For example Run installation directory start bat where installation directory is where the application is installed Quick Start Guide 1 Conventions Syntax conventions Curly braces indicate that you must choose at least one of the enclosed options Do not type the braces when you enter the command Brackets mean that choosing one or more of the enclosed options is optional Do not type the brackets when you enter the command The vertical bar means you can select only one of the options shown The comma means you can choose as many of the options shown as you like separating your choices with commas that you type as part of the command An ellipsis three dots means you may repeat the last unit as many times as you Parentheses are to be typed as part of the command need Do not include ellipses in the command Case sensitivity e All command syntax and command examples are shown in lowercase However replication command names are not case sensitive For example RA_CONFIG Ra_Config and ra_config are equivalent e Names of configuration parameters are case sensitive For example Scan_Sleep_Max is not the same as scan_sleep_max and the former would be interpreted as an invalid parameter name e Database object names are not case sensitive in replication commands However to use a mixed case object name in a replication command to match a mixed cas
65. e Installing Replication Components Review the procedures for installing the replication components For the quick start process install all replication components on the same host where an IBM DB2 Universal Database server has already been installed and is running with both the primary and the replicate databases This simplifies the quick start process and avoids the step of separately installing IBM DB2 Universal Database clients for Replication Agent and for ECDA Option for ODBC See also e Configuring Replication Components on page 82 Installing ECDA Option for ODBC Install ECDA Option for ODBC using the setup program Prerequisites Install ECDA on the same server as the replicate database to eliminate a network hop and to improve performance Task Note To install using the console mode or a response file see the ECDA Installation Guide for Linux and UNIX or the ECDA Installation Guide for Microsoft Windows 1 Verify that the drive on which you will install the products has enough free disk space for the products being installed and at least 10MB extra disk space for the installation program The program frees this extra space after the installation is complete 2 Insert the ECDA CD ROM into the CD ROM drive 72 Replication Server Options IBM DB2 Universal Database Replication Setup e On Microsoft Windows the installation program should start automatically If it does not select Start gt Run and enter
66. e downloading Replication Server and other products 1 Go to Attps sybase subscribenet com control sybs login and enter your login ID and password 70 Replication Server Options IBM DB2 Universal Database Replication Setup 2 Select Replication Server 3 From the high level Product Information page select Replication Server again 4 Ifyou are installing Replication Server Heterogeneous Edition RSHE skip to Step 7 If you are installing RSO select the Replication Server software you need by version and platform The Software Terms and Conditions page appears listing countries and regions 5 Either e Select I Agree go to the Product Download Page and continue with step 6 or e Select your country or region to read the terms The software licence agreement for the software appears Read the terms and either e Select I Agree to accept the terms go to the Product Download Page and continue with step 6 or e Select Cancel to return to the high level Product Information page 6 The Product Download Page lists the software and documentation for the product Select FTP Download or Advanced Download Options for the Replication Server software to download and save the compressed files to your local drive Note For each product the documentation download contains installation related documents and the rest of the documentation set can be accessed online 7 Return to the detailed Product Information page that you access
67. e licence agreement for the software appears Read the terms and either 36 Replication Server Options Microsoft SQL Server Replication Setup e Select I Agree to accept the terms go to the Product Download Page and continue with step 6 or e Select Cancel to return to the high level Product Information page 6 The Product Download Page lists the software and documentation for the product Select FTP Download or Advanced Download Options for the Replication Server software to download and save the compressed files to your local drive Note For each product the documentation download contains installation related documents and the rest of the documentation set can be accessed online 7 Return to the detailed Product Information page that you accessed in step 3 and select e The RSO software by primary database version and platform or e The RSHE software The Software Terms and Conditions page appears listing countries and regions 8 Navigate the Software Terms and Conditions pages as you did in step 5 9 From the Product Download Page select FTP Download or Advanced Download Options for the components of RSO or RSHE to download and save the compressed files to your local drive For RSO download e Replication Agent 15 6 e ECDA Options 15 0 e DirectConnect Client 15 0 For RSHE download e Replication Server 15 6 e Replication Agent 15 6 e ECDA Options 15 0 e DirectConnect Client 15 0 10 Uncompress and ex
68. e object name in the primary database delimit the object name with double quote characters For example pdb_get_tables 72b eName Terminology Replication Agent is a generic term used to describe the Replication Agents for Adaptive Server Enterprise Oracle IBM DB2 UDB and Microsoft SQL Server The specific names are e Replication Agent for Oracle e Replication Agent for Microsoft SQL Server e Replication Agent for UDB for IBM DB2 on Linux UNIX and Windows 2 Replication Server Options Oracle Replication Setup Oracle Replication Setup Implement a sample replication system for Oracle Note Use the tasks in the Quick Start Guide to set up replication only for proof of concept POC or testing not for production Only basic Oracle features are addressed in the example scenario In particular Oracle Automatic Storage Management ASM and Real Application Clusters RAC are not addressed here although Replication Agent does support these features See the Replication Agent Primary Database Guide The replication of sequences is not discussed in the Quick Start Guide To set up sequence replication see the Replication Agent Installation Guide and the Replication Agent Primary Database Guide See also e Planning Your Installation on page 4 e Installing Replication Components on page 7 e Configuring Replication Components on page 15 Sybase Replication System There are multiple components required to implement
69. eate a new directory enter a new directory path To restore to the default directory if you do not want to use the directory that you have entered click Restore Default Folder One of these occurs e If the installation directory you chose does not exist you see The directory directory name does not exist Do you want to create it Click Yes e If the installation directory you selected exists and already contains an Replication Server installation you see Warning You have chosen to install into an existing directory If you proceed with this installation any older versions of the products you choose to install that are detected in this directory will be replaced Click Next 6 Select the type of installation Typical installs the default components This is recommended for most users e Full installs every Replication Server component including all the supported language modules e Custom lets you select the components to install Some components are automatically installed if they are required to run your selected components Quick Start Guide 9 Oracle Replication Setup Click Next a b c Read the License and Copyright Agreement Select the geographic location from where you are installing to display the agreement for your region Select I agree to the terms of the Sybase license for the install location specified You must agree to the license and copyright agreement before y
70. eate and execute the new instance resource file a Validate the settings in the resource file using the vr parameter On Windows SSYBASE RAX 15_5 bin ra_admin bat vr myra rs On UNIX or Linux SSYBASE RAX 15_5 bin ra_admin sh vr myra rs where myra rs is the path and name of the resource file Validation results are returned as either Response file processing completed Or Response file processing completed with errors 100 Replication Server Options IBM DB2 Universal Database Replication Setup If any validation fails the ra_admin utility returns an error message and information about the failure You can repeat the validation process as many times as necessary until it executes without error No entities are changed or created b After the resource file has been validated allow the ra_admin utility to create and configure the Replication Agent instance using the r parameter e On Windows SSYBASE RAX 15_5 bin ra_admin bat r myra rs e On UNIX or Linux SSYBASE RAX 15_5 bin ra_admin sh r myra rs where myra rs is the path and name of the resource file Note If in your response file you set start_instance to yes your instance is also running If you set initialize_instance to yes your instance is also initialized c Creation results are returned as either e Response file processing completed Or e Response file processing completed with errors See the Replication A
71. ed in step 3 and select e The RSO software by primary database version and platform or e The RSHE software The Software Terms and Conditions page appears listing countries and regions 8 Navigate the Software Terms and Conditions pages as you did in step 5 9 From the Product Download Page select FTP Download or Advanced Download Options for the components of RSO or RSHE to download and save the compressed files to your local drive For RSO download e Replication Agent 15 6 e ECDA Options 15 0 e DirectConnect Client 15 0 For RSHE download e Replication Server 15 6 e Replication Agent 15 6 e ECDA Options 15 0 e DirectConnect Client 15 0 10 Uncompress and extract all the installation images to your local drive 11 Download ECDA 15 0 ESD 3 or later Quick Start Guide 71 IBM DB2 Universal Database Replication Setup a Point your Web browser to the Sybase Support Page at Aitp www sybase com support b wm Select EBFs Maintenance If prompted enter your MySybase user name and password c Select EnterpriseConnect Data Access You see a list of EBFs and maintenance releases Find the EBF containing ECDA 15 0 ESD 3 or later for your platform For example EBF xxxxx 15 0 ESD 3 where xxxxx is the number of the EBF containing the ECDA 15 0 ESD 3 for your platform d wm e Click the Info icon to display the EBF Maintenance report and click the product description to download the softwar
72. er e RDBMS an abbreviation for relational database management system an application that manages and controls relational databases Compare with DBMS See also relational database e relational database a collection of data in which data is viewed as being stored in tables which consist of columns data items and rows units of information Relational databases can be accessed by SQL requests Compare with database See also SQL e replicate data A set of data that is replicated from a primary database to a replicate database by a replication system See also primary database replication system and replicate database e replicate database a database that contains data replicated from another database the primary database through a replication system The replicate database is the database that receives replicated data in a replication system Contrast with primary database See also replicate data replicated transaction and replication system e replicated transaction a primary transaction that is replicated from a primary database to a replicate database by a transaction replication system See also primary database primary transaction replicate database and transaction replication Quick Start Guide 113 Glossary replicate site the location or facility at which replicate data servers and replicate databases are deployed to support normal business operations during scheduled downtime at the primary site Co
73. ereby subscription data in a replicate database is initialized outside of the replication system You can use bulk materialization for subscriptions to table replication definitions or function replication definitions See also atomic matertalization and nonatomic materialization e client in client server systems the part of the system that sends requests to servers and processes the results of those requests See also client application e client application software that is responsible for the user interface including menus data entry screens and report formats See also c ent e commit an instruction to the DBMS to make permanent the changes requested in a transaction See also transaction Contrast with rollback Quick Start Guide 109 Glossary database a collection of data with a specific structure or schema for accepting storing and providing data for users See also data server DBMS and RDBMS database connection a connection that allows Replication Server to manage the database and distribute transactions to the database Each database in a replication system can have only one database connection in Replication Server See also Replication Server and route data client a client application that provides access to data by connecting to a data server See also client client application and data server data distribution a method of locating or placing discrete parts of a single set of data in multip
74. ftware license before you can continue Click Next 8 Specify an installation directory The default installation directory is e Existing ZS YBASE or c sybase on Microsoft Windows platforms e Existing SYBASE or opt sybase on UNIX platforms Click Next to accept the default installation directory or e Click Choose to select an installation directory in the file browser Click Next Enter a directory name Click Next If the directory name does not exist click Yes when prompted to create it The directory does not exist Do you want to create it If the directory you specified exists you see Warning You have chosen to install into an existing directory If you proceed with this installation any older versions of the products you choose to install that are detected in this directory will be replaced If you continue and the older products were not installed with Replication Agent 15 6 the installation program overwrites the common files Note On Microsoft Windows platforms if you are prompted to overwrite a DLL click Yes only if the version of the new DLL is newer than the one the installation program is attempting to overwrite 9 Select the primary database the Replication Agent will connect to Click Next 10 Select one of these options to enter the license e Specify license keys browse to or specify the license file Use previously deployed license server use a previously deployed license
75. gent Administration Guide 5 If in your resource file you set start_instance to yes skip this step and continue with the next section If your resource file did not configure start_instance to yes change to the instance directory and run Replication Agent in the background e On Windows cd SYBASE RAX 15_5 myra e On UNIX or Linux cd SSYBASE RAX 15_5 myra Execute the RUN file in the background Your Replication Agent for UDB is now running See also Verifying the Replication Agent Instance Installation on page 101 e Initializing the Replication Agent Instance on page 103 Verifying the Replication Agent Instance Installation Verify that Replication Agent instance has been successfully installed 1 Use dsedit to update the Replication Server interfaces UNIX or Linux or sql ini Windows file to include an entry for the Replication Agent location Quick Start Guide 101 IBM DB2 Universal Database Replication Setup Note You can use any TDS client utility isql isqlApp or SQLAdvantage that you prefer Verify the connection to the Replication Agent a Open a command window in the SYBASE UNIX or Linux or SYBAS Windows directory of your Replication Agent installation Pl ole b Set the environment variables by sourcing the SYBASE csh file UNIX or Linux or executing the SYBASE bat file Windows c Log in to Replication Agent regl Use P Simyra Note These verification
76. h ECDA as maintuser e To verify whether the rs_info table has been created successfully enter isql Umaintuser Ppassword SDCService gelet o Brom Te GrG go This result is returned RSKEY RSVAL charset_name ie Oss sortorder_name bna ieo dL 2 rows affected e To verify whether the rs_ astcommit table has been created successfully enter isql Umaintuser Ppassword SDCService select from rs_lastcommit go This result is returned ORIGIN ORIGIN_QID SECONDARY_QID ORIGIN_TIM DEST_COMMIT_TIME ta 0O rows affected Quick Start Guide 95 IBM DB2 Universal Database Replication Setup If the ECDA objects are not installed successfully no results appear and you see an error message 2 Exit the isql session with the quit command Configuring Replication Agent Review the procedures for configuring Replication Agent for IBM DB2 Universal Database Primary IBM DB2 Universal Database Configuration for Replication To configure the primary IBM DB2 Universal database for replication you may need to connect to the primary database using the primary database instance owner user Verifying the Current Archive Setting of the Transaction Log Set the primary IBM DB2 Universal Database transaction logging to archive logging instead of circular logging for Replication Agent 1 Determine the LOGARCHMETHI setting 1 Connect to the database by entering this command at the CLP prompt CONN
77. hat provides customer applications third party products and other Sybase products with the interfaces needed to communicate with Open Server applications See also Open Server Open Client application An application that uses Sybase Open Client libraries to implement Open Client communication protocols See also Open Client and Open Server Open Server a Sybase product that provides the tools and interfaces required to create a custom server See also Open Client Open Server application a server application that uses Sybase Open Server libraries to implement Open Server communication protocols See also Open Client and Open Server outbound queue a stable queue managed by Replication Server to spool messages to a replicate database See also inbound queue replicate database and stable queue primary data the data source used for replication Primary data is stored and managed by the primary database See also primary database primary database the database that contains the data to be replicated to another database the replicate database through a replication system The primary database is the 112 Replication Server Options Glossary source of replicated data in a replication system Sometimes called the active database Contrast with replicate database See also primary data e primary key a column or set of columns that uniquely identifies each row in a table e primary site the location or facilit
78. he original db2cshrc or db2profile script See also e Node and Database Cataloging in an IBM DB2 Universal Database Client on page 84 Node and Database Cataloging in an IBM DB2 Universal Database Client Both the Replication Agent for UDB and ECDA Option for ODBC must use IBM DB2 Universal Database client libraries Cataloging configures an IBM DB2 Universal Database client so that it knows how to connect to a particular database Cataloging also creates a database alias which Replication Agent or ECDA uses as the data source name By default the client libraries are installed along with the IBM DB2 Universal Database server You can also separately install an IBM DB2 Universal Database client on a host other than the one on which the server is installed In either case you must perform cataloging in the IBM DB2 Universal Database client that Replication Agent or ECDA is using If Replication Agent and ECDA are installed on different hosts they are using different IBM DB2 Universal Database clients so the cataloging must be performed in both of the clients If the client and the server are on the same host and there is only one IBM DB2 Universal Database server running cataloging may have been performed when the IBM DB2 Universal Database instance was installed and the databases configured If so no additional cataloging is required Cataloging a Node in an IBM DB2 Universal Database Client Catalog a node in an IBM DB2 Universal Database
79. his installation Click OK The SySAM Notification window asks you to configure your server for e mail notification When configuration is enabled you will receive information about license management events requiring attention Enter the following e SMTP server host name e SMTP server port number e E mail Return Address e Recipient e mail addresses e Message severity that triggers e mail messages Click Next 12 A window displays a message indicating that the installation was successful and advising you to check for software updates Click Finish Installing Replication Server Install Replication Server using the setup program Prerequisites Allocate a disk partition of at least 20MB for each Replication Server you are installing You can add more partitions later if necessary Check each partition to make sure it is available and has write permissions Allocate the entire partition to the Replication Server If you allocate only a portion of the partition to Replication Server you cannot use the remainder for any other purpose Task 1 Verify that the drive on which you install the Replication Server has enough disk space for the components being installed and at least 100MB of extra disk space for the installation program Insert the Replication Server media in the appropriate drive or download and extract the Replication Server installation image from the Sybase Product Download Center SPD
80. ication Server reserved words for object names and connection names A complete list of reserved words is in the Replication Server Reference Manual Planning Your Installation Before installing the replication components verify the system requirements identify the Sybase installation directories and obtain the installation software System Requirements Make sure the target computer on which you are installing the replication components meets the minimum memory and disk space requirements For Windows mony A Replication Server 512MB 380MB ExpressConnect for Oracle 125MB 105MB Replication Agent 512MB 300MB For UNIX 4 Replication Server Options Oracle Replication Setup yA Replication Server 512MB 520MB ExpressConnect for Oracle 125MB 200MB Replication Agent 512MB 300MB For Linux mony A Replication Server 512MB 400MB ExpressConnect for Oracle 125MB 200MB Replication Agent 512MB 300MB To improve performance by avoiding multiple network hops install Replication Server ECO and the target database on the same machine Replication Agent must have direct access to the Oracle online and redo logs and must be installed on the same machine as the primary database Identify the Sybase Installation Directory Determine the directory in which to install the replication components Replication Agent Replication Server and ECO are installed in the same base directory which is identified by the SY BA
81. ication Setup 17 SQM Awaiting Message 102 0 my_mssql_access_service rdb DSI EXEC Suspended 103 1 NY NYmss5 DSI Suspended 103 NY NYmss5 24 DIST Awaiting Wakeup 103 NY NYmss5 25 SQT Awaiting Wakeup 103 1 DIST NY NYmss5 23 SQM Awaiting Message 103 1 NY NYmss5 22 SQM Awaiting Message 103 0 NY NYmss5 62 REP AGENT Awaiting Command NY NYmss5 Testing Replication When you finish setting up the replication system test that replication works as intended 1 Connect to the primary Microsoft SQL Server database as a regular user rather than as the maintenance user Make sure the regular user also exists in the replicate database 2 Create a test table to replicate in the primary database create table T1 aint b char 10 go 3 Grant permissions to any new or existing object to be replicated in the primary database Geane ALL ony m Eo ooie go 4 Connect to the replicate Microsoft SQL Server database as a regular user rather than as the maintenance user 5 Create a test table in the replicate database create table Tl a int b chart 10 go 6 Grant permissions to any new or existing object to be replicated in the replication database so that the Replication Server maintenance user can update this table emae Gull im Wil tee awole go 7 Connect to the Replication Agent and mark the table pdb_setreptable T1 mark 8 After the table is marked and the Replication Agent is in a replicating state inser
82. icrosoft SQL Server data server e pdbis the name of the primary Microsoft SQL Server database e rdsis the DirectConnect access service name e rdbis the name of the replicate database Note Save these values to be used later in the Replication Agent mssql rs file Here is an example of create database replication subscription create subscription NY_subl for database replication definition NY_repdefl with primary at NY NYmss5 with replicate at my_mssql_access_service my_repl_db without materialization go Connect to Replication Server and execute LSe Usa gt SSANIA RS a my Mesel re ersare cest Clo svig scn You see a message indicating that the subscription is being created Configuring Replication Agent Review the procedures for configuring Replication Agent for Microsoft SQL Server Primary Microsoft SQL Server Configuration and Verification Review the procedures for configuring and verifying the primary Microsoft SQL Server database for replication Verifying the Compatibility Level of Microsoft SQL Server Verify that the database compatibility level is set to Microsoft SQL Server 2005 90 or Microsoft SQL Server 2008 100 Replication Agent supports Microsoft SQL Server 2005 Service Pack 2 and Microsoft SQL Server 2008 To verify the compatibility level 1 In the Microsoft SQL Server Management Studio right click your primary database and select Properties In Database Properties clic
83. ield enter a name that identifies your replicate data server and database for example my_mssql_datasource_name Use this name later when you configure the ECDA access service Select the replicate Microsoft SQL Server in the Server drop down list Click Next 5 Be sure that Microsoft SQL Server authentication is selected and the Connect to Microsoft SQL Server check box is checked Enter a valid Microsoft SQL Server login ID and password and click Next 6 Select Change the Default Database To and select the replicate database from the drop down list Click Next 7 Click Finish 8 Verify ODBC connectivity to the replicate database by clicking Test Data Source If connectivity is not successful verify that you have configured the ODBC driver correctly 9 Click OK Quick Start Guide 47 Microsoft SQL Server Replication Setup 10 You should now see your Microsoft SQL Server data source in the list of System Data Sources Click OK See also e DirectConnect Server Configuration and Startup on page 48 e Creating a Maintenance User for Replication in Microsoft SQL Server on page 50 DirectConnect Server Configuration and Startup Create configure and start a DirectConnect server Before you begin to create and configure the DirectConnect server make sure that e The name fora valid Microsoft SQL Server account is available This name is used as the administrator for the DirectConnect server e The number of the unused po
84. ies to ra_user Note In a POC environment the user is created by an IBM DB2 Universal Database system administrator Adding a Temporary Tablespace to the Primary Database Add a temporary tablespace to the primary database 1 Inthe Control Center All Databases view right click the Table Spaces folder under your primary database and select Create from the context menu 2 In the Name window specify a name for the new table space and select the Let DB2 Manage My Storage radio button Click Next 3 In the Type window select User Temporary and click Next 4 Inthe Buffer Pool window click Create Quick Start Guide 97 IBM DB2 Universal Database Replication Setup 5 Inthe Create Buffer Pool window specify a name for the buffer pool specify at least 8KB as the page size in the drop down menu select Create Bufferpool Immediately and click OK 6 Click Finish to create the table space The new table space appears in the Table Spaces folder Creating the Replication Agent Instance Prepare and create a Replication Agent instance for replication of each IBM DB2 Universal Database A single installation of the Replication Agent can support replication from multiple databases However one Replication Agent instance is needed for each IBM DB2 Universal Database that is to be replicated Note To replicate from multiple databases using one Replication Agent instance for each database you must create a separate IBM DB2 Uni
85. ilter driver management console 6 To start the sybfilter driver enter start at the management console 56 Replication Server Options Microsoft SQL Server Replication Setup 7 Add the log file path to the sybfilter driver with the user manager or by modifying the configuration file e User manager use the add command in the management console The syntax for this command is as follows add serverName dbName logFilePath For example to add the log file named pdb2_log 1ldfatC Program Files Microsoft SQL Server MSSQL 1 MSSQL Data tothe dbName database on the serverName data server use this add myserverName dbName C Program Files Microsoft SQL Server MSSOL 1 MSSQL Data pdb2_log 1ldf Note If you add the log file path with the user manager the user manager refreshes all log paths in the sybfilter driver automatically after adding the log path into the configuration file e Configuration file to add the log file path directly to the configuration file open and manually edit the configuration file This an example of log file path entries myserver pdbl log_file_path C Program Files Microsoft SQL Server MSSQL 1 MSSQL Data pdb11_log 1ldf log_file_path C Program Files Microsoft SQL Server MSSQL 1 MSSQL Data pdb12_log 1ldf myserver pdb2 log_file_path C Program Files Microsoft SQL Server MSSOL 1 MSSQL Data pdb2_log 1ldf command in the management console 8 If you added a log file for your pri
86. imary Oracle server e pdbis the primary Oracle System ID SID For example NY NYora92 Note Save these values to be used later for the rs_source_ds and rs_source_db parameters in the Replication Agent oracle rs file This is a database replication definition example create database replication definition NY_repdefl with primary at NY NYora92 replicate DDL go 4 Connect to the Replication Server and enter isql Usa P SSAMPLE_RS L my_rs_create_db_repdef sql You see a message indicating that the database replication definition has been created See also e Creating a Database Replication Subscription on page 19 Creating a Database Replication Subscription Create a database replication subscription 1 Make a copy of the rs_create_test_db_sub sql script named my_rs_create_db_sub sql cp rs_create_test_db_sub sql my_rs_create_db_sub sql 2 Before executing the my_rs_create_db_sub sql script change all occurrences of pds pdb and rds rdb to the appropriate connection name where Quick Start Guide 19 Oracle Replication Setup e pdsis the name of the primary Oracle server e pdbis the primary Oracle System ID SID e rdsis the replicate Oracle System ID SID e rdbis any valid identifier Sybase recommends that you use the replicate Oracle SID SID Note Save the values of pds and pdb to be used later for the rs_source_ds and rs_source_db parameters in the Replication Agent orac
87. instance b Issue this command to create server xlog objects server_xlog init c Issue this command to shutdown Replication Agent shutdown Stop the Microsoft SQL Server in single user mode a Log in to the server C Program Files Microsoft SQL Server 90 Tools Binn SQLCMD EXE U username P password S serverName where username password and serverName are your user ID password and Microsoft SQL Server name b Issue the shutdown command Restart Microsoft SQL Server in multiuser mode normal start a In Control Panel gt Administrative Tools gt Services find the service named Microsoft SQL Server SERVER where SERVER is the name of your Microsoft SQL Server data server For example Microsoft SQL Server TEAMSTER b Start the service Restart Replication Agent a Change to the SYBASE directory and start the Replication Agent instance 62 Replication Server Options Microsoft SQL Server Replication Setup cd SSYBASE RAX 15_5 myra b Execute the RUN file for example RUN_myra 10 Initialize Replication Agent a Log in to Replication Agent using isql legl Usa P Smyia where myra is the name of Replication Agent b Issue pdb_xlog init The Replication Agent instance is now running and the primary data server and the Replication Agent instance have been initialized Subsequent initialization Create another Replication Agent instance after yo
88. ion directory This directory is located at SSYBASE RAX 15_5 system lt platform gt where lt platform gt is winx8 6 winx64 or winvistax64 e winvistax64 is for Windows Server Vista x64 and Windows Server 2008 R1 x64 e winx64 is for Windows Server XP Professional x64 and Windows Server 2003 x64 e winx86 is for Windows Server XP Professional x86 Windows Server 2003 x86 Windows Server Vista x86 and Windows Server 2008 R1 x86 2 Right click the sybfilter inf file to install the sybfilter driver Note There can be only one installation of the sybfilter driver on a Windows machine After the driver is installed it works for all Replication Agent for Microsoft SQL Server instances running on the same machine 3 Under any directory create a configuration file to store all log file paths for primary databases The configuration file must have a cfg suffix For example under the directory 3SYBASE RAX 15_5 system lt plat form gt create a file named LogPath cfg 4 Adda system environment variable named RACFGFilePath and set its value to the path of the configuration file a Open the Control Panel click System click the Advanced tab and choose Environment Variables b Click New to add a new system variable c Name the variable RACFGFilePath and set its value to the location of your configuration file 5 In Windows Explorer navigate to 3SYBASE RAX 15_5 bin and double click the sybfiltermgr exe file to start the sybf
89. ist Do you want to create it Click Yes e Tf the installation directory exists and contains files the software prompts Warning You have chosen to install into an existing directory If you proceed with this installation any older versions of the products you choose to install that are detected in this directory will be replaced Note You will not see this message if the directory exists but is empty Quick Start Guide 73 IBM DB2 Universal Database Replication Setup If you are prompted to overwrite any files or DDL select Yes only if the version of the new files or DDL is later than the one you are attempting to overwrite Note In certain cases when ECDA is installed with other Sybase products you may see warnings about overwriting newer versions of files In these cases simply instruct the installer to overwrite these files and proceed with the installation by selecting Yes to All Select one of the two installation types e Custom which allows you to select the ECDA options and components to install e Full which installs all ECDA products and components from the CD Click Next Based on your selection e Ifyou selected Custom the next window displays all the ECDA options and all the components checked or selected You must uncheck or deselect the components that you do not want to install Note Be aware that if you deselect a component that is required it is automatically installed if i
90. k Options and verify that the Compatibility level is Microsoft SQL Server 2005 90 or Microsoft SQL Server 2008 100 If it is not select that compatibility from the drop down list and click OK 54 Replication Server Options Microsoft SQL Server Replication Setup Disabling Microsoft SQL Server Replication A Microsoft SQL Server publication cannot be created in the primary database used by Replication Agent for Microsoft SQL Server and you cannot simultaneously use Microsoft replication and Replication Agent on the same Microsoft SQL Server database If a Microsoft SQL Server publication already exists remove the publication before using Replication Agent for Microsoft SQL Server Creating a Microsoft SQL Server User and Granting Permissions Create a Microsoft SQL Server user named ra_user with the password sybase and grant permissions to the user Log in to the primary Microsoft SQL Server as a system administrator and run use master create login ra_user use lt primary_database gt create user ra_user for login ra_user EXEC sp_addsrvrolemember ra_user sysadmin Enabling Remote DAC Enable remote DAC 1 From the Windows Start menu choose Microsoft SQL Server gt Configuration Tools gt Surface Area Configuration gt Surface Area Configuration for Features 2 In the Surface Area Configuration for Features window choose DAC under the MSSQLSERVER Database Engine tree and ma
91. ke sure Enable Remote DAC is selected Obtaining the Microsoft SQL Server DAC Port Number Obtain the Microsoft SQL Server DAC port number 1 Open the ERRORLOG file in a text editor This file is located in the 1og directory of your Microsoft SQL Server For example C Program Files Microsoft SQL Server MSSOL 1 MSSQL LOG ERRORLOG 2 Search for the string Dedicated admin to find an entry similar to this 2007 11 09 13 40 02 40 Server Dedicated admin connection support was established for listening locally on port 1348 3 Record the port number specified in this entry for use in a later step Quick Start Guide 55 Microsoft SQL Server Replication Setup Verifying the Version of the Microsoft Filter Manager Library Confirm that the Microsoft Filter Manager Library version is 5 1 2600 2978 or later to make the primary transaction log files readable for Replication Agent 1 To determine the version of the library in Windows Explorer right click c windows system32 fltlib d11 select Properties and click the Version tab in the Properties dialog box 2 Ifthe version is earlier than 5 1 2600 2978 go to the Microsoft Web site and update your Windows system Making the Primary Transaction Log Files Readable for Replication Agent Install and set up the sybfilter driver so that Replication Agent can read the primary transaction log files 1 In Windows Explorer navigate to the sypb filter driver installat
92. le rs file Here is an example of create database replication subscription create subscription NY_subl for database replication definition NY_repdefl with primary at NY NYora92 with replicate at DCOServer oratest2 without materialization go Connect to Replication Server and execute isql Usa P SSAMPLE_RS i my rs _ create db _ sub sql You see a message indicating that the subscription is being created ExpressConnect for Oracle Configuration Server connectivity for ECO is configured via Replication Server using the create connection and alter connection commands Thus there is no separate configuration required for ECO Configuring Replication Agent Configuring Replication Agent for Oracle involves configuring the primary Oracle database for replication creating verifying and initializing the Replication Agent instance Primary Oracle Database Configuration for Replication Configure the primary Oracle database for replication and to ensure that the Oracle redo log data is not overwritten before it is read by the Replication Agent Verifying the Current Archive Setting of the Redo Logs Verify that redo logging which Oracle uses to maintain a log infrastructure is turned on Turn redo logging on only in the primary database 1 2 Use SQLPLUS to connect to Oracle as a system administrator From SQLPLUS run select log_mode from v database e If the archive log is on you should see 20
93. le systems or at multiple sites Data distribution is distinct from data replication although a data replication system can be used to implement or support data distribution Contrast with data replication data replication the process of copying primary data to remote locations and synchronizing the copied data with the primary data Data replication is different from data distribution Replicated data is a stored copy of data at one or more remote sites throughout a system and it is not necessarily distributed data Contrast with data distribution See also transaction replication data server a server that provides the functionality necessary to maintain the physical representation of a table in a database Data servers are usually database servers but they can also be any data repository with the interface and functionality a data client requires See also client client application and data client datatype a keyword that identifies the characteristics of stored information on a computer Some common datatypes are char int smallint date time numeric and float Different data servers support different datatypes DBMS an abbreviation for database management system a computer based system for defining creating manipulating controlling managing and using databases The DBMS can include the user interface for using the database or it can be a standalone data server system Compare with RDBMS ERSSD an abbreviation for
94. lication Server connection to standby database for Microsoft SQL Server 50 Replication Server from primary database 17 51 default directory 9 42 77 directories default 9 42 77 errors while starting installer 7 41 76 existing installation directories 9 42 77 IBM DB2 prerequisites 69 IBM DB2 Universal Database adding a temporary tablespace to the primary database 97 Quick Start Guide 117 Index configuring Replication Server 91 configuring Replication Server for replication from the primary database 92 configuring Replication Server for replication to replicate database 91 creating a database replication definition 94 creating a database replication subscription 94 creating a Replication Server connection to standby database 92 creating a user and granting permissions 97 creating the Replication Agent instance 98 initializing the Replication Agent instance 103 installing replication components 72 replication components 68 replication overview 68 resetting the primary database for replication 106 setting environment variables 83 testing IBM DB2 Universal Database replication 105 testing replication 105 verifying ECDA Option for ODBC installation 44 verifying the ECDA installed objects 95 verifying the Replication Agent instance 101 initializing Replication Agent instance 30 Replication Agent instance in IBM DB2 Universal Database 103 installation Replication Server CD mounting 8 76 installatio
95. lick Next without sufficient hard disk space an error occurs that stops the installation 8 Verify that you have selected the correct type of installation and that you have enough disk space to complete the process Click Next to continue the installation The installation program unloads all the components from the CD and displays a progress indicator When the installation is complete a message appears indicating that the installation program wizard has completed installing your product 9 Click Next The SySAM License Server window opens and displays this prompt Will licenses be obtained from the License Server e Select Yes if you have a pre existing SySAM network license server installed or will install a new license server Enter the host name and the port number of the license server e Select No if you do not have a SySAM license server installed and are going to use a local license file A message appears telling you to download the license file If you select Yes and the license server is not found or if you select No and the local license file is not installed on your local machine this message appears Installer can t check out a license Do you want to continue without a license 10 Click Yes to complete the installation If you select No this message appears 40 Replication Server Options 1 j Microsoft SQL Server Replication Setup Please remember to download and install the license fil after t
96. maintenance reports from the Sybase Web site 1 Point your Web browser to Attp www sybase conm suppott 2 From the menu bar or the slide out menu under Support choose EBFs Maintenance 3 If prompted enter your MySybase user name and password 4 Optional Select a filter from the Display drop down list select a time frame and click Go 5 Select a product Padlock icons indicate that you do not have download authorization for certain EBF Maintenance releases because you are not registered as an authorized support contact If Quick Start Guide 107 Obtaining Help and Additional Information you have not registered but have valid information provided by your Sybase representative or through your support contract click My Account to add the Technical Support Contact role to your MySybase profile 6 Click the Info icon to display the EBF Maintenance report or click the product description to download the software Sybase Product and Component Certifications Certification reports verify Sybase product performance on a particular platform To find the latest information about certifications e For partner product certifications go to Attp www sybase com detail_list id 9784 e For platform certifications go to Attp certification sybase com uct search do Creating a MySybase Profile MySybase is a free service that allows you to create a personalized view of Sybase Web pages 1 Go to Attp www sybase com m
97. mary database before adding the log file path to the sybfilter driver restart Microsoft SQL Server to make the log file readable 9 At the management console verify that log files are readable If some log files are unreadable make sure the files have been created and that Microsoft SQL Server has been restarted if necessary See the Replication Agent Primary Database Guide Stopping the Analysis Service Terminate the Microsoft SQL Server Analysis Service 1 From the Windows Control Panel select Administrative Tools gt Services Quick Start Guide 57 Microsoft SQL Server Replication Setup 2 3 In the listed services find the service named Microsoft SQL Server Analysis Service SERVER where SERVER is the name of your Microsoft SQL Server data server Stop this service Enabling TCP IP Enable TCP IP 1 From the Windows Start menu choose Microsoft SQL Server gt Surface Area Configuration gt Surface Area Configuration for Services and Connections 2 Under the Database Engine tree click Remote Connections Select Local and remote connections and Using both TCP IP and Named Pipes protocols Installing the Microsoft SQL Server JDBC Driver Install the Microsoft SQL Server JDBC driver 1 6 Go to the Microsoft download site and search for this string Microsoft SQL Server 2005 JDBC driver Click the link for the 1 2 driver and follow the instructions to download and install the driver
98. mized the IBM DB2 Universal Database environment script use the path to that script Otherwise use the path to the original IBM DB2 Universal Database script either db2cshrc or db2profile For example to use your customized script for C shell enter source path mydb2cshrc where path is the full path to the directory in which you saved your customized file For example to use the original IBM DB2 Universal Database script for Bourne or Korn shell enter path sqllib db2profile where path is the home directory of the owner of the replicate IBM DB2 Universal Database instance Click Next Enter the ECDA access service name that you want to use for this server and click Next Verify the ECDA Server information that appears 88 Replication Server Options IBM DB2 Universal Database Replication Setup e If correct select Create Server The Start DirectConnect server Optional window appears e Ifincorrect click Back to change the information 9 Open the sSYBASE DC 15_0 servername cfg dcany cfg file Windows or the SSYBASE DC 15_0 servername cfg dcany cfg file UNIX or Linux where servername is the name of the server you created 10 In the file locate the name of the IBM DB2 Universal Database access service that you created above Under that set the ConnectionSpec1 parameter to the name of the IBM DB2 Universal Database ODBC data source you created For example Service Library Logging LogSvcLi
99. n Agent 15 6 installation directory 5 36 70 replication components configuration 47 82 replication from the primary Microsoft SQL Server database configuring Replication Server 50 replication system diagram of Microsoft SQL Server 34 IBM DB2 Universal Database diagram 68 resetting primary Oracle database for replication 33 S setting up replication for IBM DB2 Universal Database 68 setting up replication for Oracle 3 setup prerequisites 4 35 69 system requirements 4 Index Microsoft SQL Server 35 T testing Microsoft SQL Server replication 66 Oracle replication 32 V verify connection to the replicate IBM DB2 Universal Database 89 verify connection to the replicate Microsoft SQL Server 49 verifying current archive setting of the redo logs 20 installation 14 46 82 primary Oracle configuration for replication 23 Replication Agent instance in IBM DB2 Universal Database 101 Replication Agent instance in Microsoft SQL Server 62 Replication Agent instance in Oracle 29 supplemental logging of primary key data 21 Quick Start Guide 119 Index 120 Replication Server Options
100. n Server configuration file SSYBASE REP 15_5 install lt serv er gt cfg UNIX or Linux or SYBAS REP 15_5 install lt serv er gt cfg Windows Machine where RSSD resides jdoe_host1 Quick Start Guide 99 IBM DB2 Universal Database Replication Setup rssd_password pdb_archive_remove pdb_archive_path start_instance initialize_instance Password for rssd_username SAM PLE_RS_RSSD_maint_ps Enables or disables the removal of archived USE_DEFAULT IBM DB2 Universal Database transaction log files from the path specified by pdb_ar chive_path Identifies directory path where Replication A valid directory path on the Agent expects to find archived IBM DB2 machine hosting Replication Universal Database transaction log files Agent that points to a location where IBM DB2 Universal Da tabase puts the archived transac tion log files Start the instance that was created a Initialize the Replication Agent instance Warning The rs_source_ds and the rs_source_db values must match the pds pdb values of your Replication Server primary connection name that you configured in step 3 in the procedure Creating a Replication Server connection to the primary database 3 Set the IBM DB2 Universal Database environment variables by sourcing db2cshrc db2profile or your customized versions of the scripts as described in Setting IBM DB2 Universal Database environment variables 4 Cr
101. n Server Options Oracle Replication Setup 4 Optional If your operating system has a Java 6 0 x compatible JRE or JDK installed you can use the Oracle iSQL the browser based interface to SQL PLUS demo items to connect to Oracle using the JDBC driver and to verify the connection information See Verifying a JDBC Client Installation in the Oracle JDBC Developer s Guide and Reference Guide 5 Obtain a local copy of the Oracle timezone file so Replication Agent can correctly process the Oracle timestamp with timezone datatype Note This step is required only if the Replication Agent is on a machine host other than the Oracle host and does not have access to the Oracle timezone file There are two timezone files in the SORACLE_HOME oracore zoneinfo UNIX or Linux or 3ORACLE_HOME oracore zoneinfo Windows directory e The timezone dat binary file is the default file that contains the most commonly used time zones e The timezlrg dat binary file contains a larger set of defined time zones The Oracle instance uses the timezone file from the ORA_TZFILE environment variable If ORA_TZFILE is not set use the default t imezone file Determine which t imezone file is used by the primary Oracle instance and make a copy of that file available on the machine hosting the Replication Agent Note These files are Oracle version and platform dependent You cannot use a timezone file from a little endian platform o
102. n a big endian platform nor can you use a timezone file from a version of Oracle that is different than the version of the primary Oracle 6 Locate the Replication Agent for Oracle resource file template The majority of configuration values required to create and initialize a Replication Agent can be recorded and stored in a resource file Using a resource file provides a means to record or retain the configuration information for a Replication Agent instance allowing an instance to be removed and re created The resource file template oracle rs is in the Replication Agent directory SSYBASE RAX 15_5 init UNIX or Linux or 3SYBASE RAX 15_5 init Windows 7 Create an instance resource file Copy the resource file template SYBASE RAX 15_5 init oracle rs UNIX or Linux or SYBASE RAX 15_5 init oracle rs Windows to another file that contains the configuration information for a new instance for example myra rs Change the values assigned to properties in the resource file so they match the values required for your environment The majority of these values are the location user and Quick Start Guide 25 Oracle Replication Setup password information listed in the tnsname ora file for the Oracle Replication Server and RSSD connections e Set the value of rs_source_ds to the value you chose for pds e Set the value of rs_source_db to the value you chose for pdb The resource file is self documenting with
103. n directories existing 9 42 77 new 9 42 77 installation guidelines for ECDA Option for ODBC 38 72 for Replication Agent 12 44 79 for Replication Server 7 75 Replication Server for Microsoft SQL Server 41 installation program Replication Agent 12 44 79 installer errors in starting 7 41 76 installing 58 ExpressConnect for Oracle 11 installing ECDA Option for ODBC 38 72 L license agreement 10 42 78 Microsoft SQL Server configuring and verifying the primary database 54 configuring Replication Server 50 creating a DirectConnect instance for 48 creating a maintenance user for 50 installing Replication Server 41 resetting the primary database for replication 67 system requirements 35 testing replication 66 verifying the Replication Agent instance 62 Microsoft SQL Server JDBC driver 58 mount commands by platform 8 76 mounting the Replication Server installation media 8 76 N new installation directories 9 42 77 O Oracle installing replication components 7 installing Replication Server 7 75 system requirements 4 testing replication 32 verifying the Replication Agent instance 29 P planning your installation replication components 4 35 69 platform mount commands 8 76 prerequisites 36 70 Oracle 5 prerequisites for installing replication components 35 118 Replication Server Options R replication components 3 34 overview 3 34 Replication Agent installing 12 44 79 Replicatio
104. n who go You should see a message returned for the DSI connection msss_dco dbname Verify that the status is Awaiting Message or Awaiting Command 6 Exit the isql session with the quit command See also e Creating a Maintenance User for Replication in Microsoft SQL Server on page 50 Configuring Replication Server for Replication from the Primary Database Configure Replication Server for replication from the primary database Creating a Replication Server Connection to the Primary Database Create a Replication Server connection to the primary database 1 Go to the 3SYBASE RAX 15_5 scripts sybase directory 2 Make a copy ofthe rs_create_test_primary_connection sql script named my_mssql_rs_create_primary_connection sql Quick Start Guide 51 Microsoft SQL Server Replication Setup 3 Before executing the my_mssql_rs_create_primary_connection sql script against your Replication Server change rax fest to the name of the connection between Replication Agent and Replication Server where e raxis the name of the primary Microsoft SQL Server data server e testis the name of the primary Microsoft SQL Server database For example NYNYmss5 Note Save these values to be used later for the rs_source_ds and rs_source_db parameters in the Replication Agent mssql rs file 4 Execute the script in Replication Server isell usa SD SGAM INS al my_mssql_rs_create_primary_connection sql You see a mes
105. ng Started CD Product Documentation site and online help to learn more about this product release e The Getting Started CD or download contains release bulletins and installation guides in PDF format and may contain other documents or updated information e Product Documentation at Attp sybooks sybase com is an online version of Sybase documentation that you can access using a standard Web browser You can browse documents online or download them as PDFs In addition to product documentation the Web site also has links to EBFs Maintenance Technical Documents Case Management Solved Cases Community Forums Newsgroups and other resources e Online help in the product if available To read or print PDF documents you need Adobe Acrobat Reader which is available as a free download from the Adobe Web site Note A more recent release bulletin with critical product or document information added after the product release may be available from the Product Documentation Web site Technical Support Get support for Sybase products If your organization has purchased a support contract for this product then one or more of your colleagues is designated as an authorized support contact If you have any questions or if you need assistance during the installation process ask a designated person to contact Sybase Technical Support or the Sybase subsidiary in your area Downloading Sybase EBFs and Maintenance Reports Get EBFs and
106. nload e Replication Server 15 6 Replication Server Options Oracle Replication Setup e Replication Agent 15 6 e ExpressConnect for Oracle 15 5 ESD 1 10 Uncompress and extract all the installation images to your local drive Installing Replication Components Review the procedures for installing the replication components See also e Configuring Replication Components on page 15 Installing Replication Server Install Replication Server using the setup program Prerequisites e Allocate a disk partition of at least 20MB for each Replication Server you are installing You can add more partitions later if necessary Check each partition to make sure it is available and has write permissions e Allocate the entire partition to the Replication Server If you allocate only a portion of the partition to Replication Server you cannot use the remainder for any other purpose Task 1 Verify that the drive on which you install the Replication Server has enough disk space for the components being installed and at least 100MB of extra disk space for the installation program 2 Insert the Replication Server media in the appropriate drive or download and extract the Replication Server installation image from the Sybase Product Download Center SPDC 3 Start the installation e On Windows launch the setup program Note If the installer does not start automatically double click setup exe or select Start gt Run and e
107. nstall 92 Replication Server Options IBM DB2 Universal Database Replication Setup with log transfer on dsi_suspended go 5 Execute this script in Replication Server TS SWS Se SiSvVAMIP IIa IRS ak my_udb_rs_create_test_primary_connection sql You see a message indicating that the Replication Server connection to the primary database has been created See also e Creating a Database Replication Definition on page 94 e Creating a Database Replication Subscription on page 94 Verifying the ECDA Installed Objects on page 95 Granting create object Permission to the rs_username User Grant create object permission to the user ID specified by the Replication Agent rs_username parameter The rs_username user must have create object permission before it can be used to create a replication definition You must grant this permission manually from the RSSD 1 Log in to Replication Server isql Usa P SSAMPLE_RS 2 Enter grant create object to SAMPLE_RS_ra go where SAMPLE_RS ra is the value for the rs_username parameter you entered in the resource file 3 Log in to the RSSD isql USAMPLE_RS_RSSD_prim PSAMPLE_RS_RSSD_prim_ps SSAMPLE_RS_RSSD where SAMPLE_RS_RSSD SAMPLE_RS_RSSD_prim SAMPLE_RS_RSSD_prim_ps are the RSSD database name user ID and password needed to log in to the RSSD 4 Verify that the rs_username user has create object permission select fr
108. nstaller displays the configuration information for the sample Replication Server Write this information down Click Next 12 If you chose to configure and start a sample Replication Server the installer proceeds to configure and start the sample Replication Server Click Next when done Quick Start Guide 43 Microsoft SQL Server Replication Setup 13 When the final window appears indicating a successful installation click Done to exit the installer Verifying the Installation Verify that Replication Server has been successfully installed 1 In acommand window change to the directory where you installed Replication Server 2 Log in to Replication Server Ss Clee US cu pe oO AMEAE ES 3 Verify the Replication Server version admin version go 4 Exit the isql session with the quit command Installing Replication Agent Install Replication Agent using the GUI wizard Note If there is not enough disk space in your default temporary directory set the TMP Windows environment variable to a directory that has enough space Include the full path to this directory 1 Log into the Replication Agent host machine using an operating system user account with authority to start stop and administer the Replication Agent instance for example the sybase user 2 Close all nonessential applications and minimize any open windows 3 Insert the Replication Agent 15 6 distribution media in the appropriate drive
109. nter the following where x is your CD drive x setup exe If there is not enough disk space in the temporary disk space directory set the environment variable TMP to directory_name before running it again where directory_name is the name of the temporary directory to which the installation Quick Start Guide 7 Oracle Replication Setup program writes the temporary installation files When specifying the directory_name include its full path e On UNIX or Linux If you downloaded the product from SPDC go to the directory where you extracted the installation image and start the installer setup bin If you are installing using the CD mount it and start the installer Note The location of the mount command is site specific and may differ from the instructions shown here If you cannot mount the CD drive using the path shown check your operating system documentation or contact your system administrator On HP UX Itanium 1 Log out log in as root and mount the CD using mount F cdfs o ro device_name mnt cdrom 2 Log out as root and log in again as sybase 3 Start the installer cd cdrom setup bin On IBM AIX 1 Log in as sybase and mount the CD mount v cdrfs r device name mnt cdrom 2 Start the installer cd device_name setup bin On Sun Solaris 1 The operating system automatically mounts the CD Log in as sybase Note If you get CD reading errors check your o
110. ntrast with primary site See also replicate database Replication Agent an application that reads a primary database transaction log to acquire information about data changing transactions in the primary database processes the log information and then sends it to a Replication Server for distribution to a replicate database See also primary database and Replication Server replication definition a description of a table or stored procedure in a primary database for which subscriptions can be created The replication definition maintained by Replication Server includes information about the columns to be replicated and the location of the primary table or stored procedure See also Replication Server and subscription Replication Server a Sybase software product that provides the infrastructure for a transaction replication system See also Replication Agent replication system a data processing system that replicates data from one location to another Data can be replicated between separate systems at a single site or from one or more local systems to one or more remote systems See also transaction replication rollback an instruction to a database to back out of the changes requested in a unit of work called a transaction Contrast with commit See also transaction route A one way message stream from a primary Replication Server to a replicate Replication Server Routes carry data changing commands including those f
111. ntry type LOCAL Protocol TCPIP Hostname OR Ae OS Service name 50001 5 If your desired node is not listed catalog it at the CLP prompt e To catalog a local node CATALOG LOCAL NODE node_name INSTANCE db2_instance_name Quick Start Guide 85 IBM DB2 Universal Database Replication Setup where e node_name is the name of the node on the local host e db2_instance_name is the name of the associated DB2 instance e To catalog a remote node CATALOG TCPIP NODE node_name REMOTE ip_address SERVER port_number where e node_name is the name of the node on the remote host e p addressis either the IP address or name of the host on which the remote node is running e port numberis the port on which the remote node is listening for client connections 6 Verify that the node has been cataloged by issuing the list node directory command again Cataloging a Database in an IBM DB2 Universal Database Client Catalog a database in an IBM DB2 Universal Database 1 Determine if the database has already been cataloged At the CLP command prompt execute list database directory Depending on whether the database is local first entry below or remote second entry below you see results similar to this Database 1 entry Database alias GRP_DB Database name GRP_DB Local database directory db2home db2instl Database release level 0 00 Comment UTF 8 Unicode DB Direc
112. om rs_users where username SAMPLE_RS_ra go The rs_users system table should indicate that the rs_username user has create object permission Quick Start Guide 93 IBM DB2 Universal Database Replication Setup Creating a Database Replication Definition Create a database replication definition 1 Navigate to the SSYBASE RAX 15_5 script sybase UNIX or Linux or SYBASE S RAX 15_5 script sybase Windows directory Make a copy of the rs_create_test_db_repdef sql script named my_udb_rs_create_test_db_repdef sql Before executing the my_udb_rs_create_test_db_repdef sq script change the value of pds pdb to the name of the connection string you defined for the primary database where e pds is the name of the data server of the primary IBM DB2 Universal Database e pdbis the name of database of the primary IBM DB2 Universal Database For example NY N Yudb9 Note Save these values to be used later for the rs_source_ds and rs_source_dbparameters in the Replication Agent ibmudb rs file Here is a database replication definition example create database replication definition NY_repdefl with primary at NY NYudb9 go Connect to the Replication Server and enter isql Usa P SSAMPLE_RS i my_udb_rs_create_test_db_repdef sql You see a message indicating that the database replication definition has been created See also Creating a Database Replication Subscription on page 94 Veritying the ECDA
113. or RSSDs and replicated functions database procedures between separate Replication Servers See also Replication Server RSSD an abbreviation for Replication Server System Database which manages replication system information for a Replication Server See also Replication Server SQL an abbreviation for Structured Query Language a nonprocedural programming language used to process data in a relational database ANSI SQL is an industry standard See also transaction stable queue a disk device based store and forward queue managed by Replication Server Messages written into the stable queue remain there until they can be delivered to the appropriate process or replicate database Replication Server provides a stable queue for both incoming messages the inbound queue and outgoing messages the outbound queue See also database connection Replication Server and route subscription a request for Replication Server to maintain a replicated copy of a table or a set of rows from a table in a replicate database at a specified location See also replicate database replication definition and Replication Server table in a relational DBMS a two dimensional array of data or a named data object that contains a specific number of unordered rows composed of a group of columns that are specific for the table See also database transaction a unit of work in a database that can include zero one or many operations including inser
114. ou can continue with the installation Click Next On the Sybase Software Asset Management License Server window select one of Specify License Keys click Browse to select the license file To select multiple license files use Shift Click or Ctrl Click The license pane displays the license information Alternatively copy and paste the license information directly in the license pane Click Next If you specify a served license key the installer prompts you to install a new SySAM license server Click one of e Next to install the new SySAM license server and follow the installation prompts e Previous and select Use previously deployed license server if you have an existing SySAM license server on the same host Use Previously Deployed License Server if you have a previously deployed license server enter the host name of the machine where the license server is running and the port number if the port number you are using is not default Click Next Continue installation without a license key if you do not have licenses for any of the Replication Server components you can select this option and click Next to proceed The installer allows you to install and use the Replication Server components without a license for a grace period of 30 days To continue using these components after the end of the grace period obtain valid licenses from the SPDC and install these licenses using the Replication Server Li
115. password information for the Microsoft SQL Server Replication Server and RSSD connections e Set the value of rs_source_ds to the value you chose for rax and pds in previous procedures e Set the value of rs_source_db to the value you chose for fest and pdb in previous procedures The resource file is self documenting with each parameter described See the Replication Agent Administration Guide Warning The combined values of rs_source_ds and rs_source_db must match the pds pdb values of the Replication Server primary connection name Table 2 Resource File Parameters Parameter Description Example values admin_port Port number that Replication Agent uses 20500 if in use select a differ ent port number Name of the primary Microsoft SQL Server TEAMSTER pds_port_number Port number for the primary Microsoft SQL 1433 Server pds_dac_port_number Microsoft SQL Server Dedicated Adminis 1348 tration connection port number pds_database_name Name of the primary database pds_username User ID that Replication Agent uses to access ra_user the primary data server pds_password Password for pds_username ee es led Port where Replication Server is installed 11752 Replication Server user with connect source SAMPLE_RS_ra and create object permissions Quick Start Guide 59 Microsoft SQL Server Replication Setup Parameter Description Example values Valid name representing data server of pri NY ma
116. path e On UNIX or Linux If you downloaded the product from SPDC go to the directory where you extracted the installation image and start the installer setup bin If you are installing using the CD mount it and start the installer Note The location of the mount command is site specific and may differ from the instructions shown here If you cannot mount the CD drive using the path shown check your operating system documentation or contact your system administrator e On HP UX Itanium 1 Log out log in as root and mount the CD using mount F cdfs o ro device_name mnt cdrom 2 Log out as root and log in again as sybase 3 Start the installer cd cdrom setup bin e On IBM AIX 1 Log in as sybase and mount the CD mount v cdrfs r device_name mnt cdrom 2 Start the installer cd device_name setup bin e On Sun Solaris 1 The operating system automatically mounts the CD Log in as sybase Note If you get CD reading errors check your operating system kernel to make sure that the ISO 9660 option is turned on 76 Replication Server Options IBM DB2 Universal Database Replication Setup If you have previously installed a Sybase CD on your system the sign will interfere with the installation process To avoid this perform one of the following actions before installing your current CD e Restart your system e Eject the CD Delete the Volume Labe file in vol dsk
117. perating system kernel to make sure that the ISO 9660 option is turned on If you have previously installed a Sybase CD on your system the sign will interfere with the installation process To avoid this perform one of the following actions before installing your current CD e Restart your system e Eject the CD Delete the Volume Labe file in vol dsk and reinsert the CD 2 Start the installer cd cdrom Volume Label setup bin On Linux and Linux on POWER 1 Log in as sybase and mount the CD mount t iso9660 dev cdrom mnt cdrom Replication Server Options Oracle Replication Setup 2 Start the installer cd mnt cdrom setup bin where e cdrom and mnt cdromis the directory mount point you specified when mounting the CD drive e device nameis the name of the CD device drive e setup bin is the executable file name to install Replication Server If there is not enough disk space in the temporary disk space directory set the environment variable IATEMPDIR to tmp_dir before running the installer again where mp_diris the temporary directory to which the installation program writes the temporary installation files When specifying the tmp_dir include the full path 4 Inthe Introduction window click Next 5 Indicate where to install Replication Server e To select an installation directory click Choose A window appears and from that window browse for and select the directory e To cr
118. plication cceeeeeeee 106 Obtaining Help and Additional Information 107 Technical Sup DOM daisi esea a 107 Downloading Sybase EBFs and Maintenance Reports EEE E E A E E 107 iv Replication Server Options Contents Sybase Product and Component Certifications 108 Creating a MySybase Profile eeeeeeeeeeeees 108 Accessibility Features cccccceecsecceeeeeeeeeeeeeeeeeees 108 GOS SAY satis tec teiec sated A T 109 MINOX E T E 117 Quick Start Guide v Contents vi Replication Server Options Conventions Conventions Learn about the style and syntax conventions used in Sybase documentation SQL and program code Style conventions monospaced fixed wigen Commands to be entered exactly as shown File names Directory names italic monospaced In SQL or program code snippets placeholders for user specified values see example below italic File and variable names Cross references to other topics or documents In text placeholders for user specified values see example be low Glossary terms in text bold sans serif Command function stored procedure utility class and meth od names Glossary entries in the Glossary Menu option paths In numbered task or procedure steps user interface UI ele ments that you click such as buttons check boxes icons and so on If necessary an explanation for a placehol
119. r Options terms e Adaptive Server the brand name for Sybase relational database management system RDBMS software products e Adaptive Server Enterprise manages multiple large relational databases for high volume online transaction processing OLTP systems and client applications e Sybase IQ manages multiple large relational databases with special indexing algorithms to support high speed high volume business intelligence decision support and reporting client applications e SQL Anywhere formerly Adaptive Server Anywhere manages relational databases with a small DBMS footprint which is ideal for embedded applications and mobile device applications See also DBMS and RDBMS e atomic materialization a materialization method that copies subscription data from a primary database to a replicate database in a single atomic operation No changes to primary data are allowed until the subscription data is captured at the primary database See also bulk materialization and nonatomic matertalization e BCP utility a bulk copy transfer utility that provides the ability to load multiple rows of data into a table in a target database See also bulk copy bulk copy an Open Client interface for the high speed transfer of data between a database table and program variables Bulk copying provides an alternative to using SQL insert and select commands to transfer data bulk materialization a materialization method wh
120. racle database for replication 20 replication components 15 replication components for Microsoft SQL Server 47 Replication Server for Microsoft SQL Server 50 Replication Server for replication to standby 50 configuring Replication Agent 20 96 configuring and validating the resource file 58 configuring the primary IBM DB2 Universal database for replication 96 creating a Microsoft SQL Server user and granting permissions 55 disabling Microsoft SQL Server replication 35 enabling remote DAC 55 first time initialization 61 obtaining Microsoft SQL Server DAC port number 55 setting the Autocommit parameter 96 subsequent initialization 63 verifying compatibility level of Microsoft SQL Server 54 verifying current archive setting of transaction log 96 verifying the Replication Agent replicating state 64 configuring Replication Server 15 Index for replication from the primary database 15 16 50 51 for replication to standby 15 creating database replication definition 53 database replication definition for IBM DB2 Universal Database 94 database replication definitions 19 database replication subscription for IBM DB2 Universal Database 94 database replication subscriptions 19 53 DirectConnect instance 48 DirectConnect service 88 maintenance user 50 90 Oracle user 21 Replication Agent instance 24 Replication Agent instance in IBM DB2 Universal Database 98 Replication Server connection to standby database 15 92 Rep
121. rd and therefore must be enclosed in double quotes If you are not using an Oracle ECO connection profile set dsi_proc_as_rpc to on in the create connection command For example create connection to OracleSID_name OracleSID_name set error class lt error_class gt SC varnUinGislommEs CENO CASS Ue ONEENS S set username lt userid gt set password lt password gt set dsi_dataserver_make to ora set dsi_dataserver_type to oci SCuslatc hut Omori ie SiCeaC Sh OO Cm SENG CHL OmOm If you are using an Oracle ECO connection profile dsi_proc_as_rpc is enabled by default For configuring the server connectivity for ECO copy the tnsnames ora file to the RS_installation_directory REP 15_5 connector oraoci network admin directory Create the connection to the replicate database Leal Usa SSSyNMIP Ia RE a my_oracle_create_rs_standby_connection sql Log in to SAMPLE_RS and verify the Replication Server connection to the replicate database SC Usama SOAME Ini GS admin who go You should see a message returned for the DSI connection rds rdb Verify that the status is Awaiting Message or Awaiting Command Exit the isql session with the quit command Replication Server Configuration for Replication from the Primary Database Configure Replication Server for replication from the primary database It involves Creating a Replication Server Connection to the Primary Database
122. re existing SySAM network license server installed or will install a new license server 74 Replication Server Options IBM DB2 Universal Database Replication Setup Enter the host name and the port number of the license server e Select No if you do not have a SySAM license server installed and are going to use a local license file A message appears telling you to download the license file If you select Yes and the license server is not found or if you select No and the local license file is not installed on your local machine this message appears Installer can t check out a license Do you want to continue without a license 10 Click Yes to complete the installation If you select No this message appears 1 j Please remember to download and install the license fil after this installation Click OK The SySAM Notification window asks you to configure your server for e mail notification When configuration is enabled you will receive information about license management events requiring attention Enter the following e SMTP server host name e SMTP server port number e E mail Return Address e Recipient e mail addresses e Message severity that triggers e mail messages Click Next 12 A window displays a message indicating that the installation was successful and advising you to check for software updates Click Finish Installing Replication Server Install Replication Server using
123. rietary protocol used between Replication Agent and Replication Server to replicate data from the primary database to Replication Server See also Log Reader and Log Transfer Interface Log Transfer Manager an internal component of the Replication Agent that interacts with the other Replication Agent internal components to control and coordinate Replication Agent operations See also Log Reader and Log Transfer Interface maintenance user a special user login name in the replicate database that Replication Server uses to apply replicated transactions to the database See also replicate database and Replication Server materialization the process of copying the data from a primary database to a replicate database initializing the replicate database so that the replication system can begin replicating transactions See also atomic materialization bulk matertalization and nonatomic materialization nonatomic materialization a materialization method that copies subscription data without a lock on the primary database Changes to primary data are allowed during data transfer which may cause temporary inconsistencies between the primary and replicate databases Contrast with atomic materialization See also bulk matertalization ODBC an abbreviation for Open Database Connectivity an industry standard communication protocol for clients connecting to data servers See also client data server and JDBC Open Client a Sybase product t
124. rk located on the user premises and covering a limited geographical area usually a single site Communication within a local area network is not subject to external regulations however communication across the LAN boundary can be subject to some form of regulation Contrast with WAN e latency in transaction replication the time it takes to replicate a transaction from a primary database to a replicate database Specifically latency is the time elapsed between committing an original transaction in the primary database and committing the replicated transaction in the replicate database In disk replication latency is the time elapsed between a disk write operation that changes a block or page on a primary device and the disk write operation that changes the replicated block or page on a replicate device See also transaction replication e LOB an abbreviation for large object a large collection of data stored as a single entity in a database Quick Start Guide 111 Glossary Log Reader an internal component of the Replication Agent that interacts with the primary database to capture transactions for replication See also Log Transfer Interface and Log Transfer Manager Log Transfer Interface an internal component of the Replication Agent that interacts with Replication Server to forward transactions for distribution to Replication Server See also Log Reader and Log Transfer Manager Log Transfer Language the prop
125. rt user or password are incorrect b ma Verify the host and port configuration values and manually log in to the Replication Server as the configured user to determine which property is incorrectly defined See the Replication Agent Administration Guide 4 Verify the Replication Agent connection to the primary Oracle database a Enter test_connection PDS Quick Start Guide 29 Oracle Replication Setup go You see Type Connection PDS succeeded 1 row affected b Ifthe result indicates a failure e The server is not responding or e One of the connection properties is incorrect Check the tnsnames ora file and tnsnames configuration values and manually log in to the primary Oracle database as the configured user to find which property is incorrectly defined See Testing network connectivity in the Replication Agent Administration Guide See also e Initializing the Replication Agent Instance on page 30 Initializing the Replication Agent Instance Initialize the Replication Agent instance Note If you set initialize_instance to yes in your resource file and if you received no errors while the instance was created skip to step 2 1 The pdb_xlog init command verifies that the primary Oracle database is correctly configured to provide archived logging and supplemental logging and that the Oracle user ID used by the Replication Agent has the necessary permissions In addition to creating objects
126. rt to be used by the DirectConnect server is available See also e Creating a Maintenance User for Replication in Microsoft SQL Server on page 50 Creating and Configuring a DirectConnect Server Use the Create Server wizard to create a DirectConnect service 1 Start the Create Server wizard by navigating tothe SSYBASE DC 15_0 DCWizard directory and executing the DCWizard script The Welcome Create Server wizard appears Click Next 2 Select ECDA Option for ODBC Click Next 3 Enter the server name for the new DirectConnect server and the port number that you want the server to listen on Click Next 4 Enter the ECDA access service name that you want to use for the replicate Microsoft SQL Server database for example my_mssql_access_service Click Next 5 Verify the DirectConnect server information If correct select Create Server Otherwise click Back to return to the previous window and provide the correct information 6 Open the SYBASE DC 15_0 servers servername cfg dcany cfg file where servername is the name of the server you created 7 In the file locate the name of the Microsoft SQL Server access service that you created Under that set the ConnectionSpec1 parameter to the name of the Microsoft SQL Server ODBC data source you created For example Service Library Client Interaction SvclibDescription Access Service Library for ODBC Logging 48 Replication Server Options Microsoft
127. ry Microsoft SQL Server database rs_source_db Valid name representing primary Microsoft NYmss5 SQL Server database Character set that Replication Server is using Note The value defined for the rs_charset configuration parameter must match the RS_charset value in the Replication Server configuration file sSYBASE REP 15_5 install lt serv er gt cfg rssd_password Password for rssd_username SAM PLE_RS_RSSD_maint_ps start_instance Automatically start the instance that was cre yes ated initialize_instance Automatically initialize the new Replication yes Agent instance 3 Be sure your CLASSPATH environment variable points to the Microsoft SQL Server JDBC driver and use the Replication Agent ra_admin utility to validate the settings in the new instance resource file using the vr parameter For example SYBASE RAX 15_5 bin ra_admin vr myra rs Validation results are returned as either e Response file processing completed Or e Response file processing completed with errors If any validation fails the ra_admin utility returns an error message and information about the failure You can repeat the validation process as many times as necessary until it executes without error No entities are changed or created See also e First Time Initialization on page 61 e Subsequent initialization on page 63 60 Replication Server Options Microsoft SQL Server Replication Setup e Verifying
128. sage indicating that the Replication Server connection to the primary database has been created See also e Creating a Database Replication Definition on page 53 e Creating a Database Replication Subscription on page 53 Granting create object Permission to the rs_username User Grant create object permission to the user ID specified by the Replication Agent rs_username parameter The rs_username user must have create object permission before it can be used to create a replication definition You must grant this permission manually from the RSSD 1 Log in to Replication Server isql Usa P SSAMPLE_RS 2 Enter grant create object to SAMPLE_RS_ra go where SAMPLE_RS ra is the value for the rs_username parameter you entered in the resource file 3 Log in to the RSSD isql USAMPLE_RS_RSSD_prim PSAMPLE_RS_RSSD_prim_ps SSAMPLE_RS_RSSD where SAMPLE_RS_RSSD SAMPLE_RS_RSSD_prim SAMPLE_RS_RSSD_prim_ps are the RSSD database name user ID and password needed to log in to the RSSD 52 Replication Server Options Microsoft SQL Server Replication Setup 4 Verify that the rs_username user has create object permission select from rs_users where username SAMPLE_RS_ra go The rs_users system table should indicate that the rs_username user has create object permission Creating a Database Replication Definition Create database replication definition 1 Navigate to the 3SYBASE R
129. se However because Oracle expects a commit command to be issued follow simple test commands with an explicit commit command insert into Ti values 42 roos js commit 9 Use your preferred Oracle query tool to examine the replicate site for results and compare the contents of your test table from both the primary and replicate sites Resetting the Primary Oracle Database for Replication In a test environment you may occasionally need to reset the replication environment 1 Archive all current redo log data pdb_xlog move_truncpt 2 Reinitialize Replication Agent which refreshes the Replication Agent repository rather than overwriting it pdb_xlog init force Note If you prefer to delete and replace all the information in the Replication Agent repository issue the pdb_xlog remove force command followed by a normal pdb_xlog init command without the force option 3 Reset the locator stored in Replication Server isql USAMPLE_RS_RSSD_prim PSAMPLE SSAMPLE_RS_ERSSD DSAMPLE_RS_ERSSD SECO icin INNC INNeenee 2 RS_RSSD_prim_ps go You see Locator has been reset to zero return status 0 Quick Start Guide 33 Microsoft SQL Server Replication Setup Microsoft SQL Server Replication Setup Implement a sample replication system for Microsoft SQL Server Note Use the tasks in the Quick Start Guide to set up replication only for proof of concept POC or tes
130. se pane Click Next If you specify a served license key the installer prompts you to install a new SySAM license server Click one of e Next to install the new SySAM license server and follow the installation prompts e Previous and select Use previously deployed license server if you have an existing SySAM license server on the same host Use Previously Deployed License Server if you have a previously deployed license server enter the host name of the machine where the license server is running and the port number if the port number you are using is not default Click Next Continue installation without a license key if you do not have licenses for any of the Replication Server components you can select this option and click Next to proceed The installer allows you to install and use the Replication Server components without a license for a grace period of 30 days To continue using these components after the end of the grace period obtain valid licenses from the SPDC and install these licenses using the Replication Server License Installer See the SySAM Users Guide 78 Replication Server Options IBM DB2 Universal Database Replication Setup 9 Configuring your server for e mail notification enables designated users to receive information about license management events requiring attention Provide e SMTP server host name e SMTP server port number e E mail return address e Recipient e mail a
131. select an installation directory in the file browser Click Next e Enter a directory name Click Next If the directory name does not exist click Yes when prompted to create it The directory does not exist Do you want to create it If the directory you specified exists you see Warning You have chosen to install into an existing directory If you proceed with this installation any older versions of the products you choose to 80 Replication Server Options IBM DB2 Universal Database Replication Setup install that are detected in this directory will be replaced If you continue and the older products were not installed with Replication Agent 15 6 the installation program overwrites the common files Note On Microsoft Windows platforms if you are prompted to overwrite a DLL click Yes only if the version of the new DLL is newer than the one the installation program is attempting to overwrite 9 Select the primary database the Replication Agent will connect to Click Next 10 Select one of these options to enter the license e Specify license keys browse to or specify the license file e Use previously deployed license server use a previously deployed license server Enter the host name of the machine where the license server is running and the port number if the port number you are using is not the default e Continue installation without a license key install and use Replication Agent without a
132. server Enter the host name of the machine where the license server is running and the port number if the port number you are using is not the default e Continue installation without a license key install and use Replication Agent without a license for a grace period of 30 days To continue using Replication Agent after the end of the grace period obtain a valid license from the Sybase Product Download Center at Attp www sybase com detail 7id 1025266 and install it Note Replication Agent is licensed statically when installed with RSHE If you are installing Replication Agent with RSHE locate the file that contains a valid SySAM Quick Start Guide 13 Oracle Replication Setup license for the Replication Agent component in RSHE 15 6 Specify that license file here See the Sybase Software Asset Management Users Guide Click Next until you see the Sybase Software Asset Management Notification window 11 On the Sybase Software Asset Management Notification window configure your server for e mail notification When configuration is enabled you receive information about license management events that require attention Select Yes and either accept the default values that are supplied or enter values for e SMTP server host name e SMTP server port number e E mail return address e Recipients of the notification e Message severity level of an event that triggers e mail notification e Informational e Warning e Error
133. services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world Java and all Java based marks are trademarks or registered trademarks of Sun Microsystems Inc in the U S and other countries Unicode and the Unicode Logo are registered trademarks of Unicode Inc All other company and product names mentioned may be trademarks of the respective companies with which they are associated Use duplication or disclosure by the government is subject to the restrictions set forth in subparagraph c 1 ii of DFARS 52 227 7013 for the DOD and as set forth in FAR 52 227 19 a d for civilian agencies Sybase Inc One Sybase Drive Dublin CA 94568 Contents CONVEDMOIMNS 5 ccxihcsakecthesiscenhexesnenhexninedaseknenhemeiaeihasniamntanbatatenans 1 Oracle Replication Setup cccceseeeceeeeeesseeeeeeeseeeeeeeeees 3 Sybase Replication System ccccccssssccsceecteteeeeeeees 3 Prerequisites 26 co cect att secbaeh tiers Lectern eet beet eae ee tena 4 Planning Your Installation ac ex cgycesechenteevecisieesteteceaaen tease 4 System Requirement ssccceeeceeeeaeeeeeeseees 4 Identify the Sybase Installation Directory 5 Accessing and Downloading Products from SP DG secs cisiss sp e ar 5 Installing Replication Component ccccceeeeeeeeees 7 Installing Replication Server c
134. steps are optional because they were performed when you verified the resource file Verify the Replication Agent connection to Replication Server a Enter test_connection RS go b This result is returned Type Connection RS succeeded 1 row affected c Ifthe result indicates a failure either the server is not responding or the connection properties host port user or password are incorrect Verify the host and port configuration values and manually log in to the Replication Server as the configured user to determine which property is incorrectly defined See the Replication Agent Administration Guide Verify the Replication Agent connection to the primary IBM DB2 Universal Database a Enter test_connection PDS go You see Type Connection PDS succeeded 1 row affected b Ifthe result indicates a failure 102 Replication Server Options IBM DB2 Universal Database Replication Setup e The server is not responding or e One of the connection properties is incorrect Manually log in to the primary IBM DB2 Universal Database as the configured user to find which property is incorrectly defined See Testing network connectivity in the Replication Agent Administration Guide See also e Initializing the Replication Agent Instance on page 103 Initializing the Replication Agent Instance Initialize the Replication Agent instance The pdb_xlog init command verifies that the primary
135. t update and delete operations and that is either applied or rejected as a 114 Replication Server Options Glossary whole Each SQL statement that modifies data can be treated as a separate transaction if the database is so configured See also SQL e transactional consistency A condition in which all transactions in the primary database are applied in the replicate database and in the same order that they were applied in the primary database e transaction log generally the log of transactions that affect the data managed by a data server Replication Agent reads the transaction log to identify and acquire the transactions to be replicated from the primary database See also Replication Agent primary database and Replication Server e transaction replication a data replication method that copies data changing operations from a primary database to a replicate database See also data replication e WAN an abbreviation for wide area network a system of local area networks LANs connected together with data communication lines Contrast with LAN Quick Start Guide 115 Glossary 116 Replication Server Options Index A accessing and downloading from SPDC 5 36 70 Adaptive Server Enterprise verifying Replication Server installation 11 79 C cataloging a node and a database 84 configuring ECDA Option for ODBC 47 87 ExpressConnect for Oracle 20 Microsoft SQL Server ODBC driver 47 Primary O
136. t is needed to run other selected components After you have selected either the ECDA Option for ODBC or ECDA Option for Oracle and have selected the desired components click Next e Ifyou selected Full install the next window displays all the ECDA options including ECDA Option for ODBC and ECDA Option for ODBC and all the components checked or selected Click Next Before proceeding to the next window the installation program verifies the selections and checks for dependencies and available disk space The Product Summary window displays every component that is to be installed and the total disk space required for all the selected components Note If the target directory does not have enough free space the space required and the space available information is displayed If you click Next without sufficient hard disk space an error occurs that stops the installation Verify that you have selected the correct type of installation and that you have enough disk space to complete the process Click Next to continue the installation The installation program unloads all the components from the CD and displays a progress indicator When the installation is complete a message appears indicating that the installation program wizard has completed installing your product Click Next The SySAM License Server window opens and displays this prompt Will licenses be obtained from the License Server e Select Yes if you have a p
137. t must be installed on a host from which it can directly access the primary Microsoft SQL Server instance transaction log Replication Agent 15 6 Installation Directory Replication Agent 15 6 uses the RAX 15_5 directory name If you have an existing Replication Agent 15 5 installation and do not want to overwrite it install Replication Agent 15 6 in a different location Accessing and Downloading Products from SPDC If you are not using the installation CD download replication components from the Software Product Download Center SPDC Prerequisites You must have a login ID and password to download software from the SPDC Task Note If you are installing Replication Server Options RSO this task requires you to perform several steps twice as you are downloading Replication Server and other products 1 Go to Attps sybase subscribenet com controlsybs login and enter your login ID and password 2 Select Replication Server 3 From the high level Product Information page select Replication Server again 4 If you are installing Replication Server Heterogeneous Edition RSHE skip to Step 7 If you are installing RSO select the Replication Server software you need by version and platform The Software Terms and Conditions page appears listing countries and regions 5 Either e Select I Agree go to the Product Download Page and continue with step 6 or e Select your country or region to read the terms The softwar
138. t test data into the test table and commit it By default the work performed by the maintenance user of the primary connection is not replicated The user ID used to insert transactions cannot be the same as the maintenance user defined in the primary connection 66 Replication Server Options Microsoft SQL Server Replication Setup Replication Agent applies only committed transactions to a replicate database iiqsteuac alice IIL valss 2 sE go 9 Use your preferred Microsoft SQL Server query tool to examine the replicate site for results and compare the contents of your test table from both the primary and replicate sites See also e Configuring Replication Server for Replication from the Primary Database on page 51 Resetting the Primary Microsoft SQL Server Database for Replication Learn how to reset the primary Microsoft SQL Server database for replication In atest environment you may occasionally need to reset the replication environment Instead of deleting and recreating anew Replication A gent instance use this task to facilitate resetting the environment 1 Protect your new environment from old log information by using this command in the Replication Agent to archive all current redo log data pdb_xlog move_truncpt 2 To retain marking and log device information reinitialize the Replication Agent using pdb_xlog init with the force option which forces the Replication Agent repository to be
139. the All Databases view In the Control Center administration tool display window right click the replicate database name and select Authorities from the context menu In the Database Authorities window click Add User 90 Replication Server Options IBM DB2 Universal Database Replication Setup 6 In the Add User window select the user to add to the database from the list of displayed operating system users and click OK 7 Click Grant All to add necessary authorities to the maintenance user Note In a POC environment the user is created by an IBM DB2 Universal Database system administrator Configuring Replication Server Review the procedures for configuring Replication Server for IBM DB2 Universal Database Configuring Replication Server for Replication to the Target Data Server Create a Replication Server connection to the replicate data server 1 Navigate to the SSYBASE RAX 15_5 scripts ibmudb UNIX or Linux or SYBASE S REP 15_5 scripts ibmudb directory 2 Make a copy of the udb_create_rs_standby_connection sql script named my_udb_create_rs_standby_connection sql 3 Before executing the my_udb_create_rs_standby_connection sql script against your Replication Server change the name of the connection that Replication Server uses to connect to the DirectConnect service where e rds rdb is the DirectConnect service name followed by the replicate database name e rs maint userand rs_maint
140. the Replication Agent Replicating State on page 64 First Time Initialization To initialize the primary data server Replication Agent for Microsoft SQL Server installs objects at the data server level and at the database level Data server level modifications are required only once If you have previously initialized the primary data server and a Replication Agent instance and want to initialize another Replication Agent instance skip to Subsequent Initialization See also e Subsequent initialization on page 63 e Verifying the Replication Agent Replicating State on page 64 Configuring the Primary Data Server and a Replication Agent Instance for the First Time Configure the primary data server and a Replication Agent instance for the first time 1 Stop the Microsoft SQL Server service a In Control Panel gt Administrative Tools gt Services find the service named Microsoft SQL Server SERVER where SERVERis the name of your Microsoft SQL Server data server For example Microsoft SQL Server TEAMSTER b Stop the service 2 Open a command window and restart Microsoft SQL Server in single user mode C Program Files Microsoft SQL Server MSSQL 1 MSSQL Binn sqlservr exe m s instanceName where instanceName is the name of the Microsoft SQL Server instance 3 Execute the resource file to create the Replication Agent instance In your resource file if you set start_instance to yes this step also starts the Replication
141. the Replication Agent is in a replicating state insert test data into the test table and commit it By default the work performed by the maintenance user of the primary connection is not replicated The user ID used to insert transactions cannot be the same as the maintenance user defined in the primary connection 10 Use your preferred IBM DB2 Universal Database query tool to examine the replicate site for results and compare the contents of your test table from both the primary and replicate sites Resetting the Primary IBM DB2 Universal Database for Replication Learn how to reset the primary IBM DB2 Universal Database for replication In atest environment you may occasionally need to reset the replication environment Instead of deleting and re creating a new Replication Agent instance use this task to facilitate resetting the environment 1 Log in to the Replication Agent and issue suspend go Reset the locator stored in the Replication Agent ra_locator zero go Reset the locator stored in Replication Server 1S qi USAMP LE MRSeRo SDE prin SAME HR Gs oo Dm maimuE ps SSAMPLE_RS_ERSSD DSAMPLE_RS_ERSSD VS zeroltim NY NIJS go You see Locator has been reset to zero return status 0 Resume replication resume go 106 Replication Server Options Obtaining Help and Additional Information Obtaining Help and Additional Information Use the Sybase Getti
142. the setup program Prerequisites Allocate a disk partition of at least 20MB for each Replication Server you are installing You can add more partitions later if necessary Check each partition to make sure it is available and has write permissions Allocate the entire partition to the Replication Server If you allocate only a portion of the partition to Replication Server you cannot use the remainder for any other purpose Task 1 Verify that the drive on which you install the Replication Server has enough disk space for the components being installed and at least 100MB of extra disk space for the installation program Quick Start Guide 75 IBM DB2 Universal Database Replication Setup 2 Insert the Replication Server media in the appropriate drive or download and extract the Replication Server installation image from the Sybase Product Download Center SPDC 3 Start the installation e On Windows launch the setup program Note If the installer does not start automatically double click setup exe or select Start gt Run and enter the following where x is your CD drive x setup exe If there is not enough disk space in the temporary disk space directory set the environment variable TMP to directory_name before running it again where directory_name is the name of the temporary directory to which the installation program writes the temporary installation files When specifying the directory_name include its full
143. ting not for production System Architecture There are multiple components required to implementa replication system for Microsoft SQL Server e A primary Microsoft SQL Server database e A replicate Microsoft SQL Server database e A Replication Server e The Replication Server Options components e Replication Agent e ECDA Option for ODBC Figure 2 Sybase replication system for Microsoft SQL Server database Microsoft SQL Replication Agent Server primary database Replication Server ECDA Option Microsoft SQL for ODBC Server replicate database 34 Replication Server Options Microsoft SQL Server Replication Setup Prerequisites Before you begin setting up replication for Microsoft SQL Server review the prerequisites Make sure You are familiar with the Microsoft SQL Server data server and have an understanding of Sybase replication ECDA for ODBC is installed on the same host as your replicate Microsoft SQL Server database You have obtained the licenses for the Replication Server and Replication Server Options Microsoft SQL Server 2005 SP2 or Microsoft SQL Server 2008 with database compatibility set to 2005 90 is installed and configured to serve as the source primary database from which Replication Agent replicates transactions and as the target replicate database to which Replication Server with ECDA replicates transactions Microsoft SQL Server JDBC driver 1 2 is installed TCP IP connectivity
144. tion Agent goes to the REPLICATION DOWN state if an error occurs 1 gt resume DE Cf Msg 32000 Level 20 State 0 Server rao Procedure resume Line 1 Command lt resume gt failed Desired state lt REPLICATE gt could not be achieved Current state lt REPLICATIO DOWN gt 3 Validate that both primary and replicate connections are active 1S ci Usal PE OOAME MER S admin who go Note Be aware that e The DSI connection for the primary database connection is usually down because you are not replicating data back to the primary database e The Replication Agent connection if established for the replicate database connection is usually down because you are not replicating data from the replicate database Do not proceed until admin who returns status for threads similar to this admin who go You see Spid Name State Info 13 DSI EXEC Awaiting Command 101 1 SAMPLE_RS_ERSSD SAMPLE_RS_ERSSD 9 DSI Awaiting Message 101 SAMPLE_RS_ERSSD SAMPLE_RS_ERSSD 8 SQM Awaiting Message 101 0 SAMPLE_RS_ERSSD SAMPLE_RS_ERSSD 8 SQM Awaiting Message 101 0 SAMPLE_RS_ERSSD SAMPLE_RS_ERSSD 54 DSI EXEC Awaiting Command 102 1 my_mssql_access_service rdb 53 DSI Awaiting Message 102 my_mssgql_access_service rdb Quick Start Guide 65 Microsoft SQL Server Repl
145. tion sq script named my_udb_rs_create_test_primary_connection sql Before executing the my_udb_rs_create_test_ primary_connection sql script against your Replication Server change all occurrences of rax test to the name of the Replication Server connection between it and Replication Agent for UDB where e raxis the name of the data server of the primary IBM DB2 Universal Database e testis the name of the database of the primary IBM DB2 Universal Database For example NY NYudb9 Note Save these values to be used later for the rs_source_ds and rs_source_db parameters in the Replication Agent ibmudb rs file Change sys and sys_pwdto the user ID and password of the IBM DB2 Universal Database user who must have permission to apply DML operations against all user tables to be replicated where e sys is the user ID of the IBM DB2 Universal Database user sys e sys pwdis the password of the IBM DB2 Universal Database user change_on_install Note This user must be a valid user in the IBM DB2 Universal Database and it must be different from the user defined by the Replication Agent pds_username parameter which is used for Replication Agent connections to the primary database This command creates a Replication Server connection to the primary database example create connection to NY NYudb9 set error class rs_sqlserver_error_class Seu Mecon Serine Class re veog trincea Class set username sys set password change_on_i
146. tion to the Target Data Server Create a Replication Server connection to the replicate data server 1 Navigate to the SSYBAS E RAX 15_5 scripts mssq l directory 2 Make a copy of the mssql_create_rs_standby_connection sql script named my_mssql_cre ate_rs_standby_connection sql 3 Before executing the my_mssql_create_rs_standby_connection sql script against your Replication Server change rds rdb to the name of the connection that Replication Server uses to connect to the DirectConnect access service where 50 Replication Server Options Microsoft SQL Server Replication Setup e rdsis the DirectConnect access service name e rdbis any valid identifier Sybase recommends that you use the Microsoft SQL Server replicate database name e maintuser and password are the maintenance user and password created in ECDA Option for ODBC in step 1 of Creating a maintenance user for replication For example create connection to rds rdb using profile rs_msss_to_msss standard set username maintuser set password password go Note In the previous example password is a Replication Server reserved word and therefore must be enclosed in double quotes 4 Create the connection to the replicate database LSC SUSE AL SSSAMIP WEIS al my_mssql_create_rs_standby_connection sql 5 Log in to SAMPLE RS and verify the Replication Server connection to the replicate database iS Cpe Uisae li SS ANEERARS admi
147. tory entry type Indirect Catalog database partition number 0 Alternate server hostname Alternate server port number Database 2 entry Database alias DB1V9_DS Database name DB1_V9 Node name DB2NODE2 Database release level ly OC 86 Replication Server Options IBM DB2 Universal Database Replication Setup Comment Directory entry type Remote Catalog database partition number al Alternate server hostname Alternate server port number 2 If your desired database is not listed or does not have a database alias catalog it at the CLP prompt e Tocatalog a database using the default alias which is the same as the database name CATALOG DATABASE database_name AT NODE node_name where e database_name is the name of either the primary or replicate database e node_name is the name of the node that contains the database e To catalog a database with a specific alias CATALOG DATABASE database_name AS my_alias AT NODE node_name where e database_name is the name of either the primary or the replicate database e my_alias is your desired alias e node_name is the name of the node that contains the database 3 Verify that the database has been cataloged by issuing the list database directory command again 4 To exit the DB2 CLP enter the quit command Configuring ECDA Option for ODBC Review the procedures for configuring ECDA Option for ODBC DirectConnect Service Configur
148. tract all the installation images to your local drive 11 Download ECDA 15 0 ESD 3 or later a Point your Web browser to the Sybase Support Page at Aitp www sybase com support b Select EBFs Maintenance If prompted enter your MySybase user name and password c Select EnterpriseConnect Data Access You see a list of EBFs and maintenance releases d Find the EBF containing ECDA 15 0 ESD 3 or later for your platform For example EBF xxxxx 15 0 ESD 3 where xxxxx is the number of the EBF containing the ECDA 15 0 ESD 3 for your platform Quick Start Guide 37 Microsoft SQL Server Replication Setup e Click the Info icon to display the EBF Maintenance report and click the product description to download the software Installing Replication Components Review the procedures for installing the replication components Install all replication components on the same host where a Microsoft SQL Server has already been installed and is running with both the primary and replicate databases This simplifies the quick start process and avoids the step of separately installing a Microsoft SQL Server ODBC driver for ECDA Option for ODBC See also e Configuring Replication Components on page 47 Installing ECDA Option for ODBC Install ECDA Option for ODBC using the setup program Prerequisites Install ECDA on the same server as the replicate database to eliminate a network hop and to improve performance Task Note
149. ts Review the procedures for configuring each of the replication components in the replication system See also Testing Replication on page 32 Configuring Replication Server Configuring Replication Server for Oracle involves configuring Replication Server for replication to the target data server and from the primary database Configuring Replication Server for Replication to the Target Data Server Create a Replication Server connection to the replicate data server 1 Navigate to the SYBASE RAX 15_5 scripts oracle UNIX or Linux or SYBASE RAX 15_5 scripts oracle Windows directory Make a copy of the oracle_create_rs_standby_connection sql script named my_oracle_create_rs_standby_connection sql Before executing the my_oracle_create_rs_standby_connection sql script against your Replication Server change all occurrences of rds rdb to the name of the connection that Replication Server uses to connect to ECO where e rdsis the replicate Oracle SID name e rdbis any valid identifier Sybase recommends that you use the replicate Oracle SID name e maintuser and password are the user name and password created in ECO For example create connection to OracleSID_name OracleSID_name using profile rs_oracle_to_oracle eco set username to maintuser set password to password go Quick Start Guide 15 Oracle Replication Setup 7 Note In the previous example password is a Replication Server reserved wo
150. u have already initialized the primary data server See also e Verifying the Replication Agent Replicating State on page 64 Creating and Initializing a Replication Agent Instance Execute the resource file to create the Replication Agent instance 1 After the resource file has been validated as described in Configuring and Validating the Replication Agent Resource File use the ra_admin utility to create the Replication Agent instance using the r parameter for example SYBASE RAX 15_5 bin ra_admin r myra rs Execution results are returned as either e Response file processing completed Or e Response file processing completed with errors See the Replication Agent Administration Guide 2 Ifyour resource file has initialize_instance set to no start the Replication Agent instance a Change to the directory containing your Replication Agent instance RUN file for example b Execute the RUN file for example RUN_myra 3 Use the dsedit utility to update the Replication Server sql ini file with the Replication Agent location and verify the connection to the Replication Agent Quick Start Guide 63 Microsoft SQL Server Replication Setup a Open a command window in the SYBASE directory of your Replication Agent installation b Set the environment variables by executing the SYBASE bat file c Log in to Replication Agent LeeL USE E Siivace where myra is the name of Replication
151. ueue a stable queue managed by Replication Server to spool messages received from a Replication Agent See also outbound queue and stable queue e interfaces file a file containing information that Sybase Open Client and Open Server applications need to establish connections to other Open Client and Open Server applications See also Open Client and Open Server e isql an Interactive SQL client application that can connect and communicate with any Sybase Open Server application including Adaptive Server Replication Agent and Replication Server See also Open Client and Open Server e Java an object oriented programming language developed by Sun Microsystems A platform independent write once run anywhere programming language e Java VM the Java Virtual Machine The Java VM or JVM is the part of the Java Runtime Environment JRE that is responsible for interpreting Java byte codes See also Java and JRE e JDBC an abbreviation for Java Database Connectivity JDBC is the standard communication protocol for connectivity between Java clients and data servers See also data server and Java JRE an abbreviation for Java Runtime Environment The JRE consists of the Java Virtual Machine Java VM or JVM the Java Core Classes and supporting files The JRE must be installed on a machine to run Java applications such as Replication Agent See also Java VM e LAN an abbreviation for local area network a computer netwo
152. user in the Oracle database and it must be different from the user defined by the Replication Agent pds_username parameter which is used for Replication Agent connections to the primary database This example creates a Replication Server connection to the primary database create connection to NY NYora92 set error class rs_sqlserver_error_class set function string class rs_oracle_function_class set username sys set password change_on_install with log transfer on dsi_suspended go 5 Execute the script in Replication Server isel Usa SS SSyANMIPIGIa RS I lt script_path gt my_oracle_create_rs_primary_connection sql Quick Start Guide 17 Oracle Replication Setup where lt script_path gt is the full path to my_oracle_create_rs_primary_connection sql You see a message indicating that the Replication Server connection to the primary database has been created See also e Granting create object Permission to the rs_username User on page 18 e Creating a Database Replication Definition on page 19 e Creating a Database Replication Subscription on page 19 Granting create object Permission to the rs_username User Grant create object permission to the user ID specified by the Replication Agent rs_username parameter The rs_username user must have create object permission before it can be used to create a replication definition You must grant this permission manually from the RSSD 1 Log in to Replication Server isql
153. ute the RUN file in the background Your Replication Agent for Oracle is now running 28 Replication Server Options Oracle Replication Setup See also Verifying the Replication Agent Instance Installation on page 29 e Initializing the Replication Agent Instance on page 30 Verifying the Replication Agent Instance Installation Verify that the Replication Agent instance has been successfully installed 1 Use dsedit to update the Replication Server interfaces UNIX or Linux or sql ini Windows file to include an entry for the Replication Agent location Note You can use any Tabular Data Stream TDS client utility isql isqlApp or SQLAdvantage that you prefer 2 Verify the connection to the Replication Agent a Open a command window in the SYBASE UNIX or Linux or SYBAS Windows directory of your Replication Agent installation b Set the environment variables by sourcing the SYBASE csh file UNIX or Linux or by executing the SYBASE bat file Windows c Log in to Replication Agent GI ole legl Usa 2 Simyra Note These verification steps are optional because they were performed when you verified the resource file 3 Verify the Replication Agent connection to Replication Server a Enter test_connection RS go You see Type Connection RS succeeded 1 row affected If the result indicates a failure either the server is not responding or the connection properties host po
154. versal Database user for each primary database as described in Creating an IBM DB2 Universal Database User and Granting Permissions You cannot use the same user for multiple Replication Agents 1 Locate the resource file template The majority of configuration values required to create and initialize a Replication Agent can be recorded and stored in a resource file Using a resource file provides a way to record or retain the configuration information for a Replication Agent instance allowing an instance to be removed and re created The resource file template for an IBM DB2 Universal Database instance is SYBASE RAX 15_5 init ibmudb rs UNIX or Linux or 3SYBASE RAX 15_5 init ibmudb rs Windows 2 Create an instance resource file e Copy the resource file template to another file that contains the configuration information for a new instance for example myra rs e Change the values assigned to properties in the resource file so they match the values required for your environment The majority of these values are host port user and password information for the IBM DB2 Universal Database Replication Server and RSSD connections e Set the value of rs_source_ds to the value you chose for pds in previous procedures e Set the value of rs_source_db to the value you chose for pdb in previous procedures The resource file is self documenting with each parameter described See the Replication Agent Administration Guide
155. will write its temporary files 4 Read the License and Copyright Agreement Using the drop down list at the top of the window select the geographic location where you are installing ECDA to display the agreement appropriate to your region Select I agree and click Next You must agree to the license and copyright agreement before you can continue 5 In the installation directory window click Next to accept the default directory for the installation or enter a different directory where you want to install ECDA One of the following occurs e Ifthe installation directory you chose does not exist the installation program prompts The directory does not exist Do you want to create it Click Yes e If the installation directory exists and contains files the software prompts Warning You have chosen to install into an existing directory If you proceed with this installation any older versions of the products you choose to install that are detected in this directory will be replaced Note You will not see this message if the directory exists but is empty If you are prompted to overwrite any files or DDL select Yes only if the version of the new files or DDL is later than the one you are attempting to overwrite Note In certain cases when ECDA is installed with other Sybase products you may see warnings about overwriting newer versions of files In these cases simply instruct the installer to overwrite these files and procee
156. y at which primary data servers and primary databases are deployed to support normal business operations Sometimes called the active site or main site See also primary database and replicate site e primary table a table used as a source for replication Primary tables are defined in the primary database schema See also primary data and primary database e primary transaction a transaction that is committed in the primary database and recorded in the primary database transaction log See also primary database replicated transaction and transaction log e quiesce to cause a system to go into a state in which further data changes are not allowed See also quiescent e quiescent in a replication system a state in which all updates have been propagated to their destinations Some Replication Agent and Replication Server commands require that you first quiesce the replication system In a database a state in which all data updates are suspended so that transactions cannot change any data and the data and log devices are stable This term is interchangeable with quiesced and in quiesce See also quiesce e RASD an abbreviation for Replication Agent System Database Information in the RASD is used by the primary database to recognize database structure or schema objects in the transaction log e RCL an abbreviation for Replication Command Language the command language used to manage Replication Server See also Replication Serv
157. y contains an Replication Server installation you see Warning You have chosen to install into an existing directory If you proceed with this installation Quick Start Guide 77 IBM DB2 Universal Database Replication Setup any older versions of the products you choose to install that are detected in this directory will be replaced Click Next 6 Select the type of installation Typical installs the default components This is recommended for most users Full installs every Replication Server component including all the supported language modules Custom lets you select the components to install Some components are automatically installed if they are required to run your selected components Click Next 7 Read the License and Copyright Agreement a b ma c Select the geographic location from where you are installing to display the agreement for your region Select I agree to the terms of the Sybase license for the install location specified You must agree to the license and copyright agreement before you can continue with the installation Click Next 8 On the Sybase Software Asset Management License Server window select one of Specify License Keys click Browse to select the license file To select multiple license files use Shift Click or Ctrl Click The license pane displays the license information Alternatively copy and paste the license information directly in the licen
158. ysybase 2 Click Register Now Accessibility Features Accessibility ensures access to electronic information for all users including those with disabilities Documentation for Sybase products is available in an HTML version that is designed for accessibility Vision impaired users can navigate through the online document with an adaptive technology such as a screen reader or view it with a screen enlarger Sybase HTML documentation has been tested for compliance with accessibility requirements of Section 508 of the U S Rehabilitation Act Documents that comply with Section 508 generally also meet non US accessibility guidelines such as the World Wide Web Consortium W3C guidelines for Web sites Note You may need to configure your accessibility tool for optimal use Some screen readers pronounce text based on its case for example they pronounce ALL UPPERCASE TEXT as initials and MixedCase Text as words You might find it helpful to configure your tool to announce syntax conventions Consult the documentation for your tool 108 Replication Server Options Glossary For information about how Sybase supports accessibility see the Sybase Accessibility site Attp www sybase com products accessibility The site includes links to information about Section 508 and W3C standards You may find additional information about accessibility features in the product documentation Glossary This glossary describes Replication Serve

Download Pdf Manuals

Related Search

Related Contents

Philips 170B6CS  Untitled  

Copyright © All rights reserved. Failed to retrieve file