Configuring the environment for BCPLoad stages

About this task

Before you can use the BCPLoad stage you must:

Install and configure the SQL Server or Sybase client software. The BCPLoad stage uses the BCP API in the DBLIB/CTLIB and NetLIB client libraries. You must ensure that these components are installed on the IBM® InfoSphere® DataStage® server that acts as a client to the SQL Server DBMS. See the documentation supplied with your DBMS for more details.
Use one of the client tools (for example, ISQLW in the case of Microsoft SQL Server or WISQL32 for Sybase) to ensure that the connectivity between the InfoSphere DataStage server and the SQL Server host is operational.
Create the table in the database on the SQL Server.
Configure your database to use the fast copy (bulk load) option. By using this option, the data is loaded without each insert being recorded in a log file. If you do not specify this setting, all transactions are logged, slowing down the rate at which data is loaded. The fast copy option can be switched on by a stored procedure. For more information about using stored procedures, see Using Stored Procedures.

There are some special points to note about SQL Server. If the following error is returned when you are using the BCPLoad stage with data in YMD format, and the Date Format has been set:

Attempt to convert data stopped by syntax error in source field. If your table contains date fields in ymd format make sure the Date Format property is set

then clear the Use International Settings check box in the DB-Library option page of the SQL Server Client Network Utility.

If your job uses data in the upper 128 characters of the character set and the data is not appearing correctly on the database then clear the Automatic ANSI to OEM conversion check box in the DB-Library option page of the SQL Server Client Network Utility.