stored procedure for dropping / building index on table

[blom0344]

Hello,

We use Powermart to write aggregated output from our ERP system to datawarehouse on DB2 (7.1) Every month I insert a new recordset into a large table, but writing performance is very slow. The table has a unique key consisting of 5 fields. I thought of improving performance by first dropping the index on the table , doing the insert, and then rebuilding the index. This should be possible by using stored procedures in DB2 (Powermart has pre and post-session triggers to invoke the procedures).
Has anyone examples of such procedures / alternatives or a hint on documentation?

blom0344

 

[gregsimpson]

Some of the functionality you mention such as dropping and re-creating indexes can be done using the LOAD utility with DB2.

The target table for the load must exist. The target table may be a new table or an existing table to which data will be appended or replaced. Indexes on the table may or may not already exist. The load process does not create new indexes - it only builds indexes that are defined on the table.

The LOAD utility will insert data into a table much faster than say the IMPORT utility, because instead of inserting one row at a time, the LOAD utility will use the rows read from the input file to build pages which are then written directly to the database. Exisiting primary keys or unique indexes may be rebuilt after the data pages are inserted(rebuilt either totally or partially based on newly added data), and finally all the duplicate rows that do not comply with unique or primary key constraints are deleted from the table. During the load operation, individual records loaded are not logged in the log files - a major saving.

#The biggest disadvantage of the LOAD utility is that because the changes in the data are not logged, it cannot be rolled forward by using the log files. However the LOAD utility can copy the source data, so it can be reloaded if required.

There are all sorts of options such as when to build the indexes

Rebuild - Forces the indees to be rebuilt regardless

Incremental - Use data in existing indexes and add new, when the load is invoked.

Deferred - LOAD won't build indexes, they will be flagged as needing a refresh. They will then be built when either the base table is accessed or the database is restarted.

Autoselect - will allow the LOAD utility to decide when to build the indexes.

You can also do things like control freespace etc.

Other Performance related options you may be interested in as regards the LOAD utility are

COPY YES/NO
FASTPARSE
ANYORDER
NONRECOVERABLE
DATA BUFFER
DISK PARALLELISM
CPU_PARALLELISM