The Teradata JDBC Driver's COP Discovery, LCC, Logon, and more

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

The Teradata JDBC Driver Engineering team receives a lot of questions about what happens when a JDBC connection is created. Let's clarify the concepts Laddered Concurrent Connect (LCC) versus COP Discovery versus logon, and review what the Teradata JDBC Driver does to create a JDBC connection.

 

First, some definitions...

 

  • "COP" stands for Communications Processor, and is a term originating from Teradata's earliest database appliances in the 1980s. In modern usage, a "COP" is a Teradata Database node that is running a Teradata Database Gateway process.

 

  • "COP Discovery" refers to the process of performing multiple DNS lookups to identify all the Teradata Database nodes that the client software could potentially connect to.

 

 

It's important to understand that COP Discovery is completely separate from LCC, and both are completely separate from logon.

 

A command-line Java application will typically obtain a JDBC connection from the JDBC DriverManager with code similar to the following.

 

Connection con = DriverManager.getConnection("jdbc:teradata://hostname/TMODE=ANSI,CHARSET=UTF8", username, password);

 

A Java application running in an application server will typically obtain a JDBC connection from a connection pool manager. If the connection pool doesn't already contain a suitable connection, then a new JDBC connection must be created for the application.

 

In either scenario, the Teradata JDBC Driver must perform a series of steps to create the JDBC connection. The steps are described below.

 

Step 1

 

The Teradata JDBC Driver performs COP Discovery first, and produces a two-dimensional array of COP hostnames and their IP addresses.

 

COP Discovery is the process of taking a hostname (really a hostname prefix) and then appending "cop" and a number to the end of the hostname prefix, and verifying whether a DNS lookup indicates that the resulting hostname is defined. The process stops when a DNS lookup failure is encountered. Given a hostname prefix of "teradata", DNS lookups would be attempted with the following pattern.

teradatacop1
teradatacop2
teradatacop3

If a domain suffix is specified for the hostname prefix, then the "cop" and number suffix are applied to the hostname. Given a hostname prefix of "teradata.domain.com", DNS lookups would be attempted with the following pattern.

teradatacop1.domain.com
teradatacop2.domain.com
teradatacop3.domain.com

The Teradata JDBC Driver's default behavior is to perform COP Discovery. To turn off COP Discovery, you must specify the COP=OFF connection parameter. The relevant documentation is located here: http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_COP

 

Some other Teradata client interface products permit a maximum number of COPs to be specified, in order to set an upper bound on the number of DNS lookups. The Teradata JDBC Driver does not offer a feature like that. You cannot specify a COP count for the Teradata JDBC Driver's COP connection parameter.

 

Instead, Teradata JDBC Driver 16.00.00.28 introduced a COPLAST=ON connection parmeter. When COPLAST=ON is specified, and COP Discovery is enabled, then the Teradata JDBC Driver will first perform a DNS lookup for a coplast hostname to obtain the IP address of the last COP hostname before performing COP Discovery. Subsequently, during COP Discovery, the Teradata JDBC Driver will stop searching for COP hostnames when either an unknown COP hostname is encountered, or a COP hostname is encountered whose IP address matches the IP address of the coplast hostname. The relevant documentation is located here: http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_COPL...

 

Each COP hostname can have one or more IP addresses associated with it. The Teradata JDBC Driver's COP Discovery obtains all the IP addresses for all the COP hostnames.

 

The Teradata JDBC Driver creates a two-dimensional array to hold all the IP addresses for all the COP hostnames. Any duplicate IP addresses are omitted from the two-dimensional array.

 

COP Discovery is single-threaded and occurs sequentially.

 

If COP Discovery fails to discover any cop-suffixed hostnames, or if COP Discovery is turned off with the COP=OFF connection parameter, then the Teradata JDBC Driver performs one DNS lookup for the specified hostname, and obtains all the IP addresses associated with that hostname.

 

If a literal IP address was specified as the hostname, then the Teradata JDBC Driver doesn't perform COP Discovery, and simply uses the specified IP address.

 

Step 2

 

Laddered Concurrent Connect (LCC) occurs second, and uses the two-dimensional array as the list of possible IP addresses to choose from. Because duplicate IP addresses are omitted by COP Discovery, the Teradata JDBC Driver will only attempt to connect to each IP address once at most.

 

The purpose of LCC is to make multiple TCP socket connect attempts in parallel.

 

The Teradata JDBC Driver's LCC is multi-threaded, and uses a blocking socket connect method. In contrast, other Teradata client interface products' LCC implementations use a single thread and asynchronous TCP socket operations.

 

When the Teradata JDBC Driver finally makes a successful TCP socket connection, any other TCP socket connection attempts in progress are terminated with a TCP socket close.

 

When a successful TCP socket connection is made, the Teradata JDBC Driver remembers:

  • the original hostname (without COP suffix),
  • the COP hostname that was chosen for the session,
  • and the IP address that was chosen (there could have been several IP addresses for that COP hostname).

 

Step 3

 

Logon is the third step -- using the first successfully-connected TCP socket.

 

