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.5

  • Enhancements related to IBM Integrated Synchronization
      • Automatic download of required certificates from Db2 for z/OS to the accelerator
        During the enablement of Integrated Synchronization for a Db2 system from the Configuration Console, the complete certificate chain of trust is now automatically downloaded from Db2 for z/OS to the accelerator through the specified secure DDF port. Fingerprints of the downloaded certificates are displayed to the user to be confirmed for correctness.
        This new automatic download step simplifies the Integrated Synchronization setup process, because no manual RACF export and transfer of the signer certificate is required anymore. In addition, due to the download of the complete certificate chain, cascaded certificates are supported now.
      • Table is removed from accelerator after DROP TABLE execution on Db2 for z/OS
        If you execute a DROP TABLE statement for a Db2 for z/OS table that is replicated to the accelerator with Integrated Synchronization, the table is now automatically removed from the accelerator by the SYSPROC.ACCEL_REMOVE_TABLE stored procedure. This requires that execution privileges for SYSPROC.ACCEL_REMOVE_TABLE must be granted to the corresponding Db2 user. This user is specified when you enable a Db2 subsystem for replication with IBM Integrated Synchronization.
        The required privileges are described here.
  • Enhancements related to query acceleration and performance
    • Improved statistics collection
      The table statistics collection features
      • Automatic regular statistics collection (called 'auto statistics')
      • Automatic statistics collection after an initial table load (called 'early statistics')
      • Automatic copying of statistics after a full table re-load (called 'copy statistics')
      have been improved further by:
      • Improved error handling to reduce the number of statistics collection failures
        • Retry early statistics in case of a deadlock or timeout during the statistics collection
        • Retry copy statistics steps in case of a deadlock or timeout during the statistics collection
        • Avoid auto statistics runs during a table load (which may be causing incorrect cardinality estimates)
      • Improved table statistics warning
        In case of a statistics collection failure, the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure now returns the following flags correctly: 
        • tableBackendStatisticsCollectionTimestampCollected
        • archiveBackendStatisticsCollectionTimestampCollected 
        This allows you to detect missing statistics (in IBM Db2 Analytics Accelerator Studio obeying the table statistics warning or by calling the SYSPROCS.ACCEL_GET_TABLES_INFO stored procedure), so that you can take corrective action (for example, call the SYSRROC.ACCEL_COLLECT_TABLE_STATISTICS stored procedure to initiate a statistics collection manually).
    • Fetch performance improvements for queries with large result sets from locally attached Db2 for z/OS clients, such as DSNTIAUL
      Asynchronous fetching is a new method to improve the fetch performance for queries that return large result sets. Especially for queries that are executed from local applications, such as DSNTIAUL, the fetch performance of large result sets can be drastically improved by asynchronous fetching.
      In maintenance level 7.5.5, asynchronous fetching is turned off by default because it may impact the performance of certain queries negatively. Generally, a negative impact can be observed if the spill-to-disk method is used to fetch large result sets. Queries executed by remote applications with a slow fetching mechanism often benefit from the spill-to-disk method.
      For an assessment of your queries with regard to the performance of large result set fetching, contact IBM support. IBM support will enable asynchronous fetching for you if this is advisable.
    • New option for QUERY_ACCEL_OPTIONS ZPARM to enable compatibility mode
      If you set the QUERY_ACCEL_OPTION ZPARM to the value 97, you enable the compatibility mode, which makes product version 7 compatible with product version 5. In compatibility mode, queries are routed to a V7 accelerator only if they would have been routed to a V5 accelerator as well.
      For a detailed description see Db2 for z/OS APAR PH29627. Mind that this new option is available for Db2 12 for z/OS only, and only if you install the corresponding PTF for APAR PH29627.
      You can enable the compatibility mode also for accelerator maintenance levels lower than 7.5.5.
  • Enhancements for Db2 Analytics Accelerator on Z
    By default, the Admin User Interface of Db2 Analytics Accelerator on Z uses a self-signed certificate for secure web traffic ("https://"). This certificate is provided by IBM. To meet security standards that might exist for your organization, you can replace this certificate with a custom secure-sockets-layer (SSL) certificate. This custom certificate can be signed by your organization or by an external certificate authority (CA).
  • Enhancements in IBM Db2 Analytics Accelerator Studio
    • Query Monitoring
      Using a new preference setting (Select Preferences > IBM Db2 Analytics Accelerator > Accelerator View from the main menu) you can now truncate query statement text and can improve the Query Monitoring refresh time if you have many queries with a very long SQL statement text.
      In the background the setting triggers the use of SYSPROC.ACCEL_GET_QUERIES instead of the SYSPROC.ACCEL_GET_QUERIES2 stored procedure.
    • Accelerator view
      The default refresh interval for the Accelerator view has been changed from one minute to "Automatic off". This has been done to lower the frequency of stored procedure calls from Db2 Analytics Accelerator Studio. You can change the default to another value using the preference setting (Select Preferences > IBM Db2 Analytics Accelerator > Accelerator View from the main menu).
  • Enhancements in Accelerator functionality
    • Federation
      You can now use RACF authorization instead of Db2 native authorization to grant or revoke federated access to accelerator tables by using:
      • the stored procedures ACCEL_GRANT_TABLES_REFERENCE and ACCEL_REVOKE_TABLES_REFERENCE, or
      • controls in Db2 Analytics Accelerator Studio.
        A sample JCL job AQTSFEDR is included in the SAQTSAMP library. It can be customized to create a RACF profile and set the required permissions. 
  • Information related to incremental update function using IBM InfoSphere® Data Replication for z/OS
    IBM Db2 Analytics Accelerator for z/OS V7.5 offers the following options to implement incremental updates:
    • IBM Integrated Synchronization
    • IBM InfoSphere® Data Replication for z/OS (CDC)
    Integrated Synchronization has a couple of proven advantages compared with CDC such as:
    • Lower replication latency, simplified installation, packaging, and administration
    • Enhanced Db2 Utilities support for REORG DISCARD and LOAD DD DUMMY
    • Schema change support for ADD and ALTER column without remove, re-add, and re-load the table
    We would like to inform you that our future investment with respect to integration, enhancements, and new functions concerning incremental updates will prefer IBM Integrated Synchronization. With maintenance level 7.5.5, incremental updates based on IBM InfoSphere® Data Replication for z/OS has been deprecated. It is still supported, but after having migrated to Db2 Analytics Accelerator for z/OS V7.5.5, we encourage you to consider phasing-out the use of CDC for incremental updates on the accelerator and to move to IBM Integrated Synchronization.
    We are considering the removal of the option to use CDC for incremental updates in a future release.



Previous What's new information


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
      • the installation of the PTFs for the following Db2 for z/OS APARs: PH30574, PH31723 and PH33015.
      • the activation of Db2 12 for z/OS function level 509.
      For detailed descriptions of the new Db2 12 capabilities in function level 509, and specific activation details, see "Function level 509" in IBM Knowledge Center.
    • 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 


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

[{"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:
25 March 2021

UID

ibm10733587