Stats collection inside SP's

Database
Enthusiast

Stats collection inside SP's

Is it possible to collect stats for VT's inside Procedures? 

http://developer.teradata.com/blog/carrie/2013/02/statistics-collection-recommendations-teradata-14-...

In the above link at the end its given as Currently, statistics are not supported on BEGIN and END period types.  That capability is planned for a future release.

Tags (3)
16 REPLIES
Enthusiast

Re: Stats collection inside SP's

Yes, it is possible to collect stats for volatile tables inside stored procedures.

Enthusiast

Re: Stats collection inside SP's

Could u pls post an simple example? I tried so many ways but no luck!!

Also what does she mean by this statistics are not supported on BEGIN and END period types? 

Enthusiast

Re: Stats collection inside SP's

Here is one simple example -

REPLACE PROCEDURE DBNAME.PROCNAME()

BEGIN

COLLECT STATISTICS TBNAME COLUMN COL1;

END;

CALL DBNAME.PROCNAME();

What kind of error do you get when you try that ? I use it in one of my SPs and it worked fine. At lease when I call it, I don't get error.

And sorry, I really don't understand what does it mean by BEGIN and END period type.

Enthusiast

Re: Stats collection inside SP's

KS Thanks for u r reply :)

This is the error i am getting while trying to run the SP.

CALL Failed. 3598:  STATS_CHECK:Concurrent change conflict on database -- try again. 

TD Version 14.

Teradata Employee

Re: Stats collection inside SP's

BEGIN and END period data-types are the specific types that Temporal table uses to manage history.

HTH!

Enthusiast

Re: Stats collection inside SP's

Thanks Adeel :) So that doesnt have anythign to do with Stats collection inside a SP??

Also could someone pls elaborate on what can i do regarding the error

"CALL Failed. 3598:  STATS_CHECK:Concurrent change conflict on database -- try again. "

Senior Apprentice

Re: Stats collection inside SP's

You should check the QueryLog which transactions were running in parallel.

Collect Stats is first using an Access lock and after the collection it's merging the new info, but i would rather expect a "3603 Concurrent change conflict on table".

Do you get this error every time or randomly?

Dieter

Enthusiast

Re: Stats collection inside SP's

@dieter : I get this same error all the time!!! I am sure that no query is trying to access this VT.

Enthusiast

Re: Stats collection inside SP's

PFB the simple SP which is throwing the above mentioned error! 

replace procedure db.proc() sql security creator
begin
declare uname varchar(100);
declare stmt1 varchar(1000);
select current_user into uname;
create volatile table temp ,NO FALLBACK, NO JOURNAL, NO LOG (name varchar(10)) on commit preserve rows;
CALL dbc.sysexecsql ( 'COLLECT STATISTICS '|| uname ||'.temp COLUMN name;');
end;

call db.proc();