Extracting table and column statistics

Database
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”

Environment

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) )

    LANGUAGE JAVA MODIFIES SQL DATA

    PARAMETER STYLE JAVA

    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)
5 REPLIES
Enthusiast

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.

Dieter

Enthusiast

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:

GRANT CREATE EXTERNAL PROCEDURE ON SQLJ TO DBC WITH GRANT OPTION;

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

Thanks,

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.