How can I prefix the Surrogate Key to an existing column instead of adding another column in Surrogate Key Generator? I.e., I want the output field to be
but seems to me that the output field is not editable in Surrogate Key Generator.
Reason behind this request,
I want to define columns, data types, and other format options in the *parallel* Column Import stage, not the Sequential stage, which can improve performance because it separates parsing process from sequential read process.
However, I need to assign a row number in parallel using the Surrogate Key Generator to the record read sequentially.
Not possible in the way you describe. But you can interpolate a Column Generator stage in the flow to generate a constant (a Cycle with only one Value).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
fmou wrote:I want to define columns, data types, and other format options in the *parallel* Column Import stage, not the Sequential stage, which can improve performance because it separates parsing process from sequential read process.
However, I need to assign a row number in parallel using the Surrogate Key Generator to the record read sequentially.
Actually, the reason that I'm asking was,
from the book that I am reading,
IBM WebSphere DataStage Advanced Enterprise Edition (DX436), Draft - 5/19/2005, on page 280
It says,
Column Import Examples
...
Using a Sequential File stage (running sequentially):
If the incoming file does not have a way of uniquely identifying a
given row, it may be necessary to read the file sequentially, and
assign a row number in parallel using the Surrogate Key
Generator
You can always use a transformer to concatenate the surrogate key with whichever column. However, if your intent is to just parse it back out with Column Import, don't even bother with the concatenation. Just let the surrogate key column pass through to the output of Column Import....no need to waste time and CPU cycles concatenating and parsing the same generated column.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
jwiles wrote:. . . if your intent is to just parse it back out with Column Import, don't even bother with the concatenation. Just let the surrogate key column pass through to the output of Column Import....
Thanks for the reply,
Actually that's the first method that I tried, however, due to my limited knowledge to DS, I wasn't able to -- didn't know how to have Column Import handle two fields. Could you elaborate a bit please?
I guess the principle to define columns, data types, and other format options in the *parallel* Column Import stage, not the Sequential stage still hold, just the practice how to add the surrogate key has changed, right?
jwiles wrote:simply map the surrogate key column from the input link to the output link (as you would in most other stages) on the Output link Mapping tab.
Oh, just figured out why you think that way. No that doesn't work for my case because I'm using schema file as the Column Method of the Column Import stage. I.e., my Output link Mapping was empty. Guess the only way to make it work is to switch to Explicit Column Method then.
ray.wurlod wrote:Why not just have the Sequential File stage generate the row number? That would serve as the key you seek. ...
Ops, spoke too soon. Actually, the limitation is at the Column Import stage.
I'm using the schema file as the Column Method of the Column Import stage. I.e., my Output link Mapping was empty. Any columns other than the importing one can not be passed along with the imported ones, correct?
You've asked your sequential file stage to create a record number column. Is it defined in the file stage's output metadata grid (in the stage GUI)?
Is that column defined in your input link metadata grid? If so, you should be able to drag and drop it in the output link mapping tab.
If not, it won't show up as a mappable input column in the stage GUI. Do you have runtime column propagation enabled? If so, you can manually add that row count column to the metadata grid so that you can access it through the GUI.
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.