Archives of the TeradataForum

Message Posted: Fri, 27 Jun 2014 @ 16:44:58 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Anomy.Anom wrote:

How to use COLLECT DEMOGRAPHICS:

You don't need any special access right (beside SELECT on the base table) to submit a COLLECT DEMPGRAPHICS.

But the result is inserted into the DataDemographics table, which is part of the Query Capture Database (QCD) used by Visual Explain. And this might not exist on your system or you don't have rights on it.

Now the good news: you only need this specific table and nothing else. Thus you can create it anywhere, it even works when it's created as a Volatile table. This definition is from the "SQL Request and Transaction Processing" manual (I just modified the PI, but it's still working):

     CREATE TABLE DataDemographics (
         MachineName VARCHAR(30) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
         TABLENAME VARCHAR(128) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
         DatabaseName VARCHAR(128) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
         DBSize INTEGER NOT NULL,
         CollectedTime TIMESTAMP(6) NOT NULL,
         AMPNumber INTEGER NOT NULL,
         ClusterNumber INTEGER NOT NULL,
         SubTableID SMALLINT NOT NULL,
         SubTableType VARCHAR(120) CHARACTER SET LATIN,
         RowCount DECIMAL(18,0) NOT NULL,
         AvgRowSize INTEGER NOT NULL,
         QueryID INTEGER,
         IndexName VARCHAR(128) CHAR SET UNICODE UPPERCASE NOT CASESPECIFIC,
         DemographicsID INTEGER)
     PRIMARY INDEX (DatabaseName, TABLENAME);

Then it's

     COLLECT DEMOGRAPHICS FOR tab INTO DB_where_CollectDemographics_was_created;

The syntax in the manuals is a bit misleading, a comma-delimited list of tables is allowed, but no enclosing parentheses.

Any existing rows for "tab" are automatically deleted before the new info is inserted.

Finally this gets the approximate size:

     SELECT
         dd.DatabaseName
         ,dd.TABLENAME
         ,dd.SubTableId
         ,dd.SubTableType
         ,dd.IndexName
         ,CASE WHEN t.ProtectionType = 'F' THEN 2 ELSE 2 END * dd.SubTableSize AS SubTableSize
         ,dd.SubTablePercent
         ,CASE WHEN t.ProtectionType = 'F' THEN 2 ELSE 2 END * dd.CurrentPerm AS TableSize
         ,dd.SkewFactor
         ,dd.SkewFactorWinDDI
         ,t.ProtectionType
     FROM
       (
         SELECT
            DatabaseName
            ,TABLENAME
            ,SubTableId
            ,SubTableType
            ,MIN(IndexName) AS IndexName
            ,SUM(RowCount * (AvgRowSize + 0.5)) AS SubTableSize
            ,100 * SubTableSize/NULLIF(CurrentPerm,0) AS SubTablePercent
            ,SUM(SubTableSize) OVER (PARTITION BY DatabaseName, TABLENAME) AS CurrentPerm
         FROM DataDemographics AS dd
         GROUP BY 1,2,3,4
       ) AS dd
     JOIN dbc.TablesV AS t
        ON dd.DatabaseName = t.DatabaseName
       AND dd.TABLENAME = t.TABLENAME
     ORDER BY 1,2,3;

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>