Fixes are available
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
**************************************************************** * USERS AFFECTED: * * User who using NNSTAT * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to V97fp9 * ****************************************************************
Problem conclusion
Temporary fix
Comments
APAR Information
APAR number
JR46358
Reported component name
WS FA UTILITIES
Reported component ID
5724N9716
Reported release
970
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2013-04-15
Closed date
2013-12-16
Last modified date
2013-12-16
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
WS FA UTILITIES
Fixed component ID
5724N9716
Applicable component levels
R970 PSN
UP
R970 PSY
UP
[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPX","label":"Federated Server"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
16 December 2013