All Forums Connectivity
pietro.nardella 7 posts Joined 06/10
21 Apr 2015
GET METADATA FROM JDBC DRIVER (Query NOT performant)

Hi All,
one of my customer sent me a query generate by JDBC driver 15.00.00.20 to get some metadata. the execution of the query is not performant. I analyse the query and find that the where condition on databasename and tablename are write with LIKE (below I report only the where condition part of the query):
    WHERE t.TableKind IN ('O','T','V')
        AND  t.DatabaseName (NOT CASESPECIFIC) LIKE TRIM(TRAILING   FROM 'Q_TC0_KPE_KPI') (NOT CASESPECIFIC)
        AND  t.TableName (NOT CASESPECIFIC) LIKE TRIM(TRAILING    FROM 'RET_ABBINAMENTO_CAMPO') (NOT CASESPECIFIC)

   
With this where condition TERADATA optimization generate not a good plan even if we define the correspondant stats on where condition columns (as you know, if we use like TD cannot use the stats information).
I change the quey simply substituing the LIKE operator with = (equal) operator:
    WHERE t.TableKind IN ('O','T','V')
        AND  t.DatabaseName (NOT CASESPECIFIC) = TRIM(TRAILING   FROM 'Q_TC0_KPE_KPI') (NOT CASESPECIFIC)
        AND  t.TableName (NOT CASESPECIFIC) = TRIM(TRAILING    FROM 'RET_ABBINAMENTO_CAMPO') (NOT CASESPECIFIC)

In this way I can run the query with a very short elapsed (with the original version the query never end because in the plan we have a product join between 2 big spool !).
Is there a way to modify the query generate by JDBC driver ?
Why the driver use LIKE instead of '=' ?
thank you very match.
 

Pietro Nardella pietro.nardella@teradata.com Teradata Italia
pietro.nardella 7 posts Joined 06/10
21 Apr 2015

