All Forums Database
barani_sachin 141 posts Joined 01/12
14 Mar 2013
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-0
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.

KS42982 137 posts Joined 12/12
15 Mar 2013

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

barani_sachin 141 posts Joined 01/12
15 Mar 2013

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? 

KS42982 137 posts Joined 12/12
15 Mar 2013

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.

barani_sachin 141 posts Joined 01/12
17 Mar 2013

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.

Adeel Chaudhry 773 posts Joined 04/08
18 Mar 2013

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

-- If you are stuck at something .... consider it an opportunity to think anew.

barani_sachin 141 posts Joined 01/12
18 Mar 2013

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. "

dnoeth 4628 posts Joined 11/04
18 Mar 2013

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

Dieter

barani_sachin 141 posts Joined 01/12
18 Mar 2013

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

barani_sachin 141 posts Joined 01/12
18 Mar 2013

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();

 

dnoeth 4628 posts Joined 11/04
18 Mar 2013

I don't know the exact reason, but it seems to be based on the combination of the security option and the dynamic SQL when a different user calls the SP.
When you change SECURITY to OWNER and use "COLLECT STATS temp COLUMN name;" instead of sysexecsql it seems to work.
Dieter

Dieter

barani_sachin 141 posts Joined 01/12
18 Mar 2013

Thanks Dieter for this workaround :)
So if we doesnt have a table which has the same name as the VT in the default database for the particular user it wont be a problem rite? i.e we dont need to concatenate username into the VT's and then do collect stats? Because while compiling its throwing warning such as
SPL5000:W(L9), E(3806):Table/view/trigger name 'temp' is ambiguous.

dnoeth 4628 posts Joined 11/04
18 Mar 2013

I prefer a naming convention for VTs (like VT_tab)  so there's never any chance that a permanent table with the same name exists :-)
Dieter

Dieter

barani_sachin 141 posts Joined 01/12
18 Mar 2013

thanks again for your timely replies :) Could pls redirect/explain me to a link where i can get a good definition/difference for
SECURITY OWNER, CREATOR, DEFINER, INVOKER

dnoeth 4628 posts Joined 11/04
18 Mar 2013

The Stored Procedues and the DDL manual?
Dieter

Dieter

ombest 1 post Joined 02/10
14 Jun 2013

Found a workaround for dinamic SQL and collect stat on volatile tables.
Set SQL SECURITY INVOKER
Creating volatile tables, fill it and others..
and before collecting stat write dummy statement like this:

set SQL_QUERY = 'insert into '||user||'.vt_tb(column1) select 1 a where a = 0';
execute immediate SQL_QUERY;

And after that collect stat.

Piotr_Skrzypiec 11 posts Joined 09/13
11 Oct 2013

On teradata 14 collecting statistics for volatile tables within stored procedure works for me fine, BUT
due to security reason I am granted I had to add SQL SECURITY INVOKER
CREATE PROCEDURE database.sp_name (IN p_country_iso_code_2 VARCHAR(10)) SQL SECURITY INVOKER
BEGIN
...
END;
 
Adding dummy sql_query with insert wasn't necessary.
I using ANSI mode and not using dynamic sql as it is not necessary for me.
 
I hope that saves someone about 4 hours
Piotr

You must sign in to leave a comment.