PK22292: ALTER INDEX XXX PIECESIZE 64G(INVALID PIECESIZE SPECIFIED) GET SQLCODE0 BUT SHOULD BE SQLCODE644

A fix is available

 

APAR status

  • Closed as program error.

Error description

  • Alter index xxx PIECESIZE 64G(Invalid PIECESIZE specified)
    under non-64G Tablespace,get sqlcode0 and catalog didn't be
    updated, should get sqlcode644

Local fix

  • N/A

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 V7 and V8 users *
    ****************************************************************
    * PROBLEM DESCRIPTION: Invalid PIECESIZE specified on ALTER *
    * INDEX statement, SQLCODE 0 was issued. *
    ****************************************************************
    * RECOMMENDATION: *
    ****************************************************************
    An invalid PIECESIZE was specified when running ALTER
    INDEX in V7. The catalog table SYSIBM.SYSINDEXES was not
    updated, but SQLCODE 0 was returned.

    For both V7 and V8, if other options were specified in the
    same alter statement, the error process was not handled
    correctly and may leave partially updated values
    in the catalog which may cause unpredictable results.

Problem conclusion

  • The problems are fixed. SQLCODE -644 is properly issued in V7.
    The error handling is correctly processed in V7 and V8.

    Applying this fix only prevents new occurrences. It can not
    fix the existing problems. For the existing ones, user may
    have to drop and recreate the indexes.

    Some cases we know can be identified by using the following
    queries.

    Case 1:

    SYSINDEXES.COLCOUNT is not the same as the number of
    rows in SYSKEYS.

    For example:

    An alter statement containing the options other than
    PIECESIZE inserts a record in SYSKEYS, but does not
    increment the column's count in SYSINDEXES.

    ALTER INDEX index1
    PIECESIZE mG
    ADD COLUMN (new_col);

    To identify the problem use the following query.
    If SYSINDEXES.COLCOUNT is not the same as the number
    of rows in SYSKEYS, there is a problem.

    SELECT IXS.CREATOR, IXS.NAME, IXS.COLCOUNT,
    KEY.COLSEQ, KEY.COLNAME
    FROM SYSIBM.SYSINDEXES AS IXS,
    SYSIBM.SYSKEYS AS KEY
    WHERE IXS.CREATOR = KEY.IXCREATOR
    AND IXS.NAME = KEY.IXNAME
    AND IXS.COLCOUNT <> ( SELECT COUNT(*)
    FROM SYSIBM.SYSKEYS AS KEY1
    WHERE KEY1.IXCREATOR = IXS.CREATOR
    AND KEY1.IXNAME = IXS.NAME
    )
    ORDER BY IXS.CREATOR, IXS.NAME, KEY.COLSEQ;

    Case 2:

    The first version is generated for the index, a
    SYSOBDS record may be inserted for the index, but
    SYSINDEXES.VERSION remains 0.

    For example:

    Use the same SQL alter statement in case 1 to generate the
    first version for the index. Use the following query to
    identify the problem.

    SELECT IXS.CREATOR, IXS.NAME, IXS.VERSION,
    OBD.OBDTYPE, OBD.VERSION
    FROM SYSIBM.SYSINDEXES AS IXS,
    SYSIBM.SYSOBDS AS OBD
    WHERE IXS.CREATOR = OBD.CREATOR
    AND IXS.NAME = OBD.NAME
    AND OBD.OBDTYPE = 'F'
    AND IXS.VERSION = 0;

    Some cases may not be identified here.

    ADDITIONAL KEYWORDS:

    SQLALTER
    SQLCODE644
    SQLCODE0

Temporary fix

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

Comments

APAR Information

  • APAR number

    PK22292

  • Reported component name

    5740 IBM DATABA

  • Reported component ID

    5740XYR00

  • Reported release

    710

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2006-03-28

  • Closed date

    2006-06-05

  • Last modified date

    2006-07-05

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

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

    UK15153 UK15154

Modules/Macros
DSNXIAIX          

Fix information

  • Fixed component name

    5740 IBM DATABA

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R710 PSY UK15153

       UP06/06/21 P F606

  • R810 PSY UK15154

       UP06/06/21 P F606

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.

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

710

Reference #:

PK22292

Modified date:

2006-07-05

Translate my page

Machine Translation

Content navigation