RUNSTATS: What's new in DB2 10 for Linux, UNIX, and Windows

DB2 10.1 provides significant usability, performance, serviceability, and database administration enhancements for database statistics. In this article, learn about the significant performance enhancements to the RUNSTATS facility. Examples show how to take advantage of new features such as new keywords, index sampling options, enhancements to automatic statistics collection, and functions to query asynchronous automatic runstats work.

Kwai Wong (kwaiw@hotmail.com), Software Developer

Author PhotoKwai Wong is a software developer with over 20 years of experience working on the DB2 development team at the IBM Canada Lab. Her development work has focused on the runtime, query optimizer, and database statistics components of DB2. Kwai also has expertise in performance analysis, tuning, and troubleshooting.



13 February 2014

Also available in Russian

Introduction

Database statistics are an important component of system performance. Without accurate statistics, the query optimizer cannot make well-informed decisions when determining access plans. Although database statistics are rarely in the spotlight, they have been an integral part of the query optimizer since the first version of DB2 for Linux, UNIX, and Windows. DB2 10.1 has multiple enhancements to RUNSTATS that improve its usability and performance. DB2 10.1 also contains multiple enhancements to the automatic statistics collection facility.

Access DB2 downloads, including product trials, emerging technologies, updates, fixes, utilities, and drivers.

In this article, learn about the DB2 10.1 RUNSTATS performance improvements. Several examples show how to take advantage of new keywords, index sampling options, enhancements to automatic statistics collection, and new functions to query asynchronous automatic runstats work.


Optional table and index schema

Have you ever been annoyed that you must provide a fully qualified table name for RUNSTATS? For example, you can create a table TEST1 with your default schema and insert data into TEST1. You can even REORG TEST1, but you cannot do RUNSTATS on TEST1. Listing 1 shows the SQL0104N error returned when you submit RUNSTATS without a schema-name on DB2 9.7.

Listing 1. RUNSTATS without schema-name returns error on DB2 9.7
create table test1 (i1 int, i2 int)
DB20000I  The SQL command completed successfully.

insert into test1 with q(a) as (values 1 union all select a+1 from q where a<5000) 
 select a,mod(a,100) from q
DB20000I  The SQL command completed successfully.

create index test1i1 on test1 (i1)
DB20000I  The SQL command completed successfully.

runstats on table test1
SQL0104N  An unexpected token "test1" was found following "TABLE".  Expected
tokens may include:  "<schema-name.table-name>".  SQLSTATE=42601

reorg table test1
DB20000I  The REORG command completed successfully.

runstats on table kwaiwong.test1
DB20000I  The RUNSTATS command completed successfully.

With DB2 10.1, RUNSTATS supports default schema for both table and index names. You no longer have to fully qualify object names. As shown in Listing 2, if you do not specify a schema, the default schema is used. This enhancement improves usability.

Listing 2. RUNSTATS without schema-name works on DB2 10.1
runstats on table test1
DB20000I  The RUNSTATS command completed successfully.

runstats on table test1 and index test1i1
DB20000I  The RUNSTATS command completed successfully.

New VIEW keyword

Another usability enhancement in DB2 10.1 is the VIEW keyword.

Listing 3 shows a DB2 9.7 CLP script to implement a statistical view between the SALES fact table and the STORE dimension table. You create the view and alter the view to enable query optimization. Then, you perform RUNSTATS on the view but you must use the TABLE keyword in the RUNSTATS command.

Listing 3. DB2 9.7 CLP script to implement a statistical view
CREATE VIEW DB2DBA.SV_STORE AS
  (SELECT S.*
  FROM STORE S, SALES F
  WHERE S.STOREKEY = F.STOREKEY);
ALTER VIEW DB2DBA.SV_STORE ENABLE QUERY OPTIMIZATION;
RUNSTATS ON TABLE DB2DBA.SV_STORE WITH DISTRIBUTION;

