BMC AMI Reorg requirements and restrictions

Authorization requirements

BMC AMI Reorg requires the following authorizations:

• REORG authority on the database
• SYSADM or DBADM authority
• CREATE authority on the database for pending DDL materializations

Status requirements

When running BMC AMI Reorg:

• Table spaces and indexes must be in read/write (RW) status.
• Table spaces and indexes cannot be in a restricted status other than:
  • Table space can be reorg pending (REORP) 
  • Table space can be started with access UT

(BMC.DB2.SPE2407)When running BMC AMI Reorg AUX YES:

• Table spaces can be in read/write (RW) status.
• Table space can be in one of the following restricted statuses:
  • Auxiliary CHECK pending (ACHKP)
  • Auxiliary warning (AUXW)

Storage requirements

BMC AMI Reorg requires 2 GB of auxiliary storage to reorganize partition-by-growth (PBG) table spaces, regardless of the storage actually used.

Restrictions

The following restrictions apply to BMC AMI Reorg:

• BMC AMI Reorg does not support
  
  • Indexes with over 512 million leaf pages, >2TB index
  • Multi-table, simple table spaces
    
  • Clone table spaces or table spaces with an instance qualifier of 2
  • Empty table spaces clustered by a nonpartitioned secondary index (NPI)
  • Indexes in RBDPM status
• BMC AMI Reorg terminates:
  
  • If a table is added to or dropped from the table space during a reorganization
  • If a table is mass deleted from a segmented table space during a reorganization

• BMC AMI Reorg supports partition-by-range (PBR) and partition-by-growth (PBG) table spaces with partition sizes (DSSIZE) greater than 64 GB except for the following PAGESIZE and DSSIZE combinations:

  PAGESIZE	DSSIZE
  4KB	128GB
  	256GB
  8KB	256GB

• BMC AMI Reorg supports PBG table spaces with any PAGESIZE and DSSIZE combination if each partition is not greater than 16 million pages.
• BMC AMI Reorg processing a PBR RPN table space ends with an error if you use the DDLIN keyword.
• (BMC.DB2.SPE2210) BMC AMI Reorg restricts pending column ALTER on indexed columns and when detected the control will be routed to DSNUTILB. 
• BMC AMI Reorg does not support the REORG_TS_NOPAD_DEFAULT system parameter. By default, the DISCARD process creates unpadded data.
• BMC AMI Reorg does not support DB2 Catalog tablespaces—any tablespace or index in database DSNDB01, DSNDB06, or DSNDB07.
• BMC AMI Reorg Index does not support Indexes in RBDPM status and puts the object on the SYSERROR queue.

(BMC.DB2.SPE2407)The following restrictions apply to BMC AMI Reorg AUX YES:

• It does not support:
  • DECFLOAT INDEXES, released in Db2 12 FL505
  • RBALRSN_CONVERSION and sends to DSNUTILB (job fails if DSNUTILB (NO) is set for BMC AMI Utilities)
• If a table has both LOB columns and XML columns, BMC AMI Reorg AUX YES will call DSNUTILB for processing.
• If the table space was altered to define an inline LOB column, BMC AMI Reorg AUX YES does not move data to the inline portion of the LOB.
• If the table space was altered to change limit keys, BMC AMI Reorg AUX YES does not materialize the altered limit keys during the reorganization.
• If the LOB AUX table spaces DSSIZE was altered, BMC AMI Reorg AUX YES does not materialize the pending DDL.

Objects or conditions that invoke DSNUTILB

The following objects or conditions invoke DSNUTILB:

• Multiple table, simple table spaces
• Column names exceeding 30 characters  
• Pending ALTER of a partition-by-growth (PBG) table space to a partition-by-range (PBR) table space
• UTS PBG table space with no clustering index or the clustering index was dropped.
• REBALANCE statement with pending ALTER statements except for ALTER limit key operations.
   If there are pending altered limit key operations, the rebalance statement is ignored.
• Move table
• Table space with a clustering NPI that is deferred
• Rotated partitions and an ALTER limit key operation or REBALANCE statement. After the table space is rotated, DSNUTILB is called to materialize the limit key changes.
• Table space with an XML column and an ALTER limit key operation or REBALANCE statement
• Alter add XML
• XML with discards
• Pending DDL with a pending recover. Db2 Catalog table SYSIBM.SYSPENDINGDDL column STATEMENT_TYPE = 'R'. 
• Cloned table space or Instance 2 table space
• Hashed tables (deprecated by IBM with Db2 12 FL504)
• Spatial index      
• Column-level CCSID
• Inserted partition
• Pending ALTER to index compression          
• Index with a DECFLOAT column
• Index in rebuild-pending status, RBDP, <SPE2501> RBDPM or PSRBD.
• UTS PBR RPN table space if any of the following conditions exist:
  • The underlying object is an XML table space or any of its indexes.
  • A pending DSSIZE change exists on the object.
  • Limit keys changed through ALTER limit key or REBALANCE.
• (BMC.DB2.SPE2210)  Table spaces that contain a mixture of fixed length and Huffman compression across the partitions.

The following tables with LOB columns require DSNUTILB:

• Multiple table table space with LOB columns
  • BMC AMI Reorg AUX YES passes to DSNUTILB. 
  • Standard reorganization processes the base and passes the AUX TSs to DSNUTILB

• RBA/LRSN conversion
• XML
• Pending ALTER statements
• Pending Reorg 
• Deferred LOB table space