DB2数据库内存占用高时收集脚本

#!/bin/sh
###############################################################################################################
#Script name: Db2
#Script description: Get db2 memset.
#Current Release Version: 1.0.0
#Script Owner: He ,Haibo
#Latest editor: He, Haibo
#Support platform: Linux && AIX DB2 ,Support DB2 HADR&Alone&DPF,not support purescale.
#Change log: From Support Linux ONLY to Linux&AIX
#Date 2021-01-21 22:50:00
#
#
###############################################################################################################
export LANG=en_US
osbox=`uname`
paracount=$#
userName=`id | awk -F ' ' {'print $1'} | awk -F '(' {'print $2'} | awk -F ')' {'print $1'}`

if [ $paracount == 1 ];then
    DBName=$1
else
    echo "sh Db2_mem_Collect.sh <DBNAME>"
    echo 1
    exit 1
fi


Db2MemCollectPath="$HOME/db2_check_results"
datestamp=`date +"%Y%m%d"`.`date +"%H%M"`
db2Log=db2_Mem_$(date +%y%m%d).out

mkdirLogPath(){    
    if [[ ! -d $HOME/db2_check_results ]];then
        mkdir -p $HOME/db2_check_results
    fi
}

###打印日志函数
log_info(){
    DATE_N=`date "+%Y-%m-%d %H:%M:%S"`
    USER_N=`whoami`
    echo "${DATE_N} ${USER_N} execute $0 [INFO] $@"  >>  $Db2MemCollectPath/$db2Log 2>&1
}


log_error(){
    DATE_N=`date "+%Y-%m-%d %H:%M:%S"`
    USER_N=`whoami`
    echo -e "/033[41;37m ${DATE_N} ${USER_N} execute $0 [ERROR] $@ /033[0m" >> $Db2MemCollectPath/$db2Log 2>&1
}

fn_log(){
if [[ $? -eq 0 ]];then
    log_info "$@ sucessed."
    echo -e "/033[32m $@ sucessed. /033[0m"
else
    log_error "$@ failed."
    echo -e "/033[41;37m $@ failed. /033[0m"
    exit 1
fi
}

getInstanceUser(){
    ps -ef | grep db2sysc | grep -v grep | awk '{print $1}' |uniq | grep -i $userName > /dev/null
    if [[ $? == 0 ]];then
        continue
    else
        log_info "Current user is $userName,is not InstanceName or DB2 Instance is not exists."
        echo 1
        exit 1
    fi 
}    


get_os_linux_information(){
    ps -elf > $Db2MemCollectPath/ps_elf.out.$datestamp
    ps aux > $Db2MemCollectPath/ps_aux.out.$datestamp
    ipcs -am > $Db2MemCollectPath/ipcs_am.out.$datestamp
    vmstat 1 5 > $Db2MemCollectPath/vmstat_1_5.out.$datestamp
    vmstat -wt  1 30 > $Db2MemCollectPath/vmstat_1_30.out.$datestamp
    free -m > $Db2MemCollectPath/free_m.out.$datestamp
    free -g > $Db2MemCollectPath/free_g.out.$datestamp
    ps auxx | head -n 1 > $Db2MemCollectPath/ps_auxx.out.$datestamp;ps auxx | sort -k 6,6rn >> $Db2MemCollectPath/ps_auxx.out.$datestamp
}

get_os_AIX_information(){
    ps -elf > $Db2MemCollectPath/ps_elf.out.$datestamp
    ps aux > $Db2MemCollectPath/ps_aux.out.$datestamp
    svmon -G > $Db2MemCollectPath/svmon_G.out.$datestamp
    svmon -P >  $Db2MemCollectPath/svmon_P.out.$datestamp
    svmon -U $userName  > $Db2MemCollectPath/svmon_U.out.$datestamp
}


getIndirectDBnames(){
        if [[ "$osbox" = "Linux" ]];then
                get_os_linux_information
        elif [[ "$osbox" = "AIX" ]];then
                get_os_AIX_information
        fi
}

getDBArchitecture(){
    instancepath=`cat /etc/passwd | grep -i $userName | awk -F ':' {'print $6'}`
    ARCHITECTURE=`cat "$instancepath"/sqllib/db2nodes.cfg | awk '{if(NF > 3) print "Purescale"; else if(NR > 1) print "DPF"; else print "Alone"}' | tail -1`
    if [[ "$ARCHITECTURE" == "Alone" ]];then
        #cause HADR only in "Alone" architecture
        hadr_role=`db2 get db cfg for $DBName | grep -i role | awk '{print $5}'`
        if [[ "$hadr_role" == "PRIMARY" || "$hadr_role" == "STANDARD" ]];then
            ARCHITECTURE="HADR_P"
        else
            ARCHITECTURE="HADR_S"
        fi
    elif [[ "$ARCHITECTURE" == "DPF" ]];then
        hadr_role="DPF"
    else
        log_info "This ARCHITECTURE is $ARCHITECTURE, will not support."
        echo 1
        exit 1
    fi
}

