Package icon TeradataColumnStatistics.zip5.38 KB

Statistics provides valuable information to the optimizer to make decisions on generating optimal explain plan.  The information about the statistics can be obtained by using the “Help Statistics” command however the displayed data cannot be used to join with other tables.  The article provides a java stored procedure and DDL which extracts the output of "Help Statistics" at Table and Column level and capture the data into tables. 

The captured data in the tables helps with:

  1. Identifying old and stale statistics
  2. Understand the data demographics in more detail
  3. The data could be joined to DBQLObjTBL  for further analysis
  4. The data could be used to generate test data
  5. The data could be exported
  6. Better understand the explain plan there is a detailed explanation of how the statistics are used in Teradata Manual “SQL Request and Transaction Processing”


The code was built and tested on:

  • TD 13.10
  • Linux 64-bit
  • JDK 1.5

Installation & usage

  1. Three files are provided in
    1. code)
    2. TeradataColumnStatistics.sql  (DDL to hold the table and column statistics)
    3. TeradataColumnStatistics.jar(JAR file of the compiled Java code)
  2. Download the above files (always copy or move the TeradataColumnStatistics.jarfile in binary mode).
  3. Make sure that the user has privileges to call SQLJ.INSTALL_JARand CREATE EXTERNAL PROCEDURE.
  4. Create the two table provided in TeradataColumnStatistics.sql
  5. Use bteq to execute the below commands:
    CALL SQLJ.INSTALL_JAR('CJ!/tmp/TeradataColumnStatistics.jar', 'TeradataColumnStatistics', 0); 
    /** Where /tmp is the directory the TeradataColumnStatistics.jar file resides */ 
    REPLACE PROCEDURE TableColumnStatistics
    ( INOUT db VARCHAR(32),INOUT tab VARCHAR(32) )
    EXTERNAL NAME 'TeradataColumnStatistics:TeradataColumnStatistics.main';
  6. To execute the JSP in bteq:
    CALL RETAIL.TableColumnStatistics('RETAIL','ITEM'); 
    /** RETAIL is the database name and ITEM is the table name */
  7. Check the tables Help_Col_Statsand Help_Tab_Statsfor the data contents.

The java code could also be compiled on the Linux box


$/opt/teradata/jvm64/jdk5/bin/jar -cf TeradataColumnStatistics.jar TeradataColumnStatistics.class

Teradata Manual SQL External Routine Programming; Chapter 5 - Java External Stored Procedures discusses more about building and using Java stored procedures

wmmiteff 8 comments Joined 02/11
17 Sep 2013

What about version 14.0 and above? I am getting " [3706] Syntax error: INDEX and COLUMN option not allowed in HELP Statistics. " when i try to use column in a help statistics statement on 14.0.

rahul.gadodia 4 comments Joined 12/09
17 Sep 2013

In version 14.0 , DBC.StatsV view provide statistics information.
Also "SHOW STATS ON .." command can be use to get COLLECT STATS command.         

dnoeth 86 comments Joined 11/04
17 Sep 2013

Hi Ramakrishna,
do you think you can embed this code in a Java Table UDF? 
Then i could directly use it in a SELECT * FROM TABLE(....), would be so cooool.
I'm not a Java programmer, so i don't know if this is easy or complex.


Rohan_Sawant 1 comment Joined 07/14
15 Jan 2015

Hi Dieter,
Can you help me in understanding the privilages.
While running the below:
CALL SQLJ.INSTALL_JAR('CJ!/tmp/TeradataColumnStatistics.jar', 'TeradataColumnStatistics', 0); 
I am getting error for "CALL SQLJ.INSTALL_JAR('CJ!/tmp/TeradataColumnStatistics.jar', 'TeradataColumnStatistics', 0); ":
CALL Failed. 3524:  The user does not have CREATE EXTERNAL PROCEDURE access to database DBC. 
I tried granting rights:
But I am still getting the error.
I am logged in as user DBC.
Please let me know what exacts grants i need to provide.
Thanks in advance
Rohan Sawant

dnoeth 86 comments Joined 11/04
15 Jan 2015

Hi Rohan,
check the error message again :-)
"The user does not have CREATE EXTERNAL PROCEDURE access to database DBC." 
You try to create the function in dbc, so you need the "CREATE EXTERNAL PROCEDURE ON DBC", but this is impossible to grant.
INSTALL_JAR always installs in the current default, so simply change the default database to one where you got the neccessary rights.


You must sign in to leave a comment.