Hi,
Here is a scenario.. Kindly tell me we could do this using DS or not.
The basic requirement is to compare data between current and history tables based on key columns. However both the table names and the columns to be compared will be dynamic. The table name and Key column will be passed to the datastage job. The columns to be compared will change periodically. Can we achieve this using datastage. Please reply the possibility of doing it and the approach for the same. Usage of shell script using sub routines is allowed.
Datastage Challenge
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I imagine it's possible, but have not given any thought to how. Depends to some extent on what you mean by "dynamic" and whether you allow QualityStage stage types into the mix.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Ok.. We are having a table which has the details of the table name and the column names which need to be compared. The table has 2 columns. COMP_FIELD and TABLE_NAME. This table would undergo change periodically. The sample data in the table would be like this...
LAST_NM|CONSUMER_POLICY
FIRST_NM|CONSUMER_POLICY
STRT_ADDRESS_LN1|POSTAL_ADDRESS
STRT_ADDRESS_LN2|POSTAL_ADDRESS
POSTAL_ZIP_CD|POSTAL_ADDRESS
PLAN_VARIATION_CD|INS_COVERAGE_FACT
So, here we would need to compare the LAST_NM and FIRST_NM columns between CONSUMER_POLICY and TEMP_CONSUMER_POLICY tables.
then we would need to compare STRT_ADDRESS_LN1, STRT_ADDRESS_LN2 and POSTAL_ZIP_CD columns between POSTAL_ADDRESS and TEMP_POSTAL_ADDRESS tables... and so on..
How can we acheive the same?
LAST_NM|CONSUMER_POLICY
FIRST_NM|CONSUMER_POLICY
STRT_ADDRESS_LN1|POSTAL_ADDRESS
STRT_ADDRESS_LN2|POSTAL_ADDRESS
POSTAL_ZIP_CD|POSTAL_ADDRESS
PLAN_VARIATION_CD|INS_COVERAGE_FACT
So, here we would need to compare the LAST_NM and FIRST_NM columns between CONSUMER_POLICY and TEMP_CONSUMER_POLICY tables.
then we would need to compare STRT_ADDRESS_LN1, STRT_ADDRESS_LN2 and POSTAL_ZIP_CD columns between POSTAL_ADDRESS and TEMP_POSTAL_ADDRESS tables... and so on..
How can we acheive the same?
Regards,
Shank
Shank
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Personally I would do a mix of ksh with embedded sql. I know this is a DS forum but in my head it works out better going that route.
In essence I would group my key columns by table name in one select and then pass the table and column params to another query that will plug in the variables in their desired spots to achieve a dynamic query that will allow any variant of columns as well as number of columns to be compared with one sql construct.
In my mind it would be an outer loop performing the gather of columns by table and then an inner statement that is performing the compare.
Again I apologize for not throwing out a solution for it via DS but considering the nature of the solution and it is encapsulated in a DB then I would think this could be a simple and clean option as long as you have the skillset to do so.
In essence I would group my key columns by table name in one select and then pass the table and column params to another query that will plug in the variables in their desired spots to achieve a dynamic query that will allow any variant of columns as well as number of columns to be compared with one sql construct.
In my mind it would be an outer loop performing the gather of columns by table and then an inner statement that is performing the compare.
Again I apologize for not throwing out a solution for it via DS but considering the nature of the solution and it is encapsulated in a DB then I would think this could be a simple and clean option as long as you have the skillset to do so.