Data Comparison Scenario 1: SAP Adaptive Server to SAP IQ and Oracle Databases

Perform a heterogeneous comparison using SAP Adaptive Server, Oracle, and SAP IQ databases.

1. Add the JDBC driver JAR file for an Oracle database to the DA classpath, before starting the DA server or the DA agent.
2. Follow step 1 to step 6 of the distributed deployment example to start the DA server instance and connect to the DA agents.
3. Create database connections for the new DA agents.
   In this example, pubs2 database is replicated on Oracle and SAP IQ databases. The DA server's local agent on mars points to the SAP Adaptive Server database on mars, while the agents on jupiter and saturn point to their respective Oracle and SAP IQ databases:

   create connection conn_mars
   	set type=ase
   	and set agent=localagent 
   	and set host=mars
   	and set port=5000 
   	and set user=sa	 
   	and set database=pubs2 
   	and set password=password 
   	go

   create connection conn_jupiter
   	set type=oracle
   	set agent=agent_jupiter 
   	and set host=jupiter
   	and set port=1521 
   	and set user=system	 
   	and set database=pubs2 
   	and set password=password
   	go

   create connection conn_saturn
   	set type=iq
   	set agent=agent_saturn 
   	and set host=saturn
   	and set port=2638 
   	and set user=DBA	 
   	and set database=pubs2 
   	and set password=password
   	go

4. View the newly created database connections:

   show connection
   go

   show connection conn_mars
   go

   show connection conn_jupiter
   go

   show connection conn_saturn
   go

5. Test the database connections:

   test connection conn_mars
   go

   test connection conn_jupiter
   go

   test connection conn_saturn
   go

6. Create a compareset to map the entire source table.
   This example compares the SAP Adaptive Server and the Oracle data:

   create compareset authors_demo4
     with
       source conn_mars dbo authors s
       target conn_jupiter SCOTT AUTHORS t
     map
       s.au_id = t.AU_ID set key=true
       and s.au_lname = t.AU_LNAME
       and s.au_fname = t.AU_FNAME
       and s.phone = t.PHONE
       and s.address = t.ADDRESS
       and s.city = t.CITY
       and s.state = t.STATE
       and s.country = t.COUNTRY
       and s.postalcode = t.POSTALCODE
   go

7. View the compareset:

   show compareset authors_demo4
   go

8. Create a job.
   This job creates a single comparison that uses the compareset defined in step 6. It compares all rows in the authors table:

   create job authors_job4 
   		set MAX_CONCURRENT_COMPARISONS = 100 
   	add comparison cmp_authors4 
   		set COMPARESET=authors_demo4 
   		and set NUM_PARTITIONS to 1
   		and set ENABLE_ROW_COUNT to false
   		and set COMPARE_MODE to row_compare 
   		and set HASH_TYPE to AGENT_HASH
   	go

   Note: Set the hash_type comparison option to agent_hash for heterogeneous comparison. The database_hash comparison option is used only for SAP Adaptive Server-to- SAP Adaptive Server comparisons.
9. Execute the job to compare the data:

   run job authors_job4
   go

   DA compares equivalent values stored in distinct datatypes accurately. For example, the value 1 stored in a SAP IQ NUMERIC column is equivalent to the value 1 stored in an Oracle NUMBER column, and to 1.0 stored in an ASE FLOAT column.