DB2 Alter Column Type – Understanding RBDP vs. ‘Needs a Rebuild’ States

alter-tabledb2db2-luwindex

I'm new to DB2 and databases overall.

I'm building a script to alter several column data types in several tables of which some are part of indexes and some not. So after ALTER-statements I assumed I have to rebuild the indexes. According this source:
"When the data type of the ACCTID column is altered from DECIMAL(4,0) to INTEGER, the IX1 index is placed in a REBUILD-pending (RBDP) state."

My questions:
1. If index is in RBDP state, does it mean it has to be rebuild and how should I return the index to accessible state?
2. How can I find the indexes which are now in RBDP state? I tried this after a altering a column type:

SELECT tabname, indname, index_requires_rebuild
FROM TABLE(sysproc.admin_get_index_info('I','','')) AS t
WHERE index_requires_rebuild = 'Y';

but I still can't find any indexes requiring a rebuild, so I guess RBDP state and "requires rebuild" are different animals. So what is the difference and how can I be certain the index is still working as it should be after my ALTER statements?

The statements I use are in this form:

ALTER TABLE TABLE1 ALTER COLUMN COLUMN1 SET DATA TYPE BIGINT 
GO
CALL SYSPROC.ADMIN_CMD('REORG TABLE TABLE1')

Best Answer

The issue here is that you're reading documentation for Db2 on z/OS, but you're running tests on Db2 for Linux, UNIX & Windows.

When you perform this particular alter on Db2 for Linux, UNIX and Windows, it will succeed, but the table will be placed immediately into reorg pending state, which you can see in SYSIBMADM.ADMINTABINFO (see the REORG_PENDING column). INDEXES_REQUIRE_REBUILD will still show as 'N', even though performing a REORG on the table will rebuild the index.

Related Question