Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007

    Unanswered: Performance Degradation going from DB2 v8.2 to DB2 v9.1

    Hi all,

    Current version:
    RedHat 4.4
    DB2 32-bit v9.1.0.2 Fix Pack 2
    Express Edition

    Our database has two phases:

    1) A bunch of inserts to 2 tables
    2) Queries on these tables

    There are usually on a few cases of the first phase.

    Under v8.2 the first phase took 90 minutes, under v9.1
    it takes 170 minutes. The queries take roughly the same time.
    I tried using the configuration assistant and it made inserting
    3 times as bad ( 6 hours).
    Did an "alter table append on " only reduced the time for inserts by 10 minutes.

    Any ideas on where to start?


  2. #2
    Join Date
    Jan 2003
    do you have the same hardware? Can you post insert statement sintax? Can you post dbm cfg and db cfg.
    db2 get dbm cfg
    db2 get db cfg for <database>

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Did you enable compression, by any chance?
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jul 2007
    dbm cfg:

    Database Manager Configuration

    Node type = Database Server with local and remote clients

    Database manager configuration release level = 0x0b00

    CPU speed (millisec/instruction) (CPUSPEED) = 3.424496e-07

    Max number of concurrently active databases (NUMDB) = 8
    Federated Database System Support (FEDERATED) = NO
    Transaction processor monitor name (TP_MON_NAME) =

    Default charge-back account (DFT_ACCOUNT_STR) =

    Java Development Kit installation path (JDK_PATH) = /opt/FastScale/home/fsrepo/sqllib/java/jdk32

    Diagnostic error capture level (DIAGLEVEL) = 3
    Notify Level (NOTIFYLEVEL) = 3
    Diagnostic data directory path (DIAGPATH) = /opt/FastScale/home/fsrepo/sqllib/db2dump

    Default database monitor switches
    Buffer pool (DFT_MON_BUFPOOL) = OFF
    Lock (DFT_MON_LOCK) = OFF
    Sort (DFT_MON_SORT) = OFF
    Statement (DFT_MON_STMT) = OFF
    Table (DFT_MON_TABLE) = OFF
    Timestamp (DFT_MON_TIMESTAMP) = ON
    Unit of work (DFT_MON_UOW) = OFF
    Monitor health of instance and databases (HEALTH_MON) = ON

    SYSCTRL group name (SYSCTRL_GROUP) =
    SYSMAINT group name (SYSMAINT_GROUP) =
    SYSMON group name (SYSMON_GROUP) =

    Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
    Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
    Group Plugin (GROUP_PLUGIN) =
    GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
    Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
    Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
    Server Userid-Password Plugin (SRVCON_PW_PLUGIN) = db2_fs_auth
    Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
    Database manager authentication (AUTHENTICATION) = SERVER
    Cataloging allowed without authority (CATALOG_NOAUTH) = NO
    Trust all clients (TRUST_ALLCLNTS) = YES
    Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
    Bypass federated authentication (FED_NOAUTH) = NO

    Default database path (DFTDBPATH) = /opt/FastScale/home/fsrepo

    Database monitor heap size (4KB) (MON_HEAP_SZ) = 90
    Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
    Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
    Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC
    Backup buffer default size (4KB) (BACKBUFSZ) = 1024
    Restore buffer default size (4KB) (RESTBUFSZ) = 1024

    Sort heap threshold (4KB) (SHEAPTHRES) = 0

    Directory cache support (DIR_CACHE) = YES

    Application support layer heap size (4KB) (ASLHEAPSZ) = 15
    Max requester I/O block size (bytes) (RQRIOBLK) = 32767
    Query heap size (4KB) (QUERY_HEAP_SZ) = 1000

    Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

    Priority of agents (AGENTPRI) = SYSTEM
    Max number of existing agents (MAXAGENTS) = 200
    Agent pool size (NUM_POOLAGENTS) = 100(calculated)
    Initial number of agents in pool (NUM_INITAGENTS) = 0
    Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
    Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS
    Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS

    Keep fenced process (KEEPFENCED) = YES
    Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS
    Initial number of fenced processes (NUM_INITFENCED) = 0

    Index re-creation time and redo index build (INDEXREC) = RESTART

    Transaction manager database name (TM_DATABASE) = 1ST_CONN
    Transaction resync interval (sec) (RESYNC_INTERVAL) = 180

    SPM name (SPM_NAME) =
    SPM log size (SPM_LOG_FILE_SZ) = 256
    SPM resync agent limit (SPM_MAX_RESYNC) = 20
    SPM log path (SPM_LOG_PATH) =

    TCP/IP Service name (SVCENAME) = fsrepo1
    Discovery mode (DISCOVER) = SEARCH
    Discover server instance (DISCOVER_INST) = ENABLE

    Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
    Enable intra-partition parallelism (INTRA_PARALLEL) = NO

    No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC
    No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC
    db2start/db2stop timeout (min) (START_STOP_TIME) = 10

  5. #5
    Join Date
    Jul 2007

    db cfg

    Database Configuration for Database

    Database configuration release level = 0x0b00
    Database release level = 0x0b00

    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) =
    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) = 10
    Number of quantiles retained (NUM_QUANTILES) = 20

    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 = NO

    Self tuning memory (SELF_TUNING_MEM) = ON
    Size of database shared memory (4KB) (DATABASE_MEMORY) = COMPUTED
    Database memory threshold (DB_MEM_THRESH) = 10
    Max storage for lock list (4KB) (LOCKLIST) = 2048
    Percent. of lock lists per application (MAXLOCKS) = 60
    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) = 2558
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 260
    Log buffer size (4KB) (LOGBUFSZ) = 1024
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 52949
    Buffer pool size (pages) (BUFFPAGE) = 1000
    Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
    Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
    Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

    SQL statement heap (4KB) (STMTHEAP) = 65536
    Default application heap (4KB) (APPLHEAPSZ) = 1024
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

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

    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) = 64

    Log file size (4KB) (LOGFILSIZ) = 5000
    Number of primary log files (LOGPRIMARY) = 13
    Number of secondary log files (LOGSECOND) = 10
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /opt/FastScale/home/fsrepo/fsrepo/NODE0000/SQL00001/SQLOGDIR/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file =
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Percent max primary log space by transaction (MAX_LOG) = 0
    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) = 520
    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

    First log archive method (LOGARCHMETH1) = OFF
    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

    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) = ON
    Automatic table maintenance (AUTO_TBL_MAINT) = ON
    Automatic runstats (AUTO_RUNSTATS) = ON
    Automatic statistics profiling (AUTO_STATS_PROF) = OFF
    Automatic profile updates (AUTO_PROF_UPD) = OFF
    Automatic reorganization (AUTO_REORG) = OFF

  6. #6
    Join Date
    Jul 2007
    Sorry Guys to inturpt your thread...but can some expert help me on my thread of SQL1272N ? Just trying get the help from the active apologies again...

Posting Permissions

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