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

	TTLSEnvironmentAction DB2SslEnv
	{
	   TTLSKeyRingParms
	   {
	       Keyring           DB2KEYRING
	   }
	   TTLSEnvironmentAdvancedParms                                             
	   {                                                                        
	       TLSv1             Off
	       TLSv1.1           Off
	       TLSv1.2           On
	       ClientAuthType    PassThru
	   }       
	   HandShakeRole         Server
	   TTLSCipherParmsRef    Db2SslCipherParms
	}
	

• Enhancements related to IBM Integrated Synchronization
  • Toleration of REORG REBALANCE operation in Db2 for z/OS on replication-enabled partitioned-by-range (PBR) tables.
    If a REORG REBALANCE operation is executed on a replication-enabled table, Integrated Synchronization now continues the processing instead of suspending the table from replication.
    Note:
    • Since a REORG REBALANCE operation changes the limit keys of a PBR table, a subsequent execution of a REORG DISCARD or LOAD DD DUMMY will suspend the table from replication. In this case the table must be fully reloaded to continue replication.
    • The REORG REBALANCE toleration mode is not enabled by default. It has to be activated for each accelerator paired to the applicable Db2 subsystem. Open a support case to have IBM support turn on the toleration in a remote maintenance session. The activation requires a restart of the incremental update processes.
• Enhancements related to Query Acceleration and Performance
  • The new Db2 Warehouse engine incorporated in Accelerator maintenance level 7.5.12 provides
    • a configuration parameter to improve the performance of automatic statistics collection. See Known Issue DT187773 for further information.
    • query performance improvements.
• Enhancements related to load of accelerator-shadow tables
  The load function has been reworked to eliminate load hang situations and to improve load performance.
• Enhancements related to operation (Jan 31, 2024)
  The accelerator-internal monitoring has been improved to track the temp space usage high watermark in the accelerator trace file. Thus IBM Support can help customers to determine the required amount of temporary or transient storage for an Accelerator on IBM Z installation.
  
  The menu "Run Accelerator Functions" of the Configuration Console has been amended with option "(12) - Fodc Dump File Management". The serviceability feature behind this option

  • is only to be used in the context of a support case when requested by IBM support
  • manages Db2 Warehouse FODC dump files for further analysis by IBM Support.
• Enhancements for Db2 Analytics Accelerator on Z
  • Use of local NVMe (non-volatile memory express) storage as transient storage for Db2 Analytics Accelerator on LinuxONE.
    Using local NVMe storage as transient storage for temporary files is highly recommended for large multi-node Accelerator deployments to improve performance and stability.
    Introduced with maintenance level 7.5.8 as a technical preview, the use of NVMes in production is now officially supported with maintenance level 7.5.12.
    The exploitation requires adaptation of the JSON configuration file:
    • The new keyword “transient_storage”:”NVMe” must be set in all the “runtime_environments” sections.
      If specified, all available NVMe devices within a LinuxONE system (LinuxONE local storage) are used as temporary storage.
    • For use of NVMes, the parameter “temp_working_space” has to be removed from the JSON configuration file.
  • Optional separate storage pool on external storage for transient data.
    The new optional keyword “transient_devices” in the “storage_environment” section of the JSON configuration file facilitates the definition of a separate storage pool for transient data on FCP- or FICON-attached storage.
    
    Advantages:
    • Transient data is not written to the data pool anymore.
    • The data pool can be sized more precisely according to the amount of data to be loaded and synchronized to the Accelerator. Thus, out-of-space conditions in the data pool due to, for example, high tempspace usage are prevented.
    Note:
    • If “transient_devices” and “transient_storage” are defined in the JSON configuration file, the setting of “transient_storage” takes precedence and “transient_devices” is ignored.
    • Different storage pools can be deployed on different storage types. For example, the data pool could be deployed on FICON-attached storage and the transient pool on FCP-attached storage.

