0

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')
2

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.

  • Hi, thanks you very much for the response. Are you saying that performing the reorg (which I already do) is a sufficient procedure to return the index in working state? – Julius Torkkeli Dec 5 '17 at 6:17
  • @JuliusTorkkeli Yes. Issuing a REORG on the table will rebuild the indexes as part of the operation. – Ian Bjorhovde Dec 5 '17 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.