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

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 194 195 196 197 198 199 < 200 > 201 202 203 204 205 206 .. 241 >> Next

In general, DML (data manipulation language) of Teradata SQL and DB2 is compatible, but DDL (data definition language) is not compatible. This is because DB2’s physical structures, such as TABLESPACE, STOGROUP, and BUFFERPOOL, do not map to Teradata’s physical structures at all.

DB2 (Database 2) is IBM’s relational database management system for large mainframe computers under MVS/SP, MVS/XA, and MVS/ESA. It was first commercially released in 1983.

SQL/DS (Structured Query Language/Data System was IBM’s first commercially released Relational Database Management System (January 1981). It originally was used to access databases via VM/SP, DOS/VSE, and CICS/DOS/VS environments.

If you are writing your own application, refer to the Teradata Application Programming With Embedded SQL for C, Cobol, and PL/I. The Preprocessor2 translates embedded SQL. Its routines support calls from applications to the Teradata RDBMS (at runtime).

If you already have a DB2 application on site and need to access the Teradata RDBMS via the DB2 application, refer to the Teradata TS/API User’s Guide, which currently supports a range of DB2 releases, from DB2 Version 1 Release 2 to Version 3.0.

Teradata RDBMS for UNIX SQL Reference

Comparing Teradata SQL and DB2

Teradata SQL and DB2 Compatibilities

Keywords, Syntax, and Function

Data Types

The following SQL statements have broadly equivalent keywords, syntax, and function in Teradata SQL and DB2:


A Teradata SQL user who is familiar with DB2 can use most DB2 constructs when entering the above statements. Refer also to “Reserved Words Comparison.”

Teradata SQL and DB2 support the following data types:

• DECIMAL(n,m)

• NUMERIC(n,m)




• CHAR(n)









Teradata RDBMS for UNIX SQL Reference
Comparing Teradata SQL and DB2

DB2 and Teradata SQL Differences

DB2 and Teradata SQL Differences

StatlC Versus DynamlC Teradata SQL does not support the Static SQL concept but DB2

SQL does. DB2 uses a precompiler which translates SQL and contains

requests from the database in an optimized format. A bind utility then binds the application to a DB2 application plan, i.e., paths are optimized statically. Binding is the process whereby a precompiler output is converted to a usable control structure called a plan. Access paths to data are established and some authorization checking is performed.

Teradata SQL statements are optimized each time they are run, except when a macro is run repeatedly. In this case the macro is optimized and cached, i.e., it is run from an optimized cache.

Teradata’s PreProcessor2 dynamically prepares Teradata SQL by translating and storing.

In DB2, a unit of work is defined as an entity capable of being Unit Of Work committed or rolled back The first unit of work is the work done

from the first access to a relational database until a COMMIT or ROLLBACK occurs. This is available with the Teradata RDBMS in ANSI mode.

In Teradata mode, a unit of work is defined as an entity that falls between a BEGIN TRANSACTION (BT) command and en END TRANSACTION (ET) command. If no BT and ET commands are present, the Teradata RDBMS treats each SQL statement as a unit of work.

Physical Database Structure

Reserved Words in DB2

The DATABASE, TABLESPACE, BUFFERSPOOL, and STOGROUP objects of DB2 are not supported in Teradata SQL.

The list of Reserved Words differ in DB2 and Teradata SQL. Refer to the section later in this appendix titled, “Reserved Words Comparison.”

There are no DB2 expressions not supported by Teradata SQL. A DB2 Expressions DB2 expression is defined as an operand or a collection of operators

and operands that yield a single value. DB2 expressions are:

• Arithmetic Operators

• Concatenation Operators

• Datetime Operands

Teradata RDBMS for UNIX SQL Reference C-3
Comparing Teradata SQL and DB2

DB2 and Teradata SQL Differences

What is a Database?

Data Dictionary and System Catalog Structures

Hex String Formats

Duplicate Rows in DB2

• Decimal Operands

• Floating Point Operands

• Integer Operands

• Subselect Statements

Teradata SQL Expressions are divided into the following:

• Arithmetic (Operators, Functions, Aggregate Operators)

• Character String Expressions

• Logical (Comparison, [NOT] IN Operator, IS [NOT] NULL Operator, and LIKE Partial-String Operator)

• Conditional Expressions

• Built-In Values (DATE, TIME, USER, NULL)

• Indicator Variables

Both Teradata RDBMS and DB2 use the concept of database, but the meaning is somewhat different. The concept of database on Teradata RDBMS is closer in usage to DB2’s authorization ID than to DB2’s database.

Teradata SQL’s Data Dictionary differs in structure from the DB2 system catalog.
Previous << 1 .. 194 195 196 197 198 199 < 200 > 201 202 203 204 205 206 .. 241 >> Next