topic Weird behavior from tDBOutput component -- Please help explain! in Talend Studio

Weird behavior from tDBOutput component -- Please help explain!

Anonymous — Thu, 28 Feb 2019 22:41:44 GMT

I found something weird with the tDBOutput component. I will describe it in detail, please be patient with me.

I use Talend Open Studio for DI 7.1.1

Here is my job design:

This is a simple job: it reads data from a flat file "PO_RCT" and insert the rows into a table "PO_Receipt".

It only has two checkpoints: 01- Insert RCT Rows & 02 - Archive RCT File (not shown).

I added a little extra in the job:

I added a trailer row on the flat file which has the total # of records info. In my PreJob, I created a GlobalVar "totalRCTCnt" to get the total # of records from the trailer row.

In my main job, it reads the file ( set the Footer = 1 in tFileInpitDelimited to exclude the trailer row) and try to insert the rows into PO_Receipt table in the database.

I have two RunIf condition:

If ((Integer)globalMap.get("tFileInputDelimited_2_NB_LINE")).intValue()==((BigDecimal)globalMap.get("totalRCTCnt")).intValue(), Commit Rows

If ((Integer)globalMap.get("tFileInputDelimited_2_NB_LINE")).intValue()!=((BigDecimal)globalMap.get("totalRCTCnt")).intValue(), send error email and Rollback Rows.

Now I have 2 real rows in the file, but I intentionally put total # of records =12 in the trailer row to test my logic and my job. Here is the result:

So the job goes to the If (totalCountDiffer) path and Rollback the rows, the job failed and there is no rows in the PO_Receipt table. Of course, if I correct the total # of records to be 2 in the trailer row, job would succeed with 2 rows inserted.

All above is what I expected.

Then I did some other thing to the flat file, this time I observed the weird behavior:

In the flat, again I set the total # of records = 12 and the file has the original 2 rows. I insert the 3rd row right above the trailer row, and make one of fields null (empty) -- this field is part of the composite primary key in PO_Receipt table. Now I run the job again -

As we can see, 3 rows were read from the flat file but only 2 rows shown after tDBOutput ("PO_Receipt"). It seems the job didn't go to the next subjob ("Print Total Counts"), and neither tDBCommit nor tDBRollback shown run condition. And I didn't receive the error email as well.

But there are 2 rows inserted into the table! How could this happen?

BTW, I have "Auto Commit" unchecked in my DBConnection:

And I delete all rows from the table in PreJob:

I need help to understand this. Thanks!

Re: Weird behavior from tDBOutput component -- Please help explain!

Anonymous — Fri, 01 Mar 2019 05:37:04 GMT

Hi,

Could you please enable the Die on Error action for tOracleOutput where you are writing the data. Then use a OnSubJobError to capture the error and rollback (like you are doing in the case of count mismatch)?

Could you also try to print the values used in in Run if condition in the same tJava using System.out.println() function? This will give you idea whether you are getting right numbers from previous components (just another debugging step).

Please let us know the results and we will take it forward from there.

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 🙂

Re: Weird behavior from tDBOutput component -- Please help explain!

Anonymous — Fri, 01 Mar 2019 17:08:06 GMT

Nikhil,

Thank you for your reply!

Could you please enable the Die on Error action for tOracleOutput where you are writing the data.

A: Die on Error was ready enabled -

Then use a OnSubJobError to capture the error and rollback (like you are doing in the case of count mismatch)?

A: I modified the job based on your suggestion and please see the new job design and run result described below -

The new design to have a OnSubJobError path and try to print out error message before rollback the rows.

Job run result ( 3 real rows in the source, 1 of them violated the not null constraint):

I highlighted all the important info in Yellow: the job ran through the OnSubjobError path and error message was printed out and the job exit with exit code 1. Of course, no rows were inserted.

But if modify the job a little bit, I see something a little different and I still have questions to ask.

1st modification: Disable the tDie component and run the job -

As you can see, the job ran through the OnSubjobError path and we still get the same error message (which was logged by my error file), still no rows were inserted. But look at the bottom: exit code =0.

My question: Why the exit code would be 0 when the job has an error?

2nd modification: Disable the tDie & tDBRollback and run the job -

The job ran through the OnSubjobError path and we still get the same error message. And exit code = 0

And this time 2 rows were inserted! -

My question:

1. Why the exit code would be 0 when the job has an error? (same as the above one)

2. Why 2 rows would be inserted when there is no tDBCommit component working in the subjob?

3. Why the global variable tDBOutput_1_NB_LINE_INSERTED shows "Number of Rows inserted" null but there are actually rows inserted into the table?

Thanks,

Re: Weird behavior from tDBOutput component -- Please help explain!

Anonymous — Fri, 01 Mar 2019 20:15:23 GMT

Nikhil,

I did more test and have another question to ask.

In this new design, I connect tDBOutput and the subjob (3 components with red background) with OnComponentError. The job ran through the subjob and failed (exit code =1). No rows were inserted.

Then I change the OnComponentError to RunIf -

And I run the job -

As you can see, it didn't kickoff the subjob at all but 2 rows were inserted (which is the existing mystery).

My new question is: Why the Run If trigger not working here?

Re: Weird behavior from tDBOutput component -- Please help explain!

Anonymous — Sat, 02 Mar 2019 14:41:29 GMT

Hi,

I would suggest to retain the OnSubJobOk version of your job with minor modifications.

In the schema of tOracleOutput, you need to select the keys to mention the PK columns by selecting the keys check box. Also please select the Action on Data as "Insert or Update". ANother thing you have to do is in the tMap output expression editor. You need to verify whether the input columns are not null for Primary Key columns.

So you can add a filter condition in output schema's filter expression by adding Not Null check for primary key columns. You can send all the records which is failing this condition to another output flow and check these records later. Or you can send these failed records back to source system. In this way, you should be able to load all good records and reject bad ones.

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 🙂