Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    17

    Unanswered: Reorg and runstats using shell script

    Hi,

    I am trying to write a shell script to run Reorg and runstats in db2 v9.7

    Objective:
    # To automate online reorganisations using the following criteria
    # Automatically determine candidates for rorg
    # Provide a mechanism to exclude selected tables from the candidate list
    # Only perform one online-reorg at a time (ie serial queue)
    # Execute the reorganisations only within a defined maintenance window
    # Re-starting paused reorgs
    # Pausing reorgs at the end of the maintenance window
    # Parameters:
    # in:
    # db_name <DB2 alias name>
    # maint_end
    # Exclude_List "File which contains of exculded tables"
    # Mailing list ""
    # out:
    # Other inputs

    I have coded the script but the logic is not working out as per the requirement could any one help. Shell script is below

    #!/usr/bin/ksh
    #=================================
    # Objective:
    # To automate online reorganisations using the following criteria
    # Automatically determine candidates for rorg
    # Provide a mechanism to exclude selected tables from the candidate list
    # Only perform one online-reorg at a time (ie serial queue)
    # Execute the reorganisations only within a defined maintenance window
    # Re-starting paused reorgs
    # Pausing reorgs at the end of the maintenance window
    # Parameters:
    # in:
    # db_name <DB2 alias name>
    # maint_end
    # Exclude_List "File which contains of of exculded tables"
    # Mailing list " "
    # out:
    # Other inputs
    #--------------------------------------------------------------------------------
    # inputs
    script_name=$0
    db_name=$1

    tm=`date +%Y%m%d%H%M`
    maint_end_day=$2
    maint_end_hour=$3

    mailing_list=$4
    exclude_list=$5

    working_dir=/tmp/$script_name

    # Initialization

    if [ ! -d ${working_dir} ]; then
    mkdir -p ${working_dir}
    fi

    if [ -f $HOME/sqllib/db2profile ]; then
    . $HOME/sqllib/db2profile
    fi

    if [ ! -f $exclude_list ] ; then
    echo "No file name for excluded tables provided aborting" | mailx -s "${script_name} No file name for excluded tables provided aborting" $mailing_list
    exit 0
    fi

    # Main
    db2 connect to $db_name

    if [ ! -f $SQL_error ] ; then
    echo "Not able to connect to $db_name. SQL error=$SQL_error" | mailx -s "${script_name} No file name for excluded tables provided aborting" $mailing_list
    exit 0
    fi

    ##Gathering the tables, schema list which are required for reorg.
    db2 -x "CALL SYSPROC.REORGCHK_TB_STATS('T','ALL')" > ${working_dir}/reorgchk_${db_name}_$$.tmp

    db2 -x "SELECT rtrim(TABLE_SCHEMA)||'.'|| TABLE_NAME FROM SESSION.TB_STATS WHERE TABLE_SCHEMA not like 'SYS%' and TABLE_SCHEMA not like 'DB2%' and REORG LIKE '%*
    %' with ur" > ${working_dir}/Table_List_${db_name}_$$.tmp

    # Remove any tables in reorg_exclude.lst
    grep -v -f ${exclude_list} ${working_dir}/Table_List_${db_name}_$$.tmp > ${working_dir}/Table_List_${db_name}_$$.txt

    # Remove any tables in Reorg PAUSE state
    db2 "select rtrim(tabschema) as tabschema,tabname from SYSIBMADM.SNAPTAB_REORG where REORG_STATUS = 'PAUSED' with ur" > ${working_dir}/Table_List_${db_name}_$$.tmp
    grep -v -f ${exclude_list} ${working_dir}/Table_List_${db_name}_$$.tmp > ${working_dir}/Table_List_${db_name}_$$.txt

    while [ `date +%u` -le ${maint_end_day}] && [ `date +%H` -le ${maint_end_hour}]; then
    Running_reorg=`db2 "select count(*) from SYSIBMADM.SNAPTAB_REORG where REORG_STATUS = 'STARTED' with ur"`
    Paused_reorg=`db2 -x "select 'db2 count(*) from SYSIBMADM.SNAPTAB_REORG where REORG_STATUS = 'PAUSED' with ur"`
    if ${Running_reorg} gt 0
    sleep 120
    else if ${Paused_reorg} gt 0; then
    output=`db2 -x "select 'db2 reorg table ' || rtrim(tabschema) || '.' || rtrim(tabname) || ' inplace resume' from SYSIBMADM.SNAPTAB_REORG where REORG_STATUS = 'PAUSED' fetch first 1 rows only with ur"`
    then
    ${output}
    Else
    echo "Starting a new reorg"
    reorg table tabschema,tabname inplace
    fi
    if re_org running
    db2 "select rtrim(tabschema) as tabschema,tabname from SYSIBMADM.SNAPTAB_REORG where REORG_STATUS = 'Started' with ur"
    db2 "reorg table $TABLE inplace pause"
    wend
    If Re_org running
    db2 "select rtrim(tabschema) as tabschema,tabname from SYSIBMADM.SNAPTAB_REORG where REORG_STATUS = 'Started' with ur"
    db2 "reorg table $TABLE inplace pause"
    xif
    ################################### i dont know how to put if i am not wrong there should be only while loop ###########################
    while read Table
    do
    echo "Current Date: `date`"
    #Check if date/time has crossed Sunday 6am
    if [ `date +%u` -eq 7 ] && [ `date +%H` -eq 06 ]; then
    echo "Exiting reorg & runstats as the maintenance period has crossed i.e. Sunday 6am"
    echo "Exiting reorg as the maintenance period has crossed i.e. Sunday 6am" | mailx -s "Exiting reorg & runstats as the maintenance period has
    crossed i.e. Sunday 6am" $email_list
    exit 0
    elsep
    if [ `date +%u` -eq 6 ] && [ `date +%H` -eq 09 ]; then
    echo "Starting Reorg and runstats"
    db2 "reorg table $Table inplace allow read access"
    db2 "runstats on table $Table with distribution and indexes all"
    end
    esle
    echo "Cant run the script on `date +%a`"
    fi
    fi
    done < $OutputDir/Table_List_${db}.txt
    echo "Script completed" >> $OutputDir/Reorg_runstats_${db}_$tm.log
    db2 connect reset
    done

    It will be great if some one can help please

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is not working out as per the requirement ??
    what is not working as you coded ..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, keep in mind online reorgs are asynchronous. So, you say run reorg the db says ok done. Its not done. don't run a script where you aren't checking to see if the utility is done yet.
    Otherwise, I'm with Guy above. Tell us what issue you are having and maybe we can give some advice.
    Dave

Posting Permissions

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