Bulk Loader Parameters

Use the following parameters to control the bulk loader (bcp) utility for your relational database—Oracle, SQL Server, or DB2. Use the following parameters to customize the selected data source before you clone it. Configuration file parameters are included in parentheses.

Delimiter (bcp_delim)

Specify the character or set of character(s) that are used to separate variable-length fields in the bulk load input records.

For Oracle, enter only one character. Use this option when the database language implies that the decimal character is a comma (the default), as in most European languages.

For SQL Server, enter one or more characters. Use this option if the data contains alpha fields with TAB characters that need to be preserved. (A possible delimiter value in this case would be "|" or "||".)

Code Page (bcp_code_page)

Select a code page option from the drop-down list, or type a value in the valid range (0-99999).

Copied message (bcp_copied_msg)

Enables the bcp_auditor utility to determine whether or not a bulk loader was successful in cases where the database language is not English. For example, in German, this parameter is "Zeilen kopiert", but in English, it is "rows copied". If this parameter is not set correctly, the bcp_utility reports bulk loader failures even though the bulk loader worked correctly. Applies only to SQL Server.

Batch size (bcp_batch_size)

Specifies the bcp utility batch size, which is the number of rows per batch of data copied. This allows the bcp utility to load a table in several batches instead of in a single operation. Permitted values are 0 or 1000-10000000 (rows per batch). A value of zero causes the bcp utility to load the entire group of records in the data file in one batch. Copying all of the rows of a very large table in one batch may require a high number of locks on the Microsoft SQL Server database. Applies only to SQL Server.

Packet size (bcp_packet_size)

Defines the network packet size value for the bcp utility (applies to remote servers only). If you have wide tables, setting this parameter to a packet size larger than the default (4096) can speed up loading the data into the table at the expense of system resources.

Maximum temporary storage (max_temp_storage)

Specify a value between 10MB and 3GB for the maximum amount of storage the DATABridge Client will use for temporary files. Specifying a non-zero value for this parameter activates the segmented bulk load feature. Applies to Oracle and SQL Server only on Windows.

Maximum loader errors (max_errors)

Controls the bulk loader’s tolerance to records that are discarded due to data errors. Use this parameter when you have many bulk loader errors. Increasing the maximum error count allows you to gather all the errors in one run rather than finding 10 errors and then having to start over again. Permitted value range is 0-1000. Applies to Oracle and SQL Server.

SQL loader bind size (sqlld_bindsize)

Defines the value to be used for the BINDSIZE parameter for SQL*Loader operations. Increasing this value can speed up SQL*Loader operations when not using DIRECT mode (for example, running remote to a database on a UNIX system). Use this option only when running dbutility for a remote Oracle database on UNIX or Windows and the inhibit_direct_mode parameter is enabled.

SQL loader direct (inhibit_direct_mode)

Select this option if the DATABridge Client accesses a remote Oracle database using SQL*Net between two dissimilar architectures (for example, Windows and UNIX). This inhibits the use of the direct=true option when invoking SQL*Loader in the command files so that you do not to have to remove the string direct=true from every call on SQL*Loader. When you enable this option, it is recommended that you increase the SQL loader bind size for better performance. Leave this option unselected if your Oracle database is on the same machine as the DATABridge Client. Applies only to Oracle.

SQL loader parallel (enable_parallel_mode)

This parameter, which is only meaningful when DIRECT mode is enabled, causes the generate command to add the specification parallel = true to the SQL*Loader command line. Parallel mode makes the SQL*Loader run faster at the expense of additional system resources. Applies only to Oracle.

SQL loader rows (sqlld_rows)

Defines the value to be used for the ROWS specification for SQL*Loader operations. Use this option only when running dbutility for a remote Oracle database on UNIX or Windows and the inhibit_direct_mode parameter is enabled.

Use comma for decimal character (bcp_decimal_char)

When selected, this option communicates to the SQL loader that a comma is used to separate the integer and decimal portions of a number (for example, 10,3). The default is a period. Applies only to Oracle.

Verify load (verify_bulk_load)

Specifies how the DATABridge Client will handle the results of the bulk copies. Applies to Oracle and SQL Server.

When Don't verify is selected, no action is taken. When Verify (default) is selected, after the final bulk load completes, the DATABridge Client gets the record count of each table and compares it to the total number of records passed to the bulk loader. If the two counts differ, the DATABridge Client displays a warning message. Verify, exit on error is the same as Verify except that the Client terminates so that you can investigate the reason for the mismatch.