I would like to execute some dynamic sql that goes out to a table and pulls back the columns of a primary index and plugs them into a collect statistic statement. Something like:
sel 'collect stats on ' || trim(databasename) || '.' || trim(tablename) || ' index ' || <primary index fields> || ';'
from dbc.<table or view>;
I have an automated stats collection process that collects on different columns based on pre-determined needs (i.e. current_ind). I haven't been able to find, within TD14, a table or view that lists the primary and secondary index columns on one return row. I usually run this when a new table in DEV is created to get a minimum amount of statistics coverage from the start.
If anyone has a script that accomplishes this I would be very thankful if they would want to share.
I was able to utilize a 3th party SP called Run_Index_CS_SP to collect stats on primary and secondary indexes. Still curious to see if anyone has been able to generate dynamic sql similar to the above in order to obtain and collect stats on table indexes.
Is this what you are looking for:
show table dbc.querystatstbl;----this has pi and si
as Raja mentioned, there's an undocumented udfconcat function in the TD14 tdstats database, you just have to add a Group By as it's an aggregate UDF.
My StatsInfo view returns the Collect Stats statement:
I tried udfconcat but I believe it pulls back all the index columns (primary and secondary) on the same line. Do you know of any way to only pull the UPI or USI columns? I will look, but was wondering if there were an index # filter or some other filter to id as either the primary or secondary index(s). For example, the table in the sql was created with a single column UPI and multi-column USI:
select tdstats.udfconcat(trim(columnname)) from dbc.Indicesv where
databasename='base_t' and tablename='account_group_tmp'
UPI = ACCOUNT_GROUP_KEY
USI = ACQ_SITE_ABBR,ACQ_SRC_SYS_ABBR,SRC_KTOKK
This would work perfect if I could isolate and collect on the UPI and USI seperately within a dynamic sql statement whose result would look like:
collect stats on BASE_T.ACCOUNT_GROUP_TMP column ACCOUNT_GROUP_KEY; UPI statement
collect stats on BASE_T.ACCOUNT_GROUP_TMP column (ACQ_SITE_ABBR,ACQ_SRC_SYS_ABBR,SRC_KTOKK); USI statement
Dieter - does your TD14 StatsInfo pull back the index columns or only the index columns that currently have stats collected? I believe that if I create a new table, then run StatsInfo, index columns would not be returned because stats had not yet been collected. Is that true?
What I'm really looking for is something that I can plug into dynamic sql that will create the stats statements for the index columns before the table has had any stats collected.
Thanks for taking the time to respond to my questions!
Found it!.... IndexType! I was pretty close to getting this to work. I tried running the following:
sel 'collect stats on ' || trim(databasename) || '.' || trim(tablename) || ' column ( ' || tdstats.udfconcat(trim(columnname)) || ');'
from dbc.Indicesv where databasename='base_t' and tablename='account_group_tmp'
It throws the following error:
select non-aggregate values must be part of the associated group.
Must be the udf call inside the dynamic sql statement...I appreciate your suggestions!
you need to add a Group By, e.g.
sel 'collect stats on ' || databasename || '.' || tablename
|| ' column ( ' || tdstats.udfconcat(columnname) || ');'
group by DatabaseName, TableName, IndexNumber
Added IndexNumber to the group by to get multiple indexes.
where IndexNumber = 1 -> all PIs
where IndexType in ('K','P','Q','S','U','V') -> all indexes, PI + SI
where IndexType in ('K','S','U','V') and IndexNumber <> 1 -> all SIs
My StatsInfo view doesn't return missing stats on indexes anymore in TD14 (the older version did), because that's to much overhead and it's easy to write in TD14.
Sorry for posting on an old thread. I'm looking into usage of udfconcat. Is there any way to control the order of the values that this function concatenates?