Teradata Export in Eclipse Plugin not using TMODE=Tera

Teradata Studio
Enthusiast

Teradata Export in Eclipse Plugin not using TMODE=Tera

I have been using the Eclipse Plugin successfully in TMODE=TERA since our DBAs threatened me with a cease and desist for using TMODE=ANSI, however, I was recently contacted by our DBAs again with the same request while I was using the Teradata Export table command executed from the Eclipse Plugin table browser.

Is Teradata Export using a different set of connection parameters, and if so, where can they be changed?

thanks for any help.

mark

9 REPLIES
Teradata Employee

Re: Teradata Export in Eclipse Plugin not using TMODE=Tera

Mark, It is set to ANSI in the FastExport table connection, but according to the JDBC driver, "An application may choose to connect in ANSI or TERA mode, but JDBC FastLoad (TYPE=FASTLOAD) always runs in TERA mode".

Teradata Employee

Re: Teradata Export in Eclipse Plugin not using TMODE=Tera

There are two aspects to a JDBC FastLoad connection:

1. The first part is what we call the "application SQL connection", which can be either ANSI mode or TERA mode. It sounds like your DBA is complaining about the application SQL connection when JDBC FastLoad is used by Teradata Studio's Export Table feature.

2. The second part is the FastExport protocol's control connection. The control connection is a separate connection (and separate Teradata Database session) from the application SQL connection. The control connection always uses TERA mode, because that is a requirement of the FastExport wire protocol.

I'd like to understand why your DBA is complaining about your use of ANSI mode. Teradata's official recommendation is that TERA mode be used only for legacy applications that require it, and that ANSI mode be used for new applications.

Enthusiast

Re: Teradata Export in Eclipse Plugin not using TMODE=Tera

Our DBAs believe that ANSI mode queries from JDBC have the potential of locking the entire complex in certain situations, so they have written alerts to identify folks coming in with TMODE=ANSI.

I will setup time with the DBA today to try and better understand how the Eclipse Plugin is working, but would truly like to hear the TD response to their ANSI concern.

Thanks for the help Tom and FGrimmer!

Teradata Employee

Re: Teradata Export in Eclipse Plugin not using TMODE=Tera

Your DBA is concerned about the wrong issue. The potential for a transaction to hold a lock for a long time is not limited to ANSI mode; that can also occur with TERA mode.

Your application's auto-commit setting is the governing factor.

  • With auto-commit set to true (the default), transactions are committed immediately after each SQL request is executed, and locks are released.
  • With auto-commit set to false (not the default), transactions are kept open after each SQL request is executed, and locks are kept. In particular, the locks are kept in both ANSI and in TERA mode.

My guess is that your DBA once encountered someone using BTEQ or SQL Assistant in ANSI mode, who executed a query, and did not commit the transaction, then went to lunch, and locks were held for a long time. In other words, the old "went to lunch with a transaction open" problem.

This problem occurs with BTEQ and SQL Assistant in ANSI mode because they provide behavior that is equivalent to auto-commit off. In other words, BTEQ and SQL Assistant do not automatically commit the transaction in ANSI mode.

That kind of issue is less likely to occur with an application that uses the Teradata JDBC Driver, because the default auto-commit setting is true for a JDBC connection.

Enthusiast

Re: Teradata Export in Eclipse Plugin not using TMODE=Tera

I want to make sure I have the right terminology between the Eclipse Plugin UI and the TD utilities:

1. the 'Extract...' option in the table browser popup menu runs the TD FastLoad utility (default TMODE=TERA)?

2. the 'Teradata Export...' option runs the FastExport utility (default TMODE=ANSI)?

I had been using #2 to leverage the first row header option that was not available in #1's dialog box ... is there a way to configure #1 to print the column headers in the first row?

Teradata Employee

Re: Teradata Export in Eclipse Plugin not using TMODE=Tera

Mark, No. The Extract option comes from Eclipse DTP and does not use FastExport. It inherits the connection from your connection profile, which I am guessing you set TMODE=TERA.

We will look into modifying our Teradata Export (2) to also inherit the TMODE from the connection profile so that any additional SQL requests during the our Teradata Export (FastExport) process use the same TMODE as the user created for the connection profile.

Enthusiast

Re: Teradata Export in Eclipse Plugin not using TMODE=Tera

thanks for the explanation ... I think your suggestion for #2 would be helpful.

is there any way to set #1 to print the column headers in the first row?

Teradata Employee

Re: Teradata Export in Eclipse Plugin not using TMODE=Tera

No. That is why we added our own Teradata Export.

Enthusiast

Re: Teradata Export in Eclipse Plugin not using TMODE=Tera

got it ... can use Python for that. have a great weekend and thanks for all the explanations.