Teradata Post Upgrade/Expansion Checks/Tasks

·         If not performed by the CSR during the outage window, run checktable DBC only at level 3.  This should only take a few minutes.

·         If not performed by the CSR during the outage window, run scandisk and checktable to verify the rest of the file system is clean.

·         Backup DBC and any other critical databases.  If any new system databases were created as part of the upgrade, remember to grant the appropriate dump/restore privileges on them and add them to your BAR scripts.

·         Run collect stats on application tables if recommended for this upgrade version.

·         Check/reset TDWM, DBScontrol, XCTL and PSF settings.

·         Restart DBQL, Access logging, System Security, Priority scheduler, etc. as before.

·         Restart Viewpoint monitoring by enabling the system on the Viewpoint servers and/or restart the Teradata Manager server

·         Recreate Hash, Join Indexes, Triggers and UDFs that were dropped prior to the upgrade.

·         Recreate journal tables (if any).

·         Make sure the default date format is set correctly.  This often gets changed.

·         The upgrade scripts would have identified and recompiled any Teradata Stored Procedures (TDSP). 

·         Recompile and test any user stored procedures. The upgrade will not have recompiled any stored

procedures whose code is stored outside the system

·         Restore DDL for system databases that you may have modified (i.e. Sys_Calendar.CALENDAR).

·         Validate statistics are collected as should be on DBC and user tables.  Recollect if necessary.

·         If you have to restore a PPI table you may need to revalidate the Primary Index afterwards : 'Alter table x revalidate primary index'. This command normally runs quickly.

·         May need to adjust user spool settings if number of amps changed.

·         DBA regression testing.  Run test program to validate all settings, connectivity, programs, DBC tables, procedures, etc.

·         Test applications such as SQL Assistant, BTEQ, BTEQWin, OLELoad, Teradata Administrator, Visual Edge, TASM, AQM, Teradata Manager, backup, restore etc.

·         Start/confirm TDP's and enable logons.

·         Restart job schedulers such as AutoSys, cron, etc.

·         Restart open systems backup application.

·         Notify customers of system availability.

·         Sign off on user acceptance of upgrade with Teradata.

·         Close Change Controls – Teradata & Local.

Teradata Access Rights Abbreviations

/********  Manage Access Rights ***************/

/******** Assume you have DBA work database called scriptdb ************/

-- First step creat Access Right Abbreviation table on your database

CREATE SET TABLE scriptdb.AccessRightsAbbv( Description VARCHAR(50), AccessRight CHAR(2))PRIMARY INDEX(AccessRight) ;


-- Second step insert the abbreviations

ins scriptdb.AccessRightsAbbv('CHECKPOINT','CP');
ins scriptdb.AccessRightsAbbv('CREATE AUTHORIZATION','CA');
ins scriptdb.AccessRightsAbbv('CREATE MACRO','CM');
ins scriptdb.AccessRightsAbbv('CREATE PROCEDURE','PC');
ins scriptdb.AccessRightsAbbv('CREATE TABLE','CT');
ins scriptdb.AccessRightsAbbv('CREATE TRIGGER','CG');
ins scriptdb.AccessRightsAbbv('CREATE VIEW','CV');
ins scriptdb.AccessRightsAbbv('DELETE','D');
ins scriptdb.AccessRightsAbbv('DROP AUTHORIZATION','DA');
ins scriptdb.AccessRightsAbbv('DROP FUNCTION ','DF');
ins scriptdb.AccessRightsAbbv('DROP MACRO','DM');
ins scriptdb.AccessRightsAbbv('DROP PROCEDURE','PD');
ins scriptdb.AccessRightsAbbv('DROP TRIGGER','DG');
ins scriptdb.AccessRightsAbbv('DROP VIEW','DV');
ins scriptdb.AccessRightsAbbv('DUMP','DP');
ins scriptdb.AccessRightsAbbv('INSERT','I');
ins scriptdb.AccessRightsAbbv('RESTORE','RS');
ins scriptdb.AccessRightsAbbv('SELECT','R');
ins scriptdb.AccessRightsAbbv('UPDATE','U');
ins scriptdb.AccessRightsAbbv('CREATE DATABASE','CD');
ins scriptdb.AccessRightsAbbv('CREATE USER','CU');
ins scriptdb.AccessRightsAbbv('DROP DATABASE','DD');
ins scriptdb.AccessRightsAbbv('DROP USER','DU');
ins scriptdb.AccessRightsAbbv('ALTER EXTERNAL PROCEDURE','AE');
ins scriptdb.AccessRightsAbbv('ALTER FUNCTION','AF');
ins scriptdb.AccessRightsAbbv('ALTER PROCEDURE','AP');
ins scriptdb.AccessRightsAbbv('CREATE EXTERNAL PROCEDURE','CE');
ins scriptdb.AccessRightsAbbv('CREATE FUNCTION','CF');
ins scriptdb.AccessRightsAbbv('EXECUTE FUNCTION','EF');
ins scriptdb.AccessRightsAbbv('EXECUTE PROCEDURE','PE');
ins scriptdb.AccessRightsAbbv('EXECUTE','E');
ins scriptdb.AccessRightsAbbv('DROP TABLE','DT');
ins scriptdb.AccessRightsAbbv('INDEX','IX');
ins scriptdb.AccessRightsAbbv('REFERENCES','RF');
ins scriptdb.AccessRightsAbbv('ABORT SESSION','AS');
ins scriptdb.AccessRightsAbbv('MONRESOURCE','MR');
ins scriptdb.AccessRightsAbbv('MONSESSION','MS');
ins scriptdb.AccessRightsAbbv('SETRESRATE','SR');
ins scriptdb.AccessRightsAbbv('SETSESSRATE','SS');
ins scriptdb.AccessRightsAbbv('CREATE PROFILE','CO');
ins scriptdb.AccessRightsAbbv('CREATE ROLE','CR');
ins scriptdb.AccessRightsAbbv('DROP PROFILE','DO');
ins scriptdb.AccessRightsAbbv('DROP ROLE','DR');
ins scriptdb.AccessRightsAbbv('REPLCONTROL','RO');


/ ***************SQL for AccessRights held by a user***********************/

SELECT UserName, DatabaseName, TableName,ColumnName,
CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description
ELSE ALRTS.AccessRight
END AS AccessRight, GrantAuthority, GrantorName, AllnessFlag,
CreatorName, CreateTimeStamp
FROM DBC.ALLRIGHTS ALRTS LEFT OUTER JOIN scriptdb.AccessRightsAbbv Abbv ON ALRTS.AccessRight = Abbv.AccessRight
WHERE UserName='???'
AND DatabaseName='???'
Order By 2,3,4,5;

/*************SQL for AccessRights held by a ROLE************************/

SELECT RoleName, DatabaseName, TableName,ColumnName,
CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description
ELSE ALRTS.AccessRight
END AS AccessRight, GrantorName, CreateTimeStamp
FROM DBC.ALLROLERIGHTS ALRTS LEFT OUTER JOIN scriptdb.AccessRightsAbbv Abbv
ON ALRTS.AccessRight = Abbv.AccessRight
WHERE RoleName='???'
AND DatabaseName='???'
Order By 2,3,4,5;