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

DB2 Version 10.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 10 for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 11 for Linux, UNIX, and Windows

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 Oracle compatible of InfoSphere Federation
  Server Problem description and summay:
    See error description
  

Problem conclusion

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

Temporary fix

Comments

APAR Information

• 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

• RA50 PSY

     UP