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:
The code was built and tested on:
TeradataColumnStatistics.sql(DDL to hold the table and column statistics)
TeradataColumnStatistics.jar(JAR file of the compiled Java code)
TeradataColumnStatistics.jarfile in binary mode).
CREATE EXTERNAL PROCEDURE.
.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
/** RETAIL is the database name and ITEM is the table name */
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
What about version 14.0 and above? I am getting "  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.
In version 14.0 , DBC.StatsV view provide statistics information.
Also "SHOW STATS ON .." command can be use to get COLLECT STATS command.
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.
Can you help me in understanding the privilages.
While running the below:
I am getting error for "CALL SQLJ.INSTALL_JAR('CJ!/tmp/TeradataColumnStatistics
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
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.