Schema comparison

dodda · Post by **dodda** » Tue Aug 05, 2008 8:19 am

Hello,

I have a requirement where i have to compare two databases one is backup database and other one is prod database. We have around 120 tables in each database schema and we have to produce delta by comparing those tables. Is there a optimal to do this. I am thinking of change capture stage. but do i need to design a single for each pair of database comparison that would be 120 jobs? is there a way i can compare the whole schema?

Apprecaite your help.

Regards

chulett · Post by **chulett** » Tue Aug 05, 2008 8:34 am

What database? Most come with tools, or third-pary tools exist for tasks like this. It would be cumbersome and time-consuming to accomplish this via ETL, I would think.

vasubabu · Post by **vasubabu** » Tue Aug 05, 2008 8:51 am

chulett wrote:What database? Most come with tools, or third-pary tools exist for tasks like this. It would be cumbersome and time-consuming to accomplish this via ETL, I would think. ...

if every table metadata is same you can do via multiple instances
please guide me if i am wrong

phani

dodda · Post by **dodda** » Tue Aug 05, 2008 9:13 am

Hello Vasu

Thanks for your reply.since one is production database and another is backup database they have the same metadata while comparing the same table but we have 122 tables with each pair having different metadata

Thanks

dodda · Post by **dodda** » Tue Aug 05, 2008 9:15 am

chulett wrote:What database? Most come with tools, or third-pary tools exist for tasks like this. It would be cumbersome and time-consuming to accomplish this via ETL, I would think.

Hello chulett

Yes it is cumbersome i certainly agree with that. But is there a optimal option by using ETL datastage?. we cant do multiple instances as 122 tables have different metadata.

chulett · Post by **chulett** » Tue Aug 05, 2008 9:41 am

I don't think there will be anything "optimal" about an ETL solution to this problem. This is really a DBA task to validate "backup" databases. And as far as I know, if you really want to go down this road you will need 122 jobs for this... but at least they will all be very very (very) similar.

Set it up once and then clone, clone, clone.

ps. You still haven't mentioned the database - Oracle, DB2, ??

dodda · Post by **dodda** » Tue Aug 05, 2008 11:38 am

chulett wrote:I don't think there will be anything "optimal" about an ETL solution to this problem. This is really a DBA task to validate "backup" databases. And as far as I know, if you really want to go down this road you will need 122 jobs for this... but at least they will all be very very (very) similar.

Set it up once and then clone, clone, clone.

ps. You still haven't mentioned the database - Oracle, DB2, ??

Hello chulett
Thanks for your reply.The delta will be done on oracle databases.

Thanks

iDomz · Post by **iDomz** » Tue Aug 05, 2008 11:41 am

How about using a generic job and schema files for metadata?

dodda · Post by **dodda** » Tue Aug 05, 2008 1:19 pm

iDomz wrote:How about using a generic job and schema files for metadata?

Hi iDomz

can you please elaborate this. How to use schema files for metadata.
Appreciate your help

thanks

iDomz · Post by **iDomz** » Wed Aug 06, 2008 3:25 am

Create schema files for all tables
Create a job that does nothing but compare two sets of data (Change capture or Difference stage)
Turn RCP on
Use a set of common columns as keys - audit columns can be a candidate key
Mark all non key columns are values to true
Pass the schema file names as parameters to your job

I have not tried this but theoretically should work if you have common audit keys across both databases. The experts can tell you why it will not

keshav0307 · Post by **keshav0307** » Wed Aug 06, 2008 3:48 am

yes this can be done in a single CDC job.
if you are comapring record to record then read all column as as column( by concanating them). the CDC will give output of the record difference

dodda · Post by **dodda** » Wed Aug 06, 2008 7:21 am

keshav0307 wrote:yes this can be done in a single CDC job.
if you are comapring record to record then read all column as as column( by concanating them). the CDC will give output of the record difference

Hello Keshav

Thanks for your reply.Can you please explain me more elaborately since i am using two oracle stages to compare (delta) how can i pass the schema file as a parameter and also how to read all the columns from oracle as a single column.

Thanks

dodda · Post by **dodda** » Wed Aug 06, 2008 7:26 am

keshav0307 wrote:yes this can be done in a single CDC job.
if you are comapring record to record then read all column as as column( by concanating them). the CDC will give output of the record difference

Hello Keshav,

Since we are comparing 122 tables we have different metadata for each pair of tables. how can we do that

Thanks

keshav0307 · Post by **keshav0307** » Wed Aug 06, 2008 7:28 am

you didn't read it carefully.

if you are comapring record to record then read all column as as column( by concanating them). the CDC will give output of the record difference

that means irrespective of the table, there will always be only one column.

dodda · Post by **dodda** » Wed Aug 06, 2008 9:11 am

keshav0307 wrote:you didn't read it carefully.
if you are comapring record to record then read all column as as column( by concanating them). the CDC will give output of the record difference
that means irrespective of the table, there will always be only one column.

Hello Keshav,

how to do that for reading multiple tables and how to pass the schema file as parameter for oracle stage.

Thanks