Fastload Operation

The fastload operation loads data from a binary format file into a single table in a single database. It loads each contiguous n bytes of data into a new row in the target table.

The fastload operation can be performed using the fastload command (see the Command Reference Guide) or in VDBA using the Fastload Table dialog. In VDBA, you select the desired table from a database and choose a file from which you want to load the data in the Fastload Table dialog.

Requirements for Using Fastload

The following requirements must be met to perform a fastload:

• Since the fastload command creates its own server, if simultaneous access to the database is required for additional sessions, the following DBMS parameter changes are required for any DBMS that has access to the same database:
  • cache_sharing ON
  • fast_commit OFF
  • sole_server OFF
• The fastload operation must be able to obtain an exclusive lock on the table or fastload exits.
• The file's data format must match the table's data format.

  If the formats do not match, incorrect data are loaded into the table. For example, if each record in a file contains a 5-byte char and a 4-byte integer—and this file is loaded into a table that has a 4-byte char field followed by a 4-byte integer field—fastload reads 8-bytes of the file and load it as a row into the table. This means that the integer field does not contain the actual integer in the original file because the last byte of the 5-byte char field plus 3-bytes of the integer field are interpreted as a 4-byte integer. The problem grows as more data is read because the data are off by one more byte for each row.

  Check that the record length in the file matches the record length expected by the table. For tables that do not include large object columns (such as long varchar and long byte), the record length should match the row width, as given by the SQL help table statement.

  In many cases, fastload is unable to determine the record size of a binary file (this is the case on all UNIX platforms); in these cases, fastload generates a message warning that no format checking can be performed. The warning also contains the expected size of records in the binary file.

• Be aware of the extra data added by the Ingres varchar data type and all nullable fields. Fastload expects to read a 2-byte integer at the beginning of a varchar field that contains the length of the varchar data. All nullable fields must be terminated with a single byte null indicator that determines whether the field is null.
• Fastload supports all standard table structures when loading into empty tables. It can also load data into heap tables that already contain rows.

  All other table types that contain data require a data sort that merges the loaded data with the existing data. Fastload does not perform this function. The data always loads fastest into a heap table with no secondary indexes.

• Fastload does not support complex data types such as intlist, ord_pair, or udts.
• Binary format files cannot be transported between byte-swap and non-byte swap machines. The data can be generated programmatically, but you should be careful to generate the correct record format, taking into account additional bytes needed for some field types.

Perform a Fastload Operation

To perform a fastload operation (load binary format files into a table), perform the following steps:

1. Make a backup of the table's content.

   You need a backup because it can be difficult to fix or eradicate loaded data that is incorrect.

   Always check manually that the data has been loaded correctly.

2. Generate a copy of the file you want to fastload by copying it from an Ingres table that has the same format as the target table, or by creating it programmatically.

   Do the copy in binary format, for example:

   copy test() into 'test.out'

3. Enter the fastload command, for example:

   fastload fload -file=test.out -table=test

   The table "test" in the database "fload" is loaded from the file "test.out".

4. Observe that a summary of the load displays the row size, number of rows loaded, and the number of bytes read.
5. Verify manually that the data has been loaded correctly.

Loading Data in a Multi-CPU Environment

It can be faster to use copy instead of fastload if the load is being done in a multi-CPU environment. Copy is faster because it uses two processes and can use two CPUs, whereas fastload uses only one CPU. Use copy if a large amount of sorting is required to load the data. If there is more than one CPU available on the system, fastload can become CPU—bound on a single CPU.