Tools to compare two teradata's DB and find the differences

Database

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?

8 REPLIES
Enthusiast

Re: Tools to compare two teradata's DB and find the differences

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

Re: Tools to compare two teradata's DB and find the differences

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???

Enthusiast

Re: Tools to compare two teradata's DB and find the differences

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

Re: Tools to compare two teradata's DB and find the differences

Thanks Raja, It worked.

Enthusiast

Re: Tools to compare two teradata's DB and find the differences

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

Cheers,

Raja

Re: Tools to compare two teradata's DB and find the differences

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. 

Re: Tools to compare two teradata's DB and find the differences

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

Enthusiast

Re: Tools to compare two teradata's DB and find the differences

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