PI85456: ANALYZE FAILS ON BIG SQL PARTITIONED TABLES THAT HAVE A MIX OF DATE AND TIMESTAMP DIRECTORY STRUCTURES ON HDFS

APAR status

• Closed as program error.

Error description

• For partitioned tables, there is a directory on HDFS for every
  partitioning value.  Big SQL expects a certain format on HDFS
  for date values when a table is partitioned on a Date type.
  When the format on HDFS deviates from the format that Big SQL
  is expecting, Analyze may fail.
  
  For example, when a Big SQL table is partitioned by a date
  type, by default it is stored as a timestamp type in Hive.
  When a Big SQL table is partitioned by a date stored as date
  type it is stored as a date type in Hive.  When a Hive table is
  imported into Big SQL with a Date type then Big SQL expects
  values of Date type.
  
  If the directory structure contains a mix of Date and Timestamp
  values then Big SQL Analyze can get confused.
  

Local fix

• -Create a new Big SQL table (tab2) partitioned by Date in a new
  location on HDFS.  Big SQL will be expecting timestamp
  formatted directory structures on HDFS
  -Copy all timestamp formatted directories from the old table
  (tab1) to the new table on HDFS
  -Copy all date formatted directories from the old table to the
  new table on HDFS renaming the date directories to timestamp
  directories; e.g.:
         hadoop fs -cp /user/hadoop/tab1/c2=2017-07-15
  /user/hadoop/tab2/c2= 2017-07-15 00%3A00%3A00
  -Issue an hcat-sync command: CALL SYSHADOOP.HCAT_CACHE_SYNC
  ('bigsql','tab2');
  -Run the Analyze command on tab2
  Change applications to use tab2 instead of tab1
  

Problem summary

• See error description
  

Problem conclusion

• The problem is fixed in Version 5.0.1.0 and later fix packs
  

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

• Unknown
  

Fix information

• Fixed component name

  INFO BIGINSIGHT

• Fixed component ID

  5725C0900

Applicable component levels

• R425 PSY

     UP