Db/2 – SQL Error [42997] :- Function not supported (Reason code = “89”)

Background

Playing around with DB/2 LUW and ran into an error that deserves a bit of thought.

Error

Error – Image

42990_20180924_0909AM.PNG

Error – Text


SQL Error [42997]: Function not supported (Reason code = "89").. SQLCODE=-270, SQLSTATE=42997, DRIVER=4.21.29 

Reproduction

Here is the SQL statement that triggered the error


CALL SYSPROC.ADMIN_CMD('REORG INDEX "Fact"."INDX_UNIQUE_SALELARGE_INVOICEDATEKEYYEAR_PROFIT_SALEKEY" FOR TABLE "Fact"."SaleLarge<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>"     ') 

Troubleshooting

The basis for the error is that Index re-org can not be targeted against specific Indexes for un-partitioned tables.

Re-Org

Table – Reorg all Indexes

SQL

SQL – Syntax

CALL SYSPROC.ADMIN_CMD('REORG INDEXES ALL FOR TABLE "table-schema"."table"     ')

SQL – Sample

CALL SYSPROC.ADMIN_CMD('REORG INDEXES ALL FOR TABLE "Fact"."SaleLargeRowStoreClustered"     ')

Runstats

Runstats – Specific Index

SQL – Syntax


CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE "table-schema"."table" FOR INDEX "index-schema"."index-name" ') 

SQL – Sample


CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE "Fact"."SaleLarge" FOR INDEX "Fact"."INDX_UNIQUE_SALELARGE_INVOICEDATEKEYYEAR_PROFIT_SALEKEY" ') 

Runstats – All Indexes

SQL – Syntax


CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE "table-schema"."table" ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND INDEXES ALL<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>') 

SQL – Sample


CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE "Fact"."SaleLarge" ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND INDEXES ALL')

Metadata

Is table/index partitioned?

Table

  1. sysproc.admin_get_tab_info_v95
    • Partition
      • If not partitioned, Single Row for the table
      • If partitioned, Multiple Rows
    • Identifiers
      • Columns
        • DATA_PARTITION_ID
          • If not partitioned
            • DATA_PARTITION_ID = 0
          • If partitioned
            • Multiple entries for each partition
  2. syscat.datapartitionexpression
    • Column
      • datapartitionexpression
        • Partition Column / Expression
  3.  syscat.datapartitions
    • Granularity
      • Data Granularity, not Index
    • Table Space ID
      • TBSPACEID
        • Table Space ID – Data
      • INDEX_TBSPACEID
        • Tablespace ID – Index
      • Partition Info
        • LOWVALUE
        • HIGHVALUE
      • Pages used
        • NPAGES
        • FPAGES

Index

  1. sysproc
    • sysproc.admin_get_index_info
      • Partition
        • If not partitioned, Single Row for the table
        • If partitioned, Multiple Rows
      • Identifiers
        • Columns
          • DATA_PARTITION_ID
            • If not partitioned
              • DATA_PARTITION_ID = 0
            • If partitioned
              • Multiple entries for each partition
  2. syscat
    • syscat.indexpartitions
      • Added in version v 9.7
      • Only has records for partitioned Indexes
      • Columns
        • IID
          • Index ID
        •  INDPARTITIONTBSPACEID
          • Table Space
        • DATAPARTITIONID
          • Data Partition
        • Nleaf
          • Number of leaves
        • NumRIDS
          • Number of Records

Summary

In closing , here are some quick points:

  1. Re-org
    • To be able to run targeted re-org against specific indexes, it does not matter whether the Index is itself partitioned.
    • The lone requirement is for the table to be partitioned
  2. Naming Constructs
    • INDEX SCHEMA
      • Please keep in mind that unlike some other databases i.e. Microsoft SQL Server, INDEX SCHEMA is based on the person who created the Index;  Unless specific otherwise

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s