db2

#=====begin of copyright notice=====
copyright()
{
        echo -e "

        THE AULIX_UTILS FOR DEBIAN AND CENTOS \n
        The AUTHOR of this file is Alexander Borisovich Prokopyev, Kurgan, Russia \n
        More info can be found at the AUTHOR's website: http://www.aulix.com/resume \n
        Contact: alexander.prokopyev at aulix dot com \n

        Copyright (c) Alexander Prokopyev, 2006-2014 \n

        All materials contained in this file are protected by copyright law. \n
        Nobody except the AUTHOR may alter or remove this copyright notice from copies of the content. \n

        The AUTHOR allows to use this content under AGPL v3 license:
        http://opensource.org/licenses/agpl-v3.html

        ";
}

copyright;
#=====end of copyright notice=====


Action=$1
#Version=$2
#set -x

flush_cmd=/utils/disk/flush.sh;
notify_admin_cmd=/utils/custom/notify_admin.sh;

if test -d /opt/IBM/db2/V8.1; then
        Version="8.1"
else
        if test -d /opt/ibm/db2/V9.7; then
                Version="9.7";
        fi
        if test -d /opt/ibm/db2/V10.1; then
                Version="10.1";
        fi
fi

#case $Action in
#( create_instance | drop_instance | upgrade_instance )
        case $Version in
        ( 8.1 )
                DB2Path=/opt/IBM/db2/V8.1;
                UpdateLevelCmd="db2updv8";
        ;;
        ( 9.7 )
                DB2Path=/opt/ibm/db2/V9.7;
                UpdateLevelCmd="db2updv97";
        ;;
        ( 10.1 )
                DB2Path=/opt/ibm/db2/V10.1;
                UpdateLevelCmd="db2updv97";
        ;;
        (*)
                echo "Please specify the version of your db2 just after action arg: 8.1 or 9.7";
                exit 0
        ;;
        esac
#;;
#esac;

rebind_client()
{
        db2 bind /home/db2inst/sqllib/bnd/@db2ubind.lst blocking all grant public sqlerror continue;
        db2 bind /home/db2inst/sqllib/bnd/@db2cli.lst blocking all grant public;
#       =========== Windows:
#       cd C:\Program Files\IBM\SQLLIB\bnd\
#       db2cmd
#       db2 connect to $DBName user db2inst using
#       db2 bind @db2ubind.lst blocking all grant public sqlerror continue
#       db2 bind @db2cli.lst blocking all grant public
}

config_dbm()
{
        ParamName=$1; Value=$2;
        db2 update dbm cfg using $ParamName $Value;
}

config_db()
{
        DBName=$1; ParamName=$2; Value=$3;
        db2 update db cfg for $DBName using $ParamName $Value;
}

stop_db2()
{
        db2stop force;
        $flush_cmd;
}

restart_db2()
{
        stop_db2;
        db2start;
}