In DB2 10.1 you can use the VIEW keyword, as in Listing 4. The command also makes use of the optional schema enhancement.

Listing 4. DB2 10.1 supports VIEW keyword in RUNSTATS command
RUNSTATS ON VIEW SV_STORE WITH DISTRIBUTION;

Changed behaviour for DETAILED index statistics

Detailed index statistics provide information about I/O needed to fetch data pages in ISCAN-FETCH operations. The query optimizer uses this information when planning how to access a table, such as whether it should access a table using a particular index.

The collection of detailed index statistics can be CPU and memory intensive. For large tables with multiple indexes, this can mean a long RUNSTATS execution time. However, you can reduce the amount of time with the SAMPLED option for detailed index statistics. With the SAMPLED option, detailed index statistics are collected with nearly the same accuracy but with significantly reduced CPU and memory usage.

The default recommendation for index statistics collection is SAMPLED DETAILED index statistics. However, users sometimes forget to include the SAMPLED keyword and, in DB2 9.7, it resulted in the resource intensive collection of detailed index statistics.

DB2 10.1 addresses this problem with a changed behaviour, where SAMPLED is the default when the DETAILED index option is specified. A new UNSAMPLED keyword allows for the collection of unsampled detailed index statistics. Listing 5 compares the index clause syntax in DB2 9.7 and in DB2 10.1.

Listing 5. RUNSTATS index clause in DB2 9.7 and DB2 10.1
Index Clause in DB2 9.7:
                                             .-,----------.     
                                             V            |     
|--+-----------------------+--+-INDEXES-+--+---index-name-+-+---|
   '-+---------+--DETAILED-'  '-INDEX---'  '-ALL------------'   
     '-SAMPLED-'       


Index Clause in DB2 10.1:
                                               .-,----------.     
                                               V            |     
|--+-------------------------+--+-INDEXES-+--+---index-name-+-+---|
   | .-SAMPLED---.           |  '-INDEX---'  '-ALL------------'      
   '-+-----------+--DETAILED-'                
     '-UNSAMPLED-'

General performance improvements

DB2 10.1 provides performance improvements to RUNSTATS. Re-engineered CPU intensive computational code paths provide reduced path length. Another performance improvement is from index readahead (RA) prefetching, a facility introduced in DB2 10.1.

For large tables with many indexes, index statistics collection typically dominates RUNSTATS execution time; a RUNSTATS operation will fully scan all the indexes of the table. When prefetching occurs, index pages are already in the buffer pool as RUNSTATS needs them. When prefetching does not occur, RUNSTATS must wait for I/O to bring pages into the buffer pool and performance is not optimal.

Prior to DB2 10.1, sequential detection was the only prefetching facility available during RUNSTATS. When indexes are fragmented, as can occur with high insert or update activity, sequential detection will not trigger prefetch. This issue is addressed with DB2 10.1 RA prefetching, which is targeted at improving performance of operations that access fragmented objects.

In performance testing, RA prefetching demonstrates significant benefit. For example, in a controlled test of highly fragmented indexes, DB2 10.1 outperforms DB2 9.7 by more than eight times. Listing 6 shows a summary of the test.

Listing 6. DB2 9.7 and DB2 10.1 RUNSTATS test with fragmented indexes
Controlled test environment:
 - table S.T1 contains 20 million rows
 - table S.T1 has 5 indexes
 - indexes are controlled to be highly fragmented 

Command tested:
 runstats on table S.T1 with distribution and sampled detailed indexes all

Elapsed time:
 DB2 9.7 = 20 min
 DB2 10.1 = 2.3 min  (over 8x improvement)

For more information about the effects of index fragmentation on RUNSTATS in DB2 9.7, read "Optimizing RUNSTATS in DB2 for Linux, UNIX, and Windows: Troubleshooting index fragmentation" (in Resources). The article includes a technique for creating fragmented indexes used in the test summarized in Listing 6.

The performance improvements discussed in this section are immediately available when you move to DB2 10.1; you don't need to do anything to activate these improvements.


New index sampling option

The TABLESAMPLE option of RUNSTATS, introduced with DB2 8.2, allowed sampling of data pages while gathering table statistics and provided a faster RUNSTATS execution time. However, as already discussed, RUNSTATS execution time is often dictated by index statistics collection and, prior to DB2 10.1, there was no way to indicate index sampling. This shortcoming is addressed in DB2 10.1 with the new INDEXSAMPLE option.

Listing 7 shows the syntax of the INDEXSAMPLE option along with the existing TABLESAMPLE option. You can specify index sampling using syntax that is similar to that used to specify table sampling. The SYSTEM keyword directs for page level sampling and the BERNOULLI keyword directs for row level sampling. One difference to note: the TABLESAMPLE option provides a REPEATABLE specification to ensure that repeated executions return the same sample while the INDEXSAMPLE option does not.

Listing 7. TABLESAMPLE and INDEXSAMPLE options of DB2 10.1 RUNSTATS
>>-RUNSTATS ON--+-TABLE-+--object-name-------------------------->
                '-VIEW--'  
....

>--+----------------------------+--+----------------------------+--->
   '-| Table Sampling Options |-'  '-| Index Sampling Options |-'


Table Sampling Options:

|--TABLESAMPLE--+-BERNOULLI-+--(--numeric-literal--)------------>
                '-SYSTEM----'

>--+-----------------------------------+------------------------|
   '-REPEATABLE--(--integer-literal--)-'


Index Sampling Options:

|--INDEXSAMPLE--+-BERNOULLI-+--(--numeric-literal--)------------|
                '-SYSTEM----'

If you currently use the TABLESAMPLE option for some tables, you should consider using INDEXSAMPLE as well. You can start with an index sampling rate that is larger than the table sampling rate and confirm that the index statistics collected via sampling do not lead to unexpected access plan changes. Listing 8 shows a RUNSTATS command that specifies 20% page level sampling for the table and 30% page level sampling for its indexes.

Listing 8. RUNSTATS command with both TABLESAMPLE and INDEXSAMPLE
RUNSTATS ON TABLE sales WITH DISTRIBUTION AND DETAILED INDEXES ALL 
 TABLESAMPLE SYSTEM(20) INDEXSAMPLE SYSTEM(30)

For installations that experience long RUNSTATS execution time for large tables, DB2 10.1 provides relief with the combination of RA prefetching and index sampling support.


Improved RUNSTATS sampling for statistical views

Statistical views provide a powerful way to represent data statistics useful for query specifications that have:

  • Complex predicates
  • Relationships among predicates
  • Relationships across tables

DB2 10.1 contains several enhancements relating to how the query optimizer uses statistical views. In star schemas, a single statistical view on the fact table with its dimension tables can be implemented instead of individual statistical views for each fact dimension relationship. This reduces the number of views and facilitates database administration. The query optimizer can also now use statistics from view columns that are expressions, as well as column group statistics collected against view columns. To complement these optimizer enhancements, DB2 10.1 provides improved RUNSTATS sampling for statistical views.

RUNSTATS collects statistics on a statistical view by executing a query over the view and then passing the rows to the statistics collection functions. Sampling is supported for statistical views where only a sample of rows is passed to the collection functions. But, in general, to derive this sample, a full result set is returned for the query—which can mean significant I/O to access a large fact table.

Listing 4 contains a RUNSTATS command without sampling. Let's look at the same RUNSTATS command with sampling. Listing 9 shows a RUNSTATS command on the SV_STORE view that specifies 20% page sampling.

Listing 9. RUNSTATS command with page sampling on a statistical view
RUNSTATS ON VIEW SV_STORE WITH DISTRIBUTION TABLESAMPLE SYSTEM(20);

Background on RUNSTATS page sampling for views

Statistical views were introduced in DB2 9.1 to provide the query optimizer with more accurate information for cardinality estimation. RUNSTATS syntax supports page level (SYSTEM) and row level (BERNOULLI) sampling. If page sampling is specified for a statistical view, DB2 decides if the sampling can be pushed down into one of the underlying tables in the view definition. Starting with DB2 9.7, if DB2 cannot push down page sampling into a base table, row sampling is used and SQL2317W warning is returned. Prior to DB2 9.7, DB2 returns SQL20288N error.

The benefit of page sampling is I/O reduction. For statistical views, I/O reduction occurs only if the sampling can be applied to the underlying tables. Prior to DB2 10.1, the page sampling specified in the RUNSTATS command can be pushed into the underlying fact table access when referential integrity (RI) is defined between the parent dimension table (STORE) and the child fact table (SALES).

With DB2 10.1, the RI restriction is relaxed. Unique indexes are used to detect 1:N joins, and page sampling can be pushed down to the SALES table as long as the STORE dimension has a unique index (or primary key) on the STOREKEY join column. Listing 10 shows the effective query that RUNSTATS runs when page sampling can be pushed down.

Listing 10. Query executed by RUNSTATS when it pushes down page sampling
 SELECT S.* 
  FROM STORE S, SALES F TABLESAMPLE SYSTEM(20)
  WHERE S.STOREKEY = F.STOREKEY

Significant I/O savings are possible when page sampling push down occurs. Listing 11 shows the setup for a demonstration of page sampling push down. The SALES fact table is populated with 10 million rows, which occupies approximately 45,000 pages in the default 8K page size table space. The STORE dimension table has 10 rows. Initially, it does not have an index. The SV_STORE statistical view is defined on the join column STOREKEY.

Listing 11. Setup to demonstrate page sampling push down
-- setup:
-- create fact table with 10M rows
-- create dimension table with 10 rows, and no index
-- create statistical view for the sales and store join

CREATE TABLE SALES 
  (PRODKEY INT,STOREKEY INT,CUSTKEY INT,UNITS_SOLD INT,UNIT_PRICE DEC(7,2));
INSERT INTO SALES WITH Q(A) AS (VALUES 1 UNION ALL SELECT A+1 FROM Q WHERE A<10000000)
  SELECT MOD(A,1000),MOD(A,10),MOD(A,100),MOD(A,7),MOD(A,301)*10 FROM Q;
RUNSTATS ON TABLE SALES WITH DISTRIBUTION;
SELECT CARD,NPAGES FROM SYSCAT.TABLES WHERE TABSCHEMA='KWAIWONG' AND TABNAME='SALES';

CREATE TABLE STORE 
  (STOREKEY INT NOT NULL, STORENUMBER INT, STOREADDRESS VARCHAR(100));
INSERT INTO STORE WITH Q(A) AS (VALUES 1 UNION ALL SELECT A+1 FROM Q WHERE A<10)
  SELECT A,A+1000,'address'||A FROM Q;
RUNSTATS ON TABLE STORE WITH DISTRIBUTION;
SELECT CARD,NPAGES FROM SYSCAT.TABLES WHERE TABSCHEMA='KWAIWONG' AND TABNAME='STORE';

CREATE VIEW SV_STORE AS
 (SELECT S.* FROM STORE S, SALES F WHERE S.STOREKEY = F.STOREKEY);
ALTER VIEW SV_STORE ENABLE QUERY OPTIMIZATION;

Three RUNSTATS commands are compared. Listing 12 shows the RUNSTATS commands and how database monitor data is collected. The first RUNSTATS does not sample and acts as a baseline. The second RUNSTATS specifies 20% page sampling but is run when the dimension table has no indexes. The third RUNSTATS also specifies 20% page sampling but is run after a unique index is created on the STOREKEY dimension.

Listing 12. Tests for page sampling push down
-- test1:
-- run baseline RUNSTATS without sampling

connect reset;
!db2stop force;
!db2start;
activate db sample;
connect to sample;

