New StatsInfo query for TD14

Attachment	Size
stats_td14_20130830.zip	212.2 KB
stats_td14_20140605.txt	50.26 KB

Beginning with TD14 statistics are no longer stored in dbc.TVFields and dbc.Indexes, they have been moved into dbc.StatsTbl to facilitate several enhancements. A new view dbc.StatsV returns much of the information previously extracted in my StatsInfo query.

But of course this is still not enough information, at least not for me ;-)

dbc.StatsV vs. StatsInfo TD13/14

I tried to get the best of both worlds and so i wrote a new version for TD14 to extract as much additional data as possible. I had to remove a few columns (they're no longer needed or it's no longer possible to get that info) and i renamed some to match the new names in dbc.StatsV. Most of the new columns were simply not available before TD14.

Following table describes the new StatsInfo view and details the differences to the previous version and dbc.StatsV:

dbc.StatsV TD14	New StatsInfo TD14	StatsInfo TD13.10	Remarks	Description
	(Column added/removed/changed)
DatabaseName	DatabaseName	DatabaseName
TableName	TableName	TableName
ColumnName	ColumnName	ColumnName	StatsId <> 0	List of comma-separated column names
FieldIdList	FieldIdList	FieldId	StatsId <> 0	List of semicolon-separated field ids
StatsName	StatsName	StatsName	StatsId <> 0	Alias name of the statistics (if assigned)
	IndexName	IndexName	StatsId <> 0	Name of the index (if assigned)
	DateIncluded	DateIncluded	StatsId <> 0	DATE or TIMESTAMP column included, Y/N
	PartitionColumn	PartitionColumn	StatsId <> 0	Column included which is used in the table's partitioning expression: Y/N
	PartitionLevels	PartitionLevels		Number of levels in the table's partitioning expression, zero means not partitioned
	ColumnPartitioningLevel			Level number for the column partitioning level, > 0 indicates columnar table
	PartitionsDefined	PartitionsDefined		The number of partitions defined
ExpressionCount	ExpressionCount	ColumnCount	StatsId <> 0	The number of columns in the statistics
StatsId	StatsId			StatsId = 0 → Summary Stats
StatsType	StatsType			Statistics collected on: T → Table I → Join Index N → Hash Index V → View 14.10? Q → Query 14.10? L → Link Row 14.10?
	StatsTypeOld	StatsType		Statistics collected on: Summ → Summary Statistic UPI → Unique Primary Index NUPI → Non-Unique Primary Index USI → Unique Secundary Index NUSI → Non-Unique Secondary Index VOSI → Value Ordered NUSI Part → Pseudo column PARTITION Col → Single Column MCol → Multiple columns
	TableType	TableType		TempTbl → Global Temporary Table Tbl → Table JoinIdx → Join Index HashIdx → Hash Index NoPITbl → No Primary Index Table
StatsSource	StatsSource			The method this statistic is acquired: I → Internally generated S → User collected with COLLECT STATS (system built) U → User collected with COLLECT STATS VALUES clause C → Copied from other sources T → Transferred with CREATE TABLE...AS statement
ValidStats	ValidStats			TD14.10: Indicates whether the statistics are valid or not: Y/N
DBSVersion	DBSVersion			Database version statistics collected on
SampleSizePct	SampleSizePct	SampleSize	StatsId <> 0	Sample size used for collect stats, NULL if not sampled
SampleSignature	SampleSignature		StatsId <> 0	Sample option encoded as a 10 character signature USPnone → collected using NO SAMPLE USP00nn.00 → collected using SAMPLE nn PERCENT SDPxxxx.xx → sample size determined by system
ThresholdSignature	ThresholdSignature		StatsId <> 0	THRESHOLD options encoded as a 17 character signature (not used before TD14.10) Characters 1 to 10 → THRESHOLD PERCENT SCTxxxx.xx → System defined UCT005.00 → User defined 5 percent UCTnone → User defined no threshold Characters 11 to 17 → THRESHOLD DAYS STTxxxx → System defined UTT0010 → User defined 10 days UTTnone → User defined no threshold
MaxIntervals	MaxIntervals		StatsId <> 0	User-specified maximum number of intervals
StatsSkipCount	StatsSkipCount		StatsId <> 0	TD14.10 only: How many times the statistis collection has been skipped based on the THRESHOLD
MaxValueLength	MaxValueLength		StatsId <> 0	User-specified maximum value length
LastCollectTimestamp	LastCollectTimestamp	CollectTimestamp		Date and time when statistics were last collected
	LastCollectDate	CollectDate
	LastCollectTime	CollectTime
