PH19970: ANALYZE STATEMENT COLLECTING COLUMN STATS OCCASIONALLY FAILS WITH SQL5105N AND NUMBERFORMATEXCEPTION

APAR status

• Closed as fixed if next.

Error description

• DB21034E The command was processed as an SQL statement because
  it was not a valid Command Line Processor command.
  During SQL processing it returned:
  
  SQL5105N The statement failed because a
  Big SQL component encountered an
  error. Component receiving the
  error: "". Component returning the error:
  "HIVE". Log entry
  identifier: "[BSL-0-15e8ce4c3]". SQLSTATE=58040
  
  In the
  bigsql.log, you will see an error similar to:
  
  2019-03-08
  18:24:45,763 WARN com.ibm.biginsights.catalog.translator.hive.Hi
  veExceptionTranslator [Thread-277] : [BSL-0-15e8ce4c3]:
  null
  java.lang.NumberFormatException
   at
  sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
  Method)
   at sun.reflect.NativeConstructorAccessorImpl.newInstanc
  e(NativeConstructorAccessorImpl.java:88)
   at sun.reflect.Delegat
  ingConstructorAccessorImpl.newInstance(DelegatingConstructorAcce
  ssorImpl.java:57)
   at
  java.lang.reflect.Constructor.newInstance(Constructor.java:437)
  
  at java.util.concurrent.ForkJoinTask.getThrowableException(ForkJ
  oinTask.java:609)
   at java.util.concurrent.ForkJoinTask.reportEx
  ception(ForkJoinTask.java:688)
   at
  java.util.concurrent.ForkJoinTask.join(ForkJoinTask.java:731)
  
  at java.util.concurrent.ForkJoinPool.invoke(ForkJoinPool.java:26
  27)
   at com.ibm.biginsights.bigsql.stats.ColumnReader.aggColumnS
  tats(ColumnReader.java:42)
   at com.ibm.biginsights.biga.udf.stat
  s.StatsUpdater.aggColumnStats(StatsUpdater.java:249)
   at com.ibm
  .biginsights.biga.udf.stats.StatsUpdater.etlStatistics(StatsUpda
  ter.java:172)
   at com.ibm.biginsights.biga.udf.stats.StatsUpdate
  r.doETL(StatsUpdater.java:114)
   at com.ibm.biginsights.biga.udf.
  stats.AnalyzeTool.analyze(AnalyzeTool.java:142)
   at com.ibm.biginsights.biga.udf.stats.AnalyzeTool.analyze
  
  (nalyzeTool.java:69)
  
  at com.ibm.biginsights.biga.udf.BIGSQL_DDL.performAnalyze(BIGSQL
  _DDL.java:1410)
   at com.ibm.biginsights.biga.udf.BIGSQL_DDL.doAn
  alyzeStatement(BIGSQL_DDL.java:1321)
   at com.ibm.biginsights.big
  a.udf.BIGSQL_DDL.processDDL(BIGSQL_DDL.java:234)
  Caused by:
  java.lang.NumberFormatException
  (...)
  Caused by:
  java.lang.NumberFormatException: 1340359454812772127
   at com.goo
  gle.common.primitives.UnsignedLongs.parseUnsignedLong(UnsignedLo
  ngs.java:307)
   at com.google.common.primitives.UnsignedLong.valu
  eOf(UnsignedLong.java:137)
   at com.google.common.primitives.Unsi
  gnedLong.valueOf(UnsignedLong.java:125)
   at com.ibm.biginsights.
  bigsql.stats.PartitionReader.read(PartitionReader.java:116)
   at
  com.ibm.biginsights.bigsql.stats.PartitionReader.access$100(Part
  itionReader.java:30)
   at com.ibm.biginsights.bigsql.stats.Partit
  ionReader$ReadPartition.computeDirectly(PartitionReader.java:172
  )
   at com.ibm.biginsights.bigsql.stats.PartitionReader$ReadParti
  tion.compute(PartitionReader.java:158)
   ... 6 more
  
  SELECT
  statements run against this table will succeed. An ANALYZE
  statement that does not collect column statistics (such as
  "analyze table <sch>.<tablename> COMPUTE STATISTICS") will
  succeed.
  
  The value triggering the exception has a hidden
  character at the end. Using a text editor to display hidden
  characters shows that the last digit of is a null character
  (0x00). This is causing the number exception.
  
  Caused by:
  java.lang.NumberFormatException: 933061816423488757^@
   at com.go
  ogle.common.primitives.UnsignedLongs.parseUnsignedLong(UnsignedL
  ongs.java:307)
   at com.google.common.primitives.UnsignedLong.val
  ueOf(UnsignedLong.java:137)
   at com.google.common.primitives.Uns
  ignedLong.valueOf(UnsignedLong.java:125)
   at com.ibm.biginsights
  .bigsql.stats.PartitionReader.read(PartitionReader.java:116)
  
  at com.ibm.biginsights.bigsql.stats.PartitionReader.access$100(P
  artitionReader.java:30)
   at com.ibm.biginsights.bigsql.stats.Par
  titionReader$ReadPartition.computeDirectly(PartitionReader.java:
  172)
   at com.ibm.biginsights.bigsql.stats.PartitionReader$ReadPa
  rtition.compute(PartitionReader.java:158)
   ... 6 more
  
  The root cause appears to be an issue in the Guava library
  causing the last digit of some negative long values to
  be written incorrectly (as a null character).
  
  
  .
  Note that the fix for this issue prevents future occurrences
  but cannot clean up pre-existing incorrect statistics.
  The error comes from reading the synopsis data from disk;
  one or more of the values in the *.akmv files is essentially
  corrupted. Incremental Analyze (the default for partitioned
  tables) involves storing data for each partition long-term on
  disk, which is read each time an Analyze is performed, along
  with the new data being scanned (if any). The corrupted data
  could be present in this persisted synopsis data.
  The steps in the local fix (removing the ._biginsights_stats
  directories for any affected tables) must be followed to
  clean up any occurrences of this issue, even after applying
  this fix.
  

Local fix

• In some instances, removing the ._biginsights_stats directory
  and re-running ANALYZE worked. If this does not work, recreate
  the table.
  

Problem summary

• Please see problem description.
  

Problem conclusion

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

Applicable component levels