Teradata Preprocessor2 for Embedded SQL. Programmer Guide

(1)

Embedded SQL

Programmer Guide

Release 12.00.00 B035-2446-067A February 2009

(2)

This Before are trademarks or registered trademarks of Teradata Corporation or its affiliates. Adaptec and SCSISelect are trademarks or registered trademarks of Adaptec, Inc.

AMD Opteron and Opteron are trademarks of Advanced Micro Devices, Inc.

BakBone and NetVault are trademarks or registered trademarks of BakBone Software, Inc. EMC, PowerPath, SRDF, and Symmetrix are registered trademarks of EMC Corporation. GoldenGate is a trademark of GoldenGate Software, Inc.

Hewlett-Packard and HP are registered trademarks of Hewlett-Packard Company. Intel, Pentium, and XEON are registered trademarks of Intel Corporation.

IBM, CICS, DB2, MVS, RACF, Tivoli, and VM are registered trademarks of International Business Machines Corporation. Linux is a registered trademark of Linus Torvalds.

LSI and Engenio are registered trademarks of LSI Corporation.

Microsoft, Active Directory, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and other countries.

Novell and SUSE are registered trademarks of Novell, Inc., in the United States and other countries. QLogic and SANbox trademarks or registered trademarks of QLogic Corporation.

SAS and SAS/C are trademarks or registered trademarks of SAS Institute Inc. SPARC is a registered trademarks of SPARC International, Inc.

Sun Microsystems, Solaris, Sun, and Sun Java are trademarks or registered trademarks of Sun Microsystems, Inc., in the United States and other countries.

Symantec, NetBackup, and VERITAS are trademarks or registered trademarks of Symantec Corporation or its affiliates in the United States and other countries.

Unicode is a collective membership mark and a service mark of Unicode, Inc.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Other product and company names mentioned herein may be the trademarks of their respective owners.

THEINFORMATIONCONTAINEDINTHISDOCUMENTISPROVIDEDONAN “AS-IS” BASIS, WITHOUTWARRANTYOFANYKIND, EITHER EXPRESSORIMPLIED, INCLUDINGTHEIMPLIEDWARRANTIESOFMERCHANTABILITY, FITNESSFORAPARTICULARPURPOSE, OR NON-INFRINGEMENT. SOMEJURISDICTIONSDONOTALLOWTHEEXCLUSIONOFIMPLIEDWARRANTIES, SOTHEABOVEEXCLUSION MAYNOTAPPLYTOYOU. INNOEVENTWILL TERADATA CORPORATIONBELIABLEFORANYINDIRECT, DIRECT, SPECIAL, INCIDENTAL, ORCONSEQUENTIALDAMAGES, INCLUDINGLOSTPROFITSORLOSTSAVINGS, EVENIFEXPRESSLYADVISEDOFTHEPOSSIBILITYOF SUCHDAMAGES.

The information contained in this document may contain references or cross-references to features, functions, products, or services that are not announced or available in your country. Such references do not imply that Teradata Corporation intends to announce such features, functions, products, or services in your country. Please consult your local Teradata Corporation representative for those features, functions, products, or services available in your country.

Information contained in this document may contain technical inaccuracies or typographical errors. Information may be changed or updated without notice. Teradata Corporation may also make improvements or changes in the products or services described in this information at any time without notice.

To maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document. Please e-mail: [email protected]

Any comments or materials (collectively referred to as “Feedback”) sent to Teradata Corporation will be deemed non-confidential. Teradata Corporation will have no obligation of any kind with respect to Feedback and will be free to use, reproduce, disclose, exhibit, display, transform, create derivative works of, and distribute the Feedback and derivative works thereof without limitation on a royalty-free basis. Further, Teradata Corporation will be free to use any ideas, concepts, know-how, or techniques contained in such Feedback for any purpose whatsoever, including developing, manufacturing, or marketing products or services incorporating Feedback.

(3)

Purpose

This book provides information about Teradata Preprocessor2 for Embedded SQL, which is a Teradata® Tools and Utilities product. Teradata Tools and Utilities is a group of products designed to work with Teradata Database.

PP2 is used to incorporate Structured Query Language (SQL) statements into application programs that access data in a Teradata Database.

Audience

This book is intended for use by application programmers who must incorporate Teradata SQL statements into COBOL, C, or PL/I application programs to access data stored in a Teradata Database.

Supported Releases

This book supports the following releases: • Teradata Database 12.0

• Teradata Tools and Utilities 12.00.00

• Teradata Preprocessor2 for Embedded SQL 12.00.00

Note: See “Release Number Information” on page 26 to verify the Teradata Preprocessor2 for Embedded SQL version number.

To locate detailed supported-release information:

1 Go to www.info.teradata.com.

2 Navigate to General Search>Publication Product ID.

3 Enter 3119.

4 Open the version of the Teradata Tools and Utilities ##.##.## Supported Versions spreadsheet associated with this release.

The spreadsheet includes supported Teradata Database versions, platforms, and product release numbers.

(4)

Prerequisites

The following prerequisite knowledge is required for this product: • Basic computer technology

• Developing application programs in PL/I, C, or COBOL

• SQL

• Teradata Database

Changes to This Book

The following changes were made to this book in support of the current release. Changes are marked with change bars. For a complete list of changes to the product, see the Release

Definition associated with this release.

Additional Information

Additional information that supports this product and Teradata Tools and Utilities is available at the web sites listed in the table that follows. In the table, mmyx represents the publication date of a manual, where mm is the month, y is the last digit of the year, and x is an internal

Date and Release Description July 2007

12.00.00

• Added DML array support for the following platforms: • Network Cobol

• Mainframe C • Mainframe Cobol • Mainframe PL/I

• Support for online archive logging was added. • Changed application linking instructions for:

• C on UNIX

• Micro Focus COBOL on UNIX • LPI COBOL on MP-RAS

• Added support for extended ANSI-compliant Unicode delimited identifiers and character string literals.

• Added support for query banding.

• Removed limitation regarding compiling a 32-bit application on a 64-bit Windows EM64T machine.

• Added Microsoft Windows Vista support.

• Clarified that, in a mainframe environment, a TDP module must be started prior to precompiling.

(5)

publication code. Match the mmy of a related publication to the date on the cover of this book. This ensures that the publication selected supports the same release.

Type of Information Description Access to Information Release overview

Late information

Use the Release Definition for the following information:

• Overview of all of the products in the release

• Information received too late to be included in the manuals

• Operating systems and Teradata Database versions that are certified to work with each product

• Version numbers of each product and the documentation for each product • Information about available training

and the support center

1 Go to http://www.info.teradata.com/.

2 Select the General Search check box.

3 In the Publication Product ID box, type 2029.

4 Click Search.

5 Select the appropriate Release Definition from the search results.

Additional product information

Use the Teradata Information Products Publishing Library site to view or download specific manuals that supply related or additional information to this manual.

2 Select the Teradata Data Warehousing check box.

3 Do one of the following:

• For a list of Teradata Tools and Utilities documents, click Teradata Tools and Utilities and then select a release or a specific title. • Select a link to any of the data warehousing

publications categories listed.

Specific books related to Teradata Preprocessor2 for Embedded SQL are as follows:

• Teradata Call-Level Interface Version 2 Reference for Channel-Attached Systems

B035-2417-mmyx

• SQL Reference: Data Definition Statements

B035-1144-mmyx

• SQL Reference: Fundamentals

