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

[ray.wurlod]

(1) Yes.
(2) Yes, typically by using a constraint expression in a Transformer stage's output link (for example @INROWNUM <= #RowLimit#).
(3) Yes, typically by using a job parameter to specify the starting row number. You must design the counting process into your job design. Again use a constraint (for example @INROWNUM >= #StartRow#)