Hi all,
I have a design as follows
oraclestage--->sequentialfile--->transformer---> output oracle stage
output oracle stage has two links one for update and insert
hashed file look up.... with only surroagate key column
what I have to do here is that I have to insert or update
i have done the following in transformer constraints
with surrogate key as primary key
i have mentioned the following conditions
for ex
for insert ::::IsNull(product_id_sk)
FOR UPDATE::: NOT(IsNull(product_id_sk))
But the performance was not good
so i have used the look up hashed file...
Instead of using the hashed look up is there any way tp procced.. for updating or inserting
waiting for your valuable ideas.....
lookup condition
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What is the point of the Sequential File stage in your job design? It blocks all rows until they are all selected from Oracle. But the clock keeps running so your elapsed time is longer than it needs to be. The hashed file lookup is the most efficient possible; that is not where your problem lies.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
the sequential file was a part of mine design process....
the whole idea behind that was
the sequential file stage has to wait until it recieves all the records from the oracle stage and then it performs look up with hashed file where this hashed file takes records ffrom the oracle stage target
without the sequential file the oracle stage has to hit the database for each and every record it retrieves and perform the look up...
which option would be better... ???
Or instead of using the lookup can i check the condition in the source view that i am using in the input oracle stage giving some specific conditions
Or instead of using the look up hashed file,
in the target oracle stage under the update action can we put some other options like insert and update rows or anything that suits better
can you suugest me regarding me this...
the whole idea behind that was
the sequential file stage has to wait until it recieves all the records from the oracle stage and then it performs look up with hashed file where this hashed file takes records ffrom the oracle stage target
without the sequential file the oracle stage has to hit the database for each and every record it retrieves and perform the look up...
which option would be better... ???
Or instead of using the lookup can i check the condition in the source view that i am using in the input oracle stage giving some specific conditions
Or instead of using the look up hashed file,
in the target oracle stage under the update action can we put some other options like insert and update rows or anything that suits better
can you suugest me regarding me this...
What you believe to be helpful is actually an unnecessary bottleneck. In addition to retrieving each and every record from Oracle and doing a lookup for each record, you have added the additional overhead of writing each record to disk and reading it back from disk. As Ray pointed out, the sequential file is "blocking" your throughput because all of the records have to be written to disk before they can start to be read from the disk.without the sequential file the oracle stage has to hit the database for each and every record it retrieves and perform the look up...
1) Replace the sequential file stage with an IPC stage. This will explicitly split the process that is reading from Oracle and the process that is doing the lookup (transformer) into 2 processes on separate cpu's. The blocking sequential file will be replaced by some pipeline parallelism.
2) If you are not already doing so, enable the preload to memory option for the hash file.
3) Depending on the volume of inserts, you may want to consider writing the inserts to a sequential file and then bulk loading them with sql*loader.
Mike
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need something against which to lookup to determine whether to insert or update. Whether it's a hashed file or the target table directly is up to you, but the hashed file will be faster.
Code: Select all
TargetTable1 ---> HashedFile
|
|
V insert
SourceTable -----> Transformer --------> TargetTable2
-------->
update
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.