Db2 for z/OS & Db2ZAI

Db2 for z/OS and its ecosystem

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

View Only

Back to Library

Mon December 02, 2019 01:01 PM

Pat Zakhar

Differences between Db2 Analytics Accelerator V5 and V7

Watson Chris | Original Post date: Jan 9 2018 ‎

Modified on Oct 7, 2019 by Watson Chris
Updated on Dec 3, 2019
_____________________

"What is changing between V5 and V7?" Here is an overview ...

Architecture

	Accelerator Version 5.1 on PDA	Accelerator Version 7.1
Storage	Integrated Spinning Disks	IIAS: Integrated Flash Disks Z: Customer provided external storage
Query Acceleration	Through MPP Architecture, FPGAs	Through MPP Architecture and Columnar database (Db2 Warehouse)
Software components on Accelerator	Multiple installable components	Single docker image with preinstalled components
Accelerator Hardware	Physical Appliance	IIAS: Physical Appliance Z: Customer provided Hardware (z15, z14, z13, LinuxOne)
Supported Db2 z/OS versions	Db2 10, Db2 11, Db2 12	Db2 11, DB2 12

Data Types
Many Db2 data types are supported. The table below only lists the data types for which differences exist.

	Accelerator V5 on PDA	Accelerator V7
EBCDIC MBCS, GRAPHIC	Converted to UTF 8	Supported natively
TIMESTAMP(12)	Truncated to precision 6	Supported natively
FOR BIT DATA subtype	Supported for EBCDIC only	Supported natively for EBCDIC, UNICODE, ASCII
DECFLOAT, BINARY, ROWID	Not supported	Supported
VARCHAR	Supported	Supported The maximum length in Db2 for z/OS is 32704. In Db2 Warehouse, it is 32592. A table load fails and message AQT20052E is displayed if a string exceeds the Db2 Warehouse limit.

The complete list of supported data types for each version is here:

SQL
V7 lifted some of the SQL restrictions that V5 has and therefore provides a better SQL support. The following table contains the details:

	Accelerator V5 on PDA	Accelerator V7
Correlated subqueries	Only a small subset supported	All types supported including table expressions with sideway references
Recursive SQL	Not supported	*Supported*
TIMESTAMP value 24:00:00	Mapped to 23:59:59.999999	Supported natively
Scalar functions	Some not supported when using specific datatypes, e.g. MIN/MAX, DAY, LAST_DAY, BIT*, TIMESTAMP_ISO, VARIANCE/STDDEV/… with UNIQUE clause,	Improved support
HEX() function	Not supported	Supported
Mixed Encodings	Only supported to add UNICODE tables after EBCDIC table has already been added (required to set AQT_ENABLE_MULTIPLE_ENCODINGS environment variable)	Adding EBCDIC tables when UNICODE tables already added is additionally supported. EBCIDIC ->UNICODE UNICODE->EBCDIC
Mixed Encodings	SQL joins between tables in UNICODE and EBCDIC format is not possible
CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_DATE	Supported	Supported with improved accuracy (no longer dependent on time synchronization)
DATE data type in LOCAL format	Supported	Supported
Passthru capabilities of Db2 Warehouse builtin OLAP functions not available in Db2 for z/OS (e.g. LAG, LEAD, REGEXP, ….)	Not available	Supported (Prereq: Db12 FL504)

Query execution

	Accelerator V5 on PDA	Accelerator V7
Performance	Minimum accelerated query response time ~400ms	Minimum accelerated query response time ~30ms
Distribution Key	Default: Random distribution, no key. A key is only set by default if the table is enabled for incremental update based on available unique constraints and primary key	Default: column list chosen by the Accelerator, based on available unique constraints and primary key on Db2/z table (DISTRIBUTE BY HASH) DISTRIBUTE BY RANDOM used as fall-back.
Organizing Key	Table clustered according to specified organizing key columns	One-dimensional sort on the specified list of organizing key columns
Show execution plans	Netezza Explain Output	Db2exfmt and db2look output
Availability of Execution Plans	In Accelerator trace available for all queries of the last 2 weeks	In Accelerator trace only available if manual Explain has been executed before
ZPARM: QUERY_ACCEL_OPTIONS	Supported options: 1,2,3,4,5,6,7,8,10	Supported options: 2,4,7,8,10 Non relevant options: 1,3,5,6
ZPARM/special register/BIND option: GET_ACCEL_ARCHIVE	Supported	Supported

Accelerator table types

	Accelerator V5 on PDA	Accelerator V7
Accelerator-shadow tables	Available	Available
Accelerator-archive tables	Available	Available
Accelerator-only tables	Available	Available Same improvements as for improved SQL support/datatypes apply here

Load

The accelerator can be loaded using the ACCEL_LOAD_TABLES stored procedure and providing a set of tables to be loaded as the input parameter:

	Accelerator V5 on PDA	Accelerator V7
Implementation Mode	Load, load stream/parallelism tuning required by user for optimal performance. Within one stored procedure call tables are loaded serially. Partitions of a table are loaded in parallel. For loading multiple tables in parallel multiple parallel stored procedure calls are required.	SMART Load, no more load stream/parallelism tuning. Maximal throughput without any manual tuning involved. Within one stored procedure call tables and partitions are loaded in parallel. Multiple stored procedure calls are not required to load tables in parallel to achieve optimal performance.
Load throughput	Comparable
Loading small/empty tables	Supported	Supported, and much faster

Incremental Update

	Accelerator V5 on PDA	Accelerator V7
Method	Change Data Capture of InfoSphere Data Replication	Integrated Synchronization or Change Data Capture (CDC) of InfoSphere Data Replication
Implementation Mode	Default: Replication Configurable Options: Continuous Replication Parallel Apply Suspending faulty tables	Default: Continuous Replication, Parallel Apply, Suspending faulty tables Significantly reduced number of configuration options
Latency	Default 60s	-Default 31s (CDC) -Single digit number of seconds (Integrated Synchronization)
Eligible tables	All tables, even when row uniqueness is not enforced	Only tables with enforced uniqueness (primary key, unique index) or a defined informational constraint (during ADD_TABLES)
Change capture agent IP address	Available	Not available
Disable query acceleration for suspended tables	Available	Not available

Installation

	Accelerator V5 on PDA	Accelerator V7
Accelerator Software components	Single install per component by IBM Engineer	Contained in Docker container. IIAS: Initial Container Deployment by IBM Engineer Z: Initial Container deployment by customer via SSC installer
Software Update	Update of single individual components: Accelerator Server, Access Server, Replication Engine, NPS, INZA via Transfer/Apply. Synchronous Action.	IIAS: Update of single bundle file via Transfer/Apply. Asynchronous Action. Check Installation Log for Success or Problems. Z: Update of single bundle file via SSC installer
Delivery of Accelerator Software Updates	Via ShopZ	Via Fix Central
SMP/E packages	Big (because contains all Accelerator components to be transferred/applied)	Small, only Stored Procedure code
Growth-on-Demand	Available	Available for IIAS (M4002-003 only)
Scale Out	Not Available, Machine exchange required	I: In-place-expansion Z: Assign more memory/IFLs to SSC LPAR
VLAN	VLAN unaware	IIAS: VLAN aware, one VLAN supported only