Differences between DB2 import and load

Both Import and Load can Import data to the DB2 server, but the two are very different. Import actually executes the SQL INSERT operation. Like the INSERT operation, triggers are activated when the Import operation is executed. All constraints are enforced and the database buffer pool is used. Similar to the conventional path import of Oracle SQL * loader tool. The Load tool can import data files to data tables more quickly. The Load tool does not interact with the DB2 Data Engine. Therefore, when the Load tool is used, the trigger is not triggered or the buffer pool is used, and the data table constraints must be implemented independently. The Import tool is slower than Load because it is a low-level data operation tool. It processes data pages on the hard disk in three stages: LOAD, BUILD, and DELETE. The Load tool is similar to the direct path import of the Oracle SQL * loader tool. The following example illustrates this point: db2 => connect @ Database Connection Information Database server = DB2/LINUX 9.7.0 SQL authorization ID = HUATENG Local database alias = DBTESTdb2 => create table test (id int not null primary key, name varchar (20) @ DB20000I The SQL command completed successfully. db2 => commit @ DB20000I The SQL command completed successfully. db2 => create trigger tri_test db2 (cont .) => no cascadedb 2 (cont .) => before insert on testdb2 (cont .) => referencing new as ndb2 (cont .) => for each rowdb2 (cont .) => begin atomicdb2 (cont .) => set n. name = n. name | '[import test]'; db2 (cont .) => end @ DB20000I The SQL command completed successfully. db2 => commit @ DB20000I The SQL command completed successfully. db2 => insert into test values (1, 'A') @ DB20000I The SQL command completed successfully. db2 => c Ommit @ www.2cto.com DB20000I The SQL command completed successfully. db2 => select * from test @ ID NAME ----------- ---------------- 1 a [import test] 1 record (s) selected. db2 =>! Cat test.txt @ 1, "aa" 2, "bb" 3, "cc" 4, "dd" 5, "ee" 6, "ff" db2 => import from test.txt of del insert into test @ sql3366n The utility is beginning to load data from file "test.txt ". SQL3148W A row from the input file was not inserted into the table. SQLCODE "-803" was returned. SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not val Id because the primary key, unique constraint or unique index identified by "1" constrains table "HUATENG. TEST "from having duplicate values for the index key. SQLSTATE = 23505SQL3185W The previous error occurred while processing data from row "1" of the input file. SQL3110N The utility has completed processing. "6" rows were read from the input file. SQL3221W... begin commit work. input Record Count = "6 ". SQL3222W... COMMIT of any database changes was successful. SQL3149N "6" rows were processed from the input file. "5" rows were successfully inserted into the table. "1" rows were rejected. number of rows read = 6 Number of rows skipped = 0 Number of rows inserted = 5 Number of rows updated = 0 Number of rows rejected = 1 Number of rows committed = 6db2 => select * from test @ id name ------------- ------------------ 1 a [import test] 2 bb [import test] 3 cc [import test] 4 dd [import test] 5 ee [import test] 6 ff [import test] 6 record (s) selected. we can see that record 1 in the file is denied due to primary key conflict. The log file displays Number of rows rejected = 1. Other imported records also trigger the trigger operation. Next let's take a look at The Load tool: db2 => delete from test where id> 1 @ DB20000I The SQL command completed successfully. db2 => commit @ www.2cto.com DB20000I The SQL command completed successfully. db2 => select * from test @ id name ----------- ------------------ 1 a [import test] 1 record (s) selected. first, delete the imported records and keep only the records with ID = 1. Db2 =>! Cat test.txt db2 (cont .) => @ 1, "aa" 2, "bb" 3, "cc" 4, "dd" 5, "ee" 6, "ff" db2 => load from test.txt of del insert into test @ SQL3501W The table space (s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database. sql32.16n The utility is beginning to load data from file "/home/huateng/test.txt ". SQL3500W The utility is beginning the "LOAD" phase Time "06:41:44. 784072 ". SQL3519W Begin Load Consistency Point. input record count = "0 ". SQL3520W Load Consistency Point was successful. SQL3110N The utility has completed processing. "6" rows were read from the input file. SQL3519W Begin Load Consistency Point. input record count = "6 ". SQL3520W Load Consistency Point was successful. SQL3515W The utility has finished the "LOAD" phase at tim E "06:41:44. 808464 ". SQL3500W The utility is beginning the "BUILD" phase at time "06:41:44. 809206 ". SQL3213I The indexing mode is "REBUILD ". SQL3515W The utility has finished the "BUILD" phase at time "06:41:44. 914248 ". www.2cto.com SQL3500W The utility is beginning the "DELETE" phase at time "06:41:44. 952664 ". SQL3509W The utility has deleted "1" rows from The table. SQL3515W The utility has finished the "DELETE" phase at time "06:41:44. 979506 ". number of rows read = 6 Number of rows skipped = 0 Number of rows loaded = 6 Number of rows rejected = 0 Number of rows deleted = 1 Number of rows committed = 6db2 => select * from test @ id name ----------- ------------------ 1 a [import test] 2 bb 3 cc 4 dd 5 ee 6 ff 6 record (s) selected. obviously, the import result does not trigger The sender operation does not reject any rows. On the contrary, a record is deleted because Load will import all matching records to the table, duplicate record rows will be deleted in the DELETE Phase of load. Author: TOMSYAN