Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55

    Unanswered: Shared Sort allocated w/o intra-partition parallelism OR connection concentrator

    Hello DB2 friends, I have an odd mystery I'm hoping someone can enlighten me on.

    I have a DB2 LUW 9.1 ESE database running on Linux Suse 10. I'm trying to learn all about DB2's memory model, but I can't figure out how/why shared sort heap is being allocated in the database global memory.

    In my dbm cfg, (INTRA_PARALLEL) = NO & my MAX_CONNECTIONS is equal to MAX_COORDAGENTS.

    When I read this Memory allocation in DB2
    it makes me think shared sort heap should not be part of the database global memory. What am I missing?

    Here's the output from the memory tracker:

    dbiecs1p@MGPDB2L01:~> db2mtrk -d -i -v
    Tracking Memory on: 2013/04/29 at 16:05:01

    Memory for instance

    Database Monitor Heap is of size 1638400 bytes
    Other Memory is of size 30146560 bytes
    Total: 31784960 bytes

    Memory for database: ECDWF01P

    Backup/Restore/Util Heap is of size 327680 bytes
    Package Cache is of size 42336256 bytes
    Catalog Cache Heap is of size 1769472 bytes
    Buffer Pool Heap (9) is of size 33292288 bytes
    Buffer Pool Heap (8) is of size 33423360 bytes
    Buffer Pool Heap (7) is of size 4653056 bytes
    Buffer Pool Heap (6) is of size 16908288 bytes
    Buffer Pool Heap (5) is of size 6750208 bytes
    Buffer Pool Heap (4) is of size 16908288 bytes
    Buffer Pool Heap (3) is of size 4653056 bytes
    Buffer Pool Heap (2) is of size 16908288 bytes
    Buffer Pool Heap (1) is of size 34406400 bytes
    Buffer Pool Heap (System 32k buffer pool) is of size 917504 bytes
    Buffer Pool Heap (System 16k buffer pool) is of size 655360 bytes
    Buffer Pool Heap (System 8k buffer pool) is of size 524288 bytes
    Buffer Pool Heap (System 4k buffer pool) is of size 458752 bytes
    Shared Sort Heap is of size 2424832 bytes
    Lock Manager Heap is of size 1703936 bytes
    Database Heap is of size 19136512 bytes
    Other Memory is of size 458752 bytes
    Total: 238616576 bytes


    Here's my dbm cfg:


    Database Manager Configuration

    Node type = Enterprise Server Edition with local and remote clients

    Database manager configuration release level = 0x0b00

    CPU speed (millisec/instruction) (CPUSPEED) = 2.479807e-07
    Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02

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

    Default charge-back account (DFT_ACCOUNT_STR) =

    Java Development Kit installation path (JDK_PATH) = /home/dbiecs1p/sqllib/java/jdk64

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

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

    SYSADM group name (SYSADM_GROUP) = DB2GRP1
    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) =
    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) = /home

    Database monitor heap size (4KB) (MON_HEAP_SZ) = 1024
    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) = 1250
    Agent pool size (NUM_POOLAGENTS) = 625(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) = db2c_dbiecs1p
    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
    Maximum Asynchronous TQs per query (FEDERATED_ASYNC) = 0

    No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC
    No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC
    Node connection elapse time (sec) (CONN_ELAPSE) = 10
    Max number of node connection retries (MAX_CONNRETRIES) = 5
    Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

    db2start/db2stop timeout (min) (START_STOP_TIME) = 10

    Any help, thought or suggestions are welcome! Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    "When the value of the database manager configuration parameter sheapthres is 0, all sort memory consumers for the database will use the database shared memory with sheapthres_shr instead of private sort memory."

    sheapthres_shr - Sort heap threshold for shared sorts configuration parameter
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2011
    Location
    Columbus, OH
    Posts
    55
    Ah, thank you for the clarification!

Posting Permissions

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