Home Services Partners Company
Accelerate Informatica PowerCenter bulk insert from Unix/Linux to SQL Server and Sybase

Accelerate Informatica PowerCenter bulk insert from Unix/Linux to SQL Server and Sybase

October 10, 2011 0 Comments

I have been consulting on several projects to accelerate bulk insert performance into SQL Server and Sybase from Unix/Linux with Informatica PowerCenter, and want to share their success with the DataDirect Connect™ Drivers Bulk Load solution.  The amount of data being loaded into both OLTP and DW BigData systems is growing faster than their infrastructure can handle, and the feedback I have heard is that our solution is a "win-win" situation because it is:

  • Fast: High flying performance of bulk insert movement into Sybase and SQL Server using Informatica PowerCenter from Unix/Linux
  • EasyGet your POC started in 15 minutes with no external client or server components
  • An excellent value: DataDirect bulk-enabled drivers cost a fraction of alternate bulk solutions

What does DataDirect Bulk Load mean for Informatica PowerCenter?

DataDirect Drivers Bulk Load is a versatile feature built entirely within the DataDirect Connect for ODBC drivers available in the current 6.1 version.  There is no requirement for any external components, and it can be enabled without code changes to the application, including PowerCenter.  This is what we refer to as "codeless configuration".  Just flip a switch in the ODBC data source, and solve that big data bottleneck.

In what situations should DataDirect Bulk Load be used with PowerCenter?

Organizations are using DataDirect Bulk Load on AIX, Solaris, HP-UX, and Linux to bulk load data into Sybase and SQL Server.  This is because current versions of PowerCenter v8.6 distribute drivers without bulk load functionality.  The latest DataDirect Connect for ODBC drivers can be integrated with Informatica such that the bulk-enabled drivers can be used simultaneously.  Therefore, the 6.1 driver can either be used throughout your Informatica environment, or only for bulk load operations.

How do I enable bulk load when bulk mode is not available for my target database?

Set the Target Load Type from "bulk" to "normal"; and configure the property EnableBulkLoad=1 in the Sybase or SQL Server data source.  This property enables the driver to translate the native database protocol to use bulk insert on the parameter array inserts used in normal mode.

Bulk load informatica powercenter

Step on the gas today with DataDirect bulk load:

1. Download a free 15 day trial of the Connect64 for SQL Server ODBC driver and test-connect using the Quick Start guide from the Evaluation Support Center.

2. Integrate the latest driver installed into your version of PowerCenter by following the steps from our knowledge article.

3. Set EnableBulkLoad=1 in the ODBC data source, and configure the destination to use Target Load Type of 'normal'.

4. Or call 1-800-876-3101 to speak with a live Solutions Consultant to learn more about how organizations are making progress with Informatica Powercenter and DataDirect Bulk.

Then roll down the windows while you're cruising to a successful big-data POC!

Note: For optimal performance with SQL Server, refer to Chapter 11 of the User's Guide: Advanced Features > DataDirect Bulk Load.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Comments
Comments are disabled in preview mode.