select 'test1a-mon',ROWS_READ,POOL_DATA_L_READS FROM TABLE (MON_GET_DATABASE(-2));
RUNSTATS ON VIEW SV_STORE WITH DISTRIBUTION;
select 'test1b-mon',ROWS_READ,POOL_DATA_L_READS FROM TABLE (MON_GET_DATABASE(-2));

-- test2:
-- repeat test1 but use RUNSTATS with 20% page sampling
--   RUNSTATS ON VIEW SV_STORE WITH DISTRIBUTION TABLESAMPLE SYSTEM(20);
-- note: this command returns a warning that page sampling could not be used

-- test3:
-- repeat test2 after creating an unique index on the STORE dimension
--   CREATE UNIQUE INDEX STORE_PK ON STORE(STOREKEY);
-- note: with the unique index, DB2 pushes down page sampling into the fact table

Table 1 compares data for the three RUNSTATS executions. When DB2 can push down page sampling into the large fact table, there is significant I/O reduction.

Table 1. I/O reduction from page sampling push down
TestRows readLogical reads
1: Baseline RUNSTATS on statistical view.10,000,16848,908
2: RUNSTATS with TABLESAMPLE SYSTEM(20), but no dimension index.
Note: This command returns SQL2317W warning to indicate that row, not page, sampling was used.
10,000,17348,994
3: RUNSTATS with TABLESAMPLE SYSTEM(20) with dimension index allowing for page sampling push down.2,003,74613,677

The example used here is simple, with only one join. Page sampling push down can also occur for statistical views defined over more than two tables. When the view contains multiple tables, page sampling can occur as long as a single "central" table can be identified as being joined with all unique index columns of the other tables appearing in the view.


Automatic statistics collection for statistical views

DB2 10.1 provides two significant enhancements to automatic statistics collection. One enhancement is the support for statistical views in background automatic statistics collection, which simplifies database administration.

Background statistics collection on a view is triggered if any of the dependant base tables have a later statistics timestamp. This ensures that view statistics are current with respect to their dependent tables.

You must enable this feature using the new database configuration parameter AUTO_STATS_VIEWS (it is not enabled by default). When enabled, DB2 will automatically maintain the statistics for statistical views. Listing 13 shows the new database configuration parameter along with related parameters.

By default, the collection is a specification of the form RUNSTATS ON VIEW statsview_name WITH DISTRIBUTION. If additional options are needed, for example column group statistics, you can create a statistics profile for the statistical view.

If your installation is enabled for automatic statistics collection, and you currently let DB2 handle RUNSTATS for tables while you manually handle RUNSTATS for statistical views, consider enabling AUTO_STATS_VIEWS to direct DB2 to automatically maintain the statistics for views and tables.


Automatic sampling rate determination for automatic runstats

DB2 10.1 provides automatic sampling rate determination for background automatic runstats, which complements the new index sampling feature. This simplifies database administration in that you no longer need to determine sampling rates for individual tables. You don't need to create statistics profiles to direct automatic statistics collection to perform sampling for specific tables, either.

Automatic sampling rate determination is not enabled by default. You must enable it using the new database configuration parameter AUTO_SAMPLING. Listing 13 shows the AUTO_SAMPLING database configuration parameter after it has been enabled. The listing also shows that AUTO_STATS_VIEWS has been enabled.

Listing 13. New database configuration parameters AUTO_STATS_VIEWS and AUTO_SAMPLING
     Automatic table maintenance          (AUTO_TBL_MAINT) = ON
     Automatic runstats                  (AUTO_RUNSTATS) = ON
       Real-time statistics            (AUTO_STMT_STATS) = ON
       Statistical views              (AUTO_STATS_VIEWS) = ON
       Automatic sampling                (AUTO_SAMPLING) = ON

When AUTO_SAMPLING is enabled, DB2 determines an appropriate sampling rate based on table size. Sampling is used for both data and index pages. Automatic sampling always performs page level sampling. If AUTO_STATS_VIEWS is also enabled, automatic sampling applies to statistical views as well as tables.