B035-1141-mmyx

• SQL Reference: Stored Procedures and Embedded SQL

B035-1148-mmyx

• Teradata Director Program Reference

B035-2416-mmyx CD-ROM images Access a link to a downloadable CD-ROM

image of all customer documentation for this release. Customers are authorized to create CD-ROMs for their use from this image.

2 Select the General Search check box.

3 In the Title or Keyword box, type CD-ROM.

(6)

Ordering information for manuals

Use the Teradata Information Products Publishing Library site to order printed versions of manuals.

2 Select the How to Order check box under Print & CD Publications.

3 Follow the ordering instructions. General information

about Teradata

The Teradata home page provides links to numerous sources of information about Teradata. Links include:

• Executive reports, case studies of customer experiences with Teradata, and thought leadership

• Technical information, solutions, and expert advice

• Press releases, mentions, and media resources

1 Go to Teradata.com.

2 Select a link. Type of Information Description Access to Information

(7)

Preface

. . . .3

Purpose . . . .3

Audience . . . .3

Supported Releases . . . .3

Prerequisites . . . .4

Changes to This Book . . . .4

Additional Information . . . .4

Chapter 1:

Introduction

. . . 21

PP2 Processes. . . 21

Using Static and Dynamic SQL . . . 23

Upgrading PP2 and CLIv2 . . . 23

Mainframe and Windows Platforms . . . 23

UNIX/LINUX Platforms . . . 23

Supported Operating Systems and Host Programming Languages . . . 24

PP2 Input . . . 26

PP2 Output . . . 26

Contents of PP2 Output Listing . . . 26

Release Number Information . . . 26

OPTIONS, SOURCE, and XREF Sections . . . 27

PRINT Option . . . 28

Diagnostic Messages . . . 28

Client Language Source Code . . . 28

Server Failure and Recovery. . . 28

PP2 Behavior . . . 29

Application Behavior . . . 29

Query Banding . . . 31

(8)

Chapter 2:

Connecting to the Database and Invoking PP2

. . . .35

Connecting to the Teradata Database . . . .35

PP2 Connection . . . .36

Runtime Execution Connection . . . .36

Explicit Connection . . . .36

Implicit Connection. . . .39

Invoking PP2 . . . .39

IBM Mainframe Environment: z/OS and z/VM CMS . . . .39

Network-Attached Environments . . . .44

PP2 Options. . . .45

How to Read the Option Syntax. . . .45

APOST|QUOTE -a | -q . . . .48 APOSTSQL|QUOTESQL -as | -qs . . . .49 CHARSET(charset) -cs charset . . . .50 CURPREFIX(prefix) -c prefix . . . .56 DATABASE(dbname) -db dbname . . . .57 DATE(D|E|I|J|U) -d D|E|I|J|U . . . .58 DYNPREFIX (prefix) -dp prefix . . . .59 FLAG(I|W|E|S) -f I|W|E|S . . . .60 INPUT(file spec) -i filespec . . . .61 -ld logdata . . . .62 LINECOUNT(integer) -lc integer . . . .63 -lm logmech . . . .64 MARGINS(m,n[,c]) -m m,n[,c] . . . .65 NULLSCAN|NONULLSCAN -ns | -nns . . . .66 OPTIONS|NOOPTIONS -lo | -nlo . . . .67 PRINT[(file spec)]|NOPRINT -l [filespec] | -nl . . . .68

(9)

TDPID(tdpid|network group name) -t tdpid|network group name . . . 73

Chapter 3:

Writing Embedded SQL Applications in C

. . . 81

C Language Support . . . 81

Program Status Information . . . 81

Teradata Mode Communications Area . . . 82

ANSI-Compatible Mode Communications Variables . . . 83

BIGINT Support . . . 84

C Coding Considerations . . . 84

Embedded SQL Statement Format. . . 84

Array Support . . . 84

Comments . . . 85

Continuation for SQL Statements . . . 85

Dynamic SQL . . . 86 Including Code. . . 86 Margins . . . 87 Sequence Numbers . . . 87 SQL Strings . . . 87 Statement Labels. . . 87 String Delimiters . . . 87

User Defined Functions. . . 88

(10)

Special Considerations. . . .89

Host Variables . . . .89

Using C Structures as Output Host Variables . . . .89

Using C Structures as Input Host Variables . . . .89

Example Overview . . . .90

Requirements for Host Variables . . . .92

C Language and Host Variables . . . .92

SQL and Host Variables . . . .92

Indicator Variables. . . .93

PP2 Issues . . . .93

Host Variable Declaration. . . .93

Host Variable Values . . . .94

Server to Host Assignment. . . .94

Conversion Rules . . . .94

Byte String Assignment . . . .95

Character Assignment . . . .96

Single Character Data . . . .96

Varying Character Data. . . .96

Character String Data . . . .97

Character String Padding . . . .98

Character Truncation . . . .99

Numeric Value Assignment . . . .99

Date Assignment . . . .99

Host to Server Assignment. . . .100

Assignment Rules . . . .100

Host Variable Declaration . . . .101

Indicator Variables . . . .103

Rules for Indicator Variables . . . .103

Indicator Variables and Input. . . .104

Indicator Variables and Output . . . .104

Indicator Variables and Structures. . . .105

SQL Error Return—ANSI Mode . . . .105

SQLCODE Variable . . . .105

SQLSTATE Variable . . . .106

Using PPRTEXT to Retrieve Return Codes. . . .106

SQL Error Return—Teradata Mode . . . .106

SQLCODE Field . . . .107

Exception Conditions: WHENEVER . . . .108

Exception Conditions . . . .109

(11)

Dynamic Statement Example. . . 110

A Caution When Using Dynamic SQL . . . 110

Include SQLDA Example . . . 116

Multi-Session Programming . . . 118

Online Archive . . . 122

Performing a Stored Procedure . . . 123

Using Stored Procedure Dynamic Result Sets . . . 126

Using UPSERT in Embedded SQL . . . 127

Chapter 4:

Writing Embedded SQL Applications in COBOL

. . . 133

COBOL Language Support . . . 133

COBOL Application Requirements . . . 134

Program Status Information . . . 134

Teradata Mode Communications Area . . . 134

ANSI-Compatible Mode Communications Variables . . . 135

BIGINT Support . . . 135

Large Decimal Support. . . 135

COBOL Coding Considerations . . . 136

Array Support . . . 136

Embedded SQL Statement Format. . . 137

Comments . . . 137

Dynamic SQL . . . 138 Including Code. . . 139 Margins . . . 139 Paragraph Names . . . 139 Sequence Numbers . . . 139 SQL Strings . . . 139 String Delimiters . . . 140 APOSTSQL Example . . . 141 QUOTESQL Example . . . 141

User Defined Functions. . . 141

Multisession Programming Considerations . . . 142

Special Considerations. . . 142

Host Variables . . . 142

Using COBOL Structures as Output Host Variables . . . 142

Using COBOL Structures as Input Host Variables . . . 142

(12)

Requirements for Host Variables . . . .145

COBOL and Host Variables . . . .145

SQL and Host Variables . . . .146

PP2 Issues . . . .147

Host Variable Values . . . .147

Server to Host Assignment . . . .148

Host to Server Assignment . . . .150

Host Variable Declaration . . . .151

Indicator Variables . . . .155

Rules for Indicator Variables . . . .155

Indicator Variables and Input. . . .156

Indicator Variables and Output . . . .156

