IBM Support

SYSCOLDIST table is marked as invalid. Rebuild index getting error - 901 due to duplicate values

Technical Blog Post


Abstract

SYSCOLDIST table is marked as invalid. Rebuild index getting error - 901 due to duplicate values

Body

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 statistics then following could be done,


db2dart <db-name>  /IP

Above command will  ask for,

Table ID or name, tablespace ID, first page, num of pages

Following could be  entered,
<table-id-for-SYSCOLDIST>, 0, 0, 9999999, <service-password>

 

The table id for SYSCOLDIST  table could be obtained from system catalog.
 

For the  service-password  IBM support needs to be involved.
 

After  making  the SYSCOLDIST  table blank,  the index  rebuild should work normally.

 

Later  a  full runstats  for all the tables will help  to  populate the table back.

 

If  this way of fixing the issue don't help then  the  IBM DB2 support  needs to be involved.

 

If  it's not possible to  forget all the existing statistics  and only the specific records complaining with duplicate values is to be cleaned up  then a more involved steps needed and for  that  IBM  DB2 Support  needs to be involved.

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140820