Skip to content

Bulk Loader

Use the following parameters to control the bulk loader (bcp or SQL*Loader) utility for your relational database (SQL Server or Oracle). Use the following parameters to customize the selected data source before cloning.

Configuration file parameters are included below with the following_font.

Use bcp utility to load tables

Parameter: use_bcp

(SQL Server only) The SQL server Client can operate with the bcp utility or the BCP API. This parameter determines the default value used by the define and redefine commands when setting the ds_options for the various data sets. It is recommended to use the bcp utility as it is more reliable than the BCP API.

Delimiter (5 characters or less)

Parameter: bcp_delim

(SQL Server only) Specify the character or set of characters that are used to separate variable-length fields in the bulk loader input records.

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 (5 characters or less)

Parameter: bcp_code_page

For SQL Server, you can use the names ACP, EOM, RAW or a number such as 1252 to represent a code page. For Oracle, the name is a string such as "WE8ISO8859P1". Consult your Oracle documentation for the actual names.

Copied message (32 characters or less)

Parameter: bcp_copied_msg

(SQL Server only) Allows 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" (default). If this parameter is not set correctly, the bcp_utility reports bulk loader failures even though the bulk loader worked correctly.

Batch size (1000 minimum to 10000000 maximum)

Paramter: bcp_batch_size

(SQL Server only) 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 for 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.

Packet size (512 minimum to 65535 maximum)

Parameter: bcp_packet_size

(SQL Server only) 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. Before you can set the value for this parameter, you must first enable the option in the Administrative Console.

SQL loader numeric character

Parameter: bcp_decimal_char

(Oracle only) This parameter is normally auto-detected by the Client and gets its value by reading the value of Oracle database's NLS_NUMERIC_CHARACTERS parameter.

This method will work correctly when the Client and the database reside in the same machine. However, if the Client is run outside the database machine, it is not guaranteed the the Oracle Client software used by the Databridge Client will have the same NLS settings as the target database.

For example, it is possible to have a US Oracle Client in the Client machine that connects to a Brazilian database. In this rather unusual situation, you would have to set the SQL loader numeric character to ‘.’ as it will default to ',' which would lead to SQL*Loader errors for all records that have numeric data with a decimal point.

SQL loader parallel

Parameter: enable_parallel_mode

(Oracle only) 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.

SQL direct

Parameter: inhibit_direct_mode

(Oracle only) Controls whether the Generate command adds the specification direct=True to the SQL*Loader command line. If your Oracle database is on the same machine as the Databridge Client, you would let this parameter assume its default value of False, as DIRECT mode is much faster than CONVENTIONAL mode. Conversely, if your Databridge Client accesses a remote Oracle database using SQL*Net between two dissimilar architectures (for example, Windows and UNIX), you must use the CONVENTIONAL mode by setting this parameter to True.

Setting SQL direct to False inhibits the use of the "direct=True" option when invoking SQL*Loader in the command files. When you set SQL direct to False, it is recommended to increase the size of SQL loader bind size for better performance.

SQL loader bind size

Parameter: sqlld_bindsize

(Oracle only) Defines the value to be used for the BINDSIZE parameter for SQL*Loader operations. Increasing this value can speed up SQL*Loader operations when using conventional mode (for example, running remote to a database on a UNIX system). Use SQL loader rows and SQL loader bind size when you are running the Databridge Client for a remote Oracle database running on UNIX or Windows. A larger bind size and row size can increase the speed of the load across Oracle Network Services at the expense of using more memory.

SQL loader rows

Parameter: sqlld_rows

(Oracle only) Defines the value to be used for the ROWS specification for SQL*Loader operations. Use SQL loader rows and SQL loader bind size when you are running the Databridge Client for a remote Oracle database running on UNIX or Windows. A larger bind size and row size can increase the speed of the load across Oracle Network Services at the expense of using more memory.

Maximum temporary storage (40M minimum to 3G maximum)

Parameter: max_temp_storage

(Windows only) Specify a value between 10MB and 3GB for the maximum amount of storage the Databridge Client will use for temporary files. Use the letter M or G after the number to indicate megabytes or gigabytes.

Maximum loader errors (0 minimum to 1000 maximum)

Parameter: max_errors

Controls the bulk loader's tolerance to records that are discarded due to data 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.

Maximum number of failed loads (0 minimum to 25 maximum)

Parameter: max_bcp_failures

Specifies how many failed loads the Client will allow before aborting the run.

Verify load

Parameter: verify_bulk_load

Specifies how the Databridge Client will handle the results of the bulk copies.

When Do not verify is selected, no action is taken. When Verify (the default) is selected, the Databridge Client gets the record count of each table and compares it to the total number of records passed to the bulk loader after the final bulk load is completed. 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.