I set up a connection to our Teradata Aster cluster, but could not find any option to turn off autocommit. I tried setting it manually on the JDBC Connection Properties area of the connection profile editor, but the software informed me that the property "autocommit" was not supported.
The ">>" button does not seem to submit the query editor contents as a single transaction. I tried both with and without the "BEGIN WORK;" and "END WORK;" statements in the editor text. I also checked the Aster Management Console.
Am I doing something wrong? Without support for autocommit=false, Teradata Studio connections to Aster databases cannot perform explicit multi-step transactions, nor can it make use of TEMP tables (which on Aster are persisted only for the lifespan of a single transaction).
After some more investigation, it does look as though text IS being submitted in a single transaction when the ">>" button is clicked.
However, there is still something weird happening. In ACT (the "native" Aster query tool), the following text works fine:
CREATE TEMP TABLE testme(col1 integer, col2 integer) DISTRIBUTE BY HASH(col1);
INSERT INTO testme values(1,1);
SELECT * FROM testme;
The contents of table "testme" will be returned to the window. In Teradata Studio, the text can be submitted using the ">>" button, but will fail with the error message "[Aster][ASTERJDBCDSII](34) ERROR: relation "testme" does not exist() ROLLBACK performed"
Interestingly, removing the SELECT statement allows the entire batch to run w/o error.
Just to make sure it wasn't a driver issue, I did the following checks:
- Verified that the Teradata Studio embedded driver for Aster was up to date (it's for nCluster 5.10, which is the same version our clusters are on)
- Verified that the text shown above worked properly with another JDBC query tool (SQL Explorer, which is also Eclipse based).
The verdict is: Transactions do appear to be supported, but pulling data from a temp table during one does not seem to be.
This is expected behavior. According to the Aster JDBC folks, the SELECT statement fails because it expects the table to be created. The CREATE TABLE statement has gone through its prepare statement phase but not created yet.