PK55780: CREATE INDEX W/VARBINARY DSNURBXB+464E 00E40325

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Receiving problem with an index with VARBINARY DESC key.
  Create a table with a single VARBINARY(10) column. Insert
  two rows:  BX'C1' and BX'C1C2'. Now create an index using
  the VARBINARY column as a DESC key. The index create fails
  with S04E-00E40325.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 for z/OS V9 subsystems that     *
  *                 have indexes on VARBINARY columns or         *
  *                 columns with a distinct type that is         *
  *                 based on a VARBINARY data type, and          *
  *                 the columns are in descending order          *
  *                 in the indexes.                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:  1.ABEND04E DSNURBXB+464E RC00E40325    *
  *                         may occur when an index is created   *
  *                         on a populated table, and a column   *
  *                         in the index is defined as           *
  *                         descending and has the VARBINARY     *
  *                         data type or a distinct type that    *
  *                         is based on a VARBINARY data type.   *
  *                                                              *
  *                      2.Insert operation or LOAD utility      *
  *                        may encounter various ABENDs in       *
  *                        modules such as DSNKINSL or DSNKISPL  *
  *                        if the table being operated on has    *
  *                        an index which is created on a        *
  *                        VARBINARY column or a column with     *
  *                        a distinct type that is based on      *
  *                        a VARBINARY data type and the index   *
  *                        key ordering on this column is        *
  *                        descending.                           *
  *                                                              *
  *                      3.An SQL query can return incorrect     *
  *                        output if it accesses an index        *
  *                        defined on a VARBINARY column or      *
  *                        a column with a distinct type         *
  *                        that is based on a VARBINARY data     *
  *                        type and the index key ordering       *
  *                        on this column is descending.         *
  ****************************************************************
  * RECOMMENDATION: 1>Drop the affected indexes, or alter        *
  *                   the column data type to BINARY, then       *
  *                   rebuild the indexes.                       *
  *                                                              *
  *                 2>Apply the PTF for this APAR to block       *
  *                   creation of new indexes with columns       *
  *                   that are defined as descending and         *
  *                   have the VARBINARY data type or a          *
  *                   distinct type that is based on a           *
  *                   VARBINARY data type.                       *
  ****************************************************************
  Depending on the data values stored in a VARBINARY column,index
  key values stored on index pages could be out of order if
  the index is defined on a VARBINARY column or a column with
  a distinct type that is based on a VARBINARY data type and
  with DESC attribute specified.
  
  This could cause ABEND04E DSNURBXB+464E RC00E40325 being
  issued when creating such an index on a table with existing
  data in it.
  
  When such an index is defined on a table,INSERT statements
  and LOAD utilities operating on the table could run into
  various ABENDs in modules such as DSNKINSL and DSNKISPL.
  
  SQL queries accessing such an index could return incorrect
  results.
  
  Additional Keywords:
  SQLINCORR INCORROUT SQLCODE904 SQLCODE350 SQLCODE20180
  

Problem conclusion

• Due to current design limitations of VARBINARY data type
  descending-order indexing, this APAR disallows creation
  of indexes with columns that are defined as descending,
  and have the VARBINARY data type, or a distinct type that
  is based on a VARBINARY data type.
  
  This APAR introduces some incompatibility with any existing
  indexes defined on a VARBINARY column or a column with a
  distinct type that is based on a VARBINARY data type in
  descending order.Access to such indexes will be
  disallowed after applying this fix.
  
  Before applying the PTF, user should drop such indexes
  or alter the column data type to BINARY, then rebuild
  the indexes.
  
  One suggested way for user to locate all the indexes
  created on VARBINARY column or a column with a
  distinct type that is based on a VARBINARY data type
  in descending order is to query the catalog.
  
  Following query may be used for this purpose.
  
  SELECT A.NAME AS INDEX_NAME,
  A.CREATOR AS INDEX_CREATOR
  FROM SYSIBM.SYSINDEXES A,
  SYSIBM.SYSKEYS B,
  SYSIBM.SYSCOLUMNS C
  WHERE B.ORDERING = 'D' AND
  B.IXNAME = A.NAME AND
  B.IXCREATOR = A.CREATOR AND
  A.TBNAME = C.TBNAME AND
  A.TBCREATOR = C.TBCREATOR AND
  C.COLNO = B.COLNO AND
  (C.DATATYPEID = 908 OR
   C.SOURCETYPEID = 908 );
  
  Please note that dropping the index or altering the data type
  of its column will invalidate application plans and packages
  that use the index and may affect dynamic SQL.
  
  This APAR/PTF adds a new DB2 reason code 00C900B2.
  This change will be documented in the DB2 Codes manual.
  
  The text for reason code 00C900B2 is:
  
  | 00C900B2
  |
  | Explanation : Access to the index is disallowed if index
  | is defined with the DESC attribute on a VARBINARY column
  | or a column with a distinct type that is based on a
  | VARBINARY data type. Drop the index or alter the column
  | data type to BINARY, then rebuild the index.
  |
  | System action: The operation is not allowed.
  |
  | User response: Drop the index or alter the column data
  | type then rebuild the index.
  
  After applying the PTF, following situations could happen
  if a user has a pre-existing index defined on a VARBINARY
  column or a column with a distinct type that is based on
  a VARBINARY data type and the column is in descending
  order in the index:
  
  1.Queries accessing such an index will fail. SQL code -904
    with reason code 00c900b2 will be issued.
  
  2.INSERT,DELETE,UPDATE statements will fail with SQL
    code -904 and reason code 00c900b2 if the table has such
    an index created on it.
  
  3.LOAD and REORG TABLESPACE utilities will fail if they
    operate on a table which has such an index created on it.
    In the utility job output, DSNT500I resource unavailable
    message will be issued with reason code 00C900B2.
  
  4.REBUILD INDEX, REORG INDEX and CHECK INDEX utilities will
    fail if they operate on such an index. In the utility job
    output,DSNT500I resource unavailable message will be
    issued with reason code 00C900B2.
  
  If above situation happens, user should drop the index or
  alter the column data type to BINARY,then rebuild the index,
  before retry the failed operation.
  
  Following DDL changes have been made in this APAR to block
  creation of new indexes with columns that are defined as
  descending and have the VARBINARY data type or a distinct
  type that is based on a VARBINARY data type.
  
  1.CREATE INDEX on a VARBINARY column or a column with a
    distinct type that is based on a VARBINARY data type
    in descending order is not allowed. Error SQL code -350 ,
    SQLSTATE 42962 is issued in this case.
  
  2.ALTER INDEX ADD COLUMN is not allowed if the column being
    added is a VARBINARY column or a column with a distinct
    type that is based on a VARBINARY data type and the index
    key ordering on this column is descending. Error SQL code
    -350 , SQLSTATE 42962 is issued in this case.
  
  3.When a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA column
    or BINARY column is converted to VARBINARY data type,
    if there is an index created on the column in descending
    order, the ALTER is not allowed. Error SQL code -20180,
    SQLSTATE: 428FR is issued in this case.
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK34140

Modules/Macros

• DSNDIXD  DSNDRC   DSNKIXDB DSNKLOD2 DSNKTRAV
  DSNXIALC DSNXIAX2 DSNXIIKY
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK34140

     UP08/03/15 P F803

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.