Teradata API

Preface
Purpose
The Teradata Tools and Utilities are a group of products designed to work with
the Teradata RDBMS. This book provides information on the Transparency
Series/Application Program Interface (TS/API) product. It includes an
overview of the product and its components, and it describes the operational
functions and features of the product.
The TS/API application program provides access to relational databases stored
on the Teradata RDBMS via a selected set of Independent Software Vendors
(ISV) products designed to retrieve data stored in DB2 or SQL/DS databases.
TS/API intercepts database requests from applications and passes them to the
Teradata RDBMS instead of to DB2 or SQL/DS. Data and error information are
returned to the application in the same format used by DB2 and SQL/DS.
Supported Releases
This book supports the following releases:
Teradata Database 12.00.00 (formerly RDBMS)
Teradata Tools and Utilities 12.00.00
TS/API 12.00.00
Changes to this Book

The following changes were made to this book to support the current release:
Date/Release Description
July 2007 There are no documentation changes resulting from this

release.
Audience
This book is intended for:
Application programmers
System operators and other database specialists
Teradata Transparency Series/Application Programming Interface User Guide i

Preface
Prerequisites
You should be familiar with the following:
Basic concepts of the Teradata RDBMS
IBM systems concepts and terminology for MVS, CICS, or VM
DB2 or SQL/DS concepts and terminology
Teradata SQL concepts and terminology
How to use any other products in your installation for interfacing with
TS/API
Teradata Tools and Utilities Release Definition

The Teradata Tools and Utilities Release Definition gives you any additional
information received late in the release process that may not have been
included in this document.
You can also find a list of:
Operating systems and Teradata RDBMS versions that the Teradata Tools
and Utilities release are certified to work with
Product release version numbers
Documentation that supports the current release
Training and support centers
Technical Information on the Web

For technical support, additional information, or the latest versions of Teradata
publications, you may access online information through the following
Teradata Web sites:
http://www.info.teradata.com/ A direct link to Teradata Information Products

Publishing library where you can view,
download, or order technical documentation and
CD-ROMs.
You can also find a Documentation List link for
each Teradata Tools and Utilities release. The
Documentation List identifies all publications
released in support of the current Teradata
RDBMS and the Teradata Tools and Utilities
release.
ii Teradata Transparency Series/Application Programming Interface User Guide

Preface
http://www.teradata.com/ The Teradata home page provides links to numerous

sources of information about Teradata. Among the
links provided are sites that deal with the following
subjects:
Technical support
Customer education courses
Case studies of customer experiences with
Teradata
Third party industry analyses of Teradata data
warehousing products
White papers
Online periodicals
List of Acronyms
The following acronyms are used in this book.
CICS Customer Information Control System
DB2 DATABASE 2
MVS Multiple Virtual Storage
RDBMS Relational Database Management System
REXX Restructured Extended Exector
SQL Structured Query Language
SQL/DS Structured Query Language/Data System
TOS Teradata Operating System
TS/API Transparency Series/Application Program Interface
VMS Virtual Memory System
Teradata Transparency Series/Application Programming Interface User Guide iii

Preface
iv Teradata Transparency Series/Application Programming Interface User Guide

Contents
Preface
Purpose ............................................................................................................................... i
Supported Releases ........................................................................................................... i
Changes to this Book......................................................................................................... i
Audience............................................................................................................................. i
Prerequisites ...................................................................................................................... ii
Teradata Tools and Utilities Release Definition........................................................... ii
Technical Information on the Web................................................................................. ii
List of Acronyms .............................................................................................................iii
Chapter 1:
Introduction to TS/API
Overview of TS/API ....................................................................................................... 12
What is TS/API?........................................................................................................... 12
Benefits of TS/API ....................................................................................................... 12
What TS/API Supports ............................................................................................... 13
Prerequisite Knowledge .............................................................................................. 14
TS/API Usability ............................................................................................................. 15
How TS/API Functions............................................................................................... 15
TS/API, DB2, and Application Tools (CICS) ........................................................... 15
TS/API, DB2, and Application Tools (TSO)............................................................. 16
TS/API, SQL/DS, and Application Tools ................................................................ 17
Supported Environments ............................................................................................ 18
TS/API Certified Products............................................................................................. 19
Certified Products ........................................................................................................ 19
Accessing Teradata DBMS from Application Tools................................................. 110
Under MVS/CICS ...................................................................................................... 111
Under VM/CMS:........................................................................................................ 112
Query Management Facility ........................................................................................ 113
QMF Issues .................................................................................................................. 113
Statistical Analysis System (SAS)................................................................................ 116
SAS Features................................................................................................................ 116
SAS Issues .................................................................................................................... 117
NATURAL...................................................................................................................... 119
Teradata Transparency Series/Application Programming Interface User Guide v

Contents
RXSQL ............................................................................................................................. 121

RXSQL Issues .............................................................................................................. 121
DB2 Trace Facility.......................................................................................................... 122
Turning the DB2 Trace Facility On and Off............................................................ 122
Chapter 2:
TS/API Installation and Customization
Control Flow Under TSO ............................................................................................... 22
Vectoring Enabled: Switching Between the Teradata RDBMS and DB2.............. 22
Vectoring Disabled: Teradata RDBMS Access Only ............................................... 23
Accessing TS/API Load Modules Under TSO and in Batch..................................... 25
Control Flow Under CICS .............................................................................................. 26
Accessing the TS/API Load Modules Under CICS.................................................... 27
Setting Up the Teradata RDBMS for TS/API.............................................................. 28
Teradata RDBMS Disk Space Requirements ............................................................ 28
BTEQ Scripts.................................................................................................................. 28
TS/API Databases ....................................................................................................... 28
Additional Sample Tables in the SQLDBA Database............................................ 210
Installing ISV Products ................................................................................................. 211
Open Support.............................................................................................................. 211
Certified Support - 03.04.00....................................................................................... 211
Certified Support - 03.04.00....................................................................................... 211
Preparing the ISV Product ........................................................................................... 212
Link-editing the Program .......................................................................................... 212
Converting Static SQL Into Teradata RDBMS Macros.......................................... 212
Setting Up the Teradata RDBMS.............................................................................. 213
The TERABIND Utility ................................................................................................. 214
JCL Example ................................................................................................................ 214
Usage Notes................................................................................................................. 215
QMF................................................................................................................................. 217
Run BTEQ Scripts ....................................................................................................... 217
Storing QMF Objects .................................................................................................. 218
Backing Up QMS Object Tables................................................................................ 218
Importing the QMS Object Tables............................................................................ 218
QMF Command Synonyms ...................................................................................... 219
QMF Suppporting BTEQ Scripts .............................................................................. 220
QKANJI Tables/Views .............................................................................................. 221
RDBMS Macros Created ............................................................................................ 221
QMF Release Dependencies...................................................................................... 222
SAS................................................................................................................................... 223
NATURAL...................................................................................................................... 224
RXSQL ............................................................................................................................. 225
vi Teradata Transparency Series/Application Programming Interface User Guide

Contents
Chapter 3:
Session Management
Specifying the Teradata RDBMS ................................................................................... 32
Under MVS/TSO and Batch ...................................................................................... 32
Under VM/CMS........................................................................................................... 33
Providing Logon Information ....................................................................................... 34
The DBCLOGON File/Table ...................................................................................... 34
The Logon Exit .............................................................................................................. 37
Chapter 4:
Product Management
MVS/TSO and Batch Products...................................................................................... 42
VM Products .................................................................................................................... 44
MVS/CICS Products....................................................................................................... 45
Chapter 5:
Problem Management
TS/API Debug Facility ................................................................................................... 52
DEBUG Directive.......................................................................................................... 52
DB2 Trace Facility............................................................................................................ 54
Turning the DB2 Trace On and Off............................................................................ 54
The OUTPUT and TRACE Files in MVS ..................................................................... 55
Allocating OUTPUT..................................................................................................... 55
Allocating TRACE ........................................................................................................ 55
The OUTPUT File in VM ............................................................................................... 56
The TSDB Debug Destination in MVS/CICS .............................................................. 57
OUTPUT Data Set......................................................................................................... 57
Master Terminal Commands ...................................................................................... 57
Basic Problem Determination (DEBUG SQL) ............................................................. 59
Interactive Application Debugging ........................................................................... 59
OUTPUT File Contents ................................................................................................ 59
Advanced Problem Determination (DEBUG ON) ................................................... 512
OUTPUT File Contents .............................................................................................. 512
Error Reporting Procedures......................................................................................... 513
Error Translations.......................................................................................................... 515
Error Translation Logic Strategy .............................................................................. 515
Teradata Transparency Series/Application Programming Interface User Guide vii

Contents
Chapter 6:
Static SQL and System Catalog Support
Static SQL Support .......................................................................................................... 62
Preparing a DB2 Application ...................................................................................... 62
Preparing an SQL/DS Application............................................................................ 65
TS/API Support of DB2 Static SQL ........................................................................... 67
TS/API Support of SQL/DS Static SQL.................................................................... 68
System Catalog Support ............................................................................................... 610
System Catalog Support ............................................................................................ 610
System Catalog Views................................................................................................ 610
System Catalog Tables ............................................................................................... 611
Long Teradata RDBMS Names................................................................................. 611
Authorization IDs....................................................................................................... 611
DB2 SYSIBM System Catalog Tables Emulation.................................................... 611
Chapter 7:
Command Syntax
SQL Differences ............................................................................................................... 72
Transaction Management............................................................................................... 73
DB2 or SQL/DS ............................................................................................................ 73
Teradata RDBMS .......................................................................................................... 73
Updatable Cursor Support ............................................................................................ 74
Non-Mapped Error Codes........................................................................................... 74
Syntax Mapping Strategy ............................................................................................... 75
DB2 Physical Database Structures ............................................................................. 75
SQL/DS Physical Database Structures...................................................................... 75
Referential Integrity ..................................................................................................... 76
Subqueries ..................................................................................................................... 76
SQL Reserved Word Conflicts .................................................................................... 76
FIELDPROC, EDITPROC, and VALIDPROC .......................................................... 76
DATE and TIME Functions......................................................................................... 76
Teradata SQL Extensions Differences .......................................................................... 78
Long Teradata SQL Names ......................................................................................... 78
The WITH Clause ......................................................................................................... 78
Sorting Nulls ................................................................................................................. 78
DB2 Syntax Mapping ...................................................................................................... 79
ALTER INDEX .............................................................................................................. 79
ALTER STOGROUP..................................................................................................... 79
ALTER TABLE .............................................................................................................. 79
ALTER TABLESPACE ................................................................................................. 79
viii Teradata Transparency Series/Application Programming Interface User Guide

Contents
COMMENT ON............................................................................................................ 79
COMMIT (WORK) ..................................................................................................... 710
CREATE ALIAS ......................................................................................................... 710
CREATE DATABASE ................................................................................................ 710
CREATE INDEX ......................................................................................................... 710
CREATE STOGROUP ................................................................................................ 710
CREATE SYNONYM ................................................................................................. 710
CREATE TABLE ......................................................................................................... 710
CREATE TABLESPACE ............................................................................................ 711
CREATE VIEW ........................................................................................................... 711
DELETE FROM........................................................................................................... 711
DROP............................................................................................................................ 711
EXPLAIN .................................................................................................................... 712
GRANT ....................................................................................................................... 712
INSERT ........................................................................................................................ 714
LABEL ON .................................................................................................................. 715
LOCK TABLE ............................................................................................................ 715
REVOKE .................................................................................................................. 715
ROLLBACK [WORK]................................................................................................. 715
SELECT ........................................................................................................................ 715
UPDATE ...................................................................................................................... 715
SQL/DS Syntax Mapping ............................................................................................ 716
ACQUIRE DBSPACE................................................................................................. 716
ALTER DBSPACE ...................................................................................................... 716
ALTER TABLE ............................................................................................................ 716
COMMENT ON ......................................................................................................... 716
COMMIT WORK (RELEASE)................................................................................... 717
CONNECT................................................................................................................... 717
CREATE INDEX ......................................................................................................... 717
CREATE SYNONYM ................................................................................................. 717
CREATE TABLE ......................................................................................................... 717
CREATE VIEW ........................................................................................................... 718
DELETE FROM........................................................................................................... 718
DROP ........................................................................................................................... 718
EXPLAIN ..................................................................................................................... 718
GRANT ........................................................................................................................ 718
INSERT......................................................................................................................... 719
LABEL ON .................................................................................................................. 719
LOCK ........................................................................................................................... 720
REVOKE ..................................................................................................................... 720
ROLLBACK WORK (RELEASE) .............................................................................. 720
SELECT ........................................................................................................................ 720
Teradata Transparency Series/Application Programming Interface User Guide ix

Contents
UPDATE ...................................................................................................................... 720

UPDATE STATISTICS FOR ..................................................................................... 720
Teradata SQL Extensions ............................................................................................. 721
ABORT ......................................................................................................................... 721
BEGIN TRANSACTION (BT) ................................................................................... 721
CHECKPOINT ............................................................................................................ 721
COLLECT STATISTICS ............................................................................................. 721
COMMENT ON.......................................................................................................... 721
CREATE ....................................................................................................................... 722
DATABASE ................................................................................................................. 722
DELETE........................................................................................................................ 722
DROP............................................................................................................................ 722
DROP INDEX.............................................................................................................. 722
DROP MACRO ........................................................................................................... 722
DROP STATISTICS .................................................................................................... 722
END TRANSACTION (ET)....................................................................................... 722
EXECUTE macroname............................................................................................... 723
GIVE ............................................................................................................................. 723
GRANT ........................................................................................................................ 723
HELP ............................................................................................................................ 724
MODIFY....................................................................................................................... 724
Multi-Statement Requests ......................................................................................... 724
RENAME ..................................................................................................................... 724
REPLACE MACRO, REPLACE VIEW .................................................................... 725
REVOKE....................................................................................................................... 725
SELECT ........................................................................................................................ 725
SHOW .......................................................................................................................... 725
TS/API Directives ........................................................................................................ 726
Introduction................................................................................................................. 726
DEBUG ......................................................................................................................... 726
LOGON ........................................................................................................................ 726
LOGOFF....................................................................................................................... 726
DEBUG PERF .............................................................................................................. 726
SET SESSION (SS)....................................................................................................... 727
SET SESSION CHARSET........................................................................................... 728
SET SESSION COLLATION ..................................................................................... 728
SET SESSION DATABASE........................................................................................ 730
SET SESSION DATEFORM....................................................................................... 730
Appendix A:
Translation Tables
Error Codes...................................................................................................................... A2
x Teradata Transparency Series/Application Programming Interface User Guide

Contents
Error Code Translations for DB2............................................................................... A2

Error Code Translations for SQL/DS..................................................................... A17
Appendix B:
TS/API Catalog Emulation
Catalog Tables and Views ..............................................................................................B2
Catalog Emulation Table/View .................................................................................B2
SYSIBM .SYSCOLAUTH .............................................................................................B4
SYSIBM .SYSCOLUMNS.............................................................................................B5
SYSIBM.SYSCOPY........................................................................................................B7
SYSIBM .SYSDATABASE............................................................................................B8
SYSIBM .SYSDBAUTH ................................................................................................B9
SYSIBM.SYSDBRM.....................................................................................................B13
SYSIBM.SYSFIELDS ...................................................................................................B14
SYSIBM .SYSFOREIGNKEYS ...................................................................................B15
SYSIBM .SYSINDEXES ..............................................................................................B15
SYSIBM .SYSINDEXPART ........................................................................................B18
SYSIBM.SYSKEYS.......................................................................................................B20
SYSIBM.SYSLINKS.....................................................................................................B21
SYSIBM.SYSPLAN .....................................................................................................B22
SYSIBM .SYSPLANAUTH ........................................................................................B23
SYSIBM .SYSPLANDEP ............................................................................................B24
SYSIBM.SYSRELS .......................................................................................................B24
SYSIBM .SYSRESAUTH ............................................................................................B25
SYSIBM.SYSSTMT......................................................................................................B26
SYSIBM .SYSSTOGROUP..........................................................................................B26
SYSIBM .SYSSYNONYMS.........................................................................................B27
SYSIBM .SYSTABAUTH............................................................................................B27
SYSIBM .SYSTABLEPART ........................................................................................B31
SYSIBM.SYSTABLES..................................................................................................B33
SYSIBM .SYSTABLESPACE......................................................................................B37
SYSIBM .SYSUSERAUTH .........................................................................................B38
SYSIBM .SYSVIEWDEP .............................................................................................B40
SYSIBM.SYSVIEWS....................................................................................................B40
SYSIBM.SYSVLTREE .................................................................................................B41
SYSIBM .SYSVOLUMES............................................................................................B42
SYSIBM.SYSVTREE....................................................................................................B42
Emulation of the SQL/DS SYSTEM System Catalog Tables................................B43
SYSTEM.SYSACCESS ................................................................................................B44
SYSTEM .SYSCATALOG ..........................................................................................B45
SYSTEM .SYSCHARSETS..........................................................................................B47
SYSTEM .SYSCOLAUTH ..........................................................................................B48
Teradata Transparency Series/Application Programming Interface User Guide xi

Contents
SYSTEM .SYSCOLUMNS ..........................................................................................B48

SYSTEM .SYSDBSPACES ..........................................................................................B51
SYSTEM.SYSDROP ....................................................................................................B52
SYSTEM .SYSINDEXES .............................................................................................B53
SYSTEM .SYSKEYCOLS ............................................................................................B55
SYSTEM .SYSOPTIONS.............................................................................................B55
SYSTEM .SYSPROGAUTH .......................................................................................B56
SYSTEM .SYSSYNONYMS........................................................................................B57
SYSTEM .SYSTABAUTH...........................................................................................B58
SYSTEM.SYSUSAGE..................................................................................................B60
SYSTEM .SYSUSERAUTH ........................................................................................B60
SYSTEM.SYSVIEWS...................................................................................................B61
Glossary...............................................................................................................Glossary1
Index.......................................................................................................................... Index1
xii Teradata Transparency Series/Application Programming Interface User Guide

List of Figures
Figure 1-1 Flow Control in CICS.............................................................................. 16

Figure 1-2 Flow Control in TSO ............................................................................... 17
Figure 1-3 Flow Control in VM ................................................................................ 18
Figure 1-4 QMF Edit Table Command Prompt Screen ....................................... 115
Figure 2-1 Vectoring Enabled ................................................................................... 23
Figure 2-2 Vectoring Disabled .................................................................................. 24
Figure 2-3 Flow Control in CICS.............................................................................. 26
Figure 6-1 Preparing a DB2 Application with Embedded SQL for Execution .. 64
Figure 6-2 Preparing an SQL/DS Application with Embedded SQL for
Execution ............................................................................................... 66
Figure 6-3 DB2 Static SQL Translation to Teradata SQL Macros Process for
Certified ISVs ........................................................................................ 68
Figure 6-4 SQL/DS Embedded SQL Translation to Teradata Macros Process for
Certified ISVs ........................................................................................ 69
Teradata Transparency Series/Application Programming Interface User Guide xiii

List of Figures
xiv Teradata Transparency Series/Application Programming Interface User Guide

List of Tables
Table 1-1 SAS Issues............................................................................................... 117

Table 5-1 Debugging Options................................................................................. 52
Table 5-2 Problem Reporting Information.......................................................... 513
Table 5-3 Procedure to Obtain Debug Information ........................................... 514
Table 6-1 Teradata RDBMS Catalog Tables......................................................... 610
Table 7-1 Non-Mapped Error Codes ..................................................................... 74
Table 7-2 Special Registers ...................................................................................... 76
Table 7-3 GRANT (DB2 Database Privileges) .................................................... 713
Table 7-4 GRANT (DB2 Plan Privileges)............................................................. 713
Table 7-5 GRANT (DB2 System Privileges)........................................................ 714
Table 7-6 GRANT (DB2 Table Privileges) ........................................................... 714
Table 7-7 GRANT (DB2 Use Privileges).............................................................. 714
Table 7-8 GRANT (SQL/DS Privileges on Tables and Views)......................... 719
Table 7-9 GRANT (SQL/DS Privileges on Programs) ...................................... 719
Table 7-10 GRANT (SQL/DS Special Privileges) ................................................ 719
Table 7-11 Timestamp fields in CLIv2s DBCAREA communication block .... 727
Table A-1 Error Code Translations for DB2 .......................................................... A2
Table A-2 Error Code Translations for DB2 .......................................................... A4
Table A-3 Error Code Translations for SQL/DS ................................................ A17
Table A-4 Error Code Translations for SQL/DS ................................................ A19
Table B-1 DB2 SYSIBM Catalog ..............................................................................B2
Table B-2 DB2 SYSIBM Catalog ..............................................................................B3
Table B-3 SYSIBM.SYSCOLAUTH Description ...................................................B4
Table B-4 SYSIBM.SYSCOLUMNS Description ...................................................B5
Table B-5 How Columns in SYSIBM.SYSCOLUMNS are Supported ...............B6
Table B-6 SYSIBM.SYSCOPY Description.............................................................B7
Table B-7 How Columns in SYSIBM.SYSCOPY Are Supported........................B7
Table B-8 SYSIBM.SYSDATABASE Description...................................................B8
Table B-9 How Columns in SYSIBM.SYSDATABASE Are Supported .............B9
Table B-10 SYSIBM.SYSDBAUTH Description ....................................................B10
Table B-11 How Columns in SYSIBM.SYSDBAUTH Are Supported ............... B11
Table B-12 SYSIBM.SYSDBRM Description ..........................................................B13
Table B-13 SYSIBM.SYSFIELDS Description ........................................................B14
Table B-14 SYSIBM.SYSFOREIGNKEYS Description..........................................B15
Table B-15 SYSIBM.SYSINDEXES Description ....................................................B16
Table B-16 How Columns in SYSIBM.SYSINDEXES Are Supported ...............B17
Table B-17 SYSIBM.SYSINDEXPART Description ...............................................B18
Teradata Transparency Series/Application Programming Interface User Guide xv

List of Tables
Table B-18 How Columns in SYSIBM.SYSINDEXPART Are Supported..........B19

Table B-19 SYSIBM.SYSKEYS Description ............................................................B20
Table B-20 How Columns in SYSIBM.SYSKEYS Are Supported.......................B20
Table B-21 SYSIBM.SYSLINKS Description ..........................................................B21
Table B-22 SYSIBM.SYSPLAN Description...........................................................B22
Table B-23 How Columns in SYSIBM.SYSPLAN Are Supported......................B23
Table B-24 SYSIBM.SYSPLANAUTH Description...............................................B23
Table B-25 SYSIBM.SYSPLANDEP Description...................................................B24
Table B-26 SYSIBM.SYSRELS Description ............................................................B24
Table B-27 SYSIBM.SYSRESAUTH Description...................................................B25
Table B-28 SYSIBM.SYSSTMT Description ...........................................................B26
Table B-29 SYSIBM.SYSSTOGROUP Description ................................................B26
Table B-30 SYSIBM.SYSSYNONYMS Description ...............................................B27
Table B-31 SYSIBM.SYSTABAUTH Description ..................................................B28
Table B-32 How Columns in SYSIBM.SYSTABAUTH Are Supported .............B29
Table B-33 SYSIBM.SYSTABLEPART Description ...............................................B31
Table B-34 How Columns in SYSIBM.SYSTABLEPART Are Supported ..........B32
Table B-35 SYSIBM.SYSTABLES Description .......................................................B33
Table B-36 How Columns in SYSIBM.SYSTABLES Are Supported ..................B34
Table B-37 SYSIBM.SYSTABLESPACE Description.............................................B37
Table B-38 How Columns in SYSIBM.SYSTABLESPACE Are Supported........B38
Table B-39 SYSIBM.SYSUSERAUTH Description................................................B39
Table B-40 SYSIBM.SYSVIEWDEP Description ...................................................B40
Table B-41 SYSIBM.SYSVIEWS Description .........................................................B40
Table B-43 SYSIBM.SYSVLTREE Description .......................................................B41
Table B-42 How Columns in SYSIBM.SYSVIEWS Are Supported ....................B41
Table B-44 SYSIBM.SYSVOLUMES Description ..................................................B42
Table B-45 SYSIBM.SYSVTREE Description .........................................................B42
Table B-46 How Columns in SYSIBM.SYSVTREE Are Supported ....................B43
Table B-47 SQL/DS Catalog ....................................................................................B43
Table B-48 SYSTEM.SYSACCESS Description .....................................................B44
Table B-49 SYSTEM.SYSCATALOG Description .................................................B45
Table B-50 How Columns in SYSTEM.SYSCATALOG Are Supported ............B46
Table B-51 SYSTEM.SYSCHARSETS Description................................................B47
Table B-52 How Columns in SYSTEM.SYSCHARSETS Are Supported...........B47
Table B-53 SYSTEM.SYSCOLAUTH Description ................................................B48
Table B-54 SYSTEM.SYSCOLUMNS Description ................................................B49
Table B-55 How Columns in SYSTEM.SYSCOLUMNS Are Supported ...........B49
Table B-56 SYSTEM.SYSDBSPACES Description.................................................B51
Table B-57 How Columns in SYSTEM.SYSDBSPACES Are Supported............B51
Table B-58 SYSTEM.SYSDROP Description..........................................................B52
Table B-59 SYSTEM.SYSINDEXES Description....................................................B53
xvi Teradata Transparency Series/Application Programming Interface User Guide

List of Tables
Table B-60 How Columns in SYSTEM.SYSINDEXES Are Supported ..............B54

Table B-61 SYSTEM.SYSKEYCOLS Description ..................................................B55
Table B-62 SYSTEM.SYSOPTIONS Description ...................................................B55
Table B-63 How Columns in SYSTEM.SYSOPTIONS Are Supported..............B56
Table B-64 SYSTEM.SYSPROGAUTH Description..............................................B56
Table B-65 How Columns in SYSTEM.SYSPROGAUTH Are Supported ........B56
Table B-66 SYSTEM.SYSSYNONYMS Description..............................................B57
Table B-67 SYSTEM.SYSTABAUTH Description .................................................B58
Table B-68 How Columns in SYSTEM.SYSTABAUTH Are Supported ............B59
Table B-69 SYSTEM.SYSUSAGE Description .......................................................B60
Table B-70 SYSTEM.SYSUSERAUTH Description ..............................................B60
Table B-71 How Columns in SYSTEM.SYSUSERAUTH Are Supported .........B61
Table B-72 SYSTEM.SYSVIEWS Description ........................................................B61
Table B-73 How Columns in SYSTEM.SYSVIEWS Are Supported...................B62
Teradata Transparency Series/Application Programming Interface User Guide xvii

List of Tables
xviii Teradata Transparency Series/Application Programming Interface User Guide

Chapter 1:
Introduction to TS/API
This chapter provides an overview of Transparency Series/Application

Program Interface (TS/API) and containsthe following information:
Overview of TS/API
TS/API Usability
TS/API Certified Products
Accessing Teradata DBMS from Application Tools
Query Management Facility
Statistical Analysis System (SAS)
NATURAL
RXSQL
DB2 Trace Facility
Teradata Transparency Series/Application Programming Interface User Guide 11

Chapter 1: Introduction to TS/API
Overview of TS/API
Overview of TS/API
TS/API provides gateway services allowing products that access either DB2 or
SQL/DS databases to access data stored on the Teradata RDBMS. TS/API thus
lets you take advantage of both the convenient and easy-to-use data access
products and the tremendous storage capability and superior processing power
of the Teradata RDBMS.
TS/API works with Teradata RDBMS Version 1 and Version 2 (Teradata mode).
Any product that uses the IBM Call Attach Facility to access DB2 or SQL/DS
and does not use DB2- or SQL/DS-only constructs is a likely candidate for use
with TS/API. TS/API has been certified to support the following products:
SAS System, developed by SAS Institute, Inc.
Query Management Facility (QMF), developed by IBM
REXX programming language (via RXSQL requests ) developed by IBM
NATURAL, developed by Software AG.
See What TS/API Supports on page 1-3 and TS/API Certified Products on
page 1-9.
What is TS/API?
TS/API is an application program interface that allows you to access relational
databases stored on the Teradata RDBMS via a selected set of ISV (Independent
Software Vendors) products designed to retrieve data stored in DB2 or SQL/DS
databases. You do not need to know Teradata SQL in order to access this data.
TS/API intercepts database requests from applications and passes them to the
Teradata RDBMS instead of to DB2 or SQL/DS. Data and error information are
returned to the application in the same format used by DB2 and SQL/DS. By
using TS/API, a typical DB2 or SQL/DS application can access data in the
Teradata RDBMS, both Version 1 and Version 2, with no or minor application
modification.
Benefits of TS/API
TS/API allows you to take advantage of the tremendous storage capability and
processing power of the Teradata RDBMS and the NCR hardware platform
while using the features of the SAS System, QMF, REXX, NATURAL, or other
ISV product. You do not need to change your databases or the programs
already in place that access them. TS/API provides ease of use coupled with
the ability to fully exploit the advantages of the Teradata RDBMS. TS/API also
supports Teradata SQL Extensions with applications that have a pass-through
facility.
12 Teradata Transparency Series/Application Programming Interface User Guide

Overview of TS/API
What TS/API Supports

TS/API is intended principally for use with selected DB2-based or SQL/DS-
based program products that operate in the following environments.
MVS batch
MVS/TSO
MVS/CICS
VM/CMS
TS/API has been certified to work with the following products:
Query Management Facility (QMF) Releases 2.4, 3.1, 3.1.1, 3.2 and 3.3.0
NATURAL Release 2.2.5
The SAS System, Releases 6.07, 6.08, and 6.09
RXSQL Release 3.4.0
TS/API has been certified to work with the following versions of Teradata
RDBMS:
Version 1, Release 5.x
Version 2, Release 1.x, Version 2, Release 2.0 (Teradata mode only) and
Version 2, Release 3.0 (Teradata mode only).
Vendor tools certified by Teradata have undergone rigorous function testing to
ensure the integrity of all information being passed between the tool and the
Teradata RDBMS.
Caution: While it is possible that other DB2-based and SQL/DS-based program products
or user-written DB2 and SQL/DS application programs can access the Teradata
RDBMS through TS/API, Teradatas support for these products is limited at
this time. Using TS/API with any software other than the products listed in this
guide is at the users own risk.
Kanji Support
TS/API supports the following features of Kanji/Multi-byte Character Sets
(MBCS):
Character sets:
EBCDIC
KATAKANAEBCDIC
KANJIEBCDIC5026_0I
KANJIEBCDIC5035_0I
Mixed MBCS/Single-byte Character Sets (SBCS) character strings as object
names and literals.
Hexadecimal notation for object names.
GRAPHIC data types.

Overview of TS/API
Note that all SQL keywords and TS/API directives must be coded in SBCS of
the servers corresponding charset.
Prerequisite Knowledge
This book assumes that you are acquainted with the use of QMF,
REXX/RXSQL, NATURAL, and/or the SAS System. For more information on
the Teradata RDBMS, refer to one of the following manuals:
For an NCR System 3600 running Teradata RDBMS Version 1, Release 5.1:
the Teradata RDBMS Concepts and Facilities Manual
For an NCR DBC/1012 running Teradata RDBMS Version 1, Release 5.1: the
Teradata RDBMS Concepts and Facilities Manual
For either platform running Teradata RDBMS Version 1, Release 5.2 and
beyond: the Teradata RDBMS Overview Manual
For a UNIX platform running Teradata RDBMS Version 2, Release 2.0 and
beyond: Introduction to the Teradata RDBMS
For a UNIX platform running Teradata RDBMS Version 2, Release 3: see
Teradata RDBMS for UNIX Release Summary for Version 2 Release 3.0. Search
for additional Version 2, Release 3, manuals at
http://www.info.teradata.com/.

TS/API Usability
TS/API Usability
This section explains how TS/API functions and the advantages of using
TS/API.
How TS/API Functions

To support DB2 and SQL/DS application programs, TS/API performs a
number of complex operations on SQL requests, which are passed to the
Teradata RDBMS and on the data that is returned to the application program.
TS/API supports DB2 and SQL/DS programs by performing the following
functions:
Providing a mechanism for the application program user to log on to the
Teradata RDBMS
Providing DB2 Call Attach Facility replacement modules
Translating application program queries to Teradata SQL
Supporting the use of static SQL queries by storing DB2 plans and SQL/DS
access modules as Teradata RDBMS macros
Supporting the use of Extended Dynamic SQL by storing SQL/DS
packages as Teradata RDBMS macros.
Converting incoming application program data to Teradata RDBMS format
Converting outgoing Teradata RDBMS data to a format usable by
application programs
Translating Teradata RDBMS error codes to DB2 or SQL/DS SQLCODEs
and SQLSTATEs
Creating SQLERRM text inserts from Teradata RDBMS error messages
Providing DB2 and SQL/DS system catalog emulation views based on data
stored in the Teradata RDBMS system catalog
Emulating DB2 and SQL/DS unit of work logic
Supporting updatable cursors
TS/API, DB2, and Application Tools (CICS)

Under the CICS environment, application tools (QMF, NATURAL) use DB2s
CICS Call Attach Facility module (DSNCLI) to access DB2. TS/API replaces
DSNCLI, and passes all DB2 requests to TS/API and thus to Teradata RDBMS.

TS/API Usability
The following diagram depicts the flow of control.
Figure 1-1 Flow Control in CICS
Application
Tool
TS/API's
DSNALI
TS/API
Teradata
DBS
JH01A003
TS/API, DB2, and Application Tools (TSO)

