Db2load import for big tables

[Former Member]

Hi Team,

We are trying to export import some of the big tables into new tablespaces.

Export has been unsorted standard LRG TPL file.

While doing import we are using the below load args.

Running only one job at a time for the splitted table since with db2load i dont think so parallel importing is possible.

-stop_on_error -loadprocedure fast LOAD_FORCED:COMPRESS_STATIC_ALL -nolog -c 200000

There would be about close 70 TOC files each containing close to 25 million records. So team question is that what would be a commit count.

What is the logic behind a higher commit count or a lower commit count. Any reasoning would like to know.

[Frank-Martin]

Hi,

since DB2 LOAD is not SQL based the commit count is irrelevant. LOAD does not log changes in data or index pages. Only allocation of extents is logged. So the required log volume is not large even for large tables. Also the option -nolog has no influence on LOAD since LOAD is not logged anyways.

Any reason why you do not use COMPRESS_ADAPTIVE_ALL ? This will save more space.

With the R3load options above you may not get a good compression disctionary for static compression since it will be based on the first LOADed rows. Those rows may or may not be a good sample of the table data. If you would like to optimize your static compression dictionary, you may use:

-loadprocedure fast LOAD_FORCED:OPT_COMPRESS_STATIC_ALL

This will increase the runtime of R3load since R3load will scan the data twice but may give you a much better static compression.

You are correct in saying that only one LOAD operation is allowed at a time. If you want to optimize the runtime of a LOAD of a splitted table export, you may want to look at the SPLITTED_LOAD option. However this will require some extra care . E.g. all table parts need to loaded before the first index is created.

Regards

                 Frank

[Former Member]

Frank,

Can you please check my other thread about SPLITTED_LOAD option please.

[Former Member]

Hi Frank,

Guten Tag!

Thanks a ton for the response.

I just mentioned the commit count just to be on safe side , one question while using db2load we dont have any commit count? How does db2 derive this value?

-nolog also can be removed true, just to be on safe side kept in case it logs didnt want to fill up the log space.

We are using static compression everywhere in our landscapes so went with the same compression algorithm .

Yes true, the compression dictionary would be built on first loaded rows. In order to overcome the problem we are doing as below.

alter table <schema.table> compress yes static

REORG TABLE <schema.table> USE <TEMP-Tablespace> RESETDICTIONARY

RUNSTATS ON TABLE  <schema.table> WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL

For us R3load import timing is more important , so would avoid the dual scanning of the data.

Yes true SPLITTED_LOAD, have started another thread for the same.

Would ask my queries regarding SPLITTED_LOAD in other thread.

[Former Member]

Frank,

Is there a way to improve the loading speed  using DB2load. Is it even possible?

What would be the performance improvement measures would you suggest?

[Former Member]

Frank,

What is the significance of this note :

916407
- DB6: "R3load -nolocktable" option

[Frank-Martin]

Don't worry about this note. It's completely outdated. All supported kernel version do not have this problem any more.

Regards

                Frank

[Former Member]

Frank,

Guten Tag!

There is one thing which i have observed a lot is that during the index rebuild phase. It just takes almost equal to the import time.

Export : unsorted export

Import : splitted load import

Index creation phase is taking almost 12 hours.

I dont know how to speed it up.

Do you think sorted export will help?

I dont know which side take, unsorted helps in shorter export time with a trade off. What do you think is the best way forward?