ASA SQL User's Guide
Importing and Exporting Data
Introduction to import and export
The Interactive SQL INPUT and OUTPUT commands are external to the database (client-side). If ISQL is being run on a different machine than the database server, paths to files being read or written are relative to the client. An INPUT is recorded in the transaction log as a separate INSERT statement for each row read. As a result, INPUT is considerably slower than LOAD TABLE. This also means that ON INSERT triggers will fire during an INPUT. Missing values will be inserted as NULL on NULLABLE rows, as 0 (zero) on non-nullable numeric columns, and as an empty string on non-nullable non-numeric columns. The OUTPUT statement is useful when compatibility is an issue since it can write out the result set of a SELECT statement to any one of a number of file formats.
The LOAD TABLE, UNLOAD TABLE and UNLOAD statements, on the other hand, are internal to the database (server-side). Paths to files being written or read are relative to the database server. Only the command travels to the database server, where all processing happens. A LOAD table statement is recorded in the transaction log as a single command. The data file must contain the same number of columns as the table to be loaded. Missing values on columns with a default value will be inserted as NULL, zero or an empty string if the DEFAULTS option is set to OFF (default), or as the default value if the DEFAULTS value is set to ON. Internal importing and exporting only provides access to text and BCP formats, but it is a faster method.
Although loading large volumes of data into a database can be very time consuming, there are a few things you can do to save time:
If you use the LOAD TABLE statement, then bulk mode (starting the server with the -b option) is not necessary.
If you are using the INPUT command, run Interactive SQL or the client application on the same machine as the server. Loading data over the network adds extra communication overhead. This might mean loading new data during off hours.
Place data files on a separate physical disk drive from the database. This could avoid excessive disk head movement during the load.
If you are using the INPUT command, start the server with the -b option for bulk operations mode. In this mode, the server does not keep a rollback log or a transaction log, it does not perform an automatic COMMIT before data definition commands, and it does not lock any records.
The server allows only one connection when you use the -b option.
Without a rollback log, you cannot use savepoints and aborting a command always causes transactions to roll back. Without automatic COMMIT, a ROLLBACK undoes everything since the last explicit COMMIT.
Without a transaction log, there is no log of the changes. You should back up the database before and after using bulk operations mode because, in this mode, your database is not protected against media failure. For more information, see Backup and Data Recovery.
If you have data that requires many commits, running with the -b option may slow database operation. At each COMMIT, the server carries out a checkpoint; this frequent checkpointing can slow the server.
Extend the size of the database, as described in ALTER DBSPACE statement. This command allows a database to be extended in large amounts before the space is required, rather than the normal 256 kb at a time when the space is needed. As well as improving performance for loading large amounts of data, it also serves to keep the database more contiguous within the file system.
You can use temporary tables to load data. Local or global temporary tables are useful when you need to load a set of data repeatedly, or when you need to merge tables with different structures.