v15.10.3 - commit processing

Teradata Studio
Apprentice

v15.10.3 - commit processing

Hi,

I'm using Studio 15.10.00.03.201506301456 and am having trouble understanding what is happening around the 'commit' of changes.

I am logging on to a TD 15.00 system (15.0.1.6).

My session is set up to TMODE=TERA (confirmed after logging on by running a HELP SESSION command).

I am using the following commands as a test;

set query_band = 'test=tdstudio no autocommit;' for session;

 bt;

 database student101;

 select * from dbc.dbcinfov;

 et;

When running in Teradata mode the "select" statement should fail because it follows a DDL statement, and that is not allowed inside a transaction. This is what happens when using BTEQ - shown below.

 *** Logon successfully completed.

 *** Teradata Database Release is 15.00.01.05                   

set query_band = 'test=tdstudio no autocommit;' for session;

 *** Set QUERY_BAND accepted.

 bt;

 *** Begin transaction accepted.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

 database student101;

 *** New default database accepted.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

 select * from dbc.dbcinfov;

 *** Failure 3932 Only an ET or null statement is legal after a DDL Statemen

 t.

                Statement# 1, Info =0

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

 et;

 *** Failure 3510 Too many END TRANSACTION statements.

                Statement# 1, Info =0

 *** Total elapsed time was 1 second.

However, if I use the same commands in Studio (TMODE=TERA, autocommit is OFF - unticked) I get the following results:

"set query_band" works

"BT" fails with "3932 only et or null statement is valid after a ddl statement"

This is not what I'm expecting. It appears that either Studio or the JDBC driver is starting an explicit transaction - which would explain the error code.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Tags (1)
18 REPLIES
Teradata Employee

Re: v15.10.3 - commit processing

Dave, Because you have unchecked the autocommit option, you need to manually commit (click the commit toolbar button) after you run the 'set query_band' command.

Apprentice

Re: v15.10.3 - commit processing

I don't understand why I should do that.

I have defined the connection as TMODE=TERA - which I understood to be 'Teradata mode'.

