Teradata JDBC Driver User Guide Chapter 6

Capabilities

The following table lists database capabilities, and the corresponding support by the Teradata JDBC Driver.

Topic	Answer
Catalogs
What is the maximum length for a catalog name?	The database does not support catalogs.
Can a catalog name appear at the start of a qualified table name?	No, the database does not support catalogs.
Characters
How does the database treat mixed-case quoted and unquoted SQL identifiers?	Stored with case preserved, but compared case-insensitive.
What characters may be used in unquoted identifier names, other than uppercase A-Z, lowercase a-z, decimal digits 0-9, and underscore _ ?	# and $
How many hex characters are allowed in an inline binary literal?	62000
What characters are used to quote SQL identifiers?	Double-quote characters: " "
What is the separator character between the schema name (database name) and table name?	The period character: .
Character Literals
What is the maximum length for a character literal?	31000 characters
Columns
What is the maximum length of a column name?	This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
Can columns be defined as non-nullable?	Yes
Is column aliasing supported?	Yes
Database Connections
How many database connections can be active at the same time?	The Teradata JDBC Driver does not impose a limit. The database does impose limits on the number of sessions. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
GROUP BY Clauses
Is some form of the GROUP BY clause supported?	Yes
Can a GROUP BY clause: Use column not in the SELECT statement? Add columns not in the SELECT statement, provided it specifies all the column that are in the SELECT statement?	Yes Yes
What is the maximum number of columns in a GROUP BY clause?	Unlimited
Indexes
What is the maximum number of columns allowed in an index?	64
What is the maximum length of an index?	64000 bytes
Joins
Are full nested outer joins supported?	Yes
LIKE Clause Patterns
Is the escape character supported in LIKE clauses?	Yes
What is the string that can be used to escape ‘_’ or ‘%’ in the string pattern style catalog search parameters?	\ (backslash)
Multiple Result Sets
Are multiple results from a single execute supported?	Yes
NULL Values
Are concatenations between NULL values and non-NULL values themselves NULL?	Yes
Are NULL values sorted at the end, regardless of sort order?	No
Are NULL values sorted at the start, regardless of the sort order?	No
Are NULL values sorted high?	No
Are NULL values sorted low?	Yes
ORDER BY Clauses
Are expressions in ORDER BY lists supported?	Yes
What is the maximum number of columns in an ORDER BY clause?	Unlimited
Can an ORDER BY clause use columns not in the SELECT statement?	Yes
Procedures
What is the maximum length of a procedure name?	This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
Are stored procedure calls using the stored procedure escape syntax supported?	Yes
Rows
What is the maximum length of a single row?	This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
Does maximum row size include LOBs?	No
Schemas
What is the maximum length of a schema name?	A schema is equivalent to a database. This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
Can a schema name be used in: A data manipulation statement? An index definition statement? A privilege definition statement? A procedure call statement? A macro call statement A table definition statement?	Yes Yes Yes (GRANT) Yes Yes Yes
SELECT Statements
What is the maximum number of columns in a SELECT list?	This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
What is the maximum number of tables in a SELECT?	This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
SQL Statements
What is the maximum length of an SQL statement?	This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
How many active statements can be open to the same database at the same time?	16 per session
Can statements remain open across: Commits? Rollbacks?	Yes Yes
Are the following levels of the ANSI92 SQL grammar supported: Entry? Intermediate? Full?	Yes Not fully Not fully
Is the following ODBC SQL grammar supported: Minimum? Core? Extended	Yes Yes, except referential integrity No
Is the SQL Integrity Enhancement Facility supported?	No
Is SQL: UNION supported? UNION ALL supported?	Yes Yes
Are USING clauses supported?	No
Are named parameter markers supported?	No
Are unnamed question-mark parameter markers supported?	Yes
Are updates made to a LOB made on a copy or directly to the LOB?	Updates to a LOB are made on a copy
Subqueries
Are correlated subqueries supported?	Yes
Are subqueries supported in: Comparison expressions? ‘EXISTS’ expressions? ‘IN’ expressions? Quantified expressions?	Yes Yes Yes Yes
User Names
What is the maximum length of a user name?	This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
Tables
What is the maximum length of a table name?	This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
What is the maximum number of columns in a table?	This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.
Is ALTER TABLE supported: With add column? With drop column?	Yes Yes
Are table correlation names supported?	Yes
When table correlation names are supported, must they be different from the names of the tables?	Yes
Teradata Terminology
What is the Teradata term for: Catalog? Schema? Procedure?	Catalogs are not supported by the database. Database Procedure
Transactions
Are transactions supported?	Yes
Can multiple transactions on different connections be open at the same time?	Yes
What is the database default transaction isolation level?	TRANSACTION_SERIALIZABLE
Does the database support the given transaction isolation level?	TRANSACTION_READ_UNCOMMITTED - Yes TRANSACTION_READ_COMMITTED - No TRANSACTION_REPEATABLE_READ - No TRANSACTION_SERIALIZABLE - Yes
Does data definition cause transaction commit?	No
Is data definition ignored in transactions?	No
Are both data definition and data manipulation statements within a transaction supported?	Yes, but the data definition is allowed but only as the last statement in a transaction.
Are data manipulation statements supported only within a transaction?	No

Topic

Answer

Catalogs

What is the maximum length for a catalog name?

The database does not support catalogs.

Can a catalog name appear at the start of a qualified table name?

No, the database does not support catalogs.

Characters

How does the database treat mixed-case quoted and unquoted SQL identifiers?

Stored with case preserved, but compared case-insensitive.

What characters may be used in unquoted identifier names, other than uppercase A-Z, lowercase a-z, decimal digits 0-9, and underscore _ ?

# and $

How many hex characters are allowed in an inline binary literal?

62000

What characters are used to quote SQL identifiers?

Double-quote characters: " "

What is the separator character between the schema name (database name) and table name?

The period character: .

Character Literals

What is the maximum length for a character literal?

31000 characters

Columns

What is the maximum length of a column name?

This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

Can columns be defined as non-nullable?

Yes

Is column aliasing supported?

Yes

Database Connections

How many database connections can be active at the same time?

The Teradata JDBC Driver does not impose a limit. The database does impose limits on the number of sessions. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

GROUP BY Clauses

Is some form of the GROUP BY clause supported?

Yes

Can a GROUP BY clause:

Use column not in the SELECT statement?
Add columns not in the SELECT statement, provided it specifies all the column that are in the SELECT statement?

What is the maximum number of columns in a GROUP BY clause?

Unlimited

Indexes

What is the maximum number of columns allowed in an index?

What is the maximum length of an index?

64000 bytes

Joins

Are full nested outer joins supported?

Yes

LIKE Clause Patterns

Is the escape character supported in LIKE clauses?

Yes

What is the string that can be used to escape ‘_’ or ‘%’ in the string pattern style catalog search parameters?

\ (backslash)

Multiple Result Sets

Are multiple results from a single execute supported?

Yes

NULL Values

Are concatenations between NULL values and non-NULL values themselves NULL?

Yes

Are NULL values sorted at the end, regardless of sort order?

Are NULL values sorted at the start, regardless of the sort order?

Are NULL values sorted high?

Are NULL values sorted low?

Yes

ORDER BY Clauses

Are expressions in ORDER BY lists supported?

Yes

What is the maximum number of columns in an ORDER BY clause?

Unlimited

Can an ORDER BY clause use columns not in the SELECT statement?

Yes

Procedures

What is the maximum length of a procedure name?

This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

Are stored procedure calls using the stored procedure escape syntax supported?

Yes

Rows

What is the maximum length of a single row?

This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

Does maximum row size include LOBs?

Schemas

What is the maximum length of a schema name?

A schema is equivalent to a database. This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

Can a schema name be used in:

A data manipulation statement?
An index definition statement?
A privilege definition statement?
A procedure call statement?
A macro call statement
A table definition statement?

Yes
Yes
Yes (GRANT)
Yes
Yes
Yes

SELECT Statements

What is the maximum number of columns in a SELECT list?

This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

What is the maximum number of tables in a SELECT?

This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

SQL Statements

What is the maximum length of an SQL statement?

This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

How many active statements can be open to the same database at the same time?

16 per session

Can statements remain open across:

Commits?
Rollbacks?

Are the following levels of the ANSI92 SQL grammar supported:

Entry?
Intermediate?
Full?

Yes
Not fully
Not fully

Is the following ODBC SQL grammar supported:

Minimum?
Core?
Extended

Yes
Yes, except referential integrity
No

Is the SQL Integrity Enhancement Facility supported?

Is SQL:

UNION supported?
UNION ALL supported?

Are USING clauses supported?

Are named parameter markers supported?

Are unnamed question-mark parameter markers supported?

Yes

Are updates made to a LOB made on a copy or directly to the LOB?

Updates to a LOB are made on a copy

Subqueries

Are correlated subqueries supported?

Yes

Are subqueries supported in:

Comparison expressions?
‘EXISTS’ expressions?
‘IN’ expressions?
Quantified expressions?

User Names

What is the maximum length of a user name?

This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

Tables

What is the maximum length of a table name?

This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

What is the maximum number of columns in a table?

This limit depends on the database release. Please refer to the Teradata Vantage™ - Database Design reference, section Teradata System Limits.

Is ALTER TABLE supported:

With add column?
With drop column?

Are table correlation names supported?

Yes

When table correlation names are supported, must they be different from the names of the tables?

Yes

Teradata Terminology

What is the Teradata term for:

Catalog?
Schema?
Procedure?

Catalogs are not supported by the database.
Database
Procedure

Transactions

Are transactions supported?

Yes

Can multiple transactions on different connections be open at the same time?

Yes

What is the database default transaction isolation level?

TRANSACTION_SERIALIZABLE

Does the database support the given transaction isolation level?

TRANSACTION_READ_UNCOMMITTED - Yes

TRANSACTION_READ_COMMITTED - No

TRANSACTION_REPEATABLE_READ - No

TRANSACTION_SERIALIZABLE - Yes

Does data definition cause transaction commit?

Is data definition ignored in transactions?

Are both data definition and data manipulation statements within a transaction supported?

Yes, but the data definition is allowed but only as the last statement in a transaction.

Are data manipulation statements supported only within a transaction?