Archives of the TeradataForum

Message Posted: Mon, 28 Feb 2005 @ 15:19:49 GMT

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

The following is an example of the script I am running (from our HP-UX) server to backup views. Obviously I have something similar for macros and procedures.

It may not be the best, but it gets the job done for us.

Craig

     ------------------------------------------------------

     #!/bin/ksh

     #  11/03/04     crferry      Created script to backup teradata views.
     #
     ############################################################
     ############################################################
     #
     #  N        N      OOO       TTTTTTTTTTT     EEEEEEEEEEE
     #  NN       N     O   O           T          E
     #  N N      N    O     O          T          E
     #  N  N     N   O       O         T          E
     #  N   N    N   O       O         T          EEEEEE
     #  N    N   N   O       O         T          E
     #  N     N  N   O       O         T          E
     #  N      N N    O     O          T          E
     #  N       NN     O   O           T          E
     #  N        N      OOO            T          EEEEEEEEEEE
     #
     #
     #  The backup ddl is stored in the BAK files.
     #  The best method to view the ddl is via UltraEdit (or another windows
     editor).
     #  Unix vi does not allow you to view all ddl.
     #
     ############################################################
     ############################################################

     . /users/dss/.env_logon_dssdba
     . /users/dss/bin/email.rc
     . /users/dss/bin/time_calc

     echo "EXECUTING $0"

     DATABASE=teradata

     rm /dba/dss/dbloads/td_backups/view_backup.$DATABASE.ddl


     # Code to create the ddl for view backup.

     bteq << EOF
     .logon $DATABASE/${TTID},${TTPSWD};

     .EXPORT INDICDATA
     FILE=/dba/dss/dbloads/td_backups/view_backup.$DATABASE.ddl
     .set INDICDATA OFF
     .SET FORMAT OFF
     .set HEADING ' '
     .set RTITLE ' '
     .SET WIDTH 254

     Sel   'Show View ' || trim(databasename)||'.'||trim(tablename)||';'  As
     XQL
     from dbc.tables where tablekind='V';

     .EXPORT RESET

     .if errorcode <> 0 then .exit errorcode

     .LOGOFF;

     EOF

     ERROR=$?
     if [ $ERROR -ne 0 ]
     then
      echo "ERROR is " $ERROR
      email_status TD_VIEW_EXPORT
      exit $ERROR
     fi

     # Code to create the view ddl file.

     rm /dba/dss/dbloads/td_backups/view_ddl.$DATABASE.BAK

     bteq << EOF
     .logon $DATABASE/${TTID},${TTPSWD};

     .EXPORT INDICDATA
     FILE=/dba/dss/dbloads/td_backups/view_ddl.$DATABASE.BAK
     .set INDICDATA OFF
     .SET FORMAT OFF
     .set HEADING ' '
     .set RTITLE ' '
     .SET WIDTH 254

     .RUN FILE=/dba/dss/dbloads/td_backups/view_backup.$DATABASE.ddl

     .EXPORT RESET

     .if errorcode <> 0 then .exit errorcode

     .LOGOFF;

     EOF

     ERROR=$?
     if [ $ERROR -ne 0 ]
     then
      echo "ERROR is " $ERROR
      email_status TD_VIEW_EXPORT
      exit $ERROR
     fi

     DATABASE=teradev

     rm /dba/dss/dbloads/td_backups/view_backup.$DATABASE.ddl


     # Code to create the ddl for view backup.

     bteq << EOF
     .logon $DATABASE/${TTID},${TTPSWD};

     .EXPORT INDICDATA
     FILE=/dba/dss/dbloads/td_backups/view_backup.$DATABASE.ddl
     .set INDICDATA OFF
     .SET FORMAT OFF
     .set HEADING ' '
     .set RTITLE ' '
     .SET WIDTH 254

     Sel   'Show View ' || trim(databasename)||'.'||trim(tablename)||';'  As
     XQL
     from dbc.tables where tablekind='V';

     .if errorcode <> 0 then .exit errorcode

     .LOGOFF;

     EOF

     ERROR=$?
     if [ $ERROR -ne 0 ]
     then
      echo "ERROR is " $ERROR
      email_status TD_VIEW_EXPORT
      exit $ERROR
     fi

     # Code to create the view ddl file.

     rm /dba/dss/dbloads/td_backups/view_ddl.$DATABASE.BAK

     bteq << EOF
     .logon $DATABASE/${TTID},${TTPSWD};

     .EXPORT INDICDATA
     FILE=/dba/dss/dbloads/td_backups/view_ddl.$DATABASE.BAK
     .set INDICDATA OFF
     .SET FORMAT OFF
     .set HEADING ' '
     .set RTITLE ' '
     .SET WIDTH 254

     .RUN FILE=/dba/dss/dbloads/td_backups/view_backup.$DATABASE.ddl

     .if errorcode <> 0 then .exit errorcode

     .LOGOFF;

     EOF

     ERROR=$?
     if [ $ERROR -ne 0 ]
     then
      echo "ERROR is " $ERROR
      email_status TD_VIEW_EXPORT
      exit $ERROR
     fi

     email_success_status TD_VIEW_BACKUP_PROD_ONLY

     echo $0 completed $ERROR

     exit 0

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