Reorg index gets SQL0270N reason code = 89

Technote (FAQ)


Question

When I try to reorg a single index why do I get SQL0270N reason code = 89?

Cause

The REORG INDEX is being run on partitioned indexes for a partitioned table

SQL0270N
Function not supported
Reason code 89
REORG INDEX is only supported for nonpartitioned indexes on partitioned tables.


Answer

As an example assume there is a reason to work with Tivoli Storage Manager archives. Looking at the reorgchk.out output it is seen that the Archive_Objects table has no formulas flagged as needing a reorg but the indices for this table show 1 of the 5 indices with the F5 formula flagged as needing a reorg based on the output found in the reorgchk.out:


Table: TSMDB1.ARCHIVE_OBJECTS
                            2.5e+08      0  3e+06  3e+06      - 8.68e+10   0  92  96
 --- <--- no reorg needed of the table, no formulas flagged
...
Table: TSMDB1.ARCHIVE_OBJECTS
Index: TSMDB1.IMARIDXEXPIRE_NDX
                           2.5e+08 3e+05  1940    4 6e+05 2.5e+08           37            37               1548                1548               60  90  50 199   0   0 -
**-- <---- F5 only flag to trigger a reorg with Tivoli Storage Manager
Index: TSMDB1.IMARIDXKEYS_NDX
                           2.5e+08 4e+05    30    4 2e+07 2.5e+08           72            72               1688                1688               56   6  77  34   6   0 *----
Index: TSMDB1.IMARNAME_NDX
                           2.5e+08 4e+05   242    4 3e+07 2.5e+08          103           103               1688                1688               67  13  76  17   9   0 *----
Index: TSMDB1.IMARPARENT_NDX
                           2.5e+08 43657   541    3 1e+06 7804515           54            54               3206                3206               62  91  65   1   0   1 -----
Index: TSMDB1.IMAR_NDX
                           2.5e+08 2e+05  2379    3 2e+06 2.5e+08            8             8               3938                3938                0  90  65   0   0   1 -----

 
As there is only 1 index flagged it would seem expedient to do an offline reorg of only this index by issuing the following commands from an OS prompt and logged in as the Tivoli Storage Manager instance user:


    cd /opt/tivoli/tsm/db2/bin
    set db2instance=server1 (assumes default server name - change if yours is different)
    db2 connect to TSMDB1
    db2 set schema TSMDB1
    db2 "reorg index IMARIDXEXPIRE_NDX for table ARCHIVE_OBJECTS"
The above reorg command is met with the SQL0270N error:
    $ db2 "reorg index  IMARIDXEXPIRE_NDX  for table  ARCHIVE_OBJECTS "
    SQL0270N  Function not supported (Reason code = "89").  SQLSTATE=42997

The solution is to reorg all the indices for the table as such:
    $ db2 "reorg indexes all for table ARCHIVE_OBJECTS "
    (a substantial amount of time may be needed to complete especially if the indices are large)
    DB20000I  The REORG command completed successfully.

Product Alias/Synonym

TSM ITSM ADSM

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Tivoli Storage Manager
Server

Software version:

6.1, 6.2, 6.3

Operating system(s):

Linux, UNIX, Windows

Software edition:

All Editions

Reference #:

1642830

Modified date:

2013-07-09

Translate my page

Machine Translation

Content navigation