• Enhancements for Db2 Analytics Accelerator on Z
  • Support of RoCE (RDMA over Converged Ethernet) cards for inter-node communication in multi-node installations (GA, already available with V7.5.10.1)
    Using RoCE cards reduces IFL resource contention, ensures predictable query runtimes, and enhances operational stability.
  • Ability to set up a multi-node installation consisting of 4 LPARs
    Supported only for very large installations on z16, for example if a full CEC with 4 drawers is used for a Db2 Analytics Accelerator multi-node installation only.
    Provides improved performance by avoiding cross-drawer memory access.
  • Changed default value for temp_working_space parameter in JSON configuration file from unlimited to automatic
    The new default value is used for new Accelerator on Z installations. For existing installations, it is recommended to set the temp_working_space parameter either to automatic or to a fixed size. The previous default value of unlimited is deprecated starting with Accelerator maintenance level 7.5.11.
    See Installing and starting the appliance (single-node setup) for a description of the temp_working_space parameter.
• Enhancements for Db2 Analytics Accelerator on IIAS
  • Enhanced DSNX881I messages to inform about closed hardware alerts
  • Enhanced configuration options for the Call Home feature
    Ability to configure and use a proxy server between the zSystem Hardware Management Console (HMC) and the IBM support server (see also APAR PH25361). Use the ‘Configure Call Home’ menu item of then Configuration Console to configure the location and port of the proxy server.

    Notes:

    • For security reasons the configured values are not displayed using the ‘Display settings’ menu item.
    • A manual configured proxy server setup on a lower Accelerator maintenance level is preserved when upgrading to Accelerator maintenance level 7.5.11.

• Enhancements related to Query Acceleration and Performance
  • Improved performance for fetching of large result sets that have been spilled to disk 
  • New non-routing option in Db2 12 and Db2 13 for z/OS for correlated subqueries (Db2 for z/OS APAR PH52030).
    If you want to disable query offloading for these types of queries, contact IBM Support.
  • Integration of database engine Db2 Warehouse 11.5.8.
• Enhancements related to Operation and Maintenance
  • The timestamp of when a table was last used by a query is now provided by the stored procedure ACCEL_GET_TABLES_INFO
    Stored procedure ACCEL_GET_TABLES_INFO provides the following new information per table:

    • lastAccessTimestamp: Timestamp when the table was last used by a query
    • accessCount: number of queries, which used the table since the table was added to the accelerator
  • Ability to use a different cipher for encryption-of-data-in-motion for better encryption performance
    If you have configured encryption-of-data-in-motion, then the ciphers TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384 and TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 might result in better encryption performance than the implemented default ciphers.
    To use these ciphers:

    1. Change the TTLSCipherParms in the policy agent settings.
    2. Contact IBM support to change the default ciphers on the accelerator via a remote maintenance session.

• Enhancements related to IBM Integrated Synchronization
  • Support of simple REORG utility operations on replicated tables in non-UTS tablespaces.
    If you replicate a table located in a non-UTS tablespace and run a simple REORG utility on it, replication continues. It is no longer required to reload the corresponding accelerator-shadow table. This support requires applying the PTF for Db2 for z/OS PH48537.
  • Improved performance resulting in reduced latency for processing ROLLBACK operations of transactions that execute large UPDATE statements. See also APAR PH34713. The feature is turned off by default and can be turned on by IBM Support in a remote maintenance session.
  • Improved stability for processing large UPDATE statements to avoid out-of-memory failures.
• Enhancements for Db2 Analytics Accelerator on IIAS
  Starting with maintenance level 7.5.10 the upgrade of an accelerator always includes the update of the IIAS-firmware if such is available. To ensure consistency of the firmware across the various components of the hardware, essential updates are no longer supported.
• Enhancements for Db2 Analytics Accelerator on Z
  • Improved query performance by now exploiting the hardware based 'Accelerated Data Compression' feature in the Db2 Warehouse component.
  • Support of RoCE cards (RDMA over Converged Ethernet) for the data network and cluster network - Technical Preview
    For new Accelerator on Z multi-node installations it is highly recommended to use RoCE cards for the cluster network instead of Hipersockets for improved performance and stability.
