JR42190: FEDERATION SERVER NNSTAT PROCEDURE RETURNS SQL20274W ERROR WHEN UPDATE STATISTICS FOR A SERVER IN WHOLE

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• NNSTAT procedure can be run against different objects, such as
  column, index, nickname, schema or server object,
  when you run NNSTAT against a server object, it means update
  statistics for all nicknames under all schemas on that
  server in whole. However, in a server, when there are more than
  one nickname have index created on them, run NNSTAT
  against that server object will fails with SQL20274W error.
  
  CALL SYSPROC.NNSTAT('SERVER', NULL, NULL, NULL, NULL, 0,
  '/home/user1/nnstat.log', ?)
  SQL20274W  Some of the nickname statistics cannot be updated.
  
  
  You can reproduce this error by following steps:
  1) Create two tables on remote data source, and create index for
  each of them.
  CREATE TABLE testtb1(t1c1 INT, t1c2 INT);
  CREATE TABLE testtb2(t2c1 INT, t2c2 INT);
  <insert some data>
  CREATE INDEX idx1 ON TESTTB1(t1c1, t1c2);
  CREATE INDEX idx2 ON TESTTB2(t2c1, t2c2);
  
  2) On Federation Server, create Federated objects and nicknames
  for those remote table.
  CREATE WRAPPER <Wrapper> LIBRARY '<WrapperLib>'
  CREATE SERVER  <SERVER>  TYPE <server type> VERSION <version
  number> WRAPPER <Wrapper>  OPTIONS (NODE 'node name', DBNAME
  'database name')
  CREATE USER MAPPING FOR <USER> SERVER <SERVER> OPTIONS
  (REMOTE_AUTHID 'authid', REMOTE_PASSWORD 'password')
  CREATE NICKNAME FOR <SERVER>.<USER>."TESTTB1"
  CREATE NICKNAME FOR <SERVER>.<USER>."TESTTB2"
  
  3) Run NNSTAT against server object
  CALL SYSPROC.NNSTAT('SERVER', NULL, NULL, NULL, NULL, 0,
  '/home/user1/nnstat.log', ?)
  SQL20274W  Some of the nickname statistics cannot be updated.
  
  The cause of this error is that, when update INDEX columns'
  statistics information, NNSTAT doesn't distinguish columns from
  different nickname, but update them together, this will cause
  catalog looking up error when colunm under processing mismatches
  nickname under processing simutaniously.
  

Local fix

• No.
  A possible workaround is running NNSTAT against each nickname
  separately.
  

Problem summary

• User affected:
   Users of Federation Server who use NNSTAT utility
  Problem Descripion and Summary:
   See error description.
  

Problem conclusion

• Problem will be fixed in V97fp6. This fix should be
  applied on Federation Server.
  

Temporary fix

Comments

APAR Information

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

     UP