restart_db2_verified()
{
        DBName=$1;

        db2stop force;
        sleep 1s;
        # db2start; exit 0 # skip db2dart test
        mkdir /tmp/db2dart; rm -f /tmp/db2dart/*; cd /tmp/db2dart;
        if db2dart $DBName | grep "The requested DB2DART processing has completed successfully"; then
                echo "DB integrity is Ok";
                sleep 1s;
                db2start;
        else
                echo "DB integrity is BAD";
                $notify_admin_cmd "DB integrity is BAD";
        fi;
}

get_dbm_param()
{
        ParamName=$1;
        db2 get dbm config | grep "($ParamName)"| awk -F" = " '{print $2}';
}

enable_monitoring()
{
        config_dbm DFT_MON_BUFPOOL ON;
        config_dbm DFT_MON_LOCK ON;
        config_dbm DFT_MON_SORT ON;
}

case $Action in

( install_wse )
        cat /utils/db2/db2_rpm_list_wse.txt | /utils/exec_for_each_line.sh 'rpm -i' '.x86_64.rpm'
;;

( install_ese )
        cat /utils/db2/db2_rpm_list_ese.txt | /utils/exec_for_each_line.sh 'rpm -i' '.x86_64.rpm'
;;

( install_client )
        cat /utils/db2/db2_rpm_list_client.txt | /utils/exec_for_each_line.sh 'rpm -i' '.i386.rpm'
;;

( create_client )
        /utils/db2/do.sh delete_users
        groupadd -g 10999 db2iadm
        useradd -u 11004 -g db2iadm -m -d /home/db2inst db2inst
        usermod -aG db2iadm root
        #/opt/IBM/db2/V8.1/instance/db2istrt - purpose?
        $DB2Path/instance/db2icrt -a SERVER -s client db2inst
;;

( uninstall )
        cat /utils/db2/db2_rpm_list_all.txt | /utils/exec_for_each_line.sh 'rpm -e'
;;

( switch_to_wse )
        rpm -e IBM_db2conn81-8.1.3-156 IBM_db2pext81-8.1.3-156 IBM_db2essg81-8.1.3-156
        /utils/db2/do.sh install_wse
;;

( create_users )
        groupadd -g 10999 db2iadm
        groupadd -g 10998 db2fadm
        groupadd -g 10997 dasadm
        useradd -u 11004 -g db2iadm -m -d /home/db2inst db2inst
        useradd -u 11003 -g db2fadm -m -d /home/db2fenc db2fenc
        useradd -u 11002 -g dasadm -m -d /home/dasusr dasusr
        usermod -aG db2iadm,dasadm root
;;

( delete_users )
        userdel -r db2inst
        userdel -r db2fenc
        userdel -r dasusr
        groupdel db2iadm
        groupdel db2fadm
        groupdel  dasadm
;;

( recreate_users )
        /utils/db2/do.sh delete_users
        /utils/db2/do.sh create_users
;;

( create_instance )
#       set -x
        /utils/db2/do.sh recreate_users;
#       License=$3
#       if test -n $License; then
#               License="-s "$License
#       fi
        $DB2Path/instance/dascrt -u dasusr;
        $DB2Path/instance/db2icrt $2 $3 $4 $5 $6 $7 $8 -u db2fenc db2inst;
#       -w 64
        $DB2Path/cfg/db2ln;

        echo -e "\nsource /home/db2inst/sqllib/db2profile" >> /root/.bashrc;
        #echo -e "\nsource /home/dasusr/das/dasprofile" >> /root/.bashrc

        source /home/db2inst/sqllib/db2profile;
#       db2set DB2COMM=tcpip
#       db2 update dbm config using svcename 50000
        db2start;
;;

( stop_das )
        case $Version in
        ( 8.1 )
                su -c "source /home/dasusr/.profile; db2admin stop;" dasusr;
        ;;
        ( 9.7 | 10.1 )
                su -c "source /home/dasusr/das/dasprofile; db2admin stop;" dasusr;
        ;;
        esac;
;;

( start_das )
        case $Version in
        ( 8.1 )
                su -c "source /home/dasusr/.profile; db2admin start;" dasusr;
        ;;
        ( * )
                su -c "source /home/dasusr/das/dasprofile; db2admin start;" dasusr;
        ;;
        esac;
;;

#( stop_before_fixpak )
#       su - db2inst -c "db2 force applications all;db2 terminate;db2stop"
#       su - tstdbadm -c "db2admin stop"
#       /opt/ibm/db2/V9.7/bin/db2fmcu -d
#       /opt/ibm/db2/V9.7/bin/db2fm -D -i tstdbadm
#       su - db2inst -c "ipclean"
#;;

( stop_dog )
        $DB2Path/bin/db2fmcu -d
        killall -s 9 db2fmcd db2fmd db2bp db2dasrrm
;;

( drop_instance )
#       set -x
        db2stop
        $DB2Path/instance/db2idrop db2inst
        /utils/db2/do.sh stop_das
        $DB2Path/instance/dasdrop
        /utils/db2/do.sh stop_dog
;;

( upgrade_db )
        DBName=$2;
        db2start;
        db2 migrate db $DBName;
        $UpdateLevelCmd -d $DBName;
        config_db $DBName STMTHEAP 40000;
        db2rbind $DBName all -l /dev/stdout;
        echo "NOTE!!!: If above failed increase statement cache: db2 update db config for $DBName using STMTHEAP 32000"
        db2 terminate; db2 connect to $DBName;
        db2 bind /home/db2inst/sqllib/bnd/db2schema.bnd blocking all grant public sqlerror continue;
        db2 bind /home/db2inst/sqllib/bnd/db2dsproc.bnd blocking all grant public;
        rebind_client;
        echo "NOTE!!!: Do following on each client with different db2level: /utils/db2/do.sh rebind_client"
        echo -e "\n SQL procedures and all external libraries must be rebuilt on the new platform"
        db2 terminate;
;;

( rebind_client )
        rebind_client;
;;

( upgrade_instance )
        db2stop force;
        $DB2Path/instance/dasupdt;
        $DB2Path/instance/db2iupdt db2inst;
        db2start;
;;

( config_kernel )
        echo "# ***** DB2 config: *****" >> /etc/sysctl.conf;
        echo "kernel.sem=250 256000 32 1024" >> /etc/sysctl.conf;
        echo "kernel.msgmax=65535" >> /etc/sysctl.conf;
        echo "kernel.msgmnb=65535" >> /etc/sysctl.conf;
        SystemMem=`cat /proc/meminfo | grep MemTotal:|awk '{print $2}'`;
        echo "kernel.shmall=$[SystemMem/4*9/10]" >> /etc/sysctl.conf;
        echo "kernel.shmmax=1073741824" >> /etc/sysctl.conf;
        sysctl -p;
        ipcs -l;
;;

( config_db_n1 )
        db2set DB2_PARALLEL_IO=4;
        db2set DB2_EVALUNCOMMITTED=ON;
        db2set DB2_SKIPINSERTED=ON;
        db2set DB2_SKIPDELETED=ON;

        DBName=$2
        config_db $DBName LOCKLIST 100000;
        SORTHEAP=20000; config_db $DBName SORTHEAP $SORTHEAP; config_dbm SHEAPTHRES $[4*$SORTHEAP];
        config_db $DBName SHEAPTHRES_SHR 1000;
        config_db $DBName MAXLOCKS 20;
        config_db $DBName LOGRETAIN NO;
        config_db $DBName AUTO_REORG OFF;
        MAXAGENTS=`get_dbm_param MAXAGENTS`; config_dbm MAXCAGENTS $[MAXAGENTS/2];
        # config_db $DBName LOGBUFSZ 128; was
        enable_monitoring;
        restart_db2;
;;

( config_db_n2 )
        #set -x;
        DBName=$2;

        db2set DB2_PARALLEL_IO="*:3";
        config_db $DBName LOGRETAIN NO;
        config_db $DBName LOGARCHMETH1 OFF;
        enable_monitoring;
        restart_db2;
;;

( delete_v8links )
        cat v8links.txt | /utils/for_each_line.sh rm;
;;

( config_dbm | config_db | restart_db2 | stop_db2 | restart_db2_verified | config_db_n2 )
        $Action $2 $3 $4;
;;

( backup_db )
    DBName=$2

        db2 connect to $DBName;
        db2 quiesce db immediate force connections;
        db2 connect reset;

        db2 backup db $DBName to "'"$3"'" DEDUP_DEVICE;

        db2 connect to $DBName;
        db2 unquiesce db;
        db2 connect reset;
;;

( grant_readonly )
#       set -x;

        DBName=$2;
        Schema=$3;
        Group=$4;

        db2 "connect to $DBName";
        for T in $(db2 -x "select tabname from syscat.tables where tabschema='"$Schema"'"); do
                echo "Table: $T";
                db2 "grant select on $Schema.$T to group $Group";
        done;

        for V in $(db2 -x "select viewname from syscat.views where viewschema='"$Schema"'"); do
                echo "View: $V";
                db2 "grant select on $Schema.$V to group $Group";
        done;

        echo "All functions";
        db2 "grant execute on function $Schema.* to group $Group";
        #db2 "revoke execute on function $Schema.* from group $Group restrict";

        db2 "disconnect $DBName";
;;

( show_memory )
        db2mtrk -i -d -v;
;;

( compress_backup )
        pbzip2 -v -p7 /mnt/data/backup/db/*.0.db2inst.NODE0000.CATN0000.*.001;
        cd /mnt/data/backup/db/; ls /mnt/data/backup/db/ | grep .001.bz2 | grep -v .001.bz2.gpg | /utils/for_each_line.sh /utils/pgp.sh;
        mv /mnt/data/backup/db/*.bz2.gpg /mnt/data/backup/db/archive/;
        rm -f /mnt/data/backup/db/*.bz2;
        sync;
;;

(*)
        echo Please specify an action like: install_rpms_64, remove_rpms, create_users, delete_users, create_instance, drop_instance
        echo Sample: do.sh create_instance 9.7
        exit 0
;;
esac;