with Tags:
index
X
X
In DB2LUW Why index rebuild might need more resources.
Though most of the expert DBAs know about this I wanted to highlight a common question I have faced time to time. When index rebuild runs it might take large amount of resources. By index rebuild it means the system is rebuilding the indexes on it's own for some reason. It could be as part of making indexes invalid using a db2dart option to fix index corruption, it... [More]
Tags: index db2 rebuild dbluw ibm-blog |
How to achieve better CLUSTERRATIO and CLUSTERFACTOR
CLUSTERRATIO information indicates the degree to which the table data is clustered in relation to this index. The higher the number, the better the rows are ordered in index key sequence. So it is related to How the Table Data is Cluster in relation ship with Index. Reorg table is main operation to archive same, But we need to give Index name to Reorg command to cluster based upon that. Refer: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005305.html If index clustering statistics are not... [More]
Tags: index clustering clusterratio clusterfactor |
Schema considerations for performance with DELETE queries
When using DELETE queries, especially searched deletes which may delete many rows at a time, there are index considerations which can be important to getting good performance, if there are foreign key constraints present. These are helpful to be aware of in advance, since the absence of the right indexes may result in suboptimal delete performance, and the cause may not be immediately evident from looking at the plan graph in a explain (db2exfmt output). A typical scenario of this kind might... [More]
Tags: db2 index query foreign delete performance key |
Problem of running index reorg immediately after table reorg
During the reorg we are going through a list of tables that need to be reorged one by one. For each table we are doing the table reorg first : db2 " reorg table $MYUSER.${i} inplace" (where $MYUSER.${i} is each of the table name) Then doing the index rerog : db2 " reorg indexes all for table $MYUSER.${i} allow write access" This might face problem. Even if we add the tablealt lock under the special register ("CURRENT LOCK TIMEOUT") it still might not work correctly. The reason being, Inplace... [More]
Tags: db2 reorg commit table luw index |
SYSCOLDIST table is marked as invalid. Rebuild index getting error - 901 due to duplicate values
Some older DB2 levels experienced this problem. If the database cannot be restored using an earlier good backup then following way might help, The SYSCOLDIST table contains the distribution statistics. So, first a fresh runstats for all objects could be run with distribution option and checked if that can refresh that table. It might fail due to the presence of invalid index. In that case, if it's o.k. to get rid of all the current distribution... [More]
Tags: index -901 db2 invalid syscoldist db2dart |
