Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005
    Posts
    102

    Unanswered: DB2 processes taking lot of memory

    Hi All,

    Our CPU is hitting 99% usage almost everyday and when I do a ps -ef, I see bunch of these process which are running for a very long time.

    sample 4833344 1 80 Jul 07 - 37354:44 /home/sample/sqllib/bin/db2bp 3391630A1022 5 A

    Could somebody help me in understanding what these are?

    ENV: DB2 UDB v 8.2 FP 7, AIX

    Thanks

    Anks

  2. #2
    Join Date
    Mar 2004
    Posts
    46
    The "db2bp" is the background process for the db2 command line processor (also known as db2cmd or simply db2).

    You can get a good understanding of the DB2 processes from an article at http://www-128.ibm.com/developerwork...0304chong.html

    Finally, this seems to be an attempt to investigate into a performance problem where you don't know the user, application or query causing the issue.

    Here' a query I have used successfully to help in those kind of investigation.
    Note that this script collects all the relevant data - you still need to analyze it ! Save the text below to a script and run it when you observe performance degrading and then kill it (Ctrl-C) sometime after the performance has retuned to "normal".

    #==== Script begins below ==================
    #!/bin/ksh

    PROGNAME="`basename $0`"

    if [[ -z $DB2INSTANCE ]]
    then
    echo "\n\nDB2 Instance is not defined - please set environment first\n\n"
    exit 1
    fi

    if [[ -z $1 ]]
    then
    echo "\n\nPlease provide database name to monitor\n\n"
    echo "Usage: $PROGNAME <database_name>\n\n"
    exit 2
    fi

    DATABASE_NAME=$1

    START_TIMESTAMP="`date '+%Y-%m-%d.%H.%M'`"
    DBM_SNAPSHOT_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.dbm.txt" ; > $DBM_SNAPSHOT_FILE
    DB_SNAPSHOT_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.db.txt" ; > $DB_SNAPSHOT_FILE
    LOCK_SNAPSHOT_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.lock.txt" ; > $LOCK_SNAPSHOT_FILE
    APPLICATION_SNAPSHOT_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.app.txt" ; > $APPLICATION_SNAPSHOT_FILE
    BUFFERPOOL_SNAPSHOT_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.bp.txt" ; > $BUFFERPOOL_SNAPSHOT_FILE
    TABLE_SNAPSHOT_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.tab.txt" ; > $TABLE_SNAPSHOT_FILE
    TABLESPACE_SNAPSHOT_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.tbs.txt" ; > $TABLESPACE_SNAPSHOT_FILE
    SQL_SNAPSHOT_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.sql.txt" ; > $SQL_SNAPSHOT_FILE
    VMSTAT_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.vmstat.txt" ; > $VMSTAT_FILE
    PS_FILE="${DATABASE_NAME}-${START_TIMESTAMP}.ps.txt" ; > $PS_FILE

    echo "\n\nStarting data collection.\nTo stop, press <CONTROL-C> or kill this process (PID = $$)......\n\n"
    COUNTER=0
    while true
    do
    #set -x
    COUNTER="`expr $COUNTER + 1`"
    TIMESTAMP="`date`"
    echo "...${COUNTER}\c"
    echo "\n\n$TIMESTAMP\n\n" >>$DBM_SNAPSHOT_FILE
    echo "\n\n$TIMESTAMP\n\n" >>$DB_SNAPSHOT_FILE
    echo "\n\n$TIMESTAMP\n\n" >>$LOCK_SNAPSHOT_FILE
    echo "\n\n$TIMESTAMP\n\n" >>$APPLICATION_SNAPSHOT_FILE
    echo "\n\n$TIMESTAMP\n\n" >>$BUFFERPOOL_SNAPSHOT_FILE
    echo "\n\n$TIMESTAMP\n\n" >>$TABLE_SNAPSHOT_FILE
    echo "\n\n$TIMESTAMP\n\n" >>$TABLESPACE_SNAPSHOT_FILE
    echo "\n\n$TIMESTAMP\n\n" >>$VMSTAT_FILE
    echo "\n\n$TIMESTAMP\n\n" >>$PS_FILE
    vmstat 1 9 >> ${VMSTAT_FILE} &
    ps -elf >>$PS_FILE
    db2 get snapshot for database manager >>$DBM_SNAPSHOT_FILE
    db2 get snapshot for database on $DATABASE_NAME >>$DB_SNAPSHOT_FILE
    db2 get snapshot for locks on $DATABASE_NAME >>$LOCK_SNAPSHOT_FILE
    db2 get snapshot for applications on $DATABASE_NAME >>$APPLICATION_SNAPSHOT_FILE
    db2 get snapshot for bufferpools on $DATABASE_NAME >>$BUFFERPOOL_SNAPSHOT_FILE
    db2 get snapshot for tables on $DATABASE_NAME >>$TABLE_SNAPSHOT_FILE
    db2 get snapshot for tablespaces on $DATABASE_NAME >>$TABLESPACE_SNAPSHOT_FILE
    db2 get snapshot for dynamic sql on $DATABASE_NAME >>$SQL_SNAPSHOT_FILE
    # kill -23 `ps -elf|grep -i $DB2INSTANCE |grep db2ag|grep -v grep| awk '{print $4}'`
    sleep 10
    done
    #============ Script ends above ====================

Posting Permissions

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