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

Posted on 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

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.


Comments are disabled in preview mode.

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation