#!/bin/ksh #--------------------------------------------------------------------------- # Name: copy_tables.ksh # Purpose: Used to copy data from several tables from ODL # into the corresponding tables of MAF. # Checks for success of all operations. # Original Author: James Thuet 09/29/98 # Hugely Revised: Dan Martin (DRM/CTG) Fri Dec 15 09:12:48 CST 2000 # Calls: Nothing except for trusty awk (now does it ALL!) # Incorporates all operations of three now-obsolete scripts: # copy_ODL_tables.ksh monitor_ODL.ksh & reload_ODL_tables.ksh # # Alerts: The file "copy_tables.txt" contains, for reference, # the awk-written statments that are written on the fly # by this script and then piped into sqlplus. # # Maintenance: PROVIDED that no Table Names or layouts (including # Column Names) change, steps that are necessary are: # (1) Ensure that the List of Tables that gets piped into # awk (about line 114) is correct and complete. # If a new Table is designated to be copied from ODL # to MAF, then its name must be included in this List. # BE SURE TABLE NPA_NXX_LR IS ALWAYS LISTED LAST !! # (2) The column-list from a new ODL-to-MAF Table must be # correctly added to the for (i = 1; i <= nox; i++) # awk-loop that begins somewhere near line 200. # # IF any new or changed Columns need to be included # in the ODL-to-MAF COPY FROM sections, then you will # have to delve into the awk printf() statements and # make whatever corrections/additions are necessary. # # The file README_ct.txt has additional discussion on # Table naming and structure required by this script. # # Change History # Date By Description # --/--/-- ---------- ------------------------------ # 01/08/01 Dan Martin Changed names of TRUNC_TEMP_* and TEMP_* objects # to TRUNC_*_WORK and *_WORK objects respectively. # 12/21/00 Dan Martin Added code for MAF 5.0 required to COPY six new # tables from ODL to MAF: SID, ROUTE, SPECIAL_NUMBERS, # to USAGE_MARKET_POLICY, MARKET_POLICY, INCOL_SID_PAIR. # Also a new mapping of ODL NPA_NXX_LR # into MAF ACCESS_LINE_NBR_RANGE Table. # 12/11/00 Dan Martin Incorporated all operations of three now-obsolete # scripts copy_ODL_tables.ksh, monitor_ODL.ksh, # and reload_ODL_tables.ksh plus added enhanced # ODL-to-MAF error-checking on COPY FROM operations. #--------------------------------------------------------------------------- # Make sure All Is Well: all='well' [[ -z "$TLG_UP_LOG" ]] && export TLG_UP_LOG="${HOME}/log" log_cf=${TLG_UP_LOG}/ODL2MAF_`date +%Y%m%d"_T"%H%M%S`.log # Working Log File >$log_cf chmod 666 $log_cf cat <>$log_cf SCRIPT RUNNING IS: $0 BEGIN RUN AS OF: `date` LAUNCHED BY USER: $LOGNAME (real: `logname`) FROM SERVER: `uname -n` LOG FILE: $log_cf WORKING DIRECTORY: `pwd` PAW if [[ -z "$REMOTE_CONNECT" ]] then echo "Variable REMOTE_CONNECT is not set!" | tee -a ${log_cf} all='sick' fi if [[ -z $REMOTE_USER ]] then echo "Variable REMOTE_USER is not set!" | tee -a $log_cf all='sick' fi if [[ -z $MAF_ORA_USER ]] then echo "Variable MAF_ORA_USER is not set!" | tee -a ${log_cf} all='sick' fi [[ "$all" = "sick" ]] && exit 12 of1="/tmp/ct1$$.txt" # scratch file of2="/tmp/ct2$$.txt" # scratch file of3="/tmp/ct3$$.txt" # scratch file of4="/tmp/ct4$$.txt" # scratch file of5="/tmp/ct5$$.txt" # final results ## ## VARIABLE-SETUP BELOW FOR TESTING ONLY (change as needed)!! ## # REMOTE_CONNECT="yyy/zzz02@xxx_prod" # REMOTE_USER="xxx" # MAF_ORA_USER="yyyuser1/zzzp" ## VARIABLE-SETUP ABOVE FOR TESTING ONLY !! ## ## ################################################################# ## ALERT: THE LIST OF ODL-TO-MAF TABLES IN THE "CAT <SURE< TABLE NPA_NXX_LR IS ALWAYS LISTED LAST !! ## ################################################################# cat <&1 >>$log_cf # Append the four SPOOL files into one: cat $of1 $of2 $of3 $of4 >$of5 # Four Example Records in $of5: # Code Explanation (awk $1): # ODLB4 SWITCH 43567 # ODLB4 = Row-Counts of ODL Tables # MAFB4 SWITCH 32415 # MAFB4 = MAF row-counts before TRUNC # MAFAO ACCESS_LINE_NBR_RANGE 52415 # MAFAO = MAF row-counts after ODL COPY # MAFAX ACCESS_LINE_NBR_RANGE 52703 # MAFAX = MAF ALNR after NPA_NXX_LR load # Log any ODL-to-MAF problems: awk ' {if ($3 == 0) printf("WARNING Code %s: %21s TABLE in %s WAS EMPTY!\n", \ $1, $2, substr($1,1,3)) if ($1 == "ODLB4") ol4[$2] = $3 # Row-Counts in ODL Tables if ($1 == "MAFB4") mf4[$2] = $3 # Row-Counts in MAF Tables Pre-Truncation if ($1 == "MAFAO") mfo[$2] = $3 # Row-Counts in MAF Tables after ODL copy if ($1 == "MAFAX") mfx[$2] = $3 # Row-Count in MAF ALNR after NPA_NXX_LR load } END \ {nxx = (mfx[ACCESS_LINE_NBR_RANGE] - mfo[ACCESS_LINE_NBR_RANGE]) printf("%d rows copied from ODL NPA_NXX_LR to MAF ACCESS_LINE_NBR_RANGE\n", \ nxx) if (nxx != ol4[NPA_NXX_LR]) printf("NPA_NXX_LR LOAD ERROR: %4d ODL rows, %4d rows added to ALNR\n", \ ol4[NPA_NXX_LR], nxx) for (tb in mfo) if (mfo[tb] != ol4[tb]) printf("%21s LOAD ERROR: ODL = %6d rows, MAF = %6d rows\n", \ tb, ol4[tb], mfo[tb]) }' $of5 >>$log_cf # # Re-Load, from their *_WORK Table Counterparts, # any and all MAF Tables which have # row counts not equal to their ODL counterparts. awk ' {if ($1 == "ODLB4") ol4[$2] = $3 # Row-Counts in ODL Tables if ($1 == "MAFAO") mfo[$2] = $3 # Row-Counts in MAF Tables after ODL load } END \ {for (tb in mfo) if (mfo[tb] != ol4[tb]) {printf("PROMPT TRUNCATING TABLE %s\n", tb) printf("EXECUTE TRUNC_%s\n", tb) printf("PROMPT RELOADING TABLE %s FROM %s_WORK\n", tb, tb) printf("INSERT INTO %s\n", tb) printf("SELECT * FROM %s_WORK ;\n", tb) } printf("exit ; \n") }' $of5 | sqlplus -s $MAF_ORA_USER 2>&1 >>$log_cf >$of1 # Recycle this scratch file for possible page-out content awk -v lf="$log_cf" ' {if ($1 == "ODLB4") ol4[$2] = $3 # Row-Counts in ODL Tables if ($1 == "MAFAO") mfo[$2] = $3 # Row-Counts in MAF Tables after ODL load } END \ {pbm = 0 # problem counter for (tb in mfo) if (mfo[tb] != ol4[tb]) ++pbm if (pbm > 0) printf("%d ODL copies to MAF failed. Check %s"\n", pbm, lf) }' $of5 >$of1 if [[ -s "$of1" ]] then yak=`cat $of1` # pgty="4084" # Production Page pgty="4144" # Testing Page [[ -z "$pgty" ]] || /home/subehs/bin/subehs_submit.ksh $pgty "${yak}" echo "\nALERT: Page-type $pgty sent as: \"$yak\"\n" >>$log_cf fi rm -f $of1 $of2 $of3 $of4 $of5 # Cleanup cat <>$log_cf END RUN OF SCRIPT: $0 COMPLETED AS OF: `date` ON SERVER: `uname -n` LOG FILE: $log_cf PAW if [[ ! -z "$LOG_FILE_NAME" ]] then echo "\nODL-to-MAF Copy log: \"$log_cf\".\n" | tee -a $LOG_FILE_NAME fi exit 0 # EOF copy_tables.ksh