Teradata: Automating statistics in Stats Manager

Defining the statistics you have to play against one or more tables in a database

For an example, we are going to implement statistics against the dictionary. These statistics are excerpted from Carrie Ballinger’s Blog, to make everything clear!

First of all, we must play the following script in order to register the statistic in the dictionary. Though, statistics will be collected one time, but it’s not enough, you will need to synchronize the dictionary with stats manager…

Let’s try it!

List of Collect statistics commands

COLLECT STATISTICS
COLUMN TvmId ,
COLUMN UserId ,
COLUMN DatabaseId ,
COLUMN FieldId ,
COLUMN AccessRight ,
COLUMN GrantorID ,
COLUMN CreateUID ,
COLUMN (UserId ,DatabaseId) ,
COLUMN (TVMId ,DatabaseId) ,
COLUMN (TVMId ,UserId) ,
COLUMN (DatabaseId,AccessRight) ,
COLUMN (TVMId,AccessRight) ,
COLUMN (FieldId,AccessRight) ,
COLUMN (AccessRight,CreateUID) ,
COLUMN (AccessRight,GrantorID) ,
COLUMN (TVMId ,DatabaseId,UserId)
ON DBC.AccessRights;

COLLECT STATISTICS
COLUMN DatabaseId ,
COLUMN DatabaseName ,
COLUMN DatabaseNameI ,
COLUMN OwnerName ,
COLUMN LastAlterUID ,
COLUMN JournalId ,
COLUMN (DatabaseName,LastAlterUID)
ON DBC.Dbase;


COLLECT STATISTICS
COLUMN LogicalHostId ,
INDEX ( HostName )
ON DBC.Hosts;

COLLECT STATISTICS
COLUMN OWNERID ,
COLUMN OWNEEID ,
COLUMN (OWNEEID ,OWNERID)
ON DBC.Owners;

COLLECT STATISTICS
COLUMN ROLEID ,
COLUMN ROLENAMEI
ON DBC.Roles;

COLLECT STATISTICS
INDEX (GranteeId)
ON DBC.RoleGrants;
 
COLLECT STATISTICS
COLUMN (TableId) ,
COLUMN (FieldId) ,
COLUMN (FieldName) ,
COLUMN (FieldType) ,
COLUMN (DatabaseId) ,
COLUMN (CreateUID) ,
COLUMN (LastAlterUID) ,
COLUMN (UDTName) ,
COLUMN (TableId,FieldName)
ON DBC.TVFields;
 
COLLECT STATISTICS
COLUMN TVMID ,
COLUMN TVMNAME ,
COLUMN TVMNameI ,
COLUMN DATABASEID ,
COLUMN TABLEKIND ,
COLUMN CREATEUID ,
COLUMN CreatorName ,
COLUMN LASTALTERUID ,
COLUMN CommitOpt ,
COLUMN (DatabaseId,TVMName) ,
COLUMN (DATABASEID ,TVMNAMEI)
ON DBC.TVM;
 
COLLECT STATISTICS
INDEX (TableId) ,
COLUMN (FieldId) ,
COLUMN (IndexNumber) ,
COLUMN (IndexType) ,
COLUMN (UniqueFlag) ,
COLUMN (CreateUID) ,
COLUMN (LastAlterUID) ,
COLUMN (TableId,DatabaseId) ,
COLUMN (TableId,FieldId) ,
COLUMN (UniqueFlag,FieldId) ,
COLUMN (UniqueFlag,CreateUID) ,
COLUMN (UniqueFlag,LastAlterUID) ,
COLUMN (TableId,IndexNumber,DatabaseId)
ON DBC.Indexes;
 
COLLECT STATISTICS
COLUMN (IndexNumber) ,
COLUMN (StatsType)
ON DBC.StatsTbl;
 
COLLECT STATISTICS
COLUMN (ObjectId) ,
COLUMN (FieldId) ,
COLUMN (IndexNumber) ,
COLUMN (DatabaseId,ObjectId,IndexNumber)
ON DBC.ObjectUsage;
 
