Control Loading of the Target table

[Gokul]

Hi,

In our Project we want to implement Control Loading,Logic as follows.
1> Divide the total rows in the staging table by n(constant) adn obtain n ranges(group of rows).
2> Then Load each of range seperatley in the target table.
3> If any of the row in the ranges fails ,the entire range is rollbacked.
Note: The target load is direct load i.e there is no lookup performed.

e.g if total rows are 100 and n =10 ,then the ranges are 1..10,11..20 and so on

The Design that comes to my Mind is...



StagingTable-------------->Transformer--1 to n------------>TargetTable
Transformer- n+1 to 2n-------->TargetTable
Transformer- 2n+1 to 3n------->Target Table

Note: Target table is the same in all the range loading.

I want to know
1>wheteher this design is possible in DataStage
2> can we limit the number of rows that can be loaded in the Taget table
3> Can we start loading after rows after skipping specific number of rows
e.g fro n+1 to 2n range we have to skip first n rows and then start loading.

Thanks in advance,
Gokul Tendulkar

[roy]

Hi,
If you precount the number of rows in your source data you can run a multipe instance or in a loop a job that passs P_MIN_VAL P_MAX_VAL then use them in the where part to each time read only 1 portion of the data.
you'll probably need to add somelogic around this, but that's the general idea.
IHTH,

[Gokul]

Thanks Roy,

Hi,

In our Project we want to implement Control Loading,Logic as follows.
1> Divide the total rows in the staging table by n(constant) adn obtain n ranges(group of rows).
2> Then Load each of range seperatley in the target table.
3> If any of the row in the ranges fails ,the entire range is rollbacked.
Note: The target load is direct load i.e there is no lookup performed.

e.g if total rows are 100 and n =10 ,then the ranges are 1..10,11..20 and so on

The Design that comes to my Mind is...



StagingTable-------------->Transformer--1 to n------------>TargetTable
Transformer- n+1 to 2n-------->TargetTable
Transformer- 2n+1 to 3n------->Target Table

Note: Target table is the same in all the range loading.

I want to know
1>wheteher this design is possible in DataStage
2> can we limit the number of rows that can be loaded in the Taget table
3> Can we start loading after rows after skipping specific number of rows
e.g fro n+1 to 2n range we have to skip first n rows and then start loading.

Thanks in advance,
Gokul Tendulkar