APAR status

• Closed as program error.

Error description

• When the expression based partitioning feature in Big SQL 4.2
  is used to partition a table on a column that yields a DATE
  column, there can be incorrect results. This is because the
  DATE type is stored as a TIMESTAMP type on HDFS and when
  comparing a predicate in Big SQL as a DATE and a value on HDFS
  as a TIMESTAMP there can be a mismatch.  Note that this problem
  does not occur when the Java Reader is being used. It also does
  not occur for HBase tables, or tables using the ORC file
  format.  Tables that use the DATE stored as DATE type or
  complex types also will not have this issue. For example, an
  expression (CAST (trans_ts as DATE)) is used on a timestamp
  column (trans_ts) in the following table to yield a DATE column
  (trans_date) using the text or parquet file format:
  create hadoop table order_facts (
  orders_id integer,
  customers_id int,
  trans_ts timestamp
  )
  row format delimited fields terminated by ','
  stored as textfile
  PARTITIONED BY (
      CAST (trans_ts as DATE) as trans_date
  );
  When the table is queried on the trans_ts column there can be
  less rows returned as expected.  Some examples are:
  "select count(*) from order_facts where date(trans_ts) >=
  '2015-01-01';"
  "select count(*) from order_facts where trans_ts >=
  '2015-01-01 00:00:00'"
  

Local fix

• 1.  Execute db2 "SET DFS_EXTERNAL_INPUT_LIBRARY='JAVA'" before
  running the query. This will force the usage of the Java Reader
  at the session level.
  
  2. Alter the table to force the use of the Java reader using :
  "ALTER TABLE p_date SET TBLPROPERTIES ('bigsql.io.engine' =
  'java')"
   ** Please remember to alter it back once the fix has been
  applied using:
   "ALTER TABLE p_date SET TBLPROPERTIES ('bigsql.io.engine' =
  'auto')"
  

Problem summary

• See error description
  

Problem conclusion

• The problem is fixed in Version 4.2.5
  

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

• n/a
  

Fix information

• Fixed component name

  INFO BIGINSIGHT

• Fixed component ID

  5725C0900

Applicable component levels

• R425 PSY

     UP