DSXchange

Hi,
I am loading data to a table from a view which contains 13 lacs record. It took 13 hours to load the table. My design is very simple.
Source View----> Transformer------> Target table

Can you please let me help in increasing performance? The loading option for target is "clear the table then insert rows".
Thanks,
Bhaskar

If you select from a View, then the SELECT statement that defines that view must be executed first. One approach would be to pre-execute that original SELECT statement (perhaps in a DataStage job) capturing the result into a text file. You may well find that this is taking the bulk of the time - if you can "time shift" it to an earlier time, then the load itself may be able to proceed much faster.

How much work occurs in the Transformer stage? Have you used optimally efficient practices in the expressions in this stage?

Have you considered replacing your target table with a text file, and using that text file as the data file in a bulk loader?

Finally, if the source and target are in the same database, have you considered bypassing DataStage entirely and doing the whole thing with SQL?

How can I preexecute the select statement in Data Stage Job? I have executed the query in TOAD & getting the result.

Two separate jobs.

Hello Ray,
That means I have to design two similar job. Then execute the first one & finally execute the second one.

One job to extract and another job (or jobs) to load.

One job sequence to rule them all.

If you think loading (not extraction) is the problem, try to tune the table in the database increase commit level or something like that ..13lac recs are no biggy for DS should not take the time you specified.

It might, if there are lots of indexes and constraints to be checked on the target table.

But that's not a DataStage problem.