view read

[bryan]

We are sourcing from views which has like 15 million rows and we do 5 validations against hash lookup. Writing into a sequential file.
Read throughput:300rows/sec


On an average, each million takes one hour.

How can I do to leverage Datastae/AIX power?

5CPU
2GB memory
AIX 5.0

[kcbland]

Convert your job to write to a parameterized output file and run 5 instances of it. Have the select statement portion the data out into 1/5th chunks, perhaps using a WHERE MOD(somekeycolumn,5) = 0,1,2,3,4 where 0 to 4 is fed as a value to each instance.

Hopefully, your source database can handle you hitting that view 5 times simultaneously. If it can, you should finish 5X faster.

This technique is the only technique that can scale in multiples of performance, which is what you want. I'm fairly confident that no amount of hash file tweaking and tuning will give you a five-fold increase. But, tune them anyway so that each instance is optimally doing reference lookups.

[T42]

Or buy DataStage EE and let the code do the work for you most of the time.

[kcbland]

Smart aleck young whippersnapper.

[Sainath.Srinivasan]

Set inter-process buffer.

Use IPC-Stage.

Partition your hash files.

Use multi-instance jobs.

You can do lots in Server component itself.