Datastage Challenge

shank · Post by **shank** » Thu Aug 05, 2010 1:28 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 05, 2010 1:51 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Aug 05, 2010 2:12 am

This is very much possible - especially using non-transformer stages with RCP enabling.

But the main question is, if the columns are 'dynamic', then your previous run to load might have 5 columns whereas current run have 6 - but for same table. This may lead to incorrect results.

shank · Post by **shank** » Thu Aug 05, 2010 2:30 am

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?

chulett · Post by **chulett** » Thu Aug 05, 2010 5:10 am

Parallel job? Server job?

ray.wurlod · Post by **ray.wurlod** » Thu Aug 05, 2010 5:42 am

QualityStage reference match.

kduke · Post by **kduke** » Thu Aug 05, 2010 8:41 am

Are you talking about something like the Oracle minus query?

debrujr · Post by **debrujr** » Mon Aug 09, 2010 8:56 am

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.