I've a partitioned by range (year) tablespace with an index. All partitions, except the last, are loaded with information of past years. After RUNSTATS the tablespace, the last partition have its counters to 0. An explain of a SELECT limited to the year of the last partition give me a tablespace scan, a SELECT limited to other year give me a matching index.

It's possible to 'copy' the statistics from a partition to the last partition to get a matching index in the last partition?

Regards.

 

 

You need to be a member of WorldofDb2 to add comments!

Join WorldofDb2

Email me when people reply –

Replies

  • Thiago Rossetto sent this comment:

     

    Here is my suggestion. Are you using "Admin tool"? If so, then you can do the following -

    1. "GEN" at table side

    2. Choose "Y" for index and the rest can be set as "N".

    3. Search for "Generate catalog stats:" - Choose ONLY

    4. Search for "Execution mode:" - Chosse TSO or Batch.

    5. All done!

    Now you have all the information that you need. You can manually altered the stats and load it again.

    Another way is to use command "UR" at index side. Using this it you'll be able to edit the stats online!

  • If you check further into the tablespace scan, you may find that it is scanning only the last partition, which has very little data.  As soon as you have any significant amount of data in that partition, you can and should run RUNSTATS SHRLEVEL CHANGE on the partition to change the potential access paths.  This can be run during midday, or whenever you think you have enough data to need an index access path.

    --Phil Sevetson

    • Thanks, but I would like to update the last partition statistics with data from another partition before the binding of the program. Wich tables/columns of DB2 catalog must I update?

      Regards,

      Jose-Ramon Vazquez

    • There are several ways to approach this. First of all I'd check out the Runstats chapter in the Utility Guide. There is a table in there which describes which catalog tables are updated when runstats are run with various options. Be aware that some tables are only updated for 'SPACE' information and not used by the optimiser. After checking that out one option would be to run RUNSTATS in a REPORT ONLY mode for access path update only for another populated partition. You can then see in the output the tables and values updated and use this to craft some SQL for your empty partition. Personally, I've written a rexx to convert the output of a report only into SQL for updating stats. This can be useful if you don't actually want to update the catalog with a runstats but you may want to update a test environment with the production stats that would have been applied. Maybe an option that IBM could consider providing?
      Anyway, try that. Alternatively maybe you could try something such as data studio to extract the stats that you currently have and then manipulate them as required. I think you'll find SYSTABSTATS and SYSINDEXSTATS may be key and a little tweak to these tables may be all you need  but column distribution stats probably also important. Finally you'll need to decide whether you want to provide new figures for totals such as CARDF, first/full keycard etc across the table as a whole but you probably won't need this just in order to flip the access path.

    • Reading the RUNSTATS chapter of Utility Guide and Reference, I did the following UPDATE:

      UPDATE SYSIBM.SYSTABSTATS                     
             SET CARDF = 100000000 , NPAGES = 1000000
      WHERE                                         
              DBNAME     = 'CVBDP3'                        
      AND  TSNAME     = 'CVE3MVHS'                      
      AND  PARTITION = 15    

      being partition 15 the last empty partition. Now I've a pretty matching index on 4 columns.   

      Thanks to Ian, Phil and Terry.

      Regards.

                          

    • What if you set the table to be "volatile" wouldn't it take the index as well ?

    • Right!, this is a good option.

      Regards.

  • In DB2 10 for z/OS, optimizer will validate the zero statistics against RTS at bind/prepare. Prior to DB2 10, one option is to copy the statistics - although some of the data values will not be representative (because they are for a different year). Another alternative is to aggressively run RUNSTATS as the partition initially grows from zero rows.

    My assumption is that you should only hit this issue once per year given the current partitioning scheme, until you are at DB2 10 where a solution is available.

    Regards

    Terry Purcell

This reply was deleted.