Indicator Variables and Structures. . . .157

SQL Error Return—ANSI Mode . . . .158

SQLCODE Variable . . . .158

SQLSTATE Variable . . . .159

SQL Error Return—Teradata Mode . . . .159

SQLCODE Field . . . .160

Exception Conditions: WHENEVER . . . .161

Exception Conditions . . . .162

WHENEVER Is Declarative . . . .162

Rules for Using WHENEVER . . . .162

Dynamic Statement Example. . . .163

Online Archive . . . .168

Chapter 5:

Writing Embedded SQL Applications in PL/I

. . . .169

PL/I Language Support. . . .169

PL/I Application Requirements . . . .169

Program Status Information . . . .169

Teradata Mode Communications Area . . . .170

ANSI-Compatible Mode Communications Variables . . . .170

BIGINT Support . . . .171

Large Decimal Support. . . .171

PL/I Coding Considerations . . . .171

Array Support. . . .172

(13)

Comments . . . 173

Dynamic SQL . . . 173 Including Code. . . 173 Macro Processor. . . 174 Margins . . . 174 Sequence Numbers . . . 174 SQL Strings . . . 174 Statement Labels. . . 174 String Delimiters . . . 174

Multi-Session Programming Considerations . . . 175

Special Considerations. . . 175

Host Variables . . . 176

Using PL/I Structures as Output Host Variables. . . 176

Using PL/I Structures as Input Host Variables . . . 176

Example Overview . . . 177

Requirements for PL/I Host Variables . . . 178

Server to Host Assignment. . . 180

Conversion Rules . . . 180

Byte String Assignment . . . 181

Character String Assignment . . . 181

String Truncation. . . 181

Numeric Value Assignment . . . 181

Date Assignment . . . 182

Host to Server Assignment. . . 182

Graphic Literals for Multibyte Characters . . . 183

Host Variable Declaration . . . 183

Indicator Variables . . . 185

Rules for Indicator Variables . . . 186

Indicator Variables and Input. . . 186

Indicator Variables and Output . . . 187

Indicator Variables and Structures . . . 187

SQL Error Return—ANSI Mode . . . 188

SQLCODE Variable . . . 188

SQLSTATE Variable . . . 188

Using PPRTEXT to Retrieve Return Codes. . . 188

SQL Error Return—Teradata Mode . . . 189

SQLCODE Field . . . 189

Using PPRTEXT to Retrieve Return Codes. . . 189

Exception Conditions: WHENEVER . . . 190

Exception Conditions . . . 191

(14)

Rules for Using WHENEVER . . . .191

Dynamic Statement Example. . . .191

Online Archive . . . .196

Appendix A:

SQL Data Type Codes

. . . .199

Data Type Codes . . . .199

Unused/Internal Data Type Codes . . . .201

Appendix B:

Sample Application Linkage Procedure

. . . .203

Overview . . . .203

64-Bit Platform Linking Concerns . . . .203

Application Linking for z/OS. . . .204

Application Linking for CICS . . . .205

Application Linking for IMS . . . .206

Application Linking for z/VM CMS . . . .207

Application Linking for C on UNIX . . . .208

Linkage Script for a 64-Bit Application on AIX . . . .208

Linkage Script for a 64- Bit Application on Itanium HP-UX . . . .208

Linkage Script for a 64-Bit Application on 64-Bit Itanium RH. . . .209

Linkage Script for a 32-Bit Application on Opteron Solaris . . . .209

Linkage Script for a 64-Bit Application on Opteron Solaris . . . .209

Application Linking for Micro Focus COBOL on UNIX . . . .210

Linkage Script for a 64-Bit Application on AIX . . . .210

Script Notes . . . .210

Application Linking for LPI COBOL on MP-RAS. . . .211

Application Linking for Visual C++ on Windows. . . .211

Appendix C:

Embedded SQL Examples

. . . .213

About the Examples . . . .213

LAB Files . . . .214

(15)

Procedure Overview. . . 215

z/OS Operating Systems . . . 215

Where to Find Source Code and LAB Files . . . 215

Which Setup Files to Use. . . 215

z/OS JCL . . . 216

Network-Attached Operating Systems . . . 216

z/VM Operating Systems . . . 217

z/VM EXEC . . . 217

Operator Messages . . . 217

EMPLOYEE Table Source Code . . . 218

LAB6 and LAB7 Macros Source Code. . . 219

EMPLOYEE Table. . . 220

Glossary

. . . 223

(16)

(17)

(18)

(19)

Table 1: Upgrading PP2 and CLIv2 on UNIX and LINUX. . . 24

Table 2: Supported Host Programming Languages Listed by Operating System . . . 24

Table 3: OPTIONS, SOURCE, and XREF Section Information. . . 27

Table 4: Teradata-Defined Character Sets for Mainframe Environments . . . 52

Table 5: Site-Defined Character Sets for Mainframe Environments . . . 53

Table 6: Teradata-Defined Character Sets for Network-Attached Environments . . . 54

Table 7: Site-Defined Character Sets for Network-Attached Environments . . . 55

Table 8: C Definitions for Teradata Database Data Types . . . 101

Table 9: COBOL Definitions for Teradata Database Data Types . . . 151

Table 10: PL/I Definitions for Teradata Database Data Types . . . 184

(20)

(21)

Teradata Preprocessor2 for Embedded SQL, PP2, provides an easy-to-use method of accessing data stored in the Teradata Database. PP2 does this by interpreting and expanding Teradata SQL statements that have been embedded in an application program. Interpretation of the statements allows the program to perform tasks supported by Teradata SQL, such as data retrieval. The application program is called an embedded SQL host program. The language in which the host program is written is called the host programming language. Therefore, the host program consists of Teradata SQL code that provides the database interface, plus the host programming language, which provides remaining instructions for application execution. Review the next sections in this chapter for information on:

• PP2 Processes

• Using Static and Dynamic SQL • Upgrading PP2 and CLIv2

• Supported Operating Systems and Host Programming Languages • PP2 Input

• PP2 Output

• Contents of PP2 Output Listing • Diagnostic Messages

• Client Language Source Code • Server Failure and Recovery • Finding Information

PP2 Processes

PP2 includes a precompiler as well as the services that execute, or provide runtime support for a compiled application. A precompiler is necessary to interpret the embedded SQL statements in the host program. Regardless of the host language, PP2 operation consists of two stages:

1 The precompile phase, which precedes application compilation and linking.

2 Execution, or runtime support, of the application.

During precompilation, PP2 reads and replaces all the SQL statements embedded in the host program with CLIv2 calls that are acceptable to the native compiler for the host language. The host programming language syntax remains unchanged.

(22)

At runtime, the inserted syntax runs in conjunction with Call-Level Interface version 2 (CLIv2) and Teradata Director Program (TDP) modules to provide an efficient, convenient interface between the application program and the Teradata Database.

Note: In a mainframe environment, a TDP module must be started prior to PP2 performing the precompile step, even if no data access is required (that is, using NOSYNTAX).

The following example uses a COBOL program to show the operations PP2 performs, from logging on to the Teradata Database through precompilation. The process is similar for other languages:

1 Logs a session onto the Teradata Database and determines whether it is to be a Teradata mode or ANSI-compatible mode session.

2 Checks the syntax of SQL statements in the host language source program, validating database objects against the entries in the Data Dictionary.

3 Builds code in the DATA division for the data elements.

4 Builds code in the PROCEDURE division to handle SQL statement passing to the Teradata Database.

5 Comments out the SQL source code.

6 Produces COBOL source for input to the COBOL compiler.

Figure 1 gives a high-level view of starting with a PL/I embedded SQL program as the source input file, then precompiling, compiling, linking, and finally, running the PL/I program.

Figure 1: PP2 Operation 2446B051 PL/I PP2 precompiler COMPILE LOAD/LINK EXECUTION CICS precompiler In CICS environment PL/I embedded SQL programs Translates embedded SQL into appropriate host language

(For CICS, use Teradata CICS libraries)

Execute program with communication to Teradata RDBMS

(23)

Using Static and Dynamic SQL

Both static and dynamic SQL statements are allowed in the embedded SQL host program. Static SQL statements, as the name implies, remain static each time the program is run. Dynamic SQL statements are built at runtime.

To use static SQL statements in a host program, be sure to know the SQL statement type, plus database table and column names when the program is written. Only the specific data values the statement is looking for are unknown. Use host language variables to represent those unknown values. For example, use a static SQL statement to enter an order, and a host language variable to represent the quantity of an item in the order.

Because static SQL statements are hard-coded into the program, the statements require parsing, and so forth, only once. Therefore, using static SQL tends to result in faster processing.

Dynamic SQL is useful for programs where the content of a SQL statement is un-known at the time the program is written. For example, an interactive application that prompts the user for a table name, would be a good situation in which to use dynamic SQL.

Each host programming language handles dynamic SQL statements differently. Refer to Writing Embedded SQL Applications in C, Writing Embedded SQL Applications in COBOL, Writing Embedded SQL Applications in PL/I, and Embedded SQL Examples for more information.

Upgrading PP2 and CLIv2

Mainframe and Windows Platforms

When upgrading to a newer version of the Teradata Database, install the latest versions of Teradata Preprocessor2 for Embedded SQL and CLIv2.When you upgrade to a newer version of Teradata Warehouse, install the latest versions of PP2 and CLIv2.

If you have not made changes to the source code in your existing application programs, execute your programs using the latest versions of PP2 and CLIv2. It is not necessary to precompile, compile, and link the existing applications again. If you have made changes to the source code in the existing application programs, run the PP2 precompiler, compile, then link the applications.

UNIX/LINUX Platforms

When you upgrade to a newer version of Teradata Warehouse, install the latest versions of PP2 and CLIv2. Refer to Table 1 for instructions on whether or not it is necessary to precompile, compile, and link existing applications before executing them with the new PP2 and CLIv2 versions.

(24)

Supported Operating Systems and Host

Programming Languages

PP2 operates with host programming languages in several environments: Table 1: Upgrading PP2 and CLIv2 on UNIX and LINUX

Have you made changes to the source code in your existing

application program? 32-Bit Platforms 64-Bit Platforms no Relink the application with the latest PP2

runtime library.

• If the application was last precompiled with TTU 8.2 PP2, relink the

application with the latest PP2 runtime library.

• If the application was last precompiled with a version of PP2 earlier than TTU 8.2 PP2: precompile, compile, then link the application again.

yes Precompile, compile, then link the application again.

Table 2: Supported Host Programming Languages Listed by Operating System

Operating System Host Programming Language HP-UX 11.0, 11.11, and 11i (PA-RISC 2)

(32-bit)

C and Micro Focus COBOL (32-bit)

HP-UX 11.0, 11.11, and 11i (PA-RISC 2) (64-bit)

C (32-bit or 64-bit)

HP-UX 11.23 and 11.31 (Itanium) (64-bit) C (64-bit)

IBM AIX 5.1 (32-bit) C and Micro Focus COBOL (32-bit) IBM AIX 5.2 (64-bit) C (32-bit and 64-bit)

IBM AIX 5.1 (64-bit) C (32-bit and 64-bit)

IBM AIX 5.2 (32-bit) C and Micro Focus COBOL (32-bit) IBM AIX 5.3 (32-bit) C and Micro Focus COBOL (32-bit) IBM AIX 5.3 (64-bit) C and Micro Focus COBOL (64-bit) IBM z/OS 1.3–1.5 (MVS)

Note: USS is not supported.

SAS/C, IBM COBOL, IBM PL/I (32-bit)

IBM z/VM 4.3–4.4 SAS/C, IBM COBOL, IBM PL/I (32-bit) Microsoft Windows 2000 32-bit SP4 C (32-bit)

(25)

Note: The IBM mainframes are channel-attached to the NCR hardware platform running the Teradata Database. Other environments are referred to as network-attached systems.

Microsoft Windows .NET Server 2003 (32-bit or 64-bit)

C (32-bit and 64-bit)

Microsoft Windows Server 2003, Standard Edition/Enterprise Edition on Intel EM64T

Microsoft Windows Vista C (32-bit and 64-bit) Microsoft Windows XP Professional (32-bit or

64-bit)

Microsoft Windows XP Professional on Intel EM64T

Novell SUSE Linux Enterprise 9 on 32-bit Intel x86

C (32-bit)

Novell SUSE Linux Enterprise 9 on Intel EM64T

Novell SUSE Linux Enterprise 9 and 10 on 64-bit AMD Opteron

RedHat Enterprise Linux Advanced Server 4.0 on AMD Opteron

Red Hat Linux Advanced Server 2.1 and 3.0 (32-bit)

C (32-bit)

RedHat Linux Advanced Server 4.0 on 32-bit Intel x86

C (32-bit)

RedHat Linux Advanced Server 4.0 on Intel EM64T

Red Hat Linux Advanced Server 4.0 and above on 64-bit AMD Opteron

Red Hat Linux Itanium 64-bit Advanced Server 3.0

C (64-bit)

SUN Solaris 8 and 9 SPARC (32-bit) C (32-bit)

SUN Solaris 8 and 9 SPARC (64-bit) C (32-bit and 64-bit) SUN Solaris 10 on 64-bit AMD Opteron C (32-bit and 64-bit)

UNIX MP-RAS 3.2–3.3 C, Micro Focus COBOL and LPI COBOL (32-bit)

Table 2: Supported Host Programming Languages Listed by Operating System (continued)

(26)

PP2 Input

The PP2 input file is the embedded SQL host program (also called the source input file). Depending on the program environment, one or more additional files are required in addition to the PP2 input file if when using an SQL INCLUDE statement.The PP2 input file is the embedded SQL host program (also called the source input file).

Depending on the program environment, one or more additional files are required in addition to the PP2 input file if you use a SQL INCLUDE statement.

PP2 Output

The source output program is the source input program as modified by PP2. Modifications to the program include:

• Inclusion of host language comments that identify the program as output from PP2 and that provide the date and time of precompilation. These comments are inserted near the beginning.

• Conversion of all embedded Teradata SQL statements to host language comments. • Insertion of host data variable declarations and procedural statements to implement the

embedded Teradata SQL statements.

Contents of PP2 Output Listing

The output listing is headed by release number and date and time information (see “Release Number Information” on page 26). The next three sections of the listing are based on the OPTIONS, SOURCE, and XREF PP2 options (see “OPTIONS, SOURCE, and XREF Sections” on page 27).

The line-by-line listing of the input program is augmented by: • An analysis of the PP2 options specified and defaulted • Input line numbers assigned by PP2

• PP2 diagnostics

• Cross-references of host variables, cursor, and dynamic statement names • A summary of diagnostics

• A summary of input and output file record counts

