AIX KSH的一个程序

上一篇 / 下一篇  2010-09-08 14:40:39 / 个人分类：AIX

查看( 618 ) / 评论( 0 ) / 评分( 0 / 0 )
这几天做了个liquid项目，编写shell脚本，属于记录比较的，从文件解析数据然后导入临时表与另一个表的数据做比较。不管这个程序能不能达到要求，自己还是学了些技术，在此把脚本贴上留作以后参考。

# AUTHOR:
# EMAIL:
# DATE:2010-09
# PURPOSE:
# PARAMTER: $1          1 WORK_PATH
#            2 DB_NAME
#            3 USER
#            4 PASSWORD
#            5 LOG_FILE
#            6 ARTUU_BS
#            7 TP_TB_NAME
#            8 TB_SP
#            9 CRM_TB_NAME
#             10 REPORT_NM


paraWorkPath=$1
paraDBname=$2
paraDbuser=$3
paraDbpass=$4
paraLogFile=$5
paraArtuufile=$6
paraTemptable=$7
paraTablespacetemp=$8
paraCrmTable=$9
paraReportname=$10


Create_TP_TB()
{
    db2 connect to $paraDBname user $paraDbuser using $paraDbpass
   
    #Create temporary table.
    db2 "create table $paraTemptable like $paraCrmTable in $paraTablespacetemp"
    if [ $? != 0 ]
    then
        echo "Temporary table creation failed." | tee -a $paraReportname; db2 connect reset ; exit -30
    fi
   
    db2 connect reset
}


