One way to copy data from an external file to a database is by using DB2's import utility; the import utility is desgined to read data directly from an external file (provided the file is written in a format that is supported by DB2) and insert it in a specific table or updatable view.
IMPORT FROM [filename] OF [DEL|ASC|IXF|WSF]
COMMITCOUNT [commitcount] | COMMITCOUNT AUTOMATIC
[CREATE|INSERT|INSERT_UPDATE|REPLACE|REPLACE_CREATE] INTO [Tablename] [Columname...]
COMMITCOUNT - identifies the number of rows of data that are to be copied to the table or updatable view specified before a commit operation is to be performed (COMMITCOUNT AUTOMATIC should be used instead for import operations that fail because transaction logs become full)
TableName - identifies the name assigned to the table or updatable view to which data is to be imported (this cannot be the name of a system catalog table or view)
ColumName - identifies one or more specific columns to which data is to be imported
IMPORT FROM dept.ixf OF IXF INSERT INTO department;
- insert all the data values i.e. append to the existing data or insert new data
IMPORT FROM emp.del OF DEL REPLACE INTO employee;
- delete the existing data and insert the new data
IMPORT FROM dept.ixf OF IXF CREATE INTO test IN tbs1;
- target table 'test' is created in tablespace tbs1 along with all the associated indexes and then data is imported into the new table. This option allows to control in what tablespace the new table should be created
IMPORT FROM emp.del OF DEL COMMITCOUNT 1000 INSERT_UPDATE INTO employee;
- data is either inserted into the target table or used to update exisitng rows, exisitng records will be updated only if the target table specified has a primary key enabled. During import operation, every 1000 rows are committed to avoid any transaction log full error.
- any exisitng data is deleted from the target table if it already exists; then the new data is inserted. If the target table does not exist, it is created along with all its associated indexes and then data is imported into new table