In 'Teradata mode' each request (let's start with the SET QUERY_BAND) is a transaction by itself, so the dbms will commit, the application doesn't have to do anything. This is the behaviour as shown by the BTEQ script above.

Or is the jdbc driver doing some additional processing that changes this behaviour?

And if that is the case, why don't I see the results of that [additional processing] in the DBQL row for the SET QUERY_BAND request?

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: v15.10.3 - commit processing

Dave, But you have turned off Autocommit for the connection by unchecking the Autocommit box. 

Apprentice

Re: v15.10.3 - commit processing

Hi Francine,

Thanks. I understand your point, but as far as I was aware this feature ONLY controls functionality in TDStudio - or maybe in the JDBC driver. As far as I'm aware, it doesn't control functionality in the dbms.

My understanding is that IF your session is running in 'Teradata mode' then the dbms will commit changes after every request (in my case the initial SET QUERY_BAND).

This is what happens with BTEQ (i.e. a cliv2 application) as shown in the output from above.

By turning off autocommit, I was expecting the same thing to happen.

If it doesn't then it implies to me that either:

- the dbms is handling jdbc sessions differently from cliv2 sessions, OR

- the jdbc driver (or Studio) is doing something 'under the covers'.

This is what I'm trying to get to the bottom of.

Regards,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: v15.10.3 - commit processing

I second Dave, Studio (or JDBC) is definitely not working as expected.

After switching on DBQL one can easily prove what's actually sent to TD, both "autocommit" and "no autocommit" do unexpected things.

I logged on with a new session both times, this is the sequence from DBQL sent for "no autocommit" and the info from Studio:

BT 

-- this was not in my code

set query_band = 'test=tdstudio no autocommit;' for session; 

STATEMENT 1: SET  completed. 0 rows processed. Elapsed time = 00:00:00.330 


bt;

STATEMENT 2: BEGIN TRANSACTION failed.  Failed [3932 : 25000] Only an ET or null statement is legal after a DDL Statement. 

-- wrong, this rolls back the queryband to NULL



BT

-- this was not in my code



database teradata_education;

STATEMENT 2: DATABASE  completed. 0 rows processed. Elapsed time = 00:00:00.336 

select * from dbc.dbcinfov;

STATEMENT 3: Select Statement failed.  Failed [3932 : 25000] Only an ET or null statement is legal after a DDL Statement. 

BT

-- this was not in my code



et;

STATEMENT 3: ET  completed. 0 rows processed. Elapsed time = 00:00:00.117

As I understand "no autocommit" should simply send the SQL as-is without any additions.

And this for "autocommit":

set query_band = 'test=tdstudio autocommit;' for session;

STATEMENT 1: SET  completed. 0 rows processed. Elapsed time = 00:00:00.305 

Unavailable

STATEMENT 2: BEGIN TRANSACTION completed. 0 rows processed. Elapsed time = 00:00:00.241 

The QueryText is unavailable, but StatementType shows Begin Transaction

ET

-- this was not in my code

database teradata_education;

STATEMENT 3: DATABASE  completed. 0 rows processed. Elapsed time = 00:00:00.048 

select * from dbc.dbcinfov;

STATEMENT 4: Select Statement completed. 3 rows returned. Elapsed time = 00:00:00.119 

et;

STATEMENT 5: ET  failed.  Failed [3510 : 25000] Too many END TRANSACTION statements.

And how is "autocommit" in Teradata mode supposed to work?

Teradata Employee

Re: v15.10.3 - commit processing

To answer Dieter's question about how the Teradata JDBC Driver behaves in TERA mode with autocommit turned off...

First, some background: As part of the wire protocol between the Teradata Database and client interface software (such as CLI, ODBC Driver, JDBC Driver) each message transmitted from the Teradata Database to the client has a bit designated to indicate whether the session has an open transaction or not.

Thus, the client interface software "knows" at all times whether the session has an open transaction or not.

With TMODE=TERA and autocommit off, when the application asks the Teradata JDBC Driver to execute a SQL request, if the session does not have an open transaction yet, then the Teradata JDBC Driver automatically executes a "BT" command before executing the application's SQL request.

Subsequently, with TMODE=TERA and autocommit off, when the application asks the Teradata JDBC Driver to execute another SQL request, and the session already has an open transaction, then the Teradata JDBC Driver won't execute a "BT" command before executing the application's SQL request.

More background: In TERA mode, BT and ET pairs can be nested, and the Teradata Database keeps track of the nesting level. To commit the transaction, ET commands must be repeatedly executed until the nesting level reaches zero. The Teradata wire protocol bit that I mentioned earlier indicates when the nesting level reaches zero.

When the application calls the Connection.commit method in TERA mode, then the Teradata JDBC Driver spins around in a loop, repeatedly executing ET commands until the nesting level reaches zero.

Apprentice

Re: v15.10.3 - commit processing

Hi Tom,

Many thanks for that explanation, at least I now understand what is happening.

Obvious question, is there any way to turn off the 'always execute a BT if no transaction is running' option for the JDBC driver?

This is for a couple of situations:

1) So that I can use Studio (or any JDBC based tool) to correctly test transactions in Teradata mode. At the moment I can't test some scenario's - particularly error ones involving DDL requests. I can test these using Bteq, but it would be nice to test all scenarios in one tool.

2) I teach a number of education courses (for Teradata) and if we cannot turn this feature off then there are a couple of exercises that run which cannot be completed using Studio - and we're trying to move all sql based exercises to Studio.

I can't find a description of the various connection properties anywhere.

- The 'HELP' button on the 'connection properties' dialog screen doesn't appear to do anything,

- the 'help' button on the 'add jdbc property' gives access to the standard studio help information ( which doesn't seem to include this info)

- the Teradata manuals page doesn't seem to have a 'jdbc driver manual'

- a download of the jdbc driver doesn't include anything.

Many thanks,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: v15.10.3 - commit processing

>>> the Teradata manuals page doesn't seem to have a 'jdbc driver manual'

The Teradata JDBC Driver User Guide, and the other available documentation, is published here on Developer Exchange:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/frameset.html

>>> I can't find a description of the various connection properties anywhere

Here is a direct link to the section of the Teradata JDBC Driver User Guide that describes the connection properties:

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

Apprentice

Re: v15.10.3 - commit processing

Hi Tom,

Ah! That'll be why I couldn't find it with the other TD manuals, thanks for the links.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com