Release Number Information

The first block of the PP2 output listing indicates the date and time of compilation, the PP2 release number, and, if known, the Teradata Database release number. When precompiling

(27)

Teradata Database release number does not display. Here is an example where the Teradata Database release number is not known:

/*

THIS PROGRAM WAS PREPROCESSED ON JAN 30, 2006 AT 12:08:48 BY THE C PREPROCESSOR2

PREPROCESSOR SOFTWARE RELEASE: PP2.09.02.00 DBS N/A */

OPTIONS, SOURCE, and XREF Sections

Table 3: OPTIONS, SOURCE, and XREF Section Information

Section The section contains:

Output/suppression of this section is controlled by this

option: Other information OPTIONS • PP2 options that have been specified

at invocation

• options used by PP2

• option errors detected by PP2

OPTIONS/NOOPTIONS This section does not contain specified userid.

SOURCE • the same source that is input to PP2. The section format depends on the host language and includes line numbers known to PP2 and any included lines brought in via EXEC SQL INCLUDE statements.

• source errors detected by PP2

SOURCE/NOSOURCE

-XREF • host variable, cursor, and dynamic statement declarations and usages

• summary of input/output records • summary of the warnings and errors

detected by PP2

XREF/NOXREF Asterisks in the variable declaration type field indicate that the size of the field exceeds the capability of the cross reference. However, this variable remains valid for use in SQL requests.

(28)

PRINT Option

The PRINT option must be in effect for the output listing to be produced. The listing is placed in the file specified or defaulted for the PRINT option.

No listing is produced if the NOPRINT PP2 option is specified.

Diagnostic Messages

Message Form

Diagnostic messages returned by PP2 occupy two lines and take the following form:

SPPnnnn <severity>, at line nnn, column nn: <diagnostic message text>

where <severity> is one of the following: • Warning

• SQL Flagger Warning • Error

• Severe Error • Fatal Error

SPPnnnn is an identifying value unique to PP2.

The qualifying phrases at line nnn and column nn vary to suit conditions.

If the diagnostic originates with the Teradata Database, the message begins with a four digit Teradata error code. See Messages.

Client Language Source Code

PP2 produces client language source code as a result of the precompilation.

Code generation is enabled or suppressed via the PUNCH/NOPUNCH option. The code is placed in the file specified or is defaulted for the PUNCH option. No code is produced if the NOPUNCH PP2 option is specified.

C and COBOL code generation is also suppressed if an error is encountered during the precompilation phase.

Server Failure and Recovery

This section describes the behavior of PP2 and Teradata Database-generated applications when they encounter a node reset.

(29)

PP2 Behavior

Application Behavior

The behavior of Teradata Database-generated applications in the event of a node resetting depends on the:

• Crash notification setting

• Type of node on which PP2 is running

Application Behavior on a Resetting Node

Setting Crash Notification

Specify crash notification behavior with the SET CRASH statement, which sets the following wait_across_crash (WAC) and tell_about _crash (TAC) options:

The default settings for these options are: • wait_across_crash (WAC) = ‘Y’ • tell_about_crash (TAC) = ‘N’

If Teradata Preprocessor2 for Embedded SQL is running on

a… Then it…

Resetting node aborts.

Preprocessing must be restarted after the node resets. This is equivalent to the situation where a utility or application is initiated on an external client that fails.

• Non-Resetting node • LAN-attached client • Channel-attached client

reconnects its session (if connected to a server) and the current SQL statement undergoing a syntax check returns an error. Restart preprocessing to ensure complete syntax checking.

IF a PP2-generated application is running on

a… THEN it…

Resetting node aborts. Restart the application after the node resets.

This is equivalent to the situation where a utility or application is initiated on an external client that fails.

IF specified... THEN WAC is set to... AND TAC is set to...

WAIT_NOTELL ‘Y’ ‘N’

(30)

For information on GET CRASH and SET CRASH, see SQL Reference: Stored Procedures and

Embedded SQL.

Application Response When WAC is Set to Y and TAC is Set to N The following table describes the behavior of PP2-generated applications when a node resets and WAC = ‘Y’ and TAC = ‘N’.

Application Response When WAC is Set to N and TAC is Set to Y The following table describes the behavior of PP2-generated applications when a node resets and WAC = ‘N’ and TAC = ‘Y’.

IF a PP2-generated application

is running on a… THEN it… • Non-Resetting node

• LAN-attached client • Channel-attached client

reconnects its session and returns one of the following error codes from the server and the PP2-generated application takes action appropriate to the error condition:

Code Number Description

Error 2825 No record of the last request was found after the Teradata Database restart.

Error 2826 Request completed but all output was lost due to Teradata Database restart. Error 2828 Request was rolled back during system

recovery.

Error 3120 The request is aborted because of a Teradata Database recovery.

IF a PP2-generated application

is running on a… THEN it… • Non-Resetting node

• LAN-attached client • Channel-attached client

immediately disconnects the session and the application receives one of the following CLI error codes:

Code Number Description

Error 219(EM_DBC_CRASH_B) Server connection lost (network or server problem).

Error 220(EM_DBC_CRASH_A) Server connection lost (network or server problem).

(31)

Query Banding

A query band is a set of name-value pairs that can be set on a session or transaction to identify a query's originating source. The query band enables users to add their own identifiers to the current set of session identification fields. Without query bands, the pooling mechanisms that SQL-generating tools and web applications use hide the identity of users because each connection in the pool logs into the database using the same user account. Therefore, there is no way to tell the source of the request when the request comes from a multi-tiered

application.

The Query Band feature enables requests coming from a single logon process to be classified into different workloads based on the query band that is set by the originating application. Query banding also enables an application to set different priorities for different requests. The application can set a different query band for each type of job, causing the requests to be classified into different workloads. Then the different workloads can run at different priorities, instead of running the entire application at a high priority. By adjusting the priorities of its requests, the application can enable better use of system resources. A session query band is stored in a session table and recovered after a system reset. A transaction query band is discarded when the transaction ends (commits, rolls back, or aborts).

The syntax of SET QUERY_BAND is:

<set_query_band> ::= SET QUERY_BAND = <query_band_attribute> FOR <session_or_txn>; <query_band_attribute> ::= <query_band expression> | NONE

<session_or_txn> ::= SESSION | TRANSACTION

<query_band expression> ::= <quote> <pair chain> <quote> <pair chain> ::= <name-value pair> [<name-value pair> ?]

<name-value pair> ::= <pair name> <equal sign> <pair value> <semicolon operator> <pair name> ::= <character representation>

For example:

EXEC SQL

SET QUERY_BAND = 'priority=1;workload=high;' FOR SESSION ;

Finding Information

The following table provides pointers to the Teradata Database SQL Reference book set, which provides detailed information on programming with SQL:

(32)

This topic … Is found in this chapter / appendix … Of …

Session management SQL Data Handling SQL Reference: Fundamentals

Return codes Statement responses

Introduction to Embedded SQL SQL Data Definition, Control, and Manipulation

Host structures Embedded SQL SQL Reference: Stored Procedures

and Embedded SQL

Host variables Input host variables Output host variables

SQL character strings as host variables Multistatement requests with embedded SQL

SQL statements for miscellaneous embedded SQL activities

• BEGIN DECLARE SECTION • COMMENT (returning form) • DATABASE

• DECLARE TABLE

