一個資料倉儲資料重新整理的實現機制（五）

shell refresh_table

用於呼叫一個sql檔案重新整理一張表，並紀錄重新整理資訊

[@more@]

用法：refresh_table SQL_FILE_NAME SCHEMA PASSWORD

$refresh_table eden_flat_ord_cmpt_fr.sql edenfr ******

該指令碼中注意以下語句：

TABLE_NAME=`echo ${1%_*}|cut -d"/" -f 4`

其中${1%_*}是bash裡面的模式匹配。shell refresh_table位於目錄/erdb/bin下，而sql檔案位於/erdb/sql目錄下，sql檔名的命名規則為TABLENAME_COUNTRY.sql， 其中COUNTRY為縮寫，FR代表法國，UK代表英國等。

透過修改函式rfsh_env()中的引數，可以方便連到不同的資料庫（比如開發庫和生產庫），也可以修改日誌檔案目錄等。

shell refresh_table的指令碼如下：

#####################################################################
#
# Purpose: This script is to refresh tables, and log refresh
#     information both in table and OS file
#
# Usage: refresh_table SQL_FILE_NAME SCHEMA SCHEMA_PASS
#
# Global varialbles
# FILE_NAME: the file needed by refresh
# USERNAME: database login ID
# PASSWORD: password for database user
# DATABASE: service name of database
# SCHEMA: the owner of objects to be refreshed
# SCHEMA_PASS: the password for the user SCHEMA
# TABLE_NAME: the table to be refreshed
# START_TIME: when refresh begins
# END_TIME: when refresh ends
# BEFORE_ROWS: rows before refresh
# AFTER_ROWS: rows after refresh
# REFRESH_SEQ: the refresh sequence number for the table
# LOG_PATH: where the log file locates
# LOG_FILE: the file to store log information
#     file name in format refresh_log.yyyy-mm-dd
# LOG_DETAIL: the file to log detail information
#     file name in format log_detail.SCHEMA.yyyy-mm-dd
# LOG_ROWS: a mid_stage parameter
#
#
#####################################################################

#!/usr/bin/bash

rfsh_env()
{
LOG_PATH=/erdb/log
LOG_FILE=refresh_log.`date "+%Y-%m-%d"`
LOG_DETAIL=log_detail.$SCHEMA.`date "+%Y-%m-%d"`
USERNAME=eden
PASSWORD=******
DATABASE=service_name
}

