IBM Support

PK69563: RUNSTATS REPORT WRONG HIGH2KEY/LOW2KEY FOR NULL VALUE OF INTEGERCOLUMN, AND ADD DUPLICATE ENTRIES IN SYSCOLUMNS_HIST TABLE .

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Two Runstats problem:
    
    Problem 1) There are always 2 entries in the
    sysibm.syscolumns_hist table for one column at one time in case
    the column is an index as well and table(all) index(all)
    are specified in RUNSTATS. The two entries are exactly same so
    it looks like as duplicate entries.
    
    Problem 2) There are x'FF' rather than x'FF00000000' in the
    high2key/low2key of sysibm.syscolumns_hist table for a INTEGER
    column.
    
    The following testcase can be used to recreate the problems :
    
    CREATE TABLE TB48003 ( COL01 INTEGER
    ,COL02 CHAR ( 2 ) )
    PARTITION BY (COL02)
    (PARTITION 1 ENDING AT ( '11' )
    ,PARTITION 2 ENDING AT ( '12' )    )
    IN DB48003.TS48003;
    
    CREATE INDEX IX480031
    ON TB48003 ( COL01  ASC )
    PARTITIONED;
    
    INSERT INTO TB48003 (COL02) values('11');
    INSERT INTO TB48003 (COL02) values('12');
    
    The test results shows:
    - When RUNSTATS with TABLE(ALL), the high2key/low2key of COL01
    is x'FF' in SYSCOLUMN, SYSCOLUMN_HIST and SYSCOLSTATS .
    - When RUNSTATS with INDEX(ALL), the high2key/low2key of COL01
    is x'FF000000' in SYSCOLUMN, SYSCOLUMN_HIST and SYSCOLSTATS .
    - When RUNSTATS with TABLE(ALL) INDEX(ALL), the high2key/low2key
    of COL01 is x'FF000000' in SYSCOLUMN and SYSCOLSTATS; there two
    entries in SYSCOLUMN_HIST, one is x'FF000000', another is x'FF'.
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS V8 and DB2 9 for z/OS users *
    *                 of the RUNSTATS utility                      *
    ****************************************************************
    * PROBLEM DESCRIPTION: RUNSTATS TABLESPACE TABLE INDEX         *
    *                      updated redundant statistics on the     *
    *                      leading column of an index.             *
    *                                                              *
    *                      RUNSTATS TABLESPACE TABLE generated     *
    *                      incorrect null value representation for *
    *                      a nullable fixed length column.         *
    ****************************************************************
    * RECOMMENDATION: Apply corrective PTF when available.         *
    ****************************************************************
    User ran a RUNSTATS TABLESPACE TABLE ALL INDEX ALL on a
    partitioned table space, where a non-clustering partitioned
    index is defined with leading column C1.  The RUNSTATS utility
    completed successfully, but the column information for C1 would
    collected and updated twice redundantly by the same RUNSTATS
    job, resulting in multiple rows inserted into
    SYSIBM.SYSCOLUMNS_HIST.
    
    On a related scenario, RUNSTATS TABLESPACE TABLE ALL INDEX
    is run on a partitioned table space with a clustering non-
    partitioned index (NPI) defined on column C2. After RUNSTATS
    completed successfully, partition information for column C2
    are neither updated into SYSIBM.SYSCOLSTATS nor reported via
    MSGDSNU625I.
    
    For a NULL value of a nullable fixed length column, RUNSTATS
    TABLESPACE TABLE would update the catalog SYSIBM.SYSCOLUMNS
    and SYSIBM.SYSCOLSTATS with a 1 byte hex value of X'FF', while
    the same statistics collected by an index scan thru RUNSTATS
    INDEX yields a 1 byte hex value of X'FF' followed by padding
    characters of X'00' (e.g. a null value for an integer column
    is X'FF00000000').  The 1 byte representation of X'FF' by the
    table space statistics collection is incorrect.  These values
    are also reported via MSGDSNU625I and MSGDSNU615I.
    

Problem conclusion

  • Code has been modified to correct the reported problems:
    - RUNSTATS TABLESPACE TABLE INDEX will avoid collecting and
      updating redundant SYSCOLUMNS/SYSCOLSTATS information when
      the equivalent statistics can be collected on an index scan.
    - RUNSTATS TABLESPACE TABLE is modified to generate the correct
      NULL value for a fixed length nullable column.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PK69563

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    810

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-07-24

  • Closed date

    2008-09-03

  • Last modified date

    2008-10-02

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

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

    UK39501 UK39502

Modules/Macros

  • DSNUSEF2 DSNUSIXO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R810 PSY UK39501

       UP08/10/01 P F809

  • R910 PSY UK39502

       UP08/10/01 P F809

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.



Document information

More support for: DB2 for z/OS

Software version: 8.1

Reference #: PK69563

Modified date: 02 October 2008