Release notes

IBM® Db2® Big SQL v6.0.0.0 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 v6.0.0.0 has improved performance, usability, serviceability, and consumability capabilities.

Summary of new features and capabilities:

Installation and upgrade
  • To reduce deployment time, Db2 Big SQL head and worker node installation is done in parallel.
  • When upgrading from Db2 Big SQL 5.0.4 and higher, HA no longer needs to be disabled.
Enterprise and performance
  • This release adds a number of new data sources and platforms for federation that are bundled in Db2 Big SQL 6.0.0.
  • An enhancement that enables more filtering of data while tables are being joined is now enabled by default.
  • The Db2 Big SQL plugin for Ranger now supports access policies on nicknames.
  • This release includes a number of aggregation enhancements for improved memory usage and performance.
SQL compatibility improvements
  • Db2 Big SQL now has enhanced NZPL SQL support, including support for %ROWTYPE, %TYPE, FOUND, ROW_COUNT, REFTABLE, AUTOCOMMIT, and the SELECT INTO record type. There are also many SQL statement enhancements, additional data type support, and improvements to scalar functions that significantly increase Netezza® compatibility.
  • For improved compatibility with Db2 for z/OS®, you can now specify the fetch-clause in a select-statement. You can also specify the offset-clause without the fetch-clause.
  • Db2 Big SQL now supports a new set of built-in JSON SQL functions for enhanced SQL interaction with JSON data.
  • The DISTINCT predicate enables you to compare two expressions and evaluates to TRUE if the values of the two expressions are not identical.
Usability and serviceability
  • Support for storage devices that use 4-KB sector sizes is available as a technical preview only (not for production environments until further notice).
Additional support
  • This release introduces the Db2 Big SQL console, which you can use for database monitoring, administration, and configuration. The console supersedes the Data Server Manager (DSM), which is not available in this release.

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

Changed behavior

Create table (Hadoop) changes
When a table is created in Db2 Big SQL by using the CREATE TABLE (HADOOP) statement, Db2 Big SQL in turn creates a table on the Hive metastore. Starting with HDP 3.0, Hive tables are managed tables by default (for background information on managed tables, see Managed vs. External Tables). Hive tables that are implicitly created by Db2 Big SQL, however, are not Hive managed tables. To create a table, Db2 Big SQL runs a CREATE EXTERNAL TABLE statement rather than a CREATE TABLE statement in Hive. For more information, see CREATE TABLE (HADOOP) statement.

Known issues

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

Issues related to Db2 Big SQL version 6.0.0.0

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. Drop the table in Db2 Big SQL:
    
db2 DROP TABLE s1.t1;
DB20000I  The SQL command completed successfully.
LIKE operator with an ESCAPE clause while using federation to reference a Cloudera or Db2 for z/OS data source 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 while federation is used to reference a Cloudera or Db2 for z/OS data source returns an error (SQLSTATE 560BD):

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

An issue in Hive prevents Db2 Big SQL users from being able to drop a partition if the table is partitioned on a VARCHAR column.

Workaround: Alter the table in Hive to change the data type of the partition column from VARCHAR to STRING, and then drop the partition.

Issues related to HDP 3.1.0

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. Create the /tmp/hive directory on all nodes.
  3. Set the owner of the directory to user yarn with the command:
    chown yarn /tmp/hive
  4. Set the permissions of the directory to drwx------ with the command:
    chmod 700 /tmp/hive
  5. Re-run the load job.
Automatic syncing from Ambari might not work properly

When you enable automatic syncing from Ambari by selecting IBM Db2 Big SQL > Configs > Advanced bigsql-env > Automatic metadata sync enabled, the syncing of table changes might not work correctly.

Workaround: Have the bigsql user grant the EXECUTE privilege on the HCAT_SYNC_OBJECTS stored procedure to the owners of the Hive tables.

Changed behavior
When a table is created in Db2 Big SQL by using the CREATE TABLE (HADOOP) statement, Db2 Big SQL in turn creates a table on the Hive metastore. Starting with HDP 3.0, Hive tables are managed tables by default (for background information on managed tables, see Managed vs. External Tables). Hive tables that are implicitly created by Db2 Big SQL, however, are not Hive managed tables. To create a table, Db2 Big SQL runs a CREATE EXTERNAL TABLE statement rather than a CREATE TABLE statement in Hive. For more information, see CREATE TABLE (HADOOP) statement.