All Forums Teradata Studio
DaveWellman 66 posts Joined 01/06
12 Jul 2015
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
fgrimmer 553 posts Joined 04/09
14 Jul 2015

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.

DaveWellman 66 posts Joined 01/06
15 Jul 2015

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

fgrimmer 553 posts Joined 04/09
15 Jul 2015

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

DaveWellman 66 posts Joined 01/06
16 Jul 2015

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

dnoeth 4628 posts Joined 11/04
16 Jul 2015

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?

 

Dieter

tomnolan 594 posts Joined 01/08
20 Jul 2015

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.
 

DaveWellman 66 posts Joined 01/06
23 Jul 2015

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

tomnolan 594 posts Joined 01/08
23 Jul 2015

>>> 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
 

DaveWellman 66 posts Joined 01/06
26 Jul 2015

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

tomnolan 594 posts Joined 01/08
28 Jul 2015

>>> correctly test transactions in Teradata mode. At the moment I can't test some scenarios - particularly error ones involving DDL requests.
>>> a couple of exercises that run which cannot be completed
 
Please provide these scenarios and exercises, so we can recommend a solution for you. Thanks.

DaveWellman 66 posts Joined 01/06
06 Aug 2015

Hi Tom,
Sorry, only just saw your last post.
Here is one exercise that we run as part of one of the Teradata Education courses. This works fine with a CLI application (such as BTEQ) but doesn't have the desired outcome when run through TDStudio (because it is using the jdbc driver which inserts additional comands).
The commands that the students are asked to enter are;
EXPLAIN DEL Employee;  
BT;
EXPLAIN DEL Employee;
EXPLAIN ET;
ET;
EXPLAIN BT; ET;
CREATE TABLE t01 (x int, y int);   
BT;
INS t01 (1, 2);    
DATABASE Customer_Service;   
SELECT * FROM Employee;   
ET;  
 
This breaks down into four parts as shown below. I have also shown th eoutomce of my testing with/without autocommit.
Exercise - part#1
EXPLAIN DEL Employee;  <== must have autocommit=on to see 'send the end transaction'

Exercise - part#2
- with autocommit=on (exercise fails)
BT;
EXPLAIN DEL Employee;  <== this shows a 'send end transaction'
EXPLAIN ET;
ET; <== fails with TD3510

- with autocommit=off (exercise works)
BT;
EXPLAIN DEL Employee;  <== this shows a 'send end transaction'
EXPLAIN ET;
ET;

Exercise - part#3
EXPLAIN BT; ET;  <=== this fails with autocommit=on if run as indivdual statements
   - with autocommit=on this works if you 'send as single statement'

Exercise - part#4
CREATE TABLE t01 (x int, y int);   

BT;
INS t01 (1, 2);    
DATABASE Customer_Service;   
SELECT * FROM Employee;   
ET;    

- with autocommit=on the ET command fails with TD3510 error
- with autocommit=off
   BT fails with TD3932
   INS fails with TD3807 (because the CREATE TABLE was rolled back)
   DATABASE command works
   SELECT fails with TD3932
   ET works
 
From my testing it looks like the student would have to switch autocommit on/off throughout the exercise. (Maybe that is what now needs to be done).
Cheers,
Dave

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

tomnolan 594 posts Joined 01/08
07 Aug 2015

>>> the student would have to switch autocommit on/off throughout the exercise.
 
Yes, that makes sense. Using Studio with the Teradata JDBC Driver is different from using BTEQ. Consider the goal of what your exercise is trying to teach, and determine how best to teach that concept using Studio, which behaves differently from BTEQ.
 
Here are my suggested modifications to your execrcise:
 
(Turn on auto-commit)
EXPLAIN DEL Employee;
(Turn off auto-commit)
(With auto-commit turned off, executing a SQL comment will cause the Teradata JDBC Driver to automatically execute a BT command before executing the SQL comment.)
/*begin*/;
EXPLAIN DEL Employee;
EXPLAIN ET;
ET;
(Turn on auto-commit)
EXPLAIN BT; ET;
CREATE TABLE t01 (x int, y int);
(Turn off auto-commit)
(With auto-commit turned off, executing a SQL comment will cause the Teradata JDBC Driver to automatically execute a BT command before executing the SQL comment.)
/*begin*/;
INS t01 (1, 2);
DATABASE Customer_Service;
SELECT * FROM Employee;
ET;
 

DaveWellman 66 posts Joined 01/06
07 Aug 2015

Hi Tom,
Ok, thanks for the confirmation, I guess we'll have to look at the exercises we use if we're moving to using Studio as our 'standard' - or t least if more students are likely to be using it.
 
I have to ask the following question: Why does the JDBC driver exhibit this behaviour?  I assume it is because the driver is confirming to a 'standard' of some kind. If so, could you tell me which one.
Cheers,
Dave

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

tomnolan 594 posts Joined 01/08
10 Aug 2015

Yes, the Teradata JDBC Driver conforms to the JDBC API Specification, as closely as we are able to.
 
When auto-commit is turned on, a JDBC Driver is expected to commit each SQL request as it is executed.
 
When auto-commit is turned off, a JDBC Driver is expected to leave the current transaction open after each SQL request is executed, and the application is responsible for committing or rolling back the transaction by calling the Connection.commit or the Connection.rollback method.
 
Strictly speaking, an application is not supposed to use vendor-specific transaction management commands such as BT, ET, ABORT, COMMIT, or ROLLBACK, because those kind of commands differ from one vendor to another. (Note that they even differ between Teradata's two modes ANSI and TERA.) Instead, an application is expected to call the standard JDBC API methods Connection.commit and Connection.rollback for transaction management.
 

DaveWellman 66 posts Joined 01/06
10 Aug 2015

Hi Tom,
Many thanks for that, now I undertsnad what is happening we can work out how to adjust the exercises when using Studio.
Cheers,
Dave

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

PatrickA 2 posts Joined 07/09
15 Jan 2016

Hi Francine,
as BT is sent by JDBC driver automatically when autocmmit checkbox is unchecked, is there a way to disable/hide  this button in studio ?
or a pop-up box  to warn the customer about his transaction mode each time he run a query???
if a user want to deal with a transaction it should write BT,ET by himself... as it is with our old ...but easy sql assistant.
I faced many difficulties in customer site where studio have been deployed to many user ( old oracle /toad users) that are requesting directly tables without  any access lock... ==>they frequently  lock production table  and so batch processing genereting lot of frustation (this DB is worst than the older....) it make me angry.... :-(
Any help should be really apreciated!
Cheers.
Patrick
 
 

fgrimmer 553 posts Joined 04/09
15 Jan 2016

Patrick, By default, autocommit is checked. A user has to manually uncheck this box to turn off autocommit. There is not a way to disable/hide the button at this time. I will open a JIRA to request a preference to hide the option or pop up a warning message. 

PatrickA 2 posts Joined 07/09
18 Jan 2016

Great!
Thanks a lot Francine.
Cheers.
Patrick

You must sign in to leave a comment.