RowCount	RowCount	NumRows		The cardinality of the table, i.e. the number of rows
UniqueValueCount	UniqueValueCount	NumValues	StatsId <> 0	Distinct Values. Estimated when sampled
PNullUniqueValueCount	PNULLUniqueValueCount		StatsId <> 0	Number of unique values from rows with partial NULLs (multicolumn stats) Estimated when sampled
NullCount	NULLCount	NumNULLs	StatsId <> 0	Number of partly NULL and all NULL rows, estimated when sampled
AllNullCount	AllNULLCount	NumAllNULLs	StatsId <> 0	Number of all NULL rows (multicolumn stats), estimated when sampled
HighModeFreq	HighModeFreq	ModeFreq	StatsId <> 0	Frequency of the most common value, estimated when sampled
PNullHighModeFreq	PNULLHighModeFreq		StatsId <> 0	Highest frequency of values having partial NULLs (for multicolumn stats), stimated when sampled
AvgAmpRPV	AvgAmpRPV	AvgAmpRPV	StatsId <> 0	Overall average of the average rows per value from each AMP, only for NUSIs, otherwise zero
	MinValue	MinValue	StatsId <> 0	Minimum data value (only for single column numeric or datetime stats)
	ModalValue	ModalValue	StatsId <> 0	Most common data value (only for single column numeric or datetime stats)
	MaxValue	MaxValue	StatsId <> 0	Maximum data value (only for single column numeric or datetime stats)
	OneAMPSampleEst	OneAMPSampleEst	StatsId = 0	Estimated cardinality based on a single-AMP sample
	AllAmpSampleEst	AllAmpSampleEst	StatsId = 0	Estimated cardinality based on an all-AMP sample
DelRowCount	DelRowCount		StatsId = 0	Deleted rows count??? used in 14.10 ???
PhyRowCount	PhyRowCount		StatsId = 0	Seems to be the same as AllAMPSampleEst – used in 14.10 ???
AvgRowsPerBlock	AvgRowsPerBlock		StatsId = 0	Average number of rows per datablock???
AvgBlockSize	AvgBlockSize		StatsId = 0	Average datablock size???
BLCPctCompressed	BLCPctCompressed		StatsId = 0	Blockcompression in percent??? used in 14.10 ???
BLCBlkUcpuCost	BLCBlkUcpuCost		StatsId = 0	CPU cost for Blockcompression??? used in 14.10 ???
BLCBlkURatio	BLCBlkURatio		StatsId = 0	??? used in 14.10 ???
AvgRowSize	AvgRowSize		StatsId = 0	Average record size???
Temperature	Temperature		StatsId = 0	populated in 14.10???
NumOfAMPs	NumOfAMPs	NumAMPs		The number of AMPs from which statistics were collected, usually the number of AMPs in the system, 1 for an empty table
CreateTimeStamp	CreateTimeStamp			Statistics creation timestamp
LastAlterTimeStamp	LastAlterTimeStamp	LastAlterTimeStamp		Different meaning: Last user updated timestamp, i.e. Collect stats was submitted but skipped by optimizer due to threshold not reached
	LastAccessTimestamp	LastAccessTimestamp		The last time this column/index was used in queries, the same info is found in dbc.TablesV and dbc.IndicesV
	AccessCount	AccessCount		How often this column/index was used in queries, the same info is found in dbc.TablesV and dbc.IndicesV
	TableId	TableId		To facilitate additional joins to other system tables
	IndexNumber	IndexNumber	StatsId <> 0	Index number of the index on which statistics are collected
	FieldType	FieldType		Single column stats: dbc.TVFields.FieldType, NULL for multi-column
	Version	StatsVersion		Internal version of statistics: 5 → TD14 6 → TD14.10