Hi all,
I forget to report the whole query:
SELECT CAST (NULL AS VARCHAR(30)) AS TABLE_CAT,
    t.DatabaseName AS TABLE_SCHEM,
    t.TableName AS TABLE_NAME,
    c.ColumnName AS COLUMN_NAME,
    CAST ((
    CASE c.ColumnType
        WHEN 'A1' THEN 2003
        WHEN 'AN' THEN 2003
        WHEN 'AT' THEN 92
        WHEN 'BF' THEN -2
        WHEN 'BO' THEN 2004
        WHEN 'BV' THEN -3
        WHEN 'CF' THEN 1
        WHEN 'CO' THEN 2005
        WHEN 'CV' THEN 12
        WHEN 'D' THEN 3
        WHEN 'DA' THEN 91
        WHEN 'F' THEN 6
        WHEN 'GF' THEN 1
        WHEN 'GV' THEN 12
        WHEN 'I1' THEN -6
        WHEN 'I2' THEN 5
        WHEN 'I' THEN 4
        WHEN 'I8' THEN -5
        WHEN 'JN' THEN 1111
        WHEN 'N' THEN 2
        WHEN 'SZ' THEN 93
        WHEN 'TS' THEN 93
        WHEN 'TZ' THEN 92
        WHEN 'UT' THEN (
    CASE u.TypeKind
        WHEN 'D' THEN 2001
        WHEN 'S' THEN 2002 ELSE 1111
    END)
        WHEN 'XM' THEN 2009 ELSE 1111
    END) AS INTEGER) AS DATA_TYPE,
        CAST ((
    CASE c.ColumnType
        WHEN '++' THEN 'TD_ANYTYPE'
        WHEN 'A1' THEN 'SYSUDTLIB.' || TRIM(TRAILING
    FROM c.ColumnUDTName)
        WHEN 'AN' THEN 'SYSUDTLIB.' || TRIM(TRAILING
    FROM c.ColumnUDTName)
        WHEN 'AT' THEN 'TIME'
        WHEN 'BF' THEN 'BYTE'
        WHEN 'BO' THEN 'BLOB'
        WHEN 'BV' THEN 'VARBYTE'
        WHEN 'CF' THEN 'CHAR'
        WHEN 'CO' THEN 'CLOB'
        WHEN 'CV' THEN 'VARCHAR'
        WHEN 'D' THEN 'DECIMAL'
        WHEN 'DA' THEN 'DATE'
        WHEN 'DH' THEN 'INTERVAL DAY TO HOUR'
        WHEN 'DM' THEN 'INTERVAL DAY TO MINUTE'
        WHEN 'DS' THEN 'INTERVAL DAY TO SECOND'
        WHEN 'DY' THEN 'INTERVAL DAY'
        WHEN 'F' THEN 'FLOAT'
        WHEN 'GF' THEN 'GRAPHIC'
        WHEN 'GV' THEN 'VARGRAPHIC'
        WHEN 'HM' THEN 'INTERVAL HOUR TO MINUTE'
        WHEN 'HR' THEN 'INTERVAL HOUR'
        WHEN 'HS' THEN 'INTERVAL HOUR TO SECOND'
        WHEN 'I1' THEN 'BYTEINT'
        WHEN 'I2' THEN 'SMALLINT'
        WHEN 'I' THEN 'INTEGER'
        WHEN 'I8' THEN 'BIGINT'
        WHEN 'JN' THEN 'JSON'
        WHEN 'MI' THEN 'INTERVAL MINUTE'
        WHEN 'MO' THEN 'INTERVAL MONTH'
        WHEN 'MS' THEN 'INTERVAL MINUTE TO SECOND'
        WHEN 'N'  THEN 'NUMBER'
        WHEN 'PD' THEN 'PERIOD(DATE)'
        WHEN 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
        WHEN 'PS' THEN 'PERIOD(TIMESTAMP)'
        WHEN 'PT' THEN 'PERIOD(TIME)'
        WHEN 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
        WHEN 'SC' THEN 'INTERVAL SECOND'
        WHEN 'SZ' THEN 'TIMESTAMP WITH TIME ZONE'
        WHEN 'TS' THEN 'TIMESTAMP'
        WHEN 'TZ' THEN 'TIME WITH TIME ZONE'
        WHEN 'XM' THEN 'XML'
        WHEN 'YM' THEN 'INTERVAL YEAR TO MONTH'
        WHEN 'YR' THEN 'INTERVAL YEAR'
        WHEN 'UT' THEN 'SYSUDTLIB.' || TRIM(TRAILING
    FROM c.ColumnUDTName) ELSE TRIM (c.ColumnType)
    END) AS VARCHAR(500)) AS TYPE_NAME,
        (
    CASE
        WHEN c.COLUMNTYPE = 'UT' AND u.TypeKind = 'D' THEN  CAST ((
    CASE
        WHEN BaseTypes.COLUMNTYPE = 'AT' THEN 15
        WHEN (BaseTypes.COLUMNTYPE = 'CF' OR BaseTypes.COLUMNTYPE = 'CO' OR BaseTypes.COLUMNTYPE = 'CV' OR BaseTypes.COLUMNTYPE = 'GF' OR BaseTypes.COLUMNTYPE = 'GV' OR BaseTypes.COLUMNTYPE = 'JN' OR BaseTypes.COLUMNTYPE = 'XM') AND (c.CHARTYPE = 2 OR c.CHARTYPE = 4) THEN c.ColumnLength / 2
        WHEN BaseTypes.COLUMNTYPE = 'D' THEN c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'DA' THEN 10
        WHEN BaseTypes.COLUMNTYPE = 'DH' THEN 4 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'DM' THEN 7 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'DS' THEN 17 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'DY' THEN 1 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'F'  THEN 15
        WHEN BaseTypes.COLUMNTYPE = 'HM' THEN 4 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'HR' THEN 1 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'HS' THEN 14 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'I1' THEN 3
        WHEN BaseTypes.COLUMNTYPE = 'I2' THEN 5
        WHEN BaseTypes.COLUMNTYPE = 'I'  THEN 10
        WHEN BaseTypes.COLUMNTYPE = 'I8' THEN 19
        WHEN BaseTypes.COLUMNTYPE = 'MI' THEN 1 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'MO' THEN 1 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'MS' THEN 11 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'N'  THEN (
    CASE
        WHEN c.DECIMALTOTALDIGITS = -128 THEN 40 ELSE c.DECIMALTOTALDIGITS
    END)
        WHEN BaseTypes.COLUMNTYPE = 'PD' THEN 28
        WHEN BaseTypes.COLUMNTYPE = 'PM' THEN 72
        WHEN BaseTypes.COLUMNTYPE = 'PS' THEN 60
        WHEN BaseTypes.COLUMNTYPE = 'PT' THEN 38
        WHEN BaseTypes.COLUMNTYPE = 'PZ' THEN 50
        WHEN BaseTypes.COLUMNTYPE = 'SC' THEN 8 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'SZ' THEN 32
        WHEN BaseTypes.COLUMNTYPE = 'TS' THEN 26
        WHEN BaseTypes.COLUMNTYPE = 'TZ' THEN 21
        WHEN BaseTypes.COLUMNTYPE = 'YM' THEN 4 + c.DECIMALTOTALDIGITS
        WHEN BaseTypes.COLUMNTYPE = 'YR' THEN 1 + c.DECIMALTOTALDIGITS ELSE c.ColumnLength
    END) AS INTEGER) ELSE  CAST ((
    CASE
        WHEN c.COLUMNTYPE = 'AT' THEN 15
        WHEN (c.COLUMNTYPE = 'CF' OR c.COLUMNTYPE = 'CO' OR c.COLUMNTYPE = 'CV' OR c.COLUMNTYPE = 'GF' OR c.COLUMNTYPE = 'GV' OR c.COLUMNTYPE = 'JN' OR c.COLUMNTYPE = 'XM') AND (c.CHARTYPE = 2 OR c.CHARTYPE = 4) THEN c.COLUMNLENGTH / 2
        WHEN c.COLUMNTYPE = 'D' THEN c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'DA' THEN 10
        WHEN c.COLUMNTYPE = 'DH' THEN 4 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'DM' THEN 7 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'DS' THEN 17 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'DY' THEN 1 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'F'  THEN 15
        WHEN c.COLUMNTYPE = 'HM' THEN 4 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'HR' THEN 1 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'HS' THEN 14 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'I1' THEN 3
        WHEN c.COLUMNTYPE = 'I2' THEN 5
        WHEN c.COLUMNTYPE = 'I'  THEN 10
        WHEN c.COLUMNTYPE = 'I8' THEN 19
        WHEN c.COLUMNTYPE = 'MI' THEN 1 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'MO' THEN 1 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'MS' THEN 11 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'N'  THEN (
    CASE
        WHEN c.DECIMALTOTALDIGITS = -128 THEN 40 ELSE c.DECIMALTOTALDIGITS
    END)
        WHEN c.COLUMNTYPE = 'PD' THEN 28
        WHEN c.COLUMNTYPE = 'PM' THEN 72
        WHEN c.COLUMNTYPE = 'PS' THEN 60
        WHEN c.COLUMNTYPE = 'PT' THEN 38
        WHEN c.COLUMNTYPE = 'PZ' THEN 50
        WHEN c.COLUMNTYPE = 'SC' THEN 8 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'SZ' THEN 32
        WHEN c.COLUMNTYPE = 'TS' THEN 26
        WHEN c.COLUMNTYPE = 'TZ' THEN 21
        WHEN c.COLUMNTYPE = 'YM' THEN 4 + c.DECIMALTOTALDIGITS
        WHEN c.COLUMNTYPE = 'YR' THEN 1 + c.DECIMALTOTALDIGITS ELSE c.COLUMNLENGTH
    END) AS INTEGER)
    END) AS COLUMN_SIZE,
        CAST (NULL AS INTEGER) AS BUFFER_LENGTH,
        CAST ((
    CASE
        WHEN (c.COLUMNTYPE = 'N' OR c.COLUMNTYPE = 'UT' AND BaseTypes.COLUMNTYPE = 'N') AND c.DECIMALFRACTIONALDIGITS = -128 THEN 0 ELSE c.DECIMALFRACTIONALDIGITS
    END) AS INTEGER) AS DECIMAL_DIGITS,
        CAST (10 AS INTEGER) AS NUM_PREC_RADIX,
        CAST((
    CASE c.Nullable
        WHEN 'Y' THEN 1
        WHEN 'N' THEN 0 ELSE 2
    END) AS INTEGER) AS NULLABLE,
        c.CommentString AS REMARKS,
        c.DefaultValue AS COLUMN_DEF,
        CAST (NULL AS INTEGER) AS SQL_DATA_TYPE,
        CAST (NULL AS INTEGER) AS SQL_DATETIME_SUB,
        CAST (c.ColumnLength AS INTEGER) AS CHAR_OCTET_LENGTH,
        CAST ((ROW_NUMBER () OVER (PARTITION BY TABLE_SCHEM, TABLE_NAME
    ORDER BY c.ColumnID)) AS INTEGER) AS ORDINAL_POSITION,
        TRIM((
    CASE c.Nullable
        WHEN 'Y' THEN 'YES'
        WHEN 'N' THEN 'NO' ELSE ''
    END)) AS IS_NULLABLE,
        CAST (NULL AS VARCHAR(30)) AS SCOPE_CATLOG,
        CAST (NULL AS VARCHAR(30)) AS SCOPE_SCHEMA,
        CAST (NULL AS VARCHAR(30)) AS SCOPE_TABLE,
        CAST((
    CASE c.ColumnType
        WHEN 'UT' THEN (
    CASE u.TypeKind
        WHEN 'D' THEN  CAST ((
    CASE BaseTypes.ColumnType
        WHEN 'A1' THEN 2003
        WHEN 'AN' THEN 2003
        WHEN 'AT' THEN 92
        WHEN 'BF' THEN -2
        WHEN 'BO' THEN 2004
        WHEN 'BV' THEN -3
        WHEN 'CF' THEN 1
        WHEN 'CO' THEN 2005
        WHEN 'CV' THEN 12
        WHEN 'D' THEN 3
        WHEN 'DA' THEN 91
        WHEN 'F' THEN 6
        WHEN 'GF' THEN 1
        WHEN 'GV' THEN 12
        WHEN 'I1' THEN -6
        WHEN 'I2' THEN 5
        WHEN 'I' THEN 4
        WHEN 'I8' THEN -5
        WHEN 'JN' THEN 1111
        WHEN 'N' THEN 2
        WHEN 'SZ' THEN 93
        WHEN 'TS' THEN 93
        WHEN 'TZ' THEN 92
        WHEN 'UT' THEN (
    CASE NULL
        WHEN 'D' THEN 2001
        WHEN 'S' THEN 2002 ELSE 1111
    END)
        WHEN 'XM' THEN 2009 ELSE 1111
    END) AS INTEGER) ELSE NULL
    END) ELSE NULL
    END) AS SMALLINT) AS SOURCE_DATA_TYPE
    
    
    FROM DBC.TABLESV t
    
    JOIN DBC.COLUMNSV c
    ON t.DatabaseName = c.DatabaseName
    AND t.TableName = c.TableName
    
    LEFT OUTER JOIN DBC.UDTInfo u
    ON c.ColumnUDTName = u.TypeName
    
    LEFT OUTER JOIN DBC.COLUMNSV BaseTypes
    ON u.TypeKind = 'D'
    AND 'SYSUDTLIB' = BaseTypes.DatabaseName (NOT CASESPECIFIC)
    AND u.TypeName (NOT CASESPECIFIC) = BaseTypes.TableName (NOT CASESPECIFIC)
    
    
    WHERE t.TableKind IN ('O','T','V')
        AND  t.DatabaseName (NOT CASESPECIFIC) LIKE TRIM(TRAILING   FROM 'Q_TC0_KPE_KPI') (NOT CASESPECIFIC)
        AND  t.TableName (NOT CASESPECIFIC) LIKE TRIM(TRAILING    FROM 'RET_ABBINAMENTO_CAMPO') (NOT CASESPECIFIC)
    ORDER BY TABLE_SCHEM,
        TABLE_NAME,
        ORDINAL_POSITION;

 

Pietro Nardella
pietro.nardella@teradata.com
Teradata Italia

tomnolan 594 posts Joined 01/08
21 Apr 2015

Regarding the question: "Is there a way to modify the query generate by JDBC driver ?"
 
No, there is no may for the user to modify the query that is composed by the Teradata JDBC Driver for the DatabaseMetaData.getColumns method.
However, if you are the application developer, you can choose to compose and execute the Data Dictionary query in your application, rather than calling the DatabaseMetaData.getColumns method.
 
Regarding the question: "Why the driver use LIKE instead of '=' ?"
 
The behavior of the DatabaseMetaData.getColumns method is dictated by the JDBC API Specification.
In particular, the DatabaseMetaData.getColumns method is defined with a pattern arguments, so the application can use the percent-sign % and underscore _ wildcard characters for the LIKE predicate.
We are currently working on a Teradata JDBC Driver enhancement (JDBC RFC 161165) to examine the pattern arguments of the DatabaseMetaData.getColumns method, and compose the query with the equals = operator rather than the LIKE predicate if the pattern argument does not contain percent-sign % or underscore _ wildcard characters.
 

pietro.nardella 7 posts Joined 06/10
22 Apr 2015

Thank you very much for your quick reply. Can you tell me when the JDBC RFC 161165 will be ready ?
regards,
 
 

Pietro Nardella
pietro.nardella@teradata.com
Teradata Italia

tomnolan 594 posts Joined 01/08
22 Apr 2015

Sorry, no, we do not yet have a scheduled availability date for JDBC RFC 161165.

tomnolan 594 posts Joined 01/08
19 Jan 2016

JDBC RFC 161165 shipped to Teradata JDBC Driver 15.00.00.28 and Teradata JDBC Driver 15.10.00.07 on July 2, 2015.
 
However, it's common for database names, table names, and column names to include underscore characters, e.g. "RETAIL_SALES". An underscore character is a LIKE-predicate pattern wildcard character. The JDBC RFC 161165 feature can only use an equal operator ( = ) instead of a LIKE predicate when the search pattern does not include any LIKE-predicate pattern wildcard characters.
 
Therefore, in order to take advantage of the JDBC RFC 161165 feature, your application must either:
1. Change all pattern argument strings to prefix every LIKE-predicate pattern wildcard character with a backslash ( \ ) escape character.
or
2. Use the new feature provided by JDBC RFC 179996 "Connection parameter LITERAL_UNDERSCORE=ON/OFF for DatabaseMetaData pattern".
 
JDBC RFC 179996 shipped to Teradata JDBC Driver 15.10.00.14 on December 11, 2015.
 
Documentation for the LITERAL_UNDERSCORE connection parameter is available here:
http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_LITERAL_UNDERSCORE
 

You must sign in to leave a comment.