Data Comparison Scenario: Adaptive Server to IBM DB2 UDB

Perform a heterogeneous comparison using Adaptive Server and IBM DB2 UDB 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 neptune:

   $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 IBM DB2 Universal Database:

   create agent agent_neptune 
   	set host=neptune 
   	and set port=6500
   	and set user=da_admin 
   	and set password=password  
   go

5. View the newly created DA agent:

   show agent agent_neptune
   go

6. Test connection settings for the DA agent:

   test agent agent_neptune
   go
   

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

   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_neptune
      set type=UDB
      and set agent=agent_neptune
      and set host=neptune
      and set port=5001
      and set database=PUBS2
      and set user=JOHN
      and set password=mssqt12
      go
   

8. View the newly created database connections:

   show connection
   go

   show connection conn_mars
   go

   show connection conn_neptune
   go

9. Test the database connections:

   test connection conn_mars
   go

   test connection conn_neptune
   go

10. Create a compareset to map the entire source table.
    This example compares the Adaptive Server and the IBM DB2 UDB data:

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