Transaction "COMMIT WORK" executed by JDBC Driver

Database
Teradata Employee

Transaction "COMMIT WORK" executed by JDBC Driver

Hi,

 

The "Commit work" which is an ANSI transaction executed by JDBC uses the AWTs.

As far as the number of the column "NumOfActiveAMPs" of DBQLogTbl is counted up, I think the "commit work" needs the number of AWTs.

On the other hand, in case of the TMODE="Teradata" which does not use "Commit work", I can say ,that is, this mode does not use less AWTs than ANSI Mode do?

 

Regards,

quatre

Tags (1)
5 REPLIES
Teradata Employee

Re: Transaction "COMMIT WORK" executed by JDBC Driver

Teradata mode does not operate differently than ANSI mode from the POV of using AWTs. In TMode, an implicit commit work operation is executed after each statement rather than being submitted by the user/application.

 

Not all commit operations require all AMPs. If a transaction operates on data on only one or a few AMPs, the commit for that transaction will be sent only to the AMPs that have participated in the transaction. Eg a single AMP, single row insert will only require a commit on one AMP (plus perhaps another AMP if fallback is specified on the table, or if there is a USI).

Teradata Employee

Re: Transaction "COMMIT WORK" executed by JDBC Driver

Thanks, Todd.

 

Is there any difference of performance in using ANSI or Teradata mode? , though it's recommended using ANSI mode on Teradata JDBC manual.

 

Regards,

Teradata Employee

Re: Transaction "COMMIT WORK" executed by JDBC Driver

For most workloads, the performance difference is not significant between ANSI and TERA mode.

 

With auto-commit turned off, TERA mode requires more network round-trips for the BT and ET commands, whereas ANSI mode only has COMMIT commands.

 

With auto-commit turned on, ANSI mode requires more network round-trips for the COMMIT commands, whereas TERA mode has implicit auto-commit.

Teradata Employee

Re: Transaction "COMMIT WORK" executed by JDBC Driver

In addition to Tom's comments, query explains should be reviewed carefully if changing session mode between the two modes or mixing modes on the same system - eg if DBAs are defining objects in Teradata mode and users are querying in ANSI mode. There are a few tricky differences - like the default for casespecific - that can make a big difference in a query plan.

Teradata Employee

Re: Transaction "COMMIT WORK" executed by JDBC Driver

Hi, all

 

Thank you for all the details!

 

Regards,