What's new in the SQL Warehousing Tool

What's new in SQW for DB2 Warehouse V10.5

The SQW component in DB2 Warehouse Version 10.5 includes the following new features:

Support for BLU Acceleration

DB2 Warehouse Version 10.5 introduces support for BLU Acceleration. Now, compressed column-organized tables are supported. Also included is broad support for data mart (analytic) workloads that have complex queries that are commonly characterized by multi-table joins, grouping and aggregation, and table scans over a star schema.

BLU Acceleration, a combination of innovations from the IBM® Research and Development Labs, simplifies and speeds up reporting and analytics. It is initially delivered and is fully integrated into DB2 Version 10.5. Easy to set up and self optimizing, BLU Acceleration eliminates the need for indexes, aggregates, or database tuning to achieve top performance and storage efficiency. Because it is deeply integrated into DB2 Version 10.5, no SQL or schema changes are required to take advantage of this breakthrough technology.

Intelligent Miner feature is not supported with BLU Acceleration.

BLU Acceleration offers many advantages in a typical data warehouse environment. Generally, the number of read requests is much greater than the number of write requests in a warehouse environment. Therefore, column-organized tables have several advantages when compared to the row-organized tables:

• Read efficiency. In a column-organized table, only those columns that are needed are read, while each row is read in a row-organized table.
• Compression efficiency. Typically, a column-organized table features a compression ratio that is higher than a row-organized table compression ratio.
• Sorting and Indexing efficiency. Sorted columns compress more efficiently and range queries run faster.

DB2 Warehouse Version 10.5 supports the following features:

• Load, import, and ingest utilities move data from files into column-organized tables.
• Create new column-organized tables to receive mining results.
• Create suitable column-organized tables for data flow operator results.
• Optimize the normal read actions and write actions between row-organized tables and column-organized tables.

Support for DB2 Oracle-compatible mode

IBM DB2 for Linux, UNIX, and Windows features support for Oracle's SQL and PL/SQL dialects. Therefore, many applications that are written against Oracle are able to run against DB2 with minimal or no change. The SQL Warehousing Tool (SQW) for DB2 Warehouse, version 10.5 supports data sources and targets that are created in DB2 Oracle-compatible mode. Specifically, the following features are supported:

• The use of tables that are created in Oracle-compatible mode as a source table
• The use of tables that are created in Oracle-compatible mode as a target table
• The use of Oracle data types to create suitable tables
• Load, import, and ingest utilities move data from a file to a table created in Oracle-compatible mode
• The use of a database that is created in Oracle-compatible mode as a repository database

Secure Shell (SSH) protocol support in Administration Console

SSH is a protocol that is used for secure network communication. Within the DB2 Warehouse environment it is a useful tool to run remote operations. In the Manage System Resources of the Administration Console you can now create a system resource that uses an SSH connection.

Secure connection without a password in Design Studio

Previously, in the Design Studio, you must specify connection details in every operator. For instance, if you created a secure connection in the Secure FTP operator, you must specify all the connection details again if you wanted to use the same connection in Secure Command operator. Now, in DB2 Warehouse Version 10.5 you can create a machine resource to save the details of your secure connection.

In addition, you can create a connection that does not need a user password, but relies on a public key and private key to create a secure connection.