Improve performance of DB2 load

Highlighted
Eight Stars

Improve performance of DB2 load

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

 


Accepted Solutions
Employee

Re: Improve performance of DB2 load

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

 


All Replies
Employee

Re: Improve performance of DB2 load

Hi,

 

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

 

     image.png

 

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

 

Eight Stars

Re: Improve performance of DB2 load

Hi @nikhilthampi,

 

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?

 

 

Employee

Re: Improve performance of DB2 load

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

Eight Stars

Re: Improve performance of DB2 load

sure @nikhilthampi.

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 db2Screen Shot 2019-01-03 at 1.41.06 AM.png

 

 

 

Employee

Re: Improve performance of DB2 load

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

 

Nine Stars

Re: Improve performance of DB2 load

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?

 

Regards David
Dont forget to give Kudos when an answer is helpful or mark the answer as the solution.
Eight Stars

Re: Improve performance of DB2 load

Hi @nikhilthampi,

 

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

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 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download