• Enhancements in Accelerator functionality
  • Queries that use the Db2 for z/OS scalar function RAND(<value>) with a seed value as input parameter can now be routed to the Accelerator
    The RAND(<value>) support is available for any maintenance level of Db2 Analytics Accelerator V7.5 by applying the PTF for Db2 for z/OS APAR PH48480 for Db2 12 or Db2 13.
  • Queries that use the Db2 for z/OS scalar function LISTAGG can now be routed to the Accelerator
    The LISTAGG support is available for any Db2 Analytics Accelerator V7 maintenance level by applying the PTF for Db2 for z/OS APAR PH48480 for Db2 12 or Db2 13.

  • Reliability improvements for ‘copy statistics’ feature, for example the statistics of BINARY/VARBINARY columns are now correctly copied after full table reloads.
• Enhancements in operation and maintenance
  • New DSNX881I message to warn about high SYSCATSPACE table space usage.
    Contact IBM Support to reclaim space in a remote maintenance session if the table space is nearly 100% used.
  • Improved performance when saving a trace to an outputLocation on z/OS using stored procedure ACCEL_CONTROL_ACCELERATOR with getTraceData option.

• Enhancements related to IBM Integrated Synchronization
  • Using the security protocol TLS 1.2 or higher in the AT/TLS configuration file is now mandatory. Using TLS 1.1 has been deprecated with Db2 Analytics Accelerator V7.5.8 as previously announced here.
  • A DSNX881I message with ID 20 informs about a long-running transaction that is open since at least 30 minutes. Per default this message is repeated every 3 minutes until the transaction is closed. The repetition interval is now configurable. Contact IBM Support by opening a support case to increase the repetition interval.
  • The Integrated Synchronization status information, provided in a DSNX881I message as well as displayed in the Replication Events viewer of IBM Db2 Analytics Accelerator Studio, is reported now every 15 minutes (in previous releases: every 5 minutes) and has severity Information (in previous releases: Warning). The DSNX881I message has ID 3009 (in previous releases: 20).
• Enhancements for Db2 Analytics Accelerator on Z
  • Support of RoCE cards (RDMA over Converged Ethernet) for the data network and cluster network - Technical Preview
  • Improved resiliency of the internal network stack
  • Improved I/O performance and reduced CPU consumption by exploiting the hardware based 'Accelerated Data Compression' feature.
• Enhancements in Accelerator functionality
  • To collect the actual explain information:
    • The stored procedure ACCEL_CONTROL_ACCELERATOR is enhanced by the 'traceCommand="SYSTEM_PERFORMANCE_DATA"' option for the getAdditionalSupport command.
    • Accelerator Studio is enhanced by a new button 'Collect System Performance Data' located in the Query Monitoring section.

      
• Enhancements in operation and maintenance
  • Accelerator on IIAS
    • Ability to exclude traces of less important components from the appliance trace collection to reduce overall trace collection time. Contact IBM Support by opening a support case to configure the exclusion of traces. 

      

• New:  IBM Db2 Analytics Accelerator Administration Services API
  • Db2 Analytics Accelerator Admin Services consists of a set of RESTful APIs that you can use in your  applications to streamline or to automate the Accelerator administration or management.
    These Db2 Analytics Accelerator Admin Services are leveraged by the product Db2 Administration Foundation which provides a Zowe-based user interface for administering the Accelerator.  
  • For an overview, installation and configuration of the Db2 Analytics Accelerator Admin Services see IBM Documentation
• Enhancements related to IBM Integrated Synchronization
  • ALTER ROTATE partition support
    An ALTER TABLE ROTATE PARTITION operation on partitioned Db2 for z/OS tables does not require reloads of the corresponding replication enabled Accelerator shadow-tables anymore. 
  • New health and status information display in Configuration Console
    The following information can be retrieved from the Configuration Console using a new option in the Incremental Update menu:

    • Status information such as latency, number of records applied or number of open transactions
    • List of open transactions including user ID and involved tables
  • Schema change support for non-replicated tables continues to be in -Tech Preview-
    See the 'What's new description'  of maintenance level V7.5.7 for more details.
