IV68900: SUA 9.0.1.1 DATA IMPORT FAILS AROUND 15% BECAUSE IT FILLS UP DB2 TEMPSPACE1 TABLE SPACE

APAR status

• Closed as program error.

Error description

• ENV: iem 9.1.1117.0, SUA 9.0.1.1-20141028-1440
  SUA Data Import fails at about 15% while process this query
  2014-12-19 23:29:25 (+0:08:56.868) ERROR:
  
  Java::ComIbmDb2JccAm::SqlException: The file system is full..
  SQLCODE=-968, SQLSTATE=57011, DRIVER=4.17.28:         select
  O.Sitename
  as sitename,
                 O.ID       as id,
                 case when O.Sitename = 'ActionSite' then
  OM.CustomSiteName else null end as custom_sitename,
                 O.Name     as name,
                 0          as is_retrieved_property,
                 0          as is_reserved,
                 O.Fields   as fields,
                 case when O.ParentID = 1 then 1 else 0 end as
  deleted
          from BES_OBJECT_DEFS O
  
          inner join dss.object_metadata OM on O.ID = OM.ID
            and O.Sitename = OM.Sitename
            and O.IsFixlet = OM.IsFixlet
          where O.Sequence    > 79959859
            and O.Sequence  <=  82323781
            and OM.FixletType =  'Analysis'
          union all
          select 'ActionSite' as sitename,
                 O.ID         as id,
                 null         as custom_sitename,
                 O.Name       as name,
                 1            as is_retrieved_property,
                 case when AM.IsReserved = 'Yes' then 1 else 0 end
  as
  is_reserved,
                 O.Fields     as fields,
                 case when O.ParentID = 1 then 1 else 0 end as
  deleted
          from BES_OBJECT_DEFS O
          inner join dss.reserved_analysis_metadata AM on O.ID =
  AM.ID
            and O.Sitename = AM.Sitename
            and O.IsFixlet = AM.IsFixlet
          where O.Sequence      > 79959859
            and O.Sequence    <=  82323781
            and O.ContentType  =  5
            and AM.SMIME is not null
  .
  2014-12-19 23:29:25 (+0:00:00.020) INFO: ETL from SUA Server 1 -
  RawDatasourceAnalysis              (0x0000000004C41733 -
  0x0000000004E82945): Failed
  2014-12-19 23:29:25 (+0:00:00.026) ERROR: Sequel::DatabaseError:
  Java::ComIbmDb2JccAm::SqlException: The file system is full..
  SQLCODE=-968, SQLSTATE=57011, DRIVER=4.17.28
      com.ibm.db2.jcc.am.fd.a(com/ibm/db2/jcc/am/fd.java:752)
  --------------
  This was further narrowed down to be triggered by the following
  2 views which are referenced in the above query
  select count(*) from dss.object_metadata
  
  select count(*) from dss.reserved_analysis_metadata
  
  Both of these views reference the functions
  case
  when length(Fields) <= 1048576
  then dss.fn_ExtractField1M(  'CustomSiteName', 0, O.Fields)
  when length(Fields) <= 134217728
                 then dss.fn_ExtractField128M('CustomSiteName', 0,
  O.Fields)
  
  So the problem is triggered when this Field comes across very
  large BES objects.
  
  Running the following query
   select SITENAME, name, LENGTH(FIELDS) from DBO.BES_OBJECT_DEFS
  where LENGTH(FIELDS) > 1048576
  
  can identify the problem objects. In this case they were all
  custom BASELINES that were VERY LARGE (ie. over 1500
  Components).
  
  SUA needs to be able to more gracefully handle such cases so
  that it does not fill up DB2 tempspace1 tablespace.
  

Local fix

• Run the following query
   select SITENAME, name, LENGTH(FIELDS) from DBO.BES_OBJECT_DEFS
  where LENGTH(FIELDS) > 1048576
  
  to identify problem BES objects, then in the IEM console delete
  them (ie. right click Remove), then run the BESAuditTrailCleaner
  as per
  https://www.ibm.com/developerworks/community/wikis/home?lang=en#
  /wiki/Ti
  voli%20Endpoint%20Manager/page/TEM%20Audit%20Trail%20Cleaner
  
  to remove this BES objects from the BFENT database as well.
  ie. Performing a Remove from the IEM console just sets a
  isdeleted=yes flag on the object and does not show it in the
  console. The BESAuditTrailCleaner actually removes these.
  

Problem summary

• ****************************************************************
  * Data import fails because of a large Endpoint Manager fixlet.
  ****************************************************************
  * RECOMMENDATION: Apply appropriate update when available.
  ****************************************************************
  

Problem conclusion

• The fix for this APAR is contained in the following maintenance
  packages:
   | Update | SUA 9.2 site version 44
   | Update | ILMT 9.2 site version 58
  

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

  IEM SW USE ANL

• Fixed component ID

  5725F57MT

Applicable component levels

• R910 PSY

     UP