rfsh_refresh()
{
TABLE_NAME=`echo ${1%_*}|cut -d"/" -f 4`

LOG_ROWS=`sqlplus -s
 SET FEED OFF
 SET TERM OFF
 SET HEAD OFF
 SELECT count(*)
   FROM eden_refr_log
   WHERE user_nam='$SCHEMA'
   AND obj_nam='$TABLE_NAME';
EOF`

if [ $LOG_ROWS -eq 0 ]; then
  REFRESH_SEQ=1
else
  REFRESH_SEQ=`sqlplus -s
 SET FEED OFF
 SET TERM OFF
 SET HEAD OFF
 SELECT max(refr_seq_nb+1)
   FROM eden_refr_log
   WHERE user_nam='$SCHEMA'
   AND obj_nam='$TABLE_NAME';
EOF`
fi

BEFORE_ROWS=`sqlplus -s
        SET FEED OFF
        SET TERM OFF
        SET HEAD OFF
 select count(*) from $SCHEMA.$TABLE_NAME;
EOF`

START_TIME=`date "+%Y-%m-%d %H:%M:%S"`

echo "-----------------------------------------">>$LOG_PATH/$LOG_DETAIL
echo "start refreshing $TABLE_NAME ..." >>$LOG_PATH/$LOG_DETAIL
echo "-----------------------------------------">>$LOG_PATH/$LOG_DETAIL
sqlplus -s
 
  INSERT INTO $USERNAME.eden_refr_log(user_nam,obj_nam,obj_type,refr_seq_nb,strt_dat_tim,rows_bef_refr)
  VALUES('${SCHEMA}','${TABLE_NAME}','T',${REFRESH_SEQ},sysdate,$BEFORE_ROWS);
  COMMIT;
 
 
  spool /tmp/$SCHEMA_$TABLE_NAME.out
  set head off
  @$FILE_NAME
  select 'updating table eden_refr_log...' from dual;
  set head on
  spool off
 
  UPDATE $USERNAME.eden_refr_log
    SET end_dat_tim=sysdate
    WHERE user_nam='$SCHEMA'
    AND obj_nam='$TABLE_NAME'
    AND refr_seq_nb=$REFRESH_SEQ;
  COMMIT;

  EXIT;
EOF

END_TIME=`date "+%Y-%m-%d %H:%M:%S"`

ERRORMSG=`cat /tmp/$SCHEMA_$TABLE_NAME.out|grep ORA-|head -n 1`

ERR_COUNT=`cat /tmp/$SCHEMA_$TABLE_NAME.out|grep ORA-|wc -l`

if [ $ERR_COUNT -eq 0 ]; then
  REFRESH_STATUS='Y'
  RETURN_VAL=0
else
  REFRESH_STATUS='N'
  RETURN_VAL=1
fi

sqlplus -s
  UPDATE eden_refr_log
    SET sta_cod='$REFRESH_STATUS',
        tot_drtn_tim=( end_dat_tim - strt_dat_tim )*3600*24,
 err_txt='$ERRORMSG'
    WHERE user_nam='$SCHEMA'
    AND obj_nam='$TABLE_NAME'
    AND refr_seq_nb=$REFRESH_SEQ;
  COMMIT;

  EXIT;
EOF

AFTER_ROWS=`sqlplus -s
  SET FEED OFF
  SET TERM OFF
  SET HEAD OFF
  select count(*) from $SCHEMA.$TABLE_NAME;
EOF`

sqlplus -s
  UPDATE eden_refr_log
    SET   rows_aft_refr=$AFTER_ROWS
    WHERE user_nam='$SCHEMA'
    AND obj_nam='$TABLE_NAME'
    AND refr_seq_nb=$REFRESH_SEQ;
  COMMIT;

  EXIT;
EOF

echo "owner: "$SCHEMA>>$LOG_PATH/$LOG_FILE
echo "table name: "$TABLE_NAME>>$LOG_PATH/$LOG_FILE
echo "start time: "$START_TIME>>$LOG_PATH/$LOG_FILE
echo "end time: "$END_TIME>>$LOG_PATH/$LOG_FILE
echo "rows before refresh: "$BEFORE_ROWS>>$LOG_PATH/$LOG_FILE
echo "rows after refresh: "$AFTER_ROWS>>$LOG_PATH/$LOG_FILE
echo "refresh succeed(Y/N): "$REFRESH_STATUS>>$LOG_PATH/$LOG_FILE
echo "error messages if any: "$ERRORMSG>>$LOG_PATH/$LOG_FILE
echo " ">>$LOG_PATH/$LOG_FILE
echo " ">>$LOG_PATH/$LOG_FILE
}

#################################
## Main
#################################

if [ $# -lt 3 ]; then
  echo " "
  echo "Usage: refresh_table SQL_FILE_NAME SCHEMA SCHEMA_PASS"
  echo " "
  exit 1
fi

FILE_NAME=`echo $1|tr "[A-Z]" "[a-z]"`
FILE_NAME=/erdb/sql/$FILE_NAME
SCHEMA=`echo $2|tr "[A-Z]" "[a-z]"`
SCHEMA_PASS=`echo $3|tr "[A-Z]" "[a-z]"`

if [ ! -e $FILE_NAME ]; then
  echo " "
  echo "File $FILE_NAME does NOT exist."
  echo "Please specify an existed SQL file."
  echo " "
  echo " ">>$LOG_PATH/$LOG_FILE
  echo "----------------------------------">>$LOG_PATH/$LOG_FILE
  echo "  REFRESHING `echo ${1%_*}|cut -d"/" -f 3` ...">>$LOG_PATH/$LOG_FILE
  echo "----------------------------------">>$LOG_PATH/$LOG_FILE
  echo "File $FILE_NAME does NOT exist! ">>$LOG_PATH/$LOG_FILE
  exit 1
fi

rfsh_env

rfsh_refresh $FILE_NAME $SCHEMA $SCHEMA_PASS

if [ $RETURN_VAL -eq 1 ]; then
  echo ""
  echo "Errors during refresh table $TABLE_NAME."
  echo ""
fi

exit $RETURN_VAL