OriginalVersion	OriginalVersion		StatsId <> 0	Probably version when stats were migrated from older releases, but not yet recollected 4: pre-TD14 5: TD14.00 6: TD14.10
NumOfBiasedValues	NumOfBiasedValues		StatsId <> 0	Number of biased values in the histogram
NumOfEHIntervals	NumOfEHIntervals		StatsId <> 0	Number of equal height intervals in the histogram
NumOfRecords	NumOfRecords			Number of history records in the histogram
	CollectStatement	CollectStatement		COLLECT STATS statement to collect the stats. Two versions with or without double-quoted object names
	ShowStatement	HelpStatement		SHOW STATS VALUES statement to get the stats details. Two versions with or without double-quoted object names.
		MissingStats		Was a side-product of the old query, too much overhead to add
		NumIntervals		Replaced by NumOfBiasedValues & NumOfEHIntervals
		CollectDuration		Not (yet) possible, i don't know if this is stored somewhere
		NumericStats		No longer neccessary
		DataSize		Too much overhead to calculate, not really needed as the limitation of 16 bytes is removed in TD14

Please report any issues or obviously wrong output to dnoeth@gmx.de.

Attached files:

StatsInfo_vs_StatsV.pdf	Describes the new StatsInfo view and details the differences to the previous version and dbc.StatsV - added, modified and removed columns (same as above table)
Teradata Statistics TD14.pdf	Partial description of the new internal stats format based on some reverse engeneering of the binary data stored in a BLOB in dbc.StatsTbl.Histogram. Luckily the internal storage maps almost 1:1 to the output of a SHOW STATISTICS VALUES :-)
stats_td14_yyyymmdd.sql	StatsInfo source code. To keep the code clean it's based on SQL-UDFs
ReverseBytes.sql ReverseBytes.c	Can be used to replace the ReverseBytes SQL-UDF with a C-UDF which uses way less CPU (but most DBA's don't like C). Note: I'm not a C programmer, but this was so basic even I could write it.

Uploaded stats_td14_20140605.txt, fixing two bugs:

2014-02-03 dn fixed a bug resulting in a Syntax error for a „SELECT * FROM StatsInfo14;" in ANSI mode

2014-06-05 dn fixed a bug returning duplicate rows for tables with additional column constraints

Discussion

suhailmemon84 64 comments Joined 09/10

02 Sep 2013

We're still at 13.10 and use the column: "missing stats" to find all those tables with no stats on them and add pi and parition stats on them. Is there a simple way to find this info?
How about finding all those tables that are not in the below list:

(
SELECT DATABASENAME,TABLENAME FROM DBC.COLUMNSTATS WHERE FIELDSTATISTICS IS NOT NULL
UNION
SELECT DATABASENAME,TABLENAME FROM DBC.INDEXSTATS WHERE INDEXSTATISTICS IS NOT NULL
UNION
SELECT DATABASENAME,TABLENAME FROM DBC.MULTICOLUMNSTATS WHERE COLUMNSSTATISTICS IS NOT NULL
)
;

I want to ensure I do not break my script once we go to TD 14

sg186048 4 comments Joined 09/12

06 Sep 2013

Hi Dnoeth,
I was able to compile the Functions and the views without any problems , but after that when I executed
sel * from km84.StatsInfo14 , I get the following error
8500 : Object not found
Please advise

dnoeth 86 comments Joined 11/04

11 Sep 2013

@suhailmemon84:
The old Field/Index/MultiColumnStats views don't work anymore in TD14, they're always empty. In fact it's much easier, you just have to check for those names not in dbc.StatsV
@sg186048:
What's your release? I never encountered this before, it's indicating a problem with a LOB and the only LOB is the stats Histogram.
Can you run the select directly instead of querying the view?

Sorry for the late answer, but i was on vacation :-)
Dieter

Dieter

suhailmemon84 64 comments Joined 09/10

11 Sep 2013

Alright thanks Dieter. Thats relieving.
Regards,
Suhail

suhailmemon84 64 comments Joined 09/10

13 Sep 2013

Hello Dieter,
Another quick question. The previous pdcrinfo.statsinfo view(of 13.10) showed details of all tables: regardless of whether they had stats in them or not. Is the new view (of 14) doing the same too? Or it just reports those tables that have stats in them already?
Regards,
Suhail

dnoeth 86 comments Joined 11/04

14 Sep 2013

Hi Suhail,
the StatsInfo view is not part of PDCR, it was just created within the PDCRInfo database at your site.
And the new view doesn't show indexes without stats anymore, it was just a nice a side-product of the old query, but adding it to the StatsInfo14 would result in a much more complicated query.

Dieter

Dieter

suhailmemon84 64 comments Joined 09/10

16 Sep 2013

I'm sorry for confirming this again on more time.
If i create a table and add no statistics to it, the statsinfo14 view will not show it. Correct?
-Suhail

dnoeth 86 comments Joined 11/04

16 Sep 2013

Hi Suhail,
yes :-)

Dieter

Dieter

smadagan...mail.com 4 comments Joined 10/11

16 Sep 2013

Hello Dieter,
I am getting an error while executing view " REPLACE VIEW Failed. 3706: Syntax error: Data Type "ColumnName" does not match a Defined Type name. "
We are in 14.00.04.02.

-Rao

dnoeth 86 comments Joined 11/04

16 Sep 2013

Hi Rao,
this error is afaik returned when the optimizer couldn't match the datatypes.
Strange, is this at OREPLACE(ColumnName, ....)?
Do you have a pre-TD14 version of OREPLACE in your default database which doesn't support unicode?

Dieter

Dieter

dnoeth 86 comments Joined 11/04

16 Sep 2013

Hi Rao,
i just checked it, this error is returned when there an unknown function, seems like the optimizer didn't find OREPLACE.
SELECT * FROM dbc.functionsV WHERE functionname = 'OREPLACE'

should return 2 rows:

DatabaseName FunctionName SpecificName

TD_SYSFNLIB OREPLACE oreplace2

TD_SYSFNLIB OREPLACE oreplace

Dieter

smadagan...mail.com 4 comments Joined 10/11

17 Sep 2013

Thx Dieter.
Yes, this is at OREPLACE(ColumnName, ....).

Here is the 2 rows returned when I submit "SELECT * FROM dbc.functionsV WHERE functionname = 'OREPLACE' "

DatabaseName FunctionName SpecificName     FunctionId          NumParameters ParameterDataTypes
TD_SYSFNLIB      OREPLACE       oreplace_cu      00-00-43-07-00-00         3                       COCOCO
TD_SYSFNLIB      OREPLACE       oreplace_cu2    00-00-44-07-00-00         3                       CVCOCO
- Rao

dnoeth 86 comments Joined 11/04

17 Sep 2013

On 14.00.04.05 it's:

DatabaseName	FunctionName	SpecificName	FunctionId	NumParameters	ParameterDataTypes
TD_SYSFNLIB	OREPLACE	oreplace2	00-00-57-0C-00-00	2	++++
TD_SYSFNLIB	OREPLACE	oreplace	00-00-56-0C-00-00	3	++++++

What's your exact release?
'CO' means CLOB, '++' is TD14's TD_ANYTYPE.

Dieter

Dieter

smadagan...mail.com 4 comments Joined 10/11

17 Sep 2013

We are in: 14.00.04.02
InfoKey       InfoData
VERSION    14.00.04.02
RELEASE     14.00.04.02

- Rao

dnoeth 86 comments Joined 11/04

17 Sep 2013

Hi Rao,
strange, very strange. If this were functions in SYSLIB there would be an explanation, you simply created a non-standard version on your own. But only TD R&D can add functions to TD_SYYFNLIB
You should ask your Teradata support for assistance.
Btw, does a simple oReplace('blablablablablablablablablablablabla', 'bl', '') work?
Dieter

Dieter

smadagan...mail.com 4 comments Joined 10/11

17 Sep 2013

Thx Dieter. I will try.
-Rao

JHuk 2 comments Joined 11/13

19 Nov 2013

Hi Dieter

tas_58 4 comments Joined 12/13

04 Dec 2013

hi .
i need to convert orcale package which have many storedprocedure into to teradata , but there is no package concept in teradata so how can i use something which can hold more than one StoredProcedure ??
please reply..

tas_58 4 comments Joined 12/13

04 Dec 2013

how can i use %rowtype is teradata?
is there any similar type in teradata like oracle %rowtype?

dnoeth 86 comments Joined 11/04

04 Dec 2013

@tas_58: Could you please post totally unrelated questions to the forum.

Dieter

Sivakumar.thota 5 comments Joined 01/13

13 Mar 2014

Hi Dieter,
I am trying to find unused stats, I tried to use your view by keeping filter on Lastaccesstimestamp. But it is showing as Null for all the multi column stats?
Please let me know if there is any other way to find unused stats.
we are on TD 13.10.

dnoeth 86 comments Joined 11/04

13 Mar 2014

Hi Sivakunar,
as you're on 13.10,did you use my old version?: StatsInfo pre-TD14

Is the access count feature enabled on your system, i.e. do other dbc views like dbc.TablesV return values?

Dieter

Sivakumar.thota 5 comments Joined 01/13

13 Mar 2014

Yes Dieter, I am using the old version.
And yes, access feature is enabled. I can use the counts for the views you mentioned.
In fact, I can see the count for the single column stats through view. It is just multi column stats which is showing null for lastaccesstimestamp and Accesscount for all multi coulmn stats.

dnoeth 86 comments Joined 11/04

14 Mar 2014

Hi Sivakunar,
it was quite late yesterday, so I didn't read your post thoroughly.
LastAccessTimestamp does not carry info about when stats where used, it's just the last time the object was accessed in a query. Thus for multicolumn stats it's always NULL.

Dieter

Sivakumar.thota 5 comments Joined 01/13

14 Mar 2014

Thanks Dieter for the clarification.

Is there any other way to identify the unused stats.

dnoeth 86 comments Joined 11/04

14 Mar 2014

Hi Sivakunar,
afaik in TD13.10 there's no way to tell if stats are not used by the optimizer except reading explains and checking the confidence level.
In TD14.10 there's a STATSUSAGE option in BEGIN QUERY LOGGING as a part of the AutoStats feature.

Dieter

Sivakumar.thota 5 comments Joined 01/13

14 Mar 2014

k, thanks Dieter

TusharGade 1 comment Joined 10/11

05 May 2014

Hi Dieter,

Just curious if I can do help stats in a sp on TD 13.10?

Thanks,

dnoeth 86 comments Joined 11/04

05 May 2014

@TusharGade:
No, you can't use any kind of HELP or SHOW in a SP...
BUT:
There was a nice article on how to bypass that restriction recently:
Running Unsupported Queries from a Stored Procedure
I didn't test it, yet, but this sounds great and you should be able to change the source code to support HELP, too.

Dieter

hari_gaurie 1 comment Joined 07/14

02 Jul 2014

Hi Dieter,

We are using 14.10 with Sles11 and when i tried executing view " REPLACE VIEW Failed. 3706: Syntax error: Data Type "ColumnName" does not match a Defined Type name. "

Below is output of
"SELECT * FROM dbc.functionsV WHERE functionname = 'OREPLACE' "
DatabaseName FunctionName SpecificName FunctionId NumParameters ParameterDataTypes FunctionType ExternalName SrcFileLanguage NoSQLDataAccess ParameterStyle DeterministicOpt NullCall PrepareCount ExecProtectionMode ExtFileReference CharacterType Platform InterimFldSize RoutineKind ParameterUDTIds AuthIdUsed MaxOutParameters GLOPSetDatabaseName GLOPSetMemberName
1 TD_SYSFNLIB OREPLACE oreplace 00-00-C3-07-00-00 3 ++++++ F oreplace P Y I N Y N S ? 1 UDTFP 0 R ? ? 0 ? ?
2 TD_SYSFNLIB OREPLACE oreplace2 00-00-C4-07-00-00 2 ++++ F oreplace2 P Y I N Y N S ? 1 UDTFP 0 R ? ? 0 ? ?

Please suggest

abhishek.jadhav 3 comments Joined 02/08

08 Aug 2014

Hi Dieter,

When i do Show Stats on one column it gives following values

/* MinVal */ ‘AAAA’, 'Truncated',

/* MaxVal */ ‘AAAA, 'Truncated',

/* ModeVal */ ‘AAAA, 'Truncated',

I want to identify all the columns where the Histogram data is truncated and then may be increase the maxvaluelength for those columns to see if i get a better plan.

I queried Statsinfo14 but it did not report any.

