IBM Support

What's new with IBM DB2 Analytics Accelerator for z/OS V7

Product Documentation


Abstract

This document contains a brief description of the new functions shipped with each maintenance level of version 7

Content


What's new with maintenance level 7.5.4

  • Enhancements related to IBM Integrated Synchronization - Tech Preview
      • Overview
        Replication using IBM Integrated Synchronization now supports schema changes (Add/Alter column). If you add a column or alter a column of a Db2 for z/OS table that is replicated to the accelerator, this column is synchronized automatically with the corresponding accelerator-shadow table on the accelerator. It is no longer necessary to remove, re-add, and re-load the table to make the schema changes available on the accelerator.
      • Supported schema changes
        1. ALTER TABLE tablename ADD COLUMN colname [type] NULL;
        2. ALTER TABLE tablename ADD COLUMN colname [type] WITH DEFAULT [constant];
        3. ALTER TABLE tablename ALTER COLUMN colname SET DATA TYPE [extend length of current type]; - VARCHAR and VARGRAPHIC types only
        4. ALTER TABLE tablename ALTER COLUMN colname SET WITH DEFAULT [constant];
      • Notes and Requirements
        • This feature is "Technical Preview" and disabled by default in maintenance level 7.5.4. To enable it, contact IBM support by opening a support case.
        • If the schema change affects columns of the type TIMESTAMP, you must also install the PTF UI73158 for Db2 for z/OS (APAR PH31772)
        • The Db2 for z/OS user you specified as you enabled IBM Integrated Synchronization needs the following additional privileges: 
          • SELECT privilege on SYSIBM.SYSTABLES, SYSIBM.SYSCOLUMNS
          • SELECT, UPDATE privilege on SYSIBM.SYSACCELERATEDTABLES
  • Enhancements related to query acceleration
    • Improved handling of concurrent queries (and other operations) due to an improved internal lock management
    • Improved query performance through new database engine Db2 Warehouse 11.5.5.
    • New SYSPROC.ACCEL_COLLECT_TABLE_STATISTICS stored procedure
      You can request a statistics collection (RUNSTATS) on the specified set of tables to
      • Control when statistics on accelerator-only tables are collected
      • Control the timing of a statistics run on replicated tables
      • Enforce the collection of statistics on tables before critical queries or reports run that require these statistics
      Certain limitations apply for the use of the new stored procedure. For details see the corresponding description in the Knowledge Center.
    • Distribution key for accelerator-only tables (AOTs)
      As you create an AOT, you can now specify distribution keys by adding an SQL comment as part of the Db2 for z/OS ‘CREATE TABLE <AOT> .. IN ACCELERATOR…’ statement. This helps to improve the performance of queries that reference AOTs.
      Note: this function requires an installation of the PTF UI72862 for Db2 for z/OS (APAR PH30659).
    • Workload management improvements
      The IBM Z workload management importance level associated with a query is now mapped to a session priority within the accelerator’s workload management admission queue. This ensures that an accelerator admits important queries more quickly if multiple queries from the same Db2 subsystem are lined up for execution.
  • Enhancements related to accelerator-only tables (AOTs)
    • The new stored procedure SYSPROC.ACCEL_COLLECT_TABLE_STATISTICS helps to find the right time for the collection of statistics on AOTs.
    • High availability (HA) support for AOTs
      You can now create an AOT on multiple accelerators that are connected to the same Db2 for z/OS subsystem.
      Note: this function requires an installation of the PTF for Db2 for z/OS APAR PH30574.
    • As mentioned in the "Query acceleration" section above: Distribution key for accelerator-only tables (AOTs)
      As you create an AOT you can now specify distribution keys by adding an SQL comment as part of the Db2 for z/OS ‘CREATE TABLE <AOT> .. IN ACCELERATOR…’ statement. This helps to improve the performance of queries that reference AOTs.
      Note: this function requires an installation of the PTF UI72862 for Db2 for z/OS (APAR PH30659).
  • Enhancements related to operation
    • Improved stability, resource usage, and storage management 
    • Stored procedure SYSPROC.ACCEL_REMOVE_ACCELERATOR now issues a -STOP ACCEL command before an accelerator is removed from a Db2 subsystem.  
  • Enhancements related to diagnostics & serviceability
    • Extended diagnostic output in "Show explain details" to diagnose query performance issues that are related to missing statistics
      Using "Show explain details" in IBM Db2 Analytics Accelerator Studio now provides extended diagnostic output to analyze query performance issues that are related to missing statistics.
  • Enhancements in integrated components 



