what is a better option load with indexes created or else post load index creation

[Former Member]

Hi All,

We have two options while loading data using r3load where indexes can be created before or after the load has finished.

From the past experiences what is a good approach to take?

[Frank-Martin]

Hi,

I assume you are using DB2 LOAD to load the data ...

For a log time it has been faster to create the indexes before starting DB2 LOAD. In this case LOAD builds the indexes in the build pahase of LOAD. However CREATE INDEX has been improoved in DB2.  It now automatically uses intra parallelism even if INTRA_PARALLEL is switched of. Now CREATE INDEX after DB2 LOAD tends to be somewhat faster than building the indexes using LOAD.

So I recommend to use AFTER_LOAD. We even switched the R3loadctl default in SAP kernel 7.45.

Regards

               Frank

[Former Member]

Hi Frank,

Thank you so much for the information.

I will try with creation of indexes post import.

One question i know its a very tough one answer with many factors involved. But from your experience how much of a split size should be when we split the tables.

Should we go with 10 GB split size or else a lower value for a 300 GB table. Generally what has been your experience.

[Former Member]

Hi Frank,

I have a question, during the splitted option index creation phase is taking up log space. Is there a way to avoid that during index creation phase.

[maria_shen]

Hello,

Could you please firstly check the setting of database parameter "logindexbuild"? Is it OFF or ON?

Kind regards
Maria

[former_member264034]

Hi,

1058437 - DB6: R3load options for compact installation

"If the indexes of the table were created before the loading process and you use INSERT to load in parallel, the indexes may be fragmented (a small number of sequential pages). Under DB2 V9.7 and lower, these indexes may cause slow runtimes for SQL statements or RUNSTATS. Reorganize these indexes after loading, or create the indexes after loading."

As a default indexes are created before load by R3load for DB2 LUW. Experience shows that this is faster since in most cases LOAD builds the indexes. If you create large indexes after the data load the INDEX creation may hold the first active log file for a long time and therefore increase the required log volume.

If you want to use splitted tables with the LOAD procedure.

it also makes sense to create the INDEXES before the LOAD begins. I think you

have to specify this within your TSK-Files.

Starting with SAP releases using kernel Version 7.00, the default for indexes

creation is BEFORE_LOAD.

Regards,
Aidan