• Print |
• E-mail  |
• Bookmark

The following error/warning messages may be issued when using the
BULKLOAD operation with SAS/ACCESS Interface to DB2.

   ERROR: When using the IBM DB2 Load API, tablespaces and/or
   databases may become unrecoverable in the event of a load error.
   To protect data integrity, the load feature is currently disabled.
   Please consult the Alert Notes, Usage Notes or SAS Technical
   Support for information on this issue, as well as an option
   that will re-enable loading.

   WARNING: When using the IBM Load API, tablespaces and/or
   databases may become unrecoverable in the event of a load error.
   Please consult the Alert Notes, Usage Notes, or SAS Technical
   Support for more information.

   ERROR:  SQL0290N Table space access not allowed

If a load operation fails while using BULKLOAD and the BL_REMOTE_FILE
option with SAS/ACCESS to DB2 UDB, the tablespace containing the table
being loaded may be placed in a "load pending" state.  Note that the
tablespace can potentially contain many tables in addition to the one
currently being loaded.

If using DB2 UDB versions prior to 6.1, manual intervention as listed
below will be required to restore access to the tablespace.  However,
if you attempt to access a table in a tablespace that contains a table
that had a failed load, you will encounter the above SQL0290N error
message.  All data in a tablespace may potentially be lost if a load
operation fails and it can not be manually restarted or restored.  It is
strongly suggested that the BL_REMOTEFILE= option be used only with DB2
client and server versions 6.1 and higher.

In DB2 UDB versions prior to 6.1, only the following operations are
available to correct a failed load:

      - Drop and recreate the tablespace or database
      - Restore the tablespace or database from backup
      - Attempt to manually restart the load operation using the DB2
        command interface, presumably after correcting the initial
        problem (note that the data file might have been deleted if the
        BL_DELETE_DATAFILE=NO option was not specified)
      - Attempt to manually replace the table using a new manually
        generated data file, using the DB2 command interface load
        replace command (note that this would cause all existing table
        data to be lost, but might restore the tablespace to normal)
      - Terminate the load using the DB2 command interface which causes
        the tablespace state to become "backup pending," which means
        that the tablespace can only be dropped or restored from a
        backup (note that there is no real advantage to the "backup
        pending" state over the "load pending" state).

If you are using DB2 UDB version 6.1 (or higher) client and server, the
SAS/ACCESS Engine will issue a load terminate after a failed load. In
the case of UDB 6.1, this will cause the load to be rolled back to the
state before the load operation was performed, which is what is
expected.

In any case, it is suggested that tables being bulkloaded be located in
their own tablespace to minimize the problems associated with a failed
load, and to facilitate backup and restore operations.

Due to the potentially damaging behavior noted above, the BULKLOAD
operation using the BL_REMOTE_FILE option has been disabled.
To enable this feature, you must use the BL_CONFIRM_LOAD=YES option in
addition to BL_REMOTE_FILE which will generate the IBM LOAD API WARNING
noted above.

If BL_REMOTE_FILE is specified, but BL_CONFIRM_LOAD=YES is not
specified, the above IBM LOAD API ERROR is generated.

Note that if BULKLOAD=YES is specified, but the BL_REMOTE_FILE= option
is not, there will be no tablespace problems, but the load operation
could be much slower.

Operating System and Release Information