If you have large tables and observe long-running automatic runstats that might prevent automatic statistics collection from providing updated statistics in a timely manner, consider enabling AUTO_SAMPLING. You should monitor the performance of your system to ensure that the sampled statistics do not have an unexpected negative effect.


New monitor functions to query asynchronous automatic runstats work

The autonomic features of DB2 work well in most cases, but occasionally you might need to troubleshoot. For example, you might have a query that is not performing well. You:

  • Examine the EXPLAIN_FROM_SECTION access plan and note that the cardinality estimate of one of the tables is highly inaccurate.
  • Wonder why DB2 has not updated the statistics, as real-time statistics (RTS) is enabled.
  • Review the statistics log and notice that earlier in the day a synchronous collection for the table timed out. In this situation, the AUTO_STMT_STATS real-time statistics daemon should do an asynchronous collection but the statistics log does not have a record of it.
  • Run the LIST UTILITIES command to get details of RUNSTATS operations that are in progress and observe that both the auto-runstats daemon and the real-time statistics daemon are performing RUNSTATS to other tables in the database.
  • Question whether a RUNSTATS has been queued for the table in the access plan you examined.

This is a common troubleshooting scenario. Previously, there was no facility with which you could check the RTS queue.

DB2 10.1 includes the SYSPROC.MON_GET_RTS_RQST table function, which provides information about asynchronous RUNSTATS that are planned for the real-time statistics daemon on the database to which you are connected. The MON_GET_RTS_RQST table function also provides information about what the real-time statistics daemon is currently executing. Listing 14 shows sample output from the MON_GET_RTS_RQST table function. You can see there are two request types and three request statuses.

Listing 14. Sample output from MON_GET_RTS_RQST table function
SELECT QUEUE_POSITION,
       REQUEST_STATUS,
       REQUEST_TYPE,
       OBJECT_TYPE,
       VARCHAR(OBJECT_SCHEMA, 10) AS SCHEMA,
       VARCHAR(OBJECT_NAME, 10) AS NAME
FROM TABLE(MON_GET_RTS_RQST()) AS T
ORDER BY QUEUE_POSITION, SCHEMA, NAME

QUEUE_POSITION REQUEST_STATUS REQUEST_TYPE    OBJECT_TYPE SCHEMA     NAME
-------------- -------------- --------------- ----------- ---------- ----------
             1 EXECUTING      COLLECT_STATS   TABLE       TEST       EMPLOYEE
             2 QUEUED         COLLECT_STATS   TABLE       TEST       T1
             3 QUEUED         COLLECT_STATS   TABLE       TEST       T3
             - PENDING        COLLECT_STATS   TABLE       TEST       DEPT
             - PENDING        WRITE_STATS     TABLE       TEST       SALARY
             - PENDING        WRITE_STATS     TABLE       TEST       SALES
             - PENDING        WRITE_STATS     TABLE       TEST       T8

 7 record(s) selected.

When real-time statistics successfully performs a full synchronous collection, the new statistics are stored in the statistics cache, which resides in database global memory. While in cache, the statistics are available for subsequent statement compilations by the same db2agent and by other db2agents. The cached statistics are later written to the database catalogs by the real-time statistics daemon via a WRITE_STATS request.

A COLLECT_STATS request is made by a db2agent during statement compilation when it determines the statistics for a table should be updated, but the db2agent does not complete a full synchronous statistics collection. This can occur when:

  • RTS determines that new statistics would be beneficial but are not needed for the query being compiled.
  • RTS decides that the db2agent should perform synchronous fabrication instead of synchronous collection.
  • RTS decides that the synchronous collection should be sampled because the table is large.
  • A full unsampled synchronous collection exceeds the allotted collection time.

The COLLECT_STATS request indicates that the real-time statistics daemon is to perform an asynchronous collection for the table.