• DELETE (positioned form) • END DECLARE SECTION • END-EXEC • EXEC • EXEC SQL • INCLUDE • INCLUDE SQLCA • INCLUDE SQLDA • SELECT … INTO

• SELECT AND CONSUME…INTO Note: Teradata Preprocessor2 for Embedded SQL does not support SELECT AND CONSUME within cursors.

• UPDATE (positioned form) • WHENEVER

Static Embedded SQL Statements

Dynamic SQL statement syntax • EXECUTE

• EXECUTE IMMEDIATE • PREPARE

(33)

Security Administration provides information on the LOGDATA and LOGMECH commands.

SQL statements for client-server connectivity • CONNECT • GET CRASH • LOGOFF • LOGON • SET BUFFERSIZE • SET CHARSET • SET CONNECTION • SET CRASH • SET ENCRYPTION

Client-Server Connectivity Statements

SQL statements for multisession programming

• ASYNC • TEST • WAIT

Multisession Asynchronous Programming with Embedded SQL

SQL statements for cursor declaration and manipulation

• CLOSE

• DECLARE CURSOR • FETCH

• FETCH (for scrollable cursor) • OPEN

• POSITION • REWIND

SQL Cursor Control and DML Statements

SQLSTATE variable Result Code Variables SQLCODE variable

ACTIVITY_COUNT variable

SQL Descriptor Area (SQLDA) SQL Descriptor Area (SQLDA) SQL Communications Area (SQLCA) SQL Communications Area (SQLCA)

(34)

(35)

Invoking PP2

The normal sequence of operations when using PP2 is to connect to the Teradata Database, then invoke PP2 to run against the embedded SQL host program. This chapter discusses these operations, plus exceptions (for example, it is not necessary to connect to the Teradata Database before you invoke PP2).

The chapter also includes PP2 options, which allow control of the preprocessing environment of an application module.

Connecting to the Teradata Database Invoking PP2

PP2 Options

Connecting to the Teradata Database

No association exists between the PP2 connection to the Teradata Database made at precompile time and the connection made by an application at execution time. Embedded LOGON and CONNECT statements have no effect on the PP2 connection. Conversely, tdpid and userid PP2 options do not affect the application logon at execution time. For more information, refer to “USERID(userid[,password[,accountid]]) -u

userid[,password[,accountid]]” on page 77.

Normal operation, however, means connecting to the Teradata Database for the precompilation and application execution phases. This requires:

• tdpid • userid

• password for the specified userid

If you do not specify a tdpid, the connection is made using the system default. The runtime returns a positive SQLCODE value and sets SQLWARN0 and SQLWARN2 to ‘W’.

(36)

For more information about userid security, tdpids, and userids, see Teradata Director

Program Reference.

PP2 Connection

It is possible to run PP2 against an application without connecting to the Teradata Database, depending on the SQLCHECK option you have chosen.

Establish PP2 connection by using the TDPID and USERID PP2 options.

If you provide no USERID and PP2 is operating in the IBM mainframe environment, an implicit connection is attempted (see “Implicit Connection” on page 39 for more information on implicit connections).

Runtime Execution Connection

At runtime execution, connection to the Teradata Database is made either explicitly or implicitly.

The transaction mode (ANSI, BTET, COMMIT or 2PC) for a session is established when the connection (either explicit or implicit) is made. The mode is based on the TRANSACT PP2 option for the program that established the session.

Explicit Connection

An application may specify its connection to the Teradata Database explicitly via the CONNECT or the LOGON statement, which are described in SQL Reference: Stored

Procedures and Embedded SQL.

Explicit connection permits precise control over which tdp and userid to connect to. Implicit connection uses system defaults for the tdp and userid. (See “Implicit Connection” on page 39). For this reason, any time you need to connect to a nondefault tdp or userid, make an

IF your application runs

on this platform… THEN the default tdp is…

IBM mainframe obtained from the HSHSPB data area module (see Teradata Call-Level

Interface Version 2 Reference for Channel-Attached Systems for details.)

network-attached system the mtdpid, obtained from the user-defined clispb.dat file or the CLI2SPB data area.

IF you specify the SQLCHECK option as… THEN PP2…

NOSYNTAX does not require connection to the Teradata Database. • FULL (or use FULL as the default)

• SQLFLAGGER(ENTRY)

(37)

Explicit connections are generally advocated because of this precise control, even when default tdps and userids are satisfactory for your required level of security.

Extended Security with LOGMECH and LOGDATA

Network versions of PP2 09.01.00 and above support LOGMECH and LOGDATA statements for use with logon mechanisms. The channel attached (mainframe) version of PP2 does not support these statements.

IF you specify this statement… THEN you can specify…

CONNECT only the userid and password. The tdpid is taken from the system default value.

The CONNECT userid and password are each restricted to eight characters.

This statement is IBM SQL-compatible.

LOGON a complete Teradata Database logon string, including tdpid and accountid.

The userid and password each may be a maximum of 30 characters.

Note: On Windows platforms only:

1 The <userid> and <password> are optional on Windows NT.

2 A LOGON string without a <userid> and <password> will be interpreted as an SSO logon.

IF an explicit connection request is

made AND… THEN…

the application is already connected to the Teradata Database

the previous connection is dropped before the new connection is attempted.

a transaction is active • the connection request is rejected with a SQLCODE of -752.

• the application must terminate the current transaction explicitly using one of the following before attempting to issue a new explicit connection request:

• COMMIT

• ROLLBACK (or ABORT) • LOGOFF

(38)

Syntax

LOGMECH type

LOGDATA:data_address

C Example

EXEC SQL BEGIN DECLARE SECTION; VARCHAR LOGON_STRING[40]; VARCHAR LOGDATA_STRING[40]; EXEC SQL END DECLARE SECTION; EXEC SQL

LOGMECH LDAP;

strcpy (LOGDATA_STRING.arr, "authcid=guestldap password=password"); LOGDATA_STRING.len = strlen(LOGDATA_STRING.len);

EXEC SQL

LOGDATA :LOGDATA_STRING;

strcpy (LOGON_STRING.arr, "tdname"); LOGON_STRING.len = 6;

EXEC SQL

LOGON :LOGON_STRING; Cobol Example

01 LOGON-STRING.

49 FILLER PIC S9(4) COMP VALUE +5. 49 FILLER PIC X(5) VALUE 'TDP1/'. 01 LOGDATA-STRING. 49 FILLER PIC S9(4) COMP VALUE +28. 49 FILLER PIC X(28) VALUE '[email protected]@@PASSWORD’ … … … EXEC SQL LOGMECH KRB5 END-EXEC. EXEC SQL LOGDATA :LOGDATA-STRING END-EXEC. EXEC SQL LOGON :LOGON-STRING END-EXEC.

If you use LOGMECH and LOGDATA statements to pass logon credentials to the database, it

type The logon mechanism type, such as KRB5 or NTLM.

data_address The host variable name containing the data to be passed to the logon mechanism. The value is entirely dependent on the LOGMECH type.

(39)

the type of logon mechanism that you use. To disable the LOGON dialog box, specify GUILOGON=NO as an environmental variable.

Implicit Connection

If a PP2 application running in an IBM mainframe environment submits a SQL request without specifying an explicit connection to the server, an implicit connection is attempted, based on the job or session under which the application is running.

However, network-attached platforms do not provide an implicit connection mechanism. See

Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems for details of the

implicit connection mechanism.

Invoking PP2