Previous What's new information


What's new with maintenance level 7.5.3

  • Incremental updates using IBM Integrated Synchronization
    • Db2 for z/OS IP address and port change support using the Configuration Console
    • Support of tables with "NULLable" replication key columns
    • High availability for Integrated Synchronization in Data Sharing Group environment
      Using TIMEDAFFINITY option for DDVIPA address of Data Sharing Group.
    • Improved resiliency by reducing the number of situations that may cause tables to get suspended from replication.
      An improved error handling and an implemented retry logic for specific 'waiting for data' situations now ensure that tables are not suspended from replication any more. Replication continues while the waiting situation is handled internally.
  • Operation
    • New option in the Configuration Console to restart the IIAS appliance on advice by IBM Support
    • Compression improvement for small tables with less than 10240 rows
    • New display and filter option "taskID" available for queries in Query Monitoring section in Accelerator Studio to simplify data collection for diagnostic purposes as advised by IBM Support
    • Improved disk space management to prevent disk full conditions
      When the total disk space used for data on the accelerator exceeds 75 % of available disk space and more than 5 % of the used disk space is used by free pages then the accelerator is automatically triggering a "ALTER TABLESPACE REDUCE" action to return the free pages back to the operating system
    • Consistent space monitoring
      All available disk space usage monitoring counters are now based on the used pages of the tables and their corresponding synopsis tables on the accelerator, for example:
      • Table size displayed in the Tables section in Accelerator Studio
      • Disk space that a paired Db2 subsystem uses on the Accelerator displayed in Accelerator Studio, DISPLAY ACCEL command, or SMF records' monitoring counters
      • Total disk space used on the Accelerator displayed in Accelerator studio, DISPLAY ACCEL command or SMF records' monitoring counters
  • Performance
    • Fetch performance improvement for queries with large result sets (will be enabled by IBM Support if required)
    • Overall query performance improvements by enhanced Db2 Warehouse settings
    • Improved performance of queries that imply EBCDIC data conversions from numeric values to character values
    • Improved process of automatically copying statistics,  after full table reload and before new statistics have been collected
    • Performance improvements for Accelerator on Z: Stay tuned - will be released soon
    • Load performance improvements for multi-node setups (Accelerator on Z)
    • HyperPAV and zHPF are mandatory when using FICON attached storage for the Accelerator on Z. All-flash storage is strongly recommended for both FCP and FICON attached storage.
  • High Performance Storage Saver enhancement
    • Distribution and organizing keys can be changed for archived tables
      This feature was introduced in 7.5.2 as technical preview (See what's new section of 7.5.2) and is now available for production usage and enabled by default.
      If you change a distribution keys or organizing keys (from Db2 Analytics Accelerator Studio, Data Server Manager, or by running the SYSPROC.ACCEL_ALTER_TABLES stored procedures) for an accelerator-archive table, it is applied to the active and archive part of the accelerator-archive table.

      Note: An organizing key defines the sorting/clustering of rows within a table. The sort order is applied when invoking the ACCEL_ALTER_TABLES stored procedure. This means that partitions archived after the alter keys/sorting are not yet sorted until you invoke the ACCEL_ALTER_TABLES stored procedure again to include the new archived partitions. To make sure that organizing keys are preserved for all archived partitions, make the change after archiving.

  • Accelerator on Z new functionality
    • Support for GDPS 4-site configurations with GDPS 4.3
    • ZFCP Support: Allow multiple ZFCP storage environments such that (mirror-)copy-based deployments can be supported for ZFCP as well. (Current restriction: DASD only) 
    • HyperPAV support with GDPS
      Parallel Access Volumes (PAV) is the concept of using multiple devices or aliases to address a single ECKD disk device. Previous releases did not support HyperPAV aliases when GDPS triggered a fail-over to another site or region. Now the GDPS configuration section allows HyperPAV alias definition per site.
    • Support for storage serial #
      So far a storage configuration was defined based on the associated boot disk device ID. Based on this ID, additional disks for data and runtime pools are selected when the systems starts up. Storage subsystems could have the same disk IDs, particularly for storage mirroring or when a storage subsystem should be replaced. To allow identification of disks, the "key" for storage definition is enhanced to include not only the device ID but also the storage subsystem serial number. Specification of this serial number is optional, though.
    • Boot disk, Storage, and CPC/LPAR migration
      Extended capabilities are provided to move to different storage (disks) and also to other LPARs by specification of "runtime environment" and "storage environment". For example, the same SCSI (FCP) disks are accessible on another LPAR under a different device ID. To support restart of the Appliance on this other LPAR, the correct device ID needs to be accessed (and configured in the "runtime environment"). So far the configuration concept was also assuming a "primary" configuration and derived "storage maps" from these. In case of a permanent move to a different environment the options to handle the new environment (e.g., adding disks) were limited.

      Examples of corresponding use cases:

      • Manual HA/DR scenarios (where disks are mirrored by another component and manual LPAR start is acceptable or automation is implemented differently)
      • Tests on a flash copy of the data
      • Storage subsystem migration with new device IDs


What's new with maintenance level 7.5.2

  • Incremental updates using IBM Integrated Synchronization
    • Query acceleration is automatically disabled for suspended tables.
      This prevents accelerated queries accessing tables where replication is stopped, thus preventing querying of stale data.
      Important: Please consider Release Notes information about proper configuration of this feature here.
    • DML statement ALTER TABLE DATA CAPTURE CHANGES is now executed automatically.
      If replication for a table is enabled using Db2 Analytics Accelerator Studio, Data Server Manager, or the ACCEL_SET_TABLES_REPLICATION stored procedure, the DML statement ALTER TABLE DATA CAPTURE CHANGES is now executed as part of the enablement step. It is no longer required to execute this statement separately within Db2 for z/OS.

  • Db2 Analytics Accelerator as target for CDC-based replication - Tech Preview
    Using IBM InfoShere Data Replication for z/OS (CDC) you can now replicate data from any supported source system (for example IMS, VSAM, Oracle, or MS SQL Server) directly to Db2 Analytics Accelerator without the need to have this data in Db2 for z/OS first. Data is replicated into an Accelerator Only Table (AOT) or an Accelerator shadow table.
    This comprises a setup in which the Access Server and the CDC replication engine for the accelerator database run on a separate server (an Intel machine or a Linux on Z LPAR) to manage the subscription and apply the data to the tables on the accelerator.
  • Distribution key and organizing keys can be changed for archived tables - Tech Preview
    Until now, the change of the distribution key or the organizing keys (from Db2 Analytics Accelerator Studio, Data Server Manager, or by running the SYSPROC.ACCEL_ALTER_TABLES stored procedures) was possible only for the active partitions of an accelerator-archive table, but not for the archived partitions. With this maintenance level, the distribution or organizing keys can be changed for both parts of an accelerator-archive table  if configuration parameter ENABLE_ALTER_KEYS_FOR_ARCHIVE_TABLES=TRUE has been set. 
    Note:
    An organizing key defines the sorting/clustering of rows within a table. The sort order is applied when invoking the ACCEL_ALTER_KEYS stored procedure. This means that partitions archived after the alter keys/sorting are not yet sorted until you invoke the ACCEL_ALTER_KEYS stored procedure again to include the new archived partitions. To make sure that organizing keys are preserved for all archived partitions, make the change after archiving.
  • Query performance improvements
    • Improved handling of parallel queries
    • Optimized handling of NULL values in outer joins
  • Stability improvements
    • Workload Management (WLM) improvements within in the accelerator
      • Isolation of load and query workloads in order to provide sufficient resources to each application environment in concurrent high-workload situations.
      • Automatic canceling of long-running queries which block the admission of further queries.
  • Improvements specific to Accelerator on Z
    • Support for FCP/SCSI boot disks. In addition to data pools, boot disks can now be FCP/SCSI disks as well (for both, single, and multi-node deployments)
    • New administration user interface layout with consolidated data node log messages for multi-node deployments
      • Network test option to diagnose connection problems
  • Db2 z/OS Built-in functions

    (requires Db2 z/OS function level 507 with APAR PH24371)

    • Additional pass-through functions
      • REGR_SLOPE, REGR_INTERCEPT, REGR_ICPT, REGR_R2, REGR_COUNT, REGR_AVGX, REGR_AVGY,  REGR_SXX,  REGR_SYY,  REGR_SXY,  ADD_DAYS,  DAYS_BETWEEN, NEXT_MONTH, BTRIM
    • Improved acceleration support for queries using Db2 for z/OS built-in functions
      • PERCENTILE_DISC, ROUND_TIMESTAMP


What's new with maintenance level 7.5.1

  • Replication based on IBM Integrated Synchronization
    • Support for changing the Db2 credentials for IBM Integrated Synchronization on z/OS
    • Improved throughput when replicating very large transactions
    • Improved feedback during enablement of Integrated Synchronization: checks ensure that the specified port is indeed the secure DDF port
    • Optimized internal structures: Modified space for temporary data (e.g. change spilling)
    • REORG DISCARD and LOAD DD DUMMY operations on range-partitioned tables do not require reloads of the corresponding accelerator shadow-table
    • JCL sample jobs for SSL setup
  • CDC-based replication
    • Several improvements in communication stability
  • Improvements specific to Accelerator on Z
    • Introduction of GDPS Metro-Global 3-site support for HA/DR
    • Improved startup sequence and timing, specifically for multi-node deployments
  • Query performance improvements
    • Further reduction of outliers
    • Better spill-to-disk heuristics
    • Automatic collection of statistics (runstats) after an initial load to avoid performance issues in connection with statistics-sensitive queries
    • Automatic copying of statistics from the previous table during a full table reload in order to avoid a bad query performance due to missing statistics
  • Load improvements
    • Elimination of load failures caused by concurrent locks in the course of a partial table reload followed by a table cleanup
  • Platform improvements
    • Stability improvements through the use of overlay2 filesystem drivers
  • GUI
    • Data Studio based GUI
      • Menu item "Encryption Details": there are two new links to transfer or remove certificates
      • Menu item "Query Monitoring": there are two new selections 'Capture query environment data' and 'Capture query environment and performance data'
    • Data Server Manager based GUI
      • Menu item "Encryption Details": there are two new links to transfer or remove certificates
      • Force option to stop an accelerator
      • Alter Keys function: ability to order by organization key
      • Add Tables function: option to display unsupported tables


What's new with release 7.5

  • Integrated Synchronization
    • Data coherency to help ensure transactionally consistent analytical query results
    • Enterprise-grade hybrid transaction and analytical processing
    • Simplified administration, improved performance, lower cost, and reduced CPU and memory usage

  • Support for multi-node accelerator on Z
    • Multi-node deployment delivers scalability for demanding workloads
    • Optimized for large workloads while providing flexible adjustment of resources

  • Sample program that allows customers to load Db2 for z/OS data to multiple Accelerators in parallel
    Leverages the ACCEL_LOAD_TABLES callback interface

  • Performance, robustness and servicability improvements for both deployment options, such as
    • Enhanced Text Compression
    • Sophisticated handling of large intermediate results during query-processing to
      • Minimize out-of-memory situations
      • Further reduce query outliers
  • Further GUI enhancements for V7 accelerators
    • Data Server Manager V2.1.5.4 based GUI
      • Improved support for federated tables
      • Storage Saver functionality enabled
      • Support for software upgrade process indicator and essential upgrade
    • Data Studio based GUI
      • Displays the replication latency in seconds instead of low, medium, high


What's new with maintenance level 7.1.9

  • Federation
    Enable read access to all data (from multiple Db2 subsystems) on one accelerator through a single DB2 subsystem.

  • True HTAP support
    1. Support for Static SQL
      Db2 12 enhancements - Prerequisite: APAR PH14116
    2. Shorter wait times with object-level wait
      With Db2 12 function level 505 and APAR PI98126, queries with WAITFORDATA specification only wait for committed data that is relevant to the query.
       
  • Integrated synchronization as technical preview
    Note: If you like to try this feature, contact IBM support by opening a case.
     
  • Performance and robustness improvements for both deployment options
    1. Dramatically improved load performance on IIAS by cluster load
    2. Workload balancing between V5 and V7 accelerators
      (Db2 12 enhancement via APAR PH05477)
    3. Workload management by better isolation between Db2 subsystems and with regard to replication
    4. Compression improvements
      Autogrooming to compress uncompressed rows that have been loaded or inserted when the compression dictionary was not yet available (for example during initial load)
    5. Improved query performance when using the TIMESTAMPDIFF function
    6. Save Trace: Capture content of all SYSACCEL catalog tables
    7. Warning indicator for tables with missing statistics
    8. New stored procedure result set that contains the full message text

  • Accelerator on IIAS-specific enhancements
    1. Software upgrade improvements
      1. Essential update: ability to upgrade container only (without IIAS firmware)
      2. Progress info during upgrade

  • Accelerator on Z-specific enhancements
    1. Accelerator setup (storage and network settings) through configuration files
    2. Automatic discovery and configuration of HyperPAV aliases
    3. Improved stability when disk is full
    4. Support for shell LPARs for disaster recovery
    5. Definition of static routes for network connections
    6. Support for multi-node accelerator as technical preview
      Note: If you like to try this feature, contact IBM support by opening a case.


 

What's new with maintenance level 7.1.8

  1. Integrated Synchronization as technical preview
    Note: If you would like to try this feature, please contact IBM.
     
  2. High Performance Storage Saver (HPSS)
  3. Major compression improvements
  4. Real to Double support during load
  5. Call-back interfaces for external load programs
  6. Stability improvements

What's new with maintenance level 7.1.7

  1. High Performance Storage Saver (HPSS) as technical preview.
    Note: If you would like to try this feature, please contact IBM.

  2. Compression improvements via full table reload

  3. Partial reload of replicated tables

  4. Build-In functions (further exploitation)
    Note: Db2 function level 504 is required.

  5. Accelerator on Z: Small deployment system support

What's new with maintenance level 7.1.6

  1. Encryption of Data in Motion: Incremental Update support
    For more information see the corresponding chapter in the Knowledge Center .

  2. Call-Home for IIAS via HMC (HTTPS proxy on HMC)
    For more information see the corresponding chapter in the Knowledge Center .

What's new with maintenance level 7.1.5

  1. First set of OLAP built-in functions
    Some built-in functions referenced in SQL statements and only supported by Db2 Warehouse can now be accelerated. This requires Db2 function level 504.

  2. Local Date Format
    The accelerator now provides support for local DATE format. Queries with expressions that return a DATE data type are eligible for acceleration.
    The use of this function requires installation of the PTF for Db2 APAR PH00933 .

  3. Multiple-row insert for accelerator-only tables (AOTs)
    Allows faster inserts into AOTs using JDBC, ODBC, or local SQL applications.
    The use of this function requires installation of the PTFs for Db2 APARs PI96258 and PH02876 .

  4. New statement level monitoring counters (IFCID 316 and 3)
    Accelerator Statement Level Monitoring and accounting statistics for HTAP dynamic queries are now supported.
    The use of this function requires installation of the PTF for Db2 APAR PH00574 .

  5. Accelerator on IBM Z
    IBM Db2 Analytics Accelerator for z/OS, V7.1 on IBM Z now supports:
     
    • GDPS support for fail-over scenarios
      IBM Db2 Analytics Accelerator on IBM Z provides integration with GDPS MTMM environments to support fail-over from an active accelerator to a passive accelerator. This includes support for swap of GDPS Metro mirrored storage and site-specific network definitions.
    • Script-based install image handling
      So far installation of the container image of Accelerator on IBM Z was performed manually with a web admin user interface. An alternative for image deployments is provided using a set of Python sample scripts. This is especially useful when an administrator's web browser is remote.


What's new with maintenance level 7.1.4

  1. HTAP support
    For more information see the corresponding descriptions in the Knowledge Center.
    For information concerning the Db2-PTFs required for HTAP support, see the following web page.


What's new with maintenance level 7.1.3

  1. Further performance improvements for load jobs, queries, and incremental updates

  2. Query support for BINARY, ROWID and DECFLOAT data types when using Db2 12 for z/OS
    The use of this function requires installation of the PTF for Db2 12 APAR PI98408 .

  3. Recursive SQL support


What's new with maintenance level 7.1.2

  1. Incremental update function now available
    Please find further details here.

  2. SSH connections to Db2 Analytics Accelerator Console
    Please find further details here.

  3. Query support for BINARY, ROWID and DECFLOAT data types, plus associated (scalar) functions and expressions, when using Db2 11
    Please find further details here.

  4. Querying of data at isolation level 'uncommitted read (UR)' when table is not replicated and not an accelerator-only table

  5. New AQT20014I messages as part of SYSPROC.ACCEL_LOAD_TABLES

  6. Enhanced interface for third-party tool integration for loading of data

  7. Accelerator on IBM Integrated Analytics System (IIAS)
    IBM Db2 Analytics Accelerator for z/OS, V7.1 supports the new IBM Integrated Analytics System (IIAS) M4002
    • M4002 certification M4002-003
    • Growth on Demand function
    Please find further details here.

  8. Accelerator on IBM Z
    IBM Db2 Analytics Accelerator for z/OS, V7.1 on IBM Z now supports:
    • User management support for multiple administrators
    • Enhanced support for on-the-fly configuration changes (such as IP address, network devices, VLAN, and so on)


What's new with maintenance level 7.1.1

  1. Incremental Update is included as non-confidential Technology Preview Code and may be evaluated by Licensee.

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SS4LQ8","label":"DB2 Analytics Accelerator for z\/OS"},"Component":"--","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"7.1.0\n7.5.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
19 February 2021

UID

ibm10733587