Is it possible to collect stats for VT's inside Procedures?
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.
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?
Here is one simple example -
REPLACE PROCEDURE DBNAME.PROCNAME()
COLLECT STATISTICS TBNAME COLUMN COL1;
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.
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.
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. "
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?
PFB the simple SP which is throwing the above mentioned error!
replace procedure db.proc() sql security creator
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;');