Greetings,
i have a randomly changing source database table that I am bringing into another data table.
The source table may drop or add a column at a whim. The only think I think is static is the primary key field.
I need to take this database table, and add/remove columns in the target table based on source table, then do the following:
Add the data to the new columns
CDC against already matching columns and perform update/insert.
My problem is the table will continue to add/remove columns as they see fit.
Has anyone gotten into this before?
thanks
Variable Table Schema
Moderators: chulett, rschirm, roy
Variable Table Schema
iShoreETL
The solution is not simple or easy to implement.
The first part would be to keep a dynamic metadata store somewhere accessible to DataStage which has the tables, columns, keys, and datatypes. In addition, the columns to be used for CDC would also need to be marked.
The another set of jobs would read and process the metadata to determine which columns match between the dynamic source and the target tables and generate appropriate schemas.
These schemas, in turn, would then be passed as parameters to generic jobs in order to process the data and implement CDC.
This is a lot of work to implement correctly and is also very prone to problems in production and is complicated to maintain. If you are only adding source columns and never deleting them, then the process can be made much easier, but it remains a nightmare.
The first part would be to keep a dynamic metadata store somewhere accessible to DataStage which has the tables, columns, keys, and datatypes. In addition, the columns to be used for CDC would also need to be marked.
The another set of jobs would read and process the metadata to determine which columns match between the dynamic source and the target tables and generate appropriate schemas.
These schemas, in turn, would then be passed as parameters to generic jobs in order to process the data and implement CDC.
This is a lot of work to implement correctly and is also very prone to problems in production and is complicated to maintain. If you are only adding source columns and never deleting them, then the process can be made much easier, but it remains a nightmare.
</a>
Out of morbid curiousity, what kind of system includes a "randomly changing database table"? That just seems like... crazy talk. 
As to the matching changes to the target table, is that meant to be automated in some fashion or is that a manual step?
As to the matching changes to the target table, is that meant to be automated in some fashion or is that a manual step?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers