Search code examples
loopsdatastage

Simple loop in datastage

I have a basic understanding problem with datastage. I am new to this field. It is about the implementation of loops. First I get several rows of a select query using the connector-stage. Now I would like to do several more steps for each row. The result of each row should now be used as a variable in further stages. How can I do that? I know the loop possibility in the transformer stage, but does not seem to solve my problem.

Should i work with the loop stage in the jop sequence? If yes how?

the Problem:

foreach($selectQueryResults as row) {
// do something with the row-value
}

Thanks!

Solution

  • I have to admit, I've not got access to DataStage anymore nor the code I once had.

    This is to propagate stage variables at a Job Level

    However the routine activity to accomplish your task would be as follows.

    1. Propagate SQL as a variable
    2. Use DSExecute to execute a command line function (to call SQLPlus, nzsql or whichever your command line is) and pass through your SQL variable
    3. Return it's results into another variable
    4. With that variable you can split the contents, first by line and then by delimiter using loop / for statement.
    5. Use the DSSetParam to map the key value pairs to your parameters of a specific job by using the DSAttachJob function, or just propagate them as outputs from the routine activity

    BASIC Language Reference

    DSSetParam

    Remember that error handling and commenting is important within the BASIC routines otherwise

    Subsequently, this is to propagate stage variables within a transformer and is a very powerful tool once it's been mastered.

    Here is the documentation and examples for defining stage variables within a Transformer model and aggregating the output. Please note, the order of the stage variables is extremely important.

    TransformerLoops

    Within a transformer you can define incoming columns as stage variables, use those stage variables to aggregate, concatenate (strings), split, subtract... basically you can do a ridiculous amount once you get your head around it.

    I would suggest going through the Transformer Examples first as I suspect that this may be what you're looking for.

    Remember, it doesn't all have to be completed in a single Transformer stage, you can get the initial cleansing done in the first transformer and then do the complex loop in the second, break the steps down for what works for you.