Enhancing sap by using db2 9 for zos july 2007 international technical support organization sg24723900. Using listdef and template best books online library. Sample the cpu time saving is not what you might expect it is not inversely proportional with the sample parm. By the example you provided, these freqvals are specified on the index columns. Runstats on all tables yes, including system tables reorgchk on all tables. Massive runstats cpu reductions when collecting db2 for z. The reason that zhpf performs so much better than ficon is the list prefetch optimizer. If you create a new index, you should run runstats on the table and all of its indexes, not just on the new index. Runstats shows that most of the latch statistics are much improved. Testing application programs is a discipline unto itself and there are many considerations and nuances to be mastered to be able to test software appropriately. How to allow write on table during the runstats and reorg for indexes. Compared to simple indexes, the index key values of expressionbased indexes are not the same as values in the table columns. Common db2 for zos runstats collection mistakes the.
Spinning disks of 10k rpm and 15k rpm were both measured. Runstats tablespace runstats index reorg load with inline runstats different types of statistics pure access path statistics. For example, consider the following runstats specification for dsn8810. There are a bewildering number of parameters that control how much information is collected. Runstats index syntax and options ibm knowledge center.
On index creation, loads and reorgs, db2 reserves empty space for future inserts on every leaf page. The following control statement specifies that runstats is to update the catalog statistics for index dsn8810. Db2 maintenance utilities performing maintenance activities on your databases is essential to ensure that they are optimized for performance and recoverability. Therefore, db2 is able to use the index key sequence to avoid a sort for freqval collection which means that the collection of frequency statistics is already efficient. The information is stored in the db2 catalog and is used by the sql optimizer to select access paths to data during the bind. As time goes by, inserts add entries whereas delete pseudodeletes the index entries and leaves holes. However, two latch statistics are terrible for the new version. Expressionbased indexes are best suited when you want an efficient evaluation of queries that involve a column expression. For dynamic sql optimization,use rts to work out when changes to a table have been significant enough to get new stats,or update someof your tables stats using the rts. The runstats utility db2 developers guide 5th edition. For example, a 300page book would have a finished typeset index of 1218 pages each containing approximately 100 entries and subentries. The runstats utility updates statistics in the system catalog tables to help with. It also collects statistics used for space management.
Another useful reference is the ibm press book titled db2 purexml cookbook, which. For example, if table distribution statistics were gathered on the first issue, and only index statistics are gathered on the second issue, then if inconsistencies are detected as a result of activity on the table, the table distribution statistics are dropped. Number of distinct values for the first column of the index key. With the highvalue data retained on db2 for zos and the z systems platform, customers are increasingly attempting to extract value from that data for competitive advantage. Runstats tablespace gathers statistics on a table space and, optionally, on tables, indexes or columns.
This topic describes how to display the statistics and how to understand the displayed results. Thus, continue to use runstats as you would for relational data. The timestamp when runstats was last run on this table space or partition. You can get an approximate estimate based on the date the last time the statistics were updated for a given index as a rebuild operation will also update the statistics for the index. Several sample db2 catalog queries were presented in chapter 26, db2 object monitoring using the db2. The information gathered is stored in the db2 system tables and used by the sql optimizer to select the best access paths during the bind process.
Say, pctfree10 of a 4k page books 400 bytes on every leaf. Db2 12 for zos also allows you to develop applications. Runstats index utility control statements define the operations completed by runstats utility jobs. Droprecovery statement documentation for log master for. The runstats utility collects statistical information for db2 tables, table spaces, partitions, indexes, and columns. Executing the runstats command on ibm db2 database during the pause following new repository creation this task is a step in process of performing an incremental upgrade of the siebel database from siebel crm version 8. You cannot avoid running runstats for larger objects because the statistics are perhaps even more important the larger the object becomes. The db2 optimizer uses these statistics to generate efficient execution plans for sqlxml queries. This blog post will not go into a lot of depth regarding testing practices, other than to highlight the important things to keep in mind to assure optimal performance of your db2 applications. Equally important is that the statistics of related objects are in synch. Depending on the dropped object that you are recovering, log master writes runstats commands into the runstats data set as follows. For db2 to select the best indexes to use, it is important to regularly weekly be running runstats on the tables in your database with this syntax. Db2 automated table runstats db2 already offers a method of doing this but in my experience short but quite varied, and on the recommendations of an outside consultant with much expertise we decided that using the db2 health monitor to monitor the tables and decide itself when to carry out the runstats on tables was not turning out to be the. The syntax lets you specify a table or indexed view along with a target index name, statistics name, or column name.
The code is easily adaptable and configurable so that, for example, you can. Legendary db2 tuning expert tony andrews tony the tuner draws on more than 23 years of db2related experience, empowering developers to take performance into their own hands whether. The runstats command below shows an example of using both the table and index sampling options. When a table has been loaded with data, and the appropriate indexes have. Runstats will scan tablespaces and indexes gathering information about utilization of space and efficiency of indexes.
This zparm is not applicable for statistics collection via an index since sort is already avoided. For example, if you collect numcols 2 count 10, but the next runstats execution does not specify this clause, then those statistics will remain from their prior collection. Pctfree, a percentage of the pagesize reserves space for row overflows on all leaf pages. The performance is measured in terms of mbps throughput. This leads to statistics becoming stale, and that can lead to access path issues. For db2 to select the best indexes to use, it is important to regularly weekly be running runstats. For example, you might want to direct db2 to choose a different index, a different order of the table flow, or a different join method. Command parameters table tablename specifies the table to reorganize. Db2 developers guide 5th edition,2004, isbn 06723262, ean 06723262, by mullins c. The schema is the user name under which the table was created. If the data in a large object changes slowly you can probably run runstats once, and then delay running it. The following control statement specifies that runstats is to update the catalog tables and history catalog tables with all statistics for table space db0e0101. This wellorganized, easytounderstand reference brings together 102 sqlrelated skills and techniques that any developer can use to build db2 applications that deliver consistently superior performance.
Every rdbms has hints you can code in sql statements to try to get the optimizer to choose something different in its optimization. Executing the runstats command on oracle database during. The number of levels in the index tree that were added or removed since the last reorg, rebuild index, or load replace. The runstats utility has been extended to collect statistics on xml data and xml indexes. For example, when the skip distance is 65 pages, ficon throughput is 2. Simple index keys consist of a concatenation of one or more specified table columns. Where the new code is run1 and the dynamic sql code is run2, the runstat lines that concern me are. The runstats online utility gathers summary information about the characteristics of data in table spaces, indexes, and partitions. Updating catalog and history tables and reporting all statistics.
It really is important to keep your statistics up to date. Here is an example using the adventureworks database. In addition, db2 12 for zos offers performance and functional improvements for both transactional and analytical workloads and makes installation and migration simpler and faster. With sample 1, the savings are around 60%, so it only makes sense to use sample parms from 10 to maybe 25. The number of records or lobs that have been inserteddeleted since the last runstats was executed on this index space or partition. If you are recovering a dropped database, log master generates the runstats command for all dropped table spaces, except auxiliary table. For example, in the worst case a row sample might select one row from each block, requiring a full scan of the table or index.
Reorgchk is a db2 utility that calculates a number of formulas to determine what kinds of reorgs are needed on what tables. For example, the high bit must be flipped for numbers and watch out. Db2 10 utilities sample runstats control statements. Db2 might choose not to use the index if statistics are old or if it doesnt match what the query needs well enough, but the index is always valid. Db2 records these statistics in the db2 catalog and uses them to select access paths to data during the bind process. Use runstats to gather statistics on xml data and indexes. Runstats is a db2 utility that scans a table space or indexes to gather information about space utilization and index efficiency. Simply stated, db2 generates better access plans if xml statistics are. A db2 utility used to collect statistics about the data values in tables which can be used by the optimizer to decide the access path. Index index name specifies the index to use when reorganizing the table. Sample runstats control statements ibm knowledge center. You can create a control statement with the ispfpdf.
Executing the runstats command on oracle database during the pause following new repository creation this task is a step in process of performing an incremental upgrade of the siebel database from siebel crm version 8. Dont collect runstats with reorg, it makes your cluster index always look 100% clustered which is only the reality just after the reorg. With a multicolumn index for city and state, the runstats utility can be used to collect frequent value statistics to learn about the correlation between the two columns. The runstats utility gathers summary information about the characteristics of data in table spaces, indexes, and partitions. Multipartition database plan with interpartition and intrapartition parallelism db2exfmt explain table format tool appendix o. Selectivity overrides fixing your filter factor problems. This is the command we use to perform runstats every week. Post this issue, i individually triggered stats for 3 of the indexes specific to one of the queries.
823 312 603 781 518 433 307 599 355 602 1405 1482 814 685 128 260 129 1332 386 1304 998 826 851 1025 1489 66 858 1390 685 1026 1203 627 1202 511 1036 115 1345 1326 974 728 479 1116 628 791 523 1131 1012 768