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;

Clean up the DBC.AccessRights

/* -------------------------------------------------------------*/
/* Check for "orphan" accessright entries, i.e. accessrights that reference */
/* non-existent tables. Orphan access rights will not cause a problem unless you */
/* get so many that they impact the access performance. */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights
WHERE TVMID <> '000001000000'xb
AND TVMID NOT IN (SEL TVMId FROM DBC.TVM);

/* -------------------------------------------------------------*/
/* Check for accessrights that reference non-existent databases. */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights
WHERE DatabaseId NOT IN (SEL DatabaseId from DBC.DBase);

/* -------------------------------------------------------------*/
/* Check for access rights granted to non-existent users. */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights
WHERE UserID NOT IN (SEL DatabaseID FROM DBC.DBase)
AND UserID NOT IN (SEL RoleID from DBC.Roles);




/* -------------------------------------------------------------*/
/* Check for accessrights that extend Reference or Index access at the database */
/* level (this is only valid at the table level). Reference DR 43044 */
/* If these exist it is a security hole. */
/* All must be removed or any subordinate databases will inherit the problem. */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights
WHERE AccessRight IN ('IX','RF')
AND Databaseid <> '00'xb
AND TVMId = '000000000000'xb;

/* -------------------------------------------------------------*/
/* Check for accessrights entries that are not properly manipulated by access */
/* logging (see DR 48757) */
/* -------------------------------------------------------------*/

SELECT count(*) FROM DBC.AccessRights WHERE AccessRight = 'RO'
and userid = '00000100'xb AND databaseid = '00000100'xb;

Get collect stats details from Dieter Noeth Collect Views

Here is useful Query to get detailed report and make it a script from Dieter Noeth Collect Views:

First you have to install the "Dieter Noeth Collect Views", you can find it by search in T@YS

What is this query doing?

This query just do the following:

• Translate the flags in the views to its meaning (ex. C -> Column , I -> Index) and so on.
• Consider if the column or index is having full stat or sample.
• You can specify the collect duration or since what this collect done.
• Build a collect stat statement based on the above points.
  

/*Determine which collect stats take a long time or had a stale stats */

select
-- databasename, tablename, columnname, collectdate,collectduration,samplesize
'COLLECT STATISTICS' || case when samplesize is not null then ' USING SAMPLE' when samplesize is null then '' end || ' ON ' || databasename || '.' || trim(tablename) || ' '|| case when statstype='c' then 'COLUMN' when statstype='i' then 'INDEX' when statstype='m' then 'COLUMN' end || ' (' || trim(columnname) || ');'
--*
from {DB Contains Dieter Noeth Collect Views}.stats_details
where
collectduration > '0:30:00.00' -- For collect takes longer than 30 minutes
databasename = '{Database you need to inquire on}'
and tablename = '{Table Name}' -- Tables that you need to inquire, you can hash it if you need to see all tables
and columnname = '{Column Name}' -- COlumn that you need to inquire, you can hash it if you need to see all Column
and tablename not like '%temp%' -- If you need to exclude some tables, you can hash it if you need to see all tables
and cast(collectdate as date) < current_date -5 -- This means get all collect stat that run older than 5 days
order by collectduration ;

How to detriment if your sample statistics is suitable

To detriment if your sample statistics is or not (TD 12) you may need to make a HELP STATISTICS statement on you table or column twice, one with full stats and the other after applying the sampling and see the difference

1- To make a help stats on table level:

HELP STATISTICS "tablename";


2- To make a help stats on Column level:

HELP STATISTICS "tablename" Column "columnname";

Collect Statistics using sample in TD 12

There is a new feature in TD 12 that you can specify the sample percent you need before running collect stat.

You can do it on the,

1- session level:

DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=20" ON FOR SESSION;

The above statment means that all collect stats starment with "USING SAMLE" keyword in the session will be collected with 20% (You can change it to what is suitable for your tables).

2- Request level:

DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=20" ON FOR REQUEST;

That's means it will applyed only on the next collect statment that follows the diagnostic statment.

How to clean / Purge DBQL Tables

delete from DBC.DBQLObjTbl Where CollectTimeStamp < '2009-10-01 00:00:00';
delete from DBC.DBQLogTbl where LogonDateTime < '2009-10-01 00:00:00';
delete from DBC.DBQLSqlTbl Where CollectTimeStamp < '2009-10-01 00:00:00';
delete from DBC.DBQLExplainTbl where CollectTimeStamp < '2009-10-01 00:00:00';
delete from DBC.DBQLStepTbl Where CollectTimeStamp < '2009-10-01 00:00:00';
delete from DBC.DBQLSummaryTbl where CollectTimeStamp < '2009-10-01 00:00:00';