What's new in Db2 Administration Foundation

• Use the Export table specification option in the Take action drop-down to export the selected table definitions as an XML file.
• Use the Import table specification icon, located on the right above the table results, to import the tables to a specific accelerator based on the table definitions provided in an XML file.

• The following SQL workload actions are now supported without using a connection profile:
  • Creating, listing, and refining a workload
  • Workload tuning
  • Generating Explain data for a workload
• You can now perform the following action without specifying a connection profile:
  • Creating EXPLAIN tables
• A problem has been fixed in which a Query Rewrite Advisor job would become unresponsive when running at the same time as other advisors.

• You can now perform the following action without specifying a connection profile:
  • Tuning an unqualified single SQL statement
• Connection profiles can now be shared with a group of users that are included in a RACF secondary group ID.
• When you perform the following tuning actions without using a connection profile, if your Db2 connection uses a secure port, the sslConnection parameter is automatically set to true:
  • Tuning single SQL queries
  • Tuning workloads
  • Managing a workload
  • Capturing SQL
  • Capturing a query’s environment
  This enhancement eliminates the need to manually enter SSL parameters when you are not using a connection profile.
• When capturing SQL from a file, more file types are now supported, and the maximum supported size of the input file has been increased to 10 MB.
• You can now run Index Impact Analysis on Index Advisor jobs that were run without using a connection profile.
• The at sign (@), number sign (#), and dollar sign ($) characters are now supported for the SQLID and Schema fields on the Parameters tab of the Query tuning page.
• When the Workload Explain feature fails to explain a statement in the workload, the reason for the failure is provided.
• Default values have been removed from special register fields on the tuning parameters page because in some cases the default values could cause inaccurate results to be returned.
• On the Parameters tab of the Query tuning page, the Query number field is no longer populated by default with a value of 1. This change reduces the chance for conflicts when multiple users are running tuning actions simultaneously.
• A problem has been fixed that prevented the Capture Query Environment feature from working properly with the output of Index Impact Analysis jobs.
• A problem has been fixed that caused potential performance issues when switching between menu options.
• A new ZWEYAML parameter has been added, com.ibm.izp.security.jdbc.passTruststoreInfo, which enables you to control whether UMS truststore parameters or SQL Tuning Services truststore parameters are used in SQL Tuning Services API calls.

For more information, refer to Exploring Db2 subsystems.

You can now effortlessly switch between login sessions, browsers, and devices and resume work with your last user interface (UI) state. This can include search terms, filters, pagination, and display settings. For more information, refer to Fields supporting user interface (UI) state.

Supports downloading trace files in the background without interrupting the User Interface (UI) usability.

Support for viewing the partition graphs on the Load table details tab. You can now click each horizontal bar in the graph to view the partition details of a specific table.

You can now select or deselect any row on the Tables tab by clicking anywhere on the row, except the expand or collapse icon and the overflow menu.

The Refresh accelerator status button is now available on the page header, which enables you to update the Acceleration, Replication, and Status without navigating to the Overview tab.

• From the Take action drop-down.
• By clicking the three vertical dots at the end of each row.

The Copy table dialog box enables you to copy the table data from another accelerator.

• You can now reorder and resize the columns in both Tables and Queries tabs, which remain until the next login.
• You can now do the following in the Tables tab:
  • View the tables without grouping them by schema.
  • View the 'Name' column that combines both the schema and table columns.
  • Sort the values in each column.

• You can run a specific part of the SQL statement by selecting it in the editor. Refer to Running SQL queries for Db2.
• You can provide a file name for the CSV when exporting SQL queries. Refer to Exporting SQL queries.

• Load history
• Load table details
• Load table history

Launch Db2 Administration Foundation from Db2 Query Monitor or any similar application

• Accelerator-Only
• Clone
• Global Temporary

• Data editing and Data studio operations.
• Search results and export capabilities.
• Db2 Analytics Accelerator functionalities.

The Replication latency field of the accelerator Overview tab is enhanced to display the following additional information when you hover over the field: Target agent, Source agent, Latency, and Last changed timestamp.

• Stop an in-progress tuning job for a single query.
• Capture the environment for a single query without the use of a tuning profile.
• Retain the values that you specify in tuning dialogs as you move from one page to the next.
• View a visual representation of existing access paths for an SQL statement based on the statement cache or package for that statement. For more information, refer to Viewing existing access paths.

The token management mechanism is enhanced to avoid the internal server error in the RUN SQL function in Db2 Administration Foundation, when using the following:

• RACF PassTickets to authenticate UMS connections to z/OS backend services.
• Db2 Analytics Accelerator Administration Services.

For more information on the Privileges tab, refer to Working with Db2 objects

• Edit the values in a table cell, insert or delete a row in a table, and review your changes before committing or discarding them. For more information, refer to
  • Working with Db2 objects
  • Exploring Db2 objects
  • Editing data for table
• Use the Filter option to apply conditions, filter the rows by column name, and sort the rows using the order criteria to view the required data for the table object type. For more information, refer to Filtering data for table.

For more information, refer to Formatting SQL statements.

• components.izp.temporaryDirectory
• components.izp.server.maxFileUploadSizeInMB

• Filter the table data by date or size range, and plot by access count or size.
• Export the table data to a CSV file.

• Run SQL tuning actions against a single query without the use of a tuning profile.
• Cancel an in-process workload tuning job.
• Specify whether new access paths are created when you run tuning actions against a workload and choose to generate new Explain data for the workload.
• Delete multiple tuning jobs in a single operation.

Additionally, during the process of creating the repository database, if you have already created a Db2 connection, the connection attributes and the status of the subsystem are displayed.

• Capture SQL from a z/OS data set enables you to identify and display SQL statements that exist in a sequential data set or partitioned data set member and work with those statements by using other Db2 Administration Foundation features. For more information, see izp_tsk_capture_sql_from_dataset.html. 
• Workload-level support for Capture Query Environment enables you to capture information about the environments in which all the queries in a workload run. Before this update, the Capture Query Environment supported only single queries. For more information, see Capturing a query's environment.

• You can now extract queries running on the Db2 Analytics Accelerator Loader as a CSV file.
• To support smart table management, the following new columns are introduced on the Tables tab:
  • Last load time
  • Last statistics collection
  • Is timestamp collected
• A Db2 applications table is added to the Monitor tab to enhance monitoring capability.
• The following two columns are added to the Queries tab to enhance query monitoring:
  • Application stall time
  • Application handle

• Index Impact Analysis

  Generates a report that shows the impact of applying the index changes that are recommended by Index Advisor and Workload Index Advisor. For more information, see Evaluating index recommendations before deploying them.

• Workload Virtual Index Analyzer

  Enables you to test indexes to determine if the performance of the SQL statements that comprise a workload can be improved by creating indexes, dropping indexes, or both. For more information, see Analyzing the effects of applying index changes.

• Workload Refine

  Enables you to modify an existing workload. For more information, see Refining SQL workloads.

• Workload Explain Failure Reason

  Explains why SQL statements failed during a Workload Explain operation.

• Workload Index Advisor

  Generates recommendations for creating, altering, or dropping indexes that can improve the performance of an SQL workload. This feature provides DDL scripts that you can run and information about the existing indexes from the workload that you are tuning.

• Workload Statistics Advisor

  Generates recommendations and RUNSTATS DDL for creating or modifying statistical views.

• Workload Explain

  Workload Explain has been enhanced to support CURRENT QUERY ACCELERATION=ALL.

• Workload Access Path Comparison
  Workload Access Path Comparison has been updated with the following enhancements:

  • You can now specify specific types of tuning jobs.
  • Internal jobs are no longer displayed during a Workload Access Path Comparison action.

  For more information, see Tuning SQL workloads.

• Virtual Index Analyzer

  Virtually tests indexes to determine if the performance of a single query can be improved by creating indexes, dropping indexes or both. For more information, see Analyzing the effects of applying index changes.

• Workload Explain

  Gathers explain information for all statements in a workload and stores this information in the repository database. This information is used by other workload advisors to analyze these statements.

• Workload Query Rewrite Advisor now provides a Results page.
• The Results pages for Workload Access Path Advisor and Workload Access Path Comparison now enable you to display both high-level and low-level tuning results.

• Configuring UMS for External Tooling
• Configuring UMS for Db2 Analytics Accelerator Loader
• Configuring Storage tab

• Add a new Db2 accelerator. To perform this action from subsystem Overview tab, click Add new and provide new accelerator details.
• To save space, you can archive tables on a Db2 accelerator. To perform this action from the Tables tab, select tables to archive and click the Archive option from the Archiving menu.
• You can restore archived tables to a Db2 accelerator. To perform this action from the Tables tab, select tables to restore and click the Restore option from the Archiving menu.
• Manage federated access for a Db2 accelerator. To perform this action from the Tables tab, select required tables and click the Federation menu. The following options are available:
  • Grant access

    You can grant access to tables in another subsystem. Upon completion of this action, you can create reference tables from those subsystems.

  • Remove references

    The remove reference option lets you delete references from an accelerator.

• You can create a reference to a table in another subsystem. Upon completion of this action, the reference is created on the shared accelerator. To perform this action from the Tables tab, click the Create references button.
• You can revoke access from a table that will prevent it from referencing a Db2 subsystem. To perform this action from the Tables tab, click the Revoke access button.