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

DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows

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 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