Weird behavior from tDBOutput component -- Please help explain!

Highlighted
Seven Stars

Weird behavior from tDBOutput component -- Please help explain!

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:

 My job design.JPG

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:

test-1.JPG 

test-11.JPG 

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 - 

test-2.JPG test-22.JPG  

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:

NO AUTOCOMMIT.JPG   

 

And I delete all rows from the table in PreJob:

last.JPG  

 

 

I need help to understand this. Thanks!

 

 

 

 

 

 


Accepted Solutions
Employee

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

Hi,

 

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

 

image.png

 

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 :-)

View solution in original post


All Replies
Employee

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

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 :-)

Seven Stars

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

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 -

PO_Rreceipt.JPG

 

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.

new design.JPG  

 

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

new design_result.JPG

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 -

result_ without tDie.JPG

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 -

result_without Rollback_01.JPG 

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! -

result_without Rollback_02.JPG

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,

TM

Seven Stars

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

Nikhil,

I did more test and have another question to ask.

 

another_new_design.JPG  

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 -

RunIf.JPG  

And I run the job - 

RunIf_result.JPG  

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?

 

Employee

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

Hi,

 

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

 

image.png

 

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 :-)

View solution in original post

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog