All Forums UDA
SarathyG 31 posts Joined 09/06
17 Oct 2006
Colstats privileges???

HiCould any one explain, what are the privileges(access rights) required to Collect Statistics on any Table?Also, which is the exact DBC table to look in to for obtaining all the privileges one user has on all the objects ?Thanks in adv.

- Sarathy G
aravind-3019 39 posts Joined 08/06
17 Oct 2006

Hi,Drop Table Privilege is required to collect and drop statistics.You can get privilege informations about a user from DBC.ALLRIGHTS view.Rgds,Aravind Hegde

Fred 1096 posts Joined 08/04
17 Oct 2006

You can also collect stats if you have the INDEX right (though that can only be granted on individual tables and not at the database level).And if you are using roles, you will need to look at RoleMembers and AllRoleRights in addition to AllRights.

hjshah22 6 posts Joined 10/06
18 Oct 2006

In case of join index ,(index or drop) privileges are required on both join index and base tables for collecting stats.

zenith 10 posts Joined 12/10
25 Mar 2011

We have Teradata 12. Is there any way to give COLLECT STATS permission to ETL developers, with out using privileges such as DROP TABLE (too risky) or INDEX (maintenance headache since its on table level) ?

dnoeth 4628 posts Joined 11/04
26 Mar 2011

No in TD12, in TD13 there's a new STATISTICS right.

Of course you can easily encapsulate the COLLECT STATS in a Stored Procedure using Dynamic SQL. Then developers don't need specific rights.

Dieter

Dieter

zenith 10 posts Joined 12/10
26 Mar 2011

Thanks Dieter
Appreciate if you have algorithm or a blueprint for such SP,,it'll be very useful so many forum members

dnoeth 4628 posts Joined 11/04
28 Mar 2011

You just have to concat a "collect stats" string and submit it using dbc.SsyExecSQL like:
REPLACE PROCEDURE collectstats (IN DatabaseName VARCHAR(30), IN TableName VARCHAR(30), IN ColumnList VARCHAR(300))
BEGIN
DECLARE CollStatsStmt VARCHAR(400);
SET CollStatsStmt = 'COLLECT STATISTICS ON ' || TRIM(DatabaseName) || '.' || TRIM(TableName) || ' column('|| TRIM(columnlist) || ');';
CALL dbc.SysExecSQL(CollStatsStmt);
END;

Of course this is very basic, no error handling, no checking.

Btw, JK Wight posted a nice script for recollecting stats on a database level to the TeradataForum at:
http://www.teradataforum.com/attachments/a101126a.txt

Dieter

Dieter

nazygholizadeh 6 posts Joined 11/10
09 Jun 2011

Dieter, I need a little help to understand.
How can we run a collect stats in a stored procedure without the user running it having the statistics permission?

nazygholizadeh 6 posts Joined 11/10
09 Jun 2011

OK, I tried it by submitting it using dbc.SsyExecSQL and it works, but I am not sure I understand why it works that way.

rtefft 11 posts Joined 03/09
09 Sep 2011

I am having privilege/grant problems collecting stats from a procedure.  I use SysExecSQL to collect the stats, but get *** Failure 3523 Acft_SCIR_Derived_Proc:An owner referenced by user does not have INDEX WITH GRANT OPTION access to TABLE1.

The procedure is in the MACSDB database, the table is in WORKDB database.  The user who created both procedure and table (and runs the proc) is the same.  I issue:

grant index on WORKDB.TABLE1  to MACSDB with grant option;

But the procedure still fails at runtime trying to collect the stats:

set S_SQL='collect statistics on WORKDB.TABLE1 column partition';
call DBC.SysExecSQL (S_SQL);

Can anyone tell me what additional privs are needed?

Thanks in advance,

Rich

You must sign in to leave a comment.