[Bulk_Loader]
The bulk loader parameters apply to the bulk loader utility for your relational database—Oracle, SQL Server, or DB2.
Parameter |
Description |
|
bcp_batch_size |
Default:100,000 rows per batch 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. When you specify a nonzero value, the DATABridge Client adds the -b batch_size option to the bcp command line. A value of 0 omits the -b option. |
|
bcp_code_page |
Default: "" Adds the -C code_page option to the bcp command line. For example, to use the code page 932 for Japanese, set this parameter to 932 to add the -C 932 option to the bcp command line. If the parameter is 0 (default), the -C option is not added to the bcp command line. |
|
bcp_copied_msg |
Default: NULL (omitted) 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. The bcp_auditor program also accepts the bcp_copied_message in binary format expressed as the text "HEX_" followed by a string of hexadecimal values representing the values of each byte. This allows you to circumvent code page related problems, which can sometimes corrupt the bcp_copied_message when it is passed to the bcp_auditor program as a command-line argument. For example, the string "HEX_6C69676E657320636F7069E965732E" can represent the French message "lignes copiées." (The character "é" does not cause any problems when expressed as "E9".) |
|
bcp_decimal_char |
Default: a period (.) This parameter, coupled with the bcp_delim parameter, allows the DATABridge Client to work in environments where the Oracle NLS_NUMERIC_CHARACTERS parameter is ‘,.’, such as when NLS_LANG = SPANISH_SPAIN. Since the DATABridge Client cannot control SQL*Loader, it formats the data records using the numeric characters that SQL*Loader expects. Specifies the bcp utility batch size, which is the number of rows per batch of data copied. Values must be enclosed in double quotation marks. |
|
bcp_delim |
Default: For DB2, the DATABridge Client relies on the order of the data delimited by the bcp_delim parameter to match the DMSII data set to the relational database table. This parameter works as follows with the various clients: |
|
|
Oracle |
SQL Server |
|
The string "delim" must be a single character. |
The string "delim" can be longer than one character |
|
Useful if you use the SQL*Loader with the bcp_decimal_char parameter -or- when the database language implies that the decimal character is a comma, as in most European languages. |
Useful if the data contains alpha fields with TAB characters that need to be preserved. (A possible delimiter value in this case would be "|" or "||"; see inhibit_ctrl_chars. |
bcp_packet_size |
Default: 0 (which omits the -a option) Defines the network packet size value for the bcp utility. Use this parameter when you have wide tables. For wide tables, setting this parameter to a packet size larger than the bcp default (4096) can speed up loading the data into the table. When you specify a nonzero value, the DATABridge Client adds the -a pkt_size option to the bcp command line in the .CMD scripts. If you omit this parameter, or if you specify a value of 0, the DATABridge Client omits the -a pkt_size option and the bcp utility uses the default network packet size of 4096. |
|
enable_parallel_mode |
Default: False 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. |
|
inhibit_direct_mode |
Default: False 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 CONVENTIONAL mode by setting this parameter to True. Setting inhibit_direct_mode to True inhibits the use of the direct=true option when invoking SQL*Loader in the command files. It is provided for your convenience so that you do not to have to remove the string direct=true from every call on SQL*Loader. When you enable inhibit_direct_mode, it is recommended that you increase the size of sqlld_bindsize for better performance. |
|
max_errors |
Default: 10 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. For example, if you are having problems cloning a table, you may want to increase the count to 1000 or more to get all the errors in one cloning or process session. Knowing the type of errors helps you to solve the problems. The default value for this parameter is 10, which means that the bulk loader aborts after encountering 10 bad records. These bad records are written to the discard file and information about the error is written to the bulk loader log file. For information about these files, see Files Related to SQL*Loader and Files Related to bcp. |
|
max_temp_storage |
Default: 40 MB This parameter activates the segmented bulk load feature, which allows you to specify the maximum amount of storage that dbutility should use for temporary files. Because dbutility cannot stop in the middle of a record, you can expect it to use slightly more storage than the value you specify. Therefore, select a value less than the total amount of free space available on the disk. It is recommended that you keep this value low as there is no real advantage to attempting to load large tables all at once. If you set the value too high, dbutility can run out of storage while it is writing temporary files. You can specify the max_temp_storage value as an integer with any of the following suffixes: K (or KB) for kilobytes (default) M (or MB) for megabytes G (or GB) for gigabytes The space between the number and the suffix is optional. Note: The valid range for this parameter is 10 MB to 3 GB (0xC0000000). You must specify values greater than 0x7FFFFFFF without a suffix. The value you enter for max_temp_storage must be a whole number. |
|
sqlld_bindsize |
Default: 64K bytes 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 sqlld_rows and sqlld_bindsize when you are running dbutility 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. |
|
sqlld_rows |
Default: 100 Defines the value to be used for the ROWS specification for SQL*Loader operations. Use sqlld_rows and sqlld_bindsize when you are running dbutility for a remote Oracle database running on UNIX or Windows. A larger bindsize and row size can increase the speed of the load across Oracle Network Services at the expense of using more memory. |
|
use_char_mode_bcp |
Default: False Causes the generate command to use the SQL bcp -c option (instead of the SQL bcp -f option). The -c option causes bcp to assume that all data fields are type CHAR when the data is being loaded into the relational database. In addition, this parameter forces Microsoft SQL Server bcp to use the tab character as the bcp delimiter. |
|
verify_bulk_load |
Default: 1 Determines how you want the DATABridge Client to handle the results of the bulk loader operations during data extraction, as follows: |
|
Setting |
Description |
|
0 |
The DATABridge Client does not verify the results of bulk loader operations. |
|
1 |
The DATABridge Client retrieves the number of rows in the table and compares it to the number of rows handled by the bulk loader. If the two counts differ, the DATABridge Client displays a warning message. |
|
2 |
This setting is the same as the preceding setting 1, except that the DATABridge Client terminates so that you can investigate the reason for the mismatch. |
|
 |