• Enhancements related to query acceleration and performance
  • • Db2 z/OS tables containing columns with a VARCHAR length greater than 32672 bytes can now be loaded to the Accelerator.
  • Queries that use Db2 z/OS scalar function MULTIPLY_ALT can now be routed to the Accelerator. 
    The MULTIPLY_ALT support is available for any Db2 Analytics Accelerator V7 maintenance level by applying the PTF for Db2 for z/OS APAR PH42524. 
  • Defined unique constraints on Db2 for z/OS tables can now be created as informational constraints on the accelerated tables.
    This will achieve query performance improvements in certain situations. The creation of the informational constraints can be enabled or disabled per table by specifying a new XML tag "useConstraintsForQueryOptimization" in  ACCEL_ADD_TABLES or ACCEL_ALTER_TABLES stored procedure calls.  

    • Note: 
      Using this feature for a table comes with the current limitation that partial reloads of the table are not possible. Contact IBM support by opening a support case for recommendations whether this feature would be beneficial for your queries.
  • Integration of new database engine Db2 Warehouse 11.5.7 
    This database maintenance level provides improved performance when inserting data using trickle-feed inserts and provides a better compression of the inserted data. These improvements have a positive effect on replication and query performance. 
• Enhancements for Db2 Analytics Accelerator on Z
  • • Support for deploying Accelerator on Z on IBM z16
  • • New messages on the HMC console indicating that shutdown of an Accelerator on Z is in progress or completed
  • New optional keyword "transient_storage" in the "runtime_environment" section of the JSON configuration file
    With Accelerator maintenance level 7.5.8 the value "NVMe" is supported. If specified, available NVMe devices within a Linux One system (Linux One local storage) are considered to be used as temporary storage. 
  • New optional keyword "dispatch_mode" in JSON configuration file
    It refers to the z/OS HiperDispatch mode concept. The keyword can be set to "vertical" (default) or "horizontal". Vertical dispatch mode means that the workload is concentrated on a few CPUs, reducing scheduling overhead. This mode is most efficient for systems with many logical processors. In earlier Accelerator maintenance levels always dispatch mode "horizontal" was used. 
• Enhancements in IBM Db2 Analytics Accelerator Studio
  • • Tables section provides a new column called 'Last Statistics Collection'.
    • Capture Query Environment provides two additional settings for Optimization Profile Schema and Profile Name.
    • The Query Monitoring table provides a new column called 'Application Handle'.
    • Monitor Current Tasks tab provides a new table column called 'io_time'.
• Enhancements in Accelerator functionality
  • • Day-1 support for Db2 13 for z/OS
    • Stored procedure ACCEL_GET_QUERIES2 now returns also query prepare time per query and application handle within the result set. 
    • Stored procedure ACCEL_GET_TABLES_INFO now returns the timestamp of the last statistic collection per table within the result set.
• Enhancements in operation and maintenance
  • New DSNX881I message for long-running queries that have fetched no rows due to long prepare or queue time 
    • The default time frame is 2 hours.
    • This time frame can be changed by IBM Support in a remote maintenance session.

      
  • New stored procedure ACCEL_CONFIG_OPTIMIZATION_PROFILE to configure and manage optimization profiles on the Accelerator.
    An optimization profile can be configured on advice by IBM support to set different Db2 Warehouse optimization settings per connected Db2 subsystem to achieve optimal performance for different workloads from different connected Db2 subsystems.