Load_TXT()
{
    #Verify the artuu file can be red.
    if [[ -r $paraWorkPath/$paraArtuufile ]] || echo "Artuu file can not be access." | tee -a $paraReportname; exit -40
   
    #suppose we get db structure description doc to table.txt.
    #delete blank rows, rows begin with "----", rows contains "record(s) selected" and then select data type column.
    sed -e 1,'/^-----/d' table.txt | sed '/^[[:space:]]*$/d' | sed -e '/record(s) selected/d' | awk '{ print $3 }' > table_des.tmp
   
    #get file absolute directory name.
    strRecordsTxt=$paraWorkPath/$paraArtuufile       
   
    #Extract ARTUU txt file to temporary file.
    cat $strRecordsTxt | sed -n -e '/Output Record is:/p' | awk -F "Output Record is: " '{print $2}' > records.tmp
   
    nArtuRows=$(cat records.tmp | wc -l)    #get row count of Artuu records.
    myvar=1
    strOutputRecord=""
    strOneRecord=""
    while [ $myvar -ne $nArtuRows ]    #get all records iterately.
    do
        strOutputRecord=""
       
        #Select one row to process----------------------------------begin
        strOneRecord=$(sed -n ''$myvar'p' records.tmp | cat)
        exec < table_des.tmp    #load table description column information.
        coun=1
        while read linetemp
        do
            strColumnValue=$(echo $strOneRecord | awk -F "~" '{print $('$coun')}')
            if [[ $linetemp = "GRAPHIC" || $linetemp = "VARGRAPHIC" || $linetemp = "TIMESTAMP" || $linetemp = "CHAR" || $linetemp = "VARCHAR" ]]
            then
                strColumnValue=$(echo \"$strColumnValue\"\,)        #Value with quotation mark and comma.
            else
                strColumnValue=$(echo $strColumnValue\,) #Value follow comma.
            fi
            strOutputRecord=$strOutputRecord$strColumnValue
            ((coun=coun+1))
        done
        echo $strOutputRecord >> output.tmp
        myvar=$(($myvar+1))
    done
   
    sed 's/.$//' output.tmp > importRecords.del    #delete the last comma char.
    rm -f output.tmp
   
    db2 connect to $paraDBname user $paraDbuser using $paraDbpass
   
                    #db2 set schema (if need)
    db2 "import from importRecords.del of del insert into $paraTemptable"
    db2 connect reset
}


Drop_TP_TB()
{
    db2 connect to $paraDBname user $paraDbuser using $paraDbpass
    db2 "drop table $paraTemptable"
}


Create_report()
{
    echo "COM_BDS_LOV.SH running on "`Date` | tee -a $paraReportname
    db2 connect to $paraDBname user $paraDbuser using $paraDbpass
   
    echo "LOVs that exist on Business Data Standards website but not in CRM:" | tee -a $paraReportname
    db2 "select * from $paraTablespacetemp except select * from $paraCrmTable" | tee -a $paraReportname
   
    echo "" | tee -a $paraReportname
    echo "LOVs that exist in CRM but not on Business Data Standards website:" | tee -a $paraReportname
    db2 "select * from $paraCrmTable except select * from $paraTablespacetemp" | tee -a $paraReportname
   
    #Get primary keys.
    db2 "SELECT COLNAME FROM SYSCAT.COLUMNS WHERE KEYSEQ IS NOT NULL AND TABNAME='$paraCrmTable'" > keys.tmp
    cat keys.tmp | sed -e 1,'/^-----/d' table.txt | sed '/^[[:space:]]*$/d' | sed -e '/record(s) selected/d' > keys
    rm -f keys.tmp
    #Get other columns.
    db2 "SELECT COLNAME FROM SYSCAT.COLUMNS WHERE KEYSEQ IS NULL AND TABNAME='$paraCrmTable'" > otherCol.tmp
    cat otherCol.tmp | sed -e 1,'/^-----/d' table.txt | sed '/^[[:space:]]*$/d' | sed -e '/record(s) selected/d' > otherCol
   
    strExecuteSql="SELECT a.*, b.* FROM $paraCrmTable a, $paraTemptable b WHERE "
    strKeysSta=""
    strOtherColSta=""
    cou=1
    exec < keys
    #Begin to process primary columns
    while read linetemp
    do
        if [[ "$cou" -eq 1 ]]
        then
            strKeysSta="a.$linetemp = b.$linetemp "
        else
            strKeysSta="and a.$linetemp = b.$linetemp "
        fi
        ((cou=cou+1))
    done
   
    cou=1
    exec < otherCol
    #Begin to process other columns.
    while read linetemp
    do
        if [[ "$cou" -eq 1 ]]
        then
            strOtherColSta="and (a.$linetemp != b.$linetemp "
        else
            strOtherColSta="or a.$linetemp != b.$linetemp "
        fi
        ((cou=cou+1))
    done
   
    #Add the ")" in the end.
    strOtherColSta=$strOtherColSta" )"
   
    #Generate the sql statement to run.
    strExecuteSql=$strExecuteSql$strKeysSta$strOtherColSta
   
    echo "Differences between LOVs and CRM:" | tee -a $paraReportname
    db2 $strExecuteSql | tee -a $paraReportname
   
    db2 connect reset
}

Check_Parameter()
{
    #begin to check parameters.
    if (($# != 10))
        then
              echo "Usage: $0 "
              echo "(work path) (db name) (db user name) (db user password) (log file name) (artuu file name) (Temporary table name) (Tablespace to store the temporary table) (table name in CRM) (report file name)"
          exit 1
    fi
   
    if [[ -d $paraWorkPath ]] || echo "$paraWorkPath is not a directory." ; exit 2
   
    if [[ -r $paraWorkPath ]] || echo "Path $paraWorkPath can not be red." | tee -a $paraReportname ; exit -10
   
    db2 connect to $paraDBname user $paraDbuser using $paraDbpass
    if [ $? != 0 ]
    then
        echo "DB connection failed." | tee -a $paraReportname
        exit -20
    fi
}


Check_Parameter
Create_TP_TB
Load_TXT
Create_report
Drop_TP_TB





收藏 举报

TAG:

 

评分：0

我来说两句

Open Toolbar