IV72113: IMPORT TASK FAILED ON SAM::APPUSAGEPROPERTYVALUE WITH SQLCODE=-803 SQLCODE=-803, SQLSTATE=23505 ERROR.

APAR status

• Closed as program error.

Error description

• After upgraded to SUA v9.2 from v9.0.x, the import task failed
  with the following error in import ETL log:
  
  
  
  2015-04-06 05:13:37 (+0:00:00.028) WARN:
  SAM::AppUsagePropertyValue skip
  parsing sorted data for computer_id=7784, last_used=2015-03-31
  11:36:02
  +0000,process=oart2010-kb2883100-fullfile-x64-glb.exe,
  app_usage_property_id= -1 ,
  2015-04-06 05:13:37 (+0:00:00.000) DEBUG:
  SAM::AppUsagePropertyValue -
  sorted data - 10794
  2015-04-06 05:13:37 (+0:00:00.030) DEBUG:
  SAM::AppUsagePropertyValue -
  sorted data - 11902
  2015-04-06 05:13:37 (+0:00:00.000) ERROR: SQL: INSERT INTO
  SAM.APP_USAGE_PROPERTY_VALUES_ETL_CHANGES
  (APP_USAGE_PROPERTY_ID,
  COMPUTER_ID, PROCESS, FIRST_USED, LAST_USED, TOTAL_TIME,
  TOTAL_RUNS)
  SELECT * FROM (SELECT ? AS APP_USAGE_PROPERTY_ID, ? AS
  COMPUTER_ID, ? AS
  PROCESS, ? AS FIRST_USED, ? AS LAST_USED, ? AS TOTAL_TIME, ? AS
  TOTAL_RUNS FROM "SYSIBM"."SYSDUMMY1") AS T1
  2015-04-06 05:13:37 (+0:00:00.006) ERROR: Batch execution error:
  Error
  for batch element #455: One or more values in the INSERT
  statement,
  UPDATE statement, or foreign key update caused by a DELETE
  statement are
  not valid because the primary key, unique constraint or unique
  index
  identified by "1" constrains table
  "SAM.APP_USAGE_PROPERTY_VALUES_ETL_CHANGES" from having
  duplicate values
  for the index key.. SQLCODE=-803, SQLSTATE=23505,
  DRIVER=4.14.111
  2015-04-06 05:13:37 (+0:00:00.001) INFO: ETL from Data Source -
  SAM::AppUsagePropertyValue         (0x00000000676F8168 -
  0x000000006797FED2): Failed
  2015-04-06 05:13:37 (+0:00:00.025) ERROR:
  Java::ComIbmDb2JccAm::BatchUpdateException:
  [jcc][t4][102][10040][4.14.111] Batch failure.  The batch was
  submitted,
  but at least one exception occurred on an individual member of
  the
  batch.
  Use getNextException() to retrieve the exceptions for specific
  batched
  elements. ERRORCODE=-4229, SQLSTATE=null
     com.ibm.db2.jcc.am.ed.a(com/ibm/db2/jcc/am/ed.java:405)
  

Local fix

• Contact SUA L3 team for any workaround.
  

Problem summary

• ****************************************************************
  ERROR DESCRIPTION:
  After you upgrade to the latest version of Software Use
  Analysis,
  the import of data fails on the SAM::APPUSAGEPROPERTYVALUE step
  and the following error can be found in the log:
  Error for batch element #455: One or more values in the INSERT
  statement, UPDATE statement, or foreign key update caused by a
  DELETE statement are not valid because the primary key, unique
  constraint or unique index identified by "1" constrains table
  "SAM.APP_USAGE_PROPERTY_VALUES_ETL_CHANGES" from having
  duplicate
  values for the index key. SQLCODE=-803, SQLSTATE=23505,
  DRIVER=4.14.111
  Behavior after you install the update:
  The error does not occur and the import of data succeeds.
  ****************************************************************
  * RECOMMENDATION: Apply appropriate update when available or
  * refer to the documentation update
  ****************************************************************
  

Problem conclusion

• The fix for this APAR is contained in the following maintenance
  packages:
   | Update | SUA 9.2.0.2 site 51
  

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