Release notes

IBM® Db2® Big SQL v5.0.3 provides full-function SQL query capability with Hortonworks Data Platform (HDP) support and security and performance benefits.

These release notes contain:

New features overview

IBM Db2 Big SQL v5.0.3 has improved performance, usability, serviceability, and consumability capabilities.

Summary of new features and capabilities:

Installation and upgrade
  • New installation options allow deployment in a highly secured and customized environment.
  • Reduced number of open source component dependencies, such as HBase, needed to install Db2 Big SQL.
  • Express patch management is now available, and entitled customers have access to cumulative patches
Enterprise and performance
  • Creating Hadoop partitioned materialized query tables (MQTs) for better query performance
  • Db2 Big SQL now includes support for working with Hive transactional (ACID) tables.
  • Db2 Big SQL now supports integration with IBM InfoSphere Information Server to help you govern Db2 Big SQL data.
  • Improved data governance using integration with the IBM InfoSphere Information Server.
Usability and serviceability
  • You can now create a new Big SQL Ranger authorization policy based on an existing Hive Ranger policy.

For details of these features and capabilities see the What's new topic.

Known issues

Known issues exist related to Hortonworks Data Platform (HDP) 2.5 and Db2 Big SQL version 5.0.3.

Issues related to HDP 2.5

LOAD HADOOP commands with the SFTP connector are not supported

Load operations that use the LOAD HADOOP command with the SFTP connector are not supported in HDP 2.5.

Workaround: No workaround is available for HDP 2.5. These operations are supported in HDP 2.6.2.

HBase loads might fail

When you perform an HBase load, you might see error messages in the HBase RegionServer logs such as:

Caused by: java.lang.NoClassDefFoundError: /bigsql/serde/hbase/BigSqlHBaseDeserializer.

Workaround: Ensure the Java™ JAR files are accessible by completing the following steps on each HBase node:
  1. Run the following commands:
    
cd /usr/hdp/hdp_version/hbase/lib/
ln -s /usr/hdp/hdp_version/hive/lib/hive-serde.jar hive-serde.jar
ln -s /usr/hdp/hdp_version/hive/lib/hive-common.jar hive-common.jar

    where hdp_version is the version of HDP installed. For example: 2.5.5.0-102.

  2. Restart HBase and Big SQL.
Hadoop load jobs might fail

A Hadoop load job might fail with an internal error, and the job log might show that the /tmp/hive directory is not writable.

Workaround: The directory most likely has incorrect permissions. To reset permissions:
  1. Remove /tmp/hive from all nodes with the command:
    rm -rf /tmp/hive
  2. Set the owner of the directory to user yarn with the command:
    chown yarn /tmp/hive
  3. Set the permissions of the directory to drwx------ with the command:
    chmod 700 /tmp/hive
  4. Re-run the load job.
Tables that use CMX compression are not supported

CMX compression is not available for HDP 2.5.

Workaround: No workaround is currently available.

Automatic syncing from Ambari might not work properly

When you try to automatically sync from Ambari by selecting Big SQL service > Enable Metadata Sync, the sync might not work correctly.

Workaround: Have the bigsql user grant execute permission for the HCAT_SYNC_OBJECTS procedure to public or to the owners of the Hive tables.

The Explain feature in DSM might show an empty page

The first time that you try to open the Explain facility from Data Server Manager (DSM), DSM might show an empty page and fail to create explain tables.

Workaround: Create the explain tables manually by calling the SYSPROC.SYSINSTALLOBJECTS procedure. For example:

SYSPROC.SYSINSTALLOBJECTS(
  'EXPLAIN', 'C',
  CAST (NULL AS VARCHAR(128)),
  CAST (CURRENT SCHEMA AS VARCHAR(128))
)
You might be unable to access an HBase table created by Big SQL

You might be unable to access an HBase table that was created by Big SQL from Hive with interactive mode enabled (also known as the Hive 2.1 preview).

Workaround: Disable Hive low latency analytical processing (LLAP) with the following command:
SET hive.llap.execution.mode=none;
Note: This issue is resolved in HDP 2.6.2.

Issues related to Db2 Big SQL version 5.0.3

The memory and CPU used by Big SQL might cause conflicts with YARN CGroups

The memory and CPU used by Big SQL, which you can display with the command Advanced bigsql-env > bigsql_resource_percent, might cause conflicts with YARN CGroups, resulting in a kernel panic.

Workaround: YARN CPU isolation is disabled by default. Enable YARN CPU isolation:
  1. Log into Ambari
  2. Navigate to YARN > Config
  3. Click Enable CPU Isolation
Dropping a table that is defined in the HDFS encryption zone might cause an error

The HDFS encryption zone is a secure directory. If a trash interval property is enabled by default, an SQL5105N error is generated when you drop a table from a secure directory. Dropping a table moves the contents to the non-encryption zone trash folder in your home directory, which is not allowed.

Workaround: Assume that you have a table called t1 in an encryption zone: 


$ db2 "create hadoop table s1.t1 (c1 int) location '/secureDir/bigsql/t1'"
$ db2 "insert into s1.t1 values(1)"
  1. Drop the table by using the Hive PURGE option:
    hive> DROP TABLE s1.t1 PURGE;
  2. Verify that the data files are removed from HDFS: 
    hadoop fs -ls /secureDir/bigsql/t1/
    You should see the following response:

    ls: '/secureDir/igsql/t1/': No such file or directory

  3. Delete the table in Big SQL:
    
db2 DROP TABLE s1.t1;
DB20000I  The SQL command completed successfully.
Session timeout issues might occur when you log in or out of DSM

Due to a KNOX limitation, the logout and session timeout functions do not work properly in DSM. These issues are related to KNOX-997 and KNOX-571.

Workarounds:
  • If the default session timeout setting for the Knox gateway of 30 minutes is reached, refresh your browser before continuing to work in DSM.
  • If you need to log in with a different DSM user ID, first clear your browser cache.
The Hive server might not restart after an upgrade or migration

The Hive server might not restart after an upgrade or migration if the Hive server is on a different host than the Hive Meta store.

Workaround: Copy the bigsql-sync.jar file from usr/ibmpacks/bigsql/5.0.3.0/bigsql/lib/java/ to /usr/hdp/current/hive-server2/lib/ on the Hive server host.

You cannot install a second Big SQL head component on CentOS

You cannot install a second Big SQL head component on Cent OS versions 6.7, 6.8 and 6.9.

Workaround: Contact IBM Support to request a patch for Big SQL High Availability on CentOS 6.

LIKE operator with an ESCAPE clause pushed down to Impala returns an error
In Db2 Big SQL, the LIKE operator has an ESCAPE clause. Although this clause is not available with the Apache Impala LIKE operator, it is still being pushed down by Db2 Big SQL. The following example query running on Impala returns an error (SQLSTATE 560BD):

db2 "select ch from BIGAFED_ALLTYPES_SDS where ch like 'a__%' escape '_' order by 1"