A fix is available
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 number
PK55780
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
910
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2007-10-31
Closed date
2008-02-28
Last modified date
2009-06-02
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 June 2009