Archives of the TeradataForum

Message Posted: Thu, 20 Feb 2003 @ 13:34:17 GMT

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

Here is something I worte. We had problems with an older version of ODBC maintaining linefeeds when DDL was processed. It isn't perfect. BTEQ plays with linefeeds sometimes, views are in alphabetical order (so if a view calls a view you may have to move view definitionsf around) andI have not incorporated Stored Procedures, but it works pretty well. I use it to maintain changes in databases, extract data etc.

You do have to build one query (in a file) like:

insert into dba.change_list
(databasename, tablename,tablekind)
select distinct tb1.databasename, tb1.tablename, tb1.tablekind
from dbc.tables tb1
WHERE
TRIM(Tb1.DATABASENAME) =  'DUSE100T'
;

This gives you a lot of flexibility, since you can add in DBC.COLUMNS, etc.

#------------------------------------------------------------------
#
# Module: get_table_changes
# File:
# Author: Glen K. Blood
# Desc.:  Given an input query it builds the DDL from the database to
#         process a set of changes and puts them into the changefile.
#         Usage: get_table_changes logon_file tablelist changefile cleanupfile.
#
# History:
#        09/06/2000  Created
#        11/14/2000  Changed to allow stats collection on unnamed
#                    indices and non-index columns.
#        12/8/2000   Added capability to retain table level rights.
#------------------------------------------------------------------
#

#**********************************************************************
# Make sure that all of the command line parameters are there
#**********************************************************************

