SQL Guide : solidDB® SQL statements : IMPORT
  
IMPORT
IMPORT 'file_name' [COMMITBLOCK number_of_rows]
   [{OPTIMISTIC | PESSIMISTIC}]
Usage
This IMPORT statement imports data to a replica database from a data file created by the EXPORT SUBSCRIPTION statement. The IMPORT statement can only be issued on the replica database.
The file_name represents a literal value enclosed in single quotation marks. The import command can accept a single filename only. Therefore, all the data to be imported to a replica must be contained in one file.
The COMMITBLOCK option indicates the number of rows processed before the data is committed. The number_of_rows is an integer value used with the optional COMMITBLOCK clause to indicate the commitblock size. The use of the COMMITBLOCK option improves the performance of the import and releases the internal transaction resources frequently.
The optimal value for the COMMITBLOCK size varies depending on various resources at the server. A good example is a COMMITBLOCK size of 1000 for 10,000 rows. If you do not specify the COMMITBLOCK option, the IMPORT command uses all rows in the publication as one transaction. This might work well for a small number of rows, but is problematic for thousands and millions of rows.
You can define the import to use table-level pessimistic locking when it is initially executed. If the PESSIMISTIC mode is specified, all other concurrent access to the table affected is blocked until the import has completed. Otherwise, if the optimistic mode is used, the IMPORT may fail due to a concurrency conflict.
When a transaction acquires an exclusive lock to a table, the General.TableLockWaitTimeout parameter setting determines the transaction’s wait period until the exclusive or shared lock is released.
Imported data is not valid in a replica until it is refreshed once after the import. At the time a replica makes its first REFRESH, the bookmark used to export the file must exist in the master database. If it does not exist, the REFRESH statement fails. This means that you are required to create a new bookmark on the master database, re-export the data, and re-import the data on the replica.
The following rules apply when using the IMPORT statement:
▪Only one file per subscription is allowed for import.
▪The file size of an export file is dependent upon the underlying operating system. If a respective platform (such as SUN, or HP) allows more than 2 GB, you can write files greater than 2 GB. This means that a replica (recipient) should also have a compatible platform and file system. Otherwise, the replica is not able to accept the export file. If both the operating system on a master and replica support file sizes greater than 2 GB, export files greater than 2 GB are permitted.
▪Back up replica databases before using the IMPORT command. If a COMMITBLOCK option is used and fails, the imported data is only partially committed; you will need to restore the replica with a backup file.
▪To use the IMPORT statement, autocommit must be set OFF.
Return values
Error code
Description
25007
Master master_name not found.
25019
Database is not a replica database.
25069
Import file file_name open failure.
13XXX
Table level error
13124
User id num not found
This message is generated, for example, if the user has been dropped.
10006
Concurrency conflict (simultaneous other operation)
13047
No privilege for operation
13056
Insert not allowed for pseudo column
21XXX
Communication error
25024
Master not defined
25026
Not a valid master user
25031
Transaction is active, operation failed
25036
Publication publication_name not found or publication version mismatch
25040
User id user_id is not found
While executing a message reply an attempt to map a master user to a local replica id failed.
25041
Subscription to publication publication_name not found
25048
Publication publication_name request info not found
25054
Table table_name is not set for synchronization history
25056
Autocommit not allowed
25060
Column column_name does not exist on publication publication_name resultset in table table_name
Example
IMPORT 'FINANCE.EXP';
See also
solidDB® SQL statements