Adopting new Version 9.7 functionality in upgraded databases

After upgrading your DB2® server, enhance the functionality and improve the performance of your upgraded databases by adopting new Version 9.7 functionality.

Perform any of the following steps to adopt the specified Version 9.7 functionality in your upgraded DB2 environment:

• Enable automatic storage in existing databases by issuing the following statement:

  ALTER DATABASE database-name ADD STORAGE ON storage-location

  After enabling your databases for automatic storage, enable your existing DMS table spaces for automatic storage. One way to do this enablement is to keep existing containers intact and use automatic storage for future growth and reduction operations by issuing the ALTER TABLESPACE statement:

  ALTER TABLESPACE tablespace-name MANAGED BY AUTOMATIC STORAGE

  If you want to convert existing containers in your table spaces to use automatic storage, perform a redirected restore to re-create existing DMS table spaces as automatic storage table spaces.

  In addition, you can now drop storage paths from an automatic storage database as well as add them. After altering the database storage paths, you can optionally rebalance the data in the automatic storage table spaces to better utilize data striping and increase I/O throughput. The following example shows how to rebalance an automatic storage table space:

  ALTER TABLESPACE tablespace-name REBALANCE

  The following SQL statement generates a list of all the regular and large automatic storage table spaces for the currently connected database:

  SELECT TBSP_NAME
  FROM  SYSIBMADM.SNAPTBSP
  WHERE TBSP_USING_AUTO_STORAGE = 1 AND TBSP_CONTENT_TYPE IN ('ANY','LARGE')
  ORDER BY TBSP_ID

  All of these enhancements provide greater control over your automatic storage databases and table spaces.

• If you are using DMS table spaces in databases with or without automatic storage enabled, start using new DMS table spaces created in Version 9.7 or migrate existing DMS table spaces. Newly created DMS table spaces have reclaimable storage enabled by default. You can trigger the extent movement operation to relocate the maximum number of extents in them and reduce the high water mark by using the following commands:
  • For automatic storage DMS table spaces, use the ALTER TABLESPACE statement with the REDUCE clause.
  • For non-automatic storage DMS table spaces, use the ALTER TABLESPACE statement with the LOWER HIGH WATER MARK. Then issue the ALTER TABLESPACE statement with the REDUCE clause to alter the size of the containers.
  Existing DMS table spaces that you created in pre-Version 9.7 releases have reclaimable storage disabled. They can coexist with DMS table spaces that use reclaimable storage. To enable reclaimable storage in your existing DMS table spaces, you need migrate them using in one of the following methods:

  • Re-create DMS table spaces.
  • Create new DMS table spaces and call the new SYSPROC.ADMIN_MOVE_TABLE procedure to move the data to a new DMS table space with reclaimable storage enabled while the data remains online and available for access.
• If you have disk space limitations on the directory indicated by the diagpath configuration parameter, control the total size of DB2 diagnostic (db2diag) and administration notification log files by setting the diagsize database manager configuration parameter to a value and restart the instance.

  After you restart the instance, all the messages are written to db2diag rotating log files (db2diag.N.log) and rotating administration notification logs (instance.N.nfy) whose total size is limited by the value in diagsize. To avoid losing information because of the log file rotation, you must specify an adequate value between 1 GB and the amount of free space in the directory indicated by diagpath minus 5 GB.

• Use SYSTEM sampling to reduce the cost of collecting statistics on statistical views as shown in the following example:

  RUNSTATS ON TABLE view-name
           WITH DISTRIBUTION TABLESAMPLE SYSTEM (sampling-rate)

  When you issue the RUNSTATS with the TABLESAMPLE SYSTEM option on statistical views, you will not receive the SQL20288N error code. If the RUNSTATS cannot use the SYSTEM sampling, it collects statistics for the view using BERNOULLI sampling with the same sampling rate and seed as specified in the command and returns the SQL2317W warning message to indicate the change in sampling style.

  You can continue to use BERNOULLI sampling as in previous releases. DB2 Version 9.7 now provides improvements on the performance of BERNOULLI sampling.

• Use access plan reuse and statement optimization guidelines enhancements to influence the optimizer and obtain consistent query execution plans for the same query. For static statements, you indicate to the query compiler to reuse existing access plans for the statements in a package by issuing the following statement:

  ALTER PACKAGE schema-name.package-id ACCESS PLAN REUSE YES

  Also, you can indicate to the query compiler to reuse existing access plans for the statements in a package by issuing the BIND command as shown in the following example:

  BIND filename ACTION REPLACE APREUSE YES

• Enable data row compression on tables that use replication. You can now enable data row compression on tables that use data replication. Ensure that your server can manage the additional resources required for data row compression and that applications are modified to receive more data if you are using the db2ReadLog API in your application. See Upgrade impact from DB2 API changes to learn how to manage the additional resources required for your application.
• If you frequently delete data from MDC tables, use the new RECLAIM EXTENTS ONLY option with automatic reorganization to free empty extents in MDC tables to DMS table spaces so that these extends can be reused by other tables or database objects by performing the following steps:
  1. Set the auto_maint, auto_tbl_maint and auto_reorg database configuration parameters to ON.
  2. Configure an automated maintenance policy for reclaiming extents by calling the AUTOMAINT_SET_POLICY or AUTOMAINT_SET_POLICYFILE system-defined procedure using maintenance type AUTO_REORG and an the maintenance policy specification in XML as parameters. Refer to the DB2AutoReorgPolicySample.xml sample file in DB2 samples directory for details on how to specify a maintenance policy in XML.

  Reclaiming empty extents allows you to improve performance for prefetching data because there will be less extents to bring from disk to memory. However, if there are no empty extents for reuse when you insert data, new extends need to be reallocated. Although you can manually reclaim empty extends, using automatic reorganization allows the database manager to determine when this action should take place to obtain performance gains.

• Use partitioned indexes in partitioned tables to optimize performance when you add a new range or remove an existing range from a partitioned table by issuing the ALTER TABLE statement with the ATTACH PARTITION or DETACH PARTITION clauses. Index maintenance is not required when you issue the SET INTEGRITY statement on partitioned tables that use partitioned indexes. To convert existing nonpartitioned indexes in a partitioned table, you must drop and re-create them as partitioned indexes.

  In addition, partitioned index definitions in the table that you plan to attach and the partitioned table should match before you issue the ALTER TABLE statement with the ATTACH PARTITION to obtain this performance gain and avoid index maintenance.

  See Migrating existing indexes to partitioned indexes for details.

• Use the pureXML® feature in partitioned database environments to benefit from integrated XML data storage by:
  • Creating tables with one or more XML columns with the CREATE TABLE statement and the DISTRIBUTE BY clause.
  • Adding one or more XML columns to existing tables that have a distribution key with the ALTER TABLE statement and the ADD COLUMN clause.
  • Adding a distribution key to existing tables that have one or more XML columns with the ALTER TABLE statement and the DISTRIBUTE BY clause. First re-create these tables to convert the XML storage object to the new Version 9.7 format. See Re-creating tables with XML columns for details.
• Use the pureXML feature in partitioned tables to benefit from integrated XML data storage in partitioned tables or use table partitioning on tables storing XML data to benefit from partition elimination and easy roll-in and roll-out capabilities in table partitioning by:
  • Creating a partitioned table with one or more XML columns with the CREATE TABLE statement.
  • Adding an XML column to an existing partitioned table with the ALTER TABLE statement and the ADD COLUMN clause.
  • Migrate an existing table with one or more XML columns to a partitioned table.
  • Rolling in partitioned table data with the ALTER TABLE statement and the ATTACH PARTITION clause.
  • Rolling out partitioned table data with the ALTER TABLE statement and the DETACH PARTITION clause.
• Use the pureXML feature in MDC tables to benefit from integrated XML data storage or use MDC on tables storing XML data to benefit from better query performance by:
  • Creating tables with one or more XML columns with the CREATE TABLE statement and the ORGANIZE BY clause.
  • Adding one or more XML columns to existing MDC tables with the ALTER TABLE statement and the ADD COLUMN clause.
  • Migrating an existing table with one or more XML columns to an MDC table.
• Enable the read capability on HADR Standby databases to use them for read-only workloads and disaster recovery. In Version 9.7, you can connect to HADR Standby databases to run read-only workloads by setting the DB2_HADR_ROS registry variable to ON.