Extracting table and column statistics

The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

Extracting table and column statistics

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 TeradataColumnStatistics.zip
    1. TeradataColumnStatistics.java(Java 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/javac TeradataColumnStatistics.java

$/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

Tags (2)

Re: Extracting table and column statistics

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.

William Miteff

Re: Extracting table and column statistics

In version 14.0 , DBC.StatsV view provide statistics information.

Also "SHOW STATS ON .." command can be use to get COLLECT STATS command.         

Junior Contributor

Re: Extracting table and column statistics

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.



Re: Extracting table and column statistics

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

Junior Contributor

Re: Extracting table and column statistics

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.