GET METADATA FROM JDBC DRIVER (Query NOT performant)

[pietro_nardella]

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]

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;

[tomnolan]

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]

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

regards,

[tomnolan]

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

[tomnolan]

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_LITE...