Data Comparison Scenario: Adaptive Server to Microsoft SQL Server

Perform a heterogeneous comparison using Adaptive Server and Microsoft SQL Server databases.

1. Start the DA server instance named mars:

   $SYBASE/DA-15_5/server/instance/RUN_instance_64.sh

   where $SYBASE is the directory in which you installed the Data Assurance Option, instance is the name of your DA server instance, and RUN_instance_64.sh is the start-up script.

   Note: On Windows, the start-up script file is named RUN_instance_32.bat or RUN_instance_64.bat. On UNIX or Linux platforms, the file is named RUN_instance_64.sh.
2. Start the DA agent instance on the machine named pluto:

   $SYBASE/DA-15_5/agent/instance/RUN_instance_64.sh

3. From isql, log in to DA server as an administrator:

   $SYBASE/OCS-15_0/bin/isql -S mars:6501 -U da_admin -P password -w 250

4. Create a DA agent connection that connects to the Microsoft SQL Server:

   create agent agent_pluto
   	set host=pluto
   	and set port=6500 
   	and set user=da_admin 
   	and set password=password  
   go

5. View the newly created DA agent:

   show agent agent_pluto
   go

6. Test connection settings for the DA agent:

   test agent agent_pluto
   go

7. Create database connections for the new DA agent.
   In this example, the pubs2 database is replicated on the Microsoft SQL Server database. The DA server's local agent on mars points to the Adaptive Server database on mars, while the agent on pluto points to the Microsoft SQL Server database:

   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_pluto
      set type=MSSQL
      and set agent=agent_pluto
      and set host=pluto
      and set port=1433
      and set database=pubs2
      and set user=steve
      and set password=ibmste11
      go
   

8. View the newly created database connections:

   show connection
   go

   show connection conn_mars
   go

   show connection conn_pluto
   go

9. Test the database connections:

   test connection conn_mars
   go

   test connection conn_pluto
   go

10. Create a compareset to map the entire source table.
    This example compares the Adaptive Server and the Microsoft SQL Server data:

    create compareset authors_demo4
      with
        source conn_mars dbo authors s
        target conn_pluto dbo 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

11. View the compareset:

    show compareset authors_demo4
    go

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

    create job authors_job4 
    		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 comparisons between Adaptive Server.
13. Execute the job to compare the data:

    run job authors_job4
    go

    DA compares equivalent values stored in distinct datatypes accurately.