• Enhancements related to IBM Integrated Synchronization
  • Better serviceability by improved identification of log reader tasks in Db2 for z/OS
    • Improved output of the -DISPLAY STATS (LOGREADERTASKS) command and improved log reader-related messages (e.g. DSNI090I, DSNI091I, DSNI092I) help to identify the location of the accelerator that started the log reader task and the correlation ID of the log reader task. 
    • New SCOPE(GROUP) option to the -DISPLAY STATS (LOGREADERTASKS) command to display information about log reader tasks running on all active members of a data sharing group.
    • Both enhancements are delivered with Db2 for z/OS PTF UI77365 (APAR PH40261); the Accelerator maintenance level 7.5.7 is not required.
      See also: Recent enhancements to Db2 12
  • Schema change support now available also for non-replicated tables -Tech Preview
    • • Overview
        Using IBM Integrated Synchronization for data replication, schema change support for replicated tables is already supported since Accelerator maintenance level 7.5.6. Now also schema changes (add or alter column) for non-replicated tables are supported. It is no longer necessary to remove, add, and re-load the Accelerator-shadow table to make the schema changes available there.
      • 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]; for VARCHAR and VARGRAPHIC types only
        4. ALTER TABLE tablename ALTER COLUMN colname SET WITH DEFAULT [constant];
      • Notes and Requirements
        • This support is provided as Technical Preview which means it is turned off by default. Contact IBM by opening a support case to enable the schema change support of non-replicated tables.
        • Integrated Synchronization must have been enabled for the Db2 system
        • The Db2 for z/OS user you specified as you enabled IBM Integrated Synchronization needs the following additional privileges (same as in ADD COLUMN SUPPORT for replicated tables): 
          • SELECT privilege on SYSIBM.SYSTABLES, SYSIBM.SYSCOLUMNS, SYSIBM.SYSDUMMY1
          • SELECT, UPDATE privilege on SYSIBM.SYSACCELERATEDTABLES
        • The PTF for Db2 for z/OS APAR PH40937 must be installed to use this function.
  • ALTER TABLE ROTATE PARTITION Support -Tech Preview
    • This support is provided as Technical Preview which means it is turned off by default. Contact IBM by opening a support case to enable this support.
    • If enabled,  an ALTER TABLE ROTATE PARTITION operation on partitioned Db2 for z/OS tables does not require reloads of the corresponding replication enabled Accelerator shadow-tables anymore. 
• Enhancements related to query acceleration and performance
  • Optimization profiles on the Accelerator
    Ability to configure optimization profiles on the accelerator (by IBM Support) per Db2 for z/OS user Id. This is useful if different workloads executed from different Db2z users require different optimization settings for achieving optimal performance on the Accelerator.
  • Use of the dynamic plan stability feature of Db2 12
    This is useful to stabilize the decision by the Db2 for z/OS optimizer to not route a query to the Accelerator but execute it on Db2 for z/OS. The feature safeguards the Accelerator against unexpected Db2 for z/OS cost estimation changes (due to upgrading Db2 for z/OS maintenance) causing routing of queries to the Accelerator that had been run on Db2 for z/OS before. 
• Enhancements for Db2 Analytics Accelerator on IIAS
  • • Higher stability due to improved container management. This change requires a full upgrade to maintenance level 7.5.7. An essential upgrade is not possible. 
  • Enable Call Home configuration with default HMC domain
    So far the configuration of the Call Home feature requires the configuration of a customized domain and password for the Accelerator to communicate with the HMC. Now with maintenance level 7.5.7 the Accelerator's call home function can be configured using the default HMC domain and password. 
• Enhancements in IBM Db2 Analytics Accelerator Studio
  • • New 'Save Trace' option ("Collect Backend Environment File (db2support -cl 0)") to collect Db2 Warehouse environment data for enhanced serviceability. 
• Enhancements in Accelerator functionality
  • ROWID column type support for federated tables
    • In previous Accelerator maintenance levels the stored procedure ACCEL_CREATE_REFERENCE_TABLES returns error AQT10050E if the table to be federated contains a ROWID column. Accelerator maintenance level 7.5.7 together with PTF UI77688 for Db2 for z/OS V12 APAR PH40682 lift this restriction. Tables with ROWID columns can now be federated using stored procedure ACCEL_CREATE_REFERENCE_TABLES.   