if [ $# != 4 ]; then
   echo usage: get_table_changes lgon_file tablelist changefile
cleanupfile
   exit 1
fi

#**********************************************************************
# set up the parameters.
#**********************************************************************

logon_file=${1}
tablelist=${2}
changefile=${3}
cleanupfile=${4}

rm ${HOME}/showtables.sql
rm ${changefile}
rm ${cleanupfile}
bteq << EOT
.set retry off;
.titledashes off;
.run file=${logon_file};
.IF ERRORCODE <> 0 THEN EXIT;
DATABASE DBA;
DELETE DBA.CHANGE_LIST;
.run file=${tablelist};
.IF ERRORCODE <> 0 THEN EXIT;
.IF ACTIVITYCOUNT < 1 THEN EXIT;
.export report file = '${HOME}/showtables.sql', OPEN;
execute DBA.BUILD_DDL;
.export reset

.export report file = '${changefile}', OPEN;
.set width 254;
EXECUTE DBA.PREPARE_DATABASE;
.run file='${HOME}/showtables.sql';
.IF ERRORCODE <> 0 THEN EXIT;
.IF ACTIVITYCOUNT < 1 THEN EXIT;
EXECUTE DBA.PRESERVE_DATA;
EXECUTE DBA.BUILD_DB_ADMIN_STUFF;

REPLACE MACRO PREPARE_DATABASE
AS
(
SELECT
'DROP JOIN INDEX ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) ||
';'
(TITLE ' ')
FROM DBA.CHANGE_LIST
WHERE TABLEKIND = 'I'
ORDER BY 1;
SELECT
'RENAME TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ' AS '
|| TRIM(DATABASENAME) || '.' || TRIM(TABLENAME)|| '_GKB;' (TITLE ' ')
FROM DBA.CHANGE_LIST
WHERE TABLEKIND = 'T'
ORDER BY 1;
);

REPLACE MACRO PRESERVE_DATA
AS
(
SELECT DISTINCT 'INSERT INTO ' || TRIM(DATABASENAME) || '.' ||
TRIM(TABLENAME) ||
' SELECT * FROM ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || '
_GKB;'
(TITLE ' ')
FROM DBA.CHANGE_LIST
WHERE
TABLEKIND = 'T'
ORDER BY 1;
);

REPLACE MACRO BUILD_DB_ADMIN_STUFF
 AS
 (
 SELECT ' ' (TITLE ' ');
 SELECT
 COALESCE('COMMENT ON ' ||  TRIM(T.DATABASENAME) || '.' ||
TRIM(T.TABLENAME) || ' AS ''' || COMMENTSTRING || ''';','')
 (TITLE '')
 FROM DBC.TABLES T,
 DBA.CHANGE_LIST CL
 WHERE T.DATABASENAME = CL.DATABASENAME
 AND   T.TABLENAME = CL.TABLENAME
 AND CL.TABLEKIND = T.TABLEKIND
 AND COMMENTSTRING IS NOT NULL
 AND COMMENTSTRING NOT = ' '
 ORDER BY 1;
 SELECT
 'REVOKE ALL ON '|| TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || '
FROM
' || USER ||';'
 (TITLE '')
 FROM DBA.CHANGE_LIST
 ORDER BY 1;
 SELECT
 'GRANT ' ||
 (CASE ACCESSRIGHT
 WHEN 'R' THEN
 '  SELECT  '
 WHEN 'IX' THEN
 '  INDEX  '
 WHEN 'U' THEN
 '  UPDATE '
 WHEN 'I' THEN
 '  INSERT  '
 WHEN 'D' THEN
 '  DELETE '
 WHEN 'E' THEN
 '  EXECUTE '
 ELSE  ACCESSRIGHT
 END ) ||
 ' ON ' || TRIM(DATABASENAME) ||
 (CASE TABLENAME
 WHEN 'ALL' THEN ' '
 ELSE '.' || TRIM(TABLENAME)
 END
 )
 ||
 (
 CASE COLUMNNAME
 WHEN 'ALL' THEN ' '
 ELSE '.' || TRIM(COLUMNNAME)
 END
 )
 || ' TO ' ||
 (
 CASE ALLNESSFLAG
 WHEN 'Y'  THEN 'ALL '
 ELSE ' '
 END
 )
 || TRIM(USERNAME) ||
 (
 CASE GRANTAUTHORITY
 WHEN 'Y' THEN ' WITH GRANT OPTION '
 ELSE ' '
 END
 )
 || ';'
 (TITLE ' ')
 FROM DBC.ALLRIGHTS
 WHERE
 (DATABASENAME, TABLENAME)
 IN
 (SELECT DATABASENAME, TABLENAME
 FROM DBA.CHANGE_LIST
 )
 AND USERNAME NOT = USER
 ORDER BY 1;
 SELECT 'COLLECT STATISTICS ON ' || TRIM(DBC.DBASE.DATABASENAME) || '.'
||
TRIM(TVMNAME) || ' COLUMN ' || TRIM(FIELDNAME) || ';'
 (TITLE ' ')
 FROM DBC.TVM,
 DBC.DBASE,
 DBC.TVFIELDS,
 DBA.CHANGE_LIST
 WHERE
 DBC.DBASE.DATABASEID = DBC.TVM.DATABASEID
 AND DBC.DBASE.DATABASENAME = DBA.CHANGE_LIST.DATABASENAME
 AND
 DBC.TVM.TABLEKIND = 'T'
 AND
 DBC.TVM.TVMNAME = DBA.CHANGE_LIST.TABLENAME
 AND
 TVMID = TABLEID
 AND FIELDSTATISTICS IS NOT NULL
 UNION
 SELECT 'COLLECT STATISTICS ON ' || TRIM(DBC.DBASE.DATABASENAME) || '.'
||
TRIM(TVMNAME) || ' INDEX ' || TRIM(NAME) || ';'
 FROM DBC.TVM,
 DBC.DBASE,
 DBC.INDEXES,
 DBA.CHANGE_LIST
 WHERE
 DBC.DBASE.DATABASEID = DBC.TVM.DATABASEID
 AND DBC.DBASE.DATABASENAME = DBA.CHANGE_LIST.DATABASENAME
 AND
 DBC.TVM.TABLEKIND = 'T'
 AND
 DBC.TVM.TVMNAME = DBA.CHANGE_LIST.TABLENAME
 AND
 TVMID = TABLEID
 AND INDEXSTATISTICS IS NOT NULL
 AND NAME IS NOT NULL
 ORDER BY 1;

 );

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