topic Data missing from output of additional columns in Talend Studio

Data missing from output of additional columns

Anonymous — Tue, 02 Apr 2019 16:34:59 GMT

Hi All

I have created an additional column for a DBoutput component which gets a sequence value from the connected database.

The column titleid is included in both the input and output schemas.

The joob inserts records with the correctly generated sequence number. However, the subsequent dataflows are missing the values generated.

A logfile or examination of the written data shows that for both rows the value of titleid is null.

The audit_inserts needs to be able to record the sequence generated ID's during the job.

I thought the advanced additional columns was the way to go, but appears I am missing something.

(It is not possible, in this case, to use a Talend generated sequence)

Any ideas why the values are missing from the pipeline flow into Audit_inserts but are written correctly to Insert_Recs?

Re: Data missing from output of additional columns

akumar2301 — Tue, 02 Apr 2019 16:58:27 GMT

As it is advanced column ,these columns are computed in DB engine not in DI environment , So this value will not be passed to next flow in Design.

Re: Data missing from output of additional columns

Anonymous — Tue, 02 Apr 2019 18:04:21 GMT

Hi Akumar,
Yes, I cam to the same conclusion.
This puts me back to the initial issue of trapping the sequence numbers generated by the underlying DB. Any suggestions? I have checked the other threads on this issue and believed advanced additional columns to be the answer ... alas no.

Re: Data missing from output of additional columns

Anonymous — Tue, 02 Apr 2019 18:34:10 GMT

Hi,

If you are using the sequence like in advanced column, please try to commit the data before moving to next segment. Then fetch the information again (of the sequence number) using the original candidate keys of the records.

But I would think of another easier option. You can store the last modified sequence number in a control table. Then at the prejob stage, fetch the data of this value and store to a context variable.

Now, for the key column, ad the Talend numerical sequence number to insert data. But instead of starting from 1, use the context variable as the starting value.

Numeric.sequence("s1",context.max_value,1)

In this way, you can maintain the sequence using DB and Talend. You can store the maximum value of sequence back to control table at the last stage of the job (either from existing result set or by querying DB based on your comfort level).

This could be an easier option if you want the sequence number further in your flow.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