BTEQ and the Commit statement

Tools & Utilities
Enthusiast

BTEQ and the Commit statement

hello everyone, is there a method in BTEQ that I do not need to enter COMMIT after every action.. I have a very big script with full

of commands to perform..  when I do it SQL assistant it works.. and I had to enter all the commit statements... I think it could be something with Ansi or Teradata mode??? would appreciate some guidence..  thks Vince

.LOGON 2/XFAH;

DATABASE PROD_TABLE;

COMMIT WORK;

CREATE SET TABLE TACCT_CLSFN ,NO FALLBACK ,

  NO BEFORE JOURNAL,

  NO AFTER JOURNAL

  (      

   ACCT_CLSFN_CD        SMALLINT NOT NULL,

   ACCT_CLSFN_E_DESC    VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,

   ACCT_CLSFN_F_DESC    VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,

   EFF_DT               DATE NOT NULL,

   EXPY_DT              DATE)

UNIQUE PRIMARY INDEX ( ACCT_CLSFN_CD );

COMMIT WORK;

CREATE SET TABLE TIVR_ACD_NUMBER ,NO FALLBACK ,

  NO BEFORE JOURNAL,

  NO AFTER JOURNAL

  (      

   AUTO_CALL_DIST CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

   EFF_DT               DATE NOT NULL,

   ACD_NUMBER_E_DESC    VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

   ACD_NUMBER_F_DESC    VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

   EXPY_DT              DATE)

UNIQUE PRIMARY INDEX ( AUTO_CALL_DIST, EFF_DT );

COMMIT WORK;

etc...

Tags (1)
7 REPLIES
Teradata Employee

Re: BTEQ and the Commit statement

Transactions are always implicit in ANSI session mode and in Teradata session mode, they can be defined explicitly or implicitly. So you should consider using ANSI mode.
Enthusiast

Re: BTEQ and the Commit statement

DDLs are auto commit. I remember vaguely in one project where I had a unix script , where we switch between btet and ANSI. It was challenging. We segregated the DMLs as per the business requirment. In ANSI, you can think of  stuffing your works with   commit, rollback and savepoint.

You can use:

.set session transaction ANSI

You can also think of BTET.

Cheers,

Enthusiast

Re: BTEQ and the Commit statement

By default in BTEQ every statment is auto commit. untill we specify .set session transaction ANSI before loggin.

By seeing your script you didn't use ANSI mode so it should be defualt BTET and so no commit requreid. Still if it is not happening because of DBS control settings, you may try below -

.SET SESSION TRANSACTION BTET

.LOGON 2/XFAH;

DATABASE PROD_TABLE;

<your remaining script with no commit statments>

--Sri

KVB
Enthusiast

Re: BTEQ and the Commit statement

I dont think you can do that in Teradata as it is implicit.

Junior Contributor

Re: BTEQ and the Commit statement

SQL Assistant automatically adds a COMMIT to each request in an ANSI session (you can easily verify that when you look at DBQL) unless you use .BeginTx/.CommitTx

BTEQ never autocommits, if you don't specify SET SESSION TRANSACTION to set the session mode BTEQ will use the system default mode, which seems to be ANSI in your case.

Possible solutions:

- add COMMIT to each CREATE (you could do a Search & Replace to simplify it)

- use SQL Assistant to submit the script (there's a command line mode, too)

- switch to BTET mode, but as this results in different defaults for SET/MULTISET and [NOT] CASESPECIFIC this might be even more work than adding COMMIT


Enthusiast

Re: BTEQ and the Commit statement

Hi, I have noticed that when using .Net in SQL Assistant and ANSI mode, that a replace view adds a COMMIT WORK to the end of the definition of the view. This then results in a failure if you call that view from another view. This does not occur if using ODBC in ANSI mode.

Are there any settings that can be changed to prevent this COMMIT WORK from being added to the view definition?

I have found this with Teradata SQL Assistant 13.10 and 15.0.  

Teradata Employee

Re: BTEQ and the Commit statement

It sounds like a bug in the Teradata Database.

However as a workaround, you can use the SQLA Transaction statements: .BeginTx , .CommitTx and .RollbackTx .

In this case it will be something like

.BeginTx

Replace View ...

.CommitTx

Refer to the SQL-A on-line help for additional information.