IBM Support

IC96260: NNSTAT METHOD 1 OR 0 REPORTS SQL1227N RC=3 WHEN IT TRIES TO UPDATE HIGH2KEY TO SINGLE BLANK FOR AN INTEGER COLUMN

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • NNSTAT with method 1(or 0) reports SQL1227N RC=3 when it tries
    to update HIGH2KEY/LOW2KEY to a single blank for a numerical
    column:
    
    SQL1227N  The catalog statistic " " 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 same error may happen to all numerical data type columns,
    such as integer, float, decimal and double columns. The problem
    only happens when DB2_COMPATIBILITY_VECTOR=ORA.
    
    REPRODUCE:
    connect to fdbora;
    drop server serv1;
    create server serv1 type oracle version '11g' wrapper net8
    options(node 'ora11gr2',VARCHAR_NO_TRAILING_BLANKS 'Y');
    create user mapping for db2inst1 server serv1
    options(remote_authid '<user name>',remote_password
    '<password>');
    set passthru serv1;
    drop table test;
    create table test(c1 number(6), c2  number(25), c3
    number(38,12));
    insert into test values(null,null,11.1);
    insert into test values(null,1,22.2);
    insert into test values(null,2,33.3);
    insert into test values(null,3,44.4);
    set passthru reset;
    create nickname mt for  serv1."<user name>"."TEST";
    call
    sysproc.nnstat('SERV1','DB2INST1','MT',NULL,NULL,1,'/home/db2ins
    t1/nnstat1.log::DIAG',?);
    
    In above case, the nicknames COLCARDs will be:
    --------------------
      C1's COLCARD = 0
      C2's COLCARD = 3
      C3's COLCARD = 4
    --------------------
    And if statistic data haven't been ran for the source table on
    the Oracle server, all columns will have COLCARD = -1.
    
    Also, in a database created with DB2_COMPATIBILITY_VECTOR=ORA,
    if COLCARD <= 3 , CREATE NICKNAME statement(NNSTAT with method 1
     collects statistic data from remote data source by creating a
    nickname)gets HIGH2KEY/LOW2KEY as a single blank ' '(0x20) for a
     column with numerical data type.
    
    When NNSTAT tries to update HIGH2KEY/LOW2KEY to a single
    blank(the HIGH2KEY/LOW2KEY value collected by creating the
    nickname), the error happens. In this case, the column is an
    numeric, and db2 need to convert the HIGH2KEY/LOW2KEY
    string(single blank) to a native integer to interpret it
    natively.  The conversion returns an error because single blank
    is not a number:
    
    update SYSSTAT.COLUMNS SET (COLCARD, HIGH2KEY, LOW2KEY) = (3, '
    ',' ') where TABNAME='MT' and COLNAME = 'C2'
    

Local fix

  • Use method 2 instead.
    

Problem summary

  • User affected:
      Users who use NNSTAT of InfoSphere Federation Server
    Problem description and summay:
      See error description
    

Problem conclusion

  • Problem was fistly fixed in Version 10.1, FixPak 3. This
    fix should be applied on the federation Server.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC96260

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-09-25

  • Closed date

    2013-10-28

  • Last modified date

    2013-10-28

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

    JR46358

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSY

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
28 October 2013