Improve performance of DB2 load

[nivedhitha]

Hi,

I have a csv file with 1.25 millions. Using the normal tDb2output component takes forever to load. Since my target table doesn't have any constraint and the operation is just insert, I thought of bulk loading the data but was not able to. I understand that tDB2BulkOutput and Bulkexec are to be used. But in the bulk output component db2 is not found in the datasource list. So I tried using the exec component directly pointing it to my source csv file. Sadly it didn't work either. 

Can someone please redirect me in the right direction?

Appreciate any input!! Thanks in advance

[Anonymous]

Hi,

     I am able to see the Bulk Component for DB2. Please refer the screen shot for details.

     

My Studio version is 7.1. For other releases, I would suggest to verify the corresponding release notes to verify the availability of the component through help.talend.com.

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 🙂

[nivedhitha]

Hi @nthampi,

Thanks for your reply

Yes, DB2 is present in tBulkExec component but not in tDBOutputBulk component. I have use the output bulk to create a file before using bulkExec component right?

[Anonymous]

@nivedhitha

 Ok. I got your point. Could you please raise it as an issue to Talend JIRA?

https://jira.talendforge.org

If you are using Enterprise version of Talend, I would suggest to create a service ticket also to fast track the same.

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 🙂

[nivedhitha]

sure @nthampi.

In the meantime, is there any other way to improve the load?

Im also getting an error like this when I try to load the data into db2

Screen Shot 2019-01-03 at 1.41.06 AM.png

[Anonymous · Accepted solution]

Hi,

  Could you please increase your commit size so that more number of records are committed together? Please also alter the Batch size and verify the output performance. The details of both these parameters are mentioned in Advanced options of tDB2Output.

https://help.talend.com/reader/jomWd_GKqAmTZviwG_oxHQ/zdkKnxq6deDN_HGuN_VO5A

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 🙂

[David_Beaty]

Hi,

As the t{database_type}OutputBulk components just produce a file in a specific format, for the BulkExec routine to load, have you tried using one for a different database type and configuring it as needed?

[nivedhitha]

Hi @nthampi,

It did solve my problem. Configuring batch o/p and commit interval.

For 1.25 million records it only takes 2-3 minutes

thanks much !!