• Enhancements in operation and maintenance
  • Enhancements to stored procedures SYSPROC.ACCEL_GET_QUERIES and SYSPROC.ACCEL_GET_QUERIES2
    They now return the following HTAP information in the output query list:

    • "waitTimeHTAPSec": The time in milliseconds the statement waited for execution on the Accelerator because the changes it needed were not yet applied to a referenced table.
    • "timeoutsHTAP": The number of times the statement expired because the total wait time was higher than the specified WAITFORDATA value.

      
  • Improvements to the cancel query task operation
    A query can be cancelled by using stored procedure ACCEL_CONTROL_ACCELERATOR with cancelTask option or by using Accelerator Studio or Data Server Manager controls.

    • Improved internal processing to ensure that queries are reliably cancelled
    • Improved messaging to return SQLCODE -952 for all successful cancel query operations
  • New DSNX881I message for long-running queries that have fetched no rows
    New message with ID 2006 has been added to inform about running SQL statements that have fetched no rows since a configurable amount of time.

    • The default timeframe is 2 hours.
    • This timeframe can be changed by IBM Support via a remote maintenance session. 

• Enhancements related to IBM Integrated Synchronization
  • • Replication using IBM Integrated Synchronization now supports schema changes (add or alter column) no longer as "tech preview", but at production level.
      • • Overview
          Replication with IBM Integrated Synchronization now supports schema changes (add or 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]; for VARCHAR and VARGRAPHIC types only
          4. ALTER TABLE tablename ALTER COLUMN colname SET WITH DEFAULT [constant];
        • Notes and Requirements
          • Read the specific requirements in the Release Notes, section "Installation and/or Upgrade Considerations", and then "New feature-specific considerations: "Replication using IBM Integrated Synchronization now supports schema changes (add or alter column)"
    • Enhanced certificate management
      Using the Db2 Analytics Accelerator Console, you can now transfer new certificates and activate them. You can also remove certificates, for example after they have expired. 
    • Serviceability improvements
      • The DSNX881I message reporting of long-running transactions now contains the transaction user for an easier identification of the transaction on Db2 or z/OS. 
      • Regular health reporting about replication latency and replication activity. The health information is included in a DSNX881I message and displayed in the event viewer of your administration client, for example in IBM Db2 Analytics Accelerator Studio.   
• Enhancements related to query acceleration and performance
  • Masked column support
    • Tables with masked columns can now be fully added, loaded, or replicated to an accelerator. Before this maintenance level, masked columns were omitted and only non-masked columns could be added, loaded, or replicated to an accelerator. 
    • Queries referencing the masked columns can be routed to an accelerator if the masked columns are not referenced in the outermost SELECT statement of the query.
    • This enhancement requires installation of the PTF UI74917 for Db2 for z/OS APAR PH33061.
  • Option to block recursive queries from being routed to the accelerator
    A recursive query might perform better when it is processed by Db2 for z/OS rather than by an accelerator. In such situations, it may be useful to block recursive queries, so that they are not routed to an accelerator, but processed by Db2 for z/OS instead. 
    Note: This option requires an installation of Db2 for z/OS PTF APAR PH35960. It also works for accelerator maintenance levels lower than 7.5.6.
• Enhancements for Db2 Analytics Accelerator on Z
  • System Activity Report (SAR)
    System Activity Report (SAR) is a Unix System V-derived system monitor command used to report on various system loads, including CPU activity, memory/paging, interrupts, device load, network and swap space utilization. 

    • You can collect SAR data using the Admin UI of Db2 Analytics Accelerator on Z, which now includes a Diagnostics tab.
    • The collected SAR data becomes part of the accelerator trace information and can be shared with IBM support for further analysis.

  • Query performance and system stability improvements by using direct I/O mechanisms.
  • JSON configuration enhancements.
    Ability to limit the maximum size of the temporary workspace on a disk. 

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

• 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 
  • Maintenance level 7.5.4 incorporates the new database engine Db2 Warehouse 11.5.5, which provides improved compression, storage management, and query performance to accelerator users: 
    • Compression enhancements
    • Performance enhancements

• 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 anymore. 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

• 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

• 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