getDb2Mem(){
    if [[ $hadr_role == PRIMARY || $hadr_role == STANDARD ]];then
        get_mem
    elif [[ $hadr_role == DPF ]];then
        get_dpf_mem
    else
        log_info "Current hadr_role does not support."
        echo 1
        exit 1
    fi
}

get_dpf_mem(){
    db2pd -osinfo > $Db2MemCollectPath/db2pd_osinfo.$datestamp
    db2pd -dbptnmem -alldbp > $Db2MemCollectPath/db2pd_dbptnmem.out.$datestamp
    db2pd -inst -memsets -memp -alldbp > $Db2MemCollectPath/db2pd_mem_inst.out.$datestamp
    db2pd -alldbs -memsets -memp -alldbp > $Db2MemCollectPath/db2pd_mem_dbs.out.$datestamp
    db2pd -alldbs -app -activestatement -alldbp > $Db2MemCollectPath/db2pd_app.out.$datestamp
    db2pd -agent -util -edu -alldbp > $Db2MemCollectPath/db2pd_edu.out.$datestamp
    db2pd -memblocks sort -alldbp > $Db2MemCollectPath/db2pd_memb_inst.out.$datestamp
    db2pd -alldbs -memblocks sort -alldbp > $Db2MemCollectPath/db2pd_memb_dbs.out.$datestamp
}

get_mem(){
    db2pd -osinfo > $Db2MemCollectPath/db2pd_osinfo.$datestamp
    db2pd -dbptnmem > $Db2MemCollectPath/db2pd_dbptnmem.out.$datestamp
    db2pd -inst -memsets -memp > $Db2MemCollectPath/db2pd_mem_inst.out.$datestamp
    db2pd -alldbs -memsets -memp > $Db2MemCollectPath/db2pd_mem_dbs.out.$datestamp
    db2pd -alldbs -app -activestatement > $Db2MemCollectPath/db2pd_app.out.$datestamp
    db2pd -agent -util -edu > $Db2MemCollectPath/db2pd_edu.out.$datestamp
    db2pd -memblocks sort > $Db2MemCollectPath/db2pd_memb_inst.out.$datestamp
    db2pd -alldbs -memblocks sort > $Db2MemCollectPath/db2pd_memb_dbs.out.$datestamp
}

get_db2_cfg(){
    db2 get dbm cfg > $Db2MemCollectPath/dbm_cfg.$datestamp
    db2 get db cfg for $DBName > $Db2MemCollectPath/db_cfg.$datestamp
    db2set -all  > $Db2MemCollectPath/db2set_all.$datestamp
}

get_sql_information(){
    db2 connect to $DBName > /dev/null 
    db2 "SELECT edu_id, varchar(memory_set_type, 20) AS set_type, varchar(memory_pool_type,20) AS pool_type, varchar(db_name, 20) AS dbname, memory_pool_used, memory_pool_used_hwm,APPLICATION_HANDLE, EDU_ID, MEMBER FROM TABLE(MON_GET_MEMORY_POOL(NULL, CURRENT_SERVER, -2)) where memory_pool_type='PRIVATE'" >$Db2MemCollectPath/private.out.$datestamp
    db2 terminate > /dev/null
}

main(){
    mkdirLogPath
    getInstanceUser
    getIndirectDBnames
    getDBArchitecture
    get_db2_cfg
    getDb2Mem
    get_sql_information
}

main

https://www.cndba.cn/hbhe0316/article/22186 https://www.cndba.cn/hbhe0316/article/22186 https://www.cndba.cn/hbhe0316/article/22186 https://www.cndba.cn/hbhe0316/article/22186

https://www.cndba.cn/hbhe0316/article/22186

https://www.cndba.cn/hbhe0316/article/22186

https://www.cndba.cn/hbhe0316/article/22186

https://www.cndba.cn/hbhe0316/article/22186

https://www.cndba.cn/hbhe0316/article/22186 https://www.cndba.cn/hbhe0316/article/22186

版权声明：本文为博主原创文章，未经博主允许不得转载。

DB2