PP2 operates in a number of mainframe or network-attached environments. See “Supported Operating Systems and Host Programming Languages” on page 24 for more information. PP2 accepts a number of options that control the operating characteristics. The next sections discuss these options and how to invoke PP2 in different environments.

IBM Mainframe Environment: z/OS and z/VM CMS

Note: Before invoking PP2 in a mainframe environment, start at least one TDP module on the mainframe, even if no data access is required (that is, using NOSYNTAX). If a precompile step is performed prior to starting a TDP module, a fatal error occurs.

You can invoke PP2 as a:

• z/OS batch program using a JCL (job control language) procedure. • z/OS TSO batch or TSO online program, either directly or using a CLIST.

(40)

Operating PP2 in an IBM z/OS or z/VM mainframe environment involves these files:

z/OS Batch

When running PP2 in the z/OS batch environment, use standard z/OS job control language (JCL).

The next example shows preprocessor invocation JCL stream for the COBOL preprocessor. Line numbers relate to the notes following the code and are not part of the JCL syntax.

//PRECOMP JOB (job statement information) 1 //PRECOMP EXEC PGM=PPBMAIN,

// PARM=’option option’ 2 //STEPLIB DD DSN=TERADATA.TRLOAD,DISP=SHR 3 //DD DSN=TERADATA.APPLOAD,DISP=SHR 4 //SYSPRINT DD SYSOUT=*, //DCB=(LRECL=133,RECFM=FBA,BLKSIZE=1330) 5 //SYSTERM DD DUMMY 6 //SYSLIB DD DSN=customer.include.library,DISP=SHR 7 //SYSPUNCH DD DSN=customer.compiler.input, // UNIT=SYSDA,SPACE=(TRK,(5,5)), // DCB=(LRECL=80,RECFM=FB,BLKSIZE=3600) // DISP=(NEW,CATLG) 8 //SYSIN DD DSN=customer.preproc.input,DISP=SHR 9 //SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(5,2)), // DCB=(LRECL=80,RECFM=FB,BLKSIZE=3600) //

File Name Usage Requirement

SYSLIB Library input Required if the embedded SQL INCLUDE text name statement is used and PP2 is executing in the z/OS environment.

SYSIN Source input Mandatory

SYSPRINT Listing Required unless the NOPRINT option is specified.

z/OS JCL must define DCB information for LRECL, RECFM and BLKSIZE parameters.

SYSTERM Diagnostics Required if the TERM option is present.

SYSPUNCH Source output Required unless the NOPUNCH option is specified.

SYSUT1 Work file 1 z/OS JCL must define DCB information for LRECL, RECFM and BLKSIZE parameters.

(41)

Note:

1) The PARM field specifies the options to be used by the preprocessor; within the PARM field, options must be separated by commas and/or blanks.

For details on the options, see “PP2 Options” on page 45.

2) This STEPLIB partitioned data set is the Teradata-supplied C runtime library.

3) This STEPLIB partitioned data set contains the preprocessor load module:

• PPBMAIN (COBOL)

• PPIMAIN (PL/I)

• PPCMAIN (C)

4) SYSPRINT receives the preprocessor listing. SYSPRINT is required unless the NOPRINT option is specified.

The required DCB parameters are:

• LRECL

• RECFM

• BLKSIZE

5) SYSTERM receives the preprocessor diagnostics, separated from the preprocessor listing. SYSTERM is required when the TERM option is specified.

• LRECL

• RECFM

• BLKSIZE

6) The SYSLIB partitioned data set contains the preprocessor include members. SYSLIB is required if the embedded SQL INCLUDE text name statement is used.

7) SYSPUNCH receives the precompiler source. SYSPUNCH is required unless the NOPUNCH option is specified.

SYSPUNCH may be temporary if the appropriate host language compiler is invoked in a following job step.

• LRECL

• RECFM

• BLKSIZE

(42)

9) SYSUT1 is a preprocessor work file.

UNIT=VIO may be used instead of UNIT=SYSDA. The required DCB parameters are:

• LRECL

• RECFM

• BLKSIZE

Required for C and COBOL precompilation. z/OS TSO

When running PP2 in an z/OS TSO environment, use standard TSO commands. You can create a command list to simplify this process.

The next example shows a preprocessor invocation command stream for COBOL. Line numbers relate to the notes following the code and are not part of the syntax.

ATTR VBA1330 RECFM(V,B,A) LRECL(133) BLKSIZE(1330) ATTR F80 RECFM(F) LRECL(80) BLKSIZE(80) ATTR FB3120 RECFM(F,B) LRECL(80) BLKSIZE(3120) 1 ALLOCATE DDNAME(CTRANS) DATASET(’TERADATA.TRLOAD’) SHR 2 ALLOCATE DDNAME(SYSPRINT) SYSOUT(A) USING(VBA1330)

3 ALLOCATE DDNAME(SYSTERM) DATASET(*) USING(F80) 4 ALLOCATE DDNAME(SYSLIB) +

DATASET(’customer.include.library’) SHR 5 ALLOCATEDDNAME(SYSPUNCH) +

DATASET(’customer.fompiler.input’) NEW + TRACKS SPACE(5,5) USING(FB3120)

6 ALLOCATEDDNAME(SYSIN) +

DATASET(’customer.preproc.input’) SHR 7 ALLOCATEDDNAME(SYSUT1) NEW BLOCK(1024) SPACE(50,50) 8 CALL ’TERADATA.APPLOAD(PPBMAIN)’ + ’option option’ FREE DDNAME(CTRANS) FREE DDNAME(SYSPRINT) FREE DDNAME(SYSTERM) FREE DDNAME(SYSLIB) FREE DDNAME(SYSPUNCH) FREE DDNAME(SYSIN) FREE DDNAME(SYSUT1) FREE ATTRL(VBA1330) FREE ATTRL(F80) FREE ATTRL(FB3120) Note:

1) The CTRANS data set is the Teradata-supplied C runtime library.

2) The SYSPRINT data set receives the preprocessor listing. SYSPRINT is required unless you specify the NOPRINT option.

3) The SYSTERM data set receives the preprocessor diagnostics, separated from the preprocessor listing.

(43)

4) The SYSLIB partitioned data set contains the preprocessor include members.

SYSLIB is required only if you specify the embedded SQL INCLUDE text name statement.

5) The SYSPUNCH data set receives the precompiler source.

SYSPUNCH is required unless you specify the NOPUNCH option.

6) The SYSIN data set contains the preprocessor source input.

7) The SYSUT1 data set is a preprocessor work file.

8) TERADATA.APPLOAD contains the preprocessor load module - PPBMAIN (COBOL), PPIMAIN (PL/I) or PPCMAIN (C).

The CALL parameter specifies the PP2 options to be used; within the parameter, you must separate options by commas and/or blanks.

For details on the options, see “PP2 Options” on page 45. z/VM CMS

Use standard CMS commands when running PP2 in a z/VM CMS environment. You can create an EXEC to simplify this process.

The next example shows a preprocessor invocation command stream for COBOL. Line numbers relate to notes following the code.

1 GLOBAL TXTLIB COBLIBVS TSOLIB CMSLIB CLI 2 GLOBAL LOADLIB LSCRTL

3 FILEDEF SYSPRINT PRINTER (RECFM VA LRECL 121) 4 FILEDEF SYSTERM TERMINAL (RECFM F LRECL 80) 5 FILEDEF SYSPUNCH DISK compiler input