Some application tools (QMF, NATURAL, SAS) use DB2s Call Attach Facility
module (DSNALI) to access DB2 under TSO. For those tools, TS/API provides
a vectoring capability to access data in either DB2 or the Teradata RDBMS. To
accomplish this vectoring, TS/API provides its own version of DSNALI.
The vectoring is based on the subsystem ID, as follows:
DSNALI receives the SSID from Application Tools.
DSNALI checks that SSID.
If the SSID does not start with a prefix of TD, DSNALI calls IBMs original
DSNALI (TIBMALI) and control goes to DB2.
If the SSID starts with TD, control goes to TS/API and the Teradata RDBMS
is accessed.

TS/API Usability
The following diagram depicts the flow of control:
Figure 1-2 Flow Control in TSO
Application
Tool
TS/API's
DSNALI
SSID=
TDxx
Yes No
TIMBALI
TS/API
(IBM's DSNALI)
Teradata
DB2
DBS
JH01A005
TS/API, SQL/DS, and Application Tools

Application tools (QMF and SAS) use the SQL/DS Resource Manager to
connect to TS/API and then process SQL requests. If you do not have an
SQL/DS license, Teradata provides a Resource Manager for you.

TS/API Usability
The following diagram shows the relationship between application tools and
TS/API.
Figure 1-3 Flow Control in VM
Application
Tool
Resource
Manager
TS/API
Teradata
DBS
JH01A004
Supported Environments
The following environments are supported by TS/API:
MVS/XA (TSO)
MVS/ESA (TSO)
MVS/XA (CICS)
MVS ESA (CICS)
VM/CMS (SP, HPO, XA in 370 and XA modes, and ESA in 370 and ESA
modes)
Products that run in the CICS environment use the two-phase commit facility
(2PC), which allows CICS (the transaction manager) to synchronize updates on
different resources (databases, file systems, etc.) For additional details on the
2PC facility, see Teradata RDBMS CICS Interface.

This section provides brief descriptions of the products that TS/API has been
certified to support. In addition, it provides procedures for accessing the
Teradata DBMS with these certified products.
Certified Products
TS/API Release 3.3.0 has been certified to support:
Query Management Facility (QMF)
Version 2, Release 4 (MVS/TSO, VM/CMS)
Version 3, Release 1.0 (MVS/TSO, MVS/CICS, VM/CMS)
Version 3, Release 1.1 (MVS/TSO, MVS/CICS VM/CMS)
Version 3, Release 2 (MVS/TSO, MVS/CICS, VM/CMS)
Version 3, Release 3 (MVS/TSO, VM/CMS)
The SAS System (Version 6.07, Version 6.08 and Version 6.09)
SAS/ACCESS Interface to DB2 (MVS/TSO)
SAS/ACCESS Interface to SQL/DS (VM/CMS)
NATURAL, Release 2.2.5 (MVS/TSO and MVS/CICS)
RXSQL, Release 3.4.0 (VM/CMS)
To certify TS/API with an Independent Software Vendor (ISV) product,
Teradata performs extensive testing and quality assurance to ensure that
TS/API works properly with the ISV product, except as specifically noted in
this guide.
Once Teradata certifies a product, it provides technical support, problem
resolution, and software maintenance for TS/API as used with certified
products.
This section describes how TS/API is used with these ISV products.
Note: User familiarity with the supported ISV products is assumed. For
detailed information about the operations of the ISV products mentioned in
this guide, see the appropriate vendor documentation.

Accessing Teradata DBMS from

Application Tools
To ensure connection to a Teradata DBMS from your ISV products (QMF, SAS
or NATURAL), verify the following before starting the product:
Step Action
1. TS/API libraries are properly allocated. TS/API load library should be

concatenated before the DB2 load library, if any. Allocation occurs as part of
either the TSO start-up procedure, the tool starting CLIST, or the tool
starting JCL, depending on how you invoke the ISV product.
2. Your DBCLOGON data set or logon exit is properly prepared.
IF... THEN...
your installation does not provide you must build and allocate the
a logon exit DBCLOGON data set. See Chapter
3: Session Management for
details.
If your installation does provide a your userid and password are

logon exit automatically provided to the
Teradata RDBMS at logon time.
3. SSID (subsystem ID) is assigned an appropriate value. Under TSO, TS/API

provides vectoring capabilities, allowing access to the data in either
Teradata RDBMS or DB2.
IF... THEN...
you specify the SSID as TDxx Teradata RDBMS will be accessed,

using that TDxx as a <tdpid>
the SSID doesnt have a prefix of DB2 will be accessed

TD
you dont have a DB2 installed, a connection error will occur.
For more information on vectoring, see Chapter 2: TS/API Installation and

Customization.
1 10 Teradata Transparency Series/Application Programming Interface User Guide

The following are two examples illustrating JCL statements used to invoke
QMF 3.1 and SAS to access TDP0:
QMF invocation:
...
//SYSTSIN DD *
PROFILE PREFIX(USERID)
ISPSTART PGM(DSQQMFE) NEWAPPL PARM(M=B,P=QMF310,S=TDP0)
/*
...
SAS invocation:
...
//SYSTSIN DD *
PROC DBLOAD DBMS=DB2;
SSID=TDP0;
SQL COMMIT WORK;
/*
...
Under MVS/CICS
Verify the following:
Step Action
1. TS/API libraries are properly included in the DFHRPL libraries list in CICS
start-up JCL. The TS/API load libraries should be concatenated before the
DB2 load library, if any.
2. BBIRCT and DBCLOGON tables contain appropriate information for the

transaction ID youll use. Under CICS, TS/API obtains logon information
from these two tables. See Chapter 2: TS/API Installation and
Customization for details.
Teradata Transparency Series/Application Programming Interface User Guide 1 11

Under VM/CMS:
Step Action
1. The client utilities production minidisk containing TS/API is properly

allocated. Allocation normally occurs during your IPL (if done in your
PROFILE EXEC) or during the product invocation (if done in your product
invocation EXEC).
2. Your DBCLOGON file or logon exit is properly prepared.
IF... THEN...
your installation does not provide you must build and allocate the
a logon exit DBCLOGON file. See Chapter 3:
Session Management for details.
your installation does provide a your userid and password are

logon exit automatically provided for the
Teradata RDBMS at logon time.
3. TS/APIs SQLINIT with the appropriate TDxx was executed. See Chapter 3:
Session Management for details.

A tool that has become preeminent in todays DB2 and SQL/DS environment is
IBMs Query Management Facility (QMF). QMF has emerged as the de facto
product used to access relational data in order to produce reports, graphs, and
charts, either through native SQL commands, prompted queries, or queries by
example. Through TS/API, QMF can be used to access information stored in
Teradata RDBMS databases. The following table shows some QMF issues.
QMF Issues
Step Action
1 Although TS/API supports QMF release 3.30, because of the differences

between Teradata RDBMS and DB2, it does not support the Table Editor
function for default column definitions on a MVS platform.
2 TS/API supports QMF 3.2.0, which displays the dates on which each QMF
object was created, modified and last used. Please note that on VM
platforms, only date last used is supported. All three dates are supported
on MVS/TSO and MVS/CICS.
3 3. TS/API supports the QMF Edit Table Facility (ETF) in both edit and browse
mode. TS/API recognizes when a non-updatable table or view is being
accessed with ETF, and automatically places ETF in browse mode. This
allows you to view data but not to update or delete it.
For more information on updatable cursors, see Chapter 7: Command
Syntax.

Step Action
4 TS/API does not support the SAVE=IMMEDIATE option under the QMF
ETF because Teradata SQL does not support the WITH HOLD option of the
DECLARE CURSOR statement. TS/API performs a commit with each SAVE
and then performs clean up for the given ETF session, thereby removing the
cursor.
Removal of the cursor means that you cannot fetch the next row; therefore,
the query must be reexecuted to bring the result table back into memory.
Figure 1-4 on page 1-15 shows the ETF screen on which you enter the SAVE
command.
The two options of SAVE are IMMEDIATE and END. QMF issues the
message shown at the bottom of the screen when you attempt to use
SAVE=IMMEDIATE under TS/API.
5 In the CICS environment, QMF has additional issues and restrictions, which
are:
ROLLBACK is not supported for Data Definition Language (DDL)
statements, since TS/API executes all DDL statements in a one-phase
commit (1PC) session as stand-alone transactions. This means that once a
DDL statement is successfully executed, it cannot be rolled back. For
example, after you issue a DROP TABLE statement, a QMF prompt panel
will request confirmation. Even if you want to undo the DROP TABLE
command, you cant because the table will have already been dropped
and the statement committed. Be aware of this limitation and use the
DROP TABLE/ERASE TABLE statements--and any other DDL
statements--with caution.
QMF Edit Table Facility: There are some problems in TS/API when using
the QMF Edit Table Facility. The problems involve:
Adding a new row to a table (MODE=ADD)
Updating the index values of an existing row (MODE=CHANGE), for
which QMF internally INSERTs a row with a new index value.
These problems occur only when the QMF user attempts to insert a row with
a duplicate index value into a table. Use the QMF SQL Query facility instead
of the Edit Table Facility for INSERTing new rows into tables.
Currently the QMF Erase Table command does not work from the QMF
tables or QMF objects lists. Use the Drop Table SQL statement or QMF
Erase Table command from the QMF command line instead
6 Due to QMF's and DB2s limitations, the 64K DBS feature cannot be used
with TS/API.

Figure 1-4 QMF Edit Table Command Prompt Screen
EDIT TABLE Command Prompt

1_ to 16 of 16
EDIT type TABLE
Name ( Q.STAFF
Enter the name of the table in the database you
want to edit.
Mode ( CHANGE )
Enter ADD to add new rows, or CHANGE to update
or delete rows.
SAVE ( IMMEDIATE )
Enter IMMEDIATE to save database alterations as
they are made, or END to hold database alterations
until the session is completed.
Confirm ( YES )
Enter NO to turn off confirmation prompting.
Enter YES to accept default confirmation prompting.

Another tool that has become preeminent in todays DB2 and SQL/DS
environment is the SAS System from SAS Institute, Inc. The SAS System is
installed at over 8,000 mainframe sites worldwide.
It has evolved from a data analysis tool to an enterprise-wide information
delivery system. The SAS System accesses a number of different database
management systems (DBMS), among them, DB2 and SQL/DS.
SAS Features
SAS features include:
Data entry, retrieval, and management
Report writing and graphics
Application development
Business planning, forecasting, and decision support
Operations research and project management
Statistical and mathematical analysis (including quality control)
Computer performance evaluation
Executive Information System
Through TS/API, the SAS System can be used to access information stored in a
Teradata RDBMS database. Specify DB2 as the DBMS on the SAS/Access panel
in order to make a Teradata RDBMS or DB2 connection. A DBMS of DBC or
DBC/1012 returns an undefined DBMS access message from the SAS System.
The specified SSID then indicates which will be accessed: the Teradata RDBMS
or DB2.

SAS Issues
Be aware of the following issues before using either the SAS/ACCESS interface
to DB2 or the SAS/ACCESS interface to SQL/DS.
SAS/ACCESS provides a Proc SQL passthrough facility, which allows SQL
statements to be passed to DBMS without being parsed by SAS. This facility
alleviates the problems listed in the following table.
Table 1-1 SAS Issues
Step Action
1 You can use the SAS System view descriptor (without performance degradation) anywhere it is legal to
use a SAS data file, with the exception of PROC SQL, the FSEDIT window, and PROC DBLOAD.
The PROC SQL SELECT statement generates inefficient SQL in some cases.
With SELECT * FROM <descriptor>.
Performance is not affected using the PROC SQL SELECT statement if you issue a SELECT * FROM <
descriptor> ... statement or specifically select view descriptor columns (e.g., SELECT col1, col2, ...).
The SAS System retrieves all rows to the client as defined by the original view descriptor definition.
With SELECT COUNT (*) statement or SELECT...ORDER BY... statement.
However, performance degradation occurs using the PROC SQL SELECT statement if you issue a
SELECT COUNT(*)... statement or a SELECT ... ORDER BY ... statement. The SAS System retrieves
all rows to the client as defined by the original view descriptor definition into temporary storage. For
the SELECT COUNT(*) request, the SAS System calculates the COUNT (reads and counts all the
retrieved records) instead of using the more efficient COUNT function on the Teradata RDBMS. For
the SELECT ... ORDER BY ... request, SAS sorts the retrieved records itself instead of allowing the
Teradata RDBMS to perform the ORDER BY clause more quickly and without using client resources.
The performance of these queries ranges from slow to extremely slow because the client and DBMS
must engage in a duplication of effort.
A view descriptor used as input to PROC DBLOAD generates inefficient SQL because the SAS System
retrieves each row to the client (translating between DBMS data types and SAS data types) and then
builds an insert for each row retrieved, sending it back to the Teradata RDBMS. Therefore, if a large
amount of data is to be loaded on to the Teradata RDBMS, use one of Teradata RDBMS load utilities.
2 PROC DBLOADs native SQL screen is best used for most statements that do not return data.
Data-returning statement cannot be used with the native SQL screen, including:
SELECT
SHOW
HELP
EXPLAIN
COMMENT (data-returning form)
EXEC (data-returning form)

Table 1-1 (Continued) SAS Issues
Step Action
You can use the SAS Query Entry Window to execute native SQL to copy from a SAS System view
descriptor to a Teradata RDBMS table. To create the table, issue a CREATE TABLE or CT with the
appropriate parameters. To populate the table, issue:
INSERT INTO <table1> SELECT * FROM <table2>
where <table2> underlies the SAS view descriptor.
3 Batch considerations:
No special batch considerations exist for improving SAS performance with the DBMS other than those
presented above.
Submit queries in batch when you expect slow performance (see View Descriptor usage above),
thereby freeing your session while your job executes.
4 Running under MVS, SAS/ACCESS returns an SQLCODE of 100 (ROW NOT FOUND FOR FETCH)
when a user requests access to a Teradata DBS view that contains more than 50 columns. This halts SAS
execution.
In order to satisfy a user request to access a Teradata DBS view, SAS/ACCESS queries DB2 catalog table
SYSIBM.SYSCOLUMNS to gather user view column information. TS/API uses Teradata DBS emulation
view SYSIBM.SYSCOLUMNS in order to select data from the appropriate Teradata DBS catalog tables in a
format that matches the format used in the DB2 catalog table.
However, since the Teradata DBS pre-V2R2 software does not maintain view column data in its catalog for
views with more than 50 columns, the query that SAS/ACCESS issues against table
SYSIBM.SYSCOLUMNS to retrieve user view column information returns an SQLCODE of 100,
indicating that the row was not found.
Note: with Teradata RDBMS V2R2, and above, of there is no limit to the number of columns allowed.
5 SAS does not allow multi-statement requests. When using SAS under either MVS or VM, if you have an
extended IBM keyboard, you can use the } symbol to denote an end-of-statement delimiter. TS/API
translates the } symbol to a semicolon ; prior to sending the multi-statement request to the Teradata
RDBMS.
6 The logon statement issued from the command line of SAS is meaningless because the SAS System
establishes a Teradata RDBMS connection for each statement, executes the statement (in this case the
LOGON), and terminates the connection.
7 TS/API supports the SAS FSVIEW, FSBROWSE procedure as well as the browse mode of SAS FSVIEW.
Use the browse capability when you are accessing a table or view that is not updatable.

NATURAL
NATURAL
NATURAL is a fourth-generation language tool from Software AG. NATURAL

allows users to access DB2 and other databases either by writing programs
using the NATURAL programming language or interactively through native
SQL commands. Through TS/API, NATURAL can be used to access
information stored in Teradata RDBMS database.
The following table shows some NATURAL issues.

NATURAL
NATURAL Issues
Step Action
1 Performance considerations with TS/API and the Teradata RDBMS, with

respect to how certain NATURAL statements are used, are very much in line
with those when NATURAL is used with DB2. NATURAL users may refer to
the NATURAL documentation for details. Please note that performance
considerations can be even more crucial when using NATURAL with TS/API,
since Teradata RDBMS tables tend to be quite large.
Try to avoid sorting, counting, and so forth on the client. Instead, use
statements so that functions such as sorting and counting are performed on
the Teradata RDBMS, where they can be done more quickly and efficiently.
Having functions such as counting and sorting performed on the Teradata
RDBMS also serves to free up client resources to perform other tasks.
Some important items to remember are:
For NATURALs FIND, READ, and HISTOGRAM statements, the WITH
clause is evaluated by the Teradata RDBMS; the WHERE clause by
NATURAL. This means that for large tables, using the WHERE clause
should be avoided.
The *COUNTER system variable available with the NATURAL FIND and
READ statements should be avoided when retrieving a large number of
rows. When the *COUNTER system variable is used it is updated for each
row returned. The FIND NUMBER statement should be used instead
when dealing with large tables, as it makes only one call to the Teradata
RDBMS. The FIND NUMBER statement can be used when you are simply
interested in determining the number of rows that meet your selection
criteria.
For sorting, the FIND...SORT BY, READ LOGICAL and SELECT...ORDER
BY statements are recommended, since the rows will be sorted by the
Teradata RDBMS before they are returned to NATURAL. The NATURAL
SORT statement should be avoided when you are sorting a result table
with a large number of rows, as the sorting will be done less efficiently on
the client and will needlessly tie up client resources.
2 On the NATURAL Interactive SQL for DB2 screen, free form Teradata RDBMS
SQL may be executed. It must be noted, however, that NATURAL parses the
SQL, and assumes the end of input when it encounters a semicolon.
Therefore, a user who is executing a multi-statement request, a CREATE
MACRO statement, or other statement which includes more than one
semicolon must use the TS/API alternate delimiter of } in place of
semicolons, up to the last semicolon in the statement. Otherwise NATURALs
parser, upon encountering the first of several semicolons in the SQL
statement(s), will assume end of input and will truncate the rest of the SQL
input. TS/API will automatically translate all } symbols to semicolons prior
to sending the statement(s) to the Teradata RDBMS.

RXSQL
RXSQL
RXSQL is the SQL/DS Procedures Language Interface that allows REXX

programs to access the SQL/DS relational database management system.
SQL can be imbedded in the procedural languages such as Assembler, C,
COBOL, FORTRAN, PL/I and RPG. RXSQL extends the support of REXX
programs and allows SQL statements to be used in RXSQL request contained in
REXX programs. Through TS/API, RXSQL can be used to access information
stored in the Teradata RDBMS.
RXSQL Issues
The following table shows some RXSQL issues.
Step Action
1 RXSQL supports the use of both Dynamic and Extended Dynamic SQL
statements. Since Teradata RDBMS does not support the Extended Dynamic
SQL, TS/API has to emulate it. Instead of creating a package, TS/API creates
a macro with the same name, plus the section number. For example, if RXSQL
issues an Extended Dynamic PREPARE for a SELECTstatement which is to be
stored in section 1 of package SQLPKG, and the second Extended Dynamic
PREPARE for an UPDATE statement for section 2 on the same package,
TS/API will create two macros named SKLPKG_1 and SQLPKG_2. The first
macro will contain the SELECT atatement and the second will contain the
UPDATE statement.
During the execution of the Extended Dynamic SQL, TS/API will find the
appropriate macro and execute it. This process emulates the Extended
Dynamic SQL with the following limitations:
Positional UPDATE and DELETE cannot be executed with the Extended
Dynamic SQL. This is because TS/APIs updatable cursor feature does
not support Extended Dynamic SQL.
The DROP PACKAGE and DROP STATEMENT commands are ignored
by TS/API since no packages exist on the Teradata RDBMS. There is no
need to drop the macros, since TS/API always executes REPLACE
MACRO command. Therefore, the old macros are replaced.
2 Before running RXSQL with TS/API in VM/CMS, always set the following
VM/CMS system option:
SET STORCLR ENDCMD
See Chapter 2: TS/API Installation and Customization for additional
details.

DB2 Trace Facility
DB2 Trace Facility
For MVS/TSO DB2 applications, the DB2 Trace Facility is used to collect
information about the behavior of a DB2 application when it is used with DB2
itself. After collecting trace information and determining the behavior of a DB2
application when it is used with DB2, you can compare to it the behavior of the
DB2 application when it is used with TS/API.
Use the DB2 Trace Facility in the following cases:
You encounter a problem with DB2 application used with TS/API.
You attempt to use an uncertified DB2 application with TS/API.
Turning the DB2 Trace Facility On and Off

Set a flag in the TS/API standalone module (stub), BBIACAB, to turn the DB2
Trace Facility on and off.
When the DB2 Trace Facility is active, any DB2 calls which are directed to a DB2
subsystem are traced and written to file TRACE.
See Chapter 5: Problem Management for details.

Chapter 2:
TS/API Installation and Customization
This chapter contains the following information:

Control Flow Under TSO
Accessing TS/API Load Modules Under TSO and in Batch
Control Flow Under CICS
Accessing the TS/API Load Modules Under CICS
Setting Up the Teradata RDBMS for TS/API
Installing ISV Products
Preparing the ISV Product
The TERABIND Utility
QMF
SAS
NATURAL
RXSQL
Note: The MVS and VM installation sections of the previous Teradata TS/API
Installation Guide are now located in the Teradata Client for MVS Installation
Guide, and the Teradata Client for VM Installation Guide, respectively.

Chapter 2: TS/API Installation and Customization
This section describes how TS/API interacts with application tools under TSO
and in batch.
Vectoring Enabled: Switching Between the Teradata RDBMS and DB2

If you have DB2 installed, TS/API provides a mechanism to switch between the
Teradata RDBMS and DB2 based on the subsystem ID that the application uses
when it connects to DB2. The following steps enable this facility: .
Step Action
1 Use members RECUSRMD and APPUSRMD in <dbcPfx>.PROCLIB
2 Modify the JCL to meet your installation requirements, including the JOB
statement information and high-level qualifiers.
3 Submit the jobs.
When vectoring is enabled, TS/API directs all SQL requests to either the
Teradata RDBMS or DB2, depending upon the subsystem ID that the
application supplies at runtime:
If The Subsystem ID... Then...
begins with TD the request is routed to the Teradata RDBMS.
does not begin with TD the request is routed to DB2.
Figure 2-1 shows the control flow when vectoring is enabled.

Figure 2-1 Vectoring Enabled
Application
Tool
TS/API's
DSNALI
SSID= No
TDxx
Yes
DB2's
TS/API DSNALI
(Linked as TIBMALI)
Teradata
DB2
DBS
JH01A001
Vectoring Disabled: Teradata RDBMS Access Only

If you dont have DB2 installed, or if you dont want the vectoring to take place,
you may install TS/API with vectoring disabled. For that purpose, Teradata
provides its own TIBMALI module, which should be located in the TS/API
load library.
When vectoring is disabled and an application specifies a subsystem ID that
doesnt start with TD characters, TS/APIs copy of TIBMALI is invoked and it
returns to the application with an error code indicating that DB2 is not active.
Figure 2-2 shows the control flow when vectoring is disabled.
During the original installation of TS/API, vectoring is disabled since TS/APIs
copy of TIBMALI is in the load library.
If you customized TS/API to enable vectoring and subsequently decide to
disable it, do the following:
1 Execute member RSTUSRMD in <dbcPfx> PROCLIB.
2 See the comments in member TDUA001 in <dbcPfx>.SAMPLIB.

Figure 2-2 Vectoring Disabled
TSO
Application
Tool
TS/API's
DSNALI
SSID= No
TDxx
Yes
TS/API's
TS/API
TIMBALI
DB2
error code
Teradata
DBS
JH01A002

Accessing TS/API Load Modules Under TSO and in Batch
Accessing TS/API Load Modules Under

TSO and in Batch
Modules from the APILOAD library must be accessible whenever a program

using TS/API is executed. There are several possible ways to make APILOAD
modules accessible:
Include APILOAD in the system link library list.
Include APILOAD modules in the link pack area (LPA).
Concatenate APILOAD to JOBLIB or STEPLIB.
Concatenate APILOAD to a task library (such as ISPLLIB).
Note:
If... Then...
you include APILOAD modules do not include BBIACAB because it

in the LPA is a data-area-only and TS/API
writes to that area during runtime.
you have DB2 installed you must place APILOAD ahead of

any DB2 load library containing the
DSNALI load module.
you place APILOAD in a you will expose TS/API to all DB2

generally accessible area (for applications, which might impact
example, the system library list, those applications not intended to
LPA, or STEPLIB for TSO), use TS/API.
TS/API load libraries cannot be allocated using the LIBDEF command.

For more flexibility, concatenate APILOAD to your JOBLIB/STEPLIB list for
batch applications and to a task library list, such as ISPLLIB, for TSO
applications (using application starting CLIST). Also, APPLOAD should be
accessible during runtime.
See Chapter 3: Session Management for instructions on allocating the
DBCLOGON file.

In the CICS environment, application tools (for example, QMF and NATURAL)
use DB2s CICS Attach Facility module (DSNCLI) to access DB2. TS/API
replaces that module with its own DSNCLI, which routes all requests to
TS/API.
Figure 2-3 depicts the control flow.
Figure 2-3 Flow Control in CICS
CICS Application
Tool
TS/API's
DSNALI
TS/API
Teradata
DBS
JH01A003
Note: TS/API 3.1 does not support vectoring to DB2 under CICS.

Accessing the TS/API Load Modules Under CICS
Accessing the TS/API Load Modules

Under CICS
CICS must be able to access APILOAD and the CLIv2 CICS load library
(CXILOAD) in order to run transactions using TS/API. Therefore, concatenate
these libraries to the DFHRPL list. The APILOAD library should be placed
ahead of the DB2 load library (if any).
Note: For details on how to customize your CICS, see Teradata Client for MVS
Installation Guide and Teradata Client for VM Installation Guide.
Further Instructions
For instructions on allocating the DBCLOGON file see Chapter 3: Session
Management.
After completing the TS/API installation, for instructions on how to set up the
Teradata RDBMS to enable the TS/API functions see Setting Up the Teradata
RDBMS for TS/API on page 2-8, and Installing ISV Products on page 2-11.

Setting Up the Teradata RDBMS for

TS/API
This section describes the creation and initialization of the Teradata RDBMS
databases, tables, and views used by TS/API. Familiarity with BTEQ is
required. For detailed information on BTEQ, see Teradata BTEQ Reference.
Teradata RDBMS Disk Space Requirements

TS/API requires 2Mb of permanent database space on the NCR hardware
platform for storage of special tables, views, and macros.
Additional permanent database space may be required in order to use
supported program products.
BTEQ Scripts
The BTEQ scripts are supplied with a filetype of DBCSQL for VM, and are
included in <dbcPfx>.CLIST for MVS. Running these scripts requires full
Teradata RDBMS authority. Userid DBC or SYSADMIN is recommended
because these userids have Teradata RDBMS system authority. The BTEQ
scripts are subject to change depending on the release level of TS/API, and re-
installation may be required with new TS/API releases.
If... Then...
you have more than one Teradata you must run these scripts on each Teradata
RDBMS RDBMS that is used with TS/API.
you have already run these scripts you do not need to repeat the process on VM
from MVS and vice versa. The contents of these scripts are
identical in both VM and MVS.
TS/API Databases
If you are installing TS/API for the first time, you must run BTEQ with the
following scripts in the order shown. Run these scripts under the Teradata
RDBMS userid DBC or SYSADMIN:
SYSDBSCreates TS/API databases SYSAPI, SYSIBM, SYSTEM, SQLDBA.
If the SYSADMIN database does not have sufficient space, change the line
in the SYSDBS BTEQ script that modifies the SYSADMIN PERMSPACE
value. Enter the correct amount of PERMSPACE and remove the comment
indicator before running the script.
SYSAPICreates TS/API system tables.

SYSIBMCreates DB2 system catalog views and tables.

SYSTEMCreates SQL/DS system catalog views and tables.
SQLDBACreates SQL/DS administration database.
SQLDSAMPCreates SQL/DS sample tables.
If you have TS/API already installed and are migrating to a new release of
TS/API, run the following scripts:
SYSIBMReplaces DB2 catalog views. Leaves existing tables and data intact.
SYSTEMCreates SQL/DS system catalog views and tables.
SQLDBACreates SQL/DS administration database.
SQLDSAMPCreates SQL/DS sample tables.
Note: SYSIBM and SYSTEM do not have DROP TABLE statements. Therefore,
these scripts will result in non-0 return codes because the CREATE TABLE will
fail (the tables already exist).
The SYSAPI Database

TS/API uses the SYSAPI database in VM, MVS/TSO, and MVS/CICS; it is
identical in all environments.
The SYSAPI database contains one table for null mapping used by TS/API. The
SYSAPI DBCSQL BTEQ script on the distribution disk creates this table in this
database.
The SYSIBM Database

TS/API uses the SYSIBM database only under MVS. If you plan to use TS/API
only in VM, you may omit this step. However, we recommend that you install
the SYSIBM database to complete your installation of TS/API and allow for
future use of both VM and MVS.
The SYSIBM database contains views and tables TS/API uses to emulate the
DB2 system catalog tables. Run the SYSIBM DBCSQL BTEQ script to create the
required views and tables.
The SYSTEM Database

TS/API uses the SYSTEM database under VM only. If you plan to use TS/API
only in MVS, you may omit this step. However, we recommend that you install
the SYSTEM database because it completes your installation of TS/API and
allows for future use of both MVS and VM.
The SYSTEM database contains views and tables TS/API uses to emulate the
SQL/DS system catalog tables. Run the SYSTEM DBCSQL BTEQ script to
create the required views and tables.

The SQLDBA Database

TS/API uses the SQLDBA database only under VM. If you plan to use TS/API
only in MVS, you may omit this step. However, we recommend that you install
the SQLDBA database because it completes your installation of TS/API and
allows for future use of both MVS and VM.
The SQLDBA database contains views and tables TS/API uses to emulate the
SQL/DS SQLDBA catalog tables. Run the SQLDBA DBCSQL BTEQ script to
create the required views and tables.
Additional Sample Tables in the SQLDBA Database

TS/API uses the tables in the SQLDSAMP script only under VM. If you plan to
use TS/API only in MVS, you may omit this step. However, we recommend
that you install the SQLDBA database because it completes your installation of
TS/API and allows for future use of both MVS and VM.
The SQLDSAMP DBCSQL BTEQ script contains several sample tables that
SQL/DS uses. These tables are loaded into the SQLDBA database.
For more details on the contents and use of the SYSTEM, SQLDBA, and
SYSIBM databases, see the appropriate IBM manual. For more information on
how the SYSTEM, SQLDBA, and SYSIBM databases are built on the Teradata
RDBMS, see Appendix B: TS/API Catalog Emulation.

This section describes the installation of Independent Software Vendor (ISV)

products on MVS/TSO, in batch, MVS/CICS, and VM for use with TS/API.
Open Support
The TS/API 3.3.0 release provides an open support of DB2 and SQL/DS
applications in MVS/TSO, in batch, MVS/CICS, and VM/CMS environments.
Certified Support - 03.04.00

TS/API 03.04.00 provides certified support for the following ISV program
products:
Under MVS/TSO and in batch:
QMF 2.4, QMF 3.1.0, QMF 3.1.1, QMF 3.2.0, and QMF 3.3.0
SAS 6.07, SAS 6.08 and SAS 6.09
NATURAL V2.2.5
Under MVS/CICS:
QMF 3.1.0, QMF 3.1.1, QMF 3.2.0.
NATURAL V2.2.5
Under VM/CMS:
QMF 2.4, QMF 3.1.0, QMF 3.1.1, QMF 3.2.0, and QMF 3.3.0
SAS 6.07, SAS 6.08, and SAS 6.09
RXSQL 3.4.0
This guide provides general considerations for installing ISV products and the
operating instructions for installing each of the certified products.
Certified Support - 03.04.00

TS/API 03.04.00 provides certified support for the following ISV program
products:
Under MVS/TSO and in batch:
QMF 6.1.0

This section discusses preparing an ISV product, which is designed to work

with DB2 or SQL/DS, to run with TS/API.
The major steps are:
Link-editing the program (MVS only)
Converting the applications embedded static SQL statements into Teradata
RDBMS macros.
Setting up the Teradata RDBMS for the product.
Link-editing the Program

This step may be required only on MVS. If, during its normal installation, an
ISV product was link-edited with one of the DB2s Attachment Facilities
(DSNALI under TSO or DSNCLI under CICS) that part of the products
installation should be repeated using TS/APIs replacement modules with the
same names.
Converting Static SQL Into Teradata RDBMS Macros

ISV products designed to work with DB2 or SQL/DS usually contain static SQL
statements:
For DB2 products, vendors usually ship Database Request Modules
(DBRMs) that contain these statements extracted from the product.
For SQL/DS products, vendors usually ship the source files, which contain
static SQL statements. These files should be preprocessed by the SQL/DS
preprocessor during product installation to store static statements as
packages (access modules) into the SQL/DS system. For more
information, see the installation guide for the particular product.
In order for an ISV product to operate with TS/API, static SQL statements must
be converted into functionally equivalent Teradata RDBMS macros and stored
in the Teradata RDBMS. Conversion could be done manually using
corresponding DB2 DBRMs or SQL/DS source files.
To help convert DBRMs into macros, Teradata provides the TERABIND utility
(description follows). There is no such utility for SQL/DS source files. Also, for
certified supported products, Teradata, when possible, does the conversion and
provides BTEQ scripts containing macro creation statements. For details, see
the section on installation instructions for the specific certified product.

Setting Up the Teradata RDBMS

The macros, supporting static SQL statements, should be stored in the Teradata
RDBMS. Teradata may provide on the TS/API installation tape the BTEQ
scripts for creating these macros, depending on the product and environment.
In addition, some products (for example, QMF) require specific databases,
tables, and views to be created on the DBMS that the product will be accessing.
Creation of such objects usually is a part of the normal installation procedure
for an ISV product. All such database objects should be created on the Teradata
RDBMS to support operation of the product with TS/API. For certified
products, Teradata provides BTEQ scripts on the installation tape for creation
of such objects.
For more information on static SQL support, see Chapter 6: Static SQL and
System Catalog Support.

TERABIND is a TS/API utility that converts static SQL statements contained in

DB2 Database Request Modules (DBRMs) into functionally equivalent Teradata
RDBMS macros. In addition, TERABIND creates required INSERT statements
to register a plan name and define cursors names to TS/API.
TERABIND takes the ISV products DBRM(s) as input and produces the
following:
An output file, called BTEQOUT, which has all the emulation macro
definitions for the statements found in the input DBRM(s). The BTEQOUT
file also contains appropriate INSERTs into the SYSIBM.SYSPLAN table to
register the plan name with TS/API, INSERTs into SYSIBM.SYSCURS to
define the cursors to TS/API, and GRANTs to public to execute the macros.
The SYSPRINT file, which keeps a summary trace of how each DBRM
statement was processed and the TERABIND DEBUG trace information if
the DEBUG parameter was used when invoking TERABIND.
JCL Example
The following JCL example shows how to execute the TERABIND utility:
// JOBCARD
//*
//* BUILD TS/API EMULATION MACROS FROM TWO SAS DBRMS
//*
//*
//S1 EXEC PGM=IEFBR14
//DDI DD DSN=<userid>.SASMACRO.BTEQ,
// UNIT=SYSDA,SPACE=(TRK,0),
// DISP=(MOD,DELETE,DELETE)
//*
//S2 EXEC PGM=TERABIND,PARM='<dbname>.<planname>'
//STEPLIB DD DSN=<dbfpfx>.APILOAD,DISP=SHR
//INDBRM DD DSN=SAS0608.DBRM(SASDB2E),DISP=SHR
// DD DSN=SAS0608.DBRM(SASDB2L),DISP=SHR
//OUTBTEQ DD DSN=<userid>.SASMACRO.BTEQ,UNIT=SYSDA,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=80),
// SPACE=(TRK,(1,1),RLSE),DISP=(,CATLG,CATLG)
//SYSPRINT DD SYSOUT=*,
// DCB=(RECFM=FBA,LRECL=133,BLKSIZE=6118)
//SYSUDUMP DD SYSOUT=*,
// DCB=(RECFM=FBA,LRECL=133,BLKSIZE=6118)
//*
//S3 EXEC PGM=IEBGENER,COND=EVEN
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=<userid>.SASMACRO.BTEQ,DISP=SHR

//SYSUT1 DD SYSOUT=*
//SYSIN DD DUMMY
//
Usage Notes
DBRMs
DBRMs produced by DB2 V2R3 and below are supported.
DB2 Parser
The DB2 parser does not support DB2 special registers in SELECT statements
without a FROM or INTO clause. Consequently, some static SQL SELECT
statements are coded inefficiently with an unnecessary FROM clause.
An example from a QMF DBRM follows:
SELECT USER FROM SYSIBM.SYSTABAUTH
This statement returns the USER for each row in table SYSIBM.SYSTABAUTH.
Since TERABIND converts such a statement directly from a DB2 DBRM
statement into a Teradata RDBMS macro, with the FROM clause intact, you
may remove unneeded FROM clauses from such statements.
SQL
TERABIND produces SQL that sometimes is not Teradata RDBMS SQL-
compatible. For instance, if CURRENT SERVER is in a SELECT statement, the
Teradata RDBMS returns a syntax error.
Edit the TERABIND output for such problems and map the SQL syntax to
comply with Teradata RDBMS SQL. See Chapter 7: Command Syntax for
details.
User-specified Parameters
TERABIND accepts two user-specified parameters.
Provide the required input parameter of <DBNAME>.<PLANNAME>. You
may optionally specify a second keyword input parameter of DEBUG as well.
Use of the DEBUG parameter causes TERABIND to produce diagnostic
information during the translation of SQL statements to CREATE MACRO
statements. Both input parameters are nonpositional and case insensitive. For
example, all of the following combinations of input parameters are valid:
PARM=DEBUG Q.QMF310
PARM=Q.QMF310 DEBUG
PARM=Q.QMF310
PARM=DeBUG q.Qmf310

TERABIND checks the number of provided input parameters and validates

them. For example, the following specification causes TERABIND to stop
execution and issue an error message:
PARM=Q.QMF310 DEEBUGG
Record Format
TERABIND checks the format of records in DBRM and terminates if an invalid
DBRM record is encountered. For example, TERABIND verifies that the string
"DBRM" appears as the first four characters of the first record of the DBRM and
validates the legality of the DBRM name extracted from columns 17 through 25
of the first record.
The following sections describes the instructions to install each of the certified
supported products listed in the beginning of this chapter.

QMF
QMF
QMF does not need to be linked-edited with DSNALI or DSNCLI, and Teradata
provides all necessary BTEQ scripts on the installation tape, so the only
required step for QMF is to set up the Teradata RDBMS using these scripts.
Running the BTEQ scripts accomplishes the following:
Creates Q database on the Teradata RDBMS.
Creates QMF tables, views, and macros in the Q database.
Inserts the QMF plan name into the SYSIBM.SYSPLAN table (MVS only),
and INSERTs appropriate cursor names into SYSIBM.SYSCURS.
Grants the appropriate authority to the above objects.
Run BTEQ Scripts

You have to run BTEQ scripts only once, either from VM or from MVS, to
enable QMF operation on all supported environments (MVS/TSO, batch,
MVS/CICS, and VM/CMS)
Note: Run BTEQ scripts to setup TS/API, first. Run the QMF BTEQ scripts
after installing the SYSIBM and/or SYSTEM BTEQ scripts.
Before running the BTEQ scripts, you must provide the installation-specific
information in JCL (for MVS) or in an EXEC (for VM).
On MVS, the scripts are found in the <dbcPfx>.CLIST control library, and the
BTEQ sample job is in the <dbcPfx>.SAMPLIB. On VM, the scripts are found
on the client utilities installation disk.
IF... THEN...
you are installing QMF you must run the following scripts in the given order:
for TS/API QOBJNEWDefines Q database and QMF system
tables.
QReplaces QMF system views and macros.
you intend to use QMF you must run:

with Kanji support QKANJIUpdates QMF system tables for Kanji
support.

QMF
Storing QMF Objects
CAUTION: The following Teradata RDBMS tables store QMF objects
Q.COMMAND_SYNONYMS
Q.DSQ_RESERVED
Q.ERROR_LOG
Q.OBJECT_DATA
Q.OBJECT_DIRECTORY
Q.OBJECT_REMARKS
Q.PROFILES
Q.RESOURCE_TABLE
The QOBJNEW and Q BTEQ scripts then rebuild the QMF object tables,
destroying any data contained in them.
Backing Up QMS Object Tables

If previous releases of TS/API have been installed in your system and if QMF
was previously used on your Teradata RDBMS, use the BTEQ EXPORT
command to back up the QMS object tables to host files before executing the
QOBJNEW and Q BTEQ scripts.
Note: If you do not back up these tables, you may lose all QMF stored objects.
Importing the QMS Object Tables

After successfully executing the QOBJNEW and Q BTEQ scripts, use the BTEQ
IMPORT command to import the QMS object table backup host files to the
newly created QMS object tables.
See Teradata BTEQ Reference for information about using the BTEQ EXPORT
and IMPORT commands. Also, see the discussion of the QMS export and
import functions in the QMF Release Dependencies subsection later in this
chapter.
Note: With TS/API 3.0 or later, the COMMAND_SYNONYMS table is actually

represented by the following tables to support different environments:
Q.COMM_SYNS_TSO_EEnglish command synonyms in MVS/TSO.

Q.COMM_SYNS_CMS_EEnglish command synonyms in VM/CMS.
Q.COMM_SYNS_CMS_KKanji command synonyms in VM/CMS.
Q.COMM_SYNS_CICS_EEnglish command synonyms in MVS/CICS.

QMF
Q.COMM_SYNS_NULL_EEnglish command synonyms with QMF 2.4.

Q.COMM_SYNS_TSO_KKanji command synonyms in MVS/TSO.
Q.COMM_SYNS_CIC_KKanji command synonyms in MVS/CICS.
QMF Command Synonyms

TS/API is delivered with default QMF command synonym definitions. If
you have added QMF command synonym definitions beyond the delivered
default definitions to your current Q.COMMAND_SYNONYMS table, it is
your responsibility to make sure they get copied to the new QMF command
synonyms table for your QMF environment.
Sample Tables
Q.APPLICANT
Q.INTERVIEW
Q.ORG
Q.PARTS
Q.PRODUCTS
Q.PROJECT
Q.SALES
Q.STAFF
Q.SUPPLIER
Sample Kanji Tables

Q.APPLICANTK
Q.INTERVIEWK
Q.ORGK
Q.PARTSK
Q.PRODUCTSK
Q.PROJECTK
Q.SALESK
Q.STAFFK
Q.SUPPLIERK

QMF
QMF Suppporting BTEQ Scripts

As a result of running QMF supporting BTEQ scripts, the following tables and
views should appear in the Q database:
System Tables
Q.COMM_SYNS_CICS_E
Q.COMM_SYNS_CMS_E
Q.COMM_SYNS_TSO_E
Q.DSQ_RESERVED
Q.DSQEC_ALIASES
Q.DSQEC_COLS_LDB2
Q.DSQEC_COLS_RDB2
Q.DSQEC_COLS_SQL
Q.DSQEC_QMFOBJS
Q.DSQEC_TABS_LDB2
Q.DSQEC_TABS_RDB2
Q.DSQEC_TABS_SQL
Q.DSQIOLST_AI_VIEW
Q.DSQIOLST_AU_VIEW
Q.DSQIOLST_QT_VIEW
Q.DSQIOLST_TB_VIEW
Q.COMM_SYNS_CICS_E
Q.COMM_SYNS_CMS_E
Q.COMM_SYNS_TSO_E
Q.DSQ_RESERVED
Q.DSQEC_ALIASES
Q.DSQEC_COLS_LDB2
Sample Tables
Q.APPLICANT
Q.INTERVIEW
Q.ORG

QMF
Q.PARTS
Q.PRODUCTS
Q.PROJECTS
Q.SALES
Q.STAFF
Q.SUPPLIER
QKANJI Tables/Views
If QKANJI is run, the following tables/views should also appear:
System Tables
Q.COMM_SYNS_CICS_K
Q.COMM_SYNS_CMS_K
Q.COMM_SYNS_TSO_K
Sample Tables
Q.APPLICANTK
Q.INTERVIEWK
Q.ORGK
Q.PARTSK
Q.PRODUCTSK
Q.PROJECTSK
Q.SALESK
Q.STAFFK
Q.SUPPLIER
These views and tables are compatible with those supplied on the IBM QMF
installation tape. For a description of these views and tables, see the IBM QMF
Reference Manual.
RDBMS Macros Created

In addition, required Teradata RDBMS macros are created. On MVS platform,
QMF plan names are registered in the SYSIBM.SYSPLAN table, and required
cursors names are INSERTed into the SYSIBM.SYSCURS table.
If you plan to use QMF with several Teradata DBSs, you must run these BTEQ
scripts on each Teradata RDBMS.

QMF
QMF Release Dependencies

The internal format used to store QMF objects (OBJECTLEVEL) is not always
consistent between QMF releases. Therefore, a QMF stored object created in
one QMF release may not be immediately accessible using a different QMF
release.
To prevent QMF stored object access problems from occurring following QMF
release migrations, perform the following:
QMF EXPORT each stored object using the release in which the object was
created.
QMF IMPORT each stored object using the QMF migration release.
Executing these steps converts the internal format of the stored objects to a
format compatible with the QMF migration release level.
QMFs failure to access stored objects created under a different release is not a
TS/API problem. Contact your IBM representative for more information
concerning QMF.

SAS
SAS
TS/API provides certified support of SAS in the MVS/TSO, batch, and

VM/CMS environments.
There is no need to link-edit SAS module(s) with the DSNALI module and
Teradata provides the necessary BTEQ scripts on the TS/API installation tape.
These scripts are included in the SYSTEM and SYSIBM DBCSQL scripts and
accomplish the following:
Register the SAS plan name in the SYSIBM.SYSPLAN table (MVS only)
Define cursors to TS/API

NATURAL
NATURAL
TS/API provides certified support of Software AGs NATURAL release 2.2.5 in

the MVS/TSO and MVS/CICS environments.
TS/API supports NATURAL programs which use only dynamic SQL.
To install NATURAL, use JCL delivered by Software AG to link-edit
NATURAL with TS/APIs DSNALI (for TSO) or TS/APIs DSNCLI (for CICS).
IF... THEN...
you have a DB2 license use DB2s version of DSNTIAR module in these link-edit
jobs so you can get full error messages formatting.
you dont have a DB2 use TS/APIs version of DSNTIAR to link-edit

license successfully.
Teradata provides the necessary BTEQ scripts on the TS/API installation tape.
These scripts are included in the SYSIBM DBCSQL script and accomplish the
following:
Register the NATURAL plan name in the SYSIBM.SYSPLAN table
Define cursors to TS/API

RXSQL
RXSQL
TS/API provides certified support of IBMs RXSQL release 3.4.0 in VM/CMS

environment.
It is not necessary to link-edit the RXSQL module(s) with the DSNALI
modules. Teradata provides the necessary TS/API BTEQ scripts on the client
utilities installation tape.
The only step required for REXX/RXSQL is to set the VM/CMS system option,
STORCLR, to ENDCMD (the default is ENDSVC). Include the following line in
your TS/API setup EXEC:
SET STORCLR ENDCMD
After the TS/API application in finished, you can reset the STORCLR option
back to its original value.

RXSQL

Chapter 3:
Session Management

Specifying the Teradata RDBMS
Providing Logon Information
Each subject is covered for MVS/TSO, batch, MVS/CICS, and VM/CMS.

Chapter 3: Session Management
The Teradata RDBMS connects to a client through the TDP, which is identified
by its tdpid. If more than one Teradata RDBMS is connected to a client, there
may be several tdpids you can use.
An MVS tdpid can take the form:
TDxy
where x is one of the following letters:
P
Q
R
S
and y is any single digit or uppercase letter.
A VM tdpid takes the form:
TDxy
where x and y are any single digits or uppercase letters.
In all the supported environments, you can specify the desired tdpid in the
DBCLOGON file/table, as part of your LOGON string (see later in this
chapter). In addition, under MVS/TSO, in batch, and under VM/CMS, you
may omit the tdpid portion of the LOGON string and specify the tdpid as
described below:
Under MVS/TSO and Batch

When a program is invoked that uses DB2, a parameter specifies the DB2
subsystem name (SSN) to be accessed. If the SSN starts with TD, TS/APIs Call
Attach Facility (DSNALI) directs requests to TS/API. See Teradata Client for
MVS Installation Guide and Teradata Client for VM Installation Guide for more
information on vectoring. TS/API then uses that name as a tdpid to be
accessed, unless it is overridden by a tdpid provided in LOGON string.

Under VM/CMS
Before any program that uses SQL/DS is invoked the SQLINIT EXEC should
be executed, which sets up a default database for the user (DBNAME
parameter). Executing SQLINIT creates an ARISRMBT MODULE, containing
the default database name, on the users A-disk. TS/API gets the database
name from that module and uses it as a tdpid to be accessed, unless it is
overridden by a tdpid provided in the LOGON string. The following is an
example of using the SQLINIT EXEC to specify TDP0 as the tdpid:
EXEC SQLINIT DBNAME(TDP0)
To switch tdpids or to switch back to SQL/DS, re-execute SQLINIT with the
proper value of the DBNAME parameter.
Note: As mentioned previously, if a tdpid is specified in the LOGON string in

the DBCLOGON file, it overrides the value specified in other sources.

In order to log on to the Teradata RDBMS, two other values are required
besides the tdpid:
The Teradata RDBMS userid
The password associated with the Teradata RDBMS userid
These logon parameters can be specified in one of two ways:
Through a DBCLOGON file/table
Through a TDP logon exit
The DBCLOGON File/Table

Under MVS/TSO, batch and VM/CMS TS/API uses a DBCLOGON file to
obtain logon information. The DBCLOGON file should be allocated to a
sequential data set with the following characteristics:
A record format of F or FB
A record length of 80
A block size that is any multiple of 80
The DBCLOGON file may contain any number of TS/API directives, including
the LOGON command. (See Chapter 7: Command Syntax for a list of TS/API
directives.) The LOGON command provides the Teradata RDBMS userid,
password, and, optionally, the tdpid. The LOGON command in the
DBCLOGON file is formatted like the LOGON command in BTEQ (without the
leading dot):
LOGON [tdpid/]userid,password;
Note: The semicolon at the end of the LOGON command is optional. If the
tdpid is specified, it overrides the value of the tdpid passed to TS/API by other
means. On VM, if the first command from the application is an explicit
CONNECT or LOGON, the logon information is built from that command,
rather than from the DBCLOGON file.
To allocate the DBCLOGON file:

Under TSO, use a DDNAME of DBCLOGON as follows:
ALLOCATE DD(DBCLOGON) DA( logon.dataset.name) SHR
In batch, use a ddname of DBCLOGON as follows:
//DBCLOGON DD DSN=logon.dataset.name,DISP=SHR
Under VM/CMS, use a ddname of DBCLOGON as follows:
FILEDEF DBCLOGON DISK logon_fn logon_ft logon_fm

Note: Teradata recommends using userid.DBCLOGON as the

logon.dataset.name in MVS, and userid DBCLOGON A0 as logon_fn,
logon_ft, and logon_fm on VM.
DBCLOGON must be allocated before you use any program that invokes
TS/API. If it is not allocated or if it does not contain a LOGON command,
TS/API performs an implicit logon to the Teradata RDBMS. The TDP passes an
implicit logon request to the logon exit if one is installed (see details later in this
chapter).
Under CICS
TS/API uses the following tables in CICS to obtain logon information for a
transaction:
BBIRCT (Resource Control Table in CICS)
Each entry in BBIRCT contains one or more transaction ids and the
corresponding plan name. It also specifies what name associated with a
CICS transaction TS/API should use to look up the DBCLOGON table in
CICS for that transactions logon information. Each transaction that uses
TS/API must be defined in the BBIRCT table in CICS.
DBCLOGON
Each entry in the DBCLOGON table in CICS contains a key field, which is
used during the search, and an information field that contains TS/API
directives, including a LOGON command.
Using Macros for BBIRCT and DBCLOGON

TS/API provides macros for creating and updating the BBIRCT and
DBCLOGON tables in CICS. These macros support the use of the table
generation procedure (DFHAUPLK or DFHAUPLE). The following are the
instructions on use of these two macros:
BBIRCT Macro
Use the following statements to build the BBIRCT table in CICS:
BBIRCT TYPE=INITIAL
BBIRCT TYPE=ENTRY,
TXID=(<txid1>,<txid2>,<txid3>, ...),
PLAN=<plan_name>,
AUTH=(<auth_type_list>)
BBIRCT TYPE=ENTRY
TXID=(<txid1>, <txid2>, <txid3>, ...),
PLAN=<plan_name>,
AUTH=(<auth_type_lists)
...
BBIRCT TYPE=FINAL

The TYPE=INITIAL macro must be specified first. The TYPE=ENTRY macro

can be specified as many times as needed to identify specific transactions. The
TYPE=FINAL macro is specified last, causing the BBIRCT table in CICS to be
generated.
The <auth_type_list> is a list of up to three keywords: USERID, TERMID, and
TXID, separated by commas (for example, (USERID,TXID),
(TXID,USERID,TERMID), and so forth).
The AUTH parameter controls how TS/API uses the names associated with a
CICS transaction to obtain the authorization key, which is used to search the
DBCLOGON table in CICS for logon information. The key may consist of one
of the following:
The CICS sign-on ID (specified by the USERID keyword)
The terminal ID (specified by TERMID)
The transaction ID (specified by TXID)
The order in which these keywords are specified in the list dictates the order in
which TS/API obtains the information from CICS. For example,
AUTH=(USERID,TERMID) means that TS/API first checks for the CICS sign-
on ID, and only if it is blank, TS/API uses the terminal ID.
AUTH=(TERMID,USERID) causes TS/API to obtain the key in the reverse
order.
When a key is obtained from CICS, it is used to search the DBCLOGON table
for the logon string. If the authorization key is all blanks, or if there is no
matching key field in the DBCLOGON table, TS/API issues an implicit logon
request with a zero-length logon string, relying therefore on the TDP logon exit.
DBCLOGON Macro
Use the following statements to build the DBCLOGON table in CICS:
DBCLOGON TYPE=INITIAL
DBCLOGON TYPE=ENTRY,
AUTHKEY=<auth_key>,
LSTRING=<DBC_logon_string>
DBCLOGON TYPE=ENTRY,
AUTHKEY=<auth_key>,
LSTRING=<DBC_logon_string>
...
DBCLOGON TYPE=FINAL
Note: The <DBC_logon_string> must be surrounded by single quotes. TS/API

directives must be separated from each other by semicolons.
The TYPE=INITIAL macro must be specified first. The TYPE=ENTRY macro

can be specified as many times as needed to identify specific authorization
keys. The TYPE=FINAL macro is specified last, causing the DBCLOGON table
in CICS to be generated.

The <auth_key> is a name of up to 8 characters, which specifies the key of the

record. <DBC_logon_string> is a string of up to 80 characters, which typically
follows a format of:
logon tdpid/userid,password;<other TS/API directives>
The DBCLOGON and BBIRCT tables must both be linked-edited into a library
accessible to CICS for the CICS LOAD command (concatenated to the DFHRPL
library list). The tables also must be defined to CICS in the PPT table. See
Teradata Client for MVS Installation Guide for details.
However, you may leave the DBCLOGON table in CICS empty and use a TDP
exit in conjunction with TS/API to provide CICS security logic. If the
DBCLOGON table in CICS is empty or does not contain a record with a
matching key field, then at logon time, TS/API issues an implicit logon request
with a zero-length logon string.
The JCL for building the BBIRCT and DBCLOGON tables is provided in the
<dbcPfx>.SAMPLIB file. BBIRCT and DBCLOGON macros are provided in
<dbcPfx>.TDPMAC.
The Logon Exit

An alternate way to provide logon information is via the Teradata RDBMS
logon exit. The Teradata RDBMS provides two kinds of logon exits:
TDPLGUX
TDPUAX
Both exits provide a way of executing implicit logons using pre-assigned
userids and passwords. Both exits have levels of security that can prevent
unauthorized use of the Teradata RDBMS.
IF the logon exit... THEN...
authorizes access to the Teradata TS/API is connected under the userid supplied
RDBMS by the logon exit.
is not installed the attempt to perform an implicit logon

fails.
When a logon exit is used, no password is necessary to authorize access to the

Teradata RDBMS. The logon exit can direct the Teradata RDBMS to accept the
implicit logon without an associated password. Since the logon exit is secure,
unauthorized access to the Teradata RDBMS cannot occur.
The advantage of using a logon exit is the security of not having userid and
password information stored in a logon file. The disadvantages are that you
lose the flexibility to control the logon userid and the systems programming
staff must support the installation, modification, and maintenance of the logon
exit.


Chapter 4:
Product Management
TS/API Release 03.04.00 is certified to support the following products:

Query Management Facility (QMF) Versions 2.4, 3.1.0, 3.1.1, 3.2.0, and 3.3.0
The SAS System Versions 6.07, 6.08 and 6.09
NATURAL Version 2.2.5
RXSQL, Release 3.4.0
To certify TS/API with a program product, Teradata performs extensive testing
and quality assurance. Once Teradata certifies a product, it provides technical
support, problem resolution, and software maintenance for TS/API as used
with the certified product.
This chapter describes how to use these certified program products with
TS/API in different environments.
MVS/TSO and Batch Products
VM Products
MVS/CICS Products

Chapter 4: Product Management
TS/API supports QMF, SAS, and NATURAL under MVS/TSO and in the batch
environment.
To ensure connection to the Teradata RDBMS instead of to DB2, verify the
following:
Step Action
1 TS/API libraries are properly accessible.

The TS/API load library (APILOAD) must be accessible to MVS through its
normal library search order (task library, STEPLIB, JOBLIB, link library)
ahead of the DB2 load library (if any). In order to provide a proper
concatenation of the TS/API library, you may need to modify CLIST or JCL
used to invoke a product. The CLIv2 load library (APPLOAD) also must be
accessible to MVS.
2 Your DBCLOGON file, or TDP logon exit, is properly prepared (see the
previous chapter for details).
A proper tdpid in form of TDxx is specified as a subsystem name when
invoking the product. If you specify a tdpid in your DBCLOGON file, it
overrides the value specified by other means.
However, in the MVS/TSO and batch environments, the subsystem name is
used by the TS/API Call Attach Facility to route the requests either to DB2
or to TS/API. (See Chapter 2: TS/API Installation and Customization for
more information about vectoring.) Therefore, even if you have a tdpid in
the LOGON string, the subsystem name still must start with TD to ensure
vectoring the requests to TS/API rather than to DB2.
The following are examples of JCL statements used to invoke QMF and
SAS with a tdpid equal to TDP0:
//SYSTSIN DD *
PROFILE PREFIX(<userid>)
ISPSTART PGM(DSQQMFE) NEWAPPL PARM(M=B,P=QMF310,S=TDP0)
/*
//SYSTSIN DD *
PROC DBLOAD DBMS=DB2;
SSID=TDP0;
SQL COMMIT WORK;
RUN;
/*

Note: For SAS invocation, you must specify DB2 as the DBMS in order to make
a Teradata RDBMS or DB2 connection. With any other value, SAS returns an
undefined DBMS access message. The specified SSID then controls vectoring to
the Teradata RDBMS or DB2.

VM Products
VM Products
TS/API supports QMF, RXSQL, and SAS on VM/CMS. To ensure connection to

a Teradata RDBMS instead of to SQL/DS, verify the following:
Step Action
1 The client utilities installation production minidisk containing TS/API is

properly accessed. It must be accessed ahead of the SQL/DS disk (if any).
You may need to modify products invoking EXEC or PROFILE EXEC in
order to provide proper access to the TS/API disk. The CLIv2 production
minidisk (with CLI TXTLIB) also must be accessible on VM.
2 Your DBCLOGON file or logon exit is properly prepared (see previous

chapter).
3 SQLINIT is executed with the appropriate tdpid before a product is invoked.

MVS/CICS Products
MVS/CICS Products
TS/API supports QMF and NATURAL under MVS/CICS.

To ensure connection to the Teradata RDBMS instead of to DB2, verify the
following:
Step Action
1 The TS/API load library (APILOAD) is properly accessed.

It must be concatenated in the DFHRPL list ahead of the DB2 library (if any).
You will need to modify the CICS startup procedure in order to provide a
proper concatenation of the TS/API library. The CICS CLIv2 load library
(CXILOAD) also must be accessible to CICS.
2 The BBIRCT and DBCLOGON tables in CICS or the logon exit are properly
prepared (see the previous chapter).

MVS/CICS Products

Chapter 5:
Problem Management
This chapter describes the problem management features provided by TS/API.

TS/API Debug Facility
DB2 Trace Facility
The OUTPUT and TRACE Files in MVS
The OUTPUT File in VM
The TSDB Debug Destination in MVS/CICS
Basic Problem Determination (DEBUG SQL)
Advanced Problem Determination (DEBUG ON)
Error Reporting Procedures
Error Translations

Chapter 5: Problem Management
TS/API includes an error resolution feature controlled by the TS/API directive

DEBUG. DEBUG may be issued from any of the following:
The DBCLOGON file
The applications command line
The applications batch input file
The DEBUG directive controls the level of debugging output generated by
TS/API during a session.
The following table describes the four possible settings:
Table 5-1 Debugging Options
Debug Option Description
DEBUG OFF The default. No trace output is generated. You can also use DEBUG
OFF to stop tracing when DEBUG ON or DEBUG SQL is in effect.
DEBUG SQL All SQL statements sent to the Teradata RDBMS are printed. This
command lets you view how TS/API converts DB2 or SQL/DS
SQL into Teradata SQL.
DEBUG ON TS/API routine names are printed on entry and exit, along with the
contents of all relevant data structures. DEBUG ON generates a
large volume of information and is used mainly to trace serious
system problems. Usually, you would send this information to the
Global Support Center (GSC) for problem resolution.
DEBUG PREF Performance monitor information is printed (time spent in CLIv2,

TDP and Teradata RDBMS). See Chapter 7: Command Syntax for
details.
DEBUG Directive
You can issue the DEBUG directive as a standard SQL request (for instance,
from the QUERY PANEL of QMF). This method is particularly useful when you
want to see how TS/API translates your SQL before sending it to the Teradata
RDBMS.

You can also place the DEBUG directive in the DBCLOGON file/table on a
separate line. That way, you can trace TS/API activity even before a connection
is made with the Teradata RDBMS. This is useful in diagnosing errors that
occur in trying to start a program product.
When TS/API receives the DEBUG directive, its internal tracing mechanism is
activated. If the directive was sent from a program product, TS/API returns an
SQLCODE of zero, even though no actual Teradata RDBMS request was
performed.

DB2 Trace Facility
DB2 Trace Facility
TS/API also provides a DB2 trace facility under both MVS/TSO and batch, if
you have DB2 installed and vectoring enabled (see Chapter 2: TS/API
Installation and Customization). The DB2 trace facility pinpoints problems
with a DB2 application used with TS/API.
The trace provides DB2 session information when the application is used with
DB2. After determining how a DB2 application behaves when used with DB2, a
user can compare that to how the DB2 application behaves when used with
TS/API. Determining the differences in behavior, and where these differences
occur, is useful in debugging problems that a DB2 application might have when
used with TS/API.
Note that DB2 call information is traced both for the application passing
information to DB2 to make a request and for DB2 passing requested
information and call status back to the application.
Turning the DB2 Trace On and Off

The DB2 trace can be turned on and off by setting a flag in the TS/API
standalone program BBIACAB. TS/API is delivered with DB2 trace off.
IF you want to... Then...
turn the DB2 use members RECUSRMD and APPUSRMD in

trace on <dbcPfx>.PROCLIB
This reassembles and link-edits program BBIACAB with the DB2
Trace flag set to a value greater than zero, indicating that the DB2
trace is active.
turn the DB2 use member RSTUSRMD in <dbcPfx>.PROCLIB. (See comments

trace off in member TDUA002 or <dbcPfx>.SAMPLIB.)
This restores the program to its original state.
Note: The SMP/E RESTORE must be performed before applying any

subsequent PTFs to the BBIACAB program.

In MVS, debug output is written to the DDNAME OUTPUT, and DB2 trace
output is written to the DDNAME TRACE.
IF OUTPUT... AND... THEN...
is explicitly allocated that allocation is used
is not explicitly allocated a data set named that data set is used.
userid.TSAPI.DEBUG exists
Otherwise, OUTPUT is dynamically allocated to a spool file.

The TRACE file must be explicitly allocated if DB2 trace is turned on. TS/API
doesnt do any implicit allocations for the TRACE file.
The OUTPUT and TRACE files should be sequential data sets with a record
format of VBA, a record length of 85, and a block size of 6124.
Allocating OUTPUT
When allocating the OUTPUT file, a DDNAME of OUTPUT must be used. For
example, in TSO:
ALLOCATE DD(OUTPUT) SYSOUT(A) RECFM(VBA) LRECL(85) BLKSIZE(6124)
or in batch:
//OUTPUT DD SYSOUT=A,DCB=(RECFM=VBA,LRECL=85,BLKSIZE=6124)
Allocating TRACE
When allocating the TRACE file, a DDNAME of TRACE must be used. For
example, in TSO:
ALLOCATE DD(TRACE) SYSOUT(A) RECFM(VBA) LRECL(85) BLKSIZE(6124)
or in batch:
//TRACE DD SYSOUT=A,DCB=(RECFM=VBA,LRECL=85,BLKSIZE=6124)
Caution: Use a file disposition of MOD when allocating the OUTPUT and TRACE data
sets. If you dont use MOD, only the last cycle of TS/API debug or DB2 trace
output is saved in the file.

In VM, debug output is processed as follows:
IF OUTPUT... THEN...
is explicitly allocated debug output is written to the file OUTPUT.
is not explicitly allocated a file named TSAPI DEBUG is written on the users A-
disk.
When DEBUG ON is used, a large amount of output is written to the debug file.
Make sure you have sufficient disk space available before using this option.
Each user must have his own OUTPUT file, which has a record format of VBA,
a record length of 85, and a block size of 6124.
When allocating the OUTPUT file, a DDNAME of OUTPUT must be used, for
example:
FILEDEF OUTPUT DISK fn ft fm (DISP MOD)
Note: Teradata recommends using a userid as fn and DEBUG as ft for the

OUTPUT file.
Caution: Use a file disposition of MOD when explicitly allocating the OUTPUT file. If
you dont use MOD, only the last cycle of debug output is saved in the file.

The TSDB Debug Destination in

MVS/CICS
Under CICS, TS/API writes debug information to destination name TSDB. All
TS/API CICS users in a given CICS region with the debug turned on will write
to the same destination. Therefore, that destination must be defined to CICS as
an extrapartition transient data queue. See Chapter 2: TS/API Installation and
Customization for details.
That guarantees that each record in the debug output is prefixed with the CICS
terminal ID and the CICS transaction ID, which allows the debug output to be
identified with the transaction that produced it. This identification is very
important if more than one CICS user is creating debug output at the same
time.
OUTPUT Data Set

The data set associated with the TSDB data queue may have any name, but
Teradata recommends the name OUTPUT for compatibility with debug output
in MVS/TSO and VM.
The data queue can be opened automatically at CICS startup or explicitly via
the CEMT SET QUEUE command. The initial status is controlled by the
OPEN=INITIAL/DEFERRED parameter on the DFHDCT TYPE=EXTRA
macro for DESTID=TSDB.
OPEN=INITIAL, the default, specifies that the data set be opened by
system initialization.
OPEN=DEFERRED specifies that the data set remain closed until explicitly
opened via the master terminal open/close function (see below) or via a
DFHOC macro from an application program.
Master Terminal Commands

The master terminal commands to manage the data set are:
CEMT SET QUEUE(TSDB) OPEN/CLOSE
Closing the debug file will force any blocks/records buffered in memory to be
written to the data set.
An external job can then be run to copy or print the contents of the OUTPUT
data set. The OUTPUT data set can be viewed via ISPF browse while it is open.
The TSDB data queue must be open while TS/API is writing data to it. If the
queue is closed, TS/API will send the following message to the terminal that
issued the request:

fopen of the debugging file to DDNAME OUTPUT failed
The physical sequential file itself must be allocated in the CICS startup JCL. The
following statement can be used in the CICS startup JCL:
//OUTPUT DD DSN=TSAPI.DEBUG.OUTPUT,DISP=MOD
Caution: Use a file disposition of MOD. Otherwise, only the last cycle of TS/API debug
data will be captured. With MOD, each time data is written to the file, the file is
appended. Consequently, you must initialize the file when it is necessary to
clear out old data.

Basic Problem Determination (DEBUG

SQL)
Interactive Application Debugging

The TS/API debugging file can be used for interactive and batch application
debugging. Additional debugging features are available interactively in each
application.
QMF provides:
Log
Display of problem
The PF1 or HELP key causes the display of SQLCA (see Advanced
Problem Determination (DEBUG ON) later in this chapter)
The SAS System provides:
Log
Display of problem
PF1 or HELP key causes the display of SQLCA (see Advanced Problem
Determination (DEBUG ON) later in this chapter)
OUTPUT File Contents

A TS/API cycle begins at TS/API runtime invocation and ends at TS/API
runtime exit. The cycle is equivalent to an SQL request to the Teradata RDBMS
via TS/API.
First Cycle
TS/APIs first cycle is numbered 0. The presence of the first cycle indicates that
the TS/API front-end assembler routines are successfully communicating with
the TS/API C runtime routines.
Other displays in the first cycle indicate the TS/API release level and the event
date and time. Under VM, if TS/API is installed in a DCSS, the word DCSS
immediately follows the release identifier.
If nothing is displayed, one of three possibilities exists:
1 The OUTPUT file allocation is incorrect (or dummied out).
In this case, the application is successfully communicating with the TS/API
runtime routines (via the TS/API front-end), but you would have to prove
it by submitting queries (because the output file is disabled) before exiting
the application and correcting it. For example, issuing the DATABASE
<userid> command would execute successfully on the Teradata RDBMS
but not in DB2 or SQL/DS.

2 The TS/API minidisk/library is concatenated behind the DB2/SQL/DS

library/minidisk.
In this case, the application is communicating with DB2 or SQL/DS but
thinks its communicating with the Teradata RDBMS. Issuing the
DATABASE <userid> command quickly determines the correct
environment.
3 A problem in the TS/API front-end occurred before invoking the TS/API

runtime routines.
The main function of the TS/API front-end is to establish the correct
environment and to invoke the TS/API runtime routines. If an error occurs
during setup or invocation, the TS/API front-end does the following:
Updates the SQLCA with an internal DBMS error message
Updates the SQLCODE and return/reason code
Returns control to the application
This level of error usually requires contacting the Global Support Center (GSC).
See Error Reporting Procedures later in this chapter.
The Next Few Cycles

Logon information is displayed for a few cycles after the first. This information
includes DBCLOGON file allocation messages and a message indicating a
successful or unsuccessful logon.
QMF, for example, next issues static SQL requests to set up the application
environment for interaction with the DBMS. (The SAS System does not do this;
it contains no static SQL and establishes/terminates a database session for each
database request.)
The two types of SQL, static and dynamic, are IBMs method of managing SQL
provided for application use. Dynamic SQL is input at runtime from the
application. This allows user input of native SQL. Static SQL is
precompiled/bound as part of the application and is visible at precompile time
but not runtime because it is internal to DB2 and SQL/DS. Static SQL is under
application control, not user control.
Teradata RDBMS macros are created at TS/API installation time to support
DB2/SQL/DS static SQL. To view this SQL, issue the Teradata RDBMS
command SHOW MACRO <macro name>.
The Middle Cycles

The middle cycles contain debugging information on any native SQL entered
by the user and commonly sent as is to the TS/API runtime routines. The main
information displayed during each of these cycles is the following:
The call type directing the call
Any SQL before and after translation by the TS/API parser
Any Teradata RDBMS or CLIv2 error codes and messages

Any reported return codes and error codes

An exit from each call
The SQLCODE returned to the application
Last Cycles
Logoff information indicating a successful or unsuccessful logoff is displayed.
Unit of Work
QMF, SAS, and NATURAL automatically manage the unit of work for the user.
Typically, the application commits automatically after a single successful query.
TS/APIs transaction management logic automatically emulates that of DB2
and SQL/DS by issuing BT and ET requests where appropriate. Some of these
requests are performed implicitly by TS/API; others are performed by the
applications explicit request.
Typically, QMF, SAS, and NATURAL auto-rollback immediately after a query
that returns a DBMS error.

Advanced Problem Determination (DEBUG ON)
Advanced Problem Determination

(DEBUG ON)
The DEBUG ON command is useful for advanced problem determination. Each

debug setting is cumulative. For example, an output file captured with DEBUG
ON is a superset of an output file captured with DEBUG SQL.
Caution The DEBUG ON setting creates a large volume of information, so be certain

that the OUTPUT file (either with the TS/API default allocation or as
overridden by you) can adequately hold the information. Teradata strongly
recommends that you use a spool file when possible.
OUTPUT File Contents

The DEBUG ON setting prints detailed information for each call to TS/API:
Flow of control within TS/API
Entry and exit for each TS/API routine invoked
All relevant application structures for each call to TS/API:
RDIIN - The driver structure to TS/API, which specifies call type,
pointers to all other structures, and other flags related to the call
<sql statement> - Native SQL or information to build static SQL
Input SQLDA - The input data structure matching input client variables
in the <sql statement>
Output SQLDA - The output data structure matching output client
variables returned to the user
CLIv2 structures and data:
DBCAREA (equivalent to RDIIN) - The driver structure to the Teradata
RDBMS, which specifies call type, pointers to all other structures, and
other flags related to the call
<sql statement> - Native SQL or invocation of a macro
USING clause and data parcel (equivalent to Input SQLDA) - USING
clause maps the Input SQLDA and is added to the <sql statement>; the
data parcel contains the Input SQLDA and is sent to the Teradata
RDBMS with the <sql statement>
Record Parcel (equivalent to the Output SQLDA) - The contents of the
Record Parcel are moved to the Output SQLDA
Success/Failure Parcels, CLIv2 errors, and TS/API customized errors
contain information used to build most of SQLCA
Additional informational displays
Information related to key current functions, such as unit of work
messages and special-case parser messages

If you encounter a TS/API error condition that you cannot solve, report the
error to the Global Support Center (GSC). Be sure to include all of the
information in the following table:
Table 5-2 Problem Reporting Information
# Category Information
1 The operating system
2 The operating system release
3 The release of TS/API
4 The release of DB2 or SQL/DS and PTF level
5 The Teradata base release (important for

TS/API 3.0+)
6 The product being used with TS/API
7 That products release level and PUT level
8 The VS/PASCAL release level (for TS/API

Release 1.x only)
9 Problem description if not already in call log
10 The exact commands entered under the product

being used with TS/API, plus the
accompanying SQL statements. (In some cases,
the customer will not know what the SQL looks
like, since the product generates the SQL
transparently.)
11 . The TS/API DEBUG output (if any) from

OUTPUT file with DEBUG ON
12 DB2 trace output (if any) from TRACE file with

DB2 tracing enabled (for TS/API 3.0+ only)

To obtain debug information, do the following:

Table 5-3 Procedure to Obtain Debug Information
Step Action
1 Prepare and allocate a DBCLOGON file/table prior to TS/API invocation.

See Chapter 4: Product Management, for more information on
DBCLOGON.
2 In the DBCLOGON file, include the following:
DEBUG ON;
LOGON [tdpid/]userid,password;
3 Allocate an OUTPUT file to receive the output of the debugging session. See
the beginning of this chapter for more information on the OUTPUT file.
4 Send the OUTPUT file contents to the GSC (in machine-readable form, if
possible).

Error Translations
Error Translations
TS/API translates Teradata RDBMS and CLIv2 error codes into the appropriate
DB2 or SQL/DS SQLCODEs and their corresponding SQLSTATEs.
Error Translation Logic Strategy

However, in some cases, TS/API provides no translation because DB2 and
SQL/DS have a more limited set of error codes than the Teradata RDBMS, and
more meaningful information is available in the Teradata Messages Reference.
The Teradata RDBMS error codes that TS/API does not translate retain their
own error numbers in the range 1000-7999 but are sign-inverted (made
negative) to conform to DB2 and SQL/DS requirements.
The CLIv2 error codes that TS/API does not translate retain their own error
numbers in the range 1-999 but are sign-inverted (made negative) and have
negative 8000 added to them so they cant be confused with DB2 or SQL/DS
SQLCODEs or Teradata RDBMS error codes. For example, the following error
code:
CLI0286
would be represented as:
SQLCODE -8286
Because the Teradata RDBMS and CLIv2 error code range is outside of the
normal DB2 and SQL/DS range of -999 to 999, an untranslated error code
usually results in fatal termination of the application or program.
Appendix A: Translation Tables contains translation tables between DB2 and
SQL/DS SQLCODEs and Teradata SQL error codes.

Error Translations

Chapter 6:
Static SQL and System Catalog Support
This chapter describes how TS/API supports static SQL statements embedded
in DB2 or SQL/DS applications and how TS/API emulates the system catalog
tables of DB2 and SQL/DS.
Static SQL Support
System Catalog Support

Chapter 6: Static SQL and System Catalog Support
Static SQL Support
Static SQL Support
This section explains the following:

Program preparation of DB2 applications
Program preparation of SQL/DS applications
TS/API support of DB2 and SQL/DS embedded static SQL
Preparing a DB2 Application

Figure 6-1 on page 6-4 shows the process through which a DB2 application,
such as QMF, passes in order to become executable. The steps are divided into
those performed at the program product vendor site and those performed at
the customer site.
Steps Performed by the Independent Software Vendor (ISV)
Step Action
1 The source code of a program product, written in a host language, such as

ASSEMBLER or C, is fed into the DB2 precompiler at the ISV site.
2 The DB2 precompiler changes any static SQL statements embedded in the
program into comments and replaces them with valid host language
statements.
3 The DB2 precompiler validates SQL syntax and checks the definitions of the
host language variables.
4 The DB2 precompiler generates a data set called a database request module
(DBRM). DBRMs are used as input to the application program binding
process. A DBRM contains the following:
The embedded SQL statements extracted from the source program
The host variable information extracted from the source program
Information that ties the DBRM to the source statements
5 The precompiler also generates the expanded host language program, which
references the DBRM and is used as input to the compiler. Resulting object
code is input to the linkage editor, which produces one or more executable
load modules.
6 The DBRMs and the load modules are made available to the customer.

Static SQL Support
Steps Performed by the Customer

The customer uses the DBRMs as input to the process of binding the ISV
product. Binding produces a control structure, called a plan, which DB2 uses
when accessing data during application program execution.
Bind performs the following:
Step Action
1 Checking syntax
2 Checking operation authorization
3 Determining the optimal plan data access strategy
4 Validating SQL statements, using the DB2 catalog
5 Generating the plan
During the execution, DB2 uses the plan to execute the static SQL statements.

Static SQL Support
Figure 6-1 Preparing a DB2 Application with Embedded SQL for Execution
Host Language
DB2 Precompiler
DBRM
(one per source module) Expanded
Host Language
Compile
Object Module
Link Edit
Load Module
Occurs at Customer Site
Bind Application Execution
Data
Plan Base
JH01A007

Static SQL Support
Preparing an SQL/DS Application

Figure 6-2 on page 6-6 shows the process through which an SQL/DS
application, such as program product QMF, passes in order to become
executable. The steps are divided into those performed at the ISV site and those
performed at the customer site. When a customer receives an SQL/DS vendor
product that includes embedded SQL, he receives source code of the embedded
SQL.
Steps Performed by Independent Software Vendor
Step Action
1 The source code of the program product is fed into the SQL/DS preprocessor
at the vendor site.
2 The SQL/DS preprocessor validates SQL syntax and checks the definitions of
the host language variables.
3 The preprocessor running at the vendor site generates the expanded host
language program, which references the programs corresponding access
module and is used as input to the compiler.
4 Resulting object code is input to the linkage editor, which produces one or
more load modules that are shipped to the customer.
Steps Performed by Customer
Step Action
1 The ISV product embedded SQL source is fed into the SQL/DS preprocessor at
the customer site.
2 The preprocessor generates an access module, which is stored on the SQL/DS

database. An access module contains the embedded SQL statements and host
variable information extracted from the program. During runtime, SQL/DS
uses the access module to execute the static SQL statements.

Static SQL Support
Figure 6-2 Preparing an SQL/DS Application with Embedded SQL for Execution
Occurs at vendor site

Vendor Product
Source
SQL/DS
Preprocessor
Expanded
Host Language
Compile
Object Module
Link Edit
Load Module
Occurs at Customer Site
SQL Source Application Execution
SQL/DS
Preprocessor
Data Access Module

Base
JH01A008

Static SQL Support
TS/API Support of DB2 Static SQL

Running the TS/API bind utility (TERABIND) replaces the DB2 binding
process. Figure 6-3 on page 6-8 shows the procedure for translating static SQL
into the Teradata SQL macros used to access data in Teradata RDBMS
databases. The TERABIND utility produces a file containing one CREATE
MACRO statement for each static SQL statement in each DBRM. Each macro
executes SQL that is functionally equivalent to that of the SQL in the DBRM.
For certified ISV products, Teradata provides the required BTEQ scripts on the
installation tape. Use these scripts as an input to BTEQ in order to create the
emulation macros on the Teradata RDBMS. For details, see Chapter 2: TS/API
Installation and Customization.
At execution time, TS/API executes the appropriate Teradata SQL macros,
using the macro naming convention as follows:
Database name.Plan name_Program name_Section number
Teradata SQL macros are optimized each time they are executed.

Static SQL Support
Figure 6-3 DB2 Static SQL Translation to Teradata SQL Macros Process for Certified ISVs
Occurs at Teradata site Macro Statement

DBRM Creation Time
TS/API Bind Utility

TERABIND
Occurs at Customer Site Macro Creation Time

Teradata SQL
BTEQ
Teradata DBS
Data
Macros
Base
Execution Time
TS/API
Application Execution
Application Load
Module
JH01A009
TS/API Support of SQL/DS Static SQL

Figure 6-4 on page 6-9 shows the procedure for translating SQL static
statements into Teradata SQL macros used to access data in Teradata RDBMS
databases. Each static SQL statement is manually converted into a CREATE
MACRO statement. Each macro produced executes SQL that is functionally
equivalent to the original static SQL statement.
For certified products, the installation tape includes the BTEQ scripts for the
required macros. Use these scripts as input to BTEQ in order to create the
emulation macros on the Teradata RDBMS. For details, see Chapter 2: TS/API
Installation and Customization.

Static SQL Support
Figure 6-4 SQL/DS Embedded SQL Translation to Teradata Macros Process for Certified ISVs
Occurs at Teradata site Macro Statement

DBRM Creation Time
SQL static statement to

CREATE MACRO
statement translation process
Occurs at Customer Site Macro Creation Time

Teradata SQL
BTEQ
Teradata DBS
Data
Macros
Base
Execution Time
TS/API
Application Execution
Application Load
Module
JH01A010
At runtime, TS/API executes the appropriate Teradata SQL macros, using the
macro naming convention as follows:
Database name.Program name_Section number

To provide transparency, TS/API emulates the DB2 and SQL/DS system

catalogs. This section describes the techniques and information sources that
TS/API uses for system catalog emulation.

TS/API emulates the system catalog tables of DB2 and SQL/DS by building
equivalent system catalog views/tables in the Teradata RDBMS at installation
time.
The DBMS system catalog (qualified by SYSIBM on DB2, SYSTEM on SQL/DS,
and DBC on the Teradata RDBMS) contains all information relating to objects
and the authorization on those objects for the entire Teradata RDBMS.
System Catalog Views

In most cases, DB2 and SQL/DS system catalog tables are emulated using
Teradata RDBMS views. During the installation process, most DB2 or SQL/DS
system catalog tables are created as views of one or more Teradata RDBMS
catalog tables, special TS/API tables, and constant or calculated values. These
views are referred to as TS/API catalog views.
Several Teradata RDBMS catalog tables, stored in the DBC database, are used to
create the TS/API catalog views. The Teradata RDBMS catalog tables that are
used include the following:
Table 6-1 Teradata RDBMS Catalog Tables
Teradata RDBMS Table Description
DBC.DBASE Describes each database and userid on the Teradata

RDBMS
DBC.TVM Describes each table, view, or macro on the Teradata

RDBMS
DBC.TVFIELDS Describes each column on the Teradata RDBMS
DBC.ACCESSRIGHTS Describes access rights on the Teradata RDBMS
DBC.INDEXES Describes columns contained in indexes in the Teradata

RDBMS
The SYSAPI database contains one special dummy table that TS/API uses for
null mapping, called SYSAPI.SYSDUMMY.

System Catalog Tables

In a few cases, TS/API emulates a DB2 or SQL/DS system catalog table by
creating a Teradata RDBMS table. When this is necessary, the table is stored in
either the SYSIBM (for DB2) or SYSTEM (for SQL/DS) database. This kind of
table is usually required when the Teradata RDBMS catalog tables lack the
necessary information, particularly when a DB2 or SQL/DS object has no
equivalent Teradata RDBMS object.
Long Teradata RDBMS Names

DB2 and SQL/DS both support names for databases, tables, columns,
authorization ids, and other objects that are either eight or 18 characters in
length. For most of these objects, the Teradata RDBMS accepts names up to 30
characters.
To accommodate existing Teradata RDBMS tables having longer names, the
TS/API catalog views expand most of these columns to the full 30-character
width accepted by the Teradata RDBMS.
Caution: Due to internal limitations in many applications, use of Teradata RDBMS object
names longer than those permitted by DB2 or SQL/DS may cause anomalies. If
an application fetches a column name into a client variable that is less than 30
characters, a long Teradata RDBMS object name may be improperly truncated.
If the truncated name is then used as the basis for building further queries,
subsequent syntax errors may occur.
Authorization IDs
The Creator columns in DB2 and SQL/DS catalog tables refer to the
authorization id used by DB2 and SQL/DS. No authorization id for the
Teradata RDBMS exists; the database or userid under which the table or view
was created is used as the closest Teradata RDBMS equivalent to an
authorization id.
This equivalent id is not used to support either the DB2 concept of a database
or table space or the SQL/DS concept of a dbspace.
Since an authorization id is an eight-character DB2 or SQL/DS object and the
Teradata RDBMS database name or userid is up to 31 characters, the limitations
mentioned in the preceding caution apply to these columns also.
DB2 SYSIBM System Catalog Tables Emulation

Two databases, SYSIBM and SYSTEM, that emulate the system catalogs of DB2
and SQL/DS, respectively, are created during TS/API installation. Descriptions
of these databases are contained in Appendix B: TS/API Catalog Emulation.


Chapter 7:
Command Syntax
This chapter describes the differences between Teradata SQL and the SQL used
by DB2 and SQL/DS.
TS/API Release 03.04.00 supports the Teradata V1 RDBMS release, as well as
V2R1. V2R2 and V2R3 releases in Teradata mode only.
SQL Differences
Transaction Management
Updatable Cursor Support
Syntax Mapping Strategy
Teradata SQL Extensions Differences
DB2 Syntax Mapping
SQL/DS Syntax Mapping
Teradata SQL Extensions
TS/API Directives

Chapter 7: Command Syntax
SQL Differences
SQL Differences
A number of differences exist between Teradata SQL (the language used to

access databases on the Teradata RDBMS) and the SQL language used with
DB2 and SQL/DS. In order for TS/API to execute SQL requests from DB2 or
SQL/DS applications, TS/API transforms the SQL requests into valid Teradata
SQL and submits them to the Teradata RDBMS.
This chapter also describes the syntax mappings and other actions that TS/API
performs to emulate DB2 or SQL/DS behavior as much as possible. The
differences between Teradata V1 and V2 SQL are listed, where appropriate.
The following subjects are covered:
Transaction management
Emulating updatable cursors
Syntax mapping between DB2 and the Teradata RDBMS
Syntax mapping between SQL/DS and the Teradata RDBMS
Teradata SQL extensions
TS/API directives

DB2 or SQL/DS
Under DB2 or SQL/DS, a unit of work is defined as an entity capable of being
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. The next
unit of work is the work done from the next access to the database until the next
COMMIT or ROLLBACK occurs, and so forth. All modifications to the
database are capable of being rolled back until a COMMIT is successfully
executed.
Teradata RDBMS
Within the Teradata RDBMS, a unit of work is defined as an entity that falls
between a BEGIN TRANSACTION (BT) statement and an END
TRANSACTION (ET) statement. If no BT and ET statements are present, the
Teradata RDBMS automatically treats each statement as a unit of work.
To emulate DB2s or SQL/DS behavior, TS/API (under TSO or CMS) issues a
BT prior to sending any request to the Teradata RDBMS if this is the first
request in the session or if the previous request was a COMMIT/ROLLBACK.
In this way, TS/API begins a transaction, which then is ended by an
ET/ROLLBACK statement when the application issues a
COMMIT/ROLLBACK.
Under CICS, however, applications are using EXEC CICS SYNCPOINT
commands instead of COMMIT or ROLLBACK, and CICS performs
transaction management in two-phase commit (2PC) mode. Therefore, under
CICS, TS/API doesnt issue any BT/ET statements; instead, TS/API provides a
syncpoint exit routine through which CICS notifies TS/API of any syncpoints
performed. TS/API does only its own clean-up in response to these
notifications.

A cursor is a named control structure used by an application program to point

and retrieve a specific row from a set of rows representing the result of
executing a SELECT statement. If a DBMS supports updatable cursors and if
the result table is not read only, an application may use the cursor to update or
delete rows.
DB2 and SQL/DS both support updatable cursors, and applications, such as
QMF, NATURAL, and SAS, use this feature to accomplish their tasks. Since the
Teradata RDBMS pre-V2R2 software does not support updatable cursors,
TS/API has to emulate this feature by other means. (This applies to V2R2
Teradata RDBMS as well, since the same TS/API logic is used).
This emulation causes some additional restrictions, namely:
For a cursor to be updatable, the underlying table must have a unique
index; in addition, if the SELECT statement for the cursor refers to a view,
the unique index must be visible to that view.
TS/API supports only repeatable read (RR) cursor isolation level. (See the
Glossary for term definitions.)
Non-Mapped Error Codes

Table 7-1 lists the error codes normally issued by DB2 or SQL/DS, which are
returned by TS/API as a result of error conditions relating to the use of
updatable cursors.
Table 7-1 Non-Mapped Error Codes
Code Error Description
-504 THE CURSOR NAME <cursor_name> IS NOT DEFINED
-507 THE CURSOR IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS

NOT OPENED
-508 THE CURSOR IDENTIFIED IN THE UPDATE OR DELETE STATEMENT IS

NOT POSITIONED ON A ROW
-510 THE TABLE DESIGNATED BY THE CURSOR OF THE UPDATE OR DELETE

STATEMENT CANNOT BE MODIFIED
-511 THE FOR UPDATE CLAUSE CANNOT BE SPECIFIED BECAUSE THE

TABLE DESIGNATED BY THE CURSOR CANNOT BE MODIFIED
-514 THE CURSOR NAME <cursor_name> IS NOT IN A PREPARED STATE (DB2

only)

The syntax of DB2 and SQL/DS SQL is largely compatible with that of Teradata
SQL. In cases of incompatibility, TS/API ensures logical mapping between DB2
and SQL/DS SQL and Teradata SQL when possible. When a functionally
equivalent Teradata SQL syntax exists, TS/API automatically maps the DB2
and SQL/DS SQL syntax into the corresponding Teradata SQL.
If no equivalent exists, TS/API handles the mapping in one of two ways:
Null mapping
Syntax error
TS/APIs primary goal in handling DB2 and SQL/DS syntax that has no
Teradata SQL equivalent is to eliminate confusion. In cases where a DB2 or
SQL/DS SQL statement performs a function not supported by the Teradata
RDBMS, where no misleading results will occur, and where no Teradata SQL
equivalent exists, TS/API maps the DB2 or SQL/DS statement into a null
statement.
TS/API sends a null statement to the Teradata RDBMS. The statement
accomplishes no work but returns a valid return code. TS/API does not send a
null statement to the Teradata RDBMS in cases where misleading results would
occur. In such cases, TS/API does no mapping and simply returns a syntax
error. Refer to Table 7-3 through Table 7-10 for examples of null mapping and
syntax errors.
The following translation notes define some differences in syntax between
Teradata SQL and DB2 and SQL/DS SQL.
DB2 Physical Database Structures

The DATABASE, TABLESPACE, BUFFERPOOL, and STOGROUP objects of
DB2 are not supported in Teradata SQL. TS/API loosely considers the Teradata
RDBMS database to be analogous to the DB2 authorization id, not the DB2
DATABASE object. When encountered, these constructs are removed from the
SQL statement, except in some cases involving the DATABASE object.
SQL/DS Physical Database Structures

The DBSPACE, DBEXTENT, and storage pool objects of SQL/DS are not
supported in Teradata SQL. TS/API considers the Teradata RDBMS database
to be analogous to the SQL/DS authorization id, not the SQL/DS DBSPACE
object. When encountered, these constructs are removed from the SQL
statement.

Referential Integrity
The referential integrity syntax of DB2 and SQL/DS is not supported in the
Teradata SQL Version 1 release. When encountered, these constructs generate a
syntax error. However, this syntax is supported in V2R2 Teradata SQL.
Subqueries
The following functions are not supported in Teradata SQL Version 1:
The EXISTS clause
ANY, ALL, and SOME predicates
Correlated subqueries
When they occur, these constructs generate a syntax error. However, they are
supported in V2R2 Teradata SQL.
SQL Reserved Word Conflicts

The reserved word lists of DB2, SQL/DS and Teradata SQL differ greatly. As a
result, names for tables, views, columns, and other database objects used in
DB2 or SQL/DS may conflict with the Teradata SQL reserved word list.
In situations where a DB2 or SQL/DS non-keyword is a Teradata SQL
keyword, TS/API adds quotation marks around the offending word to ensure
that the Teradata RDBMS treats the word as an object name and not as a
keyword.
FIELDPROC, EDITPROC, and VALIDPROC

The FIELDPROC, EDITPROC, and VALIDPROC options of DB2 are not
currently supported in Teradata SQL, nor does TS/API provide a functional
equivalent. If encountered, TS/API strips them from the statement.
DATE and TIME Functions

DATE and TIME functions are not currently supported in Teradata SQL.
However, date arithmetic and comparisons are supported within the
functionality available with dates on the Teradata RDBMS. When encountered,
these functions generate a syntax error.
Table 7-2 Special Registers
Type Action
USER Passed to the Teradata RDBMS unchanged.
CURRENT Becomes DATE (FORMAT YYYY-MM-DD).

DATE
Because of this formatting, arithmetic operations cannot be
performed.

Table 7-2 (Continued) Special Registers
Type Action
CURRENT Becomes TIME.

TIME
CURRENT Becomes DATE (FORMAT YYYY-MM-DD)||-

TIMESTAMP
||TIME (FORMAT 99:99:99.99999) TITLE TimeStamp. The use of a
labeled duration of DAYS causes the DAYS word to be removed
from the statement. Other labeled durations are not supported.

The following are those Teradata SQL Extensions that may affect the results
returned to the application.
Long Teradata SQL Names

Teradata SQL allows objects to have names up to 30 characters in length. This
is longer than limits imposed by DB2 or SQL/DS. Depending on the
application, Teradata SQL names that exceed DB2 or SQL/DS limits may not
function properly.
The WITH Clause

The WITH clause in Teradata SQL causes subtotal rows to be returned with the
detail data rows of a SELECT query. Since DB2 and SQL/DS applications do
not expect such rows, TS/API excludes the subtotal rows from being returned
to the application.
Sorting Nulls
Teradata SQL sorts nulls after all other data in a column. DB2 and SQL/DS sort
nulls before any other data in a column. This difference may cause errors in
applications that expect nulls to be handled identically to DB2 or SQL/DS.

DB2 Syntax Mapping
DB2 Syntax Mapping
This section covers TS/API syntax mapping from DB2 SQL to Teradata SQL.
The following are DB2 commands with an explanation of how each is mapped
to Teradata SQL.
ALTER INDEX
Named indexes are not supported in Teradata SQL, nor does TS/API provide a
functional equivalent. TS/API translates ALTER INDEX into a null statement.
ALTER STOGROUP
The STOGROUP object of DB2 is not supported in Teradata SQL. TS/API
translates ALTER STOGROUP into a null statement.
ALTER TABLE
TS/API performs data type translations as necessary.
The DB2 TIME data type translates to INTEGER FORMAT 99:99:99, and the
DB2 TIMESTAMP data type translates to CHAR(26). FIELDPROC and
VALIDPROC options of DB2 are not currently supported in Teradata SQL, nor
does TS/API provide a functional equivalent. Upon encountering them,
TS/API strips them from the statement.
Note: The Teradata RDBMS limits table size to a maximum of 256 columns.
ALTER TABLESPACE
The TABLESPACE object of DB2 is not supported in Teradata SQL. TS/API
translates ALTER TABLESPACE into a null statement.
COMMENT ON
For a table or a view:
TABLE <tablename>
identical syntax to the Teradata RDBMS COMMENT ON TABLE
TABLE <viewname>
automatically mapped to the Teradata RDBMS COMMENT ON VIEW
For single-column syntax:
COLUMN
identical syntax to the Teradata RDBMS COMMENT ON COLUMN

DB2 Syntax Mapping
For multi-column syntax:

<tablename>
automatically translated to one or more Teradata RDBMS COMMENT ON
COLUMN statements
<viewname>
COLUMN statements
COMMIT (WORK)
TS/API issues an ET.
CREATE ALIAS
TS/API issues an error message.
CREATE DATABASE
TS/API removes the STOGROUP and BUFFERPOOL options from the query if
they are present. TS/API provides a default PERMSIZE of 10,000 bytes and
takes all other defaults in effect for the initial allocation. If the initial space
allocation or other parameter values are insufficient, use the Teradata SQL
MODIFY DATABASE command to modify them.
CREATE INDEX
Named indexes, VSAM-related options, and descending order are not
permitted on the Teradata RDBMS pre-V2R2 software, nor does TS/API
provide a functional equivalent. TS/API strips the named index and VSAM-
related information from the query and passes it to the Teradata RDBMS.
TS/API passes the descending order parameter to the Teradata RDBMS and the
Teradata RDBMS generates an error message indicating that the user must
check the syntax of the statement. Named Indexes are supported in V2R2
Teradata SQL.
CREATE STOGROUP
The STOGROUP object of DB2 is not supported in Teradata SQL. TS/API
translates CREATE STOGROUP into a null statement.
CREATE SYNONYM
The SYNONYM object does not exist in Teradata SQL. TS/API automatically
translates it to the corresponding Teradata SQL CREATE VIEW statement.
CREATE TABLE

DB2 Syntax Mapping
The Teradata RDBMS has no physical or logical equivalent to the DB2

DATABASE or TABLESPACE. The Teradata RDBMS DATABASE is equivalent
to a DB2 authorization id, not a DB2 DATABASE. Therefore, TS/API strips the
IN database.tablespace and IN DATABASE database clauses from the
CREATE TABLE statement.
The DB2 TIME data type translates to INTEGER FORMAT 99:99:99, and the
DB2 TIMESTAMP data type translates to CHAR(26). FIELDPROC,
EDITPROC, and VALIDPROC options of DB2 are not currently supported in
Teradata SQL, nor does TS/API provide a functional equivalent. If
encountered, TS/API strips them from the statement.
DB2 tables can be created with 51 or more columns with one CREATE TABLE
statement. The Teradata RDBMS pre-V2R2 software requires that any table of
over 50 columns have an accompanying ALTER TABLE statement for each
additional 50 columns. TS/API automatically creates and executes any ALTER
TABLE statements required to create the table in its entirety and manages the
accompanying unit of work.
If a DB2 CREATE INDEX statement immediately follows a DB2 CREATE
TABLE statement, TS/API automatically strips the index information from the
CREATE INDEX statement and re-creates the table with a Teradata SQL
PRIMARY INDEX clause matching the index definition given on the CREATE
INDEX statement. This avoids a potential performance and data distribution
problem that may be encountered when the Teradata RDBMS assumes a
default primary index. If performance problems occur, modify the CREATE
TABLE statement used to build the table by explicitly using Teradata SQL
syntax with the proper PRIMARY INDEX specification.
CREATE TABLESPACE
The TABLESPACE object of DB2 is not supported in Teradata SQL. TS/API
translates CREATE TABLESPACE into a null statement.
CREATE VIEW
The DB2 syntax is identical to Teradata SQL syntax, with the exception of the
WITH CHECK and DISTINCT options, which TS/API strips from the
statement.
DELETE FROM
The DB2 syntax is identical to the Teradata SQL syntax.
DROP
ALIAS
TS/API issues an error message.

DB2 Syntax Mapping
DATABASE
DB2 syntax is identical to the Teradata SQL syntax. TS/API considers the
Teradata RDBMS database to be analogous to the DB2 authorization id,
not the DB2 database object. Therefore, when using this command, be
certain that you really want to drop the Teradata RDBMS database
object.
INDEX
The Teradata RDBMS pre-V2R2 software does not support Named Indexes.
TS/API passes the statement unaltered to the Teradata RDBMS, which
generates a syntax error. To drop indexes through TS/API, use a valid
Teradata SQL DROP INDEX statement, specifying the index column
names. Named Indexes are supported in V2R2 Teradata SQL.
STOGROUP
TS/API translates to a null statement.
SYNONYM
TS/API translates to the corresponding DROP VIEW statement.
TABLE
DB2 syntax is identical to the Teradata SQL syntax.
TABLESPACE
VIEW
DB2 syntax is identical to the Teradata SQL syntax.
EXPLAIN
The DB2 EXPLAIN syntax is not supported in Teradata SQL, nor does TS/API
provide a functional equivalent. TS/API passes the DB2 statement as is to the
Teradata RDBMS, which generates a syntax error. See the Teradata RDBMS
EXPLAIN statement in the Teradata RDBMS Reference Manual (TOS), Volume 2
(Guide to SQL) or the Teradata RDBMS SQL Reference, for details on getting
similar information from the Teradata RDBMS.
GRANT
The GRANT syntax is explained in Table 7-3 through Table 7-7.

DB2 Syntax Mapping
Table 7-3 GRANT (DB2 Database Privileges)
DB2 Authority Maps To Teradata RDBMS Authority
DBADM ALL
DBCTRL DATABASE,
MACRO,
TABLE,
USER,
VIEW
DBMAINT SELECT
CREATETAB CREATE TABLE

CREATE VIEW
CREATETS Null mapping
DISPLAYDB Null mapping
DROP DROP DATABASE
IMAGCOPY Null mapping
LOAD RESTORE, DUMP
RECOVERDB Null mapping
REORG Null mapping
REPAIR Null mapping
STARTDB Null mapping
STATS CHECKPOINT
STOPDB Null mapping
Table 7-4 GRANT (DB2 Plan Privileges)
BIND CREATE MACRO
EXECUTE EXECUTE

DB2 Syntax Mapping
Table 7-5 GRANT (DB2 System Privileges)
BINDADD Syntax error
BSDS Null mapping
CREATEDBA Syntax error
CREATEDBC Syntax error
CREATESG Null mapping
DISPLAY Null mapping
RECOVER Null mapping
STOPALL Null mapping
STOSPACE Null mapping
SYSADM ALL
SYSOPR Syntax error
TRACE Null mapping
Table 7-6 GRANT (DB2 Table Privileges)
ALL ALL
ALTER Syntax error
DELETE DELETE
INDEX Syntax error
INSERT INSERT
SELECT SELECT
UPDATE UPDATE
UPDATE column name Syntax error
Table 7-7 GRANT (DB2 Use Privileges)
USE OF Null statement
INSERT

DB2 Syntax Mapping
LABEL ON
TABLE
TS/API translates to Teradata SQL COMMENT ON TABLE.
COLUMN
TS/API translates to Teradata SQL ALTER TABLE <table name> ADD
<column name> TITLE string constant. VIEW COLUMNS not
supported.
table name
<column name> TITLE string constant for each column specified.
view name
Not supported on the Teradata RDBMS. TS/API issues an error message.
LOCK TABLE
REVOKE
The REVOKE statement is the exact opposite of the GRANT statement in all
cases. See the GRANT statement explanations earlier in this chapter for the
mapping of the authority of REVOKE.
ROLLBACK [WORK]
TS/API issues ROLLBACK WORK.
SELECT
The DB2 syntax is identical to the Teradata SQL syntax. If the expression is
passed without a Teradata SQL NAMED clause, TS/API adds the appropriate
name. For information about the extended form of the SELECT statement, see
the SELECT statement description under Teradata SQL Extensions later in
this chapter.
UPDATE

This section covers TS/API syntax mapping from SQL/DS SQL to Teradata
SQL.
The following are SQL/DS commands with an explanation of how each is
mapped to Teradata SQL.
ACQUIRE DBSPACE
The DBSPACE object of SQL/DS is not supported in Teradata SQL. TS/API
translates ACQUIRE DBSPACE into a null statement.
ALTER DBSPACE
The DBSPACE object of SQL/DS is not supported in Teradata SQL. TS/API
translates ALTER DBSPACE into a null statement.
ALTER TABLE
TS/API performs data type translation as necessary. The TIME data type
translates to integer format 99:99:99, and the TIMESTAMP translates to
CHAR(26).
COMMENT ON
For a table or a view:
TABLE <tablename>
identical syntax to the Teradata RDBMS COMMENT ON TABLE
TABLE <viewname>
automatically mapped to the Teradata RDBMS COMMENT ON VIEW
For single-column syntax:
COLUMN
identical syntax to the Teradata RDBMS COMMENT ON COLUMN
For multi-column syntax:
<tablename>
COLUMN statements
<viewname>
COLUMN statements

COMMIT WORK (RELEASE)

TS/API issues an ET.
If specified, the RELEASE option terminates your connection to the Teradata
RDBMS and does any clean-up required.
CONNECT
TS/API translates the CONNECT <userid> IDENTIFIED BY <password> to
the LOGON statement, LOGON <userid>, <password>. See the TS/API
directive, LOGON, for more information.
CREATE INDEX
Named indexes, VSAM-related options, and descending order are not
permitted on the Teradata RDBMS pre-V2R2 software, nor does TS/API
provide a functional equivalent. TS/API strips the named index and VSAM-
related information from the query. TS/API passes the descending order
parameter to the Teradata RDBMS and the Teradata RDBMS generates an error
message indicating that the user must check the structure of the table. Named
Indexes are supported in V2R2 Teradata SQL.
CREATE SYNONYM
The SYNONYM object does not exist in Teradata SQL. TS/API automatically
translates it to the corresponding Teradata SQL CREATE VIEW statement.
CREATE TABLE
The Teradata RDBMS has no physical or logical equivalent to the SQL/DS
DATABASE or DBSPACE. The Teradata RDBMS DATABASE is equivalent to
an SQL/DS authorization id, not an SQL/DS DATABASE. Therefore, TS/API
strips the IN database.dbspace clause from the CREATE TABLE statement.
The TIME data type translates to INTEGER FORMAT 99:99:99, and the
TIMESTAMP data type translates to CHAR(26).
SQL/DS tables can have 51 or more columns. The Teradata RDBMS pre-V2R2
software requires that any table of over 50 columns have an accompanying
ALTER TABLE statement for each additional 50 columns. TS/API
automatically creates and executes any ALTER TABLE statements required to
create the table in its entirety and manages the accompanying unit of work.
If an SQL/DS CREATE INDEX statement immediately follows an SQL/DS
CREATE TABLE statement, TS/API automatically strips the index information
from the CREATE INDEX statement and re-creates the table with a Teradata
SQL PRIMARY INDEX clause matching the index definition given on the
CREATE INDEX statement. This avoids a potential performance and data

distribution problem that may be encountered when the Teradata RDBMS

assumes a default primary index. If performance problems occur, modify the
CREATE TABLE statement used to build the table by explicitly using Teradata
SQL syntax with the proper PRIMARY INDEX specification.
CREATE VIEW
The SQL/DS syntax is identical to the Teradata SQL syntax.
DELETE FROM
DROP
DBSPACE
INDEX
The Teradata RDBMS pre-V2R2 software does not support Named Indexes.
TS/API passes the statement unaltered to the Teradata RDBMS, which
generates a syntax error. To drop indexes through TS/API, use a valid
Teradata SQL DROP INDEX statement specifying the index column
names. Named Indexes are supported in V2R2 Teradata SQL.
PROGRAM
Syntax error.
SYNONYM
TS/API translates to the corresponding DROP VIEW statement.
TABLE
VIEW
EXPLAIN
The SQL/DS EXPLAIN syntax is not supported in Teradata SQL, nor does
TS/API provide a functional equivalent. TS/API passes the SQL/DS
statement as is to the Teradata RDBMS, which generates a syntax error. See the
Teradata RDBMS EXPLAIN statement in the Teradata RDBMS Reference Manual
(TOS), Volume 2 (Guide to SQL) or the Teradata RDBMS SQL Reference, for details
on getting similar information from the Teradata RDBMS.
GRANT
The GRANT syntax is explained in Table 7-8 through Table 7-11.

Table 7-8 GRANT (SQL/DS Privileges on Tables and Views)
SQL/DS Authority Maps To Teradata RDBMS Authority
ALTER Syntax error
DELETE Identical syntax
INDEX Syntax error
INSERT Identical syntax
SELECT Identical syntax
UPDATE Identical syntax
Table 7-9 GRANT (SQL/DS Privileges on Programs)
RUN GRANT EXECUTE
Table 7-10 GRANT (SQL/DS Special Privileges)
CONNECT One or more CREATE USER

statements
DBA ALL
RESOURCE Syntax error
SCHEDULE Syntax error
INSERT
LABEL ON
TABLE
TS/API translates to Teradata SQL COMMENT ON TABLE.
COLUMN
<column name> TITLE string constant. VIEW COLUMNS not
supported.
table name
<column name> TITLE string constant for each column specified.
view name

Not supported on the Teradata RDBMS. TS/API issues an error message.
LOCK
TABLE
DBSPACE
Syntax error.
REVOKE
The REVOKE statement is the exact opposite of the GRANT statement in all
cases. See the GRANTstatement explanations earlier in this chapter for the
mapping of the authority of REVOKE.
ROLLBACK WORK (RELEASE)

Most applications do not allow a ROLLBACK statement to be issued directly
by the user and passed to TS/API. If TS/API encounters a ROLLBACK, it
abnormally terminates any outstanding unit of work.
If specified, the RELEASE option normally terminates your connection to the
Teradata RDBMS and performs necessary clean-up.
SELECT
The SQL/DS syntax is identical to the Teradata SQL syntax. If the expression is
passed without a Teradata SQL NAMED clause, TS/API adds the appropriate
name. See the SELECT statement description under Teradata SQL
Extensions later in this chapter for information about the extended form of
SELECT.
UPDATE
UPDATE STATISTICS FOR

TABLE
TS/API translates to the Teradata SQL COLLECT STATISTICS.
DBSPACE

Teradata SQL Extensions are syntax capabilities that extend beyond normal
ANSI, DB2, and SQL/DS syntax. The extensions are supported only on the
Teradata RDBMS. This section explains the extensions by command. For
detailed information on Teradata SQL commands, see the Teradata RDBMS
Reference Manual (TOS), Volume 2 (Guide to SQL) or the Teradata RDBMS SQL
Reference.
These Teradata SQL extensions can easily be exploited by executing QMF SQL
queries containing Teradata SQL extensions syntax or by executing Teradata
SQL Extension syntax queries through the SAS/ACCESS Pass Through Facility.
ABORT
The ABORT command may be used in place of the DB2 and SQL/DS
ROLLBACK. Most applications automatically manage the unit of work for the
user. ABORT allows the user to abnormally terminate a unit of work.
BEGIN TRANSACTION (BT)

The BT command may be used in place of the DB2 and SQL/DS COMMIT.
Most applications automatically manage the unit of work for the user. BEGIN
TRANSACTION allows the user to normally terminate a unit of work and
begin a new unit of work.
CHECKPOINT
CHECKPOINT places a mark in a journal table, to aid in later recovery.
COLLECT STATISTICS
COLLECT STATISTICS obtains statistical data for one or more columns of a
table that may be used by the Teradata RDBMS to optimize data access.
COMMENT ON
The COMMENT ON command is extended to support comments on the
following:
DATABASE
USER
MACRO
The COMMENT ON command can return data. For the data-returning form of
this command, see the SELECT statement detailed later in this section.

CREATE
The CREATE command is extended to support the following:
DATABASE
USER
MACRO
DATABASE
The DATABASE command establishes a default database for the current
session.
DELETE
The DELETE command is extended to remove all objects from the following:
DATABASE
USER
DROP
The DROP command is extended to drop the following (as long as they are
empty):
DATABASE
USER
DROP INDEX
The DROP INDEX command allows you to drop a secondary index, specifying
the index column names.
DROP MACRO
The DROP MACRO command removes a macro definition from a database.
DROP STATISTICS
The DROP STATISTICS command drops statistical data that was created by a
previous COLLECT STATISTICS command.
END TRANSACTION (ET)

The ET command may be used in place of the DB2 and SQL/DS COMMIT.
Most applications automatically manage the unit of work for the user. END
TRANSACTION allows the user to normally terminate a unit of work.

EXECUTE macroname
The EXECUTE macroname command executes a macro that was previously
defined using the CREATE MACRO command. The data-returning form of the
EXECUTE macroname command must contain a single statement consisting of
one of the following data-returning Teradata SQL commands:
COMMENT ON
EXPLAIN
HELP
SELECT
SHOW
For more details concerning the data-returning form of this command, see the
SELECT statement detailed later in this section.
GIVE
The GIVE command transfers ownership of a database or a user space to
another user.
GRANT
The GRANT command is extended to support the following:
CREATE DATABASE
CREATE MACRO
CREATE TABLE
CREATE USER
CREATE VIEW
DROP DATABASE
DROP MACRO
DROP TABLE
DROP USER
DROP VIEW
DATABASE
MACRO
TABLE
USER
VIEW
EXECUTE
GRANT (in Version 1 only)
DUMP
RESTORE
CHECKPOINT

HELP
The HELP command obtains Data Dictionary/Directory information about a
specified database, user, table, view, macro, column, or index. For the data-
returning form of this command, see the SELECT statement detailed later in
this section.
MODIFY
The MODIFY command changes options specified at creation time for the
following:
DATABASE
USER
Multi-Statement Requests
The Teradata RDBMS is capable of receiving two or more Teradata SQL
statements in one request and handling them in parallel. This type of request is
called a multi-statement request. Three restrictions to this capability exist:
If present, a DDL statement (ALTER, CREATE, or DROP) must be the last
statement in a multi-statement request. This is a Teradata RDBMS
restriction.
The SELECT or any other data-returning command cannot be included in a
multi-statement request. This is a TS/API restriction.
The SAS System does not allow multi-statement requests.
When using the SAS System under MVS or VM, if you have an extended IBM
keyboard, you can use the } symbol to denote an end-of-statement delimiter.
TS/API translates the } symbol to a semicolon (;) prior to sending the multi-
statement request to the Teradata RDBMS.
The Teradata RDBMSs parallel processing capability for multi-statement
requests applies to the following commands:
DELETE
INSERT
UPDATE
RENAME
The RENAME command renames the following existing objects:
MACRO
TABLE
VIEW

REPLACE MACRO, REPLACE VIEW

These commands replace an existing macro or view.
REVOKE
The Extensions to the REVOKE statement are the exact opposite of the
Extensions to the GRANT statement in all cases. See the GRANT statement
Extensions earlier in this section for the authority of REVOKE.
SELECT
The normal Teradata SQL SELECT can be used to return data to the user.
Additionally, the Extensions to the following data-returning commands return
data to the user when preceded by the SELECT:
COMMENT ON
EXECUTE or EXEC
EXPLAIN
HELP
SHOW
Teradata SQL statements do not properly return data to the user unless the
above commands are preceded by a SELECT command. This is due to QMF
data retrieval logic.
TS/API automatically removes the SELECT command from the statement and
passes the remainder of the data-returning statement on to the Teradata
RDBMS for normal handling. This technique provides the capability of using
the Teradata SQL Extensions data-returning commands via TS/API. The
following example shows how to use the data-returning form of the SHOW
command:
SELECT SHOW TABLE PERSONNEL.EMPLOYEE
SHOW
The SHOW command displays the data definition statement most recently
used to create, modify, or replace the specified macro, view, or table. For the
data-returning form of this command, see the SELECT statement detailed
earlier in this section.

TS/API Directives
TS/API Directives
Introduction
Directives are statements that can be issued from within the DBCLOGON file
or from the application passthrough screen. Most commonly, they control
CLIv2 options or some form of special processing not directly related to
Teradata SQL.
The following are TS/API Directives, with a brief description of each.
DEBUG
Activates the TS/API debug facility. See Chapter 5: Problem Management for
details.
LOGON
The LOGON statement takes the form:
LOGON [<tdpid>/] <userid>, <password>;
Note: The symbols, < and >, are not part of the specification. Replace these
symbols and the text within them with the appropriate information.
Unless you use a logon exit, the logon statement in the DBCLOGON file is used
to build the CLIv2 logon request to the Teradata RDBMS.
LOGOFF
The LOGOFF command logs you off the current session.
Any outstanding unit of work is automatically committed prior to the
execution of either the LOGON or LOGOFF commands.
DEBUG PERF
This directive causes TS/API to create performance monitoring information.
TS/API places the performance monitoring information in the TS/API
OUTPUT file.
Performance monitoring is acquired from the timestamp fields located in the
DBCAREA CLIv2 communication block. The following table describes these
fields:

TS/API Directives
Table 7-11 Timestamp fields in CLIv2s DBCAREA communication block
HSISVC_Time The date and time that the request left TS/API.
TDPWAIT_Time The time the request arrived at the TDP from CLIv2.
TDPDBO_Time The time the request was sent to the Teradata

RDBMS by the TDP.
TDPDBI_Time The time the response arrived at the TDP from the
Teradata RDBMS.
TDPXMM_Time The time the TDP sent the response to CLIv2.
SRBSCHED_Time The time the CLI response was sent to TS/API.
The TS/API OUTPUT file displays the following performance information:

The actual clock time when the performance data displayed, retrieving
from the system clock.
The amount of time CLIv2 spent to process the request
The amount of time the TDP spent to process the request.
The amount of time the Teradata RDBMS spent to process the request.
The amount of time the TDP spent to process the response.
The amount of time the CLIv2 spent to process the response.
The longest Teradata RDBMS response time during the performance trace,
and when the corresponding request was issued.
NOTE: All time durations are represented in seconds.
See Chapter 5: Problem Management for details on the TS/API OUTPUT file.
SET SESSION (SS)

The following SET SESSION commands are supported:
CHARSET
If an error occurs in naming the character set, the character set is
automatically reset to the CLIv2 default.
COLLATION
Changes collating sequence to specified value.
DATABASE
Changes the current database to the specified database. It is identical to the
Teradata SQL DATABASE command.
DATEFORM
Sets the import-export dateform mode for a session.

TS/API Directives
SET SESSION CHARSET

The SET SESSION CHARSET command is compatible with its BTEQ
equivalent. The SET SESSION CHARSET command sets the name of the
character set for the current session. Two views exist in conjunction with this
command: HostsInfo View and CharTranslation View. The HostsInfo View
defines the default character set for your client. The CharTranslation View
defines the character sets available in the Teradata RDBMS.
The SET SESSION CHARSET command specifies the name of the character set
to be used for the current session. The character sets are user-definable and
defined in the CharTranslations View.
You can choose the character set by name. The name must exist in the
CharSetName column in the CharTranslations View.
The SET SESSION CHARSET command takes the following form:
SET SESSION CHARSET [charstring]
where:
charstring
specifies the name of the character set to be used. The name cannot exceed 30
characters and it must be enclosed in either single () or double () quotes.
This command takes effect only after TS/API logs on to a Teradata RDBMS.
That means the logon string itself should be coded using the clients default
character set.
SET SESSION COLLATION

The SET SESSION COLLATION command is a Teradata SQL command. The
SET SESSION COLLATION command overrides the collation currently in
effect for the session.
The collation for a session determines the ordering of data characters during
comparison operations, and when sorting data in response to a SELECT request
that includes a WITH...BY or ORDER BY clause.
COLLATION can be defined as an attribute of the user.
If the attribute is not defined, then collation for the session defaults to that of
the logon client.
The SET SESSION COLLATION command is used to define collation after a
session is started.

TS/API Directives
The SET SESSION COLLATION command takes the form:
SET SESSION COLLATION ASCII

SS EBCDIC ;
MULTINATIONAL
HOST JH01A006
where:
Syntax element... is the...
ASCII Specifies that comparison and sort operations are to use ASCII
collation.
EBCDIC Specifies that comparison and sort operations are to use

EBCDIC collation.
MULTINATIONAL Specifies that comparison and sort operations are to use the
International sort sequence.
IF the... THEN...
MULTINATIONA The Teradata RDBMS must be set up

L option is for International Character Support
selected. and the hashing algorithm must be
defined for diacritical characters.
HOST This is the default. Specifies that collation for the session is to
agree with the collation of the logon client (EBCDIC for IBM
mainframe clients, ASCII for all others).
The MULTINATIONAL option is available only if the hashing algorithm of the

Teradata RDBMS is set up for International Character Support. Otherwise, this
option returns an error. MULTINATIONAL sets collation for the session to the
International sort sequence, as described under the ORDER BY clause of the
SELECT statement.
The Teradata RDBMS converts data characters to their uppercase values for
comparison and sorting operations unless the CASESPECIFIC option is
included in the Teradata SQL request, or was defined at creation time for the
column being queried.
Character data is sorted in ascending order unless the DESC (descending)
option is included in the Teradata SQL request. The results of CASESPECIFIC
on sorted results is discussed under the ORDER BY clause of the SELECT
statement.

TS/API Directives
SET SESSION DATABASE

The SET SESSION DATABASE command is a Teradata SQL command. It
identifies the database to be used during the current session for all Teradata
SQL statements that are entered without fully qualified table, view, or macro
names.
This default database is used until the end of the session, or until a subsequent
SET SESSION DATABASE command is entered.
The SET SESSION DATABASE command takes the form:
SET SESSION DATABASE dbname
where:
dbname
Specifies the name of the default database.
SET SESSION DATEFORM

The SET SESSION DATEFORM command is a Teradata SQL command. It sets
the import-export dateform mode for a session.
The following rules apply to SET SESSION DATEFORM:
Enter the SET SESSION DATEFORM statement as follows:
SET SESSION DATEFORM = INTEGERDATE
SET SESSION DATEFORM = ANSIDATE
Depending on the DATEFORM mode specified, if INTEGERDATE, the
DATE values are returned in INTEGER form; if ANSIDATE, the DATE
values are returned in CHAR (10) form.
The DATEFORM mode can also be set after a session is logged on.
A setting of DATEFORM after logon will continue only for the life of the
session, or until another SET SESSION DATEFORM statement is issued.
The default DATEFORM for a session is INTEGERDATE
The SET SESSION DATEFORM command takes the form:
SET SESSION DATEFORM INTEGERDATE

ANSIDATE
JR01A006

Appendix A:
Translation Tables
This appendix contains error code translation tables.
Teradata Transparency Series/Application Programming Interface User Guide A1

Appendix A: Translation Tables
Error Codes
Error Codes
This appendix contains translation tables that supplement the information

provided in Chapter 5: Problem Management. For additional information,
see Teradata Messages Reference.
Error Code Translations for DB2

The following two tables list DB2 SQLCODEs and the corresponding Teradata
SQL error codes. The first table is listed in SQLCODE sequence. The next table
is listed in Teradata RDBMS error code sequence.
Table A-1 (Sheet 1 of 3) Error Code Translations for DB2
SQL-CODE Teradata RDBMS Error Code
-010 3760
-060 3527, 3528, 3529, 3530, 3617
-084 3511, 3941
-101 2664, 3509, 3540, 3597, 3609, 3629, 3702, 3705, 3710, 3712, 3714, 3741, 3850,
3851, 3867, 3896
-102 3738
-103 3751, 3752, 3759
-104 2667, 3521, 3525, 3531, 3536, 3541, 3543, 3544, 3551, 3552, 3553, 3557, 3558,
3559, 3561, 3562, 3567, 3576, 3579, 3585, 3588, 3590, 3605, 3612, 3623, 3624,
3625, 3630, 3631, 3632, 3633, 3634, 3636, 3645, 3646, 3648, 3649, 3703, 3706,
3707, 3708, 3709, 3727, 3728, 3735, 3736, 3739, 3761, 3763, 3764, 3765, 3766,
3768, 3770, 3771, 3772, 3773, 3776, 3779, 3784, 3786, 3788, 3792, 3793, 3796,
3797, 3798, 3806, 3808, 3815, 3849, 3852, 3853, 3854, 3855, 3859, 3860, 3861,
3863, 3870, 3871, 3873, 3874, 3875, 3876, 3877, 3878, 3879, 3882, 3886, 3887,
3888, 3890, 3926, 3933, CLI530, 3750, 3952, 3958, 3964, 3965, 3967, 5316
-107 3737
-109 3507
-110 3704, 3775, 3956
-112 3568, 3627, 3628
-113 3696, 3697, 3957
-117 3812, 3813
-119 3554
-120 3569, 3574, 3872
-121 3606, 3885
A2 Teradata Transparency Series/Application Programming Interface User Guide

Error Codes
-122 3504, 3883
-125 3637
-128 3731
-137 3578
-138 2662, 2663
-150 3823
-151 3659
-156 3891
-170 3816, 3817, 3818, 3820, 3821
-171 2603, 2604, 2605, 2606, 2607, 2608, 2622, 2623, 3580, 3581, 3647, 3660, 3662,
3663, 3819, 3857, 3946, 3950, 3951, 3963, 3966
-172 3732
-182 2665, 2666
-199 3516
-203 3809. 3822, 3868
-204 3526, 3539, 3656, 3802, 3807, 3824
-205 3810
-207 3848
-312 3594, 3595, 3599, 3600
-313 2673, 3593
-401 2147, 2149, 3639, 3640, 3959, 3960
-402 3622, 3643, 3644
-404 3520, 3564
-405 3753
-407 2689, 3604, 3811
-408 3814
-415 3654
-421 3607, 3608, 3653
-530 2700
-551 3523, 3524, 3856, 3858, 3865, 3866, 3880, 3881

Error Codes
-552 3545
-554 3542
-601 3519, 3744, 3801, 3803, 3804, 3805
-602 3518
-603 3534
-604 3546, 3729
-612 3515, 3517, 3560
-618 3571
-637 3733, 3789, 3889
-680 3556, 3582, 3919
-802 2161, 2162, 2163, 2164, 2165, 2166, 2232, 2233, 2239, 2240, 2614, 2615, 2616,
2617, 2618, 2619, 2620, 2621, 2661, 2674, 2675, 2676, 2677, 2678, 2679, 2682,
2683, 2684, 2685, 2686, 2687, 3532, 3535, 3641, 3642, 3754, 3755, 3756, 3757,
3758, 3953, 3954, 3955, 3961, 3962
-803 2801, 2802, 2803
-811 3669
-901 2827, 2828, 2938, 3110, 3120, 3513, 3514, 3897
-905 2805, 2843, 2977, 3130, 3566, 3577, 3638, 3661
-911 2450, 2631, 2825, 2826, 3111, 3603
-922 3002, 3003, 3004, 3014, 3015, 3016, 3023, CLI040, CLI041, CLI272, CLI370,
CLI521, CLI524, CLI527
-923 3006, 3007, CLI151, CLI155, CLI280, CLI282, CLI368, CLI369, CLI426,
CLI427, CLI512, CLI513, CLI514
Teradata RDBMS
SQLCODE
Error Code
2147 -401
2149 -401
2161 -802
2162 -802
2163 -802

Error Codes
Teradata RDBMS
SQLCODE
Error Code
2164 -802
2165 -802
2166 -802
2232 -802
2233 -802
2239 -802
2240 -802
2450 -911
2603 -171
2604 -171
2605 -171
2606 -171
2607 -171
2608 -171
2614 -802
2615 -802
2616 -802
2617 -802
2618 -802
2619 -802
2620 -802
2621 -802
2622 -171
2623 -171
2631 -911
2661 -802
2662 -138
2663 -138
2664 -101

Error Codes
Teradata RDBMS
SQLCODE
Error Code
2665 -182
2666 -182
2667 -104
2673 -313
2674 -802
2675 -802
2676 -802
2677 -802
2678 -802
2679 -802
2682 -802
2683 -802
2684 -802
2685 -802
2686 -802
2687 -802
2689 -407
2700 -530
2801 -803
2802 -803
2803 -803
2805 -905
2825 -911
2826 -911
2827 -901
2828 -901
2843 -905
2938 -901
2977 -905

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3002 -922
3003 -922
3004 -922
3006 -923
3007 -923
3014 -922
3015 -922
3016 -922
3023 -922
3110 -901
3111 -911
3120 -901
3130 -905
3504 -122
3507 -109
3509 -101
3511 -084
3513 -901
3514 -901
3515 -612
3516 -199
3517 -612
3518 -602
3519 -601
3520 -404
3521 -104
3523 -551
3524 -551
3526 -204

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3527 -060
3528 -060
3529 -060
3530 -060
3531 -104
3532 -802
3534 -603
3535 -802
3536 -104
3539 -204
3540 -101
3541 -104
3542 -554
3543 -104
3544 -104
3545 -552
3546 -604
3551 -104
3552 -104
3553 -104
3554 -119
3556 -680
3557 -104
3558 -104
3559 -104
3560 -612
3561 -104
3564 -404
3566 -905

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3567 -104
3568 -112
3569 -120
3571 -618
3574 -120
3576 -104
3577 -905
3578 -137
3579 -104
3580 -171
3581 -171
3582 -680
3585 -104
3588 -104
3590 -104
3593 -313
3594 -312
3595 -312
3597 -101
3599 -312
3600 -312
3603 -911
3604 -407
3605 -104
3606 -121
3607 -421
3608 -421
3609 -101
3612 -104

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3617 -060
3622 -402
3623 -104
3624 -104
3625 -104
3627 -112
3628 -112
3629 -101
3631 -104
3632 -104
3633 -104
3634 -104
3636 -104
3637 -125
3638 -905
3639 -401
3640 -401
3641 -802
3642 -802
3643 -402
3644 -402
3645 -104
3646 -104
3647 -171
3648 -104
3649 -104
3653 -421
3654 -415
3656 -204
A 10 Teradata Transparency Series/Application Programming Interface User Guide

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3659 -151
3660 -171
3661 -905
3662 -171
3663 -171
3669 -811
3696 -113
3697 -113
3702 -101
3703 -104
3704 -110
3705 -101
3706 -104
3707 -104
3708 -104
3709 -104
3710 -101
3712 -101
3714 -101
3727 -104
3728 -104
3729 -604
3731 -128
3732 -172
3733 -637
3735 -104
3736 -104
3737 -107
3738 -102
Teradata Transparency Series/Application Programming Interface User Guide A 11

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3739 -104
3741 -101
3744 -601
3750 -104
3751 -103
3752 -103
3753 -405
3754 -802
3755 -802
3756 -802
3757 -802
3758 -802
3759 -103
3760 -010
3761 -104
3763 -104
3764 -104
3765 -104
3766 -104
3768 -104
3770 -104
3771 -104
3772 -104
3773 -104
3775 -110
3776 -104
3779 -104
3784 -104
3786 -104

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3788 -104
3789 -637
3792 -104
3793 -104
3796 -104
3797 -104
3798 -104
3801 -601
3802 -204
3803 -601
3804 -601
3805 -601
3806 -104
3807 -204
3808 -104
3809 -203
3810 -205
3811 -407
3812 -117
3813 -117
3814 -408
3815 -104
3816 -170
3817 -170
3818 -170
3819 -171
3820 -170
3821 -170
3822 -203

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3823 -150
3824 -204
3848 -207
3849 -104
3850 -101
3851 -101
3852 -104
3853 -104
3854 -104
3855 -104
3856 -551
3857 -171
3858 -551
3859 -104
3860 -104
3861 -104
3863 -104
3865 -551
3866 -551
3867 -101
3868 -203
3870 -104
3871 -104
3872 -120
3873 -104
3874 -104
3875 -104
3876 -104
3877 -104

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3878 -104
3879 -104
3880 -551
3881 -551
3882 -104
3883 -122
3885 -121
3886 -104
3887 -104
3888 -104
3889 -637
3890 -104
3891 -156
3896 -101
3897 -901
3919 -680
3926 -104
3933 -104
3941 -084
3946 -171
3950 -171
3951 -171
3952 -104
3953 -802
3954 -802
3955 -802
3956 -110
3957 -113
3958 -104

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3959 -401
3960 -401
3961 -802
3962 -802
3963 -171
3964 -104
3965 -104
3966 -171
3967 -104
3978 -104
5316 -104
5317 -104
6706 -104
CLI040 -922
CLI041 -922
CLI151 -923
CLI155 -923
CLI272 -922
CLI280 -923
CLI282 -923
CLI368 -923
CLI369 -923
CLI370 -922
CLI426 -923
CLI427 -923
CLI512 -923
CLI513 -923
CLI514 -923
CLI521 -922

Error Codes
Teradata RDBMS
SQLCODE
Error Code
CLI524 -922
CLI527 -922
CLI530 -104
Error Code Translations for SQL/DS

The following two tables list the SQL/DS SQLCODEs and the corresponding
Teradata SQL error codes. The first table is listed in SQLCODE sequence. The
next table is listed in Teradata RDBMS error code sequence.
Table A-3 (Sheet 1 of 3) Error Code Translations for SQL/DS
-084 3941
-101 2664, 2843, 2977, 3130, 3509, 3540, 3566, 3597, 3609, 3629, 3638, 3661,
3702, 3705, 3710, 3712, 3714, 3741, 3850, 3851, 3867, 3896, 3578
-102 3738
-103 3751, 3752
-104 2667, 3516, 3521, 3525, 3531, 3536, 3541, 3543, 3544, 3551, 3552, 3553,
3557, 3558, 3559, 3561, 3562, 3567, 3571, 3576, 3579, 3585, 3588, 3590,
3605, 3612, 3623, 3624, 3625, 3630, 3631, 3632, 3633, 3634, 3636, 3641,
3642, 3645, 3646, 3648, 3649, 3703, 3706, 3707, 3708, 3709, 3727, 3728,
3733, 3735, 3736, 3739, 3761, 3763, 3764, 3765, 3766, 3768, 3770, 3771,
3772, 3773, 3776, 3779, 3784, 3786, 3788, 3789, 3792, 3793, 3796, 3797,
3798, 3806, 3808, 3815, 3849, 3852, 3853, 3854, 3855, 3859, 3860, 3861,
3863, 3868, 3870, 3871, 3873, 3874, 3875, 3876, 3877, 3878, 3879, 3882,
3886, 3887, 3888, 3889, 3890, 3926, 3933, CLI530, 6706
-105 3527, 3528, 3529, 3530, 3617
-106 3760
-107 3737
-109 3507
-110 3732
-112 3568, 3627, 3628
-117 3812, 3813
-119 3504, 3554, 3883
-120 3569, 3574, 3872

Error Codes
-121 3606, 3885
-125 3637
-128 3731
-150 3823
-151 3659
-167 3704, 3775
-168 2603, 2604, 2605, 2606, 2607, 2608, 2622, 2623, 2662, 2663, 3580, 3581,
3647, 3660, 3662, 3663, 3816, 3817, 3818, 3819, 3820, 3821, 3857
-203 3809, 3822
-204 3526, 3537, 3538, 3539, 3656, 3802, 3807, 3824
-205 3810
-207 3848
-313 2673, 3593
-314 3594, 3595, 3599, 3600
-401 2147, 2149, 2665, 2666, 3532, 3639, 3640
-402 3622, 3643, 3644
-404 3520, 3564
-405 3753
-407 2689, 3604, 3811
-408 3814
-410 2620, 2621, 2679, 2687, 3535, 3759
-413 2661
-415 3607, 3608, 3653, 3654
-530 2700
-551 3523, 3524, 3856, 3858, 3865, 3866, 3880, 3881
-552 3545
-554 3542
-560 3003, 3015
-561 3004, 3014, CLI521
-564 3002, 3016, 3023, CLI040, CLI041, CLI272, CLI527

Error Codes
-601 3519, 3744, 3801, 3803, 3804, 3805
-602 3556, 3582, 3919
-603 3534
-604 3546, 3729
-612 3515, 3517, 3560
-617 3518
-802 2161, 2162, 2163, 2164, 2165, 2166, 2232, 2233, 2239, 2240, 2614, 2615,
2616, 2617, 2618, 2619, 2674, 2675, 2676, 2677, 2678, 2682, 2683, 2684,
2685, 2686, 3754, 3755, 3756, 3757, 3758
-803 2801, 2802, 2803
-809 2805, 3577
-810 3669
-813 3891
-815 CLI370, CLI524
-901 2828, 3120, 3897
-911 2450, 2631, 2825, 2826, 3111, 3603
-914 2827, 2938, 3110, 3513, 3514
-940 3006, 3007, CLI151, CLI155, CLI280, CLI282, CLI368, CLI369, CLI426,
CLI427, CLI512, CLI513, CLI514
Teradata RDBMS
SQLCODE
Error Code
2147 -401
2149 -401
2161 -802
2162 -802
2164 -802
2165 -802
2166 -802
2232 -802

Error Codes
Teradata RDBMS
SQLCODE
Error Code
2233 -802
2239 -802
2240 -802
2450 -911
2603 -168
2604 -168
2605 -168
2606 -168
2607 -168
2608 -168
2614 -802
2615 -802
2616 -802
2617 -802
2618 -802
2619 -802
2620 -410
2621 -410
2622 -168
2623 -168
2631 -911
2661 -413
2662 -168
2663 -168
2664 -101
2665 -401
2666 -401
2667 -104
2673 -313

Error Codes
Teradata RDBMS
SQLCODE
Error Code
2674 -802
2675 -802
2676 -802
2677 -802
2678 -802
2679 -410
2682 -802
2683 -802
2684 -802
2685 -802
2686 -802
2687 -410
2689 -407
2700 -530
2801 -803
2802 -803
2803 -803
2805 -809
2825 -911
2826 -911
2827 -914
2828 -901
2843 -101
2938 -914
2977 -101
3002 -564
3003 -560
3004 -561
3006 -940

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3007 -940
3014 -561
3015 -560
3016 -564
3023 -564
3110 -914
3111 -911
3120 -901
3130 -101
3504 -119
3507 -109
3509 -101
3513 -914
3514 -914
3515 -612
3516 -104
3517 -612
3518 -617
3519 -601
3520 -404
3521 -104
3523 -551
3524 -551
3525 -104
3526 -204
3527 -105
3528 -105
3529 -105
3530 -105

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3531 -104
3532 -401
3534 -603
3535 -410
3536 -104
3537 -204
3538 -204
3539 -204
3540 -101
3541 -104
3542 -554
3543 -104
3544 -104
3545 -552
3546 -604
3551 -104
3552 -104
3553 -104
3554 -119
3556 -602
3557 -104
3558 -104
3559 -104
3560 -612
3561 -104
3562 -104
3564 -404
3566 -101
3567 -104

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3568 -112
3569 -120
3571 -104
3574 -120
3576 -104
3577 -809
3578 -101
3579 -104
3580 -168
3581 -168
3582 -602
3585 -104
3588 -104
3590 -104
3593 -313
3594 -314
3595 -314
3597 -101
3599 -314
3600 -314
3603 -911
3604 -407
3605 -104
3606 -121
3607 -415
3608 -415
3609 -101
3612 -104
3617 -105

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3622 -402
3623 -104
3624 -104
3625 -104
3627 -112
3628 -112
3629 -101
3630 -104
3631 -104
3632 -104
3633 -104
3634 -104
3636 -104
3637 -125
3638 -101
3639 -401
3640 -401
3641 -104
3642 -104
3643 -402
3644 -402
3645 -104
3646 -104
3647 -168
3648 -104
3649 -104
3653 -415
3654 -415
3656 -204

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3659 -151
3660 -168
3661 -101
3662 -168
3663 -168
3669 -810
3702 -101
3703 -104
3704 -167
3705 -101
3706 -104
3707 -104
3708 -104
3709 -104
3710 -101
3712 -101
3714 -101
3727 -104
3728 -104
3729 -604
3731 -128
3732 -110
3733 -104
3735 -104
3736 -104
3737 -107
3738 -102
3739 -104
3741 -101

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3744 -601
3751 -103
3752 -103
3753 -405
3754 -802
3755 -802
3756 -802
3757 -802
3758 -802
3759 -410
3760 -106
3761 -104
3763 -104
3764 -104
3765 -104
3766 -104
3768 -104
3770 -104
3771 -104
3772 -104
3773 -104
3775 -167
3776 -104
3779 -104
3784 -104
3786 -104
3788 -104
3789 -104
3792 -104

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3793 -104
3796 -104
3797 -104
3798 -104
3801 -601
3802 -204
3803 -601
3804 -601
3805 -601
3806 -104
3807 -204
3808 -104
3809 -203
3810 -205
3811 -407
3812 -117
3813 -117
3814 -408
3815 -104
3816 -168
3817 -168
3818 -168
3819 -168
3820 -168
3821 -168
3822 -203
3823 -150
3824 -204
3848 -207

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3849 -104
3850 -101
3851 -101
3852 -104
3853 -104
3854 -104
3855 -104
3856 -551
3857 -168
3858 -551
3859 -104
3860 -104
3861 -104
3863 -104
3865 -551
3866 -551
3867 -101
3868 -104
3870 -104
3871 -104
3872 -120
3873 -104
3874 -104
3875 -104
3876 -104
3877 -104
3879 -104
3880 -551
3881 -551

Error Codes
Teradata RDBMS
SQLCODE
Error Code
3882 -104
3883 -119
3885 -121
3886 -104
3887 -104
3888 -104
3889 -104
3890 -104
3891 -813
3896 -101
3897 -901
3919 -602
3926 -104
3933 -104
3941 -084
3978 -104
5317 -104
CLI040 -564
CLI041 -564
CLI151 -940
CLI155 -940
CLI272 -564
CLI280 -940
CLI282 -940
CLI368 -940
CLI369 -940
CLI370 -815
CLI426 -940
CLI427 -940

Error Codes
Teradata RDBMS
SQLCODE
Error Code
CLI512 -940
CLI513 -940
CLI514 -940
CLI521 -561
CLI524 -815
CLI527 -564
CLI530 -104

Error Codes

Appendix B:
TS/API Catalog Emulation
This appendix describes the SYSIBM and SYSTEM databases on the Teradata
RDBMS.
Teradata Transparency Series/Application Programming Interface User Guide B1

Appendix B: TS/API Catalog Emulation
Catalog Tables and Views
This appendix contains a description of the SYSIBM and SYSTEM databases on

the Teradata RDBMS. It supplements the information in Chapter 6: Static SQL
and System Catalog Support. TS/API provides these two databases to
emulate the system catalogs for DB2 and SQL/DS, respectively.
Catalog Emulation Table/View

This appendix describes each TS/API catalog emulation table/view, showing
the following:
The name, type, and length of each column in the DB2 or SQL/DS table
being emulated
The Teradata RDBMS table and name, type, and length of the column used
to derive the value
Sometimes the value for a column in one of the DB2 or SQL/DS system catalog
tables cannot be derived from the Teradata RDBMS system catalog tables. For
these columns, a constant or calculation may be used instead of an actual
Teradata RDBMS catalog column. If that is the case, the Teradata RDBMS table
will not be shown, and a constant value or the italicized word calculated will
appear in place of the column name.
To determine how a calculated column value is derived, you must inspect the
view definition for the TS/API catalog view. Do this by either:
Issuing the SELECT SHOW VIEW command to TS/API
Issuing the SHOW VIEW command in BTEQ
Looking at the BTEQ scripts used to create the TS/API catalog views
The SYSIBM database uses views of the Teradata RDBMS catalog tables to
emulate DB2s SYSIBM system catalog tables. The DB2 catalog tables are as
follows:
Table B-1 DB2 SYSIBM Catalog
DB2 Table Name What It Describes
SYSIBM.SYSCOLAUTH DB2 update privileges on columns of tables and views
SYSIBM.SYSCOLUMNS Each column in a table or view
SYSIBM.SYSCOPY Recovery information
SYSIBM.SYSDATABASE Database DSNDB04
SYSIBM.SYSDBAUTH Database privileges
SYSIBM.SYSDBRM DB2 DBRM for each application plan
B2 Teradata Transparency Series/Application Programming Interface User Guide

Table B-1 (Continued) DB2 SYSIBM Catalog
SYSIBM.SYSFIELDS Field procedure for each column of tables or views
SYSIBM.SYSFOREIGNKEYS Table foreign keys
SYSIBM.SYSINDEXES Table indexes
SYSIBM.SYSINDEXPART Unpartitioned indexes
SYSIBM.SYSKEYS Columns that are part of index keys
SYSIBM.SYSLINKS Links between tables
SYSIBM.SYSPLAN DB2 plans
SYSIBM.SYSPLANAUTH Application plan privileges
SYSIBM.SYSPLANDEP DB2 plan dependencies
SYSIBM.SYSRELS Link characteristics
Table B-2 DB2 SYSIBM Catalog
SYSIBM.SYSRESAUTH DB2 resource privileges
SYSIBM.SYSSTMT SQL statements of each DBRM
SYSIBM.SYSSTOGROUP Storage groups
SYSIBM.SYSSYNONYMS DB2 synonyms of tables and views
SYSIBM.SYSTABAUTH Table and view privileges
SYSIBM.SYSTABLEPART Unpartitioned table spaces
SYSIBM.SYSTABLES Tables or views
SYSIBM.SYSTABLESPACE DB2 table spaces
SYSIBM.SYSUSERAUTH System privileges
SYSIBM.SYSVIEWDEP View dependencies on a table and other views
SYSIBM.SYSVIEWS Views
SYSIBM.SYSVLTREE Remaining part of DB2 parse tree representations
SYSIBM.SYSVOLUMES Volume of each DB2 storage group
SYSIBM.SYSVTREE DB2 parse tree of views

SYSIBM .SYSCOLAUTH
This is a view of the empty table, SYSAPI.SYSDUMMY. The columns are
described exactly as in DB2. This view is furnished to support DB2 application
programs that may interrogate for DB2 column authorizations. Because the
Teradata RDBMS does not support column authorizations, zero rows are
always returned from any query. The following table describes
SYSIBM.SYSCOLAUTH.
Table B-3 SYSIBM.SYSCOLAUTH Description
Teradata Teradata Teradata

IBM Name IBM Type
RDBMS Table RDBMS Name RDBMS Type
GRANTOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)

GRANTEE CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
GRANTEETYPE CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
CREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
TNAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)
TIMESTAMP CHAR(12) SYSAPI.SYSDUMMY DUMMYCHR CHAR(12)
DATEGRANTED CHAR(6) SYSAPI.SYSDUMMY DUMMYCHR CHAR(6)
TIMEGRANTED CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
COLNAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)
IBMREQD CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
FILLER CHAR(16) SYSAPI.SYSDUMMY DUMMYCHR CHAR(16)
COLLID CHAR(18) SYSAPI.SYSDUMMY DUMMYCHR CHAR(18)
CONTOKEN CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)

SYSIBM .SYSCOLUMNS
This view joins data from the DBC.DBASE, DBC.TVM, and DBC.TVFIELDS
system catalog tables to emulate the SYSIBM.SYSCOLUMNS table. Each row
defines one column from a table or view description stored in the Teradata
RDBMS system catalog. The following table describes SYSIBM.SYSCOLUMNS.
Table B-4 SYSIBM.SYSCOLUMNS Description
Teradata
IBM Name IBM Type Teradata RDBMS Table Teradata RDBMS Name
RDBMS Type
NAME VARCHAR(18) DBC.TVFIELDS FIELDNAME VARCHAR(31)

TBNAME VARCHAR(18) DBC.TVM TVMNAME VARCHAR(31)
TBCREATOR CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
COLNO SMALLINT DBC.TVFIELDS calculated from SMALLINT
FIELDID
COLTYPE CHAR(8) DBC.TVFIELDS calculated from CHAR(8)
FIELDTYPE
calculated from
LENGTH SMALLINT DBC.TVFIELDS SMALLINT
FIELDTYPE
calculated from
SCALE SMALLINT DBC.TVFIELDS FIELDTYPE SMALLINT
NULLABLE
NULLS CHAR(1) DBC.TVFIELDS -1 CHAR(1)
COLCARD INTEGER INTEGER
HIGH2KEY CHAR(8) CHAR(8)
LOW2KEY CHAR(8) Y CHAR(8)
UPDATES CHAR(1) DBC.TVFIELDS N CHAR(1)
IBMREQD CHAR(1) COMMENTSTRING CHAR(1)
REMARKS VARCHAR(254) N VARCHAR(254)
DEFAULT CHAR(1) 0 CHAR(1)
KEYSEQ SMALLINT N SMALLINT
FOREIGNKEY CHAR(1) DBC.TVFIELDS N CHAR(1)
FLDPROC CHAR(1) FIELDTITLE CHAR(1)
LABEL VARCHAR(30) VARCHAR(60)

The following table explains how each column is supported and how its value
may vary from the expected DB2 value:
Table B-5 How Columns in SYSIBM.SYSCOLUMNS are Supported
IBM Name Description
NAME Column name. The DB2 length of 18 is expanded to a length of 31 to accommodate the
longer fieldnames available in Teradata SQL
TBNAME Table or view names. The DB2 length of 18 is expanded to a length of 31 to accommodate
the longer table names available in Teradata SQL
TBCREATOR The Teradata RDBMS userid under which the table or view was created. The DB2 length of
eight is expanded to 31 to accommodate the longer userid names available in Teradata SQL
COLNO Column number
COLTYPE Column type of the field in DB2 format
LENGTH Field length in DB2 format
SCALE Scale information for DECIMAL fields in DB2 format
NULLS Whether the field can be set to nulls
COLCARD Set to -1 indicating that statistics have not been gathered. Teradata RDBMS statistics are not
relevant to DB2 use.
HIGH2KEY Set to blank indicating no HIGH2KEY information. The Teradata RDBMS does not have
relevant key range information.
LOW2KEY Set to blank indicating no LOW2KEY information. The Teradata RDBMS does not have
relevant key range information.
UPDATES Set to Y indicating that the field is updatable. If this is a view, the field may not be
updatable. In that case, an error occurs if an application attempts to update the field.
IBMREQD Set to N indicating that the row does not come from the basic machine-readable tape.
REMARKS Column comments
DEFAULT Set to N indicating that the field does not contain a default value. This may not be correct
if the field contains a DEFAULT clause in the table description.
KEYSEQ Set to zero indicating that the field is not part of the primary key. This setting may not be
correct if the field is part of the primary key.
FOREIGNKEY Set to N indicating that the field is not part of a foreign key. The Teradata RDBMS does not
provide catalog support for foreign keys.
FLDPROC Set to N indicating that the field does not have a field procedure. The Teradata RDBMS
currently does not support field procedures.
LABEL The field title. The DB2 length of 30 is expanded to a length of 60 to accommodate the
longer field titles available in Teradata SQL.

SYSIBM.SYSCOPY
This view is a SELECT of constants used to emulate the SYSIBM.SYSCOPY
table, which contains information needed for recovery. The following table
describes SYSIBM.SYSCOPY.
Table B-6 SYSIBM.SYSCOPY Description
Teradata
IBM Name IBM Type Teradata RDBMS Name Teradata RDBMS Type
RDBMS Table
DBNAME CHAR(8) DSNDB04 CHAR(8)

TSNAME CHAR(8) DSNDB04 CHAR(8)
DSNUM INTEGER 1 INTEGER
ICTYPE CHAR(1) F CHAR(1)
ICDATE CHAR(6) 880101 CHAR(6)
START_RBA CHAR(6) CHAR(6)
FILESEQNO INTEGER 1 INTEGER
DEVTYPE CHAR(8) CHAR(8)
IBMREQD CHAR(1) N CHAR(1)
DSNAME CHAR(44) CHAR(44)
ICTIME CHAR(6) 000000 CHAR(6)
SHRLEVEL CHAR(1) CHAR(1)
DSVOLSER VARCHAR(1784) 000000 VARCHAR(1784)
TIMESTAMP CHAR(12) ? CHAR(12)
ICBACUP CHAR(2) CHAR(2)
ICUNIT CHAR(1) CHAR(1)
may vary from the expected DB2 value:
Table B-7 How Columns in SYSIBM.SYSCOPY Are Supported
DBNAME Database name; set to DSNDB04.
TSNAME Table space; set to DSNDB04. Table spaces are not defined on the Teradata RDBMS.
DSNUM Data set number within table space; set to 1.
ICTYPE Set to F. The operation is always full-image copy on the Teradata RDBMS.
ICDATE Set to 880101. The date of the entry is not defined on the Teradata RDBMS.
START_RBA Set to blank. The DB2 log is not used on the Teradata RDBMS.

Table B-7 (Continued) How Columns in SYSIBM.SYSCOPY Are Supported
FILESEQNO Tape file sequence number; set to 1.
DEVTYPE Set to blank.
IBMREQD Set to N, indicating that the row does not come from the basic machine-readable tape.
DSNAME Data set name; set to blanks. The data set name is not available on the Teradata RDBMS.
ICTIME The time at which the row was inserted; set to 000000. Not available on the Teradata RDBMS.
SHRLEVEL Set to blank. Does not describe an image copy.
DSVOLSER Set to 000000. Volume serial number unavailable.
TIMESTAMP Set to ?, indicating that no timestamp is available. This column is generally unused by users or
application programs.
ICBACUP Indicates whether the Image Copy dataset is for the primary or secondary system.
ICUNIT Device used for Image Copy dataset.
SYSIBM .SYSDATABASE
This view is a SELECT of constants used to emulate the
SYSIBM.SYSDATABASE table. The table contains one row for the DSNDB04
database. The following table describes SYSIBM.SYSDATABASE in detail.
Table B-8 SYSIBM.SYSDATABASE Description

IBM Name IBM Type
NAME CHAR(8) DSNDB04 CHAR(8)

CREATOR CHAR(8) SYSIBM CHAR(8)
STGROUP CHAR(8) SYSDEFLT CHAR(8)
BPOOL CHAR(8) BP0 CHAR(8)
DBID SMALLINT 4 SMALLINT
CREATEDBY CHAR(8) SYSIBM CHAR(8)
ROSHARE CHAR(1) CHAR(8)

may vary from the expected DB2 value.
Table B-9 How Columns in SYSIBM.SYSDATABASE Are Supported
NAME Database name; set to DSNDB04.
CREATOR Owners authorization ID; set to SYSIBM.
STGROUP Default storage group; set to SYSDEFLT.
BPOOL Default buffer pool; set to BP0.
DBID Database internal identifier; set to 4.
IBMREQD Set to N, indicating that the row does not come from the basic machine-readable
tape
CREATEDBY Primary authorization ID; set to SYSIBM
ROSHARE Indicates whether database is read-only shared data
TIMESTAMP The time the database became shared
SYSIBM .SYSDBAUTH
This view joins data from the DBC.DBASE and DBC.ACCESSRIGHTS catalog
tables to emulate the SYSIBM.SYSDBAUTH table, which records user
privileges on databases. The following table describes SYSIBM.SYSDBAUTH.

Table B-10 SYSIBM.SYSDBAUTH Description
Teradata Teradata
IBM Name IBM Type Teradata RDBMS Table
RDBMS Name RDBMS Type
GRANTOR CHAR(8) DBC.ACCESSRIGHTS GRANTORNAME CHAR(31)

GRANTEE CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
NAME CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
DATEGRANTED CHAR(6) ? CHAR(6)
TIMEGRANTED CHAR(8) ? CHAR(8)
GRANTEETYPE CHAR(1) CHAR(1)
AUTHHOWGOT CHAR(1) D CHAR(1)
CREATETABAUTH CHAR(1) DBC.ACCESSRIGHTS calculated from CHAR(1)
ACCESSRIGHT
calculated from
CREATETSAUTH CHAR(1) DBC.ACCESSRIGHTS CHAR(1)
ACCESSRIGHT
calculated from
DBADMAUTH CHAR(1) DBC.ACCESSRIGHTS ACCESSRIGHT CHAR(1)

DBCTRLAUTH CHAR(1) CHAR(1)
DBMAINTAUTH CHAR(1) CHAR(1)
DISPLAYDBAUTH CHAR(1) calculated from CHAR(1)
ACCESSRIGHT
DROPAUTH CHAR(1) DBC.ACCESSRIGHTS CHAR(1)

IMAGECOPYAUTH CHAR(1) CHAR(1)

LOADAUTH CHAR(1) CHAR(1)

REORGAUTH CHAR(1) CHAR(1)

RECOVERDBAUTH CHAR(1) CHAR(1)

REPAIRAUTH CHAR(1) CHAR(1)

STARTDBAUTH CHAR(1) CHAR(1)

STATSAUTH CHAR(1) CHAR(1)
N
STOPAUTH CHAR(1) CHAR(1)
IBMREQD CHAR(1) CHAR(1)
B 10 Teradata Transparency Series/Application Programming Interface User Guide

Table B-11 How Columns in SYSIBM.SYSDBAUTH Are Supported
GRANTOR The Teradata RDBMS user who granted an access right. The DB2 length of eight is
expanded to 31 to accommodate the longer userid names available in Teradata SQL.
GRANTEE The Teradata RDBMS user who was granted an access right. The DB2 length of eight is
NAME The name of a table or view in the Teradata RDBMS. The DB2 length of 18 characters is
expanded to a length of 31 to allow for longer table names on the Teradata RDBMS.
TIMESTAMP Set to ?, indicating that no timestamp is available. This column is generally not used
by users or application programs.
DATEGRANTED Set to ?, indicating that no date is available. This column is generally not used by users
or application programs.
TIMEGRANTED Set to ?, indicating that no time is available. This column is generally not used by
users or application programs.
GRANTEETYPE Set to blank, reflecting that the grantee is a user. Because the Teradata RDBMS cannot
grant access rights to programs, only blank is valid.
AUTHHOWGOT Authorization of the user from whom privileges were received. Always set to D
(DBADM).
CREATETABAUTH Whether GRANTEE can create tables within a database; two types exist:
Authority not held
Privilege held without GRANT option
Authority held with GRANT option is not defined to the Teradata RDBMS.
CREATETSAUTH Whether GRANTEE can create table spaces; two types exist:
Authority not held
DBADMAUTH Whether GRANTEE has DBADM authority over database; two types exist:
Authority not held
DBCTRLAUTH Whether GRANTEE has DBCTRL authority over a database; not defined to the
Teradata RDBMS. Blank is returned.
DBMAINTAUTH Whether GRANTEE has DBMAINT authority over database; not defined to the
Teradata Transparency Series/Application Programming Interface User Guide B 11

Table B-11 (Continued) How Columns in SYSIBM.SYSDBAUTH Are Supported
DISPLAYDBAUTH Whether GRANTEE can issue the DISPLAY command for the database; not defined to
the Teradata RDBMS. Blank is returned.
DROPAUTH Whether GRANTEE can drop the database; two types exist:
Authority not held
IMAGCOPYAUTH Whether GRANTEE can use the COPY and MERGECOPY utilities; not defined to the
LOADAUTH Whether GRANTEE can use the LOAD utility; not defined to the Teradata RDBMS.
Blank is returned.
REORGAUTH Whether GRANTEE can use the REORG utility; not defined to the Teradata RDBMS.
Blank is returned.
RECOVERDBAUTH Whether GRANTEE can use the RECOVER utility; not defined to the Teradata RDBMS.
Blank is returned.
REPAIRAUTH Whether GRANTEE can use REPAIR utility; not defined to the Teradata RDBMS. Blank
is returned.
STARTDBAUTH Whether GRANTEE can issue the START command; not defined to the Teradata
RDBMS. Blank is returned.
STATSAUTH Whether GRANTEE can use the RUNSTATS utility; not defined to the Teradata
STOPAUTH Whether GRANTEE can issue the STOP command; not defined to the Teradata

SYSIBM.SYSDBRM
described exactly as in DB2. This view is furnished solely for the purpose of
properly supporting DB2 application programs that may interrogate for a DB2
DBRM. The Teradata RDBMS does not support DBRMs, so zero rows are
always returned from any query. See the following table for a description of
SYSIBM.SYSDBRM.
Table B-12 SYSIBM.SYSDBRM Description
Teradata RDBMS Teradata

Name RDBMS Type
NAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)

PDSNAME CHAR(44) SYSAPI.SYSDUMMY DUMMYCHR CHAR(44)
PLNAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
PLCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
PRECOMPTIME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
PRECOMPDATE CHAR(6) SYSAPI.SYSDUMMY DUMMYCHR CHAR(6)
QUOTE CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
COMMA CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
HOSTLANG CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
CHARSET CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
MIXED CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
DEC31 CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
VERSION VARCHAR(64) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(64)

SYSIBM.SYSFIELDS
described exactly as in DB2. The Teradata RDBMS does not support
FIELDPROCS, and TS/API does not emulate them. Therefore, zero rows are
always retrieved from any query. This view is furnished solely for the purpose
of supporting DB2 application programs that may interrogate which fields
have FIELDPROCS. The following table describes SYSIBM.SYSFIELDS.
Table B-13 SYSIBM.SYSFIELDS Description
Teradata Teradata
TBCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)

TBNAME VARCHAR(8) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(8)
COLNO SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
NAME VARCHAR(8) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(8)
FLDTYPE CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
LENGTH SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
SCALE SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
FLDPROC CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
WORKAREA SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
EXITPARML SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
PARMLIST VARCHAR(254) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(254)
EXITPARM VARCHAR(1530) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(1530)

SYSIBM .SYSFOREIGNKEYS
properly supporting DB2 application programs that may interrogate for DB2
foreign keys. The Teradata RDBMS does not support foreign key definitions.
The following table describes SYSIBM.SYSFOREIGNKEYS.
Table B-14 SYSIBM.SYSFOREIGNKEYS Description

IBM Name IBM Type

RELNAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
COLNO SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
COLSEQ SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
SYSIBM .SYSINDEXES
This view joins data from DBC.DBASE, DBC.TVM, and DBC.INDEXES catalog
tables to emulate the SYSIBM.SYSINDEXES table. The table contains one row
for every index. The following table describes SYSIBM.SYSINDEXES.

Table B-15 SYSIBM.SYSINDEXES Description

IBM Name IBM Type
NAME VARCHAR(18) DBC.INDEXES calculated from TABLEID VARCHAR(18)

DATABASENAME
CREATOR CHAR(8) DBC.DBASE TVMNAME CHAR(31)
TBNAME VARCHAR(18) DBC.TVM DATABASENAME VARCHAR(18)
TBCREATOR CHAR(8) DBC.DBASE calculated from CHAR(31)
UNIQUEFLAG
UNIQUERULE CHAR(1) DBC.INDEXES CHAR(1)
FIELDPOSITION
N
COLCOUNT SMALLINT DBC.INDEXES SMALLINT
N
CLUSTERING CHAR(1) CHAR(1)
0
CLUSTERED CHAR(1) CHAR(1)
INDEXNUMBER
DBID SMALLINT SMALLINT
INDEXNUMBER
ODBID SMALLINT DBC.INDEXES SMALLINT
DATABASENAME
ISODBID SMALLINT DBC.INDEXES SMALLINT
NOIXNAME
DBNAME CHAR(8) DBC.DBASE CHAR(31)
-1
INDEXSPACE CHAR(8) CHAR(8)
-1
FIRSTKEYCARD INTEGER INTEGER
-1
FULLKEYCARD INTEGER INTEGER
-1
NLEAF INTEGER INTEGER
BP0
NLEVELS SMALLINT SMALLINT
4096
BPOOL CHAR(8) CHAR(8)
Y
PGSIZE SMALLINT SMALLINT

ERASURERULE CHAR(1) CHAR(1)
Y
DSETPASS CHAR(8) CHAR(8)
0
CLOSERULE CHAR(1) CHAR(1)
N
SPACE INTEGER INTEGER
0

CLUSTERRATIO SMALLINT SMALLINT
CREATEDBY CHAR(8) CHAR(8)

Table B-16 How Columns in SYSIBM.SYSINDEXES Are Supported
NAME The Teradata RDBMS does not support named indexes, so index name is derived from the
TABLEID and INDEXNUMBER fields in the DBC.INDEXES table.
CREATOR Authorization ID of the index owner. Field length of eight is expanded to 31 to

accommodate the longer names in Teradata SQL.
TBNAME Name of the table on which the index is defined.
TBCREATOR Authorization ID of the table owner. Field length of eight is expanded to 31 to

UNIQUERULE States whether the index is unique.
COLCOUNT The number of columns in the key
CLUSTERING Set to N. Cluster is not defined in the Teradata SQL environment.
CLUSTERED Set to N. Cluster is not defined in the Teradata SQL environment.
DBID Internal identifier of database; set to 0
OBID Internal identifier of index
ISOBID Internal identifier of index space; set to OBID.
DBNAME Name of the database containing the index. Field length of eight is expanded to 31 to
INDEXSPACE Set to NOIXNAME. Index spaces are not defined in Teradata SQL environment.
FIRSTKEYCARD The number of distinct values of the first 8 bytes of the key; set to 1.
FULLKEYCARD The number of distinct values of the key; set to 1.
NLEAF The number of levels in index tree; set to 1. Index tree is not defined in the Teradata SQL
environment.
NLEVELS The number of levels in index tree; set to -1.
BPOOL Name of buffer pool; set to BP0.
PGSIZE Size of subpages; set to 4096.
ERASURERULE Set to Y. Data sets considered erased when dropped.
DSETPASS Password for the data sets of the index; set to blanks. Not defined in Teradata SQL
environment.
CLOSERULE Set to Y. Data sets considered closed when the index is not in use.
SPACE Set to 0. DASD storage for indexes not defined in the Teradata SQL environment.

Table B-16 (Continued) How Columns in SYSIBM.SYSINDEXES Are Supported
CLUSTERRATIO Set to 0. Clusters are not defined in Teradata SQL environment.
CREATEDBY Set to blanks.
SYSIBM .SYSINDEXPART
This view joins data from the DBC.DBASE, DBC.TVM, and DBC.INDEXES
catalog tables to emulate the SYSIBM.SYSINDEXPART table. The table contains
one row for each index. The following table describes
SYSIBM.SYSINDEXPART.
Table B-17 SYSIBM.SYSINDEXPART Description

IBM Name IBM Type
PARTITION SMALLINT 0 CHAR(31)

IXNAME VARCHAR(18) DBC.INDEXES calculated from CHAR(31)
TABLEID
DATABASENAME
IXCREATOR CHAR(8) DBC.DBASE CHAR(31)
0
PQTY INTEGER INTEGER
0
SQTY SMALLINT SMALLINT
E
STORTYPE CHAR(1) CHAR(1)

STORNAME CHAR(8) CHAR(8)

VCATNAME CHAR(8) CHAR(8)
-1
CARD INTEGER INTEGER
-1
FAROFFPOS INTEGER INTEGER
-1
LEAFDIST INTEGER INTEGER
0
NEAROFFPOS INTEGER INTEGER
N
0
LIMITKEY VARCHAR(512) VARCHAR(512)
0
FREEPAGE SMALLINT SMALLINT
0
PCTFREE SMALLINT SMALLINT

Table B-18 How Columns in SYSIBM.SYSINDEXPART Are Supported
PARTITION Index not partitioned. Set to 0. Not defined in Teradata SQL.
IXNAME The Teradata RDBMS does not support named indexes, so the index name is derived
from the TABLEID and INDEXNUMBER fields in the DBC.INDEXES table. Not defined
in Teradata SQL.
IXCREATOR Authorization ID of the index owner. Field length of eight is expanded to 31 to

PQTY Storage group. Set to 0. Not defined in Teradata SQL.
SQTY Set to 0.
STORTYPE Storage allocation considered explicit. Set to E.
STORNAME Storage group. Set to blanks. Not defined in Teradata SQL.
VCATNAME VSAM. Set to blanks. Not defined in Teradata SQL.
CARD Statistics not gathered for the number of rows referenced by the index. Set to -1.
FAROFFPOS Statistics not gathered for optimal positioning information in relation to index. Set to -1.
LEAFDIST Set to -1. Not defined in Teradata SQL.
NEAROFFPOS Set to 0. Not defined in Teradata SQL.
IBMREQD Indicates whether a row comes from the basic machine-readable tape. Set to N.
LIMITKEY Set to 0. Not defined in Teradata SQL.
FREEPAGE Set to 0. Not defined in Teradata SQL.
PCTFREE Set to 0. Not defined in Teradata SQL.

SYSIBM.SYSKEYS
This view joins data from the DBC.DBASE, DBC.INDEXES, DBC.TVM, and
DBC.TVFIELDS catalog tables to emulate the SYSIBM.SYSKEYS table, which
contains one row for each column of an index key. The following table
describes SYSIBM.SYSKEYS.
Table B-19 SYSIBM.SYSKEYS Description
Teradata
RDBMS Type
IXNAME VARCHAR(18) DBC.INDEXES calculated from VARCHAR(18)

TABLEID
DATABASENAME
IXCREATOR CHAR(8) DBC.DBASE CHAR(31)
FIELDNAME
COLNAME VARCHAR(8) DBC.TVFIELDS VARCHAR(31)
calculated from
COLNO SMALLINT DBC.INDEXES SMALLINT
FIELDID
FIELDPOSITION
COLSEQ SMALLINT DBC.INDEXES SMALLINT
A
ORDERING CHAR(1) CHAR(1)
N
Table B-20 How Columns in SYSIBM.SYSKEYS Are Supported
IXNAME The Teradata RDBMS does not support named indexes, so index name is derived from the
TABLEID and INDEXNUMBER fields in the DBC.INDEXES table.
IXCREATOR Authorization ID of the index owner. Field length of eight is expanded to 31 to

COLNAME Name of the column of the key. Field length of eight is expanded to 31 to accommodate the
longer names in Teradata SQL.
COLNO Numerical position of column in the row.
COLSEQ Numerical position of column in the key.
ORDERING Set to A. All columns are ascending.

SYSIBM.SYSLINKS
described exactly as in DB2. This view is furnished only to support DB2
application programs that may interrogate for DB2 links. The Teradata RDBMS
does not support DB2 links. The following table describes SYSIBM.SYSLINKS.
Table B-21 SYSIBM.SYSLINKS Description
Teradata
IBM Name IBM Type Teradata RDBMS Table Teradata RDBMS Type
RDBMS Name

LINKNAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
PARENTNAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)
PARENTCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
CHILDSEQ SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
DBNAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
DBID SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
OBID SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
COLCOUNT SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
INSERTRULE CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)

SYSIBM.SYSPLAN
The SYSIBM.SYSPLAN table correlates the names of Teradata RDBMS macros
emulating static SQL with the DB2 plan names that are passed from application
programs. SYSIBM.SYSPLAN is built and maintained by TS/API and its
installation procedures. The following table describes SYSIBM.SYSPLAN.
Table B-22 SYSIBM.SYSPLAN Description
Teradata
RDBMS Type
NAME CHAR(8) SYSIBM.SYSPLAN NAME CHAR(8)

CREATOR CHAR(8) SYSIBM.SYSPLAN CREATOR CHAR(8)
BINDDATE CHAR(6) SYSIBM.SYSPLAN BINDDATE CHAR(6)
VALIDATE CHAR(1) SYSIBM.SYSPLAN VALIDATE CHAR(1)
ISOLATION CHAR(1) SYSIBM.SYSPLAN ISOLATION CHAR(1)
VALID CHAR(1) SYSIBM.SYSPLAN VALID CHAR(1)
OPERATIVE CHAR(1) SYSIBM.SYSPLAN OPERATIVE CHAR(1)
BINDTIME CHAR(8) SYSIBM.SYSPLAN BINDTIME CHAR(8)
PLSIZE INTEGER SYSIBM.SYSPLAN PLSIZE INTEGER
IBMREQD CHAR(1) SYSIBM.SYSPLAN IBMREQD CHAR(1)
AVGSIZE INTEGER SYSIBM.SYSPLAN AVGSIZE INTEGER
ACQUIRE CHAR(1) SYSIBM.SYSPLAN ACQUIRE CHAR(1)
RELEASE CHAR(1) SYSIBM.SYSPLAN RELEASE CHAR(1)
filler CHAR(1) SYSIBM.SYSPLAN filler CHAR(1)
EXPLAN CHAR(1) SYSIBM.SYSPLAN EXPLAN CHAR(1)
EXPREDICATE CHAR(1) SYSIBM.SYSPLAN EXPREDICATE CHAR(1)
BOUNDBY CHAR(8) SYSIBM.SYSPLAN BOUNDBY CHAR(8)
QUALIFIER CHAR(8) SYSIBM.SYSPLAN QUALIFIER CHAR(8)
CACHESIZE SMALLINT SYSIBM.SYSPLAN CACHESIZE SMALLINT
PLENTRIES SMALLINT SYSIBM.SYSPLAN PLENTRIES SMALLINT
DEFERPREP CHAR(1) SYSIBM.SYSPLAN DEFERPREP CHAR(1)
CURRENTSERVER CHAR(16) SYSIBM.SYSPLAN CURRENTSERVER CHAR(16)
SYSENTRIES SMALLINT SYSIBM.SYSPLAN SYSENTRIES SMALLINT

WARNING
Any user modification of SYSIBM.SYSPLAN may invalidate the integrity of
TS/API.
Table B-23 How Columns in SYSIBM.SYSPLAN Are Supported
NAME The plan name that is passed from an application program at execution time.
CREATOR The userid under which the plan macros are stored. This is currently limited to eight
characters, since that is the size of the authorization ID passed from a DB2 application.
The remaining columns in SYSIBM.SYSPLAN are set to default values and not
used by TS/API.
SYSIBM .SYSPLANAUTH
described exactly as in DB2. The Teradata RDBMS does not support user
privileges on application plans. The following table describes
SYSIBM.SYSPLANAUTH.
Table B-24 SYSIBM.SYSPLANAUTH Description
Teradata Teradata

AUTHHOWGOT CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
BINDAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
EXECUTEAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)

SYSIBM .SYSPLANDEP
described exactly as in DB2. The Teradata RDBMS does not support, nor does
TS/API emulate, a referential integrity relationship between plans and the
tables, views, or indexes contained in them. The following table describes
SYSIBM.SYSPLANDEP.
Table B-25 SYSIBM.SYSPLANDEP Description
Teradata Teradata
BNAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)

BCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
BTYPE CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
DNAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
SYSIBM.SYSRELS
described exactly as in DB2. The Teradata RDBMS does not support
relationships between tables. The following table describes SYSIBM.SYSRELS.
Table B-26 SYSIBM.SYSRELS Description
Teradata Teradata

RELNAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
REFTNAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)
REFTBCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
COLCOUNT SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
DELETERULE CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
RELOBID1 SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
RELOBID2 SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT

SYSIBM .SYSRESAUTH
described exactly as in DB2. The Teradata RDBMS does not support privileges
on buffer pools, storage groups, or table spaces. The following table describes
SYSIBM.SYSRESAUTH.
Table B-27 SYSIBM.SYSRESAUTH Description
Teradata Teradata

QUALIFIER CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
OBTYPE CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
USEAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)

SYSIBM.SYSSTMT
described exactly as in DB2. The Teradata RDBMS does not support the
SYSIBM.SYSSTMT table. The following table describes SYSIBM.SYSSTMT.
Table B-28 SYSIBM.SYSSTMT Description
Teradata Teradata

PLNAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
PLCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
SEQNO SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
STMTNO SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
SECTNO SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
TEXT VARCHAR(254) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(254)
SYSIBM .SYSSTOGROUP
described exactly as in DB2. The Teradata RDBMS does not support storage
groups; therefore, zero rows are always returned from any query of this table.
The following table describes SYSIBM.SYSSTOGROUP.
Table B-29 SYSIBM.SYSSTOGROUP Description
Teradata Teradata

VCATNAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
VPASSWORD CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
SPACE INTEGER SYSAPI.SYSDUMMY DUMMYNUM INTEGER
SPCDATE CHAR(5) SYSAPI.SYSDUMMY DUMMYCHR CHAR(5)
CREATEDBY CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)

SYSIBM .SYSSYNONYMS
properly supporting DB2 application programs that may interrogate for DB2
synonyms. The Teradata RDBMS does not support synonyms, so zero rows are
always returned from any query. The following table describes
SYSIBM.SYSSYNONYMS.
Table B-30 SYSIBM.SYSSYNONYMS Description
Teradata Teradata
NAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)

TBCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
SYSIBM .SYSTABAUTH
This view joins data from the DBC.ACCESSRIGHTS, DBC.TVM, and
DBC.DBASE catalog tables to emulate the SYSIBM.SYSTABAUTH table.
SYSIBM.SYSTABAUTH records user privileges on tables and views. The
following table describes SYSIBM.SYSTABAUTH.

Table B-31 SYSIBM.SYSTABAUTH Description
Teradata
RDBMS Type

GRANTEE CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
DBNAME CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
SCREATOR CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
STNAME CHAR(18) DBC.TVM TVMNAME CHAR(31)
TCREATOR CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
TTNAME CHAR(18) DBC.TVM TVMNAME CHAR(31)
AUTHHOWGOT CHAR(1) CHAR(1)
DATEGRANTED CHAR(6) ? CHAR(6)
TIMEGRANTED CHAR(8) ? CHAR(8)
UPDATECOLS CHAR(1) CHAR(1)
ALTERAUTH CHAR(1) DBC.ACCESSRIGHTS calculated from CHAR(1)
ACCESSRIGHT
calculated from
DELETEAUTH CHAR(1) DBC.ACCESSRIGHTS CHAR(1)
ACCESSRIGHT
calculated from
INDEXAUTH CHAR(1) DBC.ACCESSRIGHTS ACCESSRIGHT CHAR(1)
calculated from
ACCESSRIGHT
INSERTAUTH CHAR(1) DBC.ACCESSRIGHTS CHAR(1)
calculated from
ACCESSRIGHT
SELECTAUTH CHAR(1) DBC.ACCESSRIGHTS CHAR(1)
calculated from
ACCESSRIGHT
UPDATEAUTH CHAR(1) DBC.ACCESSRIGHTS N CHAR(1)

GRANTEELOCATION CHAR(16) CHAR(16)
LOCATION CHAR(16) CHAR(16)
COLLID CHAR(18) CHAR(18)
COLTOKEN CHAR(8) CHAR(8)
CAPTUREAUTH CHAR(1) CHAR(1)

The following table describes how each column is supported and how its value
Table B-32 How Columns in SYSIBM.SYSTABAUTH Are Supported
GRANTOR The Teradata RDBMS user who granted the access right. The DB2 length of eight is
GRANTEE The Teradata RDBMS user who was granted the access right. The DB2 length of eight is
GRANTEETYPE Set to blank, reflecting that the grantee is a user. The Teradata RDBMS cannot grant
access rights to programs, so only blank is valid.
DBNAME Name of the database on which GRANTOR has authority. The DB2 length of eight is
expanded to 31 to accommodate the longer database names available in Teradata SQL.
SCREATOR The Teradata RDBMS userid who created the table or view on which rights have been
granted. The DB2 length of eight is expanded to 31 to accommodate the longer userid
names available in Teradata SQL.
STNAME The name of the table or view on which rights have been granted. The DB2 length of
eight is expanded to 31 to accommodate the longer table names available in Teradata
SQL.
TCREATOR The same value as SCREATOR. This value is not valid for views, since the Teradata
RDBMS provides information only about the rights to the view itself, not about
underlying tables. Like SCREATOR, this column is expanded to 31 characters.
TTNAME The same value as STNAME. This value is not valid for views, since the Teradata
RDBMS provides information only about the rights to the view itself, not about
underlying tables. Like STNAME, this column is expanded to 31 characters.
AUTHHOWGOT Set to blank. The Teradata RDBMS does not support authorization level.
TIMESTAMP Set to ?, indicating that no timestamp is available. This column is generally not used by
DATEGRANTED Set to ?, indicating that no grant date is available. This column is generally not used by
TIMEGRANTED Set to ?, indicating that no time granted is available. This column is generally not used
by users or application programs.
UPDATECOLS Set to blank, indicating that any update privileges apply equally to all columns in a
table.
ALTERAUTH Whether GRANTEE can alter table:

Blank - no privilege
Y - privilege

Table B-32 (Continued) How Columns in SYSIBM.SYSTABAUTH Are Supported
DELETEAUTH Whether GRANTEE can delete rows from the table or view:
blank - no privilege
Y - privilege
INDEXAUTH Whether GRANTEE can create indexes on the table:

Y - privilege
INSERTAUTH Whether GRANTEE can insert rows into a table or view:

Y - privilege
SELECTAUTH Whether GRANTEE can select rows from a table or view:

Y - privilege
UPDATEAUTH Whether GRANTEE can update rows in a table or view:

Y - privilege
GRANTEELOCATIO Not used.

N
LOCATION If the GRANTEE is package-id, the location name.
COLLID If the GRANTEE is package-id, the collection name.
CONTOKEN If the GRANTEE is package-id, the consistency token.
CAPTUREAUTH Not used.

SYSIBM .SYSTABLEPART
This table emulates the SYSIBM.SYSTABLEPART table. The table contains one
row for the DSNDB04 table space. The following table describes
SYSIBM.SYSTABLEPART.
Table B-33 SYSIBM.SYSTABLEPART Description

IBM Name IBM Type
PARTITION SMALLINT 0 SMALLINT

TSNAME CHAR(8) DSNDB04 CHAR(8)
DBNAME CHAR(8) CHAR(8)
IXNAME VARCHAR(18) VARCHAR(18)
IXCREATOR CHAR(8) CHAR(8)
PQTY INTEGER 0 INTEGER
SQTY SMALLINT 0 SMALLINT
STORTYPE CHAR(1) E CHAR(1)
STORNAME CHAR(8) CHAR(8)
VCATNAME CHAR(8) CHAR(8)
CARD INTEGER -1 INTEGER
FARINDREF INTEGER -1 INTEGER
NEARINDREF INTEGER -1 INTEGER
PERCACTIVE SMALLINT -1 SMALLINT
PERCDROP SMALLINT -1 SMALLINT
LIMITKEY VARCHAR(512) 0 VARCHAR(512)
FREEPAGE SMALLINT 0 SMALLINT
PCTFREE SMALLINT 0 SMALLINT
CHECKFLAG CHAR(1) CHAR(1)
CHECKRID CHAR(4) CHAR(4)

Table B-34 How Columns in SYSIBM.SYSTABLEPART Are Supported
IBM Name Description Setting
PARTITION Table not partitioned 0
TSNAME Table space name DSNDB04
DBNAME Database name Blanks
IXNAME Partitioned indexes not defined under Teradata SQL environment Blanks
IXCREATOR Owner of the index Blanks
PQTY Storage groups not used 0
SQTY Storage groups not used 0
STORTYPE Storage allocation explicit. Storage groups not used E
STORNAME Blanks
VCATNAME VSAM not supported in Teradata SQL environment Blanks
CARD Setting indicates statistics not gathered -1
FARINDEF Setting indicates statistics not gathered -1
NEARINDREF Setting indicates statistics not gathered -1
PERCACTIVE Setting indicates statistics not gathered -1
PERCDROP Setting indicates statistics not gathered -1
IBMREQD Whether the row comes from the basic machine-readable tape N
LIMITKEY Table space not defined 0
FREEPAGE Pages left as free space not defined under Teradata SQL environment 0
PCTFREE No page left as free space 0
CHECKFLAG Table not partitioned Blank
CHECKRID Table not partitioned Blanks

SYSIBM.SYSTABLES
This view joins data from the DBC.DBASE and DBC.TVM catalog tables to
emulate the SYSIBM.SYSTABLES table. Each row defines one table or view
stored in the Teradata RDBMS system catalog. The following table describes
SYSIBM.SYSTABLES:
Table B-35 SYSIBM.SYSTABLES Description
Teradata RDBMS
Table
NAME VARCHAR (18) DBC.TVM TVMNAME VARCHAR(31)
CREATOR CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
TYPE CHAR(1) DBC.TVM TABLEKIND CHAR(1)
DBNAME CHAR(8) DBC.TVM TVMNAME VARCHAR(31)
OBID SMALLINT 0 SMALLINT
COLCOUNT SMALLINT DBC.TVM calculate from SMALLINT

NEXT-FIELDID
EDPROC CHAR(8) CHAR(8)
VALPROC CHAR(8) CHAR(8)
CLUSTERTYPE CHAR(1) CHAR(1)
CLUSTERID INTEGER 0 INTERGER
CARD INTEGER -1 INTEGER
NPAGES INTEGER -1 INTEGER
PCTPAGES SMALLINT -1 SMALLINT
REMARKS VARCHAR(254) DBC.TVM COMMENTSTRING VARCHAR(254)
PARENTS SMALLINT 0 SMALLINT
CHILDREN SMALLINT 0 SMALLINT
KEYCOLUMNS SMALLINT 0 SMALLINT
RECLENGTH SMALLINT 32000 SMALLINT
STATUS CHAR(1) CHAR(1)
KEYOBID SMALLINT 0 SMALLINT
LABEL VARCHAR(30) DBC.TVM COMMENTSTRING VARCHAR(254)
CHECKFLAG CHAR(1) CHAR(1)

Table B-35 (Continued) SYSIBM.SYSTABLES Description
Teradata RDBMS
Table
CHECRID CHAR(4) CHAR(4)
AUDITING CHAR(1) CHAR(1)
LOCATION CHAR(16) CHAR(16)
TBCREATOR CHAR(8) CHAR(8)
TBNAME VARCHAR(18) VARCHAR(18)
CREATEDTS CHAR(12) CHAR(12)
ALTEREDTS CHAR(12) CHAR(12)
DATACAPTURE CHAR(1) CHAR(1)
RBA1 CHAR(6) X000000000000 CHAR(6)
RBA2 CHAR(6) X000000000000 CHAR(6)
Table B-36 How Columns in SYSIBM.SYSTABLES Are Supported
NAME The name of a table or view in the Teradata RDBMS. The DB2 length of 18 characters is
expanded to a length of 31 to allow for longer table names on the Teradata RDBMS.
CREATOR The name of the user who created the table or view. The DB2 length of 8 characters is
expanded to a length of 31 to allow for longer userids on the Teradata RDBMS.
TYPE Indicates whether this description applies to a table (T) or a view (V).
DBNAME The name of the user who created the table or view. This column is identical to
CREATOR. The DB2 length of 8 characters is expanded to a length of 31 to allow for
longer userids on the Teradata RDBMSRDBMS.
TSNAME The Teradata RDBMS does not support the concept of table spaces. The table space name
reflected to the application is the same as the table name.
DBID Set to zero, indicating that this is a DB2 view. This indication may generate erroneous
results if an application depends on the value stored in this column. A valid database ID
is not available in the Teradata RDBMS for this column.
OBID Set to zero, indicating that this is a DB2 view. This indication may generate erroneous
results if an application depends on the value stored in this column. A valid table ID is
not available in the Teradata RDBMS for this column.
COLCOUNT Number of columns in a table or view.

Table B-36 (Continued) How Columns in SYSIBM.SYSTABLES Are Supported
EDPROC Set to blank, indicating that an edit procedure does not exist for this table. The Teradata
RDBMS does not support edit procedures.
VALPROC Set to blank, indicating that a validation procedure does not exist for this table. The
Teradata RDBMS does not support validation procedures.
CLUSTERTYPE Set to blank and not used in DB2 or in the Teradata RDBMS.
CLUSTERRID Set to zero and not used in DB2 or in the Teradata RDBMS.
CARD Set to -1, indicating that statistics have not been collected on this table.
NPAGES Set to -1, indicating that statistics have not been collected on this table.
PCTPAGES Set to -1, indicating that statistics have not been collected on this table.
REMARKS Contains the table comment.
PARENTS Set to zero. Parent relationship information is not available on the Teradata RDBMS.
CHILDREN Set to zero. Child relationship information is not available on the Teradata RDBMS.
KEYCOLUMNS Set to zero, indicating that a DB2 primary key does not exist.
RECLENGTH Set to 32000, the largest possible record length that can be returned from the Teradata
RDBMS. RECLENGTH does not reflect the actual record length. If an application uses
this information to allocate buffer space, excess memory may be used. If the application
depends on this value to be correct, the application may not function properly.
STATUS Set to blank, indicating that a primary key does not exist.
KEYOBID Set to zero, indicating that a link to a primary key or index does not exist.
LABEL The same as the REMARKS column because the Teradata RDBMS stores only one
comment per table. The DB2 length of 30 characters is expanded to a length of 254, since
the table comment for the Teradata RDBMS is much longer than the label allowed in DB2.
CHECKFLAG Set to blanks. The table does not contain rows that violate referential constraints.
CHECKRID Set to blanks. The table is not in a check pending state.
AUDITING Set to blanks. Audit none.
CREATEDBY Set to SYSIBM.
LOCATION Set to blanks. Not used.
TBCREATOR Set to blanks. Not used.
TBNAME Set to blanks. Not used.
CREATEDTS The timestamp of the CREATE for table, view, alias
ALTERDTS The timestamp of ALTER for tables

Table B-36 (Continued) How Columns in SYSIBM.SYSTABLES Are Supported
DATACAPTURE The value of the DATA CAPTURE option for tables (Y or blank)
RBA1 The log RBA when the tables were created
RBA2 The log RBA when the tables were last altered

SYSIBM .SYSTABLESPACE
This view emulates the SYSIBM.SYSTABLESPACE table. The table contains a
row for the DSNDB04 table space. The following table describes
SYSIBM.SYSTABLESPACE.
Table B-37 SYSIBM.SYSTABLESPACE Description

IBM Name IBM Type
NAME CHAR(8) DSNDB04 CHAR(8)

CREATOR CHAR(8) SYSIBM CHAR(8)
DBNAME CHAR(8) DSNDB04 CHAR(8)
OBID SMALLINT 4 SMALLINT
PSID SMALLINT 4 SMALLINT
BPOOL CHAR(8) BP0 CHAR(8)
PARTITIONS SMALLINT 0 SMALLINT
LOCRULE CHAR(1) A CHAR(1)
PGSIZE SMALLINT 4 SMALLINT
ERASERULE CHAR(1) N CHAR(1)
STATUS CHAR(1) A CHAR(1)
IMPLICIT CHAR(1) Y CHAR(1)
NTABLES SMALLINT 32767 SMALLINT
NACTIVE INTEGER 0 INTEGER
DSETPASS CHAR(8) CHAR(8)
CLOSERULE CHAR(1) Y CHAR(1)
SPACE INTEGER 0 INTEGER
ROOTNAME VARCHAR(18) VARCHAR(18)
ROOTCREATOR CHAR(8) CHAR(8)
SEGSIZE SMALLINT 0 SMALLINT

Table B-38 How Columns in SYSIBM.SYSTABLESPACE Are Supported
IBM Name Description Setting
NAME Table space name DSNDB04
CREATOR Owners authorization ID SYSIBM
DBNAME Database name DSNDB04
DBID Databases internal identifier 4
OBID Table space files internal identifier 4
PSID Table spaces internal identifier 4
BPOOL Name of buffer pool used for table space BP0
PARTITIONS Table space not partitioned 0
LOCKRULE Lock size is any A
PGSIZE Page size in kilobytes 4
ERASERULE Erasure of data sets N
STATUS Table space is available A
IMPLICIT Table space is created implicitly Y
NTABLES Number of tables defined in the table space 32767
NACTIVE Statistics not gathered 0
DSETPASS Not supported in the Teradata SQL environment Blanks
CLOSERULE Are data sets closed when table space is not used? Y
SPACE Is there DASD storage for table space? 0
IBMREQD Does the row come from the basic machine-readable tape? N
ROOTNAME Not a structured table space Blanks
ROOTCREATOR Root table does not exist Blanks
SEGSIZE Table space not segmented 0
CREATEDBY Primary authorization ID of user who created the table space SYSIBM
SYSIBM .SYSUSERAUTH
application programs that may interrogate for DB2 system privileges. The
Teradata RDBMS does not support system privileges held by users, so zero

rows are always returned from any query. The following table describes
SYSIBM.SYSUSERAUTH.
Table B-39 SYSIBM.SYSUSERAUTH Description

IBM Name IBM Type

ALTERBPAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
BINDADDAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
BSDSAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
CREATEDBAAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
CREATEDBCAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
CREATESGAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
DISPLAYAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
RECOVERAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
STOPALLAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
STOSPACEAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
SYSADMAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
SYSOPRAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
MON1AUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
MON2AUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
CREATEALIASAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
SYSCTRLAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
BINDAGENTAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
ARCHIVEAUTH CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
FILLER1 CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
FILLER2 CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)

SYSIBM .SYSVIEWDEP
application programs that may interrogate for the dependencies of views on
tables and other views. Teradata SQL does not support this table, so zero rows
are always returned from any query. The following table describes
SYSIBM.SYSVIEWDEP.
Table B-40 SYSIBM.SYSVIEWDEP Description
IBM Name IBM Type Teradata RDBMS Table Teradata RDBMS Name Teradata RDBMS Type
BNAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)

DNAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)
DCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
SYSIBM.SYSVIEWS
This view contains data from the DBC.TVM catalog table to emulate the
SYSIBM.SYSVIEWS table. The table contains one or more rows for each view.
The following table describes SYSIBM.SYSVIEWS.
Table B-41 SYSIBM.SYSVIEWS Description
Teradata
RDBMS Table
NAME VARCHAR(8) DBC.TVM TVMNAME VARCHAR(31)

CREATOR CHAR(8) DBC.TVM CREATORNAME CHAR(31)
SEQNO SMALLINT 1 SMALLINT
CHECK CHAR(1) N CHAR(1)
TEXT VARCHAR(254) ? VARCHAR(254)

Table B-42 How Columns in SYSIBM.SYSVIEWS Are Supported
NAME View name. Field length of eight is expanded to 31 to accommodate the longer names
in Teradata SQL.
CREATOR View owners authorization ID. Field length of eight is expanded to 31 to

SEQNO Sequence number of row; set to 1.
CHECK Set to N. Indicates CHECK option of the DB2 CREATE VIEW statement not used
IBMREQD Set to N, indicating the row does not come from the basic machine-readable tape.
TEXT Set to ?. Text portion not supported by the Teradata SQL environment.
SYSIBM.SYSVLTREE
application programs that may interrogate SYSIBM.SYSVLTREE. The Teradata
RDBMS does not support parse trees, so zero rows are always returned from
any query. The following table describes SYSIBM.SYSVLTREE.
Table B-43 SYSIBM.SYSVLTREE Description

IBM Name IBM Type

VTREE VARCHAR(4000) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(4000)

SYSIBM .SYSVOLUMES
described exactly as in DB2. This view is furnished solely to properly support
DB2 application programs that may interrogate for storage group volumes. The
Teradata RDBMS does not support storage groups, so zero rows are always
returned from any query. The following table describes
SYSIBM.SYSVOLUMES.
Table B-44 SYSIBM.SYSVOLUMES Description

IBM Name IBM Type
SGNAME CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)

SGCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
VOLID CHAR(6) SYSAPI.SYSDUMMY DUMMYCHR CHAR(6)
SYSIBM.SYSVTREE
This view contains data from the DBC.TVM catalog table to emulate the
SYSIBM.SYSVTREE table. For each view, the table contains one or more rows,
with the parse tree of the view. The following table describes
SYSIBM.SYSVTREE.
Table B-45 SYSIBM.SYSVTREE Description

IBM Name IBM Type
NAME VARCHAR(18) DBC.TVM TVMNAME VARCHAR(31)

CREATOR CHAR(8) DBC.TVM CREATORNAME CHAR(31)
TOTLEN INTEGER 0 INTEGER
VTREE VARCHAR(4000) VARCHAR(4000)

Table B-46 How Columns in SYSIBM.SYSVTREE Are Supported
NAME View name. Field length of eight is expanded to 31 to accommodate the longer names
in Teradata SQL.
CREATOR View owners authorization ID. Field length of eight is expanded to 31 to

TOTLEN Total length of parse tree; set to 0. Parse trees are not defined in the Teradata SQL
environment.
VTREE Set to blanks. Parse trees are not defined in the Teradata SQL environment.
Emulation of the SQL/DS SYSTEM System Catalog Tables

The SYSTEM database uses views of the Teradata RDBMS catalog tables to
emulate SQL/DS SYSTEM system catalog tables. The emulated SQL/DS
catalog tables are as follow:
Table B-47 SQL/DS Catalog
IBM Name What It Describes
SYSTEM.SYSACCESS Tables containing access modules
SYSTEM.SYSCATALOG Tables or views
SYSTEM.SYSCHARSETS Various character sets
SYSTEM.SYSCOLAUTH Update privileges on tables and views, on a column basis
SYSTEM.SYSCOLUMNS Columns
SYSTEM.SYSDBSPACES SQL/DS DBSPACEs
SYSTEM.SYSDROP SQL/DS objects to be dropped
SYSTEM.SYSINDEXES Indexes
SYSTEM.SYSKEYCOLS Columns of keys
SYSTEM.SYSOPTIONS SQL/DS installation options
SYSTEM.SYSPROGAUTH User privileges on macros
SYSTEM.SYSSYNONYMS Table and view synonyms
SYSTEM.SYSTABAUTH Table access rights
SYSTEM.SYSUSAGE Dependencies between SQL/DS objects

Table B-47 (Continued) SQL/DS Catalog
SYSTEM.SYSUSERAUTH Special user privileges
SYSTEM.SYSVIEWS View definitions
SYSTEM.SYSACCESS
described exactly as in SQL/DS. This view is furnished only to support
SQL/DS application programs that may interrogate for tables containing
SQL/DS access modules. The Teradata RDBMS does not support access
modules. The following table describes SYSTEM.SYSACCESS.
Table B-48 SYSTEM.SYSACCESS Description

IBM Name IBM Type

DBSPACENO SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
TABID SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
LINKID SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
FIRSTROW INTEGER SYSAPI.SYSDUMMY DUMMYNUM INTEGER
TIMESTAMPE CHAR(17) SYSAPI.SYSDUMMY DUMMYCHR CHAR(17)
VALID CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
TABTYPE CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
CONSTKN CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
PLABEL VARCHAR(30) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(30)

SYSTEM .SYSCATALOG
This view joins data from the DBC.DBASE and DBC.TVM catalog tables to
emulate the SYSTEM.SYSCATALOG table. Each row of the view defines one
table or view. The following table describes SYSTEM.SYSCATALOG.
Table B-49 SYSTEM.SYSCATALOG Description

IBM Name IBM Type
TNAME VARCHAR(18) DBC.TVM TVMNAME VARCHAR(31)

TABLETYPE CHAR(1) DBC.TVM calculated from CHAR(1)
TABLEKIND
calculated from
NCOLS SMALLINT DBC.TVM SMALLINT
COLCOUNT
COMMENTSTRING
REMARKS VARCHAR(254) DBC.TVM VARCHAR(254)
calculated from
DBSPACENO SMALLINT DBC.DBASE DATABASEID SMALLINT
DATABASENAME
DBSPACENAME VARCHAR(18) DBC.DBASE TVMID VARCHAR(31)
TABID SMALLINT DBC.TVM D BYTE(4)
CLUSTERTYPE CHAR(1) 0 CHAR(1)
CLUSTERROW INTEGER -1 INTEGER
AVGROWLEN SMALLINT -1 SMALLINT
ROWCOUNT INTEGER -1 INTEGER
NPAGES INTEGER -1 INTEGER
PCTPAGES SMALLINT 0 SMALLINT
NOVERFLOW INTEGER 0 SMALLINT
LFDTABID SMALLINT 0 SMALLINT
LFDLINK SMALLINT 0 SMALLINT
LFDDBSPACE SMALLINT COMMENTSTRING SMALLINT
TLABEL VARCHAR(30) DBC.TVM 0 VARCHAR(254)
PARENTS SMALLINT 0 SMALLINT
DEPENDENTS SMALLINT 0 SMALLINT
INACTIVE SMALLINT SMALLINT

Table B-50 How Columns in SYSTEM.SYSCATALOG Are Supported
TNAME Table or view name. The SQL/DS length of 18 characters is expanded to 31 to

accommodate the longer table names available in Teradata SQL.
CREATOR The userid under which the table or view was created. The SQL/DS length of
eight is expanded to a length of 31 to accommodate the longer userid names
available in Teradata SQL.
TABLETYPE Whether the description applies to a real table (R) or a view (V). The value is
obtained from TVM.TABLEKIND. The Teradata RDBMS value T (table) is
converted to R. The type for a view (V) remains the same.
NCOLS Number of columns in a table or view.
REMARKS The comments on the table.
DBSPACENO The Teradata RDBMS userid number under which the table or view was created.
The userid number is calculated from the 4-byte field DBASE.DATABASEID.
DBSPACENAME The Teradata RDBMS userid under which the table or view was created. The
SQL/DS length of 18 is expanded to a length of 31 to accommodate the longer
database names available in Teradata SQL.
TABID The table id of the Teradata RDBMS table. This column is not converted from 4-
byte to SMALLINT and is truncated by the Teradata RDBMS, so it may reflect
erroneous values.
CLUSTERTYPE Set to D, indicating the table is clustered by default rules. The Teradata RDBMS
does automatic clustering, so this field is not relevant to it.
CLUSTERROW Set to zero, indicating that clustering statistics have not been collected on this
table.
AVGROWLEN Set to -1, indicating that statistics have not been collected on this table.
ROWCOUNT Set to -1, indicating that statistics have not been collected on this table.
NPAGES Set to -1, indicating that statistics have not been collected on this table.
PCTPAGES Set to -1, indicating that statistics have not been collected on this table.
NOVERFLOW Set to 0, indicating that no rows have overflowed.
LFDTABID Set to 0, indicating that no long fields exist in this table.
LFDLINK Set to 0, indicating that no long fields exist in this table.
LFDDBSPACE Set to 0, indicating that no long fields exist in this table.
TLABEL The same as REMARKS. The SQL/DS length of 30 is expanded to 254 to

accommodate the longer table comments available in Teradata SQL.
PARENTS The number of parent relationships in which the tables is a dependent.

Table B-50 (Continued) How Columns in SYSTEM.SYSCATALOG Are Supported
DEPENDENTS The number of dependent relationships in which the tables is a parent.
INACTIVE The number of inactive keys for the tables.
SYSTEM .SYSCHARSETS
The rows in table SYSTEM.SYSCHARSETS contain information about various
character sets. The following table describes SYSTEM.SYSCHARSETS.
Table B-51 SYSTEM.SYSCHARSETS Description

IBM Name IBM Type
NAME VARCHAR(18) ENGLISH VARCHAR(18)

CHARCLASS CHAR(192) calculated CHAR(192)
CHARTRANS CHAR(192) calculated CHAR(192)
The following table describes how each column is supported:
Table B-52 How Columns in SYSTEM.SYSCHARSETS Are Supported
NAME The name that identifies the character set. NAME is usually the name of the
character set itself.
CHARCLASS The character classifications for this character set.
CHARTRANS The character translation values for this character set.

SYSTEM .SYSCOLAUTH
SQL/DS application programs that may interrogate for SQL/DS column
authorizations. The Teradata RDBMS does not support column authorizations,
so zero rows are always returned from any query. The following table describes
SYSTEM.SYSCOLAUTH.
Table B-53 SYSTEM.SYSCOLAUTH Description

IBM Name IBM Type

SYSTEM .SYSCOLUMNS
This view joins data from the DBC.DBASE, DBC.TVM, and DBC.TVFIELDS
catalog tables to emulate the SYSTEM.SYSCOLUMNS table. Each row defines
one column from a table or view. SYSTEM.SYSCOLUMNS also uses the
auxiliary table SYSAPI.DATATYPES to convert Teradata RDBMS column types
to SQL/DS column types. The following table describes
SYSTEM.SYSCOLUMNS.

Table B-54 SYSTEM.SYSCOLUMNS Description

IBM Name IBM Type
CNAME VARCHAR(18) DBC.TVFIELDS FIELDNAME VARCHAR(31)

TNAME VARCHAR(18) DBC.TVM TVMNAME VARCHAR(31)
COLNO SMALLINT DBC.TVFIELDS calculated from SMALLINT
FIELDID
calculated from
COLTYPE CHAR(8) DBC.TVFIELDS CHAR(8)
FIELDTYPE
calculated from
LENGTH CHAR(7) DBC.TVFIELDS FIELDTYPE CHAR(7)
calculated from
FIELDTYPE
SYSLENGTH SMALLINT DBC.TVFIELDS SMALLINT
NULLABLE
COMMENTSTRING
NULLS CHAR(1) DBC.TVFIELDS CHAR(1)
-1
REMARKS VARCHAR(254) DBC.TVFIELDS VARCHAR(254)

COLCOUNT INTEGER INTEGER

HIGH2KEY VARCHAR(12) VARCHAR(12)
-1
LOW2KEY VARCHAR(12) VARCHAR(12)
N
AVGCOLLEN SMALLINT SMALLINT
FIELDTITLE
ORDERFIELD CHAR(1) CHAR(1)
NULL
CLABEL VARCHAR(30) DBC.TVFIELDS VARCHAR(60)
NULL
COLINFO CHAR(1) CHAR(1)
1
SUBTYPE CHAR(1) CHAR(1)
N
CCSID INTEGER INTEGER
FLDPROC CHAR(1) CHAR(1)
Table B-55 How Columns in SYSTEM.SYSCOLUMNS Are Supported
CNAME Column name. The SQL/DS length of 18 is expanded to 31 to accommodate the

longer fieldnames available in Teradata SQL.
TNAME The table name containing the column. The SQL/DS length of 18 is expanded to 31
to accommodate the longer table names available in Teradata SQL.
CREATOR The userid under which the table or view was created. The SQL/DS length of 8 is
expanded to 31 to accommodate the longer database names available in Teradata
SQL.

Table B-55 (Continued) How Columns in SYSTEM.SYSCOLUMNS Are Supported
COLNO Column number
COLTYPE The column type of the field, in SQL/DS format.
LENGTH Field length, in characters
SYSLENGTH Field length, as an integer
NULLS Whether the field can be set to nulls
REMARKS Comments on the column
COLCOUNT Set to -1, indicating that statistics have not been gathered. Teradata RDBMS
statistics are not relevant to SQL/DS use.
HIGH2KEY Set to blank, indicating no HIGH2KEY information. The Teradata RDBMS does not
have relevant key range information.
LOW2KEY Set to blank, indicating no LOW2KEY information. The Teradata RDBMS does not
have relevant key range information.
AVGCOLLEN Set to -1, indicating that statistics have not been gathered. Teradata RDBMS
statistics are not relevant to SQL/DS use.
ORDERFIELD Set to N, indicating that the field is not used for physical clustering. The Teradata
RDBMS has no equivalent to physical clustering (all tables are automatically
clustered by distributing rows to multiple AMPs).
CLABEL The columns field title. The SQL/DS length of 30 is expanded to 60 to

accommodate the longer field titles available in Teradata SQL.
COLINFO Whether the SYSTEM.SYSCOLSTATS table contains additional information about

the column
SUBTYPE Applicable for CHAR, VARCHAR, and LONG VARCHAR
CCSID For conversion
FLDPROC Whether the column has a field procedure

SYSTEM .SYSDBSPACES
This view retrieves data from the DBC.DBASE catalog table to emulate the
SYSTEM.SYSDBSPACES table. Each row contains information about each
SQL/DS DBSPACE. The following table describes SYSTEM.SYSDBSPACES.
Table B-56 SYSTEM.SYSDBSPACES Description

IBM Name IBM Type
DBSPACENAME VARCHAR(18) DBC.DBASE DATABASENAME CHAR(31)

DBSPACENO SMALLINT DBC.DBASE calculated from SMALLINT
DATABASEID
DATABASENAME
OWNER CHAR(8) DBC.DBASE CHAR(8)
2
DBSPACETYPE SMALLINT VARCHAR(18)
calculated
NTABS SMALLINT SMALLINT
calculated
NPAGES INTEGER INTEGER
1
NRHEADER SMALLINT VARCHAR(18)
33
PCTINDX SMALLINT VARCHAR(18)
15
FREEPCT SMALLINT VARCHAR(18)
T
LOCKMODE CHAR(1) CHAR(1)
-1
NACTIVE INTEGER INTEGER
0
POOL SMALLINT SMALLINT
may vary from the expected SQL/DS value.
Table B-57 How Columns in SYSTEM.SYSDBSPACES Are Supported
DBSPACENAME The name given to DBSPACE. The SQL/DS length of 18 is expanded to 31 to

accommodate the longer userid names available in Teradata SQL.
DBSPACENO Internal number assigned by SQL/DS. Not defined in Teradata SQL; therefore, a
number is derived from DBC.DBASE DATABASEID.
OWNER DBSPACEs owner. The SQL/DS length of 8 is expanded to 31 to accommodate the

longer userid names available in Teradata SQL.
DBSPACETYPE Set to 2. DBSPACE is defined as private.
NTABS The number of tables in DBSPACE.
NPAGES Number of usable pages in the DBSPACE. Set to DBC.DBASE PERMSPACE/4096.
NRHEADER Number of pages to be used for DBSPACE header; set to 1.

Table B-57 (Continued) How Columns in SYSTEM.SYSDBSPACES Are Supported
PCTINDX Percentage of pages to be used for indexes; set to 33.
FREEPCT Percentage of space on each page to be kept free when rows are inserted; set to 15.
LOCKMODE Set to T, indicating row locking is to be done.
NACTIVE Set to -1, indicating no LOW2KEY information.
POOL Set to 0, indicating no storage pools.
SYSTEM.SYSDROP
SQL/DS application programs that may interrogate for SQL/DS objects to be
dropped. The following table describes SYSTEM.SYSDROP.
Table B-58 SYSTEM.SYSDROP Description

IBM Name IBM Type
DBSPACENO SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT

TABID SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
QUALF CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)

SYSTEM .SYSINDEXES
This view retrieves data from the DBC.DBASE, DBC.TVM, and DBC.INDEXES
catalog tables to emulate the SYSTEM.SYSINDEXES table. The view contains a
row for every index. The following table describes SYSTEM.SYSINDEXES.
Table B-59 SYSTEM.SYSINDEXES Description

IBM Name IBM Type
INAME VARCHAR(18) DBC.INDEXES calculated VARCHAR(18)

from TABLEID
DATABASENAME
ICREATOR CHAR(8) DBC.DBASE CHAR(31)
TVMNAME
TNAME VARCHAR(18) DBC.TVM CHAR(18)
DATABASENAME
CREATOR CHAR(8) DBC.DBASE CHAR(31)
FIELDNAME
COLNAMES VARCHAR(100) DBC.TVFIELDS VARCHAR(100)
UNIQUEFLAG
INDEXTYPE CHAR(1) DBC.INDEXES CHAR(1)
N
CLUSTER CHAR(1) CHAR(1)
0
IID SMALLINT SMALLINT

COLNUMBERS VARCHAR(34) VARCHAR(34)
0
KEYLEN SMALLINT SMALLINT
0
FIRSTKEYCOUNT INTEGER INTEGER
0
FULLKEYCOUNT INTEGER INTEGER
?
LOCKMODE CHAR(1) CHAR(1)
0
NLEAF INTEGER INTEGER
0
NLEVELS SMALLINT SMALLINT
0
IPCTFREE SMALLINT SMALLINT
0
CLUSTERRATIO SMALLINT SMALLINT
3.3.0
RELEASE CHAR(5) CHAR(5)

KEYTYPE CHAR(1) CHAR(1)

Table B-60 How Columns in SYSTEM.SYSINDEXES Are Supported
INAME The Teradata RDBMS does not support named indexes, so index name is derived from
the DBC.INDEXES TABLEID and DBC.INDEXES INDEXNUMBER.
ICREATOR Index creators userid.The SQL/DS length of 8 is expanded to 31 to accommodate the

longer userid names available in Teradata SQL.
TNAME Table where index is defined.
CREATOR Table creators userid.The SQL/DS length of eight is expanded to 31 to accommodate

the longer userid names available in Teradata SQL.
COLNAMES Column name in the index, in SQL/DS format.
INDEXTYPE Whether index is unique.
CLUSTER Set to N. Index not clustered.
IID Internal index identifier not supported by Teradata SQL. Set to 0.
COLNUMBERS Not supported by Teradata SQL. Set to blanks.
KEYLEN Average length of key field; not supported by Teradata SQL. Set to 0.
FIRSTKEYCOUNT Not supported by Teradata SQL. Set to 0.
FULLKEYCOUNT Not supported by Teradata SQL. Set to 0.
LOCKMODE Not supported by Teradata SQL. Set to ?.
NLEAF Not supported by Teradata SQL. Set to 0.
NLEVELS Not supported by Teradata SQL. Set to 0.
IPCTFREE Not supported by Teradata SQL. Set to 0.
CLUSTERRATIO Not supported by Teradata SQL. Set to 0.
RELEASE Whether the index was created before or after the installation of SQL/DS V3R3.
KEYTYPE Whether the index is used by the primary key.

SYSTEM .SYSKEYCOLS
SQL/DS application programs that may interrogate for SQL/DS key
composition. The following table describes SYSTEM.SYSKEYCOLS.
Table B-61 SYSTEM.SYSKEYCOLS Description

IBM Name IBM Type

TCREATOR CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
KEYTYPE CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
KEYNAME CHAR(18) SYSAPI.SYSDUMMY DUMMYCHR CHAR(18)
CNAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)
KEYORD SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
TABLEORD SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
DATACODE SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
SYSLENGTH SMALLINT SYSAPI.SYSDUMMY DUMMYNUM SMALLINT
TIMESTAMP TIMESTAMP SYSAPI.SYSDUMMY DUMMYCHR CHAR(12)
FLDPROC CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
CCSID INTEGER SYSAPI.SYSDUMMY DUMMYNUM INTEGER
SYSTEM .SYSOPTIONS
TS/API uses this table to build a set of values that corresponds to the SQL/DS
installation options. These options are built at installation time. The following
table describes SYSTEM.SYSOPTIONS.
Table B-62 SYSTEM.SYSOPTIONS Description

IBM Name IBM Type
SQLOPTION VARCHAR(18) constant VARCHAR(18)

VALUE VARCHAR(18) constant VARCHAR(18)
REMARKS VARCHAR(254) constant VARCHAR(254)
Warning User modification of SYSTEM.SYSOPTIONS may invalidate the integrity of

TS/API.

Table B-63 How Columns in SYSTEM.SYSOPTIONS Are Supported
SQLOPTION Name of the option being defined
VALUE Option value
REMARKS Optional remarks describing the option
SYSTEM .SYSPROGAUTH
This view retrieves data from the DBC.DBASE, DBC.ACCESSRIGHTS, and
DBC.TVM catalog tables to emulate the SYSTEM.SYSPROGAUTH table. The
view contains user privileges on macros. The following table describes
SYSTEM.SYSPROGAUTH.
Table B-64 SYSTEM.SYSPROGAUTH Description

IBM Name IBM Type

GRANTEE CHAR(8) DBC.DBASE DATABASENAMEI CHAR(8)
PROGNAME VARCHAR(8) DBC.TVM TVMNAME CHAR(31)
TIMESTAMP CHAR(12) CHAR(12)
RUNAUTH CHAR(1) Y CHAR(1)
Table B-65 How Columns in SYSTEM.SYSPROGAUTH Are Supported
GRANTOR Userid of person who granted run privilege. The SQL/DS length of eight is expanded to 31
to accommodate the longer userid names available in Teradata SQL.
GRANTEE Userid of person who holds the RUN privilege.
CREATOR Userid of person who preprocessed the macro. The SQL/DS length of eight is expanded to 31
to accommodate the longer userid names available in Teradata SQL.
PROGNAME Name of macro that may be run by the grantee. The SQL/DS length of eight is expanded to
31 to accommodate the longer userid names available in Teradata SQL.

Table B-65 (Continued) How Columns in SYSTEM.SYSPROGAUTH Are Supported
TIMESTAMP Not supported by Teradata SQL. Set to blanks.
RUNAUTH Set to Y. User only allowed to run the macro.
SYSTEM .SYSSYNONYMS
SQL/DS application programs that may interrogate for SQL/DS synonyms.
Since the Teradata RDBMS does not support synonyms, zero rows are always
returned from any query of this table. The following table describes
SYSTEM.SYSSYNONYMS.
Table B-66 SYSTEM.SYSSYNONYMS Description

IBM Name IBM Type
USERID CHAR(8) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)

ALTNAME VARCHAR(18) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)

SYSTEM .SYSTABAUTH
This view joins data from the DBC.DBASE, DBC.TVM, and
DBC.ACCESSRIGHTS catalog tables to emulate the SYSTEM.SYSTABAUTH
table. The following table describes SYSTEM.SYSTABAUTH.
Table B-67 SYSTEM.SYSTABAUTH Description

IBM Name IBM Type

GRANTEE VARCHAR(8) DBC.DBASE DATABASENAMEI CHAR(31)
SCREATOR CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
STNAME VARCHAR(18) DBC.TVM TVMNAME CHAR(31)
TCREATOR CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
TTNAME VARCHAR(18) DBC.TVM TVMNAME CHAR(31)
UPDATECOLS CHAR(1) CHAR(1)
SELECTAUTH CHAR(1) DBC.ACCESSRIGHTS calculated from CHAR(1)
ACCESSRIGHT
calculated from
INSERTAUTH CHAR(1) DBC.ACCESSRIGHTS CHAR(1)
ACCESSRIGHT
calculated from
UPDATEAUTH CHAR(1) DBC.ACCESSRIGHTS ACCESSRIGHT CHAR(1)
calculated from
ACCESSRIGHT
DELETEAUTH CHAR(1) DBC.ACCESSRIGHTS CHAR(1)
calculated from
ACCESSRIGHT
ALTERAUTH CHAR(1) DBC.ACCESSRIGHTS CHAR(1)
calculated from
ACCESSRIGHT
INDEXAUTH CHAR(1) DBC.ACCESSRIGHTS calculated from CHAR(1)
ACCESSRIGHT
REFAUTH CHAR(1) DBC.ACCESSRIGHTS CHAR(1)

Table B-68 How Columns in SYSTEM.SYSTABAUTH Are Supported
GRANTOR The Teradata RDBMS user who granted an access right. The SQL/DS length of eight is
GRANTEE The Teradata RDBMS user who was granted an access right. The SQL/DS length of eight
is expanded to 31 to accommodate the longer userid names available in Teradata SQL.
GRANTEETYPE Set to blank reflecting that the grantee is a user. The Teradata RDBMS cannot grant access
rights to programs, so only blank is valid.
SCREATOR The Teradata RDBMS userid who created the table or view on which rights have been
granted. The SQL/DS length of eight is expanded to 31 to accommodate the longer userids
STNAME The name of the table or view on which rights have been granted. The SQL/DS length of
18 is expanded to 31 to accommodate the longer table names available in Teradata SQL.
TCREATOR The same value as SCREATOR. This value is not valid for views, since the Teradata
RDBMS provides information only about the rights to the view itself. Information about
the underlying tables is not available. Like SCREATOR, this column expanded to 31
characters.
TTNAME The same value as STNAME. This value is not valid for views, since the Teradata RDBMS
provides information only about the rights to the view itself. Information about the
underlying tables is not available. Like STNAME, this column is expanded to 31
characters.
TIMESTAMP Set to ?, indicating that no timestamp is available. This column is generally not used by
UPDATECOLS Set to blank, indicating that any update privileges apply equally to all columns of a table.
SELECTAUTH Whether GRANTEE can select rows from a table or view: blank = No Privilege Y=
Privilege
INSERTAUTH Whether GRANTEE can insert rows into a table or view: blank = No Privilege Y=
Privilege
UPDATEAUTH Whether GRANTEE can update rows in a table or view: blank = No Privilege Y=
Privilege
DELETEAUTH Whether GRANTEE can delete rows from a table or view: blank = No Privilege Y=
Privilege
ALTERAUTH Whether GRANTEE can alter a table:

blank = No Privilege Y = Privilege
INDEXAUTH Whether GRANTEE can create indexes on a table:

blank = No Privilege Y = Privilege
REFAUTH Indicates REFERENCES privilege authority.

SYSTEM.SYSUSAGE
SQL/DS application programs that may interrogate for dependencies of
SQL/DS objects. The following table describes SYSTEM.SYSUSAGE.
Table B-69 SYSTEM.SYSUSAGE Description

IBM Name IBM Type
BNAME CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)

DNAME CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR VARCHAR(18)
DCREATOR CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(8)
DTYPE CHAR(1) SYSAPI.SYSDUMMY DUMMYCHR CHAR(1)
SYSTEM .SYSUSERAUTH
This view retrieves data from the DBC.DBASE catalog table to emulate the
SYSTEM.SYSUSERAUTH table. The view denotes special privileges held by
users. The following table describes SYSTEM.SYSUSERAUTH.
Table B-70 SYSTEM.SYSUSERAUTH Description

IBM Name IBM Type
BNAME CHAR(8) DBC.DBASE DATABASENAME CHAR(31)

AUTHOR CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
RESOURCEAUTH CHAR(1) Y CHAR(1)
DBAAUTH CHAR(1) Y CHAR(1)
PASSWORD CHAR(8) DBC.DBASE PASSWORDSTRING CHAR(31)
SCHEDULEAUTH CHAR(1) Y CHAR(1)

The following table describes how each column is supported and how its value
Table B-71 How Columns in SYSTEM.SYSUSERAUTH Are Supported
NAME Userid. The SQL/DS length of eight is expanded to 31 to accommodate the longer userids
AUTHOR Set to NAME. The SQL/DS length of eight is expanded to 31 to accommodate the longer
userids available in Teradata SQL.
RESOURCEAUTH Set to Y. Authorized to create new tables.
DBAAUTH Set to Y. User has DBA authority.
PASSWORD Used to verify the identity of a user.
SCHEDULEAUTH Set to Y. Authorized to CONNECT another user without specifying a password.
SYSTEM.SYSVIEWS
This view retrieves data from the DBC.DBASE and DBC.TVM catalog tables to
emulate the SYSTEM.SYSVIEWS table. The view contains the definitions of
views. The following table describes SYSTEM.SYSVIEWS.
Table B-72 SYSTEM.SYSVIEWS Description

IBM Name IBM Type
VIEWNAME VARCHAR(18) DBC.TVM TVMNAME VARCHAR(18)

VCREATOR CHAR(8) DBC.DBASE DATABASENAME CHAR(31)
SEQNO SMALLINT 1 SMALLINT
VIEWTEXT VARCHAR(254) DBC.TVM REQUESTTEXT VARCHAR(254)
VEWMAT CHAR(1) N CHAR(1)
VIEWCHECK CHAR(1) N CHAR(1)

Table B-73 How Columns in SYSTEM.SYSVIEWS Are Supported
VIEWNAME The name of the view.
VCREATOR The userid of the person who created the view. The SQL/DS length of eight is expanded to 31
to accommodate the longer userids available in Teradata SQL.
SEQNO The sequence number of the view definition. Set to 1.
VIEWTEXT Contains the SQL command that created the view.
VIEWMAT Indicates whether a view references another view, resulting in view materialization.
VIEWCHECK Indicates whether the view was created with the WITH CHECK OPTION clause.

Glossary
This chapter contains a glossary of terms relevant to TS/API.

Words that are italicized in the explanations are themselves defined in this
glossary.
The following convention was observed in alphabetizing entries in the
glossary: a blank sorts before any letter, and a dash sorts after any letter.
administrator A special user responsible for allocating Teradata RDBMS

resources to a community of users.
application program A program that performs a particular function or set of
functions that the user desires to perform.
bind Process by which the output from a precompiler is converted to a usable

control structure called a plan. Access paths to data are selected and some
authorization checking is performed.
Call Attach Facility Software that allows application programs to connect to and
use either the DB2 or Teradata RDBMS.
channel The means by which a central processor is attached to peripheral units;
the path by which data is transferred between the mainframe host and the
Teradata RDBMS hardware platform.
client A system that can execute application programs that access and
manipulate data in the Teradata RDBMS.
Customer Information Control System (CICS) An IBM monitor program for
application programs that are optimized for real-time user interaction. CICS
runs under the MVS operating system.
cursor The mechanism in SQL that moves through a multi-row response to a
SELECT or other data-returning statement. The cursor can be considered as
pointing to a current row of data.
Teradata Transparency Series/Application Programming Interface User Guide Glossary 1

Glossary
cursor isolation One of two levels of locking for a cursor. Cursor isolation levels
are repeatable read and cursor stability.
cursor stability A level of cursor isolation used by DB2 and SQL/DS that ensures
that a transaction acquires a read lock on data when it obtains addressability to
the data. The read lock is relinquished when the transaction relinquishes its
addressability to the data if the transaction has not performed any updates or
deletions. If the transaction performs any updates or deletions, the read lock is
automatically upgraded to a write lock and relinquished at end-of-transaction
time.
database In Teradata SQL, a related set of tables that share a common space
allocation and owner.
database computer A database computing system, such as the NCR DBC/1012 or the
NCR Model 3600, 3550 or 5100.
database computing system A complete hardware/software system that
provides all of the functions of a traditional database management system and
more: a non-procedural, user-friendly query language; fault-tolerant operation
with no single point of failure; multiuser access; and interactive and batch
environments.
database management system (DBMS) Computer procedures that permit the
database to be maintained independently of application programs. A database
management system provides services for data definition, data manipulation,
and data integrity.
database server A hardware/software system that processes requests from
users (clients) of a DBMS.
data integrity Data preserved in its whole state without accidental or intentional
destruction or modification.
DBMS See database management system.
DBRM Database request module. A data set member created by the DB2
precompiler that contains information regarding SQL statements. DBRMs are
used in the bind process.
Data Base 2 (DB2) IBMs relational database management system running under
MVS.
DB2 Plan A usable control structure containing access paths to data and some
access authorization that is derived from output from a precompiler.
directive A TS/API command.
Glossary 2 Teradata Transparency Series/Application Programming Interface User Guide

Glossary
dynamic SQL SQL statements that are prepared and executed while the
application program is running. The SQL source is contained in client language
variables rather than being coded in the application program.
embedded SQL All SQL statements that are contained in the application program.
Embedded SQL can be either static or dynamic.
local area network (LAN) A means of connecting workstations that allows them
to communicate with one another. The LAN is usually confined to a limited
area, such as a building.
microprocessor A computer with miniaturized elements.

Multiple Virtual Storage (MVS) One of the primary operating systems (or system
control programs) for medium and large IBM computers.
NATURAL A fourth-generation language developed and marketed by Software

AG, which operates under MVS/TSO or MVS/CICS. DB2 applications built in
NATURAL can access the Teradata RDBMS through TS/API.
parallel processing The division of a database request into two or more

components and the processing of each component separately and
simultaneously.
pass-thru facility The capability of an application (for example, SAS and QMF)
to accept SQL and pass it through for processing.
program A unit of software that performs a set of operations to satisfy the
needs of users or other programs. A program consists of one or more modules.
query A request from a database to retrieve, modify, or delete data.

Query Management Facility (QMF) IBMs online relational database query and
reporting system that can access both IBMs DB2 and SQL/DS databases.

Glossary
Relational Database Management System (RDBMS) A database management system

in which complex data structures are represented as simple, two-dimensional
tables consisting of columns and rows.
redundancy The presence of more than one component to perform a required
function. The presence of more than one copy of a block of data to provide
fallback in case of data loss.
repeatable read A level of cursor isolation used by DB2, SQL/DS and TS/API
that ensures that a transaction acquires a read lock on data when it obtains
addressability to the data. The read lock is not relinquished when the
transaction relinquishes its addressability to the data, whether or not the
transaction has performed any updates or deletions. If the transaction performs
any updates or deletions, the read lock is automatically upgraded to a write
lock. Isolation level RR ensures that both read and write locks are not
relinquished until end-of-transaction time.
request A message sent from an application program to the Teradata RDBMS.
Also, an application programs petition for action from the Teradata RDBMS, and
a response from the Teradata RDBMS as a result of that petition.
Restructured Extended Exector (REXX) A scripting language for IBM VM and
MVS systems.
RXSQL IBMs SQL/DS Procedures Language Interface that allows REXX
programs to access SQL/DS relational database management systems.
SAS System A software system, developed and marketed by SAS Institute, Inc.,
that offers capabilities for data access, statistical analysis, project management,
financial analysis, graphical data analysis, and application development.
SQLCODE The field in the SQLCA that contains a return code following
completion of a database request.
SQLERRM The field in the SQLCA that contains error message text related to
the corresponding SQLCODE following completion of a database request.
Structured Query Language/Data System (SQL/DS) IBMs relational database
management system running under VM/CMS (and VSE).
SQL/DS Access Module A file within an SQL/DS database containing code for the
SQL commands embedded in the application program. When the application
program executes, the module is accessed. This file is created as a result of
submitting the application code to an SQL/DS preprocessor.
Static SQL SQL statements that are coded within an application program and
prepared via a precompiler before program execution. The precompiler
Glossary 4 Teradata Transparency Series/Application Programming Interface User Guide

Glossary
replaces the SQL statements with statements recognizable by the client

language compiler. For DB2, output from the precompiler includes source code
(which is submitted to the compiler) and a database request module (DBRM)
that is input to the bind process.
Teradata SQL The Teradata Structured Query Language (Teradata SQL) for use
with the Teradata RDBMS.
Teradata SQL Extensions SQL syntax capabilities that extend beyond normal
ANSI, DB2, and SQL/DS syntax and that are supported only on the Teradata
RDBMS.
Time Sharing Option (TSO) A multi-user monitor subsystem that runs under the
MVS operating system.
Transparency Series/Application Program Interface (TS/API) An application program
interface that allows you to access relational databases stored on the Teradata
RDBMS.
unit of work A sequence of operations within an application process which is

recoverable.
updatable cursors A cursor used to modify (update or delete) the current row in
a multi-row response.
Virtual Machine (VM ) One of the primary operating systems (or system control
programs) for medium and large IBM computers.

Teradata API

Uploaded by

Copyright:

Available Formats

Teradata API

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Teradata API

Uploaded by

Copyright:

Available Formats

Preface

Changes to this Book

July 2007 There are no documentation changes resulting from this

Teradata Transparency Series/Application Programming Interface User Guide i

Teradata Tools and Utilities Release Definition

Technical Information on the Web

http://www.info.teradata.com/ A direct link to Teradata Information Products

ii Teradata Transparency Series/Application Programming Interface User Guide

http://www.teradata.com/ The Teradata home page provides links to numerous

CICS Customer Information Control System

MVS Multiple Virtual Storage

RDBMS Relational Database Management System

REXX Restructured Extended Exector

SQL Structured Query Language

SQL/DS Structured Query Language/Data System

TOS Teradata Operating System

TS/API Transparency Series/Application Program Interface

VMS Virtual Memory System

Teradata Transparency Series/Application Programming Interface User Guide iii

iv Teradata Transparency Series/Application Programming Interface User Guide

Teradata Transparency Series/Application Programming Interface User Guide v

RXSQL ............................................................................................................................. 121

vi Teradata Transparency Series/Application Programming Interface User Guide

Teradata Transparency Series/Application Programming Interface User Guide vii

viii Teradata Transparency Series/Application Programming Interface User Guide

Teradata Transparency Series/Application Programming Interface User Guide ix

UPDATE ...................................................................................................................... 720

x Teradata Transparency Series/Application Programming Interface User Guide

Error Code Translations for DB2............................................................................... A2

Teradata Transparency Series/Application Programming Interface User Guide xi

SYSTEM .SYSCOLUMNS ..........................................................................................B48

xii Teradata Transparency Series/Application Programming Interface User Guide

Figure 1-1 Flow Control in CICS.............................................................................. 16

Teradata Transparency Series/Application Programming Interface User Guide xiii

xiv Teradata Transparency Series/Application Programming Interface User Guide

Table 1-1 SAS Issues............................................................................................... 117

Teradata Transparency Series/Application Programming Interface User Guide xv

Table B-18 How Columns in SYSIBM.SYSINDEXPART Are Supported..........B19

xvi Teradata Transparency Series/Application Programming Interface User Guide

Table B-60 How Columns in SYSTEM.SYSINDEXES Are Supported ..............B54

Teradata Transparency Series/Application Programming Interface User Guide xvii

xviii Teradata Transparency Series/Application Programming Interface User Guide

This chapter provides an overview of Transparency Series/Application

Teradata Transparency Series/Application Programming Interface User Guide 11

12 Teradata Transparency Series/Application Programming Interface User Guide

What TS/API Supports

Teradata Transparency Series/Application Programming Interface User Guide 13

14 Teradata Transparency Series/Application Programming Interface User Guide

How TS/API Functions

TS/API, DB2, and Application Tools (CICS)

Teradata Transparency Series/Application Programming Interface User Guide 15

The following diagram depicts the flow of control.

Figure 1-1 Flow Control in CICS

TS/API, DB2, and Application Tools (TSO)

16 Teradata Transparency Series/Application Programming Interface User Guide

The following diagram depicts the flow of control:

Figure 1-2 Flow Control in TSO

TS/API, SQL/DS, and Application Tools

Teradata Transparency Series/Application Programming Interface User Guide 17