If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Auto Runstats

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-28-09, 16:48
rmarzullo rmarzullo is offline
Registered User
 
Join Date: May 2007
Posts: 56
Exclamation Auto Runstats

I am running DB2 v9.5 on Linux and I have the following issue:
---------------
For my database I have the following DB cfg:

-bash-3.2$ db2 get db cfg for figr1

Database Configuration for Database figr1

Database configuration release level = 0x0c00
Database release level = 0x0c00

Database territory = US
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = IDENTITY
Alternate collating sequence (ALT_COLLATE) =
Number compatibility = OFF
Varchar2 compatibility = OFF
Database page size = 4096

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = ENABLE

Restrict access = NO
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 50
Number of quantiles retained (NUM_QUANTILES) = 100

Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

Log retain for recovery status = NO
User exit for logging status = YES

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC

Database heap (4KB) (DBHEAP) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 1024
Log buffer size (4KB) (LOGBUFSZ) = 1024
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 524288
Buffer pool size (pages) (BUFFPAGE) = 1000
SQL statement heap (4KB) (STMTHEAP) = 16384
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Lock timeout (sec) (LOCKTIMEOUT) = 300

Changed pages threshold (CHNGPGS_THRESH) = 80
Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC
Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

Track modified pages (TRACKMOD) = OFF

Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = AUTOMATIC
Max DB files open per application (MAXFILOP) = 61440

Log file size (4KB) (LOGFILSIZ) = 16384
Number of primary log files (LOGPRIMARY) = 15
Number of secondary log files (LOGSECOND) = 20
Changed path to log files (NEWLOGPATH) =
Path to log files = /db2/bfmdv/log01/figr1/NODE0000/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0005784.LOG
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent max primary log space by transaction (MAX_LOG) = 85
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF

HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0

First log archive method (LOGARCHMETH1) = DISK:/db2/bfmdv/archlog/
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =

Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366
Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

Automatic maintenance (AUTO_MAINT) = ON
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = ON
Automatic runstats (AUTO_RUNSTATS) = ON
Automatic statement statistics (AUTO_STMT_STATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = ON
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF

Enable XML Character operations (ENABLE_XMLCHAR) = YES
WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0
-------------
I noticed that once in while when a massive delete/insert in some tables I can see Runstats running automatically (as expected)

Also here is the list utilities that I was able to capture this Auto Runstats:

db2 list utilties
ID = 8915
Type = RUNSTATS
Database Name = FIGR1
Partition Number = 0
Description = FIG_STG.STG_ORGANIZATION_EXTERNAL_DATA
Start Time = 08/28/2009 15:54:13.548344
State = Executing
Invocation Type = Automatic
Throttling:
Priority = 40

----------------------------
Another DBA who works with me insists that this scenario is impossible since my HEALTH_MON in my instance is OFF.
I don't believe this is a requirement for automatic runstats right?
Also the Invocation type in teh command above indicates that it was kicked automatically right?

Could anyone confirm this for me please?

Thanks
__________________
IBM Certified Database Administrator
DB2 9 Advanced DBA for Linux, Unix and Windows
Reply With Quote
  #2 (permalink)  
Old 08-28-09, 22:33
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,196
Auto Maintenance and Health Monitor are not the same thing. I consider both to be viruses.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #3 (permalink)  
Old 08-31-09, 09:30
rmarzullo rmarzullo is offline
Registered User
 
Join Date: May 2007
Posts: 56
Marcus,
I know both are not the same thing, I was just wondering if for any reason having Health Monitor on would trigger Auto Maintenance (in this case Auto Runstats).
What do mean by being both considered viruses? Should I disregard this feature?
__________________
IBM Certified Database Administrator
DB2 9 Advanced DBA for Linux, Unix and Windows
Reply With Quote
  #4 (permalink)  
Old 08-31-09, 12:46
Marcus_A Marcus_A is offline
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,196
To the best of my knowledge, having the health monitor on will not trigger auto runstats, but I admit to not being an expert in either of these "features." It may warn you about how long it has been since runstats were last run, but I don't really know about that either.

Either of these features can cause a lot of problems (including instance crashes depending on which release and fixpack you have), and I prefer to write and run my own scripts to do these things. These features are basically a marketing ploy to convince prospective buyers that DB2 doesn't need much in the way of DBA support; however, there are a lot of kinks that need to be worked out before I would use them.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #5 (permalink)  
Old 08-31-09, 13:15
rmarzullo rmarzullo is offline
Registered User
 
Join Date: May 2007
Posts: 56
Thanks Marcus,

Your answer matches 100% what I think. I told to the company that I am working for that those automatic things I am not a big fan of it. I really noticed, even after lots of mass updates that automatic runstats didn't run as I was expecting. The thresholds and the formula that DB2 uses for it is very complex.
Also we have experince of having instance problems because those "autonomics" that stole the whole machine memory. Auto reorg for example is another complex decision. Reorganizing tables to reclaim deleted rows could cause the HWM go up if it is not done in the proper order (check first which object holds the HWM). So using the old school by deciding what and when reorganize and update statistics it still teh best decision.
Thanks again for your feedback.
__________________
IBM Certified Database Administrator
DB2 9 Advanced DBA for Linux, Unix and Windows
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On