COLLECT STATISTICS
INDEX (FunctionID ) ,
COLUMN DatabaseId ,
COLUMN ( DatabaseId ,FunctionName )
ON DBC.UDFInfo;
 
COLLECT STATISTICS
COLUMN (TypeName) ,
COLUMN (TypeKind)
ON DBC.UDTInfo;
 
COLLECT STATISTICS
COLUMN (QueryID)
ON DBC.DBQLSqlTbl;
 
COLLECT STATISTICS
COLUMN (CollectTimeStamp),
COLUMN (QueryID),
COLUMN (UserID),
COLUMN (SessionID),
COLUMN (AppID),
COLUMN (StartTime),
COLUMN (ErrorCode),
COLUMN (StatementType),
COLUMN (UserName)
ON DBC.DBQLogTbl;

 

Syncing the DBC dictionary with TDStats database

  • Click Run…

  • Uncheck Null case in DatabaseName field

On DatabaseName line, uncheck Null and enter DBC

  • Click OK

Result

DatabaseName,INPUT,VARCHAR(128),DBC,
TableName,INPUT,VARCHAR(128),,
ObjectListName,INPUT,VARCHAR(128),,
NewerThan,INPUT,TIMESTAMP WITH TIME ZONE,,
ExcludeTempTables,INPUT,CHARACTER(1),,
MarkApproved,INPUT,CHARACTER(1),,
DeleteOrphans,INPUT,CHARACTER(1),,
AutomateId,OUTPUT,BIGINT,,909
NumCopied,OUTPUT,INTEGER,,51
NumRemoved,OUTPUT,INTEGER,,0

Create Job on Stats Manager

  • Click Jobs Tab / Actions / New Collect Job

  • In Job name field, enter the job’s name
  • Check Custom object list
  • Click Edit list

  • In Database type DBC
  • Click Add Pattern
  • Click OK

  • Click Collect List tab
  • Click Preview list

  • Click Close

  • Click Save

 Scheduling a job

  • Click on the blue arrow
  • Click Edit

 

  • Click Add Schedule

  • In Repeat list, select Weekly
  • In Repeat every, type 1
  • In On, Select Monday
  • In Start at, choose the time you want to execute the job
  • Click OK

Repeat this operation for every day of the week you want to schedule the job for.

  • Click Save

 Troubleshooting

 Invalid session mode

Failed [5510 : HY000] Invalid session mode for procedure execution.

Solution: Change ANSI Mode To TERA

  • Click Add…

  • Click OK

  • Click OK
  • Click Test Connection

  • Click OK

  • Click Yes

 Access problem

The user does not have EXECUTE PROCEDURE access to TDStats.AutomateStats.

Solution

  • GRANT EXECUTE PROCEDURE ON TDSTATS TO SYSDBA

 Error after job is completed

 Finding the error

  • Click on Collect_DBC

  • Click on 1 to view the details of error

Solution

GRANT STATISTICS ON DBC.TVFields TO TDSTATS;
GRANT STATISTICS ON AccessRights TO TDSTATS;
GRANT STATISTICS ON DBC.DBQLSqlTbl TO TDSTATS;
GRANT STATISTICS ON DBC.DBQLogTbl TO TDSTATS;
GRANT STATISTICS ON DBC.Dbase TO TDSTATS;
GRANT STATISTICS ON DBC.Hosts TO TDSTATS;
GRANT STATISTICS ON DBC.Indexes TO TDSTATS;
GRANT STATISTICS ON DBC.ObjectUsage TO TDSTATS;
GRANT STATISTICS ON DBC.Owners TO TDSTATS;
GRANT STATISTICS ON DBC.RoleGrants TO TDSTATS;
GRANT STATISTICS ON DBC.Roles TO TDSTATS;
GRANT STATISTICS ON DBC.StatsTbl TO TDSTATS;
GRANT STATISTICS ON DBC.TVM TO TDSTATS;
GRANT STATISTICS ON DBC.UDFInfo TO TDSTATS;
GRANT STATISTICS ON DBC.UDTInfo TO TDSTATS;

Run again your job to check if everything is correct now.