Toad World® Forums

Compare Results of Two Queries - Automate in Intelligence Central

I am attempting to do write an automation script to compare the results of two queries, one executing against DB2 and the other against SQL Server. In running on my local machine, I followed these steps, and got it to work.

  1. Insert results of DB2 Query into a table on Local Storage
  2. Insert results of SQL Query into a different table on Local Storage
  3. Compare the contents of the two Local Storage tables and generate a report of the differences

When I tried to publish this to Intelligence Central, I received an error, as Local Storage is not valid for Intelligence Central. I changed Local Storage output to Intelligence Central outputs (the only options are Local Storage and Intelligence Central). This works for steps 1 and 2, but not for step 3. I am unable to chose an Intelligence Central connection as input for Data Compare. Am I supposed to be able to use Intelligence Central objects as an input for Compare?

If not - then how would you recommend I change my approach?

Wow, it does appear that you can't use the Data Compare wizard for two TIC tables... gonna send this to Quest's Product Manager, as I see this use case as valid, especially since more and more users are utilizing Intelligence Central.

The Data Viewer does work with TIC tables (see below) but not sure if that's a good enough work-around for you, since -1- you're trying to automate your compare (Diff Viewer task doesn't exist in the Automation Task Toolbox) and -2- the Diff Viewer doesn't generate a sync script or pretty report like the Data Compare does.

Is it possible to create a "temp" table on each of your DB2 and SQL Server databases for each of the query result, and then do a Data Compare directly on those tables? This can be automated also.

Thank you for your response. I'll discuss the option of creating DB2/SQL tables (either temporary or permanent) with my DBA team.

Here's another possible work-around that gets you closer to your original requirement... the following from our Product Manager.

Bottom line is that TDP can compare two TIC tables within the Compare Wizard, but you'll need to accomplish this through MySQL connectors. This works because the underlying platform TIC employs for data storage and retrieval is a MySQL database (but TIC's MySQL database is obviously different from the one that supports your personal Local Storage in TDP.) So, it would be possible for you to execute an automation script on the TIC server that performs your compare. See snap below.

Thank you for the follow up. I have executed these steps from my local Toad Data Point and can confirm that this works!

1 Like

Yay! Glad it can be done!
:upside_down_face: