Source table structure keep on changing

mohandl · Post by **mohandl** » Wed Mar 05, 2008 2:37 am

Hi All,

My source is oracle and targer is also oracle.

Problem: My source table structure is chaging randomly,by changing minar changes in my job i have to run job again with new structure.

Is there any way to resolve this problem.

Regards,
Mohan.

ArndW · Post by **ArndW** » Wed Mar 05, 2008 3:08 am

DataStage will not automatically inherit metadata from changed tables. In many cases where Oracle will do implicit conversions you can change datatypes, but missing or new columns are not detected in DataStage.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 05, 2008 3:15 am

Find who's doing it and force them to agree to a design freeze.

jdmiceli · Post by **jdmiceli** » Wed Mar 05, 2008 4:36 pm

Here's a thought: I haven't experimented with this idea at all. It may not work or even be worth exploring, but I offer it up anyway...

If the field counts don't change, just the names and/or datatypes, and assuming the target table changes in parallel to what the source system is doing, would it be possible to use generic field names in DataStage that have a datatype that could accept/convert the original datatypes - maybe varchar with oversized lengths.

Then define the same number of fields (or even more if the field count is changing - though this could require some creative querying of system tables to get field counts) and create a user-defined SQL using the dreaded 'select *' notation to extract the values into the generic datastage variables. You may have to do some text conversions or ICONV/OCONV, etc., but it may be possible to be generic.

I don't have time to attempt it right now due to my own workload, but if you have time, give it a shot and let us know what you find....

Have fun!

chulett · Post by **chulett** » Wed Mar 05, 2008 4:54 pm

Me personally, I don't see how any table structure can change 'randomly'. That concept is a little... scary.

jdmiceli · Post by **jdmiceli** » Thu Mar 06, 2008 8:24 am

I agree - it is terrifying.

I can say that because my cohort in crime here has a project from HeDoubleHockeySticks that does just this. The team he is putting things together for has no idea what they are looking for until the data is in front of them. Since none of those folks can be bothered to investigate the data sources/structures, he has been charged with managing their stuff.

We originally tried doing it with DataStage, but the daily changes made it virtually impossible to work with it. We chose to go back to using DTS packages until it is all locked down, and then we will transfer it to DataStage.