Bulk import details

Bulk import is a faster method of importing large quantities of data, for example in the region of hundreds of thousands of records. You can also import from XML.

Bulk import:
  1. Uses the BULK INSERT statement to import into temporary tables.
    Note: You do not need to change the database recovery model to BULK LOGGED because bulk import uses bulk operations on temporary tables, and bulk operations on objects in tempdb are already BULK LOGGED.
  2. Creates temporary files in the Bulk Import data files folder that is defined as part of the server configuration.
  3. Transfers the data into the iBase database and on successful completion of this phase, deletes the temporary files that are created in the Bulk Import data files folder.
Bulk import can be used with:
  • Import specifications run manually from iBase Designer (they can be set up and verified in iBase)
  • Import batch specifications run manually from iBase Designer (they can also be set up in iBase)
  • Import batch specifications are run on a scheduled basis by iBase Scheduler
For each entity or link type imported, bulk import runs through four phases:
  1. It copies the import data to a temporary file in the specified Bulk Import data files folder.
    Warning: Secure this folder to prevent unauthorized access to the temporary copy of the import data. This temporary file cannot be deleted if iBase or Scheduler crash during a bulk import.

    The import fails if any errors are encountered while creating the temporary file. However, carrying out a preliminary verification in iBase would identify these errors in advance of the import. The possible errors include:

    • Data that is too long for the iBase field
    • Mismatch between locales (for example dates that are in the wrong format for the locale)
    • Too many matches because the identifiers are not sufficiently selective

    Before running a bulk import that uses a new import specification, you can verify it with some representative sample data. To verify an import, click Verify on the last page of the Import wizard.

  2. It imports the data into temporary tables.

    The import fails if any errors are encountered at this stage.  

  3. For each entity and link type, it reports any records with any of the following errors:
    • Value not defined in list for field (for 'selected from code list' fields)
    • Mandatory field value not supplied
    • Field value too long
    • Invalid direction
    • Invalid strength
    • Invalid date
    • Invalid time
    • Invalid numbers
    • Incorrect number of columns

    Bulk import then maps data to records in the database to obtain statistics on the numbers of records added, updated, and unchanged when the data is imported. All work takes place in temporary tables; no changes are made to the iBase database at this stage.

    Any errors that occur at this stage prevent individual records for this entity or link type from being created or updated.

    Note: Users can decide on the threshold for the number of errors that will cause an import to stop completely in phases 3 or 4. If unspecified, this is set to a default threshold of 10,000 errors. Records imported into the database before the threshold is reached will remain in the database.

    For example, suppose that you are importing links, and the 'entity 1' records are already imported. During the import of the 'entity 2' records, the error threshold is reached. This means that the 'entity 1' records will remain in the database, but no records for 'entity 2' or the links would be imported.

    (Standard imports will continue to run even if 10,000 errors occur.)

  4. Finally, it transfers the data into the database and updates the corresponding import set. Because the data transfer and the update of the import set occur in the same step, the import set will accurately reflect the data that was imported (even after a catastrophic server failure, such as a power cut). It will also delete the temporary files created in the Bulk Import Data Files folder.

    If you are using audit level 4 or 5, the start and end of the import is recorded in the audit log.

Permissions for running bulk import

When iBase is run with an SQL Server database, the security context for all database operations is set at the database level and defined through the iBase application role. However, bulk import uses the BULK INSERT statement and it is not possible to grant the server-level permissions required to execute this statement to the iBase application role.

To obtain the necessary level of permissions, impersonation is used. Depending on the security requirements of your organization, there are several ways of configuring this. All methods impersonate a principal that has the ADMINISTER BULK OPERATIONS permission by using the EXECUTE AS clause in a stored procedure that executes the BULK INSERT statement. In order to use impersonation, you must either:
  • Set the TRUSTWORTHY database property to ON.
  • Use a certificate-signed stored procedure.

This documentation includes three examples of suitable stored procedures that will work for both Windows and SQL Server Authentication (see Related topics below). The certificate-signed stored procedure is the most secure of the three methods.

Configuring a server for bulk import

Bulk import can only be run on a suitably configured database and server. Configuration consists of providing the information required to generate a stored procedure that executes the BULK INSERT statement. This must be repeated for each database.

There are two ways of doing this:
  • If you are both the SQL Server administrator and the iBase administrator, and are satisfied with the security context for the iBase database and its server, you can configure the server from within iBase Designer. This avoids the need to write an SQL script. For further details, including details of the default security context, see Activating Bulk Import in iBase Designer.
  • If you want to control the security context within which bulk import runs on the server (for example, if you do not want to grant server-level permissions to the iBase administrator), you will need to use an SQL script. Three example scripts are provided (see Related topics below).
Note: After you have set up the database and server for bulk import, iBase administrators should not run the Activate Bulk Import command in iBase Designer, as this will overwrite your customized setup.

Backing up databases that use bulk import

You need to ensure that the iBase database is always backed up before bulk import is run, and then again after the import has completed.

SQL Server database statistics and bulk import

A SQL Server administrator must update the SQL Server database statistics on the tables that are being loaded (including the _LinkEnd table when importing links) if the following error is displayed but you consider the identifiers to be sufficiently selective: Bulk Import failed the safety check because there are too many matches; the identifiers are not sufficiently selective.