Rules for Using WHENEVER - Teradata Preprocessor2
Teradata® Preprocessor2 for Embedded SQL Programmer Guide - 20.00
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
Lake
VMware
Product
Teradata Preprocessor2
Release Number
20.00
Published
October 2023
ft:locale
en-US
ft:lastEdition
2023-11-20
dita:mapPath
nyr1691484085721.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
xfi1470440464166
Product Category
Teradata Tools and Utilities
Introduction
Welcome to Teradata Tools and Utilities Teradata Preprocessor2 for Embedded SQL Programmer Guide
PP2 Processes
Using Static and Dynamic SQL
Prerequisites
Release Version Compatibility
Upgrading PP2 and CLIv2
Mainframe and Windows Platforms
UNIX/LINUX Platforms
Operating Systems and Host Programming Languages Compatibility
PP2 Input
PP2 Output
Contents of PP2 Output Listing
Release Number Information
OPTIONS, SOURCE, and XREF Sections
PRINT Option
Diagnostic Messages
Message Form
Client Language Source Code
Server Failure and Recovery
PP2 Behavior
Application Behavior
StatementInformation Parcel
Auto Generated Key Retrieval
Retrieving the Identity Column Value
Retrieving the Row
Inserting More than One Row
Using Structures and Arrays to Insert
Period Datatypes
PERIOD(DATE)
PERIOD(TIME)
PERIOD(TIME WITH TIMEZONE)
PERIOD(TIMESTAMP)
PERIOD(TIMESTAMP WITH TIMEZONE)
Period Datatype Examples
Query Banding
Query Band Syntax
Query Band Examples
LOB Support
Inline Method Restriction
Example C Program
User-Defined Types
Distinct UDTs
Structured UDTs
Creating a UDT
Importing and Exporting Values into a UDT Column
User-Defined Methods
Finding Information
Connecting to the Database and Invoking PP2
Overview
Connecting to the Database
PP2 Connection
Runtime Execution Connection
Explicit Connection
Extended Security with LOGMECH and LOGDATA
Implicit Connection
Invoking PP2
IBM Mainframe Environment: z/OS
Workstation-Attached Environments
PP2 Options
How to Read the Option Syntax
APOST|QUOTE
-a | -q
APOSTSQL|QUOTESQL
-as | -qs
CHARSET(charset)
-cs charset
Supported Character Sets
CURPREFIX(prefix)
-c prefix
DATABASE(dbname)
-db dbname
DATE(D|E|I|J|U)
-d D|E|I|J|U
DYNPREFIX(prefix)
-dp prefix
FLAG(I|W|E|S)
-f I|W|E|S
INPUT(filespec)
-i filespec
-ld logdata
LINECOUNT(integer)
-lc integer
-lm logmech
MARGINS(m,n[,c])
-m m,n[,c]
NULLSCAN|NONULLSCAN
-ns | -nns
OPTIONS|NOOPTIONS
-lo | -nlo
PRINT[(filespec)]|NOPRINT
-l [filespec] | -nl
PUNCH[(filespec)]|NOPUNCH
-o [filespec] | -no
SOURCE|NOSOURCE
-ls | -nls
SQLCHECK(FULL|NOSYNTAX)
-sc FULL|NOSYNTAX
SQLFLAGGER(NONE|ENTRY|INTERMEDIATE)
-sf (NONE|ENTRY|INTERMEDIATE)
TDPID(tdpid|networkgroupname)-t tdpid|network group name
TERM[(filespec)]|NOTERM
-e [filespec] | -ne
TRANSACT(ANSI|BTET|COMMIT|2PC)
-tr ANSI|BTET|COMMIT
USERID(userid[,password[,accountid]])-u userid[,password[,accountid]]
VERSION(COBOL|COBOLII|COBOL3)
-v MF1|MF2|LPI
XREF|NOXREF
-lx | -nlx
Writing Embedded SQL Applications in C
Overview
C Language Support
Program Status Information
Teradata Mode Communications Area
ANSI-Compatible Mode Communications Variables
BIGINT Support
C Coding Considerations
Embedded SQL Statement Format
Array Support
Comments
Continuation for SQL Statements
Dynamic SQL
Including Code
Margins
Sequence Numbers
SQL Strings
Statement Labels
String Delimiters
User-Defined Functions
Multi-Session Programming Considerations
Special Considerations
Host Variables
Using C Structures as Output Host Variables
Using C Structures as Input Host Variables
Host Variable Examples
Requirements for Host Variables
Host Variable Values
Server to Host Assignment
Host to Server Assignment
Host Variable Declaration
Indicator Variables
Rules for Indicator Variables
Indicator Variables and Input
Indicator Variables and Output
Indicator Variables and Structures
SQL Error Return—ANSI Mode
SQLCODE Variable
SQLSTATE Variable
Using PPRTEXT to Retrieve Return Codes
SQL Error Return—Teradata Mode
SQLCODE Field
Using PPRTEXT to Retrieve Return Codes
Exception Conditions: WHENEVER
Exception Conditions
WHENEVER Is Declarative
Rules for Using WHENEVER
Dynamic Statement Example
A Caution When Using Dynamic SQL
Include SQLDA Example
Multi-Session Programming
Example
Program Output
Performing a Stored Procedure
Stored Procedure
Example
Program Output
Period Datatype Example Using Descriptor
Using Stored Procedure Dynamic Result Sets
Using UPSERT in Embedded SQL
Example
Program Output
Writing Embedded SQL Applications in COBOL
Overview
COBOL Language Support
COBOL Application Requirements
Program Status Information
Teradata Mode Communications Area
ANSI-Compatible Mode Communications Variables
BIGINT Support
Large Decimal Support
COBOL Coding Considerations
Array Support
Embedded SQL Statement Format
Comments
Continuation for SQL Statements
Dynamic SQL
Including Code
Margins
Paragraph Names
Sequence Numbers
SQL Strings
String Delimiters
APOSTSQL Example
QUOTESQL Example
User-Defined Functions
Multi-Session Programming Considerations
Special Considerations
Host Variables
Using COBOL Structures as Output Host Variables
Using COBOL Structures as Input Host Variables
Host Variable Examples
Requirements for Host Variables
COBOL and Host Variables
SQL and Host Variables
PP2 Issues
Host Variable Values
Server to Host Assignment
Host to Server Assignment
Host Variable Declaration
Indicator Variables
Rules for Indicator Variables
Indicator Variables and Input
Indicator Variables and Output
Indicator Variables and Structures
SQL Error Return—ANSI Mode
SQLCODE Variable
SQLSTATE Variable
Using PPRTEXT to Retrieve Return Codes
SQL Error Return—Teradata Mode
SQLCODE Field
Using PPRTEXT to Retrieve Return Codes
Exception Conditions: WHENEVER
Exception Conditions
WHENEVER Is Declarative
Rules for Using WHENEVER
Dynamic Statement Example
Period Datatype Examples
Example: Retrieve a PERIOD(TIME) Datatype
Example: Retrieve Period Datatypes in Binary
Writing Embedded SQL Applications in PL/I
Overview
PL/I Language Support
PL/I Application Requirements
Program Status Information
Teradata Mode Communications Area
ANSI-Compatible Mode Communications Variables
BIGINT Support
Large Decimal Support
PL/I Coding Considerations
Array Support
Embedded SQL Statement Format
Comments
Continuation for SQL Statements
Dynamic SQL
Including Code
Macro Processor
Margins
Sequence Numbers
SQL Strings
Statement Labels
String Delimiters
Multi-Session Programming Considerations
Special Considerations
Host Variables
Using PL/I Structures as Output Host Variables
Using PL/I Structures as Input Host Variables
Host Variable Examples
Requirements for PL/I Host Variables
Server to Host Assignment
Conversion Rules
Byte String Assignment
Character String Assignment
String Truncation
Numeric Value Assignment
Date Assignment
Host to Server Assignment
Graphic Literals for Multibyte Characters
Host Variable Declaration
Indicator Variables
Rules for Indicator Variables
Indicator Variables and Input
Indicator Variables and Output
Indicator Variables and Structures
SQL Error Return—ANSI Mode
SQLCODE Variable
SQLSTATE Variable
Using PPRTEXT to Retrieve Return Codes
SQL Error Return—Teradata Mode
SQLCODE Field
Using PPRTEXT to Retrieve Return Codes
Exception Conditions: WHENEVER
Exception Conditions
WHENEVER Is Declarative
Rules for Using WHENEVER
Dynamic Statement Example
SQL Data Type Codes
Overview
Data Type Codes
Unused/Internal Data Type Codes
Sample Application Linkage Procedure
Overview
64-Bit Platform Linking Concerns
Application Linking for z/OS
Linkage Job Control Language
JCL Notes
Usage Notes
Application Linking for CICS
Linkage Job Control Language
JCL Notes
Usage Notes
Application Linking for IMS
Linkage Job Control Language
JCL Notes
Usage Notes
Application Linking for C on a UNIX System
Linkage Script for a 32-Bit Application on AIX
Linkage Script for a 64-Bit Application on AIX
Linkage Script for a 64-Bit Application on RedHat Linux, CentOS, Oracle Linux, SUSE
Linkage Script for a 32-Bit Application on Red Hat Linux, CentOS, Oracle Linux, SUSE
Script Notes
Application Linking for Micro Focus COBOL on a UNIX System
Linkage Script for a 32-Bit Application on AIX
Linkage Script for a 64-Bit Application on AIX
Script Notes
Application Linking for Visual Studio 2019 on Windows
Linkage Script for a Visual Studio 2019 32-Bit Application on Windows
Linkage Script for a Visual Studio 2019 64-Bit Application on Windows
Script Notes
Embedded SQL Examples
Overview
About the Examples
LAB Files
Setting Up the Examples
Procedure Overview
z/OS Operating Systems
Location of Source Code and LAB Files
Preferred Setup Files
z/OS JCL
Workstation-Attached Operating Systems
Location of Source Code and LAB Files
Preferred Setup Files
Operator Messages
EMPLOYEE Table Source Code
LAB6 and LAB7 Macros Source Code
EMPLOYEE Table
Additional Information
Audience
Changes and Additions
Related Documentation
Teradata Links
The WHENEVER statement must precede the SQL statement or statements for which the condition is to apply.
When it is used, the host label object must be a section name or an unqualified paragraph name.
When a PERFORM or CALL action is used, the PERFORM code object and the CALL function call must be such that PERFORM code or CALL function call is valid at every SQL statement to which that exception declaration applies.