Batch Controlling within DataStage

[SimonB]

Hi
As a relative newcomer to DS v6, I am having a problem with assiging Batch Numbers and RowIds to records. Any help greatfully received [:)]

I have created a table (BATCH_CONTROL using Interface ID as primary key) that holds info for each DS interface mapping (e.g. Batch range,max records per batch ID, last batch run date,next batch number etc.) and need DS to lookup the required info and generate the Row Ids and batch numbers accordingly (for loading into Siebel EIM).

The lookups and batch logic are housed within a shared container (so it can be reused through all interfaces) and the DS job needs to pass through the Interface ID to the container which in turn looks up the info, applies logic (including updating the lookup table) and passes out a Batch ID and Row ID for the current record.

The problem is that the Transformer that supplies the info to the container can only have 1 Stream input link (i.e. the original information) and I get the compilation error "Invalid reference input" relating to the reference link that I want to return the info from the container. How else can I get the returned info?

Very convoluted I'm sorry but I'm not sure how else to explain it. Do I need the Merge transformer to solve this problem?

Any ideas!?

Simon Bayliss
Cap Gemini Ernst & Young (UK)

[ray.wurlod]

If it's a reference lookup, which your description suggests that it is, then you need to provide the value through the Key Expression field of that link's properties within a Transformer stage. The InterfaceID is supplied on the stream input link; the name of this column is the Key Expression on the reference input link.
If this row is found, then any columns named on the reference input link are returned for that row and the reference input link's NOTFOUND variable is set to 0 (false).
If the row is not found, then the reference input link's NOTFOUND variable is set to 1 (true), and NULL is returned for all columns named on the reference input link.


Table
| (reference input)
V
-----> Transformer ------>
(stream
input)

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[SimonB]

Thanks Ray...

I guess my problem is that the container contains the ODBC lookup, 2 transformers, an aggregator and an ODBC target (as well as the container output) and I can't feed the returned output back into the transformer that referenced it (transfomer only accepts 1 stream input and a reference link throws up the error message). Subsequently, I can't combine/merge the returned data with that of the current record.

Does that help at all?!?
Cheers
Simon

[ray.wurlod]

Can't see why you can't implement what you describe. You can have as many outputs from the container as you need, and any these can deliver as many columns as you need. So, for example, one output could be delivering aggregated data, another non-aggregated data.
The problem really needs to be defined more fully. Exactly what are the error messages being logged, and exactly what is the design used within the container?
What I'm reading is the following design (dots are only to thwart the de-indenting behaviour of this Forum's server!):


.................ODBC
...................| (ref)
...................V
Inp -----> Transformer1 ---> Transformer2 ---> Agg ---> Out


What is the Key Expression in Transformer1?
What are the columns defined on the reference input link?

[SimonB]

Ray, Many thanks for your effort and interest on this....
Your diagram was almost on the button!
What I'm trying to do is:

.................ODBC
...................| (ref)
...................V
Inp -----> Transformer1 ---> Transformer2 ---> Out
..................................|
..................................V
.................................Agg --->ODBC (Update)

The primary key here is InterfaceID and the aggregator updates the ODBC each time the BATCH ID (from OCDB ref) increments by 1 (every 3000 rows in this instance) - so the mapping knows which Batch ID to start from the next time it is run (thereby avoiding unique constraints violations on the target table).

The compilation error I get is "Invalid reference input" (relating to the returned data from container). Another diagram:

........................Container (see above)
...........................A..|
...........................|..|(reference link)
...........................|..V
Seq_File_input -----> Transformer1 ----------> ODBC (Insert)
.....................(get BatchID and RowID)

I need to be able to combine the output of Container to that of Transformer1.

By coincidence, I am visiting Ascential this afternoon so they may be able to shed some light on the error of my ways!

Once again, thanks for your input on this

Kind regards
Simon Bayliss

[ray.wurlod]

A reference input link, whether it's coming from a container or not, is trying to perform:
SELECT columns FROM table WHERE key = ?
The value to replace the parameter marker is derived from the Key Expression in the Transformer stage fed by the reference input link.

This can not be fed from a Transformer stage (which is what your design is doing; you need some kind of data set that is amenable to key-based accesss, typically a table).

Have the output of your container feed its results into a table (a UV table would do), and have this feed your reference input link.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[SimonB]

Thanks Ray
A clever workaround and one that I hadn't considered. I had got to the point where the only possible solution I could think of was to call a PL/SQL stored procedure (that applied the lookup/logic within the container) and return the resultset back to the interface.

I will certainly give your suggestion a tryout today.
Many thanks for your help on this
Simon