IT15263: ADM5570W WARNINGS DETECTED ON TPCDB DATABASE

IBM Spectrum Control V5.2.12 (November 2016)
IBM Spectrum Control V5.2.13 (March 2017)
IBM Spectrum Control V5.2.14 (May 2017)
IBM Spectrum Control V5.2.15 (August 2017)
IBM Spectrum Control V5.2.15.2 (November 2017)
IBM Spectrum Control V5.2.16 (March 2018)
IBM Spectrum Control V5.2.17 (May 2018)

APAR status

• Closed as program error.

Error description

• ADM5570W warnings are due to failures of NLI (Not Logged
  Initially) operations on
  tables with failed operations, resulting in the tables getting
  marked invalid (since they
  were not logged, DB2 cannot roll back).
  Then they were marked unavailable.
  Subsequent attempts to use these MQT (materialized query tables)
  will result in DB2 warnings such as:
  
  2016-04-03-03.00.41.624000-240 E744485310F840       LEVEL:
  Warning
  PID     : 9408                 TID : 16724          PROC :
  db2syscs.exe
  INSTANCE: DB2                  NODE : 000           DB   : TPCDB
  APPHDL  : 0-23479              APPID: *LOCAL.DB2.160402203101
  AUTHID  : DB2ADMIN             HOSTNAME: MTBUVSC0201
  EDUID   : 16724                EDUNAME: db2agent (TPCDB) 0
  FUNCTION: DB2 UDB, data management, sqldSetupSQLCA, probe:3
  MESSAGE : ADM5570W  Access was attempted on an unavailable
  object with
  id "7183" in tablespace "6" for table
  "TPC.T_RPT_STORAGE_SUBSYSTEM_KEYS".  If the object is a table it
  will
  have to be dropped.  If the object is a partition it will
  have to be detached. If the object is a non-partitioned index
  the index
  will have to be dropped.
  As the operation was not logged, DB2 could not roll back. All
  DB2 could do is mark the table invalid. So it has to be dropped
  and recreated.
  These operations are not logged because logging was disabled for
  certain Cognos-specific MQTs to avoid transaction log space
  exhaustion issues.
  RECREATE STEPS:
  ________________________________________________________________
  DB2 Version used for Server:    N/A
  The defect is against component:   5608TPC00
  Server/Manager build/release (TPC):   5.2.9
  
  ________________________________________________________________
  Problem as described by customer: ADM5570W warnings noticed by
  DBA
  Initial customer impact (low/med/high): low
  

Local fix

• Recreating the MQT once it becomes inactive is fairly
  straightforward through use of SQL scripts available upon
  request from support.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * IBM Spectrum Control 5.2.x users generating Cognos reports.  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error descriptions.                                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply fix maintenance when available.                        *
  ****************************************************************
  IBM Spectrum Control reports generated in Congnos are using some
  MQTs, which are refreshed using "NOT LOGGED INITIALLY".
  When an SQLException occurs during the refresh or the database
  is recovered using ROLL FORWARD, those MQTs are marked as
  unavailable.
  Any subsequent access to them will fail with the SQL exception
  of this type:
  SQL1477N  For table "TPC.T_RPT_STORAGE_VIRTUALIZER_KEYS" an
  object "2319" intable space "7" cannot be accessed.
  SQLSTATE=55019
  

Problem conclusion

• This APAR will catch that SQLException and regenerate the MQT.
  The fix for this APAR is targeted for the following maintenance
  package:
  
  | refresh pack | 5.2-TIV-TPC-RP0012 - target November 2016
  
  Fixed in IBM Spectrum Control 5.2.12.
  
  http://www.ibm.com/support/docview.wss?&uid=swg21320822
  
  The target dates for future refresh packs do not represent a
  formal commitment by IBM. The dates are subject to change
  without notice.
  

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:

Modules/Macros

• Cognos
  

Fix information

• Fixed component name

  TPC

• Fixed component ID

  5608TPC00

Applicable component levels

• R529 PSY

     UP

• R52A PSY

     UP