bdu

The Bulk Data Utility (BDU) is a command line utility that allows you to load data from a delimited text file into a Pervasive PSQL table. The table and database must already exist.

The BDU, the table, the database, and the Pervasive PSQL database engine must all be located on the same machine. The delimited text file must be locally accessible by the database engine server through a local drive, mapped drive, mounted folder, or shared folder.

You may use a default delimiter or a user-specified delimiter. The delimiting character must not be contained in the data itself. The following tables list the permissible delimiters.

Table 16

Delimiters for Columns

Any single printable character (control characters are not printable, except null, tab, new line, and carriage return)

(*, A, t, l, and so forth)

Note: Pervasive PSQL does not support the use of NULL terminator (\0) or double quote (") as column delimiters.

Table 17

Delimiters for Rows

New line character

Carriage return

Carriage return line feed (CR LF)

The BDU supports only the single quote (') and the double quote (") characters as text qualifiers. The data file may contain column values enclosed by single quotes or by double quotes. For example, the following column values are enclosed by double quotes and delimited by the TAB character: "Fred"\t"22"\t"2459"\t"Sales"\t.

The BDU treats consecutive column delimiters as NULL values. If the utility finds consecutive column delimiters, it inserts a NULL value into the column, provided the column is nullable.

No qualifiers are allowed for a NULL value. The following column data a NULL value in the second column. Note that qualifiers are not included for that column: "Fred"\t\t"2459"\t"Sales"\t.

bdu {database_name} {table_name} {data_file}

[-e max_errors]

[-r reject_file]

[-f first_row]

[-l last_row]

[-t field_term]

[-n row_term]

[-o output_file]

[{-u login_id} {-p password}]

[-q text_qualifier]

Note: When loading data with BDU into a secured database for which the Btrieve Security policy is set to “Mixed,” the supplied credentials (user name and password) must match those of a Pervasive PSQL database user account and an operating system user account.

Table 18

Bulk Data Utility Parameters

Mandatory/Optional

Database name to connect to the local Pervasive PSQL engine

Name of the table to be populated

Name and location of the delimited text file

The BDU exits when the first error is encountered.

The maximum number of errors that the BDU ignores before exiting.

Name of the file to which to write the rows that failed load. The specified directory must exist. However, if the file does not exist in the specified directory, it will be created.

The first row in the delimited text file with which the load begins. This parameter allows you to skip a header row. For example, if your header row is row 1, set first_row to 2.

End of the source file

Last row in source file (row will be included in load). The load will stop after the end row has been loaded

A character, such as a comma or tab character

Column delimiter in the source file

A new line character

Row delimiter in the source file

Name of the file to which to write the information and error messages during load. The specified directory must exist. However, if the file does not exist in the specified directory, it will be created.

User name to connect to the Pervasive SQL engine.

Password to connect to the Pervasive SQL engine

Configuration Settings

You are not required to change any Pervasive PSQL configuration settings to use BDU.

BDU loads data into a table using the accelerated mode. During the load of data, the MicroKernel does not perform transaction logging.

If you use archival logging, back up your data files again

By default, BDU logs all information and error messages to the standard error stream (stderr). You may specify a log file to which the utility writes the information or error messages.

Two types of errors are not logged: critical and recoverable. With critical errors, BDU exits because it cannot perform error recovery. For example, a missing delimited data file is a critical error.

With recoverable errors, BDU skips the error and continues processing. The utility keeps a count of such skipped errors and exits when it reaches a user-specified threshold. By default, the threshold is set to zero.

The following constraints apply to loading data with BDU.


Constraint	Discussion
Any Referential Integrity (RI) error is considered an RI violation	Row is rejected
Any unique or primary key violations	Row is rejected
No value specified for a non-NULL column1	Row is rejected regardless of column’s default value
No value specified for a nullable column1	NULL inserted regardless of column’s default value
Table into which data is being loaded contains insert triggers	BDU returns an error and does not attempt to load the table. Drop the insert triggers on the table then re-run BDU
Table into which data is being loaded contains CLOB or BLOB columns	BDU does not attempt to load the table
Order of rows	BDU treats the delimited data file as unordered. The original order of rows may not be preserved.
Date fields	The only supported format is yyyy-mm-dd
Time fields	The only supported format is HH:MM:SS
Timestamp fields	The only supported format is yyyy-mm-dd HH:MM:SS.MS
1 BDU is not aware of default values for a column defined during table creation or update

If possible, run BDU when the database load is minimal or when no concurrent sessions exist on the table being loaded.

If the table being loaded contains any indexes, drop the indexes before using BDU. Re-create the indexes after the load is complete.

If the table being loaded contains any columns with check constraints, drop the check constraints before using BDU. Re-specify the constraints after the load is complete.

Sample Source File

The following content may be used to create a sample delimited text file. You may use the file to verify the usage examples. The examples refer to the sample file as data_file.txt.

Note that, because the following content is comma delimited, you must specify the -t parameter (-t ,) with BDU. The -t parameter is required for any delimiter except the TAB character.

pervasiveBDUsample_1,12345,pervasive,101,18446744073709551615
pervasiveBDUsample_2,12346,pervasive,102,18446744073709551614
pervasiveBDUsample_3,12347,pervasive,103,18446744073709551613
pervasiveBDUsample_4,12348,pervasive,104,18446744073709551612
pervasiveBDUsample_5,12349,pervasive,105,18446744073709551611
pervasiveBDUsample_6,12350,pervasive,106,18446744073709551610
pervasiveBDUsample_7,12351,pervasive,107,18446744073709551609
pervasiveBDUsample_8,12352,pervasive,108,18446744073709551608
pervasiveBDUsample_9,12353,pervasive,109,18446744073709551607
pervasiveBDUsample10,12354,pervasive,110,18446744073709551606

The following examples assume that a table named BDU_Table is part of the Demodata sample database. To add such a table to Demodata, use the following query:

CREATE TABLE BDU_Table (Name CHAR(20) NOT NULL CASE, PhoneNo INTEGER,BuildingName CHAR(25) NOT NULL CASE, RoomNo UINT NOT NULL,HeadOfDept UBIGINT NOT NULL)

To run BDU with the default options:

bdu demodata BDU_Table C:\data_file.txt

Note: The input data must be TAB delimited to use default options. If the input data is not TAB delimited, you must specify the delimiter with the -t parameter.

For example, to use the data from Examples, which is comma delimited, you would run BDU as follows:

bdu demodata BDU_Table C:\data_file.txt -t ,

============

To run the BDU for a database that requires username and password:

bdu demodata BDU_Table C:\data_file.txt -u <username> -p <password>

============

To run the BDU with max errors option:

bdu demodata BDU_Table C:\data_file.txt -e <no of errors user wants to allow>

For instance, for loading to continue until 100 errors have occurred:

bdu demodata BDU_Table C:\data_file.txt -e 100

============

To run the BDU with a specific column delimiter option:

bdu demodata BDU_Table C:\data_file.txt -t <column delimiter>

When the source file contains text in which each row is separated by ,

bdu demodata BDU_Table C:\data_file.txt -t ,

============

To run the BDU with a specific row delimiter option:

bdu demodata BDU_Table C:\data_file.txt -n <row delimiter>

For instance, when the source file contains text in which each row is separated by \n:

bdu demodata BDU_Table C:\data_file.txt -n \n

============

To run the BDU with a specific start row option:

bdu demodata BDU_Table C:\data_file.txt -f <line no. from which user wants loading to begin>

============

To run the BDU with a specific end row option:

bdu demodata BDU_Table C:\data_file.txt -l <line no. at which user wants loading to end>

============

You may combine parameters. To load the first 15 rows from the source file containing data that is separated by | and is enclosed in ':

bdu demodata BDU_Table C:\data_file.txt -f 1 -l 15 -t |