Saminnet-Search Article Wiki Forum Piwigo SNS Cloud vtiger Sugar
DB2 Runstats

  • Data-Articles
    • Baja iwf (106) Sun09,20:21pm

      sesuai post saya yang terakhirツdisini, rumah dua pohon sedang dalam pembangunan tahap 2 dengan material utama menggunakan baja. baja adalah material konstruksi yang paling padat apabila dibandingkan dengan beton dan kayu karena memiliki berat satuan yang cukup besar. karena baja bisa difabrikasi di pabrik, pemasangan di site bisa lebih cepat dibandingkan dengan beton yang mesti dicetak dan ditun…

      Read More...

DB2 Runstats

REORGツis used to help DB2 point to accurate data (ie, indexes should become aware of fresh data and no longer include deleted data), as well as "collapse" empty page space created by deletion of data and/or indexes. It can also help move data that is related closer to each other for more efficient access (especially true in the case of a cluster index).

RUNSTATSツis used to help gather updated statistics on the volume and/or distribution of data within tables and indexes. This information is stored in the system tables and is used by many things including the optimizer to determine the optimal path to query the data. This is especially true as data grows. The data may distribute in a way that causes the optimizer to now include an index that it didn't before in its access path (or perhaps vice versa and choose not to use an index).ツRUNSTATSツcan also affect how aツREORGツruns - at least in older versions of DB2.

Based on what I have read from DB2 DBA's you generally want to run

  1. RUNSTATS
  2. REORG
  3. RUNSTATS

The firstツRUNSTATSツhelps theツREORGツto work appropriately and efficiently. TheツRUNSTATSツafter is more to make sure that stats are now accurate given all the data movement around on pages. Depending on where you read you may see DBA's mention the lastツRUNSTATSツis no longer needed with newer versions of DB2. Since we haven't seen definitive answers on that and since most DB2 DBA's I've heard of follow the above order, our company has chosen to use that order (for the record we are on DB2 LUW 9.7 FP4 and we plan on migrating to V 10.1 sometime next year).

Also, just for the sake of completeness, generally when you update the statistics and you affect the optimizer you want all applications that call into DB2 to make sure they are taking advantage of those new optimizations. So aツREBINDツof packages will make sure that static packages know of the updated statistics and optimized query paths, and aツFLUSH PACKAGE CACHE DYNAMICツcleans out dynamic queries from things like Hibernate, so that way the queries will be rebound with the updated query paths. (NOTE: on z/OS I see you haveツBINDツandツREBINDツoptions. Perhaps theツFLUSH PACKAGE CACHE DYNAMICツis under those or has a different name on z.)

So ultimately I would do the following:

  1. RUNSTATS
  2. REORG
  3. RUNSTATS
  4. REBINDツpackages
  5. FLUSH PACKAGE CACHE DYNAMICツ(on LUW) or whatever the equivalent is on z/OS.

#########################################

    http://www.dbatodba.com/bullet.gif); list-style-type: square; font-family: 'Lucida Grande', Verdana, Lucida, Helvetica, Arial, sans-serif; font-size: 12.1440000534058px;">
  • Reorg for all tables
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';'from syscat.tables where type = 'T' " > reorg.out

db2 -tvf reorg.out

    http://www.dbatodba.com/bullet.gif); list-style-type: square; font-family: 'Lucida Grande', Verdana, Lucida, Helvetica, Arial, sans-serif; font-size: 12.1440000534058px;">
  • ツReorgchk for all tables
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';' from \
syscat.tables where type = 'T' " > reorgchk.out

db2 -tvf reorgchk.ou

    http://www.dbatodba.com/bullet.gif); list-style-type: square; font-family: 'Lucida Grande', Verdana, Lucida, Helvetica, Arial, sans-serif; font-size: 12.1440000534058px;">
  • Runstats for all tables
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' and indexes all;'from \
syscat.tables where type = 'T' " > runstats.out

db2 -tvf runstats.out

Executing reorg, reorgcheck and runstats for all tables from one specific tablespace.

    http://www.dbatodba.com/bullet.gif); list-style-type: square; font-family: 'Lucida Grande', Verdana, Lucida, Helvetica, Arial, sans-serif; font-size: 12.1440000534058px;">
  • Reorg for all tables from one specifc tablespace
db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';' \
from syscat.tables where type = 'T' and tbspace='<tablespace_name>'" > reorg.out

db2 -tvf reorg.out

    http://www.dbatodba.com/bullet.gif); list-style-type: square; font-family: 'Lucida Grande', Verdana, Lucida, Helvetica, Arial, sans-serif; font-size: 12.1440000534058px;">
  • Reorgchk for all tables from one specifc tablespace
db2 -x "select 'reorgchk update statistics on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';' from \
syscat.tables where type = 'T' and tbspace='<tablespace_name>'" > reorgchk.out

db2 -tvf reorgchk.ou

    http://www.dbatodba.com/bullet.gif); list-style-type: square; font-family: 'Lucida Grande', Verdana, Lucida, Helvetica, Arial, sans-serif; font-size: 12.1440000534058px;">
  • Runstats for all tables from one specifc tablespace
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' and indexes all;'from \
syscat.tables where type = 'T' " > runstas.out

db2 -tvf runstats.out

Comments   

 
0 #1 Guest 2017-07-25 23:40
I have read so many content regarding the blogger lovers but this
paragraph is in fact a pleasant paragraph, keep it up.


Feel free to surf to my site; PTCB
Quote
 

Category Database

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email