Page 1 of 1
How to auto-generate metadata?
Posted: Tue Sep 16, 2008 2:47 am
by varaprasad
Hi, My client has a requirment in which, whenever a source table definition changes (when new columns are added or deleted), it should get updated automatically in the DS job (they are using PX), and should reflect in the target tables. This should happen without making any changes to the job/code, explicitly.
Is there any way to do this? Please suggest.
Posted: Tue Sep 16, 2008 3:13 am
by ray.wurlod
I think they need the RMM stage.
To quote from that classic film The Castle - "tell 'em they're dreaming".
Posted: Tue Sep 16, 2008 5:39 am
by throbinson
To quote Walt Disney, "Sometimes dreams do come true".
But for DataStage this dream can quickly turn into a nightmare. It depends on the ease of obtaining the changed metadata. Is it from a DBMS? How is the target metadata maintained and updated?
Posted: Tue Sep 16, 2008 6:39 am
by varaprasad
throbinson wrote:To quote Walt Disney, "Sometimes dreams do come true".
But for DataStage this dream can quickly turn into a nightmare. It depends on the ease of obtaining the changed metadata. Is it from a DBMS? How is the target metadata maintained and updated?
The source data is on ORACLE & DB2 and loaded into flat files in the target server. What they need is, they do not want the manual intervention to change the Datastage code.
I just want to know if ther's any possibility to do this using the schema files.
Posted: Tue Sep 16, 2008 6:45 am
by OddJob
Combining schema files and Runtime Column Propogation (RCP) could solve your issue.
Posted: Tue Sep 16, 2008 6:49 am
by singhald
Hi Ray,
what is that RMM stage ?
Posted: Tue Sep 16, 2008 7:20 am
by throbinson
You could build a DataStage job to query the Oracle system tables for the source table. When a change is detected the job would create the proper schema files. Another DataStage job, RCP enabled, would read the source Oracle table and write out the fields to the just generated schema file in a Sequential stage. Easy to design far more difficult to reliably build and implement.
Posted: Tue Sep 16, 2008 7:25 am
by chulett
singhald wrote:what is that RMM stage ?
An old joke - the
Read My Mind stage.
Posted: Tue Sep 16, 2008 9:21 am
by dsusr
OddJob wrote:Combining schema files and Runtime Column Propogation (RCP) could solve your issue.
You have to use some good shell scripting to resolve this issue....
First you have to read the schema from Oracle and have to generate the Schema files which were then used in the generic DataStage job with RCP enabled but all this will work fine if its just direct loading or extraction.
But if there are some transformations then you may need to maintain the rules for each of the tables and have to try generating the transformation files at runtime which can then be used by DataStage job.