IC87667: UPDATE SYSSTAT.COLUMNS FOR HIGH2KEY AND LOW2KEY MIGHT RETURN SQLCODE -1227 IN DB2_COMPATIBILITY_VECTOR=ORA MODE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Run NNSTAT against a nickname in DB2_COMPATIBILITY_VECTOR=ORA
    mode failed with "SQL1227N reason code=3" error, even
    though
    there is only one row in the remote table. Here is the recreate
    script:
    
    db2set DB2_COMPATIBILITY_VECTOR=ORA
    db2 force application all
    db2 terminate
    db2stop
    db2start
    db2 create db ora
    db2 connect to ora
    db2 "create wrapper net8"
    db2 "create server ora10g type oracle version 10.2.0 wrapper
    net8 options (node 'nodename')"
    db2 "create user mapping for user server ora10g options
    (remote_authid 'username', remote_password 'password')"
    db2 set passthru ora10g
    db2 "create table test (c1 varchar2(15))"
    db2 "insert into test values ('ABCDEF')"
    db2 set passthru reset
    db2 "create nickname schema.test_n1 for ora10g.username.test"
    db2 "call
    sysproc.nnstat('ORA10G','SCHEMA','TEST_N1',null,null,2,'/tmp
    /nnstat.log',?)"
    SQL1227N  The catalog statistic "ABCDEF" for column "HIGH2KEY"
    is out of range
    for its target column, has an invalid format, or is inconsistent
    in relation
    to some other statistic. Reason Code = "3".
    
    The reason is there is rules about HIGH2KEY and LOW2KEY when
    manually update catalog statistics tables:
    HIGH2KEY is greater than LOW2KEY whenever there are more than
    three distinct values in the corresponding column.
    HIGH2KEY can equal to LOW2KEY when distinct value less then
    three.
    
    For example:
    In normal mode:
    => db2 "update SYSSTAT.COLUMNS set (HIGH2KEY,LOW2KEY) =
    ('ABCDEF','ABCDEF') where TABNAME='TEST_N1' and COLNAME = 'C1'"
    DB20000I  The SQL command completed successfully.
    
    But in Oracle mode:
    => db2 "update SYSSTAT.COLUMNS set (HIGH2KEY,LOW2KEY) =
    ('ABCDEF','ABCDEF') where TABNAME='TEST_N1' and COLNAME = 'C1'"
    DB21034E  The command was processed as an SQL statement because
    it was not a
    valid Command Line Processor command.  During SQL processing it
    returned:
    SQL1227N  The catalog statistic "ABCDEF" for column "HIGH2KEY"
    is out of range for
    its target column, has an invalid format, or is inconsistent in
    relation to
    some other statistic. Reason Code = "3".  SQLSTATE=23521
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * User who use NNSTAT in DB2_COMPATIBILITY_VECTOR=ORA Mode.    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Federation Server v9.7 fp8 or later version       *
    ****************************************************************
    

Problem conclusion

  • This problem is fixed in Federation Server v9.7 fp8
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC87667

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-10-30

  • Closed date

    2013-04-02

  • Last modified date

    2013-04-02

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

    JR44556

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

    IC89713

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • R970 PSY

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Reference #:

IC87667

Modified date:

2013-04-02

Translate my page

Machine Translation

Content navigation