Results 1 to 5 of 5

Thread: Auto Runstats

  1. #1
    Join Date
    May 2007

    Exclamation Unanswered: 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
    Start Time = 08/28/2009 15:54:13.548344
    State = Executing
    Invocation Type = Automatic
    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?

    IBM Certified Database Administrator
    DB2 9 Advanced DBA for Linux, Unix and Windows

  2. #2
    Join Date
    May 2003
    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

  3. #3
    Join Date
    May 2007
    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

  4. #4
    Join Date
    May 2003
    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

  5. #5
    Join Date
    May 2007
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts