Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 180 181 182 183 184 185 < 186 > 187 188 189 190 191 192 .. 241 >> Next


Refer to Chapter 7, “Queries: The SELECT Statement,” the “WHERE Clause”, for a detailed discussion of the WHERE clause.

Teradata RDBMS for UNIX SQL Reference

8-263
Teradata SQL Syntax Guide

WITH Clause

WITH Clause

The WITH clause of the SELECT statement specifies summary lines and breaks (grouping conditions) that determine how selected results are returned (typically used for subtotals).

Refer to Chapter 7, “Queries: The SELECT Statement,” the “WITH Clause”.

8-264

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

Chapter 9

Advanced SQL

Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

About This Chapter

Caution:

About This Chapter

This chapter covers the following topics:

• Transaction semantics: Operating in ANSI or Teradata mode

• The SQL flagger

• Correlated subqueries

• Referential integrity

• Updatable cursors

• EXPLAIN modifier: complex queries

Users should modify their applications as soon as practicable to operate in ANSI mode. Non-ANSI syntax, where there are ANSI equivalents, may not be supported in future releases.

Teradata RDBMS for UNIX SQL Reference

9-1
Advanced SQL

Transaction Semantics: Operating in ANSI or Teradata Mode

Transaction Semantics: Operating in ANSI or Teradata Mode

You can operate in either of the following modes:

Introduction

• ANSI

• Teradata

Teradata mode allows for compatibility with existing applications, System Defauk although there are a few caveats introduced. Upgraded systems

should set the system default to Teradata mode to provide compatibility for existing users. New customers should set the system default to ANSI.

Independent of the system default, a user can log on to the system and specify the desired mode for the session.

To change to ANSI from Teradata mode (or vice versa), carry out the Session Option: Changing following:

FOR this software . . . USE these commands or options . . . TO switch to this mode . . .
BTEQ .[SET] SESSION TRANSACTION ANSI ANSI
.[SET] SESSION TRANSACTION BTET Teradata
Log off prior to entering this command. The command does not take effect until the next logon. Refer to the Teradata BTEQ Reference for more detail on using BTEQ commands.
Preprocessor2 TRANSACT(ANSI) ANSI
TRANSACT(BTET) TRANSACT(2PC) TRANSACT(COMMIT) Teradata
Refer to Teradata Application Programming With Embedded SQL for C, COBOL, and PL/I for more detail on setting Preprocessor options.

9-2

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

Transaction Semantics: Operating in ANSI or Teradata Mode

FOR this software . . . USE these commands or options . . . TO switch to this mode . . .
CLI set tx_semantics = ‘A’ set tx_semantics to ‘A’ ANSI
set tx_semantics = ‘T’ Teradata
set tx_semantics = ‘D’ Server default
Refer to Teradata Call-Level Interface Version2 for more detail on setting the tx_semantics field.

See also Chapter 2, “Data Handling Fundamentals,” “Session Parameters”, for information about changing modes.

Terminating Transactions ANSI Mode

ANSI mode only recognizes termination of a transaction by the execution of a COMMIT or ABORT/ROLLBACK statement from the application. That is to say that the Teradata RDBMS does not arbitrarily terminate a transaction, unless it is necessary to do so to preserve the integrity of the Teradata RDBMS.

When executing in ANSI mode, errors such as constraint violations on an INSERT or UPDATE statement, will not cause the transaction to be rolled back. Instead just the current request will be rolled back.

In both Teradata and ANSI mode, implementation of ANSI transaction semantics includes the rule that if a session is terminated with an open transaction then any effects of that transaction are removed from the database.

An application-initiated asynchronous abort causes full transaction Terminating Transactions: rollback. Such a request is generated through use of any of the

ANSI & Teradata Mode following:

• CLIv2 abort request

• TDP when the application terminates without proper session cleanup

• BTEQ, with .ABORT

The default transaction mode for a session follows the system lnterfacB Overview default set for that installation. The default mode can be overridden

through use of the session options parcel which is submitted to the Teradata RDBMS with the connect or the logon/run parcel sequence.

Teradata RDBMS for UNIX SQL Reference

9-3
Advanced SQL

Transaction Semantics: Operating in ANSI or Teradata Mode

ANSI and Teradata Mode Restrictions

Session Pool Manager

Two-Phase Commit Protocol

Besides the following restrictions, everything else is allowed in either mode.

Statements not allowed in ANSI mode:

• BEGIN TRANSACTION (BT)

• END TRANSACTION (ET)

Statements not allowed in Teradata mode:

• COMMIT [WORK]

The Session Pool Manager permits setting the Teradata or ANSI mode for pooled sessions.
Previous << 1 .. 180 181 182 183 184 185 < 186 > 187 188 189 190 191 192 .. 241 >> Next