All Forums Database
10 Feb 2006
Tools to compare two teradata's DB and find the differences

We would like compare two databases in Teradata and find the diferences. What tool could we used?

Jim Chapman 449 posts Joined 09/04
10 Feb 2006

Do you want to compare the contents of tables, or are you just looking for schema differences?

prakash3366 2 posts Joined 08/12
13 Nov 2013

I am looking for to find schema differences.
Currently I am copying all the objects from database "A" to database "B".  I want to compare all the obects from database "A" with database "B".
Can you please provide any query to do the same???

Raja_KT 1246 posts Joined 07/09
13 Nov 2013

Hi,

Will this one help you? I love unix , so I always do it in bteq, exporting to a file and then do the work. Below script not tested.

 

  select case when tablekind = 'i' then 'show join index '

                 when tablekind = 'j' then 'show journal '

                 when tablekind = 'g' then 'show trigger '

                 when tablekind = 'p' then 'show procedure '

                 when tablekind = 'm' then 'show macro '

                 when tablekind = 't' then 'show table '

                 when tablekind = 'v' then 'show view '

 

            end || trim(databasename) || '.' || trim(tablename) || ' ;' (title '')

       from dbc.tables  dt

      where dt.tablekind in('i','j','g','p','m','t','v')

        AND DATABASENAME IN

            (

             'db1','db2',...

            )

        and trim(tablename) not like all ('al%','et%','lt%'','uv' etc)

        

      group by ...

      order by ...;

Cheers,

 

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

prakash3366 2 posts Joined 08/12
14 Nov 2013

Thanks Raja, It worked.

Raja_KT 1246 posts Joined 07/09
14 Nov 2013

You are always welcome. The above one is just a skeleton. You could have added more features , to serve automation.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

nanduforu 2 posts Joined 04/14
22 Apr 2014

Hello Raja_KT,
I am also have similar kind of requirement (two teradata's DB and find the differences).
I have executed your script and got the output of all show objects in the DB.
Kindly provide the sample script for the below requirement
1. I need script to identify the comparison between two schemas.
2. Compare two schema all objects , the output of comparison of two schemas need to generate in xls or csv
3. This script should run in every 2 hrs and get the comparison output file into mail box.
4. I would like to write this script in Unix box .
 
Please provide the sample script. It would be grateful to me. 

nanduforu 2 posts Joined 04/14
24 Apr 2014

Hello Raja.. Could you pleases provide update on my post

Raja_KT 1246 posts Joined 07/09
24 Apr 2014

I think you come from oracle background.The schema concept eg Oracle stores a schema object logically within a tablespace of the database . Here it is database.
 You can just fine tune and tweak the above script, using dbc.tables .First do a select * from dbc.tables and see what you want and what you can get from there.
 1. Study the output of the above script and you can fine tune/tweak as per your reqt.
 2.  If you use bteq script in unix/linux then you can port the file to windows and open and save in excel or csv format. You can also use SQL Assistant or Teradata Studio or some DB tools for test.
 3. You can write bteq script in  a unix/Linux sh or ksh or bash etc and schedule it using cron or if you have  scheduler in your organization. You need to have mailx in your script to mail it to mailbox with subject, mailid and attachment of the generated file. You may also use windows scheduler( though I have never tried and I also dont know if mail option is there or not). Please see crontab syntax if you use cron.
 4. Suggestions above.

SAMPLE BTEQ script.Test it first.You can  try part by part. I believe  learning   by commiting mistakes is a good experience. Experiment it:
bteq<<! >>${LOG_FILENAME} 2>&1
 
.logon your DB_NAME/username,password
 
.run file $HOME/abc.sql
 
.quit
 
!
 
 val=$?
 
if [ $val -eq 0 ]
 
then
 
echo "Put the logic here"
 
##########
 
else
 
##########
 
echo "FAILURE HERE"
 
fi

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.