Statsinfo14 returns NULL (?) for MinVal, MaxVal, and ModeVal whereever we have the above pattern (‘AAAA’, 'Truncated’,)

Can you please let me know why does statsinfo14 returns NULL instead of the (‘AAAA’, 'Truncated’,)

- Abhishek Jadhav

Thanks
Abhishek Jadhav

dnoeth 86 comments Joined 11/04

08 Aug 2014

Hi Abhishek,
yep, it's returning NULL because it's a VarChar, read the description of Min/Max/ModeVal:
(only for single column numeric or datetime stats)
The reason is simple, I don't know how to extract strings from the stats-BLOB :-)

You might calculate the SUM(ColumnLength) of all columns in a statistic, if it's less greater than MaxValueLength (the default is afaik 26) you can flag it as possible truncated.
I've never tried that and since ColumnLength is not always the physical size it might need some additional tweaks...

Dieter

mpatil 3 comments Joined 11/11

22 Sep 2014

Hello Dieter,

We recently upgraded to 14.10 from 13.10,

One of our procedure contains below code,

SELECT

"DatabaseName"

,"TableName"

,"IndexNumber"

,"IndexName"

,"UniqueFlag"

,"ColumnPosition"

,"ColumnName"

,"IndexStatistics"

FROM

"DBC"."IndexStats"

WHERE

"DatabaseName" = :inDatabaseName

AND "IndexType" IN ('K', 'P', 'Q', 'S')

There are 4 columns which do not exist in DBC.StatsV from above code.

What can be the replacement?

I could see DBC.IndexStatsV but again source is same.

dnoeth 86 comments Joined 11/04

22 Sep 2014

ColumnPosition is not needed anymore as you get all ColumnNames in a comma-delimited list.
IndexName and UniqueFlag would need a join to dbc.IndicesV using DatabaseName/TableName/IndexNumber plus a condition WHERE ColumnPosition = 1.

Dieter

mpatil 3 comments Joined 11/11

22 Sep 2014

Thanks Dieter, Could you please elaborate on "IndexStatistics"?
What can be the replacement?

dnoeth 86 comments Joined 11/04

22 Sep 2014

There's no other replacement than IndexStatsV.
What else do you need from the old view?

Dieter

mpatil 3 comments Joined 11/11

23 Sep 2014

I am looking for replacement of column "IndexStatistics" from old view.

dnoeth 86 comments Joined 11/04

23 Sep 2014

There's no replacement.
Dbc.StatsTbl.Histogram holds the binary stats, similar to dbc.Indexes.IndexStatistics, but the internal structure is totally different.

Dieter

teradatauser2 29 comments Joined 04/12

16 Dec 2014

Hi Diether,

We have migrated to TD 14.10 few days back. Previously we had TD 13.10. In our collect stats architecure, we have the below query that fails now saying that DBC.MULTICOLUMNSTATS doesn't exist. Could you please tell us what is the new parallel view/table to use in place of this.

SELECT DATABASENAME,TABLENAME

FROM DBC.INDEXSTATS

WHERE INDEXSTATISTICS IS NOT NULL

UNION

SELECT DATABASENAME,TABLENAME

FROM DBC.MULTICOLUMNSTATS

WHERE COLUMNSSTATISTICS IS NOT NULL

--Samir

teradatauser2 29 comments Joined 04/12

16 Dec 2014

Basically i want to find tables having stats on them - single column, index or multicolumn. below query gives me the o/p , but i want to know if there is any view like in 13.10 (as we used in above query )that has this info :

SELECT db.DATABASENAME,t.TVMName , s.expressionlist

FROM DBC.StatsTbl AS s

JOIN DBC.Dbase AS db

ON s.DatabaseId = db.DatabaseId

JOIN DBC.TVM AS t

ON s.ObjectId = t.TVMId

WHERE ExpressionCount > 1

and db.databasename = 'DBname'

and t.TVMName ='tablename'

teradatauser2 29 comments Joined 04/12

17 Dec 2014

Hi Diether,

I think i got it. I saw your post above regarding this. I just need to check the presence of that databasename and tablename in view dbc.StatsV. This view contains all the columns on which stats has been collected. Please let me know if my understanding is correct.
--Samir

dnoeth 86 comments Joined 11/04

17 Dec 2014

Hi Samir,
yep, this is correct :)

Dieter

brokenp87 2 comments Joined 05/12

16 Jan 2015

Hi Diether,
in our production environment I found out a strange situation that may have revealed a bug of the view.
Maybe you want this behavior, if so, can you explain the reason?
We have a table, let's call it TABLE_A, that has some constraints defined on it in this way:

CONSTRAINT FIELD1 CHECK ( CHKFIELD1 >=  1 ),
CONSTRAINT FIELD2 CHECK ( CHKFIELD2 >=  1 ),
CONSTRAINT FIELD3 CHECK ( CHKFIELD3 >=  1  ),
UNIQUE PRIMARY INDEX ( PIFIELD )
PARTITION BY RANGE_N(PARTITIONFIELD1  BETWEEN DATE '2013-01-01' AND DATE '2033-12-31' EACH INTERVAL '1' MONTH );

The three constraints plus the partition constraint are all stored in the dictionary table DBC.TableConstraints.
In the view the table DBC.TableConstraints is in LEFT JOIN to take the fields DefinedCombinedPartitions and ColumnPartitioningLevel.
Let's imagine that TABLE_A has 4 stats defined.
When doing the left join with DBC.TableConstraints the result is that these 4 stats are joined with the 4 rows inside TableConstraints causing a duplication of the result of the view.
In fact, we have 16 rows in the answerset!
From what I understand you are interested only in the partitioning constraints.
Why not put a filter in the LEFT JOIN with TableConstraints to only make participate in the join the rows that has ConstraintType = 'Q' (that means partitioning constraints)?
Thanks in advance and regards
Giovanni

dnoeth 86 comments Joined 11/04

16 Jan 2015

Hi Giovanni,
that's a bug, it was fixed half a year ago, but I forgot to upload it.
Now it's attached, thanks
Dieter

Dieter

Renji 2 comments Joined 08/05

28 Jan 2015

Hello .. We are on 14.10. The SHOW STATISTICS ON <table> gives all the stats on the table, in a COLLECT STATS syntax. Is there a way to capture this result in a stored procedure ??
We are attempting to compress the tables, and TD doesn't allow compression if there is a stats on the column. So I am attempting to capture the statement, drop all the stats, compress the table and recollect stats using the results saved earlier. If the SHOW STATS results cannot be saved, I guess my option is to select columns from DBC.STATSV and generate COLLECT STATS script.
Thanks .. John Abraham

Regards
John Abraham

dnoeth 86 comments Joined 11/04

31 Jan 2015

Hi John,
to run a SHOW STATS in a SP you might try Glenn McCall's approach:
Running Unsupported Queries from a Stored Procedure

But I woud prefer creating an empty copy of the table.
Then use this to store the stats temporarily using COLLECT STATS FROM.
After compression you simply copy it back.

Dieter

Renji 2 comments Joined 08/05

03 Feb 2015

Thanks Dieter. I will explore the option of calling XSPs. For now, I wrote the code to capture all columns which have stats, then generate a script to collect stats, and executing at the end.
SHOW STATISTICS shows stats for all columns and indices. Even for the Index column, it shows as COLUMN (col name). I know we cannot collect stats on the same column twice as INDEX and also as COLUMN. Assumig the PI is ACCT_NBR, shouldn't there be a difference between collecting the stats as INDEX (ACCT_NBR) vs COLUMN (ACCT_NBR) ?

Regards
John Abraham

Afridi 1 comment Joined 11/13

18 Feb 2015

Hi Dieter,
Great Work..!!! many thanksss..!!

I faced the same issue that the OREPLACE was not working on select view. However simple OREPLACE is function is working. Anyways, I used the non OREPLACE version and its working great.

Id like to know how we would incorporate the missing stats for tables?

thanks.

rs-reply 1 comment Joined 11/12

18 Jun 2015

Hi Dieter, first of all many thanks for your work, we have used a lot your StatInfo view on TD12.
And now, on TD14.10, I cannot create the function Byte2Float, the query ends with this error: "Function BITAND called with an invalid number or type of parameters".
Can you help me? The exect version of TD is 14.10.05.02.

Thanks, bye
Simone

arpitsinha 5 comments Joined 06/15

22 Jul 2015

Hi Dieter,

I have just created all the functions and view (StatsInfo14).
How can I execute the "C" code which you have provided.
"ReverseBytes.c"

Is below error related to this "C" code?

Executed as Single statement. Failed [3706 : 42000] Syntax error: expected something between '(' and the 'IN' keyword.

Elapsed time = 00:00:00.027

STATEMENT 1: Select Statement failed.

Regards, Arpit

Pages

You must sign in to leave a comment.

Search form

Error message

dbc.StatsV vs. StatsInfo TD13/14

Pages