(RECFM F LRECL 80) 6 FILEDEF SYSIN DISK preproc input 7 FILEDEF SYSUT1 DISK SQLPPUT1 DATA A3 8 PPBMAIN option option

FILEDEF SYSPRINT CLEAR FILEDEF SYSTERM CLEAR FILEDEF SYSPUNCH CLEAR FILEDEF SYSIN CLEAR FILEDEF SYSUT1 CLEAR

(44)

Note:

1) Specifies the libraries needed to resolve included members. In the preprocessor, the libraries are used only with the SQL INCLUDE statement.

2) Specifies the location of the PPBMAIN module.

3) Specifies the print output to which the preprocessor listing is sent. This command is required unless the NOPRINT option is specified.

4) Specifies the location of preprocessor diagnostics. This is required only if the TERM option is specified.

5) Specifies the output of the preprocessor, which is the input to the compiler.

6) Specifies the input to the preprocessor. The input, which is your application, includes any embedded SQL statements.

7) Specifies a work file location.

8) Each option should be separated by a space.

Network-Attached Environments

In the network-attached environment, PP2 operates on UNIX and Windows. PP2 operation in these environments is described in the sections that follow.

PP2 supports both 32-bit and 64-bit platforms on AIX, HP-UX, Linux, Solaris, and Windows. UNIX

Use standard commands to invoke the process when running PP2 in a UNIX environment. The next example shows an invocation for the C preprocessor. Line numbers relate to the notes following the code and are not part of the syntax.

1 ppcmain option option \

2 -Include (infile) (outfile) Note:

1) Separate the options with a space.

2) (infile) designates the preprocessor source input filename. (outfile) designates the precompiler output.

Windows

Use standard commands to invoke the process when running PP2 in an Windows environment.

The next example shows an invocation for the C preprocessor. Line numbers relate to the notes following the code and are not part of the syntax.

1 ppcmain option option

(45)

1 Separate the preprocessor options with blanks.

2 Use the /I to indicate the search path for embedded SQL INCLUDE text name statements; <infile> is a designator for the preprocessor source input filename.

Specify <infile> by using the INPUT preprocessor option, -i filespec, or by the indirection operator <; <outfile> is a designator for the precompiler output.

Specify <outfile> by using the PUNCH preprocessor option, -o filespec, or by the indirection operator >.

3 Surround each option by quotes (“) when using the standard option syntax.

PP2 Options

PP2 options allow control of the preprocessing environment of an application module. The VERSION option specifies the target COBOL compiler. Use the SQLCHECK option to disable the logon to the server.

Other options control other aspects of the preprocessing function. You can specify a maximum of 20 options.

How to Read the Option Syntax

Option specification is different for applications written for channel- or network-attached systems.

Supported PP2 options are:

This symbol… Means… |

(for channel-attached systems)

OR

For example, APOST|QUOTE means you can specify either APOST or QUOTE (but not both).

[]

(for network-attached systems)

the enclosed variable is optional.

Syntax for Channel-Attached Systems Syntax for Network-Attached Systems

APOST|QUOTE -a | -q

APOSTSQL|QUOTESQL -as | -qs

CHARSET (charset) -cs charset

(46)

Note: Whenever the DATABASE or USERID option values contain any Kanji or other multibyte character set characters, the CHARSET option must precede them in the preprocessor invocation line.

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 (file spec) -i filespec

LINECOUNT (integer) -lc integer

N/A -ld logdata

N/A -lm logmech

MARGINS (m,n [,c ] ) -m m,n[,c]

NULLSCAN|NONULLSCAN -ns | -nns

OPTIONS|NOOPTIONS -lo | -nlo

PRINT [ (file spec) ] |NOPRINT -l [filespec] | -nl PUNCH [ (file spec) ] |NOPUNCH -o [filespec] | -no

SOURCE|NOSOURCE -ls | -nls

SQLCHECK (FULL|NOSYNTAX) -sc FULL|NOSYNTAX SQLFLAGGER

(NONE|ENTRY|INTERMEDIATE)

-sf NONE|ENTRY|INTERMEDIATE

TDPID (tdpid|network group name) -t tdpid|network group name TERM [ (file spec) ] |NOTERM -e [filespec] | -ne

XREF|NOXREF -lx | -nlx

(47)

The file-spec variable used with some options takes the following forms:

Note: If you specify associated PP2 options more than once (for example, both TERM and NOTERM), the rightmost specification prevails.

The next pages describe PP2 options in detail. The UNIX-style syntax appears below the standard syntax.

Client Type File Spec

z/OS mainframe ddname

z/VM CMS mainframe filedef_name

(48)

APOST|QUOTE

-a | -q

Purpose

APOSTSQL specifies that for embedded SQL statements, the string delimiter is the apostrophe (') and that the SQL escape (name) delimiter is the quotation mark ("). Usage Notes

Host language statements generated by PP2 obey the same conventions. These options do not affect string and SQL escape (name) delimiters in embedded SQL statements. See

“APOSTSQL|QUOTESQL -as | -qs” on page 49.

Abbreviation for QUOTE Mutually Exclusive

Q APOST and QUOTE

This specification… Is the default for… Notes

APOST COBOL and may be

changed. Refer to the Notes column.

You can explicitly specify QUOTE for COBOL; if you do, also specify the COBOL compiler QUOTE option.

APOST PL/I and may not be

changed.

(49)

APOSTSQL|QUOTESQL

-as | -qs

Purpose

APOSTSQL specifies that for embedded SQL statements, the string delimiter is the apostrophe (') and that the SQL escape (name) delimiter is the quotation mark (").

QUOTESQL specifies that in embedded SQL statements, the string delimiter is the quotation mark (") and that the SQL escape (name) delimiter is the apostrophe (').

Usage Notes

APOSTSQL and QUOTESQL are mutually exclusive.

QUOTESQL may not be explicitly specified for any language except COBOL. These options do not affect string delimiters in host language statements.

This specification… Is the default for…

QUOTESQL COBOL, if QUOTE is specified.

APOSTSQL COBOL, if APOST is specified or defaulted.

APOSTSQL PL/I and may not be changed.

(50)

CHARSET(charset)

-cs charset

Purpose

Controls the mapping of data, object names and identifier values. Usage Notes

CHARSET specifies the predefined character set name or character set code to be used at the following times:

Specify the CHARSET option before the DATABASE or USERID operations if the DATABASE or USERID option values contain any Kanji or other multibyte character set characters. CHARSET controls the mapping of data, object names and identifier values.

On IBM mainframe client platforms, if the CHARSET option is not specified, PP2 establishes the default character set by order of precedence:

1 Character set specified in the HSHSPB parameter module.

2 Default character set defined on the server specified by the TDPID option when SQLCHECK(FULL) is specified.

3 EBCDIC

On network platforms, if the CHARSET option is not specified, PP2 establishes the default character set by order of precedence:

1 The character set specified in the clispb.dat file

2 ASCII

UTF8 and UTF16 Support

The Teradata Warehouse offers UTF8 (Universal Transformation Format) and UTF16 character set support.

At this time… This happens…

precompile the CHARSET option tells the precompiler the character set in which your input/ output source files, the output listing, and output messages are written.

run the precompiler establishes the character set for communicating with the Teradata Database as the value of the CHARSET option unless overridden by the character set designated by the SET CHARSET request.

For information on SET CHARSET, see SQL Reference: Data Definition Statements Subsequent connections to the Teradata Database use the most recently established character set.