Configuring Spoon lookup steps

You can use lookup steps to extract extra data from the database into the data stream.

About this task

The Call DB procedure allows the transform to call a database procedure. Information flows through the procedure and back to the transform. You can create sequences for the fact table entries.

Procedure

  1. Set up your DB procedure call to use NEXTVAL, to send in SEQ_NAME and INCR and to output by using CURR_VALUE.
  2. Determine how many instances of this lookup step to run. When you are testing the transform, running this step with five instances greatly helps with performance.
    For example, for 30,000 records the performance time reduces from 90 seconds down to 30 seconds.
  3. Change the number of threads that run a step by right-clicking the step and selecting Change number of copies to start.
  4. Tune the number of threads that are running the DB Procedure step.