Archives of the TeradataForum

Message Posted: Tue, 20 May 2008 @ 08:18:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Anomy,

Yes, we have a process to check for invalid views. I have embedded the text of 2 files in this mail.

Keep in mind that the shell script is possibly using other functions and shell scripts which you would need to create alternatives for. It would not be practical to share all of our code base with you. Our original intention was to automatically retry compiling invalid views, but we have decided not to for specific reasons.

     "viewlist_startpoint.sql":
     width 15000;
     SELECT TRIM(t_VWS.DATABASENAME)||';'||
             TRIM(t_VWS.TABLENAME)||';'/*||
             TRIM( TRAILING FROM t_VWS.RequestText)*/ "Record"
     FROM DBC.TABLES t_VWS,
          (select CHILD
           from DBC.CHILDREN
           where PARENT='PROD') t_DBS
     WHERE t_VWS.TABLEKIND = 'V'
     AND   t_VWS.DATABASENAME = t_DBS.CHILD
     /*AND   TRIM(t_VWS.TABLENAME) = 'M_CCP_CUST_V'
     */ORDER BY 1;



     "viewlist_chk_invalid.ksh" 110 lines, 4684 characters
     #!/usr/bin/ksh
     ###################################################################
     #
     ### ScriptName: viewlist_chk_invalid.ksh
     #
     ###
     #
     ### Created:  Etienne Stieger          2007-01-29
     #
     ### Updated:  Etienne Stieger          2007-01-29
     #
     ###
     #
     ###################################################################
     ### Description: Checks for invalid views. Sends a report.
     .  ${HOME}/.profile
     TOPIC='basename ${0}'': '
     v_HOST='uname -n'

     umask 133

     #TODAY='date "+%Y%m%d%H%M%S"'
     LOGFILE1="${PTH_DBA_SCRPT}viewlist_chk_invalid.log"
     VIEW_SQL_SRC="${PTH_DBA_SCRPT}viewlist_startpoint.sql"
     VIEWLIST1="${PTH_DBA_SCRPT}viewlist_chk_invalid.doc"
     PRBLMLST1="${PTH_DBA_SCRPT}viewlist_problem1.doc"
     #FIXEDLST1="${PTH_DBA_SCRPT}viewlist_fixed.doc"
     #FINALLST1="${PTH_DBA_SCRPT}viewlist_final1_smry.doc"
     FINALLST2="${PTH_DBA_SCRPT}viewlist_final2_full.doc"
     HDR_VIEWLIST1='###            Views checked            ###'
     HDR_PRBLMLST1='### Initial list of invalid views found ###'
     #HDR_FIXEDLST1='###  Invalid views successfully fixed   ###'
     #HDR_FINALLST1='###       Invalid views not fixed       ###'
     HDR_FINALLST2='### Invalid views not fixed - full log  ###'
     HDR_UNDRSCORE='###=====================================###'

     ### Empty the main log file
     echo "\c" > ${LOGFILE1}
     exec >> ${LOGFILE1} 2>&1

     ### Empty other files
     echo "\c" > ${VIEWLIST1}
     echo "\c" > ${PRBLMLST1}
     #echo "\c" > ${FIXEDLST1}
     #echo "\c" > ${FINALLST1}
     echo "\c" > ${FINALLST2}
     echo "${HDR_UNDRSCORE}\n${HDR_VIEWLIST1}\n${HDR_UNDRSCORE}" >> ${VIEWLIST1}
     echo "${HDR_UNDRSCORE}\n${HDR_PRBLMLST1}\n${HDR_UNDRSCORE}\n" >> ${PRBLMLST1}
     #echo "${HDR_UNDRSCORE}\n${HDR_FIXEDLST1}\n${HDR_UNDRSCORE}\n" >> ${FIXEDLST1}
     #echo "${HDR_UNDRSCORE}\n${HDR_FINALLST1}\n${HDR_UNDRSCORE}\n" >> ${FINALLST1}
     echo "${HDR_UNDRSCORE}\n${HDR_FINALLST2}\n${HDR_UNDRSCORE}\n" >> ${FINALLST2}

     echo 'Started ''date "+%Y%m%d%H%M%S"'

     SQLSTMT='cat ${VIEW_SQL_SRC}'

     ### Create list of views for checking
     ${PTH_DBA_SCRPT}execsql_td_nopwd.ksh '' 'dbabatch' '' "${SQLSTMT}" N Y N
     Y|sed "/^Record/d" >> ${VIEWLIST1}

     for v_LINE in 'cat ${VIEWLIST1}|sed "/^###/d"'
        do
          v_OWNR='echo "${v_LINE}"|awk -F";" '{print $1}''
          v_VIEW='echo "${v_LINE}"|awk -F";" '{print $2}''
          SQLSTMT="help view ${v_OWNR}.${v_VIEW};"
          MSG='2>&1 ${PTH_DBA_SCRPT}execsql_td_nopwd.ksh '' 'dbabatch' ''
     "${SQLSTMT}" N Y N Y'
          RC=${?}
          if [ ${RC} != 0 ]
            then
              echo "${v_OWNR}.${v_VIEW}" >> ${PRBLMLST1}
              echo "### Problem with View!!! RC=${RC}; ${v_OWNR}.${v_VIEW} ###" >> ${FINALLST2}
              echo "${MSG}\n#@#\n#@#\n#@#"|sed "/^.* Problem executing execbt.*$/d"|sed "/^$/d"|sed -e "s/#@#//g" >> ${FINALLST2}
              echo "###    Problem with View!!! RC=${RC}; ${v_OWNR}.${v_VIEW} ###"
     #        echo "${MSG}"
          else
              echo "### View OK!!! RC=${RC}; ${v_OWNR}.${v_VIEW} ###"
     #        echo "${MSG}"
          fi
     done

     echo 'Completed ''date "+%Y%m%d%H%M%S"'

     PRBLM_CNT='cat "${PRBLMLST1}"|sed "/^###/d"|sed "/^$/d"|wc -l|awk
     '{print $1}''
     echo "PRBLM_CNT=${PRBLM_CNT}"
     v_FROM='${PTH_GEN_SCRPT}get_dba_mail_addr.ksh'
     if [ "@${USER}" = '@' ]
        then
          v_USER_ENV='crontab/backround job'
     else
          v_USER_ENV="${USER}"
     fi
     echo "v_USER_ENV=${v_USER_ENV}"

     if [ ${PRBLM_CNT} != 0 ]
        then
          v_mailsbj="View validity report : ${PRBLM_CNT} view/s are invalid"
          echo "v_mailsbj=${v_mailsbj}"
          v_MSG='echo "Please check attachments for information on invalid
     views\nScript: ${TOPIC} (${v_HOST})\n\nSent by: ${LOGNAME}(${v_
     USER_ENV})"'
          echo "v_MSG=${v_MSG}"
          ${PTH_GEN_SCRPT}mailfromtable.ksh 'InvViews' '001' "${v_FROM}"
     "${v_mailsbj}" "${v_MSG}" "${PRBLMLST1} ${FINALLST2} ${VIEWLIST1}
     "
          MAILRC=${?}
          NEWRC=1
          echo "MAILRC=${MAILRC}; NEWRC=${NEWRC}"
          exit ${NEWRC}
     else
          v_mailsbj="View validity report : All views are valid"
          echo "v_mailsbj=${v_mailsbj}"
          v_MSG='echo "Attachment contain information on views
     checked\nScript: ${TOPIC} (${v_HOST})\n\nSent by:
     ${LOGNAME}(${v_USER_ENV})
     "'
          echo "v_MSG=${v_MSG}"
          ${PTH_GEN_SCRPT}mailfromtable.ksh 'InvViews' '001' "${v_FROM}"
     "${v_mailsbj}" "${v_MSG}" "${VIEWLIST1} ${PRBLMLST1} ${FINALLST2}
     "
          MAILRC=${?}
          NEWRC=0
          echo "MAILRC=${MAILRC}; NEWRC=${NEWRC}"
          exit ${NEWRC}
     fi

Regards

Etienne Stieger
Analytics and Information Management (AIM/EIW)
Standard Bank of South Africa Ltd

	<Prev	Next>		<<First	<Prev	Next>	Last>>