At any given time only one table can have EXECUTING status, which indicates the real-time statistics daemon is currently processing that table. PENDING status indicates that the request has been made by a db2agent. A pending request exists at the database server, awaiting the real-time statistics daemon to pick it up. QUEUED status indicates that the request has been picked up by the real-time statistics daemon. A queued request will be processed within the daemon's current evaluation. The real-time statistics daemon can check for pending requests at the server every five minutes. Once it has picked up a set of requests to put on its work queue, it will complete the queued requests before going back to the server for additional work.

The SYSPROC.MON_GET_AUTO_RUNSTATS_QUEUE table function, which complements MON_GET_RTS_RQST to query real-time statistics work, is available to query the background automatic runstats queue. When automatic statistics collection is enabled via the AUTO_RUNSTATS database configuration parameter, the auto-runstats daemon works in the background to evaluate if any database object requires updated statistics. The auto-runstats daemon can start an evaluation approximately every two hours. At the start of an evaluation, the auto-runstats daemon determines the set of objects (tables, nicknames, views) that it will evaluate. Each object is evaluated one at a time. If the auto-runstats daemon determines that the statistics for an object should be updated, it will perform an asynchronous collection for the object.

Listing 15 shows sample output from the MON_GET_AUTO_RUNSTATS_QUEUE table function. Objects that were evaluated and processed earlier in the evaluation are not displayed. If the auto-runstats daemon is performing an asynchronous collection on an object, the object status will indicate JOB_SUBMITTED. Objects that are yet to be evaluated will have EVALUATION_PENDING status.

Listing 15. Sample output from MON_GET_AUTO_RUNSTATS_QUEUE table function
SELECT QUEUE_POSITION,
       OBJECT_TYPE,
       OBJECT_STATUS,
       VARCHAR(OBJECT_SCHEMA, 10) AS SCHEMA,
       VARCHAR(OBJECT_NAME, 10) AS NAME
FROM TABLE(MON_GET_AUTO_RUNSTATS_QUEUE()) AS T
ORDER BY QUEUE_POSITION

QUEUE_POSITION OBJECT_TYPE OBJECT_STATUS        SCHEMA     NAME
-------------- ----------- -------------------- ---------- ----------
             1 TABLE       JOB_SUBMITTED        TEST       EMPLOYEE
             2 TABLE       EVALUATION_PENDING   TEST       T1
             3 TABLE       EVALUATION_PENDING   TEST       T2

3 record(s) selected.

The new MON_GET_AUTO_RUNSTATS_QUEUE and MON_GET_RTS_RQST table functions are useful serviceability enhancements if you need to troubleshoot automatic statistics collection.


Deprecation of automatic statistics profiling

Automatic statistics profiling (ASP) was an autonomic feature introduced in DB2 8.2. It assisted with recommending and creating statistics profiles to collect column group statistics based on workload. The function, which had significant restrictions, was not supported in DPF environments running multiple database partitions. It was not recommended for customers to enable ASP in production because of the performance penalty associated with its operation. ASP was incompatible with the section actuals feature introduced in DB2 9.7 and it provided statistics recommendations for limited predicates.

DB2 10.1 announced the deprecation of ASP and this feature has been discontinued in DB2 10.5. For statistics recommendations, you can use the Statistics Advisor of the IBM Data Studio tool. Resources has more about the Statistics Advisor.


Conclusion

Database statistics allow the query optimizer to determine optimal access plans. DB2 10.1 provides significant enhancements to the RUNSTATS facility. When you upgrade to DB2 10.1, you can immediately experience performance, usability, and serviceability enhancements. Experiment with index sampling to further improve RUNSTATS performance, and try out the new autonomics capabilities to simplify database administration.


Acknowledgments

Special thanks to Ivan Popivanov of the DB2 development team for his feedback on this article.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=962224
ArticleTitle=RUNSTATS: What's new in DB2 10 for Linux, UNIX, and Windows
publish-date=02132014