It is never the case that multiple logon attempts occur in parallel. Logon is single-threaded.

 

The credentials (username and password) are always encrypted before they are transmitted to the Teradata Database, regardless of the setting of the ENCRYPTDATA connection parameter. The ENCRYPTDATA connection parameter does not control the encryption of credentials; instead, it controls whether encryption is provided for data transmitted to and from the Teradata Database after logon.

 

Step 4

 

After a successful logon, the fourth step is to modify an expired password, if needed. An expired password will be automatically set to a new password if the user's password has expired, and a new password was specified with the NEW_PASSWORD connection parameter.

 

The relevant documentation is located here: http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_NEW_...

 

Step 5

 

If the logged-on user has a startup string, and the RUNSTARTUP=ON connection parameter was specified, then the Teradata JDBC Driver will execute the user's startup string.

 

The relevant documentation is located here:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_RUNS...

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGBHBDAB

 

Step 6

 

If the DATABASE connection parameter was specified, then the current database will be switched to the specified database.

 

The relevant documentation is located here: http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_DATA...

 

Step 7

 

The new JDBC connection is returned to the application.

 

 

4 Comments
Enthusiast
Hi Tom,
such a lightening paper is nice everytime !
So, can we say that COP discovery is not a mandatory step for a JDBC connection and COP = OFF leads to a DNS lookup ?
what about the connection pool: we experiment situations with a lot of "responding" sessions coming from a pool and staying in state for hours or days. When asked about thoses cases end-users say that all is over ...
About the TMODE option, ANSI or TERADATA, i use to recommend the choice of TERADATA (for implicit COMMIT) but i need arguments ...
Thanks for advice,
Pierre
Teradata Employee

can we say that COP discovery is not a mandatory step for a JDBC connection?

COP Discovery is not mandatory for a JDBC connection. However, whether or not COP Discovery is needed depends on how DNS is configured at a site.

can we say that ... COP = OFF leads to a DNS lookup ?

If the COP=OFF connection parameter is specified, one DNS lookup will be performed for the Teradata Database hostname. The only time that no DNS lookup is performed is when a literal IP addresses is specified as the Teradata Database hostname.

what about the connection pool: we experiment situations with a lot of "responding" sessions coming from a pool and staying in state for hours or days. When asked about thoses cases end-users say that all is over

It is normal for a session to be in RESPONSE state. RESPONSE state is not a bad situation; it simply means that a session has one or more response spools open. A session in RESPONSE state will not block other sessions because of the RESPONSE state.

If the customer is concerned about spool space being consumed by sessions in RESPONSE state for a long time, then the customer should ensure that their applications call ResultSet.close, Statement.close, Blob.free, and/or Clob.free in a more timely manner. The customer can follow the recommendations in the "Response Limit Exceeded Error" section of the Teradata JDBC Driver user Guide.

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_5.html#CHDGCHBB

The customer should also ensure that they are using a version of the Teradata JDBC Driver that contains the fix for: JDBC DR 163433 "ResultSet.close and Statement.close may not always close response spools spanning multiple messages".

Teradata JDBC Driver 13.00.00.30 and later

Teradata JDBC Driver 13.10.00.33 and later

Teradata JDBC Driver 14.00.00.34 and later

Teradata JDBC Driver 14.10.00.14 and later

About the TMODE option, ANSI or TERADATA, i use to recommend the choice of TERADATA (for implicit COMMIT) but i need arguments

The Teradata Database SQL Reference/Statement and Transaction Processing recommends that ANSI transaction mode be used for all new applications. The primary benefit of using TMODE=ANSI (ANSI transaction mode) is that inadvertent data truncation is avoided. In contrast, when using TERA transaction mode, silent data truncation can occur when data is inserted, because silent data truncation is a feature of TERA transaction mode. For more information, please refer to the Teradata JDBC Driver FAQ.

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/faq.html#q4

Enthusiast

Very helpful post!

Is there a scenario missing in Step 1 where the DNS name provided is a valid name with no additional COP entries?  It appears to work with the one IP address it finds, but I want to make sure I understand what it is actually doing.

Example 1:  Have DNS entry of "teradata" with no COP entries and specify it as JDBC hostname

Result:  JDBC will use the IP of "teradata" even if it finds no COP entries for it

Example 2:  Have DNS entry of "teradatacop2" and specify it as JDBC hostname

Result: JDBC will use this IP only and none of the other COP entries like "teradatacop1"  (because, similar to example 1 above, it will append COP1 to the hostname and find no matches to "teradatacop2cop1" in DNS).

Also, digging through other documentation and posts, it appears that ALL TD clients (ODBC, JDBC, native CLI based clients like BTEQ and TPT) use the COP algorithm.  Is that correct?

Teradata Employee

You were correct regarding the missing scenario in Step 1, so I revised Step 1 to say "If COP Discovery fails to discover any cop-suffixed hostnames, or if COP Discovery is turned off with the COP=OFF connection parameter, then the Teradata JDBC Driver performs one DNS lookup for the specified hostname, and obtains all the IP addresses associated with that hostname."

You are also correct that all Teradata client software provides COP Discovery.