Data movement utility in db2

Question

Why the not logged initially option during the data movement is faster than using the LOAD utility in DB2?

Not logged initially Method:

db2 alter table tablename activate not logged initially

db2 insert into table tablename select * from tbname

Load Utility:

db2 declare source cursor for select * from tablename

db2 load from source of cursor insert into tablename nonrecoverable

Please provide additional details, especially the volume of data you are loading. — Ian Bjorhovde, Jun 24 '14 at 09:34
There are around 50+ tables in the database of size 52TB (DB size). The total time taken by load query is almost double the time taken by "Not logged Initially" method. — Tom123456, Jun 24 '14 at 09:39

score 1 · Accepted Answer · answered Jun 24 '14 at 16:01

Based on your database size and performance question I am going to assume that you are using the Database Partitioning Feature (DPF) with DB2.

When you perform INSERT INTO ... SELECT, this occurs in parallel on all database partitions – each partition is working independently. With logging turned off, this will be quite fast (albeit dangerous – if there is a problem, the not-logged-initially table will have to be dropped and recreated).

When you use LOAD FROM CURSOR, all of the database partitions execute the SELECT statement and return the rows to the coordinator partition, which then feeds them into the LOAD utility. The LOAD utility then performs hash partitioning to send the data back to all of the database partitions again. As you can imagine, with a large volume of data, shipping all of this data back and forth can be quite inefficient.

Thanks Lan for your response! Kindly let me know which could be the favourable way to make replica of tables. We need to do this activity every month. — Tom123456, Jun 25 '14 at 09:11
Either method is OK, which you choose should be a function of your application's specific requirements. — Ian Bjorhovde, Jun 25 '14 at 18:06

Data movement utility in db2

1 Answers1