Read Teradata FastLoad Reference text version

Teradata® FastLoad

Reference

Release 07.05.00

B035-2411-062A June 2002

The product described in this book is a licensed product of NCR Corporation. BYNET is an NCR trademark registered in the U.S. Patent and Trademark Office. CICS, CICS/400, CICS/600, CICS/ESA, CICS/MVS, CICSPLEX, CICSVIEW, CICS/VSE, DB2, DFSMS/MVS, DFSMS/ VM, IBM, NQS/MVS, OPERATING SYSTEM/2, OS/2, PS/2, MVS, QMS, RACF, SQL/400, VM/ESA, and VTAM are trademarks or registered trademarks of International Business Machines Corporation in the U. S. and other countries. DEC, DECNET, MICROVAX, VAX and VMS are registered trademarks of Digital Equipment Corporation. HEWLETT-PACKARD, HP, HP BRIO, HP BRIO PC, and HP-UX are registered trademarks of Hewlett-Packard Co. KBMS is a trademark of Trinzic Corporation. MICROSOFT, MS-DOS, MSN, The Microsoft Network, MULTIPLAN, SQLWINDOWS, WIN32, WINDOWS, WINDOWS 2000, and WINDOWS NT are trademarks or registered trademarks of Microsoft Corporation. SOLARIS, SPARC, SUN and SUN OS are trademarks of Sun Microsystems, Inc. TCP/IP protocol is a United States Department of Defense Standard ARPANET protocol. TERADATA and DBC/1012 are registered trademarks of NCR International, Inc. UNIX is a registered trademark of The Open Group. X and X/OPEN are registered trademarks of X/Open Company Limited. YNET is a trademark of NCR Corporation. THE INFORMATION CONTAINED IN THIS DOCUMENT IS PROVIDED ON AN "AS-IS" BASIS, WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY NOT APPLY TO YOU. IN NO EVENT WILL NCR CORPORATION (NCR) BE LIABLE FOR ANY INDIRECT, DIRECT, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS OR LOST SAVINGS, EVEN IF EXPRESSLY ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. 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 NCR intends to announce such features, functions, products, or services in your country. Please consult your local NCR 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. NCR 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] or write: Information Engineering NCR Corporation 17095 Via Del Campo San Diego, California 92127-1711 U.S.A. Any comments or materials (collectively referred to as "Feedback") sent to NCR will be deemed non-confidential. NCR 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, NCR 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. Copyright © 1998-2002, NCR Corporation All Rights Reserved

Preface

Purpose

This book describes the FastLoad utility for channel- and network-attached client systems. It describes the operational features and capabilities of the utility; and includes the syntax for FastLoad commands and the Teradata SQL statements used in FastLoad session control and data handling activities.

Supported Releases

This product supports the following releases: · · · Teradata RDBMS V2R4.1.3 Teradata Tools and Utilities 06.02.00 Teradata FastLoad 07.05.00

Changes to This Book

This book includes the following changes to support the current releases:

Date Description

June 2002

·

No changes from the prior release. However, a note was added to the AXSMOD command stating that the Large File Access Module is no longer available because the Data Connector API supports file sizes greater than 2 GB on Windows NT/2000/XP, HP-UX, AIX, and Solaris-SPARC platforms.

Audience

This book is intended for system and application programmers and system administrators using the FastLoad utility with the following systems: · · · · · Teradata DBS for TOS on NCR System 3600 or DBC/1012 Teradata RDBMS for UNIX Teradata for Windows NT Teradata for Windows 2000 Teradata for MVS/VM

Teradata FastLoad Reference

i

Preface

Prerequisites

This book is a reference document. There is no prerequisite reading, but you should be familiar with computer technology, database management systems, and utilities that load and retrieve data.

Related Documents

Teradata Tools and Utilities Support Publications

The general publications that support Teradata Tools and Utilities are listed below. The most recent versions of the publications are maintained on the NCR Information Products Publishing Web site. The online versions of these books will be updated as necessary to reflect any changes introduced between product releases. Note: In the book numbers referenced below, the first eight characters are a unique Product ID for the book, and can be used to search for the book online. The mmyx represents the publication date, where mm is the month, y is the last digit of the year, and x is an internal code.

B035-2029-mmyx B035-2427-mmyx B035-2407-mmyx B035-2415-mmyx B035-2420-mmyx B035-2422-mmyx B035-2437-mmyx B035-2438-mmyx B035-2439-mmyx B035-2440-mmyx B035-1096-mmyx Teradata® Tools and Utilities Release Definition Teradata® Tools and Utilities Release Summary Teradata® Tools and Utilities Installation Guide for Microsoft® Windows® Teradata® Tools and Utilities Installation Guide for IBM® MVS Teradata® Tools and Utilities Installation Guide for NCR UNIX® MP-RAS Teradata® Tools and Utilities Installation Guide for IBM® VM Teradata® Tools and Utilities Installation Guide for IBM® AIX® Teradata® Tools and Utilities Installation Guide for HP-UX® Teradata® Tools and Utilities Installation Guide for SolarisTM Teradata® Tools and Utilities Installation Guide for IBM® OS/390 USS® Teradata® RDBMS Messages Reference

ii

Teradata FastLoad Reference

Preface

Product Related Publications

The publications that support the FastLoad product are listed below.

BD10-4858-A BD10-4957-D B035-1031-mmyx B035-1101-mmyx B035-2414-mmyx B035-2425-mmyx Teradata® DBS Reference Manual (TOS), Volume 1 (Database Design and Administration) Teradata® RDBMS for UNIX SQL Reference Teradata® for Windows NT SQL Reference Teradata® RDBMS SQL Reference Basic Teradata® Query (BTEQ) Reference Teradata® Tools and Utilities Access Modules Reference

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 NCR Web sites:

http://www.info.ncr.com/ A direct link to NCR Information Products Publishing library where you can view, download, or order technical documentation and CD-ROMs. The NCR 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

http://www.ncr.com/

Teradata FastLoad Reference

iii

Preface

List of Acronyms

The following acronyms, listed in alphabetical order, are used in this book:

AMP Access Module Process (UNIX-based systems) Access Module Processor (TOS-based systems) ANSI AP ASCII BPL BTEQ CLIv2 CMS COP DBC DBS DD American National Standards Institute Application Processor American Standard Code for Information Interchange Bypass Label Basic Teradata Query Call-Level Interface version 2 Conversational Monitor System Communications Processor Database Computer Database System Data Definition Data Dictionary DLL DNS INMOD JCL MP-RAS MVS NFS NL OS PIC PL/I RDBMS SPB SSO SQL Dynamic-Link Library Domain Name System Input Module Job Control Language Multi-Processor Reliability, Availability & Serviceability Multiple Virtual Storage Network File System Nonlabel Operating System Position Independent Code Programming Language 1 Relational Database Management System System Parameter Block Single Sign On Structured Query Language

iv

Teradata FastLoad Reference

Preface

TCP/IP TOS TDP TSO VM

Transmission Control Protocol/Internet Protocol Teradata Operating System Teradata Director Program Time Sharing Option Virtual Machine

Teradata FastLoad Reference

v

Preface

vi

Teradata FastLoad Reference

Contents

Preface Purpose ............................................................................................................................... i Supported Releases ........................................................................................................... i Changes to This Book ....................................................................................................... i Audience ............................................................................................................................. i Prerequisites ...................................................................................................................... ii Related Documents .......................................................................................................... ii Technical Information on the Web................................................................................iii List of Acronyms .............................................................................................................iv

Chapter 1: Introduction FastLoad Utility Overview............................................................................................. 1­2 Description .................................................................................................................... 1­2 How It Works................................................................................................................ 1­2 What It Does.................................................................................................................. 1­3 Operating Features and Capabilities ............................................................................ 1­4 Operating Systems ....................................................................................................... 1­4 Operating Modes.......................................................................................................... 1­4 Character Sets................................................................................................................ 1­5 Latin Character Sets .................................................................................................. 1­5 Japanese Character Sets ............................................................................................ 1­5 Chinese and Korean Character Sets........................................................................ 1­5 Site-Defined Character Sets...................................................................................... 1­6 Character Set Specifications for AXSMOD ............................................................ 1­7 Data Transfer Capabilities.............................................................................................. 1­8 Data Conversion Capabilities ..................................................................................... 1­8 Checkpoints................................................................................................................... 1­9 Input Data Formats ....................................................................................................... 1­10 Definition ..................................................................................................................... 1­10 Formatted Data ........................................................................................................... 1­11 Unformatted Data....................................................................................................... 1­12 Binary Data.................................................................................................................. 1­12 Text Data...................................................................................................................... 1­13

Teradata FastLoad Reference

vii

Contents

Variable-length Text................................................................................................... 1­13 Unsupported Data Sets and Tapes........................................................................... 1­14 FastLoad Commands .................................................................................................... 1­15 Command Functions.................................................................................................. 1­15 FastLoad Command Summary................................................................................. 1­15 Teradata SQL Statements .......................................................................................... 1­17 A FastLoad Example ..................................................................................................... 1­19 Using Channel-Attached Client Systems ............................................................. 1­23 Using the MultiLoad Utility................................................................................... 1­23 Using Different Input File Formats....................................................................... 1­23 Using INMOD Routines ......................................................................................... 1­24

Chapter 2: Using FastLoad Invoking FastLoad........................................................................................................... 2­2 File Requirements ......................................................................................................... 2­2 In Interactive Mode ...................................................................................................... 2­2 In Batch Mode on Network-Attached Client Systems ............................................ 2­3 In Batch Mode on Channel-Attached MVS Client Systems ................................... 2­3 In Batch Mode on Channel-Attached VM Client Systems ..................................... 2­4 FastLoad Runtime Parameters.................................................................................... 2­4 VM Example.................................................................................................................. 2­8 MVS Example................................................................................................................ 2­9 UNIX and Windows Examples ................................................................................ 2­11 Terminating FastLoad................................................................................................... 2­12 Methods ....................................................................................................................... 2­12 Normal Termination .................................................................................................. 2­12 Abort Termination...................................................................................................... 2­13 Restarting a Paused FastLoad Job ............................................................................... 2­14 Paused Fastload Jobs.................................................................................................. 2­14 After a Client System or FastLoad Failure.............................................................. 2­14 After a Database Overfill Condition ........................................................................ 2­15 After a Teradata RDBMS Failure.............................................................................. 2­15 After an AP Reset Condition..................................................................................... 2­16 After an Unrecoverable Error Condition ................................................................ 2­17 Factors Affecting a Restart ........................................................................................ 2­18 Restart Procedure ....................................................................................................... 2­18 Programming Considerations ..................................................................................... 2­20 The FastLoad Configuration File.............................................................................. 2­20 Character Set Specification........................................................................................ 2­22 ANSI/SQL DateTime Specifications ....................................................................... 2­22 Checkpoint Tradeoffs................................................................................................. 2­23

viii

Teradata FastLoad Reference

Contents

Comments.................................................................................................................... 2­24 Concurrent Load Utility Tasks ................................................................................. 2­24 Data Conversion Factors ........................................................................................... 2­25 Error Limits ................................................................................................................. 2­26 Foreign Key References ............................................................................................. 2­26 Secondary Indexes...................................................................................................... 2­26 Non-unique Index Sorts ............................................................................................ 2­26 Duplicate Rows........................................................................................................... 2­26 Join Index Restrictions ............................................................................................... 2­26 Range Constraints ...................................................................................................... 2­27 Record Mode Load Anomaly ................................................................................... 2­28 Session Limits.............................................................................................................. 2­28 File Size Restrictions................................................................................................... 2­28 Space Requirements and Limitations ...................................................................... 2­28 UNIX Signals............................................................................................................... 2­29 Using INMOD and Notify Exit Routines................................................................... 2­30 INMOD Routines........................................................................................................ 2­30 Notify Exit Routines................................................................................................... 2­30 Programming Languages .......................................................................................... 2­30 Programming Structure............................................................................................. 2­31 Entry Points ................................................................................................................. 2­32 Addressing Mode on VM and MVS Systems......................................................... 2­32 The FastLoad/INMOD Interface ............................................................................. 2­32 The FastLoad/Notify Exit Routine Interface.......................................................... 2­34 Using the FastLoad Sample INMOD Routines ...................................................... 2­36 Calling BLKEXIT.C..................................................................................................... 2­37 Calling BLKEXITR.C.................................................................................................. 2­37 Creating Your Own INMOD Routines.................................................................... 2­38 Writing a FastLoad Job Script...................................................................................... 2­39 Definition ..................................................................................................................... 2­39 Entering FastLoad Commands................................................................................. 2­39 Example FastLoad Job Script .................................................................................... 2­39 Running Multi-File FastLoad Jobs .............................................................................. 2­42 Definition ..................................................................................................................... 2­42 Initiating the FastLoad Job and Loading the First Data Source........................... 2­42 Restarting the FastLoad Job and Loading the Second Data Source .................... 2­43 Restarting the FastLoad Job and Loading the Third Data Source....................... 2­43 Terminating the FastLoad Job .................................................................................. 2­43 Command Functions.................................................................................................. 2­44 Handling FastLoad Errors............................................................................................ 2­46 FastLoad Error Conditions........................................................................................ 2­46 Error Recording .......................................................................................................... 2­47

Teradata FastLoad Reference

ix

Contents

Error Table Formats ................................................................................................... 2­47 Correcting Errors ........................................................................................................ 2­48 Procedure for Correcting Errors in the First Error Table...................................... 2­48 Procedure for Correcting Errors in the Second Error Table ................................. 2­50

Chapter 3: FastLoad Commands Syntax Notes.................................................................................................................. 3­1 AXSMOD .......................................................................................................................... 3­2 BEGIN LOADING........................................................................................................... 3­4 CLEAR .............................................................................................................................. 3­9 DATEFORM ................................................................................................................... 3­10 DEFINE ........................................................................................................................... 3­12 END LOADING............................................................................................................. 3­28 ERRLIMIT....................................................................................................................... 3­30 HELP ............................................................................................................................... 3­32 HELP TABLE ................................................................................................................. 3­35 INSERT............................................................................................................................ 3­37 LOGOFF.......................................................................................................................... 3­41 LOGON ........................................................................................................................... 3­43 NOTIFY........................................................................................................................... 3­46 OS..................................................................................................................................... 3­51 QUIT ................................................................................................................................ 3­56 RECORD ......................................................................................................................... 3­58 SESSIONS ....................................................................................................................... 3­61 SET RECORD ................................................................................................................. 3­64 SET SESSION CHARSET ............................................................................................. 3­70 SHOW ............................................................................................................................. 3­72 SHOW VERSIONS ........................................................................................................ 3­74 SLEEP .............................................................................................................................. 3­76 TENACITY ..................................................................................................................... 3­78

Appendix A: How to Read Syntax Diagrams Syntax Diagram Conventions....................................................................................... A­2

Appendix B: Multifile FastLoad Job Script Examples First Output File...............................................................................................................B­2 Second Output File..........................................................................................................B­4

x

Teradata FastLoad Reference

Contents

Third Output File ............................................................................................................B­6

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS............................................................................................................ C­2 Assembler INMOD Example ..................................................................................... C­2 COBOL INMOD Example.......................................................................................... C­6 PL/I INMOD Example ............................................................................................... C­9 For UNIX ....................................................................................................................... C­11 BLKEXIT.C Sample INMOD.................................................................................... C­11 BLKEXITR.C Sample INMOD ................................................................................. C­17 For All Platforms .......................................................................................................... C­24 Notify Exit Routine Example ................................................................................... C­24

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For VM ............................................................................................................................. D­2 Using Assembler.......................................................................................................... D­2 Procedure................................................................................................................... D­2 Compiling the INMOD............................................................................................ D­2 Executing the INMOD ............................................................................................. D­2 Using COBOL ............................................................................................................. D­3 Procedure................................................................................................................... D­3 Compiling the INMOD............................................................................................ D­3 Link-Editing the INMOD ........................................................................................ D­3 Executing the INMOD ............................................................................................. D­4 Using PL/I.................................................................................................................... D­5 Procedure................................................................................................................... D­5 Compiling the INMOD............................................................................................ D­5 Link-Editing the INMOD ........................................................................................ D­6 Executing the INMOD ............................................................................................. D­6 Using SAS/C ............................................................................................................... D­7 Procedure................................................................................................................... D­7 Compiling the Routine............................................................................................. D­7 Link-Editing the Routine ......................................................................................... D­7 Executing FastLoad .................................................................................................. D­8 For MVS ........................................................................................................................... D­9 Using Assembler.......................................................................................................... D­9 Procedure................................................................................................................... D­9 Example...................................................................................................................... D­9 Using COBOL ............................................................................................................ D­11

Teradata FastLoad Reference

xi

Contents

Procedure ................................................................................................................. D­11 Example.................................................................................................................... D­11 Using PL/I ................................................................................................................. D­13 Procedure ................................................................................................................. D­13 Example.................................................................................................................... D­13 Using SAS/C ............................................................................................................. D­15 Procedure ................................................................................................................. D­15 Example.................................................................................................................... D­15 For UNIX........................................................................................................................ D­17 Compiling and Linking Routines............................................................................ D­17 For MP-RAS, SOLARIS-SPARC, SOLARIS-INTEL ........................................... D­17 For HP-UX ............................................................................................................... D­18 For IBM-AIX ........................................................................................................... D­19 For Windows................................................................................................................. D­22 Using C........................................................................................................................ D­22

Glossary...............................................................................................................Glossary­1

Index.......................................................................................................................... Index­1

xii

Teradata FastLoad Reference

List of Figures

Figure 3-1 Indicator Bits ............................................................................................ 3­8

Teradata FastLoad Reference

xiii

List of Figures

xiv

Teradata FastLoad Reference

List of Tables

Table 1-1 Table 1-2 Table 1-3 Table 1-4 Table 1-5 Table 1-6 Table 2-1 Table 3-1 Table 3-2 Supported Data Formats ...................................................................... 1­10 Formatted Record Field Descriptions................................................. 1­11 Unformatted Record Field Descriptions ............................................ 1­12 Binary Record Field Descriptions ....................................................... 1­13 FastLoad Commands ............................................................................ 1­15 Teradata SQL Statements ..................................................................... 1­17 Runtime Parameter Descriptions .......................................................... 2­4 ANSI/SQL DateTime Specifications ................................................. 3­23 Events That Create Notifications ....................................................... 3­48

Teradata FastLoad Reference

xv

List of Tables

xvi

Teradata FastLoad Reference

Chapter 1:

Introduction

This chapter provides an introductory overview of the FastLoad utility, including: · · "FastLoad Utility Overview" provides a general description of FastLoad, how it works and what it does "Operating Features and Capabilities" provides information about the operating systems that the utility runs under, the operating modes for different system configurations, and the key operational capabilities "Input Data Formats" provides a description of the record formats of input source data for FastLoad operations "FastLoad Commands" provides a brief description of the FastLoad commands and the Teradata SQL statements that you can use in your FastLoad jobs "A FastLoad Example" provides an example of a small FastLoad job that you can quickly set up and run

· ·

·

Teradata FastLoad Reference

1­1

Chapter 1: Introduction FastLoad Utility Overview

FastLoad Utility Overview

Description

FastLoad is a command-driven utility you can use to quickly load large amounts of data in an empty table on a Teradata Relational Database Management System (RDBMS). You can load data from: · · · · Disk or tape files on a channel-attached client system Input files on a network-attached workstation Special input module (INMOD) routines you write to select, validate, and preprocess input data Any other device providing properly formatted source data

FastLoad uses multiple sessions to load data. However, it loads data into only one table on a Teradata RDBMS per job. If you want to load data into more than one table in an RDBMS, you must submit multiple FastLoad jobs--one for each table.

How It Works

FastLoad processes a series of FastLoad commands and Teradata SQL statements you enter either interactively or in batch mode. You use the FastLoad commands for session control and data handling of the data transfers. The Teradata SQL statements create, maintain and drop tables on the Teradata RDBMS. During a load operation, FastLoad inserts the data from each record of your data source into one row of the table on a Teradata RDBMS. The table on the Teradata RDBMS receiving the data must be empty and have no defined secondary indexes. Note: FastLoad does not load duplicate rows from your data source to the Teradata RDBMS. (A duplicate row is one in which every field contains the exact same data as the fields of an existing row.) This is true even for MULTISET tables. If you want to load duplicate rows in a MULTISET table, use the MultiLoad utility.

1­2

Teradata FastLoad Reference

Chapter 1: Introduction FastLoad Utility Overview

What It Does

When you invoke FastLoad, the utility executes the FastLoad commands and Teradata SQL statements in your FastLoad job script. These direct FastLoad to: 1 2 3 4 Log you on to the Teradata RDBMS for a specified number of sessions, using your username, password and tdpid/acctid information. Load the input data into the FastLoad table on the Teradata RDBMS. Log you off from the Teradata RDBMS If the load operation was successful, return the following information about the FastLoad operation and then terminate: · Total number of records read, skipped and sent to the Teradata RDBMS · Number of errors posted to the FastLoad error tables · Number of inserts applied · Number of duplicate rows

Teradata FastLoad Reference

1­3

Chapter 1: Introduction Operating Features and Capabilities

Operating Features and Capabilities

Operating Systems

The FastLoad utility runs under the following operating systems:

System Configuration Operating System

Network-attached

· UNIX (MP-RAS and HP-UX) · IBM AIX · Solaris (SPARC and Intel) · Windows 95, 2000, and NT (In this book, Windows refers to all three.)

Channel-attached

· IBM MVS · IBM VM

Caution:

Some system configurations running FastLoad on a network-attached UNIX workstation with the dataset mounted on a Network File System (NFS) can produce errors and failed or incomplete data transfer operations. Do not use FastLoad with an NFS-mounted dataset.

Operating Modes

The FastLoad utility runs in the following operating modes: · · Interactive Batch

1­4

Teradata FastLoad Reference

Chapter 1: Introduction Operating Features and Capabilities

Character Sets

The FastLoad utility supports the following character sets.

Latin Character Sets

Character Set System Configuration Name Code

Network-attached Channel-attached

ASCII EBCDIC

255 64 or 192

Japanese Character Sets

Character Set System Configuration Name Code

Network-attached

KANJIEUC_0U KANJISJIS_0S

118 119 111 or 239 112 or 240 113 or 241

Channel-attached

KATAKANAEBCDIC KANJIEBCDIC5026_0I KANJIEBCDIC5035_0I

Note: The kanji character sets are not supported by Teradata DBS for TOS Version 1, Release 5.1.x and below, or Teradata RDBMS for UNIX Version 2, Release 1.1.1 and below.

Chinese and Korean Character Sets

Chinese and Korean character sets will be available for channel- and networkattached client systems when Teradata RDBMS V2R4.1.2 is released. The following character sets are defined as a part of V2R4.1.2:

Chinese Character Sets--Teradata Defined System Configuration Name Code

Channel-Attached

SCHEBCDIC935_21J TCHEBCDIC937_3IB

109 110 121 122

Network-Attached

SCHGB2312_1T0 TCHBIG5_1R0

Teradata FastLoad Reference

1­5

Chapter 1: Introduction Operating Features and Capabilities

The following Korean character sets are defined as a part of V2R4.1.2:

Korean Character Sets--Teradata Defined System Configuration Name Code

Channel-Attached Network-Attached

HANGULEBCDIC933_1II HANGULKSC5601_2R4

108 120

Site-Defined Character Sets

When the character sets defined for V2R4.1.2 are not appropriate for your site, you can define the character sets in the following tables.

Japanese Character Sets--Site Defined System Configuration Name Code

Channel-Attached

SDKATAKANAEBCDIC_4IF SDKANJIEBCDIC5026_4IG SDKANJIEBCDIC5035_4IH

77 78 79 91 92

Network-Attached

SDKANJIEUC_1U3 SDKANJISJIS_1S3

Chinese Character Sets--Site Defined System Configuration Name Code

Channel-Attached

SDSCHEBCDIC935_6IJ SDTCHEBCDIC937_7IB

75 76 94 95

Network-Attached

SDTCHGB2312_2T0 SDTCHBIG5_3R0

Korean Character Sets--Site Defined System Configuration Name Code

Channel-Attached Network-Attached

SDHANGULEBCDIC933_5II SDHANGULKSC5601_4R4

74 93

Note: Refer to Teradata RDBMS SQL Reference, volume 1 for information on defining your own character set.

1­6

Teradata FastLoad Reference

Chapter 1: Introduction Operating Features and Capabilities

Rules for Using Chinese and Korean Character Sets You need to

follow these rules when using Chinese and Korean character sets on channeland network-attached platforms. · Object Names ­ Object names are limited to A-Z, a-z, 0-9, and special characters such as $ and _. Maximum String Length ­ The DBS requires two bytes to process each of the Chinese or Korean characters. This limits both request size and record size. For example, if a record consists of one string, the length of that string is limited to a maximum of 32,000 characters or 64,000 bytes.

·

Note: For information on Chinese and Korean character set restrictions for the DBS, refer to Teradata V2R4.1.2 documentation.

Character Set Specifications for AXSMOD

When an AXSMOD is used, FastLoad will pass the session character set as an attribute to the AXSMOD for its possible use (most AXSMODs will not make any use of this information). The information will be passed as follows:

If the session character set was specified by... Then the attribute name will be...

name id

CHARSET_NAME CHARSET_NUMBER

The attribute value will be a variable length character string consisting of either the character set name, or the character representation of the character set id, as appropriate. For more information about alternate character sets, refer to the appendices of the following reference documents for your operating system environment: · · · Teradata DBS Reference Manual (TOS), Volume 1 (Database Design and Administration) Teradata RDBMS for UNIX SQL Reference Teradata for Windows NT SQL Reference

See "Product Related Publications" on page -iii.

Teradata FastLoad Reference

1­7

Chapter 1: Introduction Data Transfer Capabilities

Data Transfer Capabilities

On network-attached workstations, FastLoad uses the TCP/IP network protocol for all data transfer operations. On channel-attached systems, FastLoad transfers data as either: · · A multivolume data set or file A number of single-volume data sets or files in separate FastLoad jobs

You can restart serial FastLoad operations by loading the next tape in a series instead of beginning with the first tape in a set. In either case, FastLoad: · · Uses multiple Teradata sessions, at one session per AMP, to transfer data Transfers multiple rows of data within a single message

Also, in either case, until you complete the FastLoad job and have loaded the data into the FastLoad table: · · There is no journaling or fallback data You cannot define the secondary indexes

Data Conversion Capabilities

The FastLoad utility can redefine the data type specification of numeric, character and date input data so it matches the type specification of its destination column in the FastLoad table on the Teradata RDBMS. If, for example, an input field with numeric type data is targeted for a column with a character data type specification, FastLoad can change the input data specification to character before inserting it into the table. The types of data conversions you can specify are: · · · · Numeric-to-numeric (for example integer-to-decimal) Character-to-numeric Character-to-date Date-to-character

Note: Redundant conversions, like integer-to-integer, are legal and necessary to support the zoned decimal format. For more information about the zoned decimal format, see the Teradata database design and administration reference documentation for your operating system environment.

1­8

Teradata FastLoad Reference

Chapter 1: Introduction Data Transfer Capabilities

You use the datatype specification of the DEFINE command to convert input data to a different type before inserting it into the FastLoad table on the Teradata RDBMS. For details on data types and data conversions, see Teradata RDBMS SQL Reference (B035-1101-061A).

Checkpoints

Checkpoints are entries posted to a restart log table at regular intervals during the FastLoad data transfer operation. If processing stops while a FastLoad job is running, you can restart the job at the most recent checkpoint. If, for example, you are loading 1,000,000 records into a table and have specified checkpoints every 50,000 records, FastLoad pauses and posts an entry to the restart log table whenever multiples of 50,000 records have been successfully sent to the Teradata RDBMS. If the job stops after record 60,000 has been loaded, you can restart the job at the record immediately following the last checkpoint--record 50,001. You enable the checkpoint function by specifying a checkpoint value in the BEGIN LOADING command.

Teradata FastLoad Reference

1­9

Chapter 1: Introduction Input Data Formats

Input Data Formats

Definition

Input data is the raw data that FastLoad loads from the client system or workstation to the Teradata RDBMS. The input data can be either formatted, unformatted, variable-length text or of specific file types, depending on the configuration of your client system, as shown in Table 1-1.

Table 1-1 Supported Data Formats

System Configuration Supported Data Formats

Network-attached

· Formatted · Unformatted · Binary · Text · Variable-length text

Channel-attached

Data sets and tape files with the following record format (RECFM) attributes: · F (fixed) · FB (fixed block) · V (variable) · VB (variable block) · VBS (variable block, spanned) On VM, tapes with the following symbolic names and virtual addresses:

Symbolic Name Virtual Address Symbolic Name Virtual Address

TAP0 TAP1 TAP2 TAP3 TAP4 TAP5 TAP6 TAP7

180 181 182 183 1184 185 186 187

TAP8 TAP9 TAPA TAPB TAPC TAPD TAPE TAPF

188 289 28A 28B 28C 28D 28E 28F

1 ­ 10

Teradata FastLoad Reference

Chapter 1: Introduction Input Data Formats

Formatted Data

Formatted data on network-attached systems is input data that conforms to the format of data from a Teradata RDBMS source, such as a BTEQ EXPORT file. As described in Table 1-2, each record has: · · · · A two-byte data length field Optionally, a variable-length indicator bytes field A variable-length input data field A one-byte end-of-record delimiter field

Table 1-2 Formatted Record Field Descriptions

Input Record Field Description

Data length

A two-byte field indicating the total length of the record, in bytes, excluding the first two bytes (this field), and the last byte (the endof-record field). The data length must be specified as an explicit value, not an ASCII value. For a data length of one byte, for example, the specification must be hexadecimal 01. It can not be hexadecimal 31, or the ASCII equivalent of the number 1.

Indicator bytes Input data

Optional bytes to indicate null data. The actual input data for rows in the FastLoad table. The input data stream always starts at either: · The third byte of the record, if there are no optional indicator bytes · Immediately after the last optional indicator byte The length of the input data field, less any optional indicator bytes, is as specified in the first two bytes.

End-of-record

The end-of-record delimiter field can be either of two hexadecimal values: · 0A (carriage return) · 0D (line feed)

Teradata FastLoad Reference

1 ­ 11

Chapter 1: Introduction Input Data Formats

Unformatted Data

Unformatted data on network-attached systems is data that does not conform to the format of data from a Teradata RDBMS source. It may, however, originate from a Teradata RDBMS source, or from other sources, such as a dBASE III file. As described in Table 1-3, unformatted records include: · · Optionally, a variable-length indicator bytes field A variable-length data field

Unformatted records have no end-of-record delimiter field.

Table 1-3 Unformatted Record Field Descriptions

Input Record Field Description

Indicator bytes Input data

Optional bytes to indicate null data. Raw data to be loaded into the FastLoad table on the Teradata RDBMS.

Note: If your network-attached system configuration includes both UNIX and Windows operating system platforms, make sure that your FastLoad job scripts accommodate the different ways that each platform specifies new lines in ASCII text files. Windows platforms use a two-character sequence to signify a new line-- carriage return + line feed. UNIX platforms use a single new-line character to perform the same function. When loading unformatted ASCII files from a network-attached system, always make sure that the DEFINE command properly identifies the format of the new-line function for the source platform: · · One character for UNIX platforms Two characters for Windows platforms

Binary Data

The binary data format is a 2-byte integer, n, followed by n bytes of data. Binary data format is similar to formatted data format, except that there is no end-ofrecord marker. As described in Table 1-2, each record has: · · · A two-byte data length field Optionally, a variable-length indicator bytes field A variable-length input data field

1 ­ 12

Teradata FastLoad Reference

Chapter 1: Introduction Input Data Formats Table 1-4 Binary Record Field Descriptions

Input Record Field Description

Data length

A two-byte field indicating the total length of the record, in bytes, excluding the first two bytes (this field). The data length must be specified as an explicit value, not an ASCII value. For a data length of one byte, for example, the specification must be hexadecimal 01. It can not be hexadecimal 31, or the ASCII equivalent of the number 1.

Indicator bytes Input data

Optional bytes to indicate null data. The actual input data for rows in the FastLoad table. The input data stream always starts at either: · The third byte of the record, if there are no optional indicator bytes · Immediately after the last optional indicator byte The length of the input data field, less any optional indicator bytes, is as specified in the first two bytes.

Text Data

Text data on network-attached systems consists of an arbitrary number of bytes, followed by an end-of-record marker. This marker is a linefeed on UNIX platforms and is a carriage-return/linefeed pair on Windows platforms. Text data can be accepted as two types: non-ASCII and ASCII. If the data is binary data (not binary record type) that is not printable as ASCII text, then it follows the same rules as formatted data, except that there is not a leading 2byte length. The text is scanned by the utility until it reaches an end-of-record marker, and that is considered the record. This type of text data can also have indicator bytes. The other type of text data is data contained in ASCII form (for example, a report created with Notepad on a PC). This data is comprised of ASCII text (for instance), with each record ending with an end-of-record marker. With this type of text data, all fields are generally fixed-length fields.Variablae-length fields, like VARCHAR, are not allow because there is no way to specify a 2-byte length for the field.

Variable-length Text

Variable-length text on a network-attached system is ASCII text data consisting of variable-length fields and records, with each field separated by a delimiter character. When loading variable-length text records from a network-attached system, use the SET RECORD command to specify the delimiter character.

Teradata FastLoad Reference

1 ­ 13

Chapter 1: Introduction Input Data Formats

Unsupported Data Sets and Tapes

The FastLoad utility does not support the following types of data sets and tapes on channel-attached systems: · · Concatenated data sets Nonlabel (NL) and bypass label (BPL) tapes

1 ­ 14

Teradata FastLoad Reference

Chapter 1: Introduction FastLoad Commands

FastLoad Commands

Command Functions

The FastLoad utility accepts both FastLoad commands and a subset of Teradata SQL statements. The FastLoad commands perform session control and data handling activities. The Teradata SQL statements define and manipulate the data stored in the Teradata RDBMS. This section provides a summary of the FastLoad commands and Teradata SQL statements.

FastLoad Command Summary

The FastLoad commands perform two types of activities:

Activity Description

Session control

Session control commands begin and end FastLoad sessions and provide online information about a particular FastLoad operation. Data handling commands establish and define a FastLoad operation.

Data handling

The FastLoad commands that perform these activities are summarized below. See Chapter 3: "FastLoad Commands"for a detailed description of each FastLoad command.

Table 1-5 FastLoad Commands

Activity FastLoad Command Function

Session control

HELP HELP TABLE LOGOFF/QUIT LOGON NOTIFY OS SESSIONS

Lists FastLoad commands and options. Creates a list of field names that you can use with the INSERT statement. Ends FastLoad sessions and terminates FastLoad. Begins one or more FastLoad sessions. Specifies a user exit or action to be performed when certain significant events occur. Enters client operating system commands. Specifies the number of FastLoad sessions logged on with a LOGON command and, optionally, the minimum number of sessions required to run the job.

Teradata FastLoad Reference

1 ­ 15

Chapter 1: Introduction FastLoad Commands Table 1-5 (Continued) FastLoad Commands

Activity FastLoad Command Function

Session control (continued)

SHOW SHOW VERSIONS SLEEP TENACITY

Shows the current field/file definitions established by DEFINE commands. Shows the current level of all FastLoad software modules. Specifies the number of minutes that FastLoad pauses before retrying a logon operation. Specifies the number of hours that FastLoad continues trying to log on when the maximum number of load jobs is already running on the Teradata RDBMS. Specifies the name and initialization string for a shared object file that loads data from a file on network-attached client systems. Identifies the tables used in the FastLoad operation and, optionally, specifies when checkpoints are taken or if the user supplies indicator data. Cancels the current DEFINE command specifications. Specifies the form of the DATE data type specifications for the FastLoad job. Describes each field of an input data source record and specifies the name of the input data source or INMOD routine. Informs the Teradata RDBMS that all input data has been sent. Limits the number of errors detected during the loading phase of a FastLoad job. Processing stops when the limit is reached. Specifies the number of a record in an input data source at which FastLoad begins to read data and/or the number of the last record to be read.

Data handling

AXSMOD

BEGIN LOADING

CLEAR DATEFORM DEFINE

END LOADING ERRLIMIT

RECORD

1 ­ 16

Teradata FastLoad Reference

Chapter 1: Introduction FastLoad Commands Table 1-5 (Continued) FastLoad Commands

Activity FastLoad Command Function

Data handling (continued)

SET RECORD

Specifies that the input data records are either: · Formatted · Unformatted · Binary · Text · Variable-length text Note: The SET RECORD command applies only to network-attached systems.

SET SESSION CHARSET

Specifies which character set is in effect during a specific FastLoad invocation. Note: The SET SESSION CHARSET command applies only to network-attached systems.

Note: The SET RETRY command is obsolete and ignored by FastLoad. See "Invoking FastLoad" on page 2-2 and "TENACITY" on page 3-78 for a description of the tenacity function.

Teradata SQL Statements

Teradata SQL statements define and manipulate the data stored in the Teradata RDBMS. The Teradata SQL statements supported by FastLoad are summarized in the table below. The FastLoad utility supports only the Teradata SQL statements listed below. To use other Teradata SQL statements, you must exit FastLoad and enter them from another application, such as BTEQ. Refer to Teradata RDBMS SQL Reference for your operating system environment for more information about using Teradata SQL statements. See "Product Related Publications" on page -iii.

Table 1-6 Teradata SQL Statements

Teradata SQL Statement Function

CREATE TABLE DATABASE DELETE DROP TABLE INSERT

Defines the columns, index and other qualities of a table. Changes the default database. Deletes rows from a table. Removes a table and all of its rows from a database. Inserts rows into a table.

Teradata FastLoad Reference

1 ­ 17

Chapter 1: Introduction FastLoad Commands

See the SQL reference documentation for your operating system environment for a complete description of the CREATE TABLE, DATABASE, DELETE, and DROP TABLE statements. The FastLoad version of the INSERT statement includes a special "wildcard" table name specification that is not supported by the Teradata RDBMS. See "INSERT" on page 3-37 for a complete description of the FastLoad version of the INSERT statement.

1 ­ 18

Teradata FastLoad Reference

Chapter 1: Introduction A FastLoad Example

A FastLoad Example

This subsection provides an example of a small FastLoad job that you can quickly set up and run. The example shows you how to: · · · Create a data file that you will use as the input source for a FastLoad job Use a FastLoad job script to load data into a newly created table Select data from the table to verify the load task

Note: This example is for UNIX or Windows on a network-attached client system. Refer to the following appendixes for additional UNIX and Windows examples, and for VM and MVS examples on channel-attached systems: · · · Appendix Appendix B: "Multifile FastLoad Job Script Examples" Appendix Appendix C: "INMOD and Notify Exit Routine Examples" Appendix Appendix D: "Compiling, Linking, and Executing INMOD and Notify Exit Routines"

Dropping the Employee Table

The table in this example is named Employee. Use the Teradata SQL DROP TABLE command to delete any existing version of the Employee table from your database:

bteq .logon tdpid/username,password DROP TABLE employee; .quit

Creating the Source Data File

Create and save a six-record text file named insert.input:

|10021 |10001 |10002 |10028 |10029 |10023 |Brown, Jo |Jones, Bill |Smith, Jim |Lee, Sandra |Berg, Andy |Ayer, John |200|2312|Development |100|5376|President |100|4912|Sales |200|5844|Support |200|2312|Test |300|4432|Accounting |63000.00 |83000.00 |73000.00 |77000.00 |67000.00 |52000.00 |20|Jan |15|Jan |10|Jan | 4|Jan |10|Jan | 8|Jan 01 01 01 01 01 01 1955|F| 1960|M| 1970|M| 1971|F| 1967|M| 1965|M| |M|16| |M|14| |M|13| |M|18| |M|15| |M|13| 0| 0| 1| 0| 0| 0|

You use this file as the input source for a FastLoad job. Note: The file example uses field delimiter characters ( | ) to help you visualize each field--you need not use them in your file.

Teradata FastLoad Reference

1 ­ 19

Chapter 1: Introduction A FastLoad Example

Writing the FastLoad Job Script

Create and save a FastLoad job script file named flinsert.fastload that loads the six-record insert.data file into the Employee table:

sessions 2; errlimit 25; logon tdpid/username,password; CREATE TABLE employee ( EmpNo SMALLINT FORMAT `9(5)' BETWEEN 10001 AND 32001 NOT NULL, Name VARCHAR(12), DeptNo SMALLINT FORMAT `999' BETWEEN 100 AND 900 , PhoneNo SMALLINT FORMAT `9999' BETWEEN 1000 AND 9999, JobTitle VARCHAR(12), Salary DECIMAL(8,2) FORMAT `ZZZ,ZZ9.99' BETWEEN 1.00 AND 999000.00 , YrsExp BYTEINT FORMAT `Z9' BETWEEN -99 AND 99 , DOB DATE FORMAT `MMMbDDbYYYY', Sex CHAR(1) UPPERCASE, Race CHAR(1) UPPERCASE, MStat CHAR(1) UPPERCASE, EdLev BYTEINT FORMAT `Z9' BETWEEN 0 AND 22, HCap BYTEINT FORMAT `Z9' BETWEEN -99 AND 99 ) UNIQUE PRIMARY INDEX( EmpNo ) ; set record unformatted; define delim0(char(1)), EmpNo(char(9)), delim1(char(1)), Name(char(12)), delim2(char(1)), DeptNo(char(3)), delim3(char(1)), PhoneNo(char(4)), delim4(char(1)), JobTitle(char(12)), delim5(char(1)), Salary(char(9)), delim6(char(1)), YrsExp(char(2)), delim7(char(1)), DOB(char(11)), delim8(char(1)), Sex(char(1)), delim9(char(1)), Race(char(1)), delim10(char(1)), MStat(char(1)), delim11(char(1)), EdLev(char(2)), delim12(char(1)), HCap(char(2)), delim13(char(1)), newlinechar(char(1)) file=insert.input;

1 ­ 20

Teradata FastLoad Reference

Chapter 1: Introduction A FastLoad Example show; begin loading employee errorfiles error_1, error_2; insert into employee ( :EmpNo, :Name, :DeptNo, :PhoneNo, :JobTitle, :Salary, :YrsExp, :DOB, :Sex, :Race, :MStat, :EdLev, :HCap ); end loading; logoff;

Comments:

1 See Chapter 3 for syntax and descriptions of the following commands:

· SESSIONS · ERRLIMIT · LOGON · SET RECORD · DEFINE · SHOW · BEGIN LOADING · INSERT · END LOADING · LOGOFF

2

The CREATE TABLE statement creates a new table on the Teradata RDBMS: · Named employee · With thirteen columns:

· EmpNo · Name · DeptNo · PhoneNo · JobTitle · Salary · YrsExp · DOB · Sex · Race · MStat · EdLev · HCap

· Indexed by EmpNo (UNIQUE PRIMARY) See the SQL reference documentation for your operating system environment for a description of the Teradata SQL CREATE TABLE statement.

Teradata FastLoad Reference

1 ­ 21

Chapter 1: Introduction A FastLoad Example

3

The DEFINE command specifies each field of the data records that will be sent to the Teradata RDBMS. (The insert.input data file that you created in the "Creating the Source Data File" subsection.) Each field definition provides the name and data type description for each field in the input data records. In making the field declarations, note that the one character delimiter fields are optional. You need not use them in your example. The BEGIN LOADING command specifies the error files and starts the FastLoad job, using the insert.input file and the Employee table.

4

Running the FastLoad Job

Use the following command to invoke FastLoad and run the flinsert.fastload job script:

fastload < flinsert.fastload

Note that the redirection mechanism is required for FastLoad job scripts on network-attached client systems.

Verifying the Import Task

Use the following BTEQ commands to verify the FastLoad job by selecting newly loaded data from the Employee table:

bteq .logon tdpid/username,password .width 120 select * from employee where salary > 65000.00; .quit

The response indicates that the Employee table was successfully loaded with the data from the insert.input file:

*** Query completed. 4 rows found. 13 columns returned. *** Total elapsed time was 6 seconds. EmpNo Name DeptNo PhoneNo JobTitle Salary YrsExp DOB Sex Race MStat EdLev HCap ----- ------------ ------ ------- ------------ ---------- ------ ----------- --- ---- ----- ----- ---10028 Lee, Sandra 200 5844 Support 77,000.00 4 JAN 01 1971 F M 18 0 10001 Jones, Bill 100 5376 President 83,000.00 15 JAN 01 1960 M M 14 0 10002 Smith, Jim 100 4912 Sales 73,000.00 10 JAN 01 1970 M M 13 1 10029 Berg, Andy 200 2312 Test 67,000.00 10 JAN 01 1967 M M 15 0

1 ­ 22

Teradata FastLoad Reference

Chapter 1: Introduction A FastLoad Example

Other Alternatives

The FastLoad example is a simple, straightforward exercise that you can use to quickly create a job script and run FastLoad. The following information explains some of the more significant functional alternatives that FastLoad provides, and refers you to where they are described in greater detail.

Using Channel-Attached Client Systems

The FastLoad example assumes you are using UNIX or Windows on a networkattached client system. To run the example using VM or MVS on a channel-attached client system, you must use the standard VM EXEC or MVS JCL control statements (FILEDEF and DD) to allocate and create the FastLoad data sets or files before you invoke the utility. See "Invoking FastLoad" on page 2-2 for more information about invoking FastLoad on channel-attached client systems.

Using the MultiLoad Utility

The FastLoad example shows a very simple FastLoad job--loading a very small amount of data into an empty table. You could have used the MultiLoad utility to perform the same task, but the job would have run much slower--FastLoad works only on empty tables. You could use the MultiLoad utility to: · · · · · · Insert additional data rows into existing tables Update individual rows of existing tables Delete individual rows from existing tables Load data into multiple tables Load data into MULTISET tables Load data into tables with non-unique secondary indexes

Using Different Input File Formats

The format of the input data source for the FastLoad example (insert.input) is UNFORMATTED, as specified by the SET RECORD command. The FastLoad utility also supports input data source files with the following formats: · · · · FORMATTED BINARY TEXT VARTEXT

For descriptions of all the supported input file formats, see "SET RECORD" on page 3-64.

Teradata FastLoad Reference

1 ­ 23

Chapter 1: Introduction A FastLoad Example

Using INMOD Routines

In the FastLoad example, the utility reads the input data records directly from the specified source file (insert.input). An alternative would be to write an INMOD routine that FastLoad could call to obtain input records. The INMOD routine, for example, could: · · · · · Read and preprocess records from a file Generate data records Read data from other database systems Validate data records Convert data record fields

In this case, you would use the optional INMOD name specification of the DEFINE command to identify the name of your INMOD routine. For more information about using INMOD routines, see "Using INMOD and Notify Exit Routines" on page 2-30 and the INMOD name option in "DEFINE" on page 3-12.

1 ­ 24

Teradata FastLoad Reference

Chapter 2:

Using FastLoad

This chapter describes: · · · · · · · · Invoking FastLoad Terminating FastLoad Restarting a Paused FastLoad Job Programming Considerations Using INMOD and Notify Exit Routines Writing a FastLoad Job Script Running Multi-File FastLoad Jobs Handling FastLoad Errors

Teradata FastLoad Reference

2­1

Chapter 2: Using FastLoad Invoking FastLoad

Invoking FastLoad

File Requirements

In addition to the input data source, FastLoad accesses four different data sets/files or input/output devices:

This data set/file or device... Provides the...

standard input standard output standard error configuration

FastLoad commands and Teradata SQL statements that make up your FastLoad job. destination for FastLoad output responses and messages. destination for FastLoad error messages. optional specification of FastLoad utility default values.

When running FastLoad in interactive mode, your terminal keyboard functions as the standard input device and your display screen is the standard output/error device. When running FastLoad in batch mode, you must specify a data set or file name for each of these functions. The method of doing this varies, depending on the configuration of your client system: · · On network-attached client systems, use the standard redirection mechanism to specify the FastLoad files when you invoke the utility. On channel-attached client systems, use standard MVS JCL or VM EXEC control commands to allocate and create the FastLoad data sets or files before you invoke the utility.

In Interactive Mode

To invoke FastLoad in interactive mode, enter fastload at your system command prompt:

fastload

The FastLoad utility displays the following message to begin your interactive session:

======================================================= = = = FASTLOAD UTILITY VERSION mm.mm.mm = = = =======================================================

where mm.mm.mm is the release level of your FastLoad utility software.

2­2

Teradata FastLoad Reference

Chapter 2: Using FastLoad Invoking FastLoad

In Batch Mode on Network-Attached Client Systems

Refer to the run time parameter descriptions in Table 2-1 and use the following syntax to invoke FastLoad in batch mode on network-attached client systems:

fastload 8 -b kilobytes -c character-set-name -e filename -M max-sessions -N min-sessions -s minutes -t hours -v

2411A007

< infilename

> outfilename

Note: See "Syntax Diagram Conventions" on page A-2 for a description of how to read the syntax diagrams used in this book.

In Batch Mode on Channel-Attached MVS Client Systems

Refer to the runtime parameter descriptions in Table 2-1 and use the following syntax to invoke FastLoad in batch mode on channel-attached MVS client systems:

// EXEC TDSFAST , ,FDLOPT= ' BUFSIZE=kilobytes CHARSET=character-set-name ERRLOG=filename INMODTYPE=SAS_C MAXSESS=max-sessions MINSESS=min-sessions SLEEP=minutes TENACITY=hours VERBOSE

2411A008

9 '

Teradata FastLoad Reference

2­3

Chapter 2: Using FastLoad Invoking FastLoad

In Batch Mode on Channel-Attached VM Client Systems

Refer to the runtime parameter descriptions in Table 2-1 and use the following syntax to invoke FastLoad in batch mode on channel-attached VM client systems:

EXEC FAST 9 BUFSIZE=kilobytes CHARSET=character-set-name ERRLOG=filename INMODTYPE=SAS_C MAXSESS=max-sessions MINSESS=min-sessions SLEEP=minutes TENACITY=hours VERBOSE

2411A009

Note: On VM, you must use the following statement before the EXEC FAST statement:

"GLOBAL LOADLIB DYNAMC"

FastLoad Runtime Parameters

The table below describes the runtime parameters used by FastLoad.

Table 2-1 Runtime Parameter Descriptions

Runtime parameter... on... systems Channel-attached Network-attached Is the...

BUFSIZE= kilobytes

-b kilobytes

output buffer size specification, where kilobytes is the size of the output buffer, in kilobytes, that will be used for FastLoad messages to the Teradata RDBMS. The output buffer size and the size of the rows in the FastLoad table determine the maximum number of rows that can be included in each message to the Teradata RDBMS. A larger buffer size reduces processing overhead by including more data in each message. The default buffer size is also the maximum size allowed--63K bytes. If you specify a value greater than 63K bytes, FastLoad: · · · Responds with a warning message Resets the buffer size back to the default value Continues with the FastLoad job

2­4

Teradata FastLoad Reference

Chapter 2: Using FastLoad Invoking FastLoad Table 2-1 (Continued) Runtime Parameter Descriptions

Runtime parameter... on... systems Channel-attached Network-attached Is the...

CHARSET= character-setname

-c characterset-name

character set specification for the FastLoad job. You can specify either a character set name or its code. For channel-attached client systems, specify:

Character Set Name Code

EBCDIC SDHANGULEBCDIC933_5II SDSCHEBCDIC935_6IJ SDTCHEBCDIC937_7IB HANGULEBCDIC933_1II SCHEBCDIC935_21J TCHEBCDIC937_3IB KATAKANAEBCDIC KANJIEBCDIC5026_0I KANJIEBCDIC5035_0I SDKATAKANAEBCDIC_4IF SDKANJIEBDIC5026_4IG SDKANJIEBCDIC5035_4IH For network-attached client systems:

Character Set Name

64 or 192 74 75 76 108 109 110 111 or 239 112 or 240 113 or 241 77 78 79

Code

SDKANJIEUC_1U3 SDKANJISJIS_1S3 SDHANGULKSC5601_4R4 SDTCHGB2312_2T0 SDTCHBIG5_3R0 KANJIEUC_0U KANJISJIS_0S

91 92 93 94 95 118 119

Teradata FastLoad Reference

2­5

Chapter 2: Using FastLoad Invoking FastLoad Table 2-1 (Continued) Runtime Parameter Descriptions

Runtime parameter... on... systems Channel-attached Network-attached Is the...

CHARSET= character-setname (continued)

-c characterset-name (continued)

HANGULKSC5601_2R4

120

SCHGB2312_1T0 TCHBIG5_1R0 ASCII

121 122 255

Your character set specification remains in effect for the entire FastLoad job, even if the Teradata RDBMS server resets, causing the FastLoad job to be restarted. Caution: The character set specification does not remain in effect if the client system fails, or if you cancel the FastLoad job. In these cases, when you resubmit the job, you must use the same character set specification that you used on the initial job. If you use a different character set specification when you resubmit such a job, the data loaded by the restarted job will not appear the same as the data loaded by the initial job. When you specify a particular character set, you can then specify the identifiers and data in that character set, thus affecting how the data is loaded into the FastLoad table. If you do not enter a character set specification, the default is whatever character set that is specified for the Teradata RDBMS whenever you invoke FastLoad. Note: The order in which the character set is determined for a FastLoad job is as follows:

1 2 3

ERRORLOG= filename -e filename

User specified by either a runtime parameter on channel-attached systems or a SET SESSION CHARSET command on network-attached systems System Parameter Block (SPB) specified by either the HSHSPB on channelattached systems or the clispb.dat file on network-attached systems Teradata RDBMS default, determined by a query from FastLoad

alternate file specification for FastLoad error messages. Specifying an alternate file name produces a duplicate record of all FastLoad error messages. On channel-attached client systems, the alternate file specification is limited to eight characters and: · · On VM, it must be to an existing file definition (FILEDEF) On MVS, it must be to a DD name defined in the JCL

There is no default filename specification.

2­6

Teradata FastLoad Reference

Chapter 2: Using FastLoad Invoking FastLoad Table 2-1 (Continued) Runtime Parameter Descriptions

Runtime parameter... on... systems Channel-attached Network-attached Is the...

ERRORLOG= filename (continued)

< infilename

name of the standard input file that contains your FastLoad commands and Teradata SQL statements on network-attached client systems. Your infilename specification redirects the standard input (stdin). If you do not enter an infilename specification, the default is stdin. Note: On channel-attached client systems, you must use the SYSIN control command to specify the input file before you invoke the utility.

> outfilename

name of the standard output file for FastLoad messages on network-attached systems. Your outfilename specification redirects the standard output (stdout). If you do not enter an outfilename specification, the default is stdout. CAUTION: If you use an outfilename specification to redirect stdout, do not use the same outfilename as an output or echo destination in the ROUTE MESSAGES command. Doing so produces incomplete results because of the conflicting write operations to the same file. Note: On channel-attached client systems, you must use the SYSPRINT control command to specify the output file before you invoke the utility.

INMODTYPE = SAS_C

specification on channel-attached systems that your FastLoad job will use an INMOD routine written in the SAS/C programming language. Your FastLoad job will fail if you do not specify this option when using a SAS/C INMOD routine. -M maxsessions maximum number of FastLoad sessions logged on to the Teradata RDBMS. The maximum specification must be greater than zero and no more than the total number of AMPs on your system. The default is one session for each AMP. minimum number of FastLoad sessions required to run the job. The minimum specification must be greater than zero and it must be less than the value given for MAXSESS. The default is 1. specification of the sleep option in which minutes is the number of minutes that FastLoad pauses before retrying the logon operation. See "SLEEP" on page 3-76 for more information. The FastLoad default value is 6 minutes.

MAXSESS = max-sessions

MINSESS = min-sessions SLEEP= minutes

-N minsessions -s minutes

Teradata FastLoad Reference

2­7

Chapter 2: Using FastLoad Invoking FastLoad Table 2-1 (Continued) Runtime Parameter Descriptions

Runtime parameter... on... systems Channel-attached Network-attached Is the...

TENACITY= hours

-t hours

specification of the tenacity option in which hours is the number of hours that FastLoad continues trying to log on when the maximum number of load jobs are already running on the Teradata RDBMS. See "TENACITY" on page 3-78 for more information. The FastLoad default is no tenacity. You must use either a FastLoad configuration file entry, the runtime parameter, or a TENACITY command in your FastLoad job script to enable the tenacity feature for your FastLoad logon operation.

VERBOSE

-v

lets you print out every request sent to the RDBMS.

VM Example

Following is an example VM program that invokes FastLoad:

GLOBAL LOADLIB DYNAMC FILEDEF DATA01 DISK FAST DATA A /*<=input data file FILEDEF SYSIN DISK FAST CNTL A /*<=command control file FILEDEF SYSTERM DISK FAST SYSTERM A FILEDEF SYSPRINT DISK FAST SYSPRINT A(LRECL 137 RECFM VBA /*<=joblog EXEC FAST PRINT FAST SYSPRINT A /*<=prints joblog exit

*/ */ */ */

where:

The standard... file Is...

input output error

FILEDEF SYSIN DISK fn ft A FILEDEF SYSPRINT DISK fn ft A FILEDEF SYSTERM DISK fn ft A

VM Procedure

Following is a sample VM procedure provided on the release tape with the FastLoad software. Use this procedure to meet your specific site requirements and invoke FastLoad on a channel-attached VM client system.

/*************************************************************************/ /* Teradata Database System */ /* */ /* SAMPLE DRIVER TO INVOKE */ /* FASTLOAD/VMSP */

2­8

Teradata FastLoad Reference

Chapter 2: Using FastLoad Invoking FastLoad /* */ /* History 850201 LHH ORIGINAL IMPLEMENTATION */ /* 850306 SKP ADDED COMMENTS */ /* 850422 LHH Changed SYSPRINT */ /* for LRECL 137 RECFM VBA */ /* */ /* USER MUST ENSURE THAT FILEDEFS FILES */ /* ARE PROPERLY LINKED */ /* */ /* FASTXQT first sets up the FILEDEF's */ /* for FASTLOAD then executes the FAST EXEC */ /* */ /* DATA01 -defines the input data file */ /* used by FASTLOAD and as specified */ /* within SYSIN 'DEFINE' statement */ /* DDNAME = */ /* SYSIN -defines the DBC command input file */ /* used by FASTLOAD */ /* SYSPRINT -defines the job log output file */ /* <= Identifies lines that USER */ /* should change to job specific needs */ /*************************************************************************/ "GLOBAL LOADLIB DYNAMC" "FILEDEF DATA01 DISK FAST DATA A" /*<=input data file*/ "FILEDEF SYSIN DISK FAST CNTL A" /*<=command control file*/ "FILEDEF SYSTERM DISK FAST SYSTERMA" "FILEDEF SYSPRINT DISK FAST SYSPRINT A (LRECL 137 RECFM VBA" /*<=joblog*/ "EXEC FAST" "PRINT FAST SYSPRINT A" /*<=prints joblog*/ exit /*************************************************************************/ /* EXAMPLE of defining tape as the input data file */ /* - modify VOLID, LRECL, and BLKSIZE */ /* to correspond with a specific tape */ /* */ /* "FILEDEF DATA01 TAP1 SL VOLID */ /* 410489 (RECFM FB LRECL 170 BLKSIZE 06800" */ /*************************************************************************/

MVS Example

Following is an example MVS program that invokes FastLoad:

//FASTLOAD EXEC TDSFAST,INFILE='<input dataset>' //FAST.SYSIN DD DATA,DLM=$$ <FastLoad Control Statements> $$

Teradata FastLoad Reference

2­9

Chapter 2: Using FastLoad Invoking FastLoad

where the ddname SYSIN is the input data set that contains the FastLoad job script.

MVS Procedure

Following is a sample MVS procedure that is provided on the release tape with FastLoad software. You can change this procedure to meet your specific site requirements and use it to invoke FastLoad on a channel-attached MVS client system.

//TDSFAST PROC FDLOPT=,PFX1='DBC',PFX2='DBC', //INFILE='NULLFILE' //***************************************** //* THIS PROCEDURE EXECUTES * //* THE FASTLOAD PROGRAM * //* * //* YOU CAN COPY THIS PROCEDURE * //* INTO YOUR SYSTEM PROCLIB * //* FOR ALL DBC USERS GENERAL USAGE. * //* * //* EXAMPLE EXECUTION: * //* //FAST EXEC TDSFAST * //* //FAST.SYSIN DD DATA,DLM=$$ * //* LOGON .....; * //* LOGOFF; * //* $$ * //* * //* DDNAMES USED: * //* SYSPRINT - PRINTED OUTPUT. * //* SYSIN - INPUT DATA SET * //* CONTAINING FASTLOAD * //* STATEMENTS. * //* INFILE - INPUT DATA SET * //* CONTAINING DATA. * //*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* //* TAILOR THE FOLLOWING FOR YOUR * //* SPECIFIC ENVIRONMENT: * //* SYMBOLIC USE * //* ======== === * //* PFX1 = HIGH LEVEL QUALIFIER * //* FOR APPLOAD LIBRARY. * //* FPX2 = HIGH LEVEL QUALIFIER * //* FOR SAS/C RUNTIME LIBRRARY.* //* FDLOPT = FASTLOAD PARAMETER INPUT. * //* INFILE = DSNAME OF INPUT DATASET. * //* * //*---------------------------------------* //* * //* -DATE-- III DR/DCR- CHNG#--COMMENTS-- *

2 ­ 10

Teradata FastLoad Reference

Chapter 2: Using FastLoad Invoking FastLoad //* * //* 23JAN96 MXM CREATED FOR C * //* VERSION OF * //* FASTLOAD * //***************************************** //FASTLOAD EXEC PGM=FASTLOAD, // PARM='&FDLOPT', REGION=4096K //STEPLIB DD DSN=&PFX1..APPLOAD,DISP=SHR // DD DSN-&PFX2..TRLOAD,DISP=SHR //SYSPRINT DD SYSOUT=* //SYSABEND DD SYSOUT=* //SYSTERM DD SYSOUT=* //INFILE DD DSN=&INFILE,DISP=SHR

UNIX and Windows Examples

Following are examples of three ways to invoke FastLoad on network-attached client systems: ·

fastload </home/fluser/tests/test1 >/home/fluser/tests/out1

This command specifies both an input file and an output file: · /home/fluser/tests/test1 is the input file that provides the FastLoad job script. · /home/fluser/tests/out1 is the destination file for output data. ·

fastload </home/fluser/tests/test1

This command specifies only an input file. In this case, the output is written to the standard output device, which is usually your terminal. ·

fastload

This command specifies neither an input nor an output device. In this case, your terminal provides both the command input and the output data destination.

Teradata FastLoad Reference

2 ­ 11

Chapter 2: Using FastLoad Terminating FastLoad

Terminating FastLoad

Methods

There are two ways to terminate FastLoad, depending on the operational situation: · · Normal Termination Abort Termination

Either way ends all of your FastLoad sessions and logs you off the Teradata RDBMS. A normal termination, however, does so in an orderly, controlled fashion, and returns messages indicating the status of the FastLoad job, and whether the utility was paused or terminated. An abort termination does not.

Normal Termination

Use the LOGOFF or QUIT command in your FastLoad batch job script or interactive session to terminate FastLoad normally on both network and channel-attached client systems:

LOGOFF QUIT

;

GK01A013

The FastLoad utility logs off all sessions with the Teradata RDBMS and returns a status message indicating: · · · The total processor time that was used The job start and stop date/time The highest return code that was encountered: · 0 if the job completed normally · 4 if a warning condition occurred · 8 if a user error occurred · 12 if a fatal error occurred Whether the utility terminated or paused

·

See "LOGOFF" on page 3-41 or "QUIT" on page 3-56 for more information about return codes and the conditions that terminate or pause FastLoad.

2 ­ 12

Teradata FastLoad Reference

Chapter 2: Using FastLoad Terminating FastLoad

Abort Termination

The procedure for aborting a FastLoad job depends on whether the utility is running on a network-attached or channel-attached client system.

IF you want to abort a FastLoad job running on a client system that is...

THEN...

network attached channel attached

press the Control + C key combination three times on your workstation keyboard. cancel the job from the client system console.

Teradata FastLoad Reference

2 ­ 13

Chapter 2: Using FastLoad Restarting a Paused FastLoad Job

Restarting a Paused FastLoad Job

Paused Fastload Jobs

A paused FastLoad job is one that was halted, before completing, during the loading or end loading phase of the FastLoad operation. The paused condition can be intentional, or the result of a system failure or error condition. You can pause a FastLoad job intentionally by using the LOGOFF or QUIT command before the END LOADING command in your FastLoad job script, as when running a multi-file FastLoad job. Unintentional conditions that can pause a FastLoad job include: · · · · · Client system or FastLoad failures Unrecoverable error conditions Database or table overfills Teradata RDBMS failures AP reset conditions

When a FastLoad job is in the paused state, the FastLoad target table and the two error tables on the Teradata RDBMS are locked. You can access the two error tables by using a locking modifier, such as:

locking error_table_name for access select errorcode, errorfieldname from error_table_name;

To access the FastLoad target table, you must submit a FastLoad job consisting of a BEGIN LOADING and an END LOADING command. This effectively restarts and ends the job. After executing the END LOADING command, you can access the FastLoad target table, but you cannot restart the original job. The following subsections describe the various pause conditions, the factors affecting FastLoad restart operations, and the procedure for restarting a paused FastLoad job.

After a Client System or FastLoad Failure

When a client system or a FastLoad failure occurs while a FastLoad job is running, FastLoad: 1 2 Pauses the job. Tries to log off all FastLoad sessions on the Teradata RDBMS.

2 ­ 14

Teradata FastLoad Reference

Chapter 2: Using FastLoad Restarting a Paused FastLoad Job

After a Database Overfill Condition

When your FastLoad job tries to load more data into a table than the table or the database that it is in can hold, FastLoad pauses the job and returns the following message:

RDBMS error 2644: No more room in database <uid> Increase database size and restart FastLoad

In this case, after increasing the amount of space allocated to the database, you can restart the FastLoad job at the point where the reported out of space condition occurred.

After a Teradata RDBMS Failure

Restarting a FastLoad job that was paused because of a Teradata RDBMS failure depends on the operational configuration of the Teradata RDBMS when it returns to service:

IF the configuration of the restarted Teradata RDBMS is...

THEN FastLoad...

EXACTLY the same as it was when you invoked FastLoad

restarts the job automatically. In this case, if the FastLoad job was paused in the end loading phase, the Teradata RDBMS resumes processing at the same place it was stopped. If the FastLoad job was paused in the loading phase the Teradata RDBMS resumes processing: · At the last checkpoint if the BEGIN LOADING command specified the checkpoint option. · At the beginning if the BEGIN LOADING command did not specify the checkpoint option. Note: If the FastLoad job uses an INMOD routine, the routine must be able to handle restarts and checkpoints when restarted in the loading phase.

different from the way it was when you invoked FastLoad

does not restart the job. In this case, to restart and continue with the paused FastLoad job, you must reestablish the original configuration of the Teradata RDBMS. If this is not possible, then you must:

1 2

Delete the FastLoad table and error tables. Resubmit the FastLoad job, from the beginning, as a new job.

Teradata FastLoad Reference

2 ­ 15

Chapter 2: Using FastLoad Restarting a Paused FastLoad Job

After an AP Reset Condition

When your FastLoad job is interrupted by a resetting AP on the Teradata RDBMS, the restart alternatives depend on the environment in which FastLoad is running: · · · On the resetting AP On a nonresetting AP On a network-attached client system

Note: For the Teradata DBS for TOS, the FastLoad restart alternatives for an AP reset condition apply only to Version 1 Release 5.2 and higher.

IF an AP reset condition occurs and FastLoad is running on a...

THEN your FastLoad job is...

resetting AP nonresetting AP

This situation is similar to FastLoad jobs that are paused after a client system or a FastLoad failure. not paused, and does not need to be manually restarted. The FastLoad reaction to the AP reset condition varies, depending on whether your FastLoad jobs has sessions connected through the resetting AP. If your FastLoad job has sessions connected through a resetting AP, FastLoad automatically:

1 2 3 4

Logs off all sessions. Logs them back on. Rolls back to the most recent checkpoint. Resumes processing.

If your FastLoad job does not have sessions connected through a resetting AP, FastLoad is not affected by the resetting AP. The increased session loading caused by the reconnection of other sessions through the resetting AP may degrade the system response time. network-attached client system paused, but does not need to be manually restarted. The FastLoad reaction to the AP reset condition varies, depending on whether your FastLoad jobs has sessions connected through the resetting AP.

2 ­ 16

Teradata FastLoad Reference

Chapter 2: Using FastLoad Restarting a Paused FastLoad Job

IF an AP reset condition occurs and FastLoad is running on a...

THEN your FastLoad job is...

network-attached client system (continued)

If your FastLoad job has sessions connected through a resetting AP, FastLoad automatically:

1 2 3 4

Logs off all sessions. Logs them back on. Rolls back to the most recent checkpoint. Resumes processing.

If your FastLoad job does not have sessions connected through a resetting AP, FastLoad is not affected by the resetting AP. The increased session loading caused by the reconnection of other sessions through the resetting AP may degrade the system response time. This situation is similar to an RDBMS restart or network failure.

After an Unrecoverable Error Condition

When an unrecoverable error condition occurs while a FastLoad job is processing the BEGIN LOADING command, but before completing the end loading process, FastLoad: 1 2 3 Pauses the job. Logs off all FastLoad sessions on the Teradata RDBMS. Writes the FastLoad PAUSED message to the standard output device, along with the error code associated with the error.

Note: The FastLoad utility terminates if an unrecoverable error condition occurs before it begins processing the BEGIN LOADING command, or after it has completed processing the END LOADING command.

Teradata FastLoad Reference

2 ­ 17

Chapter 2: Using FastLoad Restarting a Paused FastLoad Job

Factors Affecting a Restart

The procedure that you use and the FastLoad response to restarting a paused FastLoad job depends on: · · · The phase that the FastLoad job was in when it was paused Whether the checkpoint option was specified in the BEGIN LOADING command Whether your FastLoad job included an INMOD routine

You can restart a job that was paused during this phase...

From...

loading

either the beginning, or from the most recent checkpoint if the BEGIN LOADING command specified the checkpoint option. wherever it was interrupted, because the Teradata RDBMS uses internal checkpointing during this phase.

end loading

Restart Procedure

Regardless of the cause, use the following procedure to restart a paused FastLoad job:

IF the FastLoad job was paused during the...

THEN to restart the job...

loading phase

first, remove the CREATE TABLE statement and any DROP TABLE and DELETE statements from the FastLoad job script. You do not want your restarted job to drop the partially loaded FastLoad table or delete the entries in the two error tables. Then invoke FastLoad to start the job. The FastLoad utility:

1 2 3

Establishes new sessions using your LOGON command Reads the restart log to determine the restart point In response to the BEGIN LOADING command, indicates that the job is being restarted.

If, for example, the job had a checkpoint specification of 100, and the failure occurred at row 1100, the FastLoad response would be: FastLoad RESTARTED The last checkpoint was taken at FastLoad will now restart at row: 1100 row: 1101

2 ­ 18

Teradata FastLoad Reference

Chapter 2: Using FastLoad Restarting a Paused FastLoad Job

IF the FastLoad job was paused during the...

THEN to restart the job...

loading phase and uses an INMOD routine

the INMOD routine must be able to handle restarts and checkpoints when restarted in the loading phase. Following a restart, FastLoad passes a status code of 2 or 4 to an INMOD routine that is participating in a load operation. The routine must adjust the record to be read to the position of the last checkpoint and, upon subsequent calls, send records to FastLoad.

end loading phase

you generally do not need to do anything because processing after the END LOADING command is executed on the Teradata RDBMS is not dependent on FastLoad. If, however, you do need to restart the job in the end loading phase, use the same LOGON command and then submit the BEGIN LOADING and END LOADING commands, as in the following example: LOGON dbc/sjn,music ; BEGIN LOADING Fast_Table ERRORFILES Error_1, Error_2 ; END LOADING ; Note: If you use your FastLoad job script to assemble these commands, make sure you delete the CREATE TABLE and any DROP TABLE and DELETE statements before restarting the job.

Teradata FastLoad Reference

2 ­ 19

Chapter 2: Using FastLoad Programming Considerations

Programming Considerations

This section describes the things you should consider when designing and coding your FastLoad job script: · · · · · · · · · · · · · Using a FastLoad configuration file Using ANSI/SQL DateTime data type specifications Checkpoint tradeoffs Comments Running concurrent load utility tasks Data conversion factors Specifying error limits Foreign key references Using index sorts Join Index Restrictions Range constraints Factors that limit sessions Space requirements and limitations on the Teradata RDBMS

The FastLoad Configuration File

You can create a FastLoad configuration file to set the initial default values for the following operating parameters when you invoke FastLoad: · · · · · · · TENACITY SLEEP BUFSIZE CHARSET INMODRETURN MAXSESS MINSESS

The values that you specify in the FastLoad configuration file override the internal utility default values for these parameters. The configuration file parameters themselves can be overridden by the TENACITY, SLEEP, SESSION, and SET SESSION CHARSET commands in your FastLoad job script, and by the corresponding runtime parameters, as shown in Table 2-1. The order of preference for these parameters, from highest to lowest, is: 1--Runtime parameters 2--FastLoad script commands 3--FastLoad configuration file specifications 4--FastLoad default values

2 ­ 20

Teradata FastLoad Reference

Chapter 2: Using FastLoad Programming Considerations

Note that the utility default for TENACITY is no tenacity. You must use either a configuration file entry, the runtime parameter or the TENACITY command in your FastLoad job script to enable the tenacity feature for your FastLoad logon operation.

File Name and Location

On network-attached systems, the FastLoad configuration file must be named:

floadcfg.dat

And it must be located in either: · · The current directory for your FastLoad utility software The directory specified in the FLOADLIB environment variable

On channel-attached systems, the DD statement for the FastLoad configuration file must be labeled:

FLOADCFG

File Contents

Your FastLoad configuration file can have up to seven entries, one for each parameter:

TENACITY=hours SLEEP=minutes BUFSIZE=kilobytes CHARSET=character-set-name INMODRETURN=ON or YES MAXSESS=max-sessions MINSESS=min-sessions

where: · hours is the TENACITY specification of the number of hours that FastLoad continues trying to log on when the maximum number of load jobs are already running on the Teradata RDBMS. See "TENACITY" on page 3-78 to learn about this specification. minutes is the SLEEP specification of the number of minutes that FastLoad pauses between attempts to log on. See"SLEEP" on page 3-76 for more information. kilobytes is the size of the output buffer, in kilobytes, to be used for FastLoad messages to the Teradata RDBMS. See the BUFSIZE/-b runtime parameter topic in Table 2-1 for a description of the BUFSIZE specification. character-set-name is the character set specification for the FastLoad job. See the CHARSET/-c runtime parameter topic in Table 2-1 and "SET SESSION CHARSET" on page 3-70 to learn about this specification.

·

·

·

Teradata FastLoad Reference

2 ­ 21

Chapter 2: Using FastLoad Programming Considerations

·

·

·

ON or YES specifies that INMOD return codes are to be checked and returned. The informational message INMOD return codes will be checked displays. Nonzero return codes force FastLoad to terminate. max-sessions is the MAXSESS specification for the maximum number of FastLoad sessions logged on to the Teradata RDBMS. See "SESSIONS" on page 3-61 to find out about this specification. min-sessions is the MINSESS specification for the minimum number of FastLoad sessions required to run the job. See "SESSIONS" on page 3-61 to learn about this specification.

Your FastLoad configuration file can also have comment statements preceded by a # character.

File Processing

The FastLoad utility automatically checks for a configuration file each time you enter the invocation command. Upon locating a configuration file, the utility sets the defaults as specified, produces the appropriate output messages and begins processing your FastLoad job. If the configuration file cannot be opened, or if FastLoad encounters syntax errors in the file, the utility produces an output message, disregards the error condition and begins processing your FastLoad job. An invalid configuration file entry does not abort your FastLoad job. If there is no configuration file, the utility begins processing your FastLoad job without an error indication. The configuration file is an optional feature of FastLoad, and its absence is not considered to be an error condition.

Character Set Specification

When an AXSMOD is used, FastLoad will pass the session character set as an attribute to the AXSMOD for its possible use (most AXSMODs will not make any use of this information). The information will be passed as follows:

If the session character set was specified by... then the attribute name will be...

name id

CHARSET_NAME CHARSET_NUMBER

The attribute value will be a variable length character string consisting of either the character set name, or the character representation of the character set id, as appropriate.

ANSI/SQL DateTime Specifications

You can use the ANSI/SQL DATE, TIME, TIMESTAMP and INTERVAL DateTime data types in Teradata SQL CREATE TABLE statements, and you can

2 ­ 22

Teradata FastLoad Reference

Chapter 2: Using FastLoad Programming Considerations

specify them as column/field modifiers in INSERT statements. However, certain restrictions should be noted: · · You cannot use ANSI/SQL DateTime data types when specifying the column/field names in a DEFINE command. You cannot specify format for TIME, TIMESTAMP, and INTERVAL DateTime data types in a DML (data manipulation) statement (such as UPDATE, DELETE, INSERT, etc.). For example, this usage:

insert into t1 (col1, col2) values (:c1 (format 'hhmmss'), :c2);

·

will give you an invalid operation on ANSI/SQL DATE, TIME, TIMESTAMP, and INTERVAL DateTime data types. You must convert ANSI/SQL DateTime data types to fixed-length CHAR data types when specifying the column/field names in the DEFINE command. See "Usage Notes" on page 3-14 for a description of the fixed-length CHAR representations for each DATE, TIME, TIMESTAMP and INTERVAL data type specification.

Checkpoint Tradeoffs

Though the checkpoint feature substantially enhances FastLoad restart operations, it also reduces the speed of your FastLoad job. Always consider the following factors when deciding how often to specify checkpoints: · · · Each checkpoint temporarily halts the multiple session data transfer feature of FastLoad, thereby decreasing the speed of the FastLoad job. For each checkpoint, FastLoad waits for each session to complete sending its current request, which interrupts the data transfer operation. The record size and the size of the Teradata RDBMS influence how often you should specify checkpoints. On a smaller Teradata RDBMS, specify checkpoints: · Every 50,000 records if each record is more then 4 Kb · Every 100,000 records if each record is less than 4 Kb On a larger Teradata RDBMS, specify higher (less frequent) checkpoint values.

Teradata FastLoad Reference

2 ­ 23

Chapter 2: Using FastLoad Programming Considerations

Comments

The FastLoad utility supports C language style comments, as described in the following table:

Comment Topic Description

Beginning and Ending Delimiters Comment Destinations Invalid Within String or Character Literals Nested Comments

A comment begins with a slash star ( /* ) character sequence and ends with a star slash ( */ ) sequence. All intervening text is treated as a comment. Comments are always written to the message destination, and they may or may not be sent to the Teradata RDBMS. Comments cannot occur within string or character literals--a /* within a quoted string is not treated as the beginning of a comment. The FastLoad utility does not support nested comments.

Concurrent Load Utility Tasks

The maximum number of concurrent FastLoad tasks that can run is: · · 5 on the Teradata DBS for TOS and on the Teradata RDBMS for UNIX Version 2, Release 1.1.2 and below Variable, from 0 to 15 on the Teradata RDBMS for UNIX Version 2, Release 2.0 and above, Teradata for Windows 2000, and Teradata for Windows NT. The default limit is 5. Note: The variable limit can be controlled by the system administrator. See the description of the MaxLoadTask parameter of the DBSCONTROL record in the Teradata database design and administration and support utilities reference documentation for your operating system environment. If your FastLoad job exceeds these limits, the Teradata RDBMS returns a 2633 error message (too many loads running), and the utility retries until: · · It can execute the task It reaches the TENACITY hours runtime specification

2 ­ 24

Teradata FastLoad Reference

Chapter 2: Using FastLoad Programming Considerations

Data Conversion Factors

When using the DEFINE command to change the data type specification of source data before inserting it into the FastLoad table on the Teradata RDBMS, you are limited to one type conversion per column. Note: You cannot use FastLoad to define a column with an arithmetic expression. For example, FastLoad will not calculate a monthly salary column from yearly salary data. For details on data types and data conversions, see Teradata RDBMS SQL Reference (B035-1101-061A).

Valid Data Conversion Example

The following valid example converts character data to integer data, assuming that column b is of type INTEGER:

DEFINE a (char(10)) file= . . . ; INSERT INTO table1 (b) VALUES (:a(integer)) ;

Valid Redundant Conversion Example

The following valid example converts data in zoned decimal format to type decimal format, assuming that column d is of type DECIMAL (9,2):

DEFINE b (char(9)) file= . . . ; INSERT INTO Table1 (d) VALUES (:b (decimal(9,2)));

Note: You can also use redundant conversions, as in this example, to remind yourself that a data conversion is taking place.

Invalid Data Conversion Example

The following data conversion example is invalid because it requests two conversions--from character to decimal, and then from decimal to integer, assuming that column b is of type INTEGER:

DEFINE a(char(10)) file= . . . ; INSERT INTO table1 (b) VALUES (:a (decimal(5,2))) ;

Teradata FastLoad Reference

2 ­ 25

Chapter 2: Using FastLoad Programming Considerations

Error Limits

Consider the number and type of errors you expect from your FastLoad job when specifying the ERRLIMIT value.

IF you expect your FastLoad job to encounter...

THEN specify an ERRLIMIT value that is...

no errors or very few errors many errors that are considered allowable

low. high.

Foreign Key References

The FastLoad utility does not support foreign key references in target tables. Attempting a FastLoad task or any action against a target table defined with a foreign key constraint produces an error condition.

Secondary Indexes

The FastLoad utility does not support target tables defined with secondary indexes. Attempting a FastLoad task against a target table defined with secondary indexes produces an error condition. To load such a table, you must first drop the secondary indexes. Then you can load the table and recreate the secondary indexes. Or, alternatively, if only non-unique secondary indexes are involved, consider using the MultiLoad utility.

Non-unique Index Sorts

If you define a FastLoad table with a non-unique primary index, you can enhance the performance of the FastLoad job by not using the index value to sort the input data.

Duplicate Rows

The FastLoad utility does not load duplicate rows, as in MULTISET tables. If you use FastLoad to load a target table defined as MULTISET, the utility will discard any duplicate rows. If you must load duplicate rows, consider using the MultiLoad utility.

Join Index Restrictions

The FastLoad utility does not maintain Join Indexes. You cannot use FastLoad to load data to tables with an associated Join Index on a Teradata RDBMS for UNIX, V2R2.1 database. In this case, you must first drop the Join Index, then recreate it after running the FastLoad job.

2 ­ 26

Teradata FastLoad Reference

Chapter 2: Using FastLoad Programming Considerations

Range Constraints

Range constraints are data description phrases that you enter in the Teradata SQL CREATE TABLE statement that limit the range of acceptable values for a column. Since the range constraint checks occur while FastLoad inserts data into the FastLoad table, the number of range constraints in your FastLoad job script has a direct impact on the performance of FastLoad.

Range Constraint Types

There are two types of range constraints, explicit and implicit:

Constraint Type Example

Explicit Implicit

The Salary column range of between 1 and 99000.00, as shown in the following CREATE TABLE example. The DeptNo column range of ZZ9, as shown in the following CREATE TABLE example.

Range Constraint Examples

The following Teradata SQL CREATE TABLE statement shows the two types of range constraint phrases for the Salary and DeptNo columns:

CREATE TABLE Employee (EmpNo INTEGER FORMAT `ZZZZ9', Name VARCHAR (12) CASESPECIFIC TITLE `Employee//Name', DeptNo INTEGER FORMAT `zz9' TITLE `Dept#', Salary DECIMAL (7,2) BETWEEN 1 AND 99000.00 FORMAT `ZZ,ZZ9.99') UNIQUE PRIMARY INDEX (EmpNo);

Before inserting each row in the Employee table, FastLoad checks to verify that the value for: · · Salary is in the range of 1 to 99000.00 DeptNo is between -999 and 999

If you know, for example, that the values for the DeptNo column are always in the range of -999 to 999, then you can improve the performance of the FastLoad job by removing the ZZ9 phrase from the CREATE TABLE statement in your FastLoad job script.

Teradata FastLoad Reference

2 ­ 27

Chapter 2: Using FastLoad Programming Considerations

Record Mode Load Anomaly

When loading data in Record Mode into a NULLABLE DATE field, if the source data is a binary integer of value zero, then the Teradata RDBMS sets the field to NULL, not to zero.

Session Limits

The value that you specify with the SESSIONS command is not the only factor that limits the number of sessions that FastLoad establishes with the Teradata RDBMS. The other limiting factors are: · · The Teradata RDBMS limit of one session per AMP The platform limit on the maximum number of sessions per application This value is defined in the COP Interface software file, CLISPB.DAT, under the MaxSess variable. You can use the TDP SET MAXSESSIONS command to specify a platform limit. The default limit is equal to the server MAXSESS. The limit of the network protocol software on network-attached systems

·

So, when you invoke FastLoad, the actual session limit is determined by whichever limiting factor is encountered first.

File Size Restrictions

The maximum file size that is supported by FastLoad on network-attached client systems is 2 gigabytes.

Space Requirements and Limitations

Always estimate the final size of the FastLoad table, and make sure that the destination database on the Teradata RDBMS has enough space to accommodate your FastLoad job. If the database that owns the FastLoad table or the error tables runs out of space, the Teradata RDBMS returns an error message and FastLoad pauses your FastLoad job. When this happens, you have to allocate more space to the database before you can restart the job. Note: For TOS-based systems, the upper size limit for tables that can be sorted is approximately 5.5 gigabytes per AMP.

2 ­ 28

Teradata FastLoad Reference

Chapter 2: Using FastLoad Programming Considerations

UNIX Signals

If you are running FastLoad in a UNIX operating system, you need to be aware of the UNIX signals used by FastLoad. You cannot use the FastLoad UNIX signals in any module or routine you program for use with FastLoad. Doing so causes an error in FastLoad. FastLoad uses the following UNIX signals: · · · · SIGINT (interrupt signal) SIGQUIT (quit signal) SIGTERM (terminate signal) SIGUSR1 (user signal 1)

Note: Signals are predefined messages sent between two UNIX processes to communicate the occurrence of unexpected external events, or exceptions. Aborting a FastLoad session while FastLoad is in the middle of processing a job is an example of an exception. In this scenario, FastLoad uses the UNIX signals to trap the abort command, disconnects all sessions, do any necessary cleanup, and then terminate in an orderly manner.

Teradata FastLoad Reference

2 ­ 29

Chapter 2: Using FastLoad Using INMOD and Notify Exit Routines

Using INMOD and Notify Exit Routines

INMOD Routines

The term INMOD is an acronym for input modification routines. These are user exit routines that FastLoad and other load/export utilities can call to provide enhanced processing functions on input records before they are sent to the Teradata RDBMS. When you specify an INMOD routine in your DEFINE command, it is the named routine, rather than a data source, that provides the input data records that FastLoad loads into the FastLoad table on the Teradata RDBMS. You can use INMOD routines to: · · · Select and validate data records before passing them to the Teradata RDBMS Convert fields in data records before passing them to the Teradata RDBMS Read data directly from different database system data sets, such as IMS, TOTAL, etc., and create and pass a consolidated data record to the Teradata RDBMS without using an intermediate tape or disk data set.

Notify Exit Routines

An exit routine specifies a predefined action to be performed whenever certain significant events occur during a FastLoad job. Notify exit routines are especially useful in operator-free environments where job scheduling relies heavily on automation to optimize system performance. For example, by writing an exit in C (without using CLIv2) and using the NOTIFY command, you can provide a routine to detect whether a FastLoad job succeeds or fails, how many records were loaded, what the return code is for a failed job, and so on.

Programming Languages

The FastLoad utility is written in: · · SAS C for channel-attached VM and MVS client systems C for network-attached UNIX and Windows client systems

In all cases, INMOD and notify exit routines are dynamically loaded at run time, rather than link edited into the FastLoad module.You can write INMOD and notify exit routines in the following programming languages, depending on the platform that runs FastLoad:

2 ­ 30

Teradata FastLoad Reference

Chapter 2: Using FastLoad Using INMOD and Notify Exit Routines

On these platforms...

You can write...

· VM · MVS

INMOD routines in: · Assembler · COBOL · PL/I · SAS/C Notify exit routines in: · SAS/C

· UNIX · Windows

INMOD and notify exit routines in: · C Note: Although it is neither certified nor supported, you can write INMOD routines in COBOL on network-attached client systems if you use the Micro Focus COBOL for UNIX compiler.

Programming Structure

The structure for communicating between FastLoad and an INMOD routine is defined by the programming language:

For INMOD routines written in...

The programming structure is...

Assembler

RRECORD RTNCODE RLENGTH RBODY

DSECT DS DS DS F F CL32004

C

struct { long Status; long RecordLength; char buffer[32004]; }

COBOL

01 INMOD-RECORD. 03 RETURN-CODE PIC S9(9) COMP. 03 RECORD-LENGTH PIC S9(9) COMP. 03 RECORD-BODY PIC X(32004).

PL/I

DCL

1

PARMLIST, 10 STATUSFIXEDBINARY(31,0) 10 RLENGTHFIXEDBINARY(31,0) 10 REC CHAR(32004)

Teradata FastLoad Reference

2 ­ 31

Chapter 2: Using FastLoad Using INMOD and Notify Exit Routines

In each structure, the records must be constructed so that the left to right order of the data field corresponds to the order of the field names specified in the DEFINE command.

Entry Points

The following table shows the entry points for INMOD routines.

For INMOD routines written in... The entry point for FastLoad is...

SAS/C on MVS and VM platforms C on all supported workstation platforms COBOL and PL/I

dynamn BLKEXIT BLKEXIT

Addressing Mode on VM and MVS Systems

On FastLoad 07.00.00 and later, you can use either 31-bit or 24-bit addressing for INMOD routines on channel-attached systems. The 31-bit mode provides access to more memory, which enhances performance for FastLoad jobs with a large number of sessions. Use the following linkage parameters to specify the addressing mode when building INMOD routines for VM and MVS systems: · · AMODE(31) for 31-bit addressing AMODE(24) for 24-bit addressing

The FastLoad/INMOD Interface

The FastLoad utility exchanges information with an INMOD routine by passing an address that points to a three-value structure where:

Value... Is the...

StatusCode

32-bit signed binary value that carries information in both directions.

FastLoad to INMOD Interface

The FastLoad to INMOD interface uses six status codes where:

Value... Indicates that...

0

· ·

The FastLoad utility is calling for the first time. FastLoad expects the INMOD routine to return a record.

At this point the INMOD routine should perform its initialization tasks before sending a data record to FastLoad.

2 ­ 32

Teradata FastLoad Reference

Chapter 2: Using FastLoad Using INMOD and Notify Exit Routines

Value... Is the...

Status Code (continued)

1

· ·

The FastLoad utility is calling, not for the first time. FastLoad expects the INMOD routine to return a record. The client system has been restarted The INMOD routine should reposition to the last checkpoint. FastLoad is not expecting the INMOD routine to return a record.

2

· · ·

This is a one-time call, and FastLoad does not issue a subsequent call with a status code value of zero. 3 · · · 4 · · · A checkpoint has been written. The INMOD routine should remember the checkpoint position. FastLoad does not expect the INMOD routine to return a record. The Teradata RDBMS has failed. The INMOD routine should reposition to the last checkpoint. FastLoad is not expecting the INMOD routine to return a record.

This is a one-time call, and FastLoad does not issue a subsequent call with a status code value of zero. 5 · · The FastLoad job has ended. The INMOD routine should perform any required cleanup tasks.

Note: This condition only applies to network-attached client systems.

INMOD to FastLoad Interface

The INMOD to FastLoad interface uses two status codes where:

Value... Indicates that the INMOD routine is...

0 any nonzero value

returning a record as the Body value. at an end-of-file condition.

Teradata FastLoad Reference

2 ­ 33

Chapter 2: Using FastLoad Using INMOD and Notify Exit Routines

Value... Is the...

Length

32-bit signed binary value that the INMOD routine uses to specify the length, in bytes, of the data record. The INMOD routine can use a Length value of zero to indicate an end of file condition.

Body

area where the INMOD routine places the data record. The maximum record length depends on the release/version level of the Teradata RDBMS. The FastLoad utility neither checks nor enforces record length restrictions on data provided by your INMOD routine. The record length, therefore, must not exceed the capability of the Teradata RDBMS.

Caution: To prevent data corruption, INMOD routines that cannot comply with these protocols should terminate if they encounter a restart code 2, 3 or 4. To support proper FastLoad restart operations, INMOD routines must save and restore checkpoint information as described here. If the INMOD saves checkpoint information in some other manner, a subsequent restart/recovery operation could result in data loss or corruption.

The FastLoad/Notify Exit Routine Interface

The FastLoad utility accumulates operational information about specific events that occur during a FastLoad job. If the FastLoad job script includes a NOTIFY command with an EXIT option specification, then, when the specific events occur, FastLoad calls the named notify exit routine and passes to it: · · An event code to identify the event Specific information about the event

The following table lists the event codes and describes the data that FastLoad passes to the notify exit routine for each event. (See "NOTIFY" on page 3-46 for a description of the events associated with each level of notification--low, medium and high.) Note: To support future enhancements, always make sure that your notify exit routines ignore invalid or undefined event codes, and that they do not cause FastLoad to terminate abnormally

2 ­ 34

Teradata FastLoad Reference

Chapter 2: Using FastLoad Using INMOD and Notify Exit Routines

Event

Event Code

Event Description and Data Passed To The Notify Exit Routine

Initialize

0

Signifies successful processing of the NOTIFY command: · · · · · · · · · Version ID length--4-byte unsigned integer Version ID string--32-character (maximum) array Utility ID--4-byte unsigned integer Utility name length--4-byte unsigned integer Utility name string--32-character (maximum) array User name length--4-byte unsigned integer User name string--64-character (maximum) array Optional string length--4-byte unsigned integer Optional string--80-character (maximum) array

File or INMOD open

1

Signifies successful processing of the DEFINE command that specifies the file or INMOD routine name: · · File name length--4-byte unsigned integer File name--256-character (maximum) array

Phase 1 begin

2

Signifies the beginning of the insert phase, where the table name is specified by the INSERT statement: · · Table name length--4-byte unsigned integer Table name--128-character (maximum) array

Checkpoint

3

Signifies that checkpoint information has been written to the restart log table: · Record number--4-byte unsigned integer

Phase 1 end

4

Signifies the CHECKPOINT LOADING END request has successfully completed after the end of the insert phase: · · · · Records read--4-byte unsigned integer Records skipped--4-byte unsigned integer Records rejected--4-byte unsigned integer Records sent to the Teradata RDBMS--4-byte unsigned integer

Phase 2 begin

5

Signifies that the END LOADING command is about to be sent to the Teradata RDBMS: · No data accompanies the phase 2 begin event code

Phase 2 end

6

Signifies that processing of the END LOADING command completed successfully: · Records loaded--4-byte unsigned integer

Teradata FastLoad Reference

2 ­ 35

Chapter 2: Using FastLoad Using INMOD and Notify Exit Routines

Event Code

Event

Event Description and Data Passed To The Notify Exit Routine

Error table 1

7

Signifies that processing of the SEL COUNT(*) request completed successfully for the first error table: · Number of rows--4-byte unsigned integer

Error table 2

8

Signifies that processing of the SEL COUNT(*) request completed successfully for the second error table: · Number of rows--4-byte unsigned integer

Teradata RDBMS restart

9

Signifies that FastLoad received a crash message from the Teradata RDBMS or from the CLIv2: · No data accompanies the Teradata RDBMS restart event code

CLIv2 error

10

Signifies that FastLoad received a CLIv2 error: · Error code--4-byte unsigned integer

RDBMS error

11

Signifies that FastLoad received a Teradata RDBMS error that will produce an exit code of 12: · Error code--4-byte unsigned integer

Note: Not all Teradata RDBMS errors cause this event. A 3807 error, for example, while trying to drop or create a table does not terminate FastLoad Exit 12 Signifies that FastLoad is terminating: · Exit code--4-byte unsigned integer

Using the FastLoad Sample INMOD Routines

The FastLoad utility software includes two sample INMOD routines that show you how to write an INMOD routine using the C programming language:

Sample INMOD Routine Description

BLKEXITR.C

Retrieves records from a data source and supplies them to FastLoad. It runs continuously until it reaches EOF. Note: The BLKEXITR.C sample INMOD routine supports FastLoad restart operations.

2 ­ 36

Teradata FastLoad Reference

Chapter 2: Using FastLoad Using INMOD and Notify Exit Routines

Sample INMOD Routine Description

BLKEXIT.C

Generates data internally and passes the records to a FastLoad job for processing. To use this sample INMOD routine, you must supply a FastLoad job to load the data to the Teradata RDBMS. Note: The BLKEXIT.C sample INMOD routine does not support FastLoad restart operations.

The next two subsections provide a sample FastLoad job script for calling the sample FastLoad INMOD routines. As required, each job script includes a FastLoad DEFINE command that specifies the INMOD option. Also, you can execute these jobs either interactively or in batch mode, as described earlier in this chapter. See Appendix C for a complete listing of each sample INMOD routine.

Calling BLKEXIT.C

The following FastLoad job script calls the BLKEXIT.C sample INMOD routine:

SESSIONS 1 ; LOGON tdpid/username,password ; DROP TABLE Fastest ; DROP TABLE Fasterr1 ; DROP TABLE Fasterr2 ; CREATE TABLE FastTest (Column1 Integer Format '9(5)' NOT NULL BETWEEN 1 AND 9999) UNIQUE PRIMARY INDEX (Column1) ; DEFINE Test(Integer) INMOD=Blkexit ; BEGIN LOADING FastTest ERRORFILES Fasterr1, Fasterr2 ; INSERT INTO FastTest (Column1) VALUES (:test); END LOADING ; LOGOFF ;

Calling BLKEXITR.C

The following FastLoad job script calls the BLKEXITR.C sample INMOD routine:

* use your own account and password here. * LOGON sia1/weekly, weekly; DROP TABLE Error_1; DROP TABLE Error_2; DROP TABLE BlkExit; CREATE TABLE BlkExit AS

Teradata FastLoad Reference

2 ­ 37

Chapter 2: Using FastLoad Using INMOD and Notify Exit Routines Counter(Integer), c2(smallint), c3(integer), c4(smallint), c5(integer) UNIQUE PRIMARY INDEX (Counter); BEGIN LOADING BlkExit ErrorFiles Error_1, Error_2; DEFINE Counter(Integer), c2(smallint), c3(integer), c4(smallint), c5(integer) INMOD = BLKEXIT; INSERT INTO BlkExit (Counter, c2, c3, c4, c5 ) VALUES (:Counter, :c2, :c3, :c4, :c5 ); END LOADING; LOGOFF;

Creating Your Own INMOD Routines

To meet your particular system needs, you can either write your own INMOD routines, or you can modify the sample FastLoad INMOD routines to: · · · Select only records that meet specific criteria Convert certain fields to a different data type Perform other functions, as required

Whenever you create a new INMOD routine, or modify an existing one, you must compile the new or modified routine and link it into a shared object for use by FastLoad. Note for UNIX users: Because FastLoad 6.0 and later uses dynamic linking to load INMOD routines at runtime, you must also recompile and relink any INMOD routines that you created or modified under earlier versions of the utility. See Appendix D for procedures and examples of compiling and linking INMOD routines.

2 ­ 38

Teradata FastLoad Reference

Chapter 2: Using FastLoad Writing a FastLoad Job Script

Writing a FastLoad Job Script

Definition

A FastLoad job script, or program, is a set of FastLoad commands and Teradata SQL statements that actually load the data from your input data source or INMOD routine into the FastLoad table on the Teradata RDBMS. Before running FastLoad in batch mode, you must create a standard input file that contains the FastLoad job script. You then use the appropriate input file specification to identify the standard input file when you invoke FastLoad: · · · < infilename specification on network-attached client systems SYSIN DDNAME specification on channel-attached VM client systems FILEDEF SYSIN specification on channel-attached MVS client systems

Entering FastLoad Commands

FastLoad accepts the FastLoad commands and a subset of Teradata SQL statements described in Chapter 3. Though the command keywords are all shown in uppercase characters in the syntax diagrams, the commands are not case sensitive. You can use either uppercase or lowercase letters when typing the commands. When running FastLoad in interactive mode, you must wait for the FastLoad command prompt before entering a FastLoad command:

FastLoad - Enter your command:

Example FastLoad Job Script

The following FastLoad job script provides an overview of a typical FastLoad operation:

SESSIONS 4; RECORD 100 THRU 100000; ERRLIMIT 25; LOGON tdpid/userid,password DROP TABLE FastTable; DROP TABLE Error1; DROP TABLE Error2; CREATE TABLE FastTable, NO FALLBACK ( ID INTEGER, UFACTOR INTEGER, MISC CHAR(42)) PRIMARY INDEX(ID); DEFINE ID (INTEGER), UFACTOR (INTEGER), MISC (CHAR(42)) FILE=FileName;

Teradata FastLoad Reference

2 ­ 39

Chapter 2: Using FastLoad Writing a FastLoad Job Script SHOW; BEGIN LOADING FastTable ERRORFILES Error1,Error2 CHECKPOINT 10000; INSERT INTO FastTable (ID, UFACTOR, MISC) VALUES (:ID, :MISC); END LOADING; LOGOFF;

where:

This command... Performs this function...

SESSIONS RECORD ERRLIMIT LOGON DROP TABLE

Directs FastLoad to log on to the Teradata RDBMS for up to four sessions. Directs FastLoad to begin reading data at record 100 in the input data source and stop reading records at record 100,000. Directs FastLoad to stop processing when 25 errors occur. Logs the specified user on to the Teradata RDBMS for up to four sessions, as specified in the SESSIONS command. Makes sure that the FastLoad table and the two error tables do not already exist on the Teradata RDBMS. The FastLoad utility will not run if the two error tables exist from a prior job. And, though the FastLoad table can be an existing table, it must be empty. Thus, instead of deleting an existing FastLoad table, you could instead use the DELETE statement to remove all the rows.

CREATE TABLE DEFINE

Creates the FastLoad table on the Teradata RDBMS. Defines the data fields in each record and identifies the input data source. This command corresponds to a Teradata SQL USING clause. Displays the active definitions for the input data source and the field names that were specified in the previous DEFINE command. This command allows you to verify the exact definitions in effect during the FastLoad operation. Begins the loading phase of the FastLoad job. This command specifies the name of the FastLoad table and the two error tables, and, in this example, specifies that a checkpoint be taken every 10,000 records.

SHOW

BEGIN LOADING

2 ­ 40

Teradata FastLoad Reference

Chapter 2: Using FastLoad Writing a FastLoad Job Script

This command... Performs this function...

INSERT

Sends input data records to the Teradata RDBMS and inserts rows into the FastLoad table. The FastLoad utility processes the data records by:

1 2

END LOADING

Packaging them into large data blocks Transferring them to the Teradata RDBMS, where they are distributed to the AMPs

Directs the Teradata RDBMS to redistribute (hash) the rows of data on the AMPs and store them in the FastLoad table. Upon successful completion, FastLoad returns a status message that displays the total number of: · Records read · Records skipped · Records sent to the Teradata RDBMS · Records inserted as rows in the FastLoad table · Error records in the two error tables · Duplicate rows

LOGOFF

Logs off all FastLoad sessions, terminates FastLoad and presents your system command prompt.

Teradata FastLoad Reference

2 ­ 41

Chapter 2: Using FastLoad Running Multi-File FastLoad Jobs

Running Multi-File FastLoad Jobs

Definition

A multi-file FastLoad job is one that loads the FastLoad table with input data from more than one source. You do this by: 1 Using a LOGOFF command, with no END LOADING command, to intentionally pause the FastLoad job after you have initiated the job and loaded the data from the first source. Successively restarting and pausing the FastLoad job to load the data from each subsequent input source. Using an END LOADING command to terminate the FastLoad job after you have loaded the data from the last input source.

2 3

Note: When you run a multi-file FastLoad job, the FastLoad table and the two error tables remain locked and are not available to users until you use the END LOADING command to conclude the FastLoad job. The following subsections provide example FastLoad job scripts that show how to load a table called Fast_Table with data stored in three different input data sources--FirstFile, SecondFile and ThirdFile. "Command Functions" on page 2-44 describes each of the commands in the three job scripts. Appendix B gives you a complete example using all loading commands.

Initiating the FastLoad Job and Loading the First Data Source

The following FastLoad job script begins the multi-file FastLoad job and loads data from the data source named FirstFile into the FastLoad table:

LOGON tdpid/jwt,smart ; DROP TABLE Fast_Table ; DROP TABLE Error_1 ; DROP TABLE Error_2 ; CREATE TABLE Fast_Table (col1 (char(5), col2(integer)) ; BEGIN LOADING Fast_Table ERRORFILES Error_1, Error_2 ; DEFINE Field_1 (char(5)), Field_2 (integer) FILE = FirstFile ; INSERT INTO Fast_Table (col1, col2) VALUES (:Field_1, :Field_2) ; LOGOFF ;

2 ­ 42

Teradata FastLoad Reference

Chapter 2: Using FastLoad Running Multi-File FastLoad Jobs

Note: These examples do not use a RECORD command to specify a range of records in the input data sources. By default, they load the entire contents of each source into the FastLoad table. To specify a range of records, use a RECORD command before each INSERT statement in your multi-file FastLoad job scripts.

Restarting the FastLoad Job and Loading the Second Data Source

The following FastLoad job script loads the FastLoad table shown in the previous example with data from the data source named SecondFile:

LOGON tdpid/jwt,smart ; BEGIN LOADING Fast_Table ERRORFILES Error_1, Error_2 ; DEFINE Field_1 (char(5)), Field_2 (integer) FILE = SecondFile ; INSERT INTO Fast_Table (col1, col2) VALUES (:Field_1, :Field_2) ; LOGOFF ;

Restarting the FastLoad Job and Loading the Third Data Source

The following FastLoad job script loads the FastLoad table shown in the previous examples with data from the data source named ThirdFile:

LOGON tdpid/jwt,smart ; BEGIN LOADING Fast_Table ERRORFILES Error_1, Error_2 ; DEFINE Field_1 (char(5)), Field_2 (integer) FILE = ThirdFile ; INSERT INTO Fast_Table (col1, col2) VALUES (:Field_1, :Field_2) ; LOGOFF ;

Terminating the FastLoad Job

Sending the END LOADING command is the last step in a multi-file FastLoad job. You can include it in the job used to process the last input data source, or submit it as a separate job script. The following FastLoad job script issues the END LOADING command in a separate job script:

LOGON tdpid/jwt,smart ; BEGIN LOADING Fast_Table ERRORFILES Error_1, Error_2 ; END LOADING ; LOGOFF ;

Teradata FastLoad Reference

2 ­ 43

Chapter 2: Using FastLoad Running Multi-File FastLoad Jobs

Command Functions

In the example multi-file FastLoad job scripts:

This command... Performs this function...

LOGON DROP TABLE

logs user JWT on to the Teradata RDBMS. makes sure that the FastLoad table and the two error tables do not already exist on the Teradata RDBMS when you initiate the FastLoad job. creates a new FastLoad table, ensuring it is empty when you initiate the FastLoad job. specifies the name of the FastLoad table and the two error tables, and starts the loading phase of the FastLoad job. After executing the BEGIN LOADING command in the FastLoad job script that initiates the job and loads the first data source, the FastLoad response signifies the start of a new job: BEGIN LOADING COMPLETE In subsequent multi-file FastLoad job scripts, the FastLoad response is: FastLoad is continuing a multifile job.

CREATE TABLE BEGIN LOADING

DEFINE

identifies the field in each record to be inserted into the FastLoad table and specifies the name of the input data source. The field names should be the same for all of the multi-file FastLoad job scripts. The FILE= attribute, however, successively identifies a different source data source for each portion of the multi-file FastLoad job.

INSERT

transfers the data records from the specified input data source to the FastLoad table. The Teradata RDBMS automatically takes a checkpoint after the last record of each insert operation.

END LOADING

distributes all of the rows to the FastLoad table. The following message indicates that the command executed successfully: END LOADING COMPLETE Note: At this point, the FastLoad table and the two error tables are unlocked and available to any user with the appropriate access privileges.

2 ­ 44

Teradata FastLoad Reference

Chapter 2: Using FastLoad Running Multi-File FastLoad Jobs

This command... Performs this function...

LOGOFF

pauses the FastLoad operation when executed before an END LOADING command, which is the case for each multi-file FastLoad job script that identifies a new data source. Because the LOGOFF command is entered during the loading phase, FastLoad responds with the message: FastLoad Paused. When a FastLoad job is paused during the loading phase, the tables remain locked and cannot be accessed until FastLoad executes the END LOADING command. When submitted after an END LOADING command, as in the last multi-file FastLoad job script, the LOGOFF command terminates the FastLoad job. In this case, FastLoad displays: logoff; and an end of job status message, such as: Logging off all sessions Total processor time used = `0.54 Seconds' Start: Tue Feb 27 15:00 1996 End : Tue Feb 27 15:12 1996 = `0'. FDL4818 FastLoad Terminated. and then presents your system command prompt. Highest return code encountered

Teradata FastLoad Reference

2 ­ 45

Chapter 2: Using FastLoad Handling FastLoad Errors

Handling FastLoad Errors

FastLoad Error Conditions

While processing your FastLoad job script, FastLoad tracks and records information about five types of error conditions that cause the Teradata RDBMS to reject an input data record: · · · · · Constraint violations Conversion errors Unavailable AMP conditions Unique primary index violations Duplicate rows

where:

This error type... Refers to records from the input file that...

constraint violation conversion error unavailable AMP condition unique primary index violation duplicate row

do not comply with the range constraints you specified in the CREATE TABLE statement. fail a data type conversion that you specified in the DEFINE command. are destined for a nonfallback table on an AMP that is down. have a value for the unique primary index field that already exists, but is not a duplicate row. are exact duplicates of existing rows. Note: The FastLoad utility does not store duplicate rows in an error table.

Additionally, when operating in batch mode, FastLoad returns the system error codes for error conditions encountered during FastLoad operations. (FastLoad does not return system error codes when operating in interactive mode.) This subsection describes the procedures for handling the five types of FastLoad error conditions. For more information about system error messages, refer to the messages reference documentation for your operating system environment.

2 ­ 46

Teradata FastLoad Reference

Chapter 2: Using FastLoad Handling FastLoad Errors

Error Recording

The FastLoad utility stores the input data records related to constraint violations, conversion errors, unavailable AMP conditions and unique primary index violations in the two error tables that you specify in your BEGIN LOADING command:

The table specified as... Stores the records that produced these error types...

errortname1

· Constraint violations · Conversion errors · Unavailable AMP conditions These types of errors always occur during the loading phase of your FastLoad job--after executing the BEGIN LOADING command, but before the END LOADING command.

errortname2

· Unique primary index violations This type of error always occurs during the end-loading phase of your FastLoad job--after executing the END LOADING command.

The FastLoad utility discards all records that produce a duplicate row error, but includes the total number of duplicate rows encountered, along with the total records in each error table, in the end-of-job status report.

Error Table Formats

The first error table, errortname1, has three columns where:

This column... Contains the...

ErrorCode

Teradata RDBMS returncode for the error condition, as specified in the messages reference documentation for your operating system environment. name of the data item that caused the error condition, as specified in the fieldname attribute of the DEFINE command in your FastLoad job script. entire data record, as provided by the source producer operator. DataParcel is used as the primary index for the first error table. The data record string can be up to 64,000 bytes, depending on which version of the DBS the job is run against.

ErrorFieldName

DataParcel

The format of the second error table, errortname2, is identical to that of the FastLoad target table.

Teradata FastLoad Reference

2 ­ 47

Chapter 2: Using FastLoad Handling FastLoad Errors

Correcting Errors

Though the procedures are somewhat different, depending on the error table, correcting errors is a three step process in which you: 1 2 3 Retrieve the error information from the error tables on the Teradata RDBMS. Evaluate and correct the errors. Insert the corrected records into the FastLoad table.

Because FastLoad only operates on an empty table, after the job has completed you must use another utility, such as BTEQ, to access the Teradata RDBMS. The procedures and examples in the following subsections are performed under BTEQ. They assume you have invoked BTEQ and logged on to the Teradata RDBMS. See Teradata BTEQ Reference for more information about using BTEQ. Refer to "Product Related Publications" on page -iii.

Procedure for Correcting Errors in the First Error Table

Use the following procedure to correct errors recorded in the error table that you specified as errortname1:

Step Action

1

Use the following Teradata SQL statement to retrieve the error code and field name for each error in the first error table: SELECT ErrorCode, ErrorFieldName FROM BY ErrorCode ; errortname1 ORDER

where errortname1 is the name you specified for the first error table. BTEQ responds with a list of the error codes and the associated field names, formatted as follows: ***Query completed. 2 rows found. 2 columns returned. ***Total elapsed time was 1 second. ErrorCode --------2679 2679 ErrorFieldName -----------------A A

The values listed in the ErrorCode column are the Teradata RDBMS returncodes for each error condition, as specified in the messages reference documentation for your operating system environment. The values listed in the ErrorFieldName column are the names of the fields that caused each error.

2 ­ 48

Teradata FastLoad Reference

Chapter 2: Using FastLoad Handling FastLoad Errors

Step Action

2

Use the following BTEQ commands and Teradata SQL statements to retrieve the data records for each error in the first error table and store them in the specified err.out file on your client system: · If the values in the ErrorCode column indicate that either a constraint violation or a conversion error occurred, retrieve the DataParcel information in record mode: .SET RECORDMODE ON .EXPORT DATA FILE=err.out SELECT DataParcel FROM errortname1 In record mode, the Teradata RDBMS returns the SELECT statement results in client computer format, which is usually a hexadecimal dump. Thus, if all of the fields identified in the FastLoad DEFINE commands were also specified in the INSERT statements, the data retrieved from the FastLoad error table is in the same format as it was in the original input data source · Otherwise, if the values in the ErrorCode column indicate that the errors were all caused by unavailable AMP conditions, do not use the RECORDMODE command: .EXPORT DATA FILE=err.out SELECT DataParcel FROM errortname1

3

Use the ErrorCode and ErrorFieldName information returned in step 1 and the DataParcel information returned in step 2 to determine which records you want to correct and reload to the Teradata RDBMS. The methods that you use to correct the individual error conditions will vary, depending on the number and types of errors. After the errors have been corrected, use the following BTEQ commands and Teradata SQL statements to insert the corrected records into the FastLoad table on the Teradata RDBMS:

To... Use the...

4

transfer the data to the Teradata RDBMS define the fields in each record insert a record into the FastLoad table

BTEQ IMPORT command Teradata SQL USING modifier Teradata SQL INSERT statement

Caution: Do not reference the first two bytes in the INSERT statement for data records that were exported from the Teradata RDBMS in record mode. Instead, make the first field (variable parameter) in the USING modifier a dummy SMALLINT field.

Teradata FastLoad Reference

2 ­ 49

Chapter 2: Using FastLoad Handling FastLoad Errors

Step Action

4

(continued) When selecting data in record mode, the variable-length columns are all preceded by a two-byte field whose value indicates the length of the data field. But, because the DataParcel column of the errortname1 table is defined as a variable-length field, the first two bytes always indicate the length. If you do not reference this field in the INSERT statement, the Teradata RDBMS ignores this portion of each record in the input data.

5 6

Repeat steps 2 through 4 as required to resolve all of the errortname1 error conditions. Drop the errortname1 table from the Teradata RDBMS after you have resolved all of the errors.

Procedure for Correcting Errors in the Second Error Table

Use the following procedure to correct errors recorded in the error table that you specified as errortname2:

Step Action

1

Use the following Teradata SQL statement to retrieve all rows from the second error table: SELECT * FROM errortname2 ORDER BY cname ; where:

Syntax element... Is the...

errortname2 cname

name of the second error table. unique primary index for the table.

The BTEQ response is a list of the contents of the second error table, ordered by the values in the primary index column.

2

Use the following Teradata SQL statement to retrieve each row from the FastLoad table that has a primary index value identical to a row retrieved from the second error table: SELECT * FROM tname WHERE cname = errorvalue where:

Syntax element... Is the...

tname cname errorvalue

name of the FastLoad table. index of the FastLoad table. index value retrieved from the second error table.

2 ­ 50

Teradata FastLoad Reference

Chapter 2: Using FastLoad Handling FastLoad Errors

Step Action

3

Compare the rows selected from the error table with the rows selected from the FastLoad table and determine which is correct:

IF the row selected from the... THEN use a Teradata SQL...

error table is correct

a

DELETE statement to delete the incorrect row from the FastLoad table. correct row.

b INSERT statement to insert the

FastLoad table is correct DELETE statement to delete the corresponding row from the error table.

4 5

Repeat steps 2 and 3 until all rows in the error table are accounted for. Drop the errortname2 table from the Teradata RDBMS after you have resolved all of the errors.

Teradata FastLoad Reference

2 ­ 51

Chapter 2: Using FastLoad Handling FastLoad Errors

2 ­ 52

Teradata FastLoad Reference

Chapter 3:

FastLoad Commands

This chapter provides detailed descriptions of the FastLoad commands and the FastLoad version of the INSERT statement that you can execute from the FastLoad utility. The FastLoad utility accepts both FastLoad commands and a subset of Teradata SQL statements. The FastLoad commands perform session control and data handling activities. The Teradata SQL statements define and manipulate the data stored in the Teradata RDBMS. Experienced FastLoad users can also refer to the simplified command descriptions in the FastLoad chapter of Teradata Client Command Summary. This book provides the syntax diagrams and a brief description of the syntax variables for each Teradata client utility.

Syntax Notes

The following syntax rules apply when using FastLoad commands: · · · · · Commands may begin with a period, but do not have to. If there is no leading period, then there must be a semicolon at the end. If the command has a leading period, it must all be on one line. Commands that begin with a period cannot span multiple lines. The ending semicolon character is optional for FastLoad commands that both begin with a period and are specified on a single line Though you should always use a semicolon character to signify the end of a FastLoad command, it is not required for simple commands like SHOW and SESSIONS. Teradata SQL statements must not have a leading period and must always end with a semicolon.

·

See "Syntax Diagram Conventions" on page A-2 for a description of how to read the syntax diagrams used in this book.

Teradata FastLoad Reference

3­1

Chapter 3: FastLoad Commands AXSMOD

AXSMOD

Function

The AXSMOD command specifies the name and optional initialization string for an access module that provides data to the FastLoad utility on networkattached client systems. (For more information about specific access modules, refer to Teradata Client Access Modules Reference. See "Product Related Publications" on page -iii.)

Syntax

AXSMOD name "init-string"

GK01A038

;

where

Syntax element... Is the...

name

name of the access module file, where · tntbar.dll = Tape Access Module (on Windows platforms) · libpmrl.so = ReelLibrarian Access Module (on MP-RAS platform) · OLEDB_AXSMOD.dll = OLE DB Access Module (on Windows platforms) · np_axsmod.so = Named Pipes Access Module (on MP-RAS and Solaris platforms) · np_axsmod.dll = Named Pipes Access Module (on Windows platforms) · libmqs = MQ Series Access Module (on IBM MVS/ESA) NOTE: Large File Access Module is no longer available because the Data Connector API supports file sizes greater than 2 GB on Windows NT/2000/XP, HP-UX, AIX, and SolarisSPARC platforms. You may use your own shared library file name if you have a custom access module.

init-string

optional initialization string for the access module.

3­2

Teradata FastLoad Reference

Chapter 3: FastLoad Commands AXSMOD

Usage Notes

The following table describes the things you should consider when using the AXSMOD command:

Topic Usage Notes

When to Use the AXSMOD Command

The AXSMOD command is not required for loading: · Disk files on either network or channel-attached client systems · Magnetic tape files on channel-attached client systems It is required for loading magnetic tape and other types of files on network-attached client systems.

Command Placement

When using an access module, you must state the AXSMOD command before the DEFINE command in your FastLoad job script. If you state the AXSMOD command after the DEFINE command, the FastLoad utility terminates with an error message. Release 07.02.00 and above of the FastLoad utility software is not compatible with access modules prepared for release 07.00.00 and earlier. Similarly, access modules prepared for release 007.02.00 and above of the FastLoad utility are not compatible with release 07.00.00 and earlier.

Release Level Compatibility

Example

The following example provides the volume set name and owner as initialization parameters for the REELlibrarian access module, called libprmrl.so:

AXSMOD libpmrl.so "-V FastLoad -O lmn" ;

where · · · libpmrl.so is the name of the access module FastLoad is the name of the volume set lmn is the owner of the volume set

Teradata FastLoad Reference

3­3

Chapter 3: FastLoad Commands BEGIN LOADING

BEGIN LOADING

Function

The BEGIN LOADING command: · · · · Identifies the FastLoad table to receive data transferred from a data source on the client computer Specifies the names of the two error tables Starts a new FastLoad job or restarts a job that has been paused Locks the tables specified in the command so that users cannot access them until an END LOADING command is executed (After the end loading phase completes, any user with the appropriate privileges can access these tables.) Optionally: · Indicates how often checkpoints are taken · Identifies the presence of null indicators

·

Syntax

BEGIN LOADING A ERRORFILES dbname. B 2 CHECKPOINT integer INDICATORS

GK01B001

tname1 dbname. errortname1, dbname. errortname2 ;

A B

where

Syntax element... Is the...

dbname

name of the database in which each table resides. FastLoad uses the current default database if you do not include a database name with the table name specifications.

tname1

name of the FastLoad target table to receive the data from the client system. This must be a new table name.You cannot use a name that duplicates the name of an existing table unless you are restarting a paused FastLoad job.

3­4

Teradata FastLoad Reference

Chapter 3: FastLoad Commands BEGIN LOADING

Syntax element... Is the...

errortname1

name of the first error table. This must be a new table name.You cannot use a name that duplicates the name of an existing table unless you are restarting a paused FastLoad job.

errortname2

name of the second error table. This must be a new table name. You cannot use a name that duplicates an existing table unless you are restarting a paused FastLoad job.

CHECKPOINT integer

keyword that enables the checkpoint option. number of rows transmitted to the Teradata RDBMS between checkpoints when you use the CHECKPOINT keyword to enable the checkpoint option. (The checkpoint option is not enabled if you do not include the CHECKPOINT keyword in the BEGIN LOADING command.) See the Checkpoints topic under "Usage Notes" for more information about specifying the CHECKPOINT integer value.

INDICATORS

keyword that places null indicator bits at the front of each record. The number of fields in each record determines how many bytes contain null indicators, as described in the Indicators topic under "Usage Notes" for this command. Note: You cannot use the INDICATORS specification when loading records in variable-length text format. If your FastLoad job script specifies INDICATORS in the BEGIN LOADING command and the VARTEXT option in the SET RECORD command, the utility terminates with an error message.

Teradata FastLoad Reference

3­5

Chapter 3: FastLoad Commands BEGIN LOADING

Usage Notes

The following table describes the things you should consider when using the BEGIN LOADING command.

Topic Usage Notes

Required Privileges

The userid that is logged in to the FastLoad job must have: · SELECT and INSERT privileges on the FastLoad table · CREATE TABLE privilege on the database that owns the two error tables

The Restart Log Table

To run FastLoad, the following access rights must be available to user PUBLIC on the FastLoad restart log table (SYSADMIN.FASTLOG): · SELECT · INSERT · · UPDATE DELETE

Error Tables

FastLoad creates two error tables when executing the BEGIN LOADING command:

Error table specified by... Contains records that...

errortname1

were rejected because of an error other than unique primary index or duplicate row violation. violated the unique primary index constraint.

errortname2

Duplicate Records

The Teradata RDBMS ignores duplicate records, which are not inserted in either error table.

Reusing Table Names

If an error table has one or more rows, it is not dropped from the Teradata RDBMS at the end of a FastLoad job. To reuse the names specified for the error tables, use the DROP TABLE statement to remove the tables from the Teradata RDBMS.

More Information

See "Error Recording" on page 2-47.

3­6

Teradata FastLoad Reference

Chapter 3: FastLoad Commands BEGIN LOADING

Topic Usage Notes

Checkpoints

The CHECKPOINT option defines points in a job where the FastLoad utility pauses to record that the Teradata RDBMS has processed a specified number of input records. When you use checkpoints, you do not have to rerun the entire FastLoad job if it stops before completion. FastLoad uses the checkpoint information in the restart log table to determine the restart location.

Specifying the CHECKPOINT Integer Value

When specifying the integer value for the CHECKPOINT option: · If you enter zero as the value, the FastLoad utility processes the BEGIN LOADING command as if you did not enter a CHECKPOINT value. · If you do not enter a value, or if you enter a value that is not an integer, the Teradata RDBMS returns a syntax error.

More Information

See "Checkpoint Tradeoffs" on page 2-23. Indicators Indicators are bits at the beginning of a record that identify the nulled fields in the record. When you specify INDICATORS in the BEGIN LOADING command, FastLoad expects the first bytes of the record to contain an indicator bit for each record field. If the INDICATORS option is set but indicator bits are not entered at the beginning of the record, FastLoad assumes that the first field contains indicator bytes and loads the record incorrectly. Indicator bits must be stored in a minimum of eight-bit bytes. For example, if a record contains from one to eight fields, one byte is required for the indicator bits. If a record contains from nine to 16 fields, two bytes are required for the indicator bits and so on. Set unused bits in indicator bytes to zero.

Teradata FastLoad Reference

3­7

Chapter 3: FastLoad Commands BEGIN LOADING

Topic Usage Notes Indicator Bit Positions

Indicators (continued)

The positions of the indicator bits correspond to the record fields. The first bit in the byte is the indicator for the first field in the record. If an indicator bit is set to 1, the Teradata RDBMS nulls the corresponding field when the record is loaded. If the indicator bit is set to zero, the Teradata RDBMS loads the data specified for that field. Figure 3-1 shows a record containing indicators.

More Information

See the following documents for more information about indicator bits and the INDICATORS option: · · Teradata Call-Level Interface Version2 for Channel-Attached Systems Teradata Call-Level Interface Version2 for Network-Attached Systems

Figure 3-1 Indicator Bits

These fields are nulled

01001000

00000000

F1 F2 F3

F4 F5 F6 F7 F8

....

F16

GK01A036

Example

The following command example starts a FastLoad job:

BEGIN LOADING Employee ERRORFILES ErrTable1, ErrTable2 CHECKPOINT 50000;

IF the command is used to... THEN FastLoad responds...

start a new job, restart a partially completed job, continue a multifile job,

BEGIN LOADING COMPLETE! FastLoad is continuing a paused job! FastLoad is continuing a multifile job!

3­8

Teradata FastLoad Reference

Chapter 3: FastLoad Commands CLEAR

CLEAR

Function

The CLEAR command cancels the definitions that were specified by a previous DEFINE command, including the input data source.

Syntax

;

GK01A002

CLEAR

Usage Notes

The CLEAR command is: · · · Local to FastLoad Not transmitted to the Teradata RDBMS Intended for online use

Example

The following command example cancels the field definitions and input data source or INMOD name of a previous DEFINE command:

CLEAR ;

Completion Message

The FastLoad completion message is:

Warning: All previous column and file definitions cleared!

If you enter the SHOW command now, the result is blank.

Teradata FastLoad Reference

3­9

Chapter 3: FastLoad Commands DATEFORM

DATEFORM

Function

The DATEFORM command specifies the form of the DATE data type specifications for the FastLoad job.

Syntax

DATEFORM

INTEGERDATE ANSIDATE

;

JR01A014

where

Syntax element... Is the...

INTEGERDATE

keyword that specifies integer DATE data types for the FastLoad job. This is the default Teradata DATE data type specification for FastLoad jobs if you do not enter a DATEFORM command. keyword that specifies ANSI fixed-length CHAR(10) DATE data types for the FastLoad job.

ANSIDATE

Usage Notes

The following table describes the things you should consider when using the DATEFORM command.

Topic Usage Notes

Command Placement and Frequency

When using a DATEFORM command: · You must enter the command before the LOGON command. · You can use only one DATEFORM command.

3 ­ 10

Teradata FastLoad Reference

Chapter 3: FastLoad Commands DATEFORM

Topic Usage Notes

Data Type Conversions

When you use the ANSIDATE specification, you must convert ANSI/SQL DateTime data types to fixed-length CHAR data types when specifying the column/field names in the FastLoad DEFINE command. See the Usage Notes subsection of the DEFINE command description for a description of the fixed-length CHAR representations for each DATE, TIME, TIMESTAMP and INTERVAL data type specification.

Release Applicability

The ANSIDATE specification is valid for FastLoad jobs on the Teradata RDBMS Version 2, Release 3.0 and later.

Teradata FastLoad Reference

3 ­ 11

Chapter 3: FastLoad Commands DEFINE

DEFINE

Function

The DEFINE command: · · Describes the fields in a record of input data that are inserted in the FastLoad table Identifies the name of the input data source or use of an INMOD routine

The FastLoad utility translates the DEFINE command specifications into a Teradata SQL USING clause, and links the USING clause with a subsequent INSERT statement. Your FastLoad job must include DEFINE specifications for each field in a record from the input data source before executing an INSERT statement. Note: Though every field used in an INSERT statement must have been previously defined in a DEFINE command, every field so defined need not be used in an INSERT statement. Note also: Though there is no limit to the number of fields you can specify with the FastLoad DEFINE command, the maximum number of columns in a Teradata RDBMS table is 255. The FastLoad utility also uses the DEFINE command data type specifications to determine the format and record length of stored data.

Syntax

DEFINE DEF fieldname (datatype ,NULLIF = A FILE DDNAME INMOD = name

GK01C005

A , ) value ; = filename

3 ­ 12

Teradata FastLoad Reference

Chapter 3: FastLoad Commands DEFINE

where

Syntax element... Is the...

fieldname

name of a field in a record of the input data source, from left to right. You cannot use FILE, DDNAME or INMOD for a fieldname. Note: You can omit some or all of the field names if the accompanying INSERT statement uses the "wild card" table name specification (tablename.*) for all of the columns in the table. See the INSERT statement description later in this chapter for the syntax of the tname.* specification.

datatype

keyword or keyword phrase that specifies the data type of the field. For details on data types and data conversions, see Teradata RDBMS SQL Reference (B035-1101-061A). The valid data types for records in a FastLoad table are: · BYTE(n) · BYTEINT · CHARACTERS(n) · DATE · DECIMAL(x) or DECIMAL(x,y) · FLOAT · GRAPHIC (n) · · · · · · · INTEGER LONG VARCHAR LONG VARGRAPHIC SMALLINT VARBYTE (n) VARCHAR (n) VARGRAPHIC (n)

NULLIF=value

keyword phrase that loads the Teradata RDBMS field with a null value if the defined client field contains the specified value. The value specification can be 1 to 80 bytes in length, and it pertains only to BYTE, CHAR and GRAPHIC types. It does not pertain to integer and float data types. The NULLIF option occurs only with the DEFINE command.

FILE=filename

keyword phrase specifying the name of the data source that contains the input data. Note: For channel-attached systems, replace FILE with DDNAME for MVS and VM. DDNAME is the sequential dataset for MVS or CMS file of input data for VM.

INMOD=name

keyword phrase specifying the name of a user exit routine that provides input data records. See "INMOD Routines" on page 2-30 to learn about using INMOD routines.

Teradata FastLoad Reference

3 ­ 13

Chapter 3: FastLoad Commands DEFINE

Usage Notes

The following table describes the things you should consider when using the DEFINE command.

Topic Usage Notes

Data Type Descriptions

Depending on the data type phrase of the CREATE TABLE statement, the FastLoad utility stores data with CHAR(n) and VARCHAR(n) data type specifications as follows: · When the DEFINE command datatype attribute is CHAR(n):

And the CREATE TABLE datadesc attribute is... FastLoad stores the data in...

CHAR(n) CHAR(m) VARCHAR(m) ·

fixed-length format, entire field fixed-length format, padded if m > n, truncated if m < n variable-length format with blanks trimmed

When the DEFINE command datatype attribute is VARCHAR(n):

And the CREATE TABLE datadesc attribute is... FastLoad stores the data in...

VARCHAR(m) CHAR(m)

variable-length format, no padding, blanks not trimmed padded or truncated format, as required

Input Length and Field Descriptions

Following is the input length and field description for each data type specification: BYTE(n) · · Length: n bytes Description: n bytes

BYTEINT · · Length: 1 byte Description: 8-bit signed binary

CHAR, CHARS(n), and CHARACTERS(n) · · Length: n bytes Description: n ASCII characters

3 ­ 14

Teradata FastLoad Reference

Chapter 3: FastLoad Commands DEFINE

Topic Usage Notes

Input Length and Field Descriptions (continued)

DATE · · Length: 4 bytes Description: 32-bit integer in YYYMMDD format as a decimal value

Note: If you have used a DATEFORM command to specify ANSIDATE as the DATE data type, the FastLoad utility internally converts each DATE field to a CHAR(10) field. DECIMAL(x) and DECIMAL(x,y) · · Length: 1, 2, 4, or 8 bytes for network; packed decimal for main frame Description: 64-bit double precision, floating point

For more information on the DECIMAL data type, see Teradata RDBMS SQL Reference (B035-1101-061A). FLOAT · · Length: 8 bytes Description: 64-bit, double precision, floating point

INTEGER · · Length: 4 bytes Description: 32-bit, signed binary

LONG VARCHAR · · Length: m + 2 characters where m = 32000 Description: 16-bit integer, count m, followed by m ASCII characters

SMALLINT · · Length: 2 bytes Description: 16-bit signed binary

VARCHAR(n) · · Length: m + 2 bytes where m = 32000 Description: 16-bit integer, count m, followed by m ASCII characters

VARBYTE(n) · · Length: m + 2 bytes where m <= n Description: 16-bit integer, count m, followed by m bytes of data

Teradata FastLoad Reference

3 ­ 15

Chapter 3: FastLoad Commands DEFINE

Topic Usage Notes

Input Length and Field Descriptions (continued)

GRAPHIC(n) · · Length: (n*2) bytes, if n is specified; otherwise 2 bytes as n=1 is assumed Description: n double-byte characters (1n is the length of the input stream in terms of double-byte characters)

VARGRAPHIC(n) · · Length: m + 2 bytes where m/2 <= n Description: 2-byte integer followed by m/2 double-byte characters

Note: For both VARGRAPHIC and LONG VARGRAPHIC, m, a value occupying the first two bytes of the input data, is the length of the input in bytes, not characters. Each multibyte character-set character is 2 bytes. LONG VARGRAPHIC · · Length: m + 2 bytes where m/2 <= 16000 Description: 2-byte integer followed by m/2 double-byte characters

Note: LONG VARGRAPHIC also implies VARGRAPHIC (16000). Range is 0 - 16000 in a 32000-byte field. GRAPHIC Data Types GRAPHIC data types define multibyte character set data. FastLoad accepts GRAPHIC data in its input data when a site is defined for kanji. The DEFINE command supports these types of input data: · · · GRAPHIC VARGRAPHIC LONG VARGRAPHIC

The format to accommodate multibyte character sets and data containing multibyte characters is: · · "G" and "XG" for channel-attached systems "XG" and the standard character string format for networkattached systems

where

Syntax Element... Is of the Form...

"G"

G'<....>' where · "<" and ">" represent the Shift-Out (0x0E) and Shift-In (0x0F) characters, respectively

3 ­ 16

Teradata FastLoad Reference

Chapter 3: FastLoad Commands DEFINE

Topic Usage Notes Syntax Element... Is of the Form...

GRAPHIC Data Types (continued)

· ·

All characters in between must be valid characters for the character set The number of characters within the Shift-Out/Shift-In must be an even number.

"XG"

`hhhh'XG where · "hh" represents a pair of hexadecimal digits (0-9 and A-F) · Each pair of hexadecimal digits represents a single GRAPHIC character. · Since a maximum of 80 bytes may be specified in a NULLIF clause, this translates to 80 pairs of hexadecimal digits.

The NULLIF option nulls a column in a table when the data field is a certain value. A field with a value of zero, for example, could represent a null date. To meet this requirement, enter the field definition in the DEFINE command as: DueDate (DATE, NULLIF = 0)

The FastLoad utility compares the value entered in the NULLIF clause with the actual data row. If they match, the utility sets the appropriate indicator bit to ON for the column in that row and sends both the row and the indicator bit string to the Teradata RDBMS. The Teradata RDBMS then inserts a null value into the column. VARCHAR fields are checked to see if the length of the NULLIF string matches the two byte length indicator field (in the data row). The values are compared only if they are equal. If you have a NULLIF value that equals ten bytes, FastLoad compares it with the first ten bytes of the corresponding field in the data row. NULLIF Data Type Restrictions and Limitations The following minimum and maximum values may not apply in some applications because of the runtime environment of the individual platform:

BYTE

Up to 80 hexadecimal digits, enclosed by single quotes and must be an even number. "XB" is required after the hex string. The total number of bytes must not exceed two times the number of bytes specified in the data description.

Teradata FastLoad Reference

3 ­ 17

Chapter 3: FastLoad Commands DEFINE

Topic Usage Notes

NULLIF Data Type Restrictions and Limitations (continued)

Characters must be within the range of 0-9 or A-F.

Valid examples: DEFINE T1(BYTE (7), NULLIF = '01'XB); DEFINE T1(BYTE (7), NULLIF = '0123456789ABCD'XB); Invalid examples: DEFINE T1(BYTE (7), NULLIF = '0'XB) ; DEFINE T1(BYTE (7), NULLIF = '0M'XB) ;

BYTEINT

Must be within the range of -128 to 127. Valid examples: DEFINE T1(BYTEINT, NULLIF = 123) ; DEFINE T1(BYTEINT, NULLIF = -123) ; Invalid examples: DEFINE T1(BYTEINT, NULLIF = 129) ; DEFINE T1(BYTEINT, NULLIF = -129) ;

CHAR, CHARS, and CHARACTERS

For normal string format, from 1 to 80 bytes enclosed in single quotes. For "XC" format, up to 80 pairs of hexadecimal digits enclosed in single quotes. This must be an even number and the "XC" is required. Each pair of hexadecimal digits corresponds to a single character. The total number of characters defined in the NULLIF option must not exceed the number of characters specified by the data definition. Character compare operations are case sensitive, and apply only to the first 80 bytes. Valid examples: DEFINE T1(CHAR (7), NULLIF = ' ') ; DEFINE T1(CHAR (7), NULLIF = 'ABCDEFG') ; Invalid examples: DEFINE T1(CHAR (7), NULLIF = 'ABCDEFGH') ; DEFINE T1(CHAR (4), NULLIF = 'ABCDEFGH'XC');

3 ­ 18

Teradata FastLoad Reference

Chapter 3: FastLoad Commands DEFINE

Topic Usage Notes DATE

NULLIF Data Type Restrictions and Limitations (continued)

INTEGER format only and cannot be negative. Valid examples: DEFINE T1(INTEGER, NULLIF = 123) ; DEFINE T1(DATE, NULLIF = 941015) ; Invalid example: DEFINE T1 (DATE, NULLIF=94-10-15)

DECIMAL

Must be specified by a zoned number less than or equal to 15 digits. The number of digits specified in the NULLIF option must not exceed the number of digits entered in the data definition. If the NULLIF value contains more digits after the decimal point than are defined, results are undefined. Using NULLIF for DECIMAL (other than zero) may jeopardize a NULLIF match. Valid examples: DEFINE T1(DECIMAL (5,2), NULLIF = 0) ; DEFINE T1(DECIMAL Invalid examples: DEFINE T1(DECIMAL (18,0), NULLIF = 1234) ; DEFINE T1(DECIMAL (6,0), NULLIF = 1234567) ;

FLOAT

(5,2), NULLIF = 123.45) ;

Floating point values are represented differently on the Teradata RDBMS and on some of the other platforms. Consequently, compare operations with exported floating point numbers may not function properly. The format for floating point numbers is: xxx.xxx or xx.xxE(+/-)yy or xE(+/-)yy or xxx The range of valid floating point values on the various platforms is: · · · VM and MVS: 1E-37 to 1E+37 UNIX: 4.94065645841246544e-324 to 1.79769313486231470e+308 Windows: 3.4e-38 to 3.4e+38

Teradata FastLoad Reference

3 ­ 19

Chapter 3: FastLoad Commands DEFINE

Topic Usage Notes

NULLIF Data Type Restrictions and Limitations (continued)

A valid example: DEFINE T1(FLOAT, NULLIF = 123) ;

GRAPHIC and VARGRAPHIC

From 1 to 80 characters and must be enclosed in single quotes. For channel-attached systems, the quoted string must be preceded by "G" or followed by "XG". For network-attached systems, the quoted string may be followed by "XG", but cannot be preceded by "G". When using the "G" format, the total number of characters defined by the NULLIF clause must not exceed two times the number of bytes specified in the data description. When using the "XG" format, the total number of hexadecimal digits defined by the NULLIF clause must not exceed four times the number of bytes specified in the data description. The GRAPHIC or VARGRAPHIC string has this form: G'<ABC>' where <ABC> is the quoted string of valid MBC and the characters < and > represent 0x0E and 0x0F. Valid examples on channel-attached systems: DEFINE T1(GRAPHIC(4), NULLIF = G'<ABCDEFGH>'); DEFINE T1(GRAPHIC(4), NULLIF = G'<01234567>'); Invalid examples on channel-attached systems: DEFINE T1(GRAPHIC(4), NULLIF = G'<01234567ABCD>'); DEFINE T1(GRAPHIC(4), NULLIF = G'ABCD0123'); Valid examples on network-attached systems: DEFINE T1(GRAPHIC(4), NULLIF = 'ABCDEFGH'); DEFINE T1(GRAPHIC(4), NULLIF = '01234567'); Invalid examples on network-attached systems: DEFINE T1(GRAPHIC(4), NULLIF = G'<ABCDEFGH>'); DEFINE T1(GRAPHIC(4), NULLIF = G'<01234567>');

3 ­ 20

Teradata FastLoad Reference

Chapter 3: FastLoad Commands DEFINE

Topic Usage Notes INTEGER

NULLIF Data Type Restrictions and Limitations (continued)

Integer fields and date fields must be within the range of 2147483648 to 2147483647. Valid examples: DEFINE T1(INTEGER, NULLIF = 123) ; DEFINE T1(DATE, NULLIF = 941015) ;

SMALLINT

Small integer fields must be within the range -32768 to 32767. Valid examples: DEFINE T1(SMALLINT, NULLIF = 123) ; DEFINE T1(SMALLINT, NULLIF = -123) ; Invalid examples: DEFINE T1(SMALLINT, NULLIF = 32768) ; DEFINE T1(SMALLINT, NULLIF = -32769) ;

VARBYTE

80 hex digits, enclosed by single quotes and must be an even number. "XB" is required after the hex string.

VARCHAR

For normal string format, 1-80 bytes enclosed in single quotes. For "XC" format, up to 80 pairs of hexadecimal digits, enclosed in single-quotes and must be an even number. The "XC" is required, and each pair of hexadecimal digits corresponds to a single character. Numeric Fields The MAXIMUM and MINIMUM range for all fields is the default for each machine implementation. These values generally agree with the Teradata RDBMS except in cases where they are limited by the implementation. These values are documented by the manufacturer or can be found in the "C" language guide for that machine. You can use either of the following commands to retrieve a list of field names from the referenced table: HELP TABLE tname ; INSERT tname.* ; Note: Do not use both of these commands together.

Using Table Definitions to Define Data

Teradata FastLoad Reference

3 ­ 21

Chapter 3: FastLoad Commands DEFINE

Topic Usage Notes

Using Table Definitions to Define Data (continued)

CAUTION: Do not use the tname.* version of an INSERT statement when using Unicode data from: · · · A KATAKANAEBCDIC session A session with a character set name ending with _0I Any session with a character set that does not support multibyte characters (e.g., ASCII, or EBCDIC).

(See the Using Unicode Data topic In the Usage Notes subsection of the INSERT command description, later in this chapter for more information about this precaution.) When you use this format of the INSERT statement, the FastLoad utility constructs a list of field names from the table definition. During the insert operation, the utility gets the field names and their data types from the CREATE TABLE statement used to define the table and from the table definition. The following example uses an INSERT statement to get a list of field names from a table called Employee: LOGON dbc/peterson,veep ; BEGIN LOADING Employee ERRORFILES Etable2 ; DEFINE FILE = Accounts ; INSERT Employee.*; The following example uses a HELP command to get a list of field names from a table called Employee: LOGON dbc/peterson,veep ; BEGIN LOADING Employee ERRORFILES Etable2 ; DEFINE FILE = INFILE ; HELP TABLE Employee ; INSERT INTO Employee (EmpNum, Name) (:EmpNum, :Name) ; VALUES Etable1, Etable1,

Note: With either of these examples, you must also enter a DEFINE command specifying either the input data source or INMOD parameter. Using More Than One DEFINE Command When a DEFINE command does not fit on one input line, you can enter either: · · The command on several lines Several DEFINE commands

In either case, the FastLoad utility concatenates your field definitions until you enter an INSERT statement.

3 ­ 22

Teradata FastLoad Reference

Chapter 3: FastLoad Commands DEFINE

Topic Usage Notes

One DEFINE Command (continued)

Also, when you enter more than one DEFINE command, the field definitions in all of them must appear in the same order as they do in the input data record, just as they would if you entered them in a single DEFINE command. And, you can have only a FILE or INMOD declaration in one of the DEFINE commands. When you use the DATEFORM command to specify ANSIDATE as the DATE data type, the FastLoad utility internally converts each DATE field to a CHAR(10) field. You must convert all ANSI/SQL DateTime TIME, TIMESTAMP and INTERVAL data types to fixed-length CHAR data types to specify column/field names in a FastLoad DEFINE command. Table 3-1 provides the conversion specifications and format examples for each ANSI/SQL DateTime specification.

Using ANSI/SQL DateTime Data Types

Table 3-1

ANSI/SQL DateTime Specifications

DATE

Convert to: Format: Example:

TIME TIME (n)

CHAR(10) yyyy/mm/dd 1998/01/01

where n is the number of digits after the decimal point, 1 through 6. (Default = 6.) Convert to: Format (n = 0): Example: Format: (n = 4): Example:

TIMESTAMP TIMESTAMP (n)

CHAR(8 + n + (1 if n > 0, otherwise 0)) hh:mm:ss 11:37:58 hh:mm:ss.ssss 11:37:58.1234

where n is the number of digits after the decimal point, 1 through 6. (Default = 6.) Convert to: Format (n = 0): Example: Format (n = 4): Example:

TIME WITH TIME ZONE TIME (n) WITH TIME ZONE

CHAR(19 + n + (1 if n > 0, otherwise 0)) yyyy-mm-dd hh:mm:ss 1998-09-04 11:37:58 yyyy-mm-dd hh:mm:ss.ssss 1998-09-04 11:37:58.1234

Teradata FastLoad Reference

3 ­ 23

Chapter 3: FastLoad Commands DEFINE Table 3-1 (Continued) ANSI/SQL DateTime Specifications where n is the number of digits after the decimal point, 1 through 6. (Default = 6.) Convert to: Format (n = 0): Example: Format (n = 4): Example: CHAR(14 + n + (1 if n > 0, otherwise 0)) hh:mm:ss{±}hh:mm 11:37:58-08:00 hh:mm:ss.ssss {±} hh:mm 11:37:58.1234-08:00

TIMESTAMP WITH TIME ZONE TIMESTAMP (n) WITH TIME ZONE

where n is the number of digits after the decimal point, 1 through 6. (Default = 6.) Convert to: Format (n = 0): Example Format (n = 4): Example:

INTERVAL YEAR INTERVAL YEAR (n)

CHAR(25 + n + (1 if n > 0, otherwise 0)) yyyy-mm-dd hh:mm:ss{±}hh:mm 1998-09-24 11:37:58+07:00 yyyy-mm-dd hh:mm:ss.ssss{±}hh:mm 1998-09-24 11:37:58.1234+07:00

where n is the number of digits, 1 through 4. (Default = 2.) Convert to: Format (n = 2): Example: Format (n = 4): Example: CHAR(n) yy 98 yyyy 1998

INTERVAL YEAR TO MONTH INTERVAL YEAR (n) TO MONTH

where n is the number of digits, 1 through 4. (Default = 2.) Convert to: Format (n = 2): Example: Format (n = 4): Example:

INTERVAL MONTH INTERVAL MONTH (n)

CHAR(n + 3) yy-mm 98-12 yyyy-mm 1998-12

where n is the number of digits, 1 through 4. (Default = 2.) Convert to: CHAR(n)

3 ­ 24

Teradata FastLoad Reference

Chapter 3: FastLoad Commands DEFINE Table 3-1 (Continued) ANSI/SQL DateTime Specifications Format (n = 2): Example: Format (n = 4): Example:

INTERVAL DAY INTERVAL DAY (n)

mm 12 mmmm 0012

where n is the number of digits, 1 through 4. (Default = 2.) Convert to: Format (n = 2): Example: Format (n = 4): Example:

INTERVAL DAY TO HOUR INTERVAL DAY (n) TO HOUR

CHAR(n) dd 31 dddd 0031

where n is the number of digits, 1 through 4. (Default = 2.) Convert to: Format (n = 2): Example: Format (n = 4): Example: CHAR(n + 3) dd hh 31 12 dddd hh 0031 12

INTERVAL DAY TO MINUTE INTERVAL DAY (n) TO MINUTE

where n is the number of digits, 1 through 4. (Default = 2.) Convert to: Format (n = 2): Example: Format (n = 4): Example: CHAR(n + 6) dd hh:mm 31 12:59 dddd hh:mm 0031 12:59

INTERVAL DAY TO SECOND INTERVAL DAY (n) TO SECOND INTERVAL DAY TO SECOND (m) INTERVAL DAY (n) TO SECOND (m)

where · n is the number of digits, 1 through 4. (Default = 2.) · m is the number of digits after the decimal point, 1 through 6. (Default = 6.) Convert to: CHAR(n + 9 + m + (1 if m > 0, 0 otherwise))

Teradata FastLoad Reference

3 ­ 25

Chapter 3: FastLoad Commands DEFINE Table 3-1 (Continued) ANSI/SQL DateTime Specifications Format (n = 2, m = 0): Example: Format (n = 4, m = 4): Example:

INTERVAL HOUR INTERVAL HOUR (n)

dd hh:mm:ss 31 12:59:59 dddd hh:mm:ss.ssss 0031 12:59:59:59.1234

where n is the number of digits, 1 through 4. (Default = 2.) Convert to: Format (n = 2): Example: Format (n = 4): Example: CHAR(n) hh 12 hhhh 0012

INTERVAL HOUR TO MINUTE INTERVAL HOUR (n) TO MINUTE

where n is the number of digits, 1 through 4. (Default = 2.) Convert to: Format (n = 2): Example: Format (n = 4): Example: CHAR(n + 3) hh:mm 12:59 hhhh:mm 0012:59

INTERVAL HOUR TO SECOND INTERVAL HOUR (n) TO SECOND INTERVAL HOUR TO SECOND (m) INTERVAL HOUR (n) TO SECOND (m)

where · n is the number of digits, 1 through 4. (Default = 2.) · m is the number of digits after the decimal point, 1 through 6. (Default = 6.) Convert to: Format (n = 2, m = 0): Example: Format (n = 4, m = 4): Example:

INTERVAL MINUTE INTERVAL MINUTE (n)

CHAR(n + 6 + m + (1 if m > 0, 0 otherwise)) hh:mm:ss 12:59:59 hhhh:mm:ss.ssss 0012:59:59.1234

where n is the number of digits, 1 through 4. (Default = 2.) Convert to: CHAR(n)

3 ­ 26

Teradata FastLoad Reference

Chapter 3: FastLoad Commands DEFINE Table 3-1 (Continued) ANSI/SQL DateTime Specifications Format (n = 2): Example: Format (n = 4): Example: mm 59 mmmm 0059

INTERVAL MINUTE TO SECOND INTERVAL MINUTE (n) TO SECOND INTERVAL MINUTE TO SECOND (m) INTERVAL MINUTE (n) TO SECOND (m)

where · n is the number of digits, 1 through 4. (Default = 2.) · m is the number of digits after the decimal point, 1 through 6. (Default = 6.) Convert to: Format (n = 2, m = 0): Example: Format (n = 4, m = 4): Example:

INTERVAL SECOND INTERVAL SECOND (n) INTERVAL SECOND (n,m)

CHAR(n + 3 + m + (1 if m > 0, 0 otherwise)) mm:ss 59:59 mmmm:ss.ssss 0059:59.1234

where · n is the number of digits, 1 through 4. (Default = 2.) · m is the number of digits after the decimal point, 1 through 6. (Default = 6.) Convert to: Format (n = 2, m = 0): Example: Format (n = 4, m = 4): Example: CHAR(n + m + (1 if m > 0, 0 otherwise)) ss 59 ssss.ssss 0059.1234

Teradata FastLoad Reference

3 ­ 27

Chapter 3: FastLoad Commands END LOADING

END LOADING

Function

The END LOADING command distributes all of the rows that were sent from the client to the Teradata RDBMS during the loading phase to their final destination on the AMPs.

Syntax

END LOADING ;

GK01A008

Usage Notes

The following table describes the things you should consider when using the END LOADING command.

Topic Usage Notes

The End Loading Phase

The END LOADING command begins the end loading phase of a FastLoad job. During this phase, all rows are distributed on the AMPs and stored in the final FastLoad table. When the end loading phase completes, the Teradata RDBMS removes the access locks that were placed on the three tables specified in the BEGIN LOADING command so users with the proper privileges can access them using Teradata SQL statements.

Entering Teradata SQL Statements

Because the FastLoad utility supports only a subset of the Teradata SQL language, you cannot enter many of the Teradata SQL statements from the FastLoad utility. So, when END LOADING has completed and you want to access data stored in the FastLoad table or the error tables, you cannot do so from FastLoad. You must use BTEQ or a similar application program to query these tables. The FastLoad utility automatically drops error tables that contain no rows when END LOADING finishes executing. The Teradata RDBMS uses internal checkpointing while processing an END LOADING command. Therefore, a job can be interrupted during the end loading phase without disturbing processing status. When the job is restarted, the Teradata RDBMS resumes processing from where it left off.

Error Tables Internal Checkpointing

3 ­ 28

Teradata FastLoad Reference

Chapter 3: FastLoad Commands END LOADING

Example

The following command example completes a FastLoad job:

END LOADING ;

Completion Message

The FastLoad completion message for the END LOADING command depends on whether the job includes a RECORD command.

If the job... FastLoad gives you a response like this...

includes a RECORD command

Total Records Read = - skipped by RECORD command = - sent to the RDBMS = Total Error Table 1 = Total Error Table 2 = Total Inserts Applied = Total Duplicate Rows =

500 50 450 25 0 425 0 500 25 0 475 0

does not include a RECORD command

Total Records Read = Total Error Table 1 = Total Error Table 2 = Total Inserts Applied = Total Duplicate Rows =

Teradata FastLoad Reference

3 ­ 29

Chapter 3: FastLoad Commands ERRLIMIT

ERRLIMIT

Function

The ERRLIMIT command limits the number of records that can be rejected while inserting data into the FastLoad table.

Syntax

ERRLIMIT n ;

GK01A009

where

Syntax element... Is the...

n

maximum number of records that can be rejected before executing the END LOADING command. The default error limit value is 1000000.

Usage Notes

The following table describes the things you should consider when using the ERRLIMIT command.

Topic Usage Notes

Limiting Insertion Errors

Use the ERRLIMIT command to limit the number of insertion errors captured in the first error table (errortname1) during the loading phase of a job. Processing terminates when the number of errors encountered reaches the error limit. If, for example, you expect no errors in the input data, set the error limit value to one. In this case, the job terminates when any record causes an error. Note, however, that when the specified error limit is reached, the FastLoad utility continues processing until each session completes its current data block. This continued processing can cause the total number of error rows captured in the first error table to exceed the ERRLIMIT specification.

Restarting a Job

You can restart a job from the last checkpoint if it terminates because the error limit is reached. If checkpoints were not taken, restart the job from the beginning.

3 ­ 30

Teradata FastLoad Reference

Chapter 3: FastLoad Commands ERRLIMIT

Example

The following command example terminates the job after 20 errors:

ERRLIMIT 20 ;

Completion Message

The FastLoad completion message is:

Error limit set to :20

Teradata FastLoad Reference

3 ­ 31

Chapter 3: FastLoad Commands HELP

HELP

Function

The HELP command returns the syntax of all FastLoad commands and lists the Teradata SQL statements supported by the FastLoad utility.

Syntax

HELP ;

GK01A010

Usage Notes

The following table describes the things you should consider when using the HELP command.

Topic Usage Notes

Using the HELP Command Return Message Symbols

The HELP command is intended for online use. The HELP command return messages use these symbols:

Symbol Indicates

[ ] { } More Information

An optional entry A choice of entries, one of which must be selected

Refer to Teradata RDBMS SQL Reference for your operating system environment for syntax and a complete description of each Teradata SQL statement. See "Product Related Publications" on page -iii.

Example

The following command example returns a list of FastLoad commands:

HELP ;

3 ­ 32

Teradata FastLoad Reference

Chapter 3: FastLoad Commands HELP

Completion Message

The FastLoad completion message is:

Listed below is the syntax of each FastLoad command. Single-line commands may be preceded by a [.] or terminated by a [;]. Multi-line commands must NOT be preceded by a [.] but must be terminated by a [;]. SQL statements must NOT be preceded by a [.] and MUST be terminated by a [;]. AXSMOD name [ "<init-string>" ] ; BEGIN LOADING [dbname.]tname1 ERRORFILES [dbname.]errortname1, [ CHECKPOINT integer ] [ INDICATORS ] ; CLEAR ; { INTEGERDATE } DATEFORM { ----------- } ; { ANSIDATE } DEF[INE] [ fieldname (data type [,NULLIF [=] value ]) ... [,fieldname (data type [,NULLIF [=] value ])] ] [ { FILE=filename } ] [ { } ] ; [ { INMOD=name } ] END LOADING ; ERRLIMIT n ; HELP ; HELP TABLE tname ; The INSERT statement has two formats: 1. 2. INS[ERT] [INTO] tname.* ; INS[ERT] [INTO] tname (cname [... ,cname])

[dbname.]errortname2

Teradata FastLoad Reference

3 ­ 33

Chapter 3: FastLoad Commands HELP VALUES (:fieldname [... ,fieldname]) ; LOGOFF ; LOG[ON] [tdpid/] username,password [ , 'acctid' ] ; { { NOTIFY { { { OFF --LOW MEDIUM HIGH } } [ EXIT [name] [TEXT "string"] ] } [ MSG [text] ] ; } [ QUEUE [options] ] }

OS oscommand ; QUIT ; RECORD [startrecordnumber] [THRU endrecordnumber] ; SESSIONS n|* [ m|* ] ; { SET RECORD { { { FORMATTED --------UNFORMATTED VARTEXT [ c ] [DISPLAY_ERRORS] [NOSTOP] 0 - 255 ASCII ----KanjiEUC_0U KanjiSJIS_0S } } } ; } } } } ; } }

{ { SET SESSION CHARSET { { { SHOW ; SHOW VERSION[S] ; SLEEP n ; TENACITY n ;

The following DBS/SQL statements are supported by the FastLoad utility (refer to RDBMS Reference Manual for syntax and a complete description of each statement): CREATE TABLE DATABASE dbname ; DEL[ETE] FROM tname [ ALL ] ; DROP TABLE tname ;

Note: Replace "FILE=" with "DDNAME=" for MVS and VM.

3 ­ 34

Teradata FastLoad Reference

Chapter 3: FastLoad Commands HELP TABLE

HELP TABLE

Function

The HELP TABLE command creates a list of field names by querying the Teradata RDBMS and deriving the DEFINE list from the table definition.

Syntax

HELP TABLE dbname.

GK01A011

tname

;

where

Syntax element... Is the name of the...

tname dbname

name of the table to be queried. database in which the table resides.

Usage Notes

The following table describes the things you should consider when using the HELP TABLE command.

Topic Usage Notes

Using DEFINE Commands Using a CLEAR Command

If you use the HELP TABLE command to define field names, you must still use a DEFINE command to specify the input data source name or INMOD routine. When using two HELP TABLE commands in the same FastLoad job, using a CLEAR command before the second one cancels the first. The following command example produces a list of only the Department table: HELP TABLE Employee ; CLEAR ; HELP TABLE Department ;

Teradata FastLoad Reference

3 ­ 35

Chapter 3: FastLoad Commands HELP TABLE

Topic Usage Notes

Using a CLEAR Command (continued)

Entering the two HELP TABLE commands without a CLEAR command, as in the following example, produces a list of both the Employee and Department tables: HELP TABLE Employee ; HELP TABLE Department ;

Example

The following command example builds a list of field names from the table definition for a table named Employee:

HELP TABLE Employee ;

You can then use a SHOW command to verify the field names defined in the HELP TABLE command:

SHOW ;

3 ­ 36

Teradata FastLoad Reference

Chapter 3: FastLoad Commands INSERT

INSERT

Function

INSERT is a Teradata SQL statement that inserts data records into the rows of the FastLoad table. Note: The maximum number of columns in a Teradata RDBMS table is 255.

Syntax

INSERT INS INTO tname . * , ( cname ) values VALUES , ( :fieldname )

GK01A012

; values

where

Syntax element... Is the...

tname cname

name of the table into which rows are inserted. name of column to receive a new row value during the insert operation. For each cname you define, you must specify a corresponding fieldname. You can define a list of column names in any order. You do not have to define them in the same order as they appear in the CREATE TABLE statement.

fieldname

field name that was defined in a previous DEFINE command. During the insert operation, the FastLoad utility inserts the field in the input data record that was assigned to the fieldname into the corresponding column (cname) of the FastLoad table.

.*

wildcard specification that all columns in tname that are used to construct the list of field names be used in the insert operation. When you use the wildcard specification, the FastLoad utility queries the Teradata RDBMS for all of the column names and uses them to construct a valid INSERT statement.

Teradata FastLoad Reference

3 ­ 37

Chapter 3: FastLoad Commands INSERT

Usage Notes

The following table describes the things you should consider when using the INSERT statement.

Topic Usage Notes

Required Privileges Inserting Field Values

To use the INSERT statement, the userid associated with the FastLoad job must have INSERT privilege on the specified table. During the insert operation, field values are inserted in the table in the order in which the columns are listed in the CREATE TABLE statement. If field values in the input data are stored in the same order as columns are defined in the CREATE TABLE statement for the FastLoad table, you do not need to specify a list of column names in the INSERT statement (for instance, INSERT INTO table1 VALUES (:f1, :f2). When you use the second format of the INSERT statement, a list of field names is constructed from the definition of the table. During the insert operation, field names and their data types are taken from the CREATE TABLE statement and used to define the table. The field name definitions are established in the order in which columns are defined in the CREATE TABLE statement. So, the fields in each data record must be in the same order as the columns in the definition of the table.

Using DEFINE Commands

When using the second form of the INSERT statement, you still need to use the DEFINE command to specify the name of the input data source or INMOD routine used in the FastLoad job. If you enter a DEFINE command that defines one or more fields before the INSERT statement, the FastLoad utility appends the field definitions to the definitions constructed from the INSERT statement. (See the command examples that follow.) Note: The colon character preceding the input field name descriptions (:fieldname) indicates that a corresponding DEFINE field must exist. If the INSERT statement does not include: fieldname expressions, then the FastLoad utility transmits the command to the Teradata RDBMS intact, without linking it with a previous DEFINE command.

Using SHOW TABLE Statements

If you want to use a Teradata SQL SHOW TABLE statement to display the exact definition of a table, you must do so from BTEQ or another application. The FastLoad utility does not support this statement.

3 ­ 38

Teradata FastLoad Reference

Chapter 3: FastLoad Commands INSERT

Topic Usage Notes

ANSI/SQL DateTime Specifications

You can use the ANSI/SQL DATE, TIME, TIMESTAMP and INTERVAL DateTime data types in Teradata SQL CREATE TABLE statements, and you can specify them as column/field modifiers in INSERT statements. You must convert them to fixed-length CHAR data types when specifying the column/field names in the FastLoad DEFINE command. Caution: Do not use the tname.* version of an INSERT statement when using Unicode data from: · A KATAKANAEBCDIC session · A session with a character set name ending with _0I · Any session with a character set that does not support multibyte characters (e.g., ASCII, or EBCDIC). In addition to the field names from the referenced tables, these functions return byte/character counts that the FastLoad utility uses internally to construct the USING clause for the subsequent load operation. Because of the byte and character count conversions that take place when importing and exporting CHAR and VARCHAR data between your client system and the Teradata RDBMS, the internally generated USING clause would not properly reflect the structure of your input data stream.

Using Unicode Data

Example 1

The following command example defines the EmpRecs input data source and the fields in each record (Emp_Number and Emp_Name):

DEFINE Emp_Number (INTEGER), Emp_Name (VARCHAR(30)), FILE=EmpRecs ; INSERT INTO Employee (EmpNo, Name) VALUES (:Emp_Number, :Emp_Name) ;

The INSERT statement defines the table and columns to receive new data values. For each data record, the value in the first field (Emp_Number) is inserted in the EmpNum column and the value in the second field (Emp_Name) is inserted in the Name column.

Teradata FastLoad Reference

3 ­ 39

Chapter 3: FastLoad Commands INSERT

Example 2

The following command example establishes a list of field names from the definition of the FastLoad table:

DEFINE FILE=InFile; INSERT OldTable.* ;

The DEFINE command specifies the input data source (InFile) and defines each field to be inserted in the FastLoad table (OldTable).

Example 3

Sometimes the records in an input data source contain data that does not belong in the FastLoad table. If, for example, each record contains 100 bytes of extra data, you can define a dummy field in the DEFINE command that is not referenced in the INSERT statement. The following command example constructs a list of field names that match the current definition of NewTable appended to the definition of the extra data item:

DEFINE ExtraData (CHAR (100)) FILE = InFile; INSERT NewTable.*;

When you use extra data in the input data source with the INSERT TABLE .* feature, the extra data must be located at the beginning of each record. You cannot use this feature if the extra data occurs in the middle or at the end of the records. In this case, you must explicitly define each data item in the data source and each item in the values clause of the INSERT statement.

3 ­ 40

Teradata FastLoad Reference

Chapter 3: FastLoad Commands LOGOFF

LOGOFF

Function

The LOGOFF command ends FastLoad sessions and exits from the Teradata RDBMS. QUIT is a synonym for LOGOFF.

Syntax

LOGOFF QUIT

GK01A013

;

Usage Notes

The following table describes the things you should consider when using the LOGOFF command.

Topic Usage Notes

Pausing FastLoad

If you enter the LOGOFF command after a BEGIN LOADING command, but before the END LOADING command, the FastLoad job pauses and can be restarted later. When a FastLoad job pauses during the loading phase, the Teradata RDBMS locks the tables named in the BEGIN LOADING command. They remain locked until you enter an END LOADING command. When a FastLoad job terminates, the utility returns a code indicating the way the job completed:

This code... Signifies that...

Locked Tables

Terminating Return Codes

0 4 8 12

the job completed normally. a warning condition occurred. Warning conditions do not terminate the job. a user error, such as a syntax error in the FastLoad job script, terminated the job. a fatal error terminated the job. A fatal error is any error other than a user error.

More Information

See "Restarting a Paused FastLoad Job" on page 2-14.

Teradata FastLoad Reference

3 ­ 41

Chapter 3: FastLoad Commands LOGOFF

Example

The following command example ends FastLoad and exits the Teradata RDBMS:

LOGOFF ;

Completion Message

The completion message indicates that the FastLoad job was either paused or terminated, depending on whether the job was in the loading phase or not:

IF the job is... THEN FastLoad responds...

in the loading phase

Logging off all sessions Total processor time used='0.54 Seconds' Start : Tue Feb 27 15:28:43 1996 End : Tue Feb 27 15:28:47 1996 Highest return code encountered = `4'. FDL4818 FastLoad Paused

not in the loading phase

Logging off all sessions Total processor time used='0.54 Seconds' Start : Tue Feb 27 15:28:43 1996 End : Tue Feb 27 15:28:47 1996 Highest return code encountered = `0'. FDL4818 FastLoad Terminated

3 ­ 42

Teradata FastLoad Reference

Chapter 3: FastLoad Commands LOGON

LOGON

Function

The LOGON command establishes one or more FastLoad sessions with the Teradata RDBMS.

Syntax

Standard LOGON Syntax

LOGON tdpid/

username, pasword ,'acctid'

;

GK01B014

Single Sign-on LOGON Syntax

LOGON

;

tdpid/

username , password

,'acctid'

2411A006

where

Syntax element... Is the...

username password

user identifier of up to 30 characters. password associated with the username. A password can have up to 30 characters.

tdpid

optional character string that identifies the name of a TDP. The tdpid string can have from 1 to 8 characters. Note for network-attached systems: The tdpid string can have up to 256 characters and can be a DNS name. The tdpid is the name of the host entered in the network hosts file. If this field is not supplied, tdpid defaults to the TDP established for the user by the system administrator.

Teradata FastLoad Reference

3 ­ 43

Chapter 3: FastLoad Commands LOGON

Syntax element... Is the...

tdpid (continued)

Note for channel-attached systems: The tdpid string must be in the form: TDPn where n is the TDP identifier.

acctid

account associated with the username. An account identifier can have up to 30 characters. If omitted, the FastLoad utility uses the default account identifier defined when the user was created.

Usage Notes

The following table describes the things you should consider when using the LOGON command.

Topic Usage Notes

Logon Parameters

For standard logon, the parameters (tdpid, username, password, and acctid) are used in all sessions established with the Teradata RDBMS. The LOGON command may occur only once. For single sign-on logon, if your Gateway to Teradata RDBMS is configured to use single sign-on (SSO), and you are already logged on to your Teradata client machine, the machine name, user name, and password are not required in the LOGON command. The user name and password combination specified when you logged on to your Teradata client machine are authenticated via network security for a single sign-on such that valid Teradata users will be permitted to log on to the Teradata RDBMS. The use of SSO is strictly optional, unless the Gateway has been configured to accept only SSO-style logons. If you want to connect to a Teradata RDBMS other than the one your are currently logged onto, the TDPid must be included in the LOGON command. If the TDPid is not specified, the default contained in clispb.dat will be used. (Refer to the Teradata RDBMS Database Administration Guide for information about setting defaults.) To be interpreted correctly, the TDPid must be followed by the slash separator (`/') to distinguish the TDPid from a Teradata RDBMS username. For example, to connect to slugger, you would enter one of the following: .LOGON slugger/; .LOGON slugger/,,'acctinfo'; If an account ID is to be used, the optional account ID must be specified in the LOGON command.

3 ­ 44

Teradata FastLoad Reference

Chapter 3: FastLoad Commands LOGON

Topic Usage Notes

Starting FastLoad

The LOGON command starts a FastLoad job and automatically establishes: · Two Teradata SQL sessions · A number of FastLoad sessions

Number of Sessions

The number of FastLoad sessions depends on the system limitations described in "Session Limits" on page 2-28. FastLoad attempts to connect sessions, in groups of 16, until either: · The number of sessions that you specified with a SESSIONS command are connected · An RDBMS 2632 error is returned · Some other session limit is reached Note: If the number that you specify in a SESSIONS command exceeds the number of available sessions, FastLoad logs only the number of available sessions.

Reported Sessions

After you enter a LOGON command, FastLoad reports the number of FastLoad sessions that are logged on. The Teradata SQL sessions are not included in this report.

Example

The following command example logs on user Peterson:

LOGON DBC/peterson,HTims ;

Completion Message

The FastLoad completion message is:

Number of FastLoad sessions connected = 8 FDL4808 LOGON successful

Teradata FastLoad Reference

3 ­ 45

Chapter 3: FastLoad Commands NOTIFY

NOTIFY

Function

The NOTIFY command specifies a user exit or predefined action to be performed whenever certain significant events occur during a FastLoad job. The notify function is especially useful in operator-free environments where job scheduling relies heavily on automation to optimize system performance. For example, by writing an exit in C (without using CLIv2) and using the NOTIFY... EXIT option, you can provide a routine to detect whether a FastLoad job succeeds or fails, how many records were loaded, what the return code was for a failed job, and so on. Note: The FastLoad NOTIFY command applies only to the job that immediately follows it.

Syntax

NOTIFY OFF LOW EXIT name MSG "string " QUEUE option MEDIUM HIGH MSG "string "

FZAPC095

;

TEXT "string "

EXIT name TEXT "string "

where

Syntax element... Specifies...

OFF LOW MEDIUM HIGH EXIT

that no notification of events is to be provided. This is the default. that notification is to be provided for those events signified by "Yes" in the Low Notification Level column of Table 3-2. that notification is to be provided for those events signified by "Yes" in the Medium Notification Level column of Table 3-2. that notification is to be provided for those events signified by "Yes" in the High Notification Level column of Table 3-2. that a user-written exit is to be called at the appropriate time.

3 ­ 46

Teradata FastLoad Reference

Chapter 3: FastLoad Commands NOTIFY

Syntax element... Specifies...

name

the name of a user-supplied library with an entry point named _dynamn. The default library names are: · libnotfyext.dll for Windows · libnotfyext.so for UNIX platforms · NOTFYEXT for VM and MVS platforms.

TEXT "string"

a user-supplied string of up to 80 characters that the FastLoad utility passes to the named exit routine. The string specification must be enclosed in double quote characters ( "). a user-supplied string of up to 16 characters that the FastLoad utility logs on to: · The operator console on channel-attached VM and MVS client systems · The system log or EventLog service on network-attached UNIX or Windows systems The string specification must be enclosed in double quote characters. This service is not available on Windows 95.

MSG "string"

QUEUE

that a queue is to be manipulated via ENQ or DEQ. See the QUEUE Notes topic in "Usage Notes", below, for more details. This option is valid only for MVS.

option

one of the following:

Parameter Description

RNAME SCOPE

A quoted string of up to 255 characters. The default is TRDUSER. · JOB specifies that the QUEUE is local to the job (including all of the job steps). This is the default. · SYSTEMS specifies that the QUEUE is global to all computers in the complex. · SYSTEM specifies that the QUEUE is global to the computer on which it is running.

NOBLOCK

Specifies that if the ENQ blocks for any reason, it must return an error instead. This is a fatal error for the job The default, an implied BLOCK (there is no BLOCK keyword), means that the ENQ will wait for the QUEUE.

Teradata FastLoad Reference

3 ­ 47

Chapter 3: FastLoad Commands NOTIFY Table 3-2 Events That Create Notifications

Notification Level Event Low Medium High Signifies

Initialize File or INMOD open Phase 1 begin

Yes No No

Yes No Yes

Yes Yes Yes

Successful processing of the NOTIFY command Successful processing of the DEFINE command Beginning of the insert phase, as specified by the INSERT statement Checkpoint information has been written to the restart log table Successful processing of the CHECKPOINT LOADING END request after the end of the insert phase The END LOADING command is about to be sent to the Teradata RDBMS Successful processing of the END LOADING command Successful processing of the SEL COUNT(*) request for the first error table Successful processing of the SEL COUNT(*) request for the second error table A crash error from the Teradata RDBMS or the CLIv2 A CLIv2 error A Teradata RDBMS error that will terminate the FastLoad utility The FastLoad utility is terminating

Checkpoint Phase 1 end

No No

No Yes

Yes Yes

Phase 2 begin

No

Yes

Yes

Phase 2 end Error table 1

No No

Yes No

Yes Yes

Error table 2

No

No

Yes

Teradata RDBMS Restart CLIv2 error RDBMS error Exit

No Yes Yes Yes

Yes Yes Yes Yes

Yes Yes Yes Yes

3 ­ 48

Teradata FastLoad Reference

Chapter 3: FastLoad Commands NOTIFY

Usage Notes

The following table describes the things you should consider when using the NOTIFY command.

Topic Usage Notes

QUEUE Notes

When you specify QUEUE with LOW option, the following takes place:

1

When NOTIFY is processed, it performs an ENQ upon a QUEUE with RNAME of `TRDUSER' and a scope of `JOB'. This call blocks until it acquires the QUEUE. After the job gets the QUEUE, it continues until it reaches a specific point (such as the request completes) when it releases the QUEUE by performing a DEQ.

2

Error Handling

When an error occurs, NOTIFY behaves as follows: · When NOTIFY is processed, the subsystems used by FastLoad are initialized and, if necessary, any user exits are loaded and a call is made to initialize the system log (or an ENQ is performed). · If initialization fails, a warning message is issued and processing continues. The exception to this rule is the case where a `NOTIFY ... QUEUE' is issued. If initialization (the ENQ call) fails, it is a fatal error. · If anything fails after initialization, the request fails. If a user exit returns anything other than 0, a failure is indicated and the job stops.

Restarts

The following points pertain to restarts related to NOTIFY: · If a FastLoad job ends abnormally or unsuccessfully, it can be restarted and some NOTIFY-related activities are reexecuted. This is an important issue with respect to writing user exits · If a FastLoad job ends abnormally or unsuccessfully while it is holding a queue (using the QUEUE type parameter under MVS), it releases the queue before exiting the job. Therefore, when the job restarts, you must ensure that it again acquires the queue before it continues processing.

Teradata FastLoad Reference

3 ­ 49

Chapter 3: FastLoad Commands NOTIFY

Topic Usage Notes

Creating Exit Modules

When creating an exit module, the following general procedures are constant across all operating systems: · The exit must be named _dynamn. · Success is indicated by the return of a 0 (long integer format). · Failure is indicated by the return of a nonzero value (long integer format). You can use different integers to indicate different errors. · The parameter to the procedure is a pointer to a variable record structure. Note: See "Notify Exit Routine Example" on page C-24 for a definition of the variable record structure. · A C prototype example for an exit procedure might be as follows (using a FastLoad example): long _dynamn(FLNotifyExitParm *P) The procedures for creating and using an exit module are the same as for creating and using an INMOD routine, as described in "Using INMOD and Notify Exit Routines" on page 2-30 and Appendix D of this manual.

Message Examples

Following are example messages for FastLoad events. In each case, the message is preceded by the text string specified with the MSG option of the NOTIFY command.

FastLoad Event Message

Notify processed Phase 1 is about to begin Each checkpoint When a data file is about to be opened Phase 1 completes successfully Phase 2 completes successfully

- FastLoad notify processed. - FastLoad phase one starting for table [N] XXXXXXX.XXXXXXX. - FastLoad checkpoint complete: NNNNNNNNNN records sent. - FastLoad opening file filename

- FastLoad phase one completes: NNNNNNNNN records sent. - FastLoad phase two completes: NNNNNNNNN records sent.

3 ­ 50

Teradata FastLoad Reference

Chapter 3: FastLoad Commands OS

OS

Function

The OS command submits an operating system command to your client environment during a FastLoad session.

Syntax

OS command ;

GK01B015

where

Syntax element... Is ...

command

any command that is valid for your client operating system.

Usage Notes

The OS command must terminate with a semicolon.

UNIX Examples

The table below shows examples of using the OS command on a UNIX client system.

Command Example

ls

The following command example lists the files in your directory on the UNIX operating system and then returns you to the FastLoad utility: OS ls ;

Teradata FastLoad Reference

3 ­ 51

Chapter 3: FastLoad Commands OS

Command Example

exec sh

The following command example accesses the UNIX shell: OS exec sh ; Then, at your client system prompt, you can enter other UNIX commands, such as: $ pg myfile.one $ cp oldfile newfile $ cd draft Press Ctrl + D to exit the UNIX environment and return to the FastLoad utility.

Windows Examples

The table below shows examples of using the OS command on a Windows client system.

Command Example

dir

The following command example lists the files in your directory on the Windows operating system and then returns you to the FastLoad utility: OS dir;

command

The following command example accesses the Windows operating system: OS command; At your client system prompt, you can enter other commands, such as: c:\teradata\bin>type myfile.one c:\teradata\bin>edit myfile.one c:\teradata\bin>exit Enter exit to exit the Windows environment and return to the FastLoad utility.

3 ­ 52

Teradata FastLoad Reference

Chapter 3: FastLoad Commands OS

VM Example

The table below shows an example procedure for using the OS command on a VM client system.

Command Example

CMS Setup Procedure

Before using the OS command on VM, you must set up CMS to run the FastLoad utility interactively, as in the following example: +++++++Start of CMS Procedure+++++++ FILEDEF SYSIN FILEDEF SYSTERM RECFM VBA FASTLOAD TERMINAL DISK fn ft fm (LRECL 137

FILEDEF SYSPRINT TERMINAL

<options>

+++++++End of CMS Procedure+++++++++ CMS Setup Procedure (continued) where · · · · fn is the name of the SYSTERM output file. ft is the type of SYSTERM output file. fm is the mode of system output file. <options> is the FastLoad options.

Note: FILEDEFs for input data sources can be assigned interactively using the FastLoad OS command. CMS Command After you have set up CMS to run the FastLoad utility interactively, you can use the FastLoad OS command to enter any valid CMS command: OS <CMS command>;

Teradata FastLoad Reference

3 ­ 53

Chapter 3: FastLoad Commands OS

MVS Example

The table below shows an example procedure for using the OS command on an MVS client system.

Command Example

TSO Setup Procedure

Before using the OS command on MVS, you must set up TSO to run the FastLoad utility interactively, as in the following example: ++++++Start of CLIST Procedure++++++ PROC 0 FASTLIB('<System Loadlib>') SASCLIB('<SAS/C Linklib>') SYSOUT('<unit>') CONTROL NOMSG PROMPT NOFLUSH /* IN CASE PREVIOUSLY ALLOCATED - FREE FILES */ FREE FI(CTRANS SYSABEND SYSOUT SYSPRINT SYSTERM SYSIN) /* ALLOCATE FILES */ ALLOC FI(CTRANS) DA('&SASCLIB') SHR ALLOC FI(SYSABEND) SYSOUT(&SYSOUT) ALLOC FI(SYSOUT) ALLOC FI(SYSTERM) ALLOC FI(SYSIN) /* RUN FASTLOAD */ CALL '&FASTLIB(FASTLOAD)' /* FREE ALLOCATED FILES */ FREE FI(CTRANS SYSABEND SYSOUT SYSPRINT SYSTERM SYSIN) +++++++End of CLIST Procedure+++++++ where · <System Loadlib> is the fully qualified name of the load library containing FASTLOAD and CLIV2 components. Note: Unlike batch, TSO does not support concatenated load libraries. · · <SAS/C Linklib>is the fully qualified name of the SAS/C link library. <unit> is the sysout output class. SYSOUT(&SYSOUT) SYSOUT(&SYSOUT) DA(*) -

ALLOC FI(SYSPRINT) DA(*)

Note: Input datasets can be allocated and freed.

3 ­ 54

Teradata FastLoad Reference

Chapter 3: FastLoad Commands OS

Command Example

TSO Command

After you have set up TSO to run the FastLoad utility interactively, you can use the FastLoad OS command to enter any valid TSO command: OS <TSO command>;

Teradata FastLoad Reference

3 ­ 55

Chapter 3: FastLoad Commands QUIT

QUIT

Function

The QUIT command ends FastLoad sessions and exits from the Teradata RDBMS. LOGOFF is a synonym for QUIT.

Syntax

QUIT LOGOFF

GK01A016

;

Usage Notes

The following table describes the things you should consider when using the QUIT command.

Topic Usage Notes

Pausing FastLoad

If you enter the QUIT command after a BEGIN LOADING command, but before the END LOADING command, the FastLoad job pauses, and can be restarted later. When a FastLoad job pauses during the loading phase, the Teradata RDBMS locks the tables named in the BEGIN LOADING command. They remain locked until you enter an END LOADING command. When a FastLoad job terminates, the utility returns a code indicating the way the job completed:

This code... Signifies that...

Locked Tables

Terminating Return Codes

0 4 8 12

the job completed normally. a warning condition occurred. Warning conditions do not terminate the job. a user error, such as a syntax error in the FastLoad job script, terminated the job. a fatal error (any error other than a user error), terminated the job.

More Information

See "Restarting a Paused FastLoad Job" on page 2-14 for more information about restarting a paused job.

3 ­ 56

Teradata FastLoad Reference

Chapter 3: FastLoad Commands QUIT

Example

The following command example ends FastLoad and exits the Teradata RDBMS:

QUIT ;

Completion Message

The completion message indicates that the FastLoad job was either paused or terminated, depending on whether the job was in the loading phase or not:

IF the job is... THEN FastLoad responds...

in the loading phase

Logging off all sessions Total processor time used='0.54 Seconds' Start : Tue Feb 27 15:28:43 1996 End : Tue Feb 27 15:28:47 1996 Highest return code encountered = `0'. FDL4818 FastLoad Paused

not in the loading phase

Logging off all sessions Total processor time used='0.54 Seconds' Start : Tue Feb 27 15:28:43 1996 End : Tue Feb 27 15:28:47 1996 Highest return code encountered = `0'. FDL4818 FastLoad Terminated

Teradata FastLoad Reference

3 ­ 57

Chapter 3: FastLoad Commands RECORD

RECORD

Function

The RECORD command defines the records of the input data source at which FastLoad processing starts and ends.

Syntax

RECORD startrecordnumber THRU endrecordnumber

GK01A024

;

where

Syntax element... Is the...

startrecordnumber

record at which processing begins. The default is record 1.

THRU endrecordnumber

keyword that introduces the optional endrecordnumber parameter. record after which processing ends. The endrecordnumber number must be equal to or greater than startrecordnumber.

Usage Notes

The following table describes the things you should consider when using the RECORD command.

Topic Usage Notes

Entering the RECORD Command

You must enter the RECORD command before the INSERT statement in your FastLoad job. If you do not use a RECORD command, FastLoad reads from the first record in the data source to the last record, unless the job is restarted. When a job restarts, if the CHECKPOINT option is enabled, the utility begins reading at the next record immediately after the last checkpointed record.

Restarting FastLoad Jobs

3 ­ 58

Teradata FastLoad Reference

Chapter 3: FastLoad Commands RECORD

Topic Usage Notes

Invalid Record Numbers

The RECORD command cannot specify invalid record numbers, such as: · An endrecordnumber less than a startrecordnumber · A negative value If you specify an invalid record number, the FastLoad utility returns an error message:

IF the error occurs... THEN...

before the BEGIN LOADING command after BEGIN LOADING

all FastLoad sessions are logged off and you exit the utility. the job pauses (all FastLoad sessions are logged off and the tables named in BEGIN LOADING remain locked until END LOADING is executed).

The THRU Specification

If you do not specify the THRU endrecordnumber parameter, FastLoad begins to read at startrecordnumber and continues until it finds the last record in the data source.

Example 1

The following command example specifies the records in the input data source starting at record 1,000 and stopping at record 20,000:

RECORD 1000 THRU 20000 ;

Completion Message

The FastLoad completion message is:

Starting record number set to :1000 Ending record number set to :20000

Example 2

The following command example specifies the records in the input data source starting at record number 1 and stopping at record number 50,000:

RECORD THRU 50000 ;

Teradata FastLoad Reference

3 ­ 59

Chapter 3: FastLoad Commands RECORD

Completion Message

The FastLoad completion message is:

Starting record number set to :1 Ending record number set to :50000

3 ­ 60

Teradata FastLoad Reference

Chapter 3: FastLoad Commands SESSIONS

SESSIONS

Function

The SESSIONS command specifies how many FastLoad sessions will be logged on when you enter a LOGON command and, optionally, the minimum number of sessions required to run the job.

Syntax

SESSIONS max * min *

GK01B018

;

where

Syntax element... Specifies...

max

the maximum number of sessions to log on. The max specification must be greater than zero. The default, if you do not use the SESSIONS command, is one session for each AMP.

min

the minimum number of sessions required for the job to continue. The min specification must be greater than zero. The default, if you do not use the SESSIONS command, is 1.

*

the minimum and maximum number of sessions. Using the asterisk character as the max specification logs on for the maximum number of sessions--one for each AMP. Using the asterisk character as the min specification logs on for at least one session, but less than or equal to the max specification. Note 1: Specifying SESSIONS * * has the same effect as not using the SESSIONS command at all.

Teradata FastLoad Reference

3 ­ 61

Chapter 3: FastLoad Commands SESSIONS

Syntax element... Specifies...

* (continued)

Note 2: On large to very large Teradata RDBMS configurations, the default of one session per AMP may be inappropriate. There is no general method to determine the optimal number of sessions, because it is dependent on several factors, including, but not limited to: · · · · · Teradata RDBMS performance and workload Client platform type, performance, and workload Channel performance, for channel-attached systems Network topology and performance, for network-attached systems Volume of data to be processed by the application

Using too few sessions is likely to unnecessarily limit throughput. On the other hand, using too many sessions can increase session management overhead (and also reduce the number of sessions available to any other applications) and may, in some circumstances, degrade throughput. Regardless of the size of the Teradata RDBMS configuration, for large repetitive production applications, it will usually be appropriate to experiment with several different session configurations to determine the best trade-off between resource utilization and throughput performance. For larger Teradata RDBMS configurations, it is appropriate to establish an installation default for the maximum number of sessions that is less than one session per AMP. This can be done either via the installation configuration file (see "The FastLoad Configuration File" on page 2-20) or via a standard runtime parameter (see "FastLoad Runtime Parameters" on page 2-4). An installation default for number of sessions, if specified in the configuration file, can be overridden in individual FastLoad job scripts, when necessary.

Usage Notes

The following table describes the things you should consider when using the SESSIONS command.

3 ­ 62

Teradata FastLoad Reference

Chapter 3: FastLoad Commands SESSIONS

Topic

Usage Notes

Entering the SESSIONS Command Session Number Limits

You must enter the SESSIONS command before the LOGON command in your FastLoad job. Regardless of the number of sessions you specify, the actual number of sessions FastLoad uses is limited to the number of AMPs available on the Teradata RDBMS. Thus, there is no guarantee that the number of sessions specified in the command will actually be logged on. FastLoad reports the number of sessions logged on when a LOGON command is executed. The maximum relevant number of sessions you can specify is 32767. The FastLoad utility disregards any larger number and logs on for as many sessions as it can--one session per available AMP, as indicated in the FastLoad error message: FDL4867 Invalid number of sessions requested FastLoad will log on as many sessions as possible

Reported Number of Sessions Invalid Number of Sessions

Example 1

The following example specifies five FastLoad sessions:

SESSIONS 5 ;

Example 2

The following example specifies ten FastLoad sessions with a minimum of five:

SESSIONS 10 5 ;

Completion Message

The FastLoad completion message is:

FDL4866 SESSIONS command accepted

Error Message

If you specify an invalid value, FastLoad responds with the following error message:

FDL4867 Invalid number of sessions requested FastLoad will log on as many sessions as possible.

Teradata FastLoad Reference

3 ­ 63

Chapter 3: FastLoad Commands SET RECORD

SET RECORD

Function

The SET RECORD command specifies the format of the input data as either: · · · · · Formatted Unformatted Binary Text Variable-length text

Note: The SET RECORD command: · · · Applies only to network-attached systems Can be specified only one time in your FastLoad job script When specified, must be appear before the DEFINE command

Syntax

SET RECORD FORMATTED UNFORMATTED BINARY TEXT VARTEXT 3 "c " DISPLAY_ERRORS NOSTOP

GK01C019

;

where

Syntax element... Is the...

FORMATTED

keyword specification that the input data source is in Teradata RDBMS standard format. This is the default specification, if you do not use the SET RECORD command in your FastLoad job script. keyword specification that the input data source deviates from Teradata RDBMS standard format. Unformatted data originates from other sources, such as dBASEIII or Lotus 1-2-3.

UNFORMATTED

3 ­ 64

Teradata FastLoad Reference

Chapter 3: FastLoad Commands SET RECORD

Syntax element... Is the...

BINARY

keyword specification that the input data source is in binary format. The format must be a 2-byte integer, n, followed by n bytes of data. keyword specification that the input data source is in text format. The format must be an arbitrary number of bytes, followed by an end-of-record marker, which a: · · Linefeed (x'0A) on UNIX platforms Carriage-return/linefeed pair (X'0D0A') on Windows platforms

TEXT

VARTEXT

keyword specification that the input data source is in variablelength text record format, with each field separated by a delimiter character. optional specification of the delimiter character that separates fields in the variable-length text records of the input data source. The default, if you do not use a "c" specification, is the pipe character ( | ).

"c"

DISPLAY_ERRORS NOSTOP

optional keyword specification that writes input data records that produce errors to the standard error file. optional keyword specification that inhibits the FastLoad termination in response to an error condition associated with a variable-length text record.

Usage Notes

The following table describes the things you should consider when using the SET RECORD command.

Topic Usage Notes

Data Formats

The input source data can be either in text or binary format, where · Text format is a data source containing characters for display on an ASCII terminal. · Binary format is numbers in hexadecimal

Unformatted Records

When you specify UNFORMATTED, the FastLoad utility assumes nothing concerning the structure of the data, end of record delimiters, special characters and field length indicators. The input data can be either text or binary, and you must:

Teradata FastLoad Reference

3 ­ 65

Chapter 3: FastLoad Commands SET RECORD

Topic Usage Notes

Unformatted Records (continued)

Use both an INSERT statement and a DEFINE command to define the fields For binary data, manually insert the indicator bytes preceding each record The FastLoad utility then uses the DEFINE clause as a guide to calculate the actual length of each record. Data that is extraneous and not intended for use can be defined as CHAR. Note: For ASCII data, line ending characters can differ from platform to platform. For example, some systems might only use a carriage return character, while others might use both a carriage return and a line feed character to end a line. Always consider the platform-dependent characteristics when reading ASCII data from a text file.

VARTEXT Records

When you specify VARTEXT, the FastLoad utility assumes that the input data is variable-length text fields separated by a field delimiter character. The utility parses each input data record on a field by field basis, and creates a VARCHAR field for each input text field.

Data Type Specifications

When using the VARTEXT specification, VARCHAR and VARBYTE are the only valid data type specifications you can use in the FastLoad DEFINE command.

Null Fields

Two consecutive delimiter characters direct the FastLoad utility to null the field corresponding to the one right after the first delimiter character. Also, if the last character in a record is a delimiter character, and yet there was at least one more field to be processed, the FastLoad utility nulls the field corresponding to the next one to be processed, as defined in the DEFINE command.

Input Record Requirements

The total number of fields in each input record must be equal to or greater than the number of fields described in the FastLoad DEFINE command. If it is less, the FastLoad utility generates an error message. If it is more, the Teradata RDBMS ignores the extra fields. The last field of a record does not have to end with a delimiter character. It can end with a delimiter character, but it is not required.

3 ­ 66

Teradata FastLoad Reference

Chapter 3: FastLoad Commands SET RECORD

Topic Usage Notes Error Record Handling

VARTEXT Records (continued)

When the FastLoad utility encounters an error condition in an input record, it normally discards the record and terminates. When loading variable-length text records, you can inhibit either or both of these functions by specifying the error handling options: · DISPLAY_ERRORS · NOSTOP By specifying both options and redirecting STDERR to a file location instead of your terminal screen, your FastLoad job will run to completion and save all the error records. Then you can manually modify them and use another utility such as BTEQ or MultiLoad to load them into the table.

Variable-length Fields

For Formatted and Unformatted Records

When using variable-length fields in either formatted or unformatted records, you must either: · Include a two-byte binary integer indicator immediately preceding each variable-length field. The FastLoad utility uses this indicator to determine the exact length of the field. · Pad each variable-length field with blanks to produce fixedlength fields In either case, you cannot exceed the maximum field length as shown in the table definition. DEFINE and INSERT Specifications

For Variable-length Data in Formatted and Unformatted Records

Use VARCHAR specifications in the DEFINE command and INSERT statements for variable-length data: User.Table Definition Name Co1001 Co1002 Co1003 define Type Integer Varchar(8) Date Size 4 bytes up to 8 bytes 4 bytes

Co1001 (integer), Co1002 (Varchar(8)), Co1003 (date)

file = file_path ;

Teradata FastLoad Reference

3 ­ 67

Chapter 3: FastLoad Commands SET RECORD

Topic Usage Notes

DEFINE and INSERT Specifications (continued)

insert into User.Table values ( :Co1001, :Co1002, :Co1003 ) ; To pad a variable-length field to the maximum used in the table definition, in this case eight bytes, define column 2 as Char(8) with the table definition remaining Varchar(8).

For Fixed-length Data in Formatted and Unformatted Records

The following table (User.Table) contains three columns of fixedlength data types. Each record has four bytes as an integer, followed by eight bytes of characters, and then four bytes of a date in integer format:

For Variable-length Data in Formatted and Unformatted Records (continued)

User.Table Definition Name Co1001 Co1002 Co1003 Type Integer Char(8) Date Size 4 bytes 8 bytes 4 bytes

Assuming that the fields in the record correspond exactly to the table columns, the DEFINE command and INSERT statement specifications would be: define Co1001 (integer), Co1002 (char(8)), Co1003 (date) file = file_path ; insert into User.Table values ( :Co1001, :Co1002, :Co1003 ) ; Co1001 Co1002 Co1003 |00030506|4549474854202020|000CFD1F The DEFINE and INSERT specifications to define undesirable data (such as special control characters or carriage returns using HEX 0A as end of record delimiters) would be:

3 ­ 68

Teradata FastLoad Reference

Chapter 3: FastLoad Commands SET RECORD

Topic Usage Notes

DEFINE and INSERT Specifications (continued)

defineDummy(char(8)), Co1001 Co1002 Co1003 (integer), (char(8)), (date),

Newline(char(1)) file = file_path ; insert into User.Table values ( :Co1001, :Co1002, :Co1003 ) ; Control Char Co1001 Co1002 Co1003 0FCA037CB86BFF8A|00030506|4549474854202020|000C FD1F|0A New line

For Variable-length Data in Formatted and Unformatted Records (continued)

Note, however, that on some systems, you might need to use a Newline(char(2)) specification instead of Newline(char(1)).

Example

The following command example sets records to unformatted mode:

set record unformatted ;

Completion Message

The FastLoad completion message is:

Now set to read "UNFORMATTED" records.

Teradata FastLoad Reference

3 ­ 69

Chapter 3: FastLoad Commands SET SESSION CHARSET

SET SESSION CHARSET

Function

The SET SESSION CHARSET command specifies which character set is in effect during a specific FastLoad invocation. Note: The SET SESSION CHARSET command applies only to networkattached systems. For channel-attached systems, see "Invoking FastLoad" on page 2-2 for a description of the CHARSET parameter.

Syntax

SET SESSION CHARSET " ASCII KANJIEUC _ 0U KANJISJIS _ 0S charsetcode " ;

GK01D021

where

Syntax element... Specifies the...

ASCII KANJIEUC_0U KANJISJIS_0S charsetcode

ASCII character set. kanji Extended UNIX Code character set. combined JIS-x0208 and JIS-x0201 character set developed by Microsoft. character set code in the range of 0 through 255. For example, 118 is the code for KanjiEUC_0U and 119 is the code for KanjiSJIS_0S.

3 ­ 70

Teradata FastLoad Reference

Chapter 3: FastLoad Commands SET SESSION CHARSET

Usage Notes

The following table describes the things you should consider when using the SET SESSION CHARSET command.

Topic Usage Notes

Command Placement Double Quote Characters in Character Set Name Specifications Priority of Character Set Specifications

The SET SESSION CHARSET command must appear before the LOGON command in your FastLoad job script. The names of the character sets must be enclosed in double quote characters. The charsetcode numeric, 0-255, must not be enclosed in double quotes. The order in which the character set is determined for a FastLoad job is as follows:

1

User specified by a: · · · Runtime parameter (This specification has the highest priority on all supported platforms.) SET SESSION CHARSET command on networkattached systems CHARSET parameter in the FastLoad configuration file

2

System Parameter Block (SPB) specified by the: · · HSHSPB on channel-attached systems clispb.dat file on network-attached systems

3

Teradata RDBMS default, determined by a query from the FastLoad utility

Teradata FastLoad Reference

3 ­ 71

Chapter 3: FastLoad Commands SHOW

SHOW

Function

The SHOW command displays active definitions for the input data source or INMOD routine and the field names that were established by one or more DEFINE commands.

Syntax

SHOW ;

GK01A022

Usage Notes

The SHOW command displays: · · · · · Field names Data type (integer or character) of each field Internal length of each field Offset of each field within the input record Input data source or INMOD routine names

If you have not entered a DEFINE command, the result is blank and the FastLoad utility responds with this message:

TOTAL RECORD LENGTH = 0

Note: The SHOW command is processed by the FastLoad utility. It is not transmitted to the Teradata RDBMS.

Example

When the following DEFINE command is active:

DEFINE EmpNo (smallint), Name (varchar(12)), DeptNo (decimal (3,0)), JobTitle (varchar(12)), Salary (decimal(8,2)), YrsExp (byteint), DOB (date), Sex (char(1)),

3 ­ 72

Teradata FastLoad Reference

Chapter 3: FastLoad Commands SHOW Race (char(1)), MStat (char(1)), EdLev (byteint), HCap (byteint) FILE=EmpData ;

The FastLoad response to the SHOW command is:

FILE = EmpData EMPNO OFFSET= NAME OFFSET = DEPTNO OFFSET= JOBTITLE OFFSET= SALARY OFFSET= YRSEXP OFFSET= DOB OFFSET= SEX OFFSET= RACE OFFSET= MSTAT OFFSET= EDLEV OFFSET= HCAP OFFSET= TOTAL RECORD LENGTH=

0 2 16 18 32 36 37 41 42 43 44 45 46

LEN LEN LEN LEN LEN LEN LEN LEN LEN LEN LEN LEN

= = = = = = = = = = = =

2 12 2 12 4 1 4 1 1 1 1 1

SMALLINT VARCHAR DECIMAL VARCHAR DECIMAL BYTEINT DATE CHAR CHAR CHAR BYTEINT BYTEINT

Teradata FastLoad Reference

3 ­ 73

Chapter 3: FastLoad Commands SHOW VERSIONS

SHOW VERSIONS

Function

The SHOW VERSIONS command displays the current level of all FastLoad utility software modules.

Syntax

SHOW VERSIONS VERSION

GK01B023

;

Usage Notes

Use the SHOW VERSIONS command to retrieve the version information when reporting software problems.

Example

The following command example displays the current versions of software modules used at your site:

SHOW VERSIONS ;

For network-attached systems, the utility displays:

FastLoad Version 07.01.00 for UNIX 5.4 running Streams TCP/IP FastLoad : rr.mm.xx.xx FastCmds : rr.mm.xx.xx FastIO : rr.mm.xx.xx FastMBCS : rr.mm.xx.xx FastNtfy : rr.mm.xx.xx FastPars : rr.mm.xx.xx FastSQL : rr.mm.xx.xx FastUtil : rr.mm.xx.xx pmProcs : rr.mm.xx.xx CLIV2 : rr.mm.xx.xx MTDP : rr.mm.xx.xx MOSIos : rr.mm.xx.xx MOSIDEP : rr.mm.xx.xx OSENCRYPT : N/A OSERR : rr.mm.xx.xx

3 ­ 74

Teradata FastLoad Reference

Chapter 3: FastLoad Commands SHOW VERSIONS

where

Reported value ... Is the...

rr mm xx

major release number. minor release number. maintenance release number.

Note: The version numbers may differ from module to module.

Teradata FastLoad Reference

3 ­ 75

Chapter 3: FastLoad Commands SLEEP

SLEEP

Function

The SLEEP command specifies the number of minutes that the FastLoad utility pauses before retrying a logon operation when the maximum number of load operations are already running on the Teradata RDBMS.

Syntax

SLEEP minutes ;

GK01A039

where

Syntax element... Specifies the...

minutes

number of minutes that FastLoad pauses before retrying the logon operation. The minutes specification must be greater than zero. If you enter zero, the FastLoad utility responds with an error message and terminates. The FastLoad utility default, if you do not use the SLEEP command, is 6 minutes.

Usage Notes

The following table describes the things you should consider when using the SLEEP command.

Topic Usage Notes

Function

The SLEEP specification works with the TENACITY specification to control FastLoad logon attempts. When the FastLoad utility tries to log on for a new session, and the Teradata RDBMS indicates that the maximum number of load sessions are already running, the FastLoad utility: · Logs off any new sessions that were logged on · Waits for six minutes, by default, or for the amount of time specified by the SLEEP command · Then tries to log on to the Teradata RDBMS again

3 ­ 76

Teradata FastLoad Reference

Chapter 3: FastLoad Commands SLEEP

Topic Usage Notes

Function (continued) Command Placement

The FastLoad utility repeats this process until it has either logged on for the required number of sessions or exceeded amount of time specified by the TENACITY command. Since they effect the logon operation, you must state both the SLEEP and TENACITY commands before the LOGON command in your FastLoad job script. If your FastLoad job script states the SLEEP or TENACITY command after the LOGON command, the utility terminates with an error message. The SLEEP and TENACITY command specifications override the corresponding SLEEP and TENACITY specifications that may be made in the FastLoad configuration file. Similarly, the SLEEP and TENACITY command specifications themselves are overridden by the corresponding specifications made as runtime parameters when you invoke the FastLoad utility. The order of preferences for the SLEEP and TENACITY specifications, from highest to lowest, is: 1--Runtime parameters 2--FastLoad script commands 3--FastLoad configuration file specifications 4--FastLoad default values

Command Overrides

Teradata FastLoad Reference

3 ­ 77

Chapter 3: FastLoad Commands TENACITY

TENACITY

Function

The TENACITY command specifies the number of hours that the FastLoad utility continues trying to log on when the maximum number of load operations are already running on the Teradata RDBMS.

Syntax

TENACITY hours ;

GK01A040

where

Syntax element... Specifies the...

hours

number of hours that FastLoad continues trying to log on. The hours specification must be greater than zero. If you enter zero, the FastLoad utility responds with an error message and terminates.

Usage Notes

The following table describes the things you should consider when using the TENACITY command.

Topic Usage Notes

Function

The TENACITY specification works with the SLEEP specification to control FastLoad logon attempts. When the FastLoad utility tries to log on for a new session, and the Teradata RDBMS indicates that the maximum number of load sessions are already running, the FastLoad utility: · Logs off any new sessions that were logged on · Waits for six minutes, by default, or for the amount of time specified by the SLEEP command · Then tries to log on to the Teradata RDBMS again The FastLoad utility repeats this process until it has either logged on for the required number of sessions or exceeded amount of time specified by the TENACITY command.

3 ­ 78

Teradata FastLoad Reference

Chapter 3: FastLoad Commands TENACITY

Topic Usage Notes

Function (continued)

Note: The utility default for TENACITY is no tenacity. You must use either a FastLoad configuration file entry, the runtime parameter or a TENACITY command in your FastLoad job script to enable the tenacity feature for your FastLoad logon operation. Since they effect the logon operation, you must state both the TENACITY and SLEEP commands before the LOGON command in your FastLoad job script. If your FastLoad job script states the TENACITY or SLEEP command after the LOGON command, the utility terminates with an error message. The TENACITY and SLEEP command specifications override the corresponding TENACITY and SLEEP specifications that may be made in the FastLoad configuration file. Similarly, the TENACITY and SLEEP command specifications themselves are overridden by the corresponding specifications made as runtime parameters when you invoke the FastLoad utility. The order of preferences for the TENACITY and SLEEP specifications, from highest to lowest, is: 1--Runtime parameters 2--FastLoad script commands 3--FastLoad configuration file specifications 4--FastLoad default values

Command Placement

Command Overrides

Teradata FastLoad Reference

3 ­ 79

Chapter 3: FastLoad Commands TENACITY

3 ­ 80

Teradata FastLoad Reference

Appendix A:

How to Read Syntax Diagrams

This appendix describes the conventions that apply to reading the syntax diagrams used in this book.

Teradata FastLoad Reference

A­1

Appendix A: How to Read Syntax Diagrams Syntax Diagram Conventions

Syntax Diagram Conventions

Notation Conventions

The following table defines the notation used in this section:

Item Definition / Comments

Letter Number

An uppercase or lowercase alphabetic character ranging from A through Z. A digit ranging from 0 through 9. Do not use commas when entering a number with more than three digits.

Word

Variables and reserved words.

IF a word is shown in... THEN it represents...

UPPERCASE LETTERS

a keyword. Syntax diagrams show all keywords in uppercase, unless operating system restrictions require them to be in lowercase. If a keyword is shown in uppercase, you may enter it in uppercase or mixed case.

lowercase letters lowercase italic letters

a keyword that you must enter in lowercase, such as a UNIX command. a variable such as a column or table name. You must substitute a proper value.

lowercase bold letters UNDERLINED LETTERS

a variable that is defined immediately following the diagram that contains it. the default value. This applies both to uppercase and to lowercase words.

Spaces Punctuation

Use one space between items, such as keywords or variables. Enter all punctuation exactly as it appears in the diagram.

A­2

Teradata FastLoad Reference

Appendix A: How to Read Syntax Diagrams Syntax Diagram Conventions

Paths

The main path along the syntax diagram begins at the left, and proceeds, left to right, to the vertical bar, which marks the end of the diagram. Paths that do not have an arrow or a vertical bar only show portions of the syntax. The only part of a path that reads from right to left is a loop. Paths that are too long for one line use continuation links. Continuation links are small circles with letters indicating the beginning and end of a link:

A

A

FE0CA002

When you see a circled letter in a syntax diagram, go to the corresponding circled letter and continue.

Required Items

Required items appear on the main path:

SHOW

FE0CA003

If you can choose from more than one item, the choices appear vertically, in a stack. The first item appears on the main path:

SHOW CONTROLS VERSIONS

FE0CA005

Optional Items

Optional items appear below the main path:

SHOW CONTROLS

FE0CA004

Teradata FastLoad Reference

A­3

Appendix A: How to Read Syntax Diagrams Syntax Diagram Conventions

If choosing one of the items is optional, all the choices appear below the main path:

SHOW CONTROLS VERSIONS

FE0CA006

You can choose one of the options, or you can disregard all of the options.

Abbreviations

If a keyword or a reserved word has a valid abbreviation, the unabbreviated form always appears on the main path. The shortest valid abbreviation appears beneath.

SHOW CONTROLS CONTROL

FE0CA042

In the above syntax, the following formats are valid: · · SHOW CONTROLS SHOW CONTROL

Loops

A loop is an entry or a group of entries that you can repeat one or more times. Syntax diagrams show loops as a return path above the main path, over the item or items that you can repeat.

, , ( cname 4 )

JC01B012

3

The following rules apply to loops:

IF... THEN...

there is a maximum number of entries allowed

the number appears in a circle on the return path. In the example, you may enter cname a maximum of 4 times.

A­4

Teradata FastLoad Reference

Appendix A: How to Read Syntax Diagrams Syntax Diagram Conventions

IF... THEN...

there is a minimum number of entries required

the number appears in a square on the return path. In the example, you must enter at least 3 groups of column names.

a separator character is required between entries

the character appears on the return path. If the diagram does not show a separator character, use one blank space. In the example, the separator character is a comma.

a delimiter character is required around entries

the beginning and end characters appear outside the return path. Generally, a space is not needed between delimiter characters and entries. In the example, the delimiter characters are the left and right parentheses.

Excerpts

Sometimes a piece of a syntax phrase is too large to fit into the diagram. Such a phrase is indicated by a break in the path, marked by | terminators on either side of the break. A name for the excerpted piece appears between the break marks in boldface type. The named phrase appears immediately after the complete diagram, as illustrated by the following example.

LOCKING A HAVING con excerpt where_cond , cname , col_pos

JC01A014

excerpt

A

Teradata FastLoad Reference

A­5

Appendix A: How to Read Syntax Diagrams Syntax Diagram Conventions

A­6

Teradata FastLoad Reference

Appendix B:

Multifile FastLoad Job Script Examples

This appendix provides three example multifile FastLoad job scripts. The third script includes the END LOADING command. The following subsections show the output from the three FastLoad job scripts. Note: The third output file contains the final statistics.

Teradata FastLoad Reference

B­1

Appendix B: Multifile FastLoad Job Script Examples First Output File

First Output File

==================================================================== = = = FASTLOAD UTILITY VERSION 07.00.00 = = = ==================================================================== **** 0001 **** **** 0002 **** 0003 15:24:25 Processing starting at: Thu Jan 15 15:24:25 1998 RECORD 1 THRU 10; 15:24:25 Starting record number set to : 1 15:24:25 Ending record number set to : 10 .SESSIONS 10 15:24:25 FDL4866 SESSIONS command accepted SHOW VERSION; FastLoad Version 07.00.00 for UNIX 5.4 running Streams TCP/IP FastLoad : 07.00.00.05 FastCmds : 07.00.00.06 FastIO : 07.00.00.01 FastMBCS : 07.00.00.01 FastNtfy : 07.00.00.01 FastPars : 07.00.00.01 FastSQL : 07.00.00.06 FastUtil : 07.00.00.04 pmProcs : 01.00.00.01 CLIV2 : 04.02.00.05 MTDP : 04.02.00.03 MOSIos : 04.01.01.03 MOSIDEP : 04.02.00.04 OSENCRYPT : N/A OSERR : 04.02.00.00

FastLoad linking date: Dec 5 1997 ==================================================================== = = = Logon/Connection = = = ==================================================================== 0004 **** **** **** **** 0005 **** LOGON xyz, Current CLI or RDBMS allows maximum row size: 32K 15:24:29 Number of FastLoad sessions requested = 10 15:24:29 Number of FastLoad sessions connected = 10 15:24:29 FDL4808 LOGON successful DROP TABLE Error_1; 15:24:30 Command completed successfully

B­2

Teradata FastLoad Reference

Appendix B: Multifile FastLoad Job Script Examples First Output File 0006 **** 0007 **** 0008 DROP TABLE Error_2; 15:24:33 Command completed successfully DROP TABLE test2; 15:24:36 Command completed successfully CREATE TABLE test2, fallback ( Counter INTEGER, text CHAR(10)) UNIQUE PRIMARY INDEX (Counter); **** 15:24:37 Command completed successfully 0009 BEGIN LOADING test2 ErrorFiles Error_1, Error_2; **** 15:24:41 Number of AMPs available: 24 **** 15:24:41 BEGIN LOADING COMPLETE 0010 DEFINE Counter (int), text(char(10)) FILE=test.data; **** 15:24:41 FDL4803 DEFINE statement processed ==================================================================== = = = Insert Phase = = = ==================================================================== 0011 INSERT INTO test2 (Counter, text) VALUES (:Counter, :text); **** 15:24:42 Number of recs/msg: 1587 **** 15:24:42 Starting to send to DBS with record 1 **** 15:24:42 Sending row 10 **** 15:24:42 Finished sending rows to the DBS 0012 LOGOFF; ==================================================================== = = = Logoff/Disconnect = = = ==================================================================== **** 15:24:43 Logging off all sessions **** 15:24:44 Total processor time used = '1.02 Seconds' . Start : Thu Jan 15 15:24:25 1998 . End : Thu Jan 15 15:24:44 1998 . Highest return code encountered = '4'. **** 15:24:44 FastLoad Paused

Teradata FastLoad Reference

B­3

Appendix B: Multifile FastLoad Job Script Examples Second Output File

Second Output File

==================================================================== = = = FASTLOAD UTILITY VERSION 07.00.00 = = = ==================================================================== **** 0001 **** **** 0002 **** 0003 15:24:57 Processing starting at: Thu Jan 15 15:24:57 1998 RECORD 101 THRU 110; 15:24:57 Starting record number set to : 101 15:24:57 Ending record number set to : 110 .SESSIONS 10 15:24:57 FDL4866 SESSIONS command accepted SHOW VERSION; FastLoad Version 07.00.00 for UNIX 5.4 running Streams TCP/IP FastLoad : 07.00.00.05 FastCmds : 07.00.00.06 FastIO : 07.00.00.01 FastMBCS : 07.00.00.01 FastNtfy : 07.00.00.01 FastPars : 07.00.00.01 FastSQL : 07.00.00.06 FastUtil : 07.00.00.04 pmProcs : 01.00.00.01 CLIV2 : 04.02.00.05 MTDP : 04.02.00.03 MOSIos : 04.01.01.03 MOSIDEP : 04.02.00.04 OSENCRYPT : N/A OSERR : 04.02.00.00

FastLoad linking date: Dec 5 1997 ==================================================================== = = = Logon/Connection = = = ==================================================================== 0004 **** **** **** **** 0005 **** LOGON xyz, Current CLI or RDBMS allows maximum row size: 32K 15:25:01 Number of FastLoad sessions requested = 10 15:25:01 Number of FastLoad sessions connected = 10 15:25:01 FDL4808 LOGON successful BEGIN LOADING test2 ErrorFiles Error_1, Error_2; 15:25:01 FastLoad is continuing a multifile job

B­4

Teradata FastLoad Reference

Appendix B: Multifile FastLoad Job Script Examples Second Output File **** 15:25:02 Number of AMPs available: 24 **** 15:25:02 BEGIN LOADING COMPLETE 0006 DEFINE Counter (int), text(char(10)) FILE=test.data; **** 15:25:02 FDL4803 DEFINE statement processed ==================================================================== = = = Insert Phase = = = ==================================================================== 0007 INSERT INTO test2 (Counter, text) VALUES (:Counter, :text); **** 15:25:02 Number of recs/msg: 1587 **** 15:25:02 Starting to send to DBS with record 101 **** 15:25:02 Sending row 110 **** 15:25:02 Finished sending rows to the DBS 0008 LOGOFF; ==================================================================== = = = Logoff/Disconnect = = = ==================================================================== **** 15:25:03 Logging off all sessions **** 15:25:05 Total processor time used = `0.97 Seconds' . Start : Thu Jan 15 15:24:57 1998 . End : Thu Jan 15 15:25:05 1998 . Highest return code encountered = `4'. **** 15:25:05 FastLoad Paused

Teradata FastLoad Reference

B­5

Appendix B: Multifile FastLoad Job Script Examples Third Output File

Third Output File

==================================================================== = = = FASTLOAD UTILITY VERSION 07.00.00 = = = ==================================================================== **** 0001 **** **** 0002 **** 0003 15:25:13 Processing starting at: Thu Jan 15 15:25:13 1998 RECORD 201 THRU 210; 15:25:13 Starting record number set to : 201 15:25:13 Ending record number set to : 210 .SESSIONS 10 15:25:13 FDL4866 SESSIONS command accepted SHOW VERSION; FastLoad Version 07.00.00 for UNIX 5.4 running Streams TCP/IP FastLoad : 07.00.00.05 FastCmds : 07.00.00.06 FastIO : 07.00.00.01 FastMBCS : 07.00.00.01 FastNtfy : 07.00.00.01 FastPars : 07.00.00.01 FastSQL : 07.00.00.06 FastUtil : 07.00.00.04 pmProcs : 01.00.00.01 CLIV2 : 04.02.00.05 MTDP : 04.02.00.03 MOSIos : 04.01.01.03 MOSIDEP : 04.02.00.04 OSENCRYPT : N/A OSERR : 04.02.00.00

FastLoad linking date: Dec 5 1997 ==================================================================== = = = Logon/Connection = = = ==================================================================== 0004 **** **** **** **** 0005 **** LOGON xyz, Current CLI or RDBMS allows maximum row size: 32K 15:25:16 Number of FastLoad sessions requested = 10 15:25:16 Number of FastLoad sessions connected = 10 15:25:16 FDL4808 LOGON successful BEGIN LOADING test2 ErrorFiles Error_1, Error_2 checkpoint 5; 15:25:17 FastLoad is continuing a multifile job

B­6

Teradata FastLoad Reference

Appendix B: Multifile FastLoad Job Script Examples Third Output File **** 15:25:17 Number of AMPs available: 24 **** 15:25:17 BEGIN LOADING COMPLETE 0006 DEFINE ( Counter (int), text (char(10)) FILE=test.data; **** 15:25:18 FDL4803 DEFINE statement processed ==================================================================== = = = Insert Phase = = = ==================================================================== 0007 INSERT INTO test2 (Counter, text) VALUES (:Counter, :text); **** 15:25:18 Number of recs/msg: 1587 **** 15:25:18 Starting to send to DBS with record 201 **** 15:25:18 Sending row 205 **** 15:25:18 Sending row 210 **** 15:25:18 Finished sending rows to the DBS ==================================================================== = = = End Loading Phase = = = ==================================================================== 0008 END LOADING; Total Records Read - skipped by RECORD command - sent to the DBS Total Error Table 1 Total Error Table 2 Total Inserts Applied Total Duplicate Rows Start: End :

= 230 = 200 = 30 = 0 ---- Table has been dropped = 0 ---- Table has been dropped = 30 = 0

Thu Jan 15 15:25:19 1998 Thu Jan 15 15:25:23 1998

0009 LOGOFF; ==================================================================== = = = Logoff/Disconnect = = = ==================================================================== **** 15:25:23 Logging off all sessions **** 15:25:25 Total processor time used = `0.92 Seconds' . Start : Thu Jan 15 15:25:13 1998 . End : Thu Jan 15 15:25:25 1998 . Highest return code encountered = `0'. **** 15:25:25 FDL4818 FastLoad Terminated

Teradata FastLoad Reference

B­7

Appendix B: Multifile FastLoad Job Script Examples Third Output File

B­8

Teradata FastLoad Reference

Appendix C:

INMOD and Notify Exit Routine Examples

This appendix provides program listings of sample INMOD and notify exit routines for the following Teradata client platforms: · For VM and MVS--example INMOD routines written in: · Assembler · COBOL · PL/I · SAS/C For UNIX--the sample INMOD routines that are provided with the FastLoad utility software: · BLKEXIT.C · BLKEXITR.C For All Platforms--the notify exit routine: · flnfyext.c

·

·

Teradata FastLoad Reference

C­1

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS

For VM and MVS

Assembler INMOD Example

The INMOD in the following example reads a record from the input data file and adds a four-byte integer field to the front of the record. The new field contains a sequence record that ranges from one to the total number of input records.

BULKCON TITLE'-- CONCATENATE INPUT RECORDS FOR INPUT TO FASTLOAD ' BULKCON CSECT USING BULKCON,15 ******************************************************************** * THIS PROGRAM IS CALLED BY THE TERADATA FASTLOAD PROGRAMS * * TO OBTAIN A RECORD TO BE USED TO INSERT, UPDATE, DELETE, * * OR SELECT ROWS OF A DBC TABLE. * * * * THIS PROGRAM IS NOT REENTRANT. * * FUNCTION: * * READ AN INPUT RECORD AND ADD A FOUR-BYTE INTEGER FIELD * * THE FRONT OF THE RECORD. THE NEW FIELD WILL CONTAIN * * A SEQUENCE NUMBER WHICH RANGES FROM 1 TO ... * * NUMBER-OF-INPUT-RECORDS. * * * * RETURN TO THE CALLER, FASTLOAD, INDICATING * * EITHER MORE RECORDS ARE AVAILABLE OR NO MORE RECORDS * * ARE TO BE PROCESSED. * * * * THIS INMOD PROGRAM CAN BE USED TO ENSURE UNIQUE RECORDS * * IN CERTAIN APPLICATIONS, THE SEQUENCE FIELD * * CAN BE USED FOR "DATA SAMPLING". * * * * DDNAME OF THE INPUT DATA SET: "INDATA" * * * ******************************************************************** B STOREGS BRANCH AROUND EP DC AL1(31) DEFINE EP LENGTH DC CL9'BULKIN ' DEFINE DC CL9'&SYSDATE' ENTRY DC CL8' MVS ' POINT DC CL5'&SYSTIME' IDENTIFIER

C­2

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS ******************************************************************** * SAVE REGISTERS * ******************************************************************** STOREGS DS 0H DEFINE AND ALIGN SYMBOL STM R14,R12,12(R13) STORE OFF CALLER'S REGISTERS LR R12,R15 COPY BASE ADDRESS DROP R15 DROP VOLATILE BASE REGISTER USING BULKCON,R12 ESTAB PERM CSECT ADDRBLTY LA R14,SAVEAREA POINT AT LOCAL SAVE WORK ST R14,8(,R13) STORE FWD LINK IN SA CHAIN ST R13,4(,R14) STORE BWD LINK IN SA CHAIN LR R13,R14 COPY LOCAL SAVE/WORK AREA ADDR L R11,0(,R1) POINT TO PARM SPACE 1 ******************************************************************** * VOPEN "DATA" DATA SET * * (ONLY THE FIRST TIME) * ******************************************************************** USING PREBUF,R11 COVER PRE-PROC AREA LA R9,PREREC POINT TO START OF PREPROC. DATA L R15,PRECODE CHECK ON CODE FROM FASTLOAD OC PRECODE,PRECODE FIRST ENTRY ? (0=FIRST ENTRY) BNZ NOOPEN NO, SKIP OPEN USING IHADCB,R10 YES,COVER DCB FOR OPEN LA R10,INDATA POINT TO DATA DCB OPEN INDATA OPEN INPUT DATA SET TM DCBOFLGS,X'10' DID IT OPEN ? BO OPENOK YES, WTO 'UNABLE TO OPEN INDATADATA SET',ROUTCDE=11 B BADRET RETURN WITH ERROR CODE ******************************************************************* * CHECK FASTLOAD/BULKLOAD STATUS CODES * * 0 = FIRST ENTRY (FASTLOAD/BULKLOAD EXPECTS TO RECEIVE A * * RECORD) * * 1 = GET NEXT RECORD (FASTLOAD/BULKLOAD EXPECTS TO RECEIVE A * * RECORD) * * 2 = Client RESTART CALL (FASTLOAD DOES NOT EXPECT A RECORD) * * 3 = CHECKPOINT CALL (FASTLOAD DOES NOT EXPECT A RECORD) * * 4 = DBC RESTART CALL (FASTLOAD DOES NOT EXPECT A RECORD) * * * * * * NOTE: THIS INMOD WAS WRITTEN TO BE COMPATIBLE WITH FASTLOAD * * AND BULKLOAD AND THEREFORE CONTAINS INFORMATION ON * * STATUS CODES 2,3, AND 4 WHICH PERTAIN ONLY TO * * FASTLOAD. CODES 2,3, AND 4 ARE NOT HANDLED BY * * THIS PROGRAM. * ******************************************************************* OPENOK DS 0H

Teradata FastLoad Reference

C­3

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS MVI ISPOPEN,1 INDICATE INPUT FILE HAS BEEN OPEN L R15,PRECODE CHECK ON CODE FROM FASTLOAD C R15,=F'1' NEED RECORD ? BH NOREC NO , DO NOT "GET" A RECORD L R15,SAMPNUM GET CURRENT SAMPLE NUM. LA R15,1(R15) INCR BY 1 ST R15,0(R9) STORE AT FRONT OF RECORD ST R15,SAMPNUM RESET COUNTER LA R9,4(R9) ADVANCE FOR READ ADDR. LA R10,INDATA COVER INDATA DCB GETNEXT GET INDATA,(R9) READ A RECORD INCREC LH R9,DCBLRECL GET RECORD LENGTH AH R9,=H'4' ADD 4 FOR NEW FIELD SR R15,R15 SET RETURN CODE VALUE RETURN ST R9,PRELEN SET LENGTH (ZERO AFTER EOF) ST R15,PRECODE L R13,4(,R13) RETURN (14,12),RC=0 RETURN SPACE CLEANUP DS 0H CLI ISOPEN,0 IS INPUT FILE OPEN? BE RETURN NO - JUST RETURN B EOF CLEAN IT UP SPACE5 ******************************************************************** * EOF ENTERED AT END-OF-FILE * ******************************************************************** EOF CLOSE INDATA CLOSE INPUT DATA SET MVI ISOPEN,0 INDICATE FILE NOT OPEN ******************************************************************** NOREC SR R15,R15 SET ZERO RETURN CODE SR R9,R9 SET ZERO LENGTH B RETURN RETURN * BADRET LA R15,16 SET RETURN CODE FOR ERROR SR R9,R9 SET LENGTH = 0 B RETURN ERROR RETURN EJECT * * CONSTANTS * R0 EQU 0 R1 EQU 1 R2 EQU 2 R3 EQU 3 R4 EQU 4 R5 EQU 5 R6 EQU 6 R7 EQU 7

NOOPEN

C­4

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS R8 R9 R10 R11 R12 R13 R14 R15 * * * EQU EQU EQU EQU EQU EQU EQU EQU EJECT 8 9 10 11 12 13 14 15

DATA STRUCTURES AND VARIABLES

SPACE SAVEAREA DC SAMPNUM DC ISOPEN DC SPACE INDATA DCB PREBUF DSECT PRECODE DS PRELEN DS ROWSIZE EQU PREREC DS DCBD END

18F'0' SAVE AREA F'0' XL1'00' OPEN FILE INDICATOR 10 DDNAME=INDATA,MACRF=(GM),DSORG=PS,EODAD=EOF F F 31774 MAXIMUM ROW SIZE 0XL(ROWSIZE) DEVD=DA,DSORG=PS

Teradata FastLoad Reference

C­5

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS

COBOL INMOD Example

The INMOD in the following example reads five 80-byte records from the input data file, combines them, then returns a single, 400-byte record to the FastLoad utility.

IDENTIFICATION DIVISION. PROGRAM-ID. BLKEXIT. AUTHOR. STV . INSTALLATION. TERADATA. DATE-WRITTEN. 05 APRIL 1984. DATE-COMPILED. SECURITY. OPEN. REMARKS. THIS PROGRAM IS AN EXAMPLE OF A COBOL INMOD ROUTINE. THE NAME MUST BE BLKEXIT. FUNCTION: THE PROGRAM READS FIVE 80-BYTE RECORDS AND RETURNS A COMPOSITE RECORD WHICH IS 400 BYTES LONG. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. IBM-370. OBJECT-COMPUTER. IBM-370. INPUT-OUTPUT SECTION. FILE-CONTROL. SELECT INMOD-DATA-FILE ASSIGN TO SYSIN-INDATA. DATA DIVISION. FILE SECTION. FD INMOD-DATA-FILE BLOCK CONTAINS 0 RECORDS LABEL RECORDS STANDARD. 01 INPUT-PARM-AREA PICTURE IS X(80). WORKING-STORAGE SECTION. 01 NUMIN PIC S9(4) COMP VALUE +0. 01 NUMOUT PIC S9(4) COMP VALUE +0. 01 FILES_OPEN PIC S9(4) COMP VALUE +0. LINKAGE SECTION. 01 STRUCT. 02 RETURN-INDICATEPIC S9(9) COMP. 02 RECORD-LEN PIC S9(9) COMP. 02 RECORD-BODY. 03 DATA-AREA1 PIC X(80). 03 DATA-AREA2 PIC X(80). 03 DATA-AREA3 PIC X(80). 03 DATA-AREA4 PIC X(80).

C­6

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS 03 DATA-AREA5 PIC X(80). PROCEDURE DIVISION USING STRUCT. BEGIN. MAIN. IF RETURN-INDICATE = 0 THEN DISPLAY 'BLKEXIT CALLED - RETURN CODE PERFORM OPEN-FILES PERFORM READ-RECORDS GOBACK ELSE IF RETURN-INDICATE = 1 THEN DISPLAY 'BLKEXIT CALLED - RETURN CODE PERFORM READ-RECORDS GOBACK ELSE IF RETURN-INDICATE = 2 THEN DISPLAY 'BLKEXIT CALLED - RETURN CODE PERFORM OPEN-FILES MOVE 0 TO RECORD-LEN GOBACK ELSE IF RETURN-INDICATE = 5 THEN DISPLAY 'BLKEXIT CALLED - RETURN CODE IF FILES_OPEN = 1 THEN CLOSE INMOD-DATA-FILE MOVE 0 TO FILES_OPEN. MOVE 0 TO RECORD-LEN GOBACK ELSE DISPLAY 'BLKEXIT CALLED - RETURN CODE GOBACK. OPEN-FILES SECTION. OPEN INPUT INMOD-DATA-FILE. MOVE 1 TO FILES_OPEN. MOVE 0 TO RETURN-INDICATE. READ-RECORDS SECTION. MOVE 0 TO BLOCK-NUM. READ INMOD-DATA-FILE INTO DATA-AREA1 AT END GO TO END-DATA. ADD 1 TO NUMIN. READ INMOD-DATA-FILE INTO DATA-AREA2 AT END GO TO END-DATA. ADD 1 TO NUMIN. READ INMOD-DATA-FILE INTO DATA-AREA3 AT END GO TO END-DATA. ADD 1 TO NUMIN. READ INMOD-DATA-FILE INTO DATA-AREA4 AT END GO TO END-DATA ADD 1 TO NUMIN.

0'

1'

2'

5'

X'

Teradata FastLoad Reference

C­7

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS READ INMOD-DATA-FILE INTO DATA-AREA5 AT END GO TO END-DATA. ADD 1 TO NUMIN. MOVE 0 TO RETURN-INDICATE. MOVE 400 TO RECORD-LEN. ADD 1 TO NUMOUT. END-DATA SECTION. MOVE 0 TO RETURN-INDICATE. CLOSE INMOD-DATA-FILE. MOVE 0 TO FILES_OPEN. DISPLAY 'NUMBER OF INPUT RECORDS =' NUMIN. DISPLAY 'NUMBER OF OUTPUT RECORDS=' NUMOUT. MOVE 0 TO RECORD-LEN. MOVE 99 TO RETURN-INDICATE. GOBACK.

C­8

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS

PL/I INMOD Example

The INMOD in the following example reads the data form the input file and presents it as read to the FastLoad utility. You could modify this basic program to select records that meet a specified criteria, convert certain fields, or perform other preprocessing functions.

BLKEXIT: PROC (PARM_LIST) OPTIONS(MAIN); /* Routine name must always be BLKEXIT */ DCL EXPORT FILE RECORD INPUT; DCL EOF FIXED BINARY (31,0) INIT (0); DCL THIS_LENGTH FIXED BINARY (31,0); DCL1 PARM_LIST, 10 STATUS FIXED BINARY (31,0), 10 RLENGTH FIXED BINARY (31,0), 10 CCDB_REC CHAR (80); ON ENDFILE (EXPORT) EOF = 1; IF STATUS = 3 /* disregard restarts and checkpoints */ THEN GO TO PROC_RETURN; IF STATUS = 4 /* disregard restarts and checkpoints */ THEN GO TO PROC_RETURN; IF STATUS = 2 /* client restart */ /*Note:STATUS values 2 , 3 and 4 are only applicable to the FastLoad program. An INMOD program can be written to interface with either FastLoad or Bulk Data Load -STATUS values greater than '1' will not be set by the Bulk Data Load program. THEN DO; OPEN FILE (EXPORT); GO TO PROC_RETURN; END; IF STATUS = 0 /* CHECK FOR FIRST-TIME-THRU CYCLE */ THEN DO; OPEN FILE (EXPORT); END; RLENGTH = 80; READ FILE (EXPORT) INTO (CCDB_REC); IFEOF = 1 THEN DO; CLOSE FILE (EXPORT); STATUS=1; /* INDICATE EOF TO BDL OR FDL */ RLENGTH = 0; GO TO PROC_RETURN; END; STATUS=0; /* INDICATE NEXT RECORD TO BDL OR FDL */ /* Additional processing can be done at this point */ PROC_RETURN: END BLKEXIT `OPTIONS(MAIN)';

*/

Teradata FastLoad Reference

C­9

Appendix C: INMOD and Notify Exit Routine Examples For VM and MVS

SAS/C INMOD Example

Refer to"BLKEXITR.C Sample INMOD" on page C-17. The BLKEXITR.C sample, because it is written in C and the entry point is written for all platforms, would work on the mainframe for a SAS/C INMOD.

C ­ 10

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For UNIX

For UNIX

BLKEXIT.C Sample INMOD

Following is the listing of the BLKEXIT.C sample INMOD routine that is provided with the FastLoad utility software:

/******************************************************************* *Title: BLKEXIT - sample INMOD routine * * COPYRIGHT (C) NCR Corporation. 1998 * * This copyrighted material is the Confidential, Unpublished * Property of the NCR Corporation. This copyright notice and * any other copyright notices included in machine readable * copies must be reproduced on all authorized copies. * * * Description This file contains a sample INMOD, written in C. * This file does not support DBS restarts. * * * History Information * * Revision Date DCR DID Comments * ----------- -------- ----- ------- ----------------------------* 07.01.00.01 11/12/98 44120 SF3 Release for FastLoad 7.1 * 06.00.00.00 07/18/96 34208 SF3 Release for FastLoad 6.0 * * How to build this INMOD on a Unix system: * * Compile and link it into a shared object: * * cc -G -KPIC <inmod-name>.c -o <shared-object-name> * * * How to use this program: * * This INMOD routine will generate 2 columns of data: * * 4-byte integer counter for the Unique Primary Index * 10-byte character string * * This sample INMOD will generate 100,000 rows, if no RECORD * statement is used in the FastLoad job script.

Teradata FastLoad Reference

C ­ 11

Appendix C: INMOD and Notify Exit Routine Examples For UNIX * * A sample of the job script for this INMOD would be as follows: * * * use your own system, account and password here. * LOGON tdpid/user,password; DROP TABLE Error_1; DROP TABLE Error_2; DROP TABLE TestTable; CREATE TABLE TestTable AS ( Counter Integer, text char(10) ) UNIQUE PRIMARY INDEX(Counter); BEGIN LOADING TestTable ErrorFiles Error_1, Error_2; DEFINE Counter (Integer), text (char(10)) INMOD=<shared-object-name>; INSERT INTO TestTable (Counter, text) VALUES (:Counter, :text); END LOADING; LOGOFF; *************************************************************************/ #include <stdio.h> #include <string.h> #define FILEOF #define EM_OK #define NUMROWS #define ROWSIZE 401 0 100000 64000

typedef long Int32; typedef short Int16; typedef struct inmod_struct { Int32 ReturnCode; Int32 Length;

C ­ 12

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For UNIX char Body[ROWSIZE]; } inmdtyp,*inmdptr; inmdptr inmodptr; char *str = "123test890"; Int32 reccnt = 0; /********************************************************************** * * MakeRecord - Generate a record * * This module creates the data record * * In this example, we are just generating dummy records * with canned data, only we change the first column, * essentially a record number, so that each row is unique. * * For performance reasons, we do not change the 2nd column. * ****************************************************************/ Int32 MakeRecord() { char *p; /* have we reached EOF yet? */ if (reccnt >= NUMROWS) return(FILEOF); /* nope. get start of buffer */ p = inmodptr->Body; /* place column 1, a unique primary index */ memcpy(p, &reccnt, sizeof(reccnt)); p += sizeof(reccnt); /* place column 2, a string */ memcpy(p, str, strlen(str)); p += strlen(str); inmodptr->ReturnCode = 0; inmodptr->Length = p - inmodptr->Body; reccnt++;

Teradata FastLoad Reference

C ­ 13

Appendix C: INMOD and Notify Exit Routine Examples For UNIX

return(EM_OK); } /**************************************************************** * * HostRestart - Host restarted * * Reset and start sending data from the begining. * *****************************************************************/ Int32 HostRestart() { return(EM_OK); } /********************************************************************** * * CheckPoint - Save checkpoint * **********************************************************************/ Int32 CheckPoint() { return(EM_OK); } /*********************************************************************** * * DBSRestart - DBS restarted * * Do what you have to do. * Reset record counter to checkpoint value * **********************************************************************/ Int32 DBSRestart() { return(EM_OK); } /********************************************************************** * * CleanUp - Do cleanup. * **********************************************************************/ Int32 CleanUp() { return(EM_OK); }

C ­ 14

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For UNIX /********************************************************************** * * InvalidCode - Invalid INMOD code returned. * *************************************************************************/ Int32 InvalidCode() { fprintf(stderr, "**** Invalid code received by INMOD\n"); return(EM_OK); } /************************************************************************* * * Init - initialize some stuff * * Do any initialization necessary * *****************************************************************/ Int32 Init() { return(EM_OK); } /**************************************************************** * * BLKEXIT - Start processing * * This is the main module which contains the checks for * number of records generated and buffer filling. This * module also sends the filled buffer to the DBS. * *****************************************************************/ #if defined WIN32 __declspec(dllexport) Int32 BLKEXIT(tblptr) #elif defined I370 Int32 _dynamn(tblptr) #else Int32 BLKEXIT(tblptr) #endif char *tblptr; { Int32 result; inmodptr = (struct inmod_struct *)tblptr; /* process the function passed to the INMOD */ switch (inmodptr->ReturnCode) { case 0: result = Init();

Teradata FastLoad Reference

C ­ 15

Appendix C: INMOD and Notify Exit Routine Examples For UNIX if (result) break; result = MakeRecord(); break; case 1: result = MakeRecord(); break; case 2: result = HostRestart(); break; case 3: result = CheckPoint(); break; case 4: result = DBSRestart(); break; case 5: result = CleanUp(); break; default: result = InvalidCode(); } /* see if we have reached EOF condition */ if (result == FILEOF) { inmodptr->Length = 0; result = EM_OK; } return(result); }

C ­ 16

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For UNIX

BLKEXITR.C Sample INMOD

Following is the listing of the BLKEXITR.C sample INMOD routine that is provided with the FastLoad utility software:

/******************************************************************** *Title: BLKEXITR - sample INMOD routine * * COPYRIGHT (C) NCR Corporation. 1998 * * This copyrighted material is the Confidential, Unpublished * Property of the NCR Corporation. This copyright notice and * any other copyright notices included in machine readable * copies must be reproduced on all authorized copies. * * * Description This file contains a sample INMOD, written in C. * This file supports DBS restarts. * * * History Information * * Revision Date DCR DID Comments * ----------- -------- ----- ------- --------------------------------------* 07.01.00.01 11/12/98 44120 SF3 Release for FastLoad 7.1 * 06.00.00.00 07/18/96 34208 SF3 Release for FastLoad 6.0 * * * How to build this INMOD on a Unix system: * * Compile and link it into a shared object: * * cc -G -KPIC <inmod-name>.c -o <shared-object-name> * * * How to use this program: * * This INMOD routine will generate 2 columns of data: * * 4-byte integer counter for the Unique Primary Index * 10-byte character string * * This sample INMOD will generate 100,000 rows, if no RECORD * statement is used in the FastLoad job script. * * A sample of the job script for this INMOD would be as follows: *

Teradata FastLoad Reference

C ­ 17

Appendix C: INMOD and Notify Exit Routine Examples For UNIX * * use your own system, account and password here. * LOGON tdpid/user,password; DROP TABLE Error_1; DROP TABLE Error_2; DROP TABLE TestTable; CREATE TABLE TestTable AS ( Counter Integer, text char(10) ) UNIQUE PRIMARY INDEX(Counter); BEGIN LOADING TestTable ErrorFiles Error_1, Error_2; DEFINE Counter (Integer), text (char(10)) INMOD=<shared-object-name>; INSERT INTO TestTable (Counter, text) VALUES (:Counter, :text); END LOADING; LOGOFF; *****************************************************************/ #include <stdio.h> #include <string.h> #define FILEOF #define EM_OK #define NUMROWS #define ROWSIZE 401 0 100000 64000

typedef long Int32; typedef short Int16; typedef struct inmod_struct { Int32 ReturnCode; Int32 Length; char Body[ROWSIZE]; } inmdtyp,*inmdptr;

C ­ 18

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For UNIX inmdptr inmodptr; char *str = "123test890"; char *fname = "chkpoint.dat"; FILE *fp = NULL; Int32 reccnt = 0; Int32 chkpnt; /*********************************************************************** * * MakeRecord - Generate a record * * This module creates the data record * * In this example, we are just generating dummy records * with canned data, only we change the first column, * essentially a record number, so that each row is unique. * ***********************************************************************/ Int32 MakeRecord() { char *p; /* have we reached EOF yet? */ if (reccnt >= NUMROWS) return(FILEOF); /* nope. get start of buffer */ p = inmodptr->Body; /* place column 1, a unique primary index */ memcpy(p, &reccnt, sizeof(reccnt)); p += sizeof(reccnt); /* place column 2, a string */ memcpy(p, str, strlen(str)); p += strlen(str); inmodptr->ReturnCode = 0; inmodptr->Length = p - inmodptr->Body; reccnt++; return(EM_OK);

Teradata FastLoad Reference

C ­ 19

Appendix C: INMOD and Notify Exit Routine Examples For UNIX } /********************************************************************** * * HostRestart - Host restarted * * Retrieve the checkpoint information from the checkpoint file. * Reset record counter to checkpoint value * *****************************************************************/ Int32 HostRestart() { Int32 result; /* see if the file is already open */ if (!fp) { fp = fopen(fname, "r+"); if (!fp) return(!EM_OK); } rewind(fp); result = fread(&chkpnt, sizeof(chkpnt), 1, fp); if (result != 1) { fprintf(stderr, "INMOD: ERROR READING CHECKPOINT FILE\n"); fprintf(stderr, "INMOD: %d ELEMENTS WERE READ\n", result); perror("INMOD"); return(!EM_OK); } fprintf(stderr, "INMOD: HOST RESTARTED. CHECKPOINT: %d\n", chkpnt); reccnt = chkpnt; return(EM_OK); } /************************************************************************* * * CheckPoint - Save checkpoint * *****************************************************************/ Int32 CheckPoint() { Int32 result; chkpnt = reccnt;

C ­ 20

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For UNIX rewind(fp); result = fwrite(&chkpnt, sizeof(chkpnt), 1, fp); if (result != 1) { fprintf(stderr, "INMOD: ERROR WRITING TO CHECKPOINT FILE\n"); fprintf(stderr, "INMOD: %d ELEMENTS WERE WRITTEN\n", result); perror("INMOD"); return(!EM_OK); } fprintf(stderr, "INMOD: CHECKPOINT AT ROW: %d\n", chkpnt); return(EM_OK); } /*********************************************************************** * * DBSRestart - DBS restarted * * Retrieve the checkpoint information from the checkpoint file. * Reset record counter to checkpoint value * ***********************************************************************/ Int32 DBSRestart() { Int32 result; /* see if the file is already open */ if (!fp) { fp = fopen(fname, "r+"); if (!fp) return(!EM_OK); } rewind(fp); result = fread(&chkpnt, sizeof(chkpnt), 1, fp); if (result != 1) { fprintf(stderr, "INMOD: ERROR READING CHECKPOINT FILE\n"); fprintf(stderr, "INMOD: %d ELEMENTS WERE READ\n", result); perror("INMOD"); return(!EM_OK); } fprintf(stderr, "INMOD: DBS RESTARTED. CHECKPOINT: %d\n", chkpnt); reccnt = chkpnt; return(EM_OK); }

Teradata FastLoad Reference

C ­ 21

Appendix C: INMOD and Notify Exit Routine Examples For UNIX /*********************************************************************** * * CleanUp - Do cleanup. * * Here we close the file and then remove it. * ***********************************************************************/ Int32 CleanUp() { fclose(fp); remove(fname); return(EM_OK); } /************************************************************************* * * InvalidCode - Invalid INMOD code returned. * *****************************************************************/ Int32 InvalidCode() { fprintf(stderr, "**** Invalid code received by INMOD\n"); return(EM_OK); } /***************************************************************** * * Init - initialize some stuff * * Do any initialization necessary * * For this example, we will open a disk file to hold * the checkpoint information. For this example, we * will just store the row number. If this INMOD was * reading data from a file, then the file position * would need to be stored. * *****************************************************************/ Int32 Init() { fp = fopen(fname, "w"); if (!fp) return(!EM_OK); return(EM_OK); } /***************************************************************** *

C ­ 22

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For UNIX * BLKEXIT - Start processing * * This is the main module which contains the checks for * number of records generated and buffer filling. This * module also sends the filled buffer to the DBS. *****************************************************************/ #if defined WIN32 __declspec(dllexport) Int32 BLKEXIT(tblptr) #elif defined I370 Int32 _dynamn(tblptr) #else Int32 BLKEXIT(tblptr) #endif char *tblptr; { Int32 result; inmodptr = (struct inmod_struct *)tblptr; /* process the function passed to the INMOD */ switch (inmodptr->ReturnCode) { case 0: result = Init(); if (result) break; result = MakeRecord(); break; case 1: result = MakeRecord(); break; case 2: result = HostRestart(); break; case 3: result = CheckPoint(); break; case 4: result = DBSRestart(); break; case 5: result = CleanUp(); break; default: result = InvalidCode(); } /* see if we have reached EOF condition */ if (result == FILEOF) { inmodptr->Length = 0; result = EM_OK; } return(result); }

Teradata FastLoad Reference

C ­ 23

Appendix C: INMOD and Notify Exit Routine Examples For All Platforms

For All Platforms

Notify Exit Routine Example

The user exit routine in the following example processes events specified by the NOTIFY command.

/************************************************************************** * * TITLE: flnfyext.c .... an example notify exit... * * COPYRIGHT (C) NCR Corporation. 1996 * * This copyrighted material is the Confidential, Unpublished * Property of the NCR Corporation. This copyright notice and * any other copyright notices included in machine readable * copies must be reproduced on all authorized copies. * * Description This file is a sample user exit routine for * processing NOTIFY events * * History Information * * Revision Date DCR DID Comments * ----------- -------- ----- ------- --------------------------------------* 06.01.00.01 04/04/97 34579 SF3 Initial Version * * * Notes The procedure must be called "_dynamn" * **************************************************************************/ #include <stdio.h> typedef long Int32; typedef enum { NFEventInit, NFEventCkPoint, NFEventOpen, NFEventPhaseI, NFEventPhaseII, NFEventComplete, NFEventErrTabI, NFEventErrTabII, NFEventExit

C ­ 24

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For All Platforms } NfyFLDEvent; typedef struct _FLNotifyExitParm { Int32 Event; /* should be NfyFLDEvent values */ union { struct { int dummy; /* picky SASC compiler */ }Init; struct { Int32 RecordCount; } CkPoint; struct { char *FileOrInmodName; }Open ; struct { char *TableName; }PhaseI ; struct { Int32 RecordCount; }PhaseII ; struct { Int32 RecordsLoaded; } Complete; struct { Int32 RecsInETI; } ErrTabI; struct { Int32 RecsInETII; } ErrTabII ; struct { Int32 ReturnCode; } Exit; } Vals; } FLNotifyExitParm; /************************************************************************* * * CODE STARTS HERE * *************************************************************************/ #ifdef WIN32 __declspec(dllexport) Int32 _dynamn(FLNotifyExitParm *P) #else Int32 _dynamn(FLNotifyExitParm *P) #endif { FILE *fp; if (!(fp = fopen("NFYEXIT.OUT", "a")))

Teradata FastLoad Reference

C ­ 25

Appendix C: INMOD and Notify Exit Routine Examples For All Platforms return(1); switch(P->Event) { case NFEventInit : /* Nothing */ fprintf(fp, "exit called @ fastload init.\n"); break; case NFEventCkPoint : fprintf(fp, "exit called @ fastload checkpoint : %d records loaded.\n", P->Vals.CkPoint.RecordCount); break; case NFEventOpen : fprintf(fp, "exit called @ fastload file/inmod open: %s.\n", P->Vals.Open.FileOrInmodName); break; case NFEventPhaseI : fprintf(fp, "exit called @ fastload phase I (start) for table %s.\n", P->Vals.PhaseI.TableName); break; case NFEventPhaseII : fprintf(fp, "exit called @ fastload phase II (start): %d recs.\n", P->Vals.PhaseII.RecordCount); break; case NFEventErrTabI : fprintf(fp, "exit called @ fastload ET 1 Drop : %d records in table.\n", P->Vals.ErrTabI.RecsInETI); break; case NFEventErrTabII : fprintf(fp, "exit called @ fastload ET 2 Drop : %d records in table.\n", P->Vals.ErrTabII.RecsInETII); break; case NFEventComplete : fprintf(fp, "exit called @ fastload phase II complete: %d records.\n", P->Vals.Complete.RecordsLoaded); break; case NFEventExit : fprintf(fp, "exit called @ fastload notify out of scope: return code %d.\n", P->Vals.Exit.ReturnCode); break; } fclose(fp); return(0);

C ­ 26

Teradata FastLoad Reference

Appendix C: INMOD and Notify Exit Routine Examples For All Platforms } /************************************************************************** * * End of FlNfyExt.c * **************************************************************************/

Teradata FastLoad Reference

C ­ 27

Appendix C: INMOD and Notify Exit Routine Examples For All Platforms

C ­ 28

Teradata FastLoad Reference

Appendix D:

Compiling, Linking, and Executing INMOD and Notify Exit Routines

This appendix provides examples of compiling, linking, and executing INMOD and notify exit routines written in the supported programming languages for the following client system platforms. · · · · For VM For MVS For UNIX For Windows

Teradata FastLoad Reference

D­1

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For VM

For VM

Using Assembler

INMOD routines written in Assembler on VM must contain the entry point name BLKEXIT. They do not need to be linked-edited with other load modules or stored in a VM LOADLIB file.

Procedure

To create and use an Assembler INMOD routine on VM:

Step Action

1 2 3

Prepare the INMOD routine source file. Compile the INMOD routine. Invoke the FastLoad utility to execute the INMOD routine.

Compiling the INMOD

Use this command to compile the INMOD routine source file:

GLOBAL MACLIB OSMACRO ASSEMBLE ASMINMOD

Executing the INMOD

Use this EXEC to invoke the FastLoad utility and execute the INMOD routine:

&TRACE CP LINK TDAPP 191 402 RR ACCESS 402 B FILEDEF SYSPRINT TERMINAL (LRECL 120 RECFM V FILEDEF SYSIN DISK DOCINASM FSTSYSIN FILEDEF INDATA DISK ASMEXMPL DATA A GLOBAL TXTLIB CLI EXEC FAST &EXIT

Note: The SYSIN file DOCINASM FSTSYSI has these statements:

LOGON e/JLH,HOOD; BEGIN LOADING asm_example DEFINE t1(INTEGER),t2(CHAR(80)) INMOD=ASMINMOD; INSERT asm_example (:t1,:t2); LOGOFF;

D­2

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For VM

Note also: Table asm_example was previously created using this statement:

create table asm_example (f1 INTEGER ,f2 CHAR(80)) primary index (f1);

Using COBOL

INMOD routines written in COBOL on VM must: · · · Contain the entry point name BLKEXIT Be link-edited with the load module: · TERCOBOL Be stored as a VM LOADLIB file

Procedure

To create and use a COBOL INMOD routine on VM:

Step Action

1 2 3 4

Prepare the INMOD routine source file. Compile the INMOD routine. Link-edit the INMOD program with the specified load modules and store the results as a LOADLIB file. Invoke the FastLoad utility to execute the INMOD routine.

Compiling the INMOD

Use this command to compile the INMOD routine source file:

cobol cobolin (apo osdeck

The following terminal output signifies a successful compile operation:

REL2.3 OS/VS COBOL IN PROGRESS R; T=0.24/0.33 09:46:40

Link-Editing the INMOD

Use this EXEC to link-edit the resulting COBOLIN text file with the TERCOBOL load module:

&TRACE CP LINK TDAPP 191 402 RR ACCESS 402 B FILEDEF INMODR DISK COBOLIN TEXT A FILEDEF CLILIB DISK CLI TXTLIB B FILEDEF PP1LIB DISK PP1 TXTLIB B

Teradata FastLoad Reference

D­3

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For VM FILEDEF SYSLIB DISK COBLIBVS TXTLIB Y LKED LINKCOB (MAP XREF LET LIBE MYLOADLB &EXIT

Note: The file LINKCOB TEXT contains the these link-edit control statements, which must begin after column one:

INCLUDE PP1LIB(TERCOBOL) INCLUDE INMODR ENTRY TERCOBOL NAME COBINMOD

Executing the INMOD

Use this EXEC to invoke the FastLoad utility and execute the INMOD routine:

&TRACE CP LINK TDAPP 191 402 RR ACCESS 402 B FILEDEF SYSPRINT TERMINAL (LRECL 120 RECFM V FILEDEF SYSIN DISK DOCINCOB FSTSYSIN FILEDEF INDATA DISK COBEXMPL DATA A GLOBAL TXTLIB COBLIBVS CLI GLOBAL LOADLIB MYLOADLB EXEC FAST &EXIT

Note: The SYSIN file DOCINCOB FSTSYSIN contains these statements:

LOGON e/JLH,HOOD; BEGIN LOADING cobol_example ERRORFILES cobol_error1,cobol_error2; DEFINE T1(CHAR(10)),T2(CHAR(390)) INMOD=COBINMOD; INSERT cobol_example (:t1,:t2); END LOADING; LOGOFF;

Note also: The table cobol_example used in this example was previously created as:

CREATE table cobol_example (F1 CHAR(10), F2 VARCHAR(390)) PRIMARY INDEX(F1);

D­4

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For VM

Using PL/I

INMOD routines written in PL/I must: · · · · Contain the entry point name DYNAMN Be link-edited with the PLIA load module Be stored as a VM LOADLIB file Include `OPTIONS(MAIN)'

Procedure

To create and use a PL/I INMOD routine on VM:

Step Action

1 2 3 4

Prepare the INMOD routine source file. Compile the INMOD routine. Link-edit the INMOD program with the specified load modules and store the results as a LOADLIB file. Invoke the FastLoad utility to execute the INMOD routine.

Compiling the INMOD

Use this command to compile the INMOD routine source file:

pliopt MYINMOD source (osdeck

The following terminal output signifies a successful compile operation:

PL/1 OPTIMIZER V1 R3.1 PTF 70 TIME: 16.55.25 DATE: 15 JAN 98 COMPILER DIAGNOSTIC MESSAGES ERROR ID L NUMBER MESSAGE DESCRIPTION COMPILER INFORMATORY MESSAGES IEL0430I I 10 NO 'MAIN' OPTION ON EXTERNAL PROCEDURE. END OF COMPILER DIAGNOSTIC MESSAGES COMPILE TIME 0.00 MINS SPILL FILE: 0 RECORDS, SIZE 3491

Teradata FastLoad Reference

D­5

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For VM

Link-Editing the INMOD

Use this EXEC to link-edit the resulting MYINMOD text file with the PLIA module:

&TRACE CP LINK TDAPP 191 402 RR ACCESS 402 B FILEDEF INMODR DISK PLIINMOD TEXT A FILEDEF PLIA DISK PLIA TEXT B FILEDEF SYSLIB DISK PLILIB TXTLIB Y LKED LINKCONT (MAP XREF LET LIBE MYLOADLB &EXIT

Note: The file LINKCONT TEXT contains these link-edit control statements, which must begin after column one:

INCLUDE INCLUDE ENTRY NAME PLIA INMODR DYNAMN MYINMOD(R)

Executing the INMOD

Use this EXEC to invoke the FastLoad utility and execute the INMOD routine:

&TRACE CP LINK TDAPP 191 402 RR ACCESS 402 B FILEDEF SYSPRINT TERMINAL (LRECL 120 RECFM V FILEDEF SYSIN DISK DOCINPUT FSTSYSIN FILEDEF EXPORT DISK STUFF CNTL A GLOBAL TXTLIB PLILIB GLOBAL LOADLIB MYLOADLB EXEC CFAST &EXIT

Note: The file DOCINPUT FSTSYSIN contains these FastLoad statements:

LOGON 5/JLH,HOOD; BEGIN LOADING stuff ERRORFILES stuff_error1,stuff_error2; DEFINE T1(CHAR(40)),T2(CHAR(40)) INMOD=MYINMOD; INSERT stuff(:T1,:T2); END LOADING; LOGOFF;

Note also: The table called "stuff" was previously created using this statement:

CREATE TABLE stuff (t1 CHAR(40) ,t2 CHAR(40)) primary index(t1);

D­6

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For VM

Using SAS/C

INMOD and notify exit routines written in SAS/C must: · · Contain the entry point name _dynamn Be link-edited with the following load modules: · BLKPLIA · BLKPLI · DBCMEM Be stored as a VM LOADLIB file

·

Procedure

To create and use a SAS/C INMOD or notify exit routine on VM:

Step Action

1 2 3 4

Prepare the INMOD routine source file. Compile the INMOD routine. Link-edit the INMOD program with the specified load modules and store the results as a LOADLIB file. Invoke the FastLoad utility to execute the INMOD routine.

Compiling the Routine

Use this command to compile the INMOD or notify exit routine source file:

lc370 myinmod (dynamndef

The following terminal output signifies a successful compile operation:

SAS/C Release 5.00G(CMS) Licensed to : <Company Name> Site Number <Site Number> *** No errors; No warnings; No user suppressed warnings

Link-Editing the Routine

Use this EXEC to link-edit the resulting PLIINMOD text file with the BLKPLIA, BLKPLI, and DBCMEM load modules:

GLOBAL TXTLIB LC370STD LC370BAS CLINK myinmod (LKED

Note: The link-edit control statements shown above must begin after column one.

Teradata FastLoad Reference

D­7

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For VM

Executing FastLoad

Use this EXEC to invoke the FastLoad utility and execute the INMOD or notify exit routine:

'FILEDEF SYSPRINT DISK stuff SYSPRINT A (LRECL 121 RECFM V' 'FILEDEF SYSTERM DISK stuff SYSTERM A (LRECL 121 RECFM V' 'FILEDEF INFILE DISK stuff INFILE A' /* INPUT DATA FILE */ 'FILEDEF SYSIN DISK stuff CNTL A' /* COMMAND CONTROL FILE */ 'GLOBAL TXTLIB RH30CLI' 'FAST options '

Note: In this example, the file STUFF CNTL contains these FastLoad statements:

LOGON 5/JLH,HOOD; BEGIN LOADING stuff ERRORFILES stuff_error1,stuff_error2; DEFINE T1(CHAR(40)),T2(CHAR(40)) INMOD=MYINMOD; INSERT stuff(:T1,:T2); END LOADING; LOGOFF;

Note also The table called "stuff" was previously created using this statement:

CREATE TABLE stuff (t1 CHAR(40) ,t2 CHAR(40)) primary index(t1);

D­8

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For MVS

For MVS

Using Assembler

INMOD routines written in Assembler do not need to be link-edited with other load modules.

Procedure

To create and use an Assembler INMOD routine on MVS:

Step Action

1 2 3

Prepare the INMOD routine source file. Compile the INMOD program. Invoke the FastLoad utility to execute the INMOD routine.

Example

The INMOD routine in the following example reads a record from the input data source and adds a four byte integer field to the front of the record. The new field contains a sequence record that ranges from one to the total number of input records.

//JLHFAST JOB 1,'FASTLOAD/INMOD',MSGCLASS=A,CLASS=B,NOTIFY=JLH //**************************************************************** //* Compile and link-edit the INMOD * //**************************************************************** //ASMCOMPL EXEC ASMFCL //**************************************************************** //* The INMOD starts here * //**************************************************************** //ASM.SYSIN DD * <Assembler source goes here> /* //LKED.SYSLMOD DD DSN=JLH.INMOD.LOAD,DISP=SHR //LKED.SYSIN DD * NAME ASMINMOD(R) /* //**************************************************************** //* Execute BTEQ to create a Teradata DBS table * //* * //* The BTEQ statement .run ddname=logon allows the user to * //* place sensitive logon information in a secure data set (in * //* this case, the ddname is logon). Logging on from a data *

Teradata FastLoad Reference

D­9

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For MVS //* set prevents this sensitive information from appearing in * //* the SYSIN file. * //**************************************************************** /* //BTEQ EXEC TDSBTEQ //LOGON DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR //SYSIN DD DATA,DLM=## .run ddname=logon drop table asm_example; drop table asm_error1; drop table asm_error2; create table asm_example (f1 INTEGER, f2 CHAR(80)) primary index(f1); .quit ## //* //****************************************************************** //* Execute FastLoad * //****************************************************************** //FAST EXEC TDSFAST //FASTLOAD.STEPLIB DD // DD DSN=JLH.INMOD.LOAD,DISP=SHR //FASTLOAD.SYSIN DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR // DD DATA,DLM=$$ BEGIN LOADING asm_example ERRORFILES asm_error1,asm_error2; DEFINE T1 (integer), T2 (CHAR(80)) INMOD=ASMINMOD; INSERT asm_example (:t1,:t2); END LOADING; LOGOFF; $$ //FASTLOAD.INDATA DDDSN=JLH.ASMXMPL.DATA,DISP=SHR //**************************************************************** //* Using BTEQ, select the rows loaded by FastLoad * //**************************************************************** //BTEQ EXEC TDSBTEQ //LOGON DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR //SYSIN DD DATA,DLM=## .runddname logon select * from asm_example order by 1; .quit ##

D ­ 10

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For MVS

Using COBOL

INMOD routines written in COBOL on MVS must: · · · Contain the entry point name BLKEXIT Be link-edited with the TERCOBOL load module. Be stored as an MVS load library file

Procedure

To create and use a COBOL INMOD routine on MVS:

Step Action

1 2 3 4

Prepare the INMOD routine source file. Compile the INMOD routine. Link-edit the INMOD program with the specified load modules and store the results as an MVS load library file. Invoke the FastLoad utility to execute the INMOD routine.

Example

The INMOD in the following example reads five 80-byte records from the input data source and returns a single, 400 byte record to FastLoad. BTEQ1 creates a table on the Teradata RDBMS and BTEQ2 selects rows from the table to check for properly loaded data.

//JLHFAST JOB 1,'FASTLOAD/INMOD',MSGCLASS=A,CLASS=B,NOTIFY=JLH //***************************************************************** //* Compile and link-edit the INMOD * //**************************************************************** //COBCOMPL EXEC COBUCL //**************************************************************** //* The INMOD starts here * //**************************************************************** //COB.SYSIN DD * <COBOL source goes here> //**************************************************************** //* Link-edit the INMOD with the Teradata-supplied module * //* (TERCOBOL) * //**************************************************************** /* //LKED.SYSLIB DD DSN=SYS1.COBLIB,DISP=SHR // DD DSN=TERADATA.APPLOAD,DISP=SHR //LKED.SYSLMOD DD DSN=JLH.INMOD.LOAD,DISP=SHR //LKED.SYSIN DD * INCLUDE SYSLIB(TERCOBOL) ENTRY TERCOBOL

Teradata FastLoad Reference

D ­ 11

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For MVS NAME COBINMOD(R) /* //**************************************************************** //* Execute BTEQ to create a Teradata DBS table * //* * //* The BTEQ statement .run ddname=logon allows the user to * //* place sensitive logon information in a secure data set (in * //* this case, the ddname is logon). Logging on from a data * //* set prevents this sensitive information from appearing in * //* the SYSIN file. * //**************************************************************** //BTEQ1 EXEC TDSBTEQ //LOGON DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR //SYSIN DD DATA,DLM=## .run ddname=logon drop table cobol_example; drop table example_error1; drop table example_error2; create table cobol_example (f1 char(10), f2 varchar(390)) primary index(f1); .quit ## //* //FAST EXEC TDSFAST //FASTLOAD.STEPLIB DD // DD DSN=JLH.INMOD.LOAD,DISP=SHR //FASTLOAD.SYSIN DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR // DD DATA,DLM=$$ BEGIN LOADING cobol_example, ERRORFILES cobol_error1,cobol_error2; DEFINE T1 (CHAR(10)) ,T2 (CHAR(390)) INMOD=COBINMOD; INSERT cobol_example (:t1,:t2); END LOADING; LOGOFF; $$ //FASTLOAD.INDATA DD DSN=JLH.COBXMPL.DATA,DISP=SHR //* //**************************************************************** //* Using BTEQ, select the rows loaded by FastLoad * //**************************************************************** //* //BTEQ2 EXEC TDSBTEQ //LOGON DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR //SYSIN DD DATA,DLM=## .run ddname=logon select * from cobol_example order by 1; .quit ##

D ­ 12

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For MVS

Using PL/I

INMOD routines written in PL/I must: · · · · Contain the entry point name DYNAMN Be link-edited with the PLIA load module Be stored as an MVS load library file Include `OPTIONS(MAIN)'

Procedure

To create and use a PL/I INMOD routine on MVS:

Step Action

1 2 3 4

Prepare the INMOD routine source file. Compile the INMOD routine. Link-edit the INMOD program with the specified load modules and store the results as an MVS load library file. Invoke the FastLoad utility to execute the INMOD routine.

Example

The INMOD routine in the following example reads the data from the input source and presents it, as read, to the FastLoad utility. BTEQ1 creates a table on the Teradata RDBMS and BTEQ2 selects rows from the table to check for properly loaded data. You could modify this basic program to select records that meet a specified criteria, convert certain fields or perform other preprocessing functions.

//IEL1CL EXEC IEL1CL,MEM=MYINMOD //PLI.SYSIN DD DSN=JLH.SOURCE(&MEM),DISP=SHR //PLI.SYSPRINT DD SYSOUT=* //LKED.SYSPRINT DD SYSOUT=* //LKED.SYSLMOD DD DSN=JLH.INMOD.LOAD(&MEM),DISP=SHR //* //LINK EXEC PGM=IEWL,PARM=(LIST,LET,MAP,NORENT,NOREUS) //SYSPRINT DD SYSOUT=* //SYSLOUT DD SYSOUT=* //SYSUT1 DD UNIT=VIO,SPACE=(CYL,(1,1)) //SYSLIB DD DSN=TERADATA.APPLOAD,DISP=SHR // DD DSN=SYS1.SCEELKED,DISP=SHR // DD DSN=JLH.INMOD.LOAD,DISP=SHR //OBJLIB DD DSN=JLH.UTILS.OBJLIB,DISP=SHR //SYSLMOD DD DSN=JLH.INMOD.LOAD,DISP=SHR //SYSLIN DD * INCLUDE SYSLIB(MYINMOD)

Teradata FastLoad Reference

D ­ 13

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For MVS INCLUDE OBJLIB(PLIA) ENTRY DYNAMN NAME MYINMOD(R) //**************************************************************** //* Execute BTEQ to create a Teradata DBS table * //* * //* The BTEQ statement .run ddname=logon allows the user to * //* place sensitive logon information in a secure data set (in * //* this case, the ddname is logon). Logging on from a data * //* set prevents this sensitive information from appearing in * //* the SYSIN file. * //**************************************************************** //BTEQ1 EXEC TDSBTEQ //LOGON DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR //SYSIN DD DATA,DLM=## .run ddname=logon drop table stuff; drop table stuff_error1; drop table stuff_error2; create table stuff (t1 char(40), t2 char(40)) primary index(t1); .quit ## //* //* //FAST EXEC TDSCFAST //FASTLOAD.STEPLIBDD // DD DSN=JLH.INMOD.LOAD,DISP=SHR //FASTLOAD.SYSIN DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR // DD DATA,DLM=$$ BEGIN LOADING stuff ERRORFILES stuff_error1,stuff_error2; DEFINE t1 (char(40)), t2 (char(40)) INMOD=MYINMOD; INSERT INTO stuff (:t1,:t2); END LOADING; logoff; $$ //FASTLOAD.EXPORT DD DSN=JLH.STUFF.DATA,DISP=SHR //**************************************************************** //* Using BTEQ, select the rows loaded by FastLoad * //**************************************************************** //BTEQ2 EXEC TDSBTEQ //LOGON DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR //SYSIN DD DATA,DLM=## .run ddname=logon select * from stuff order by 1; .quit

D ­ 14

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For MVS

Using SAS/C

INMOD and notify exit routines written in SAS/C must: · · Contain the entry point name _dynamn Be link-edited with the following load modules: · BLKPLIA · BLKPLI · DBCMEM Be stored as an MVS load library file

·

Procedure

To create and use a SAS/C INMOD or notify exit routine on MVS:

Step Action

1 2 3 4

Prepare the INMOD routine source file. Compile the INMOD routine. Link-edit the INMOD program with the specified load modules and store the results as an MVS load library file. Invoke the FastLoad utility to execute the INMOD routine.

Example

The INMOD routine in the following example reads the data from the input source and presents it, as read, to the FastLoad utility. BTEQ1 creates a table on the Teradata RDBMS and BTEQ2 selects rows from the table to check for properly loaded data. You could modify this basic program to select records that meet a specified criteria, convert certain fields or perform other preprocessing functions.

//* Compile and link-edit the INMOD //****************************************************************** //SASCOMPL EXEC LC370CL,ENTRY=DYNNR,PARM.C='DYNAMNDEF,OPTIMIZE' //C.SYSLIB DD // DD DISP=SHR,DSN=<Your macro library> //C.SYSIN DD * <SAS/C Source goes here> //****************************************************************** //* Link-edit the INMOD * //****************************************************************** //LKED.SYSLMOD DD DISP=SHR,DSN=<Your load library> //LKED.SYSIN DD * NAME MYINMOD(R)

Teradata FastLoad Reference

D ­ 15

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For MVS //**************************************************************** //* Execute BTEQ to create a Teradata DBS table * //* * //* The BTEQ statement .run ddname=logon allows the user to * //* place sensitive logon information in a secure data set (in * //* this case, the ddname is logon). Logging on from a data * //* set prevents this sensitive information from appearing in * //*the SYSIN file. * //**************************************************************** //BTEQ1 EXEC TDSBTEQ //LOGON DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR //SYSIN DD DATA,DLM=## .run ddname=logon drop table stuff; drop table stuff_error1; drop table stuff_error2; create table stuff (t1 char(40), t2 char(40)) primary index(t1); .quit ## //* //* //FAST EXEC TDSFAST,PARM='INMODTYPE=SAS_C' //FASTLOAD.STEPLIB DD // DD // DD DISP=SHR,DSN=<your load library> //FASTLOAD.SYSIN DD DATA,DLM=$$ LOGON <Logon userid and password> BEGIN LOADING stuff ERRORFILES stuff_error1,stuff_error2; DEFINE t1 (char(40)), t2 (char(40)) INMOD=MYINMOD; INSERT INTO stuff (:t1,:t2); END LOADING; logoff; $$ //FASTLOAD.EXPORT DD DSN=JLH.STUFF.DATA,DISP=SHR //* //**************************************************************** //* Using BTEQ, select the rows loaded by FastLoad * //**************************************************************** //BTEQ2 EXEC TDSBTEQ //LOGON DD DSN=JLH.CNTL(JLHLOGON),DISP=SHR //SYSIN DD DATA,DLM=## .run ddname=logon select * from stuff order by 1; .quit

D ­ 16

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For UNIX

For UNIX

Compiling and Linking Routines

For MP-RAS, SOLARIS-SPARC, SOLARIS-INTEL

Use the following syntax to compile source files into a shared object module for INMOD or notify exit routines on MP-RAS, SOLARIS-SPARC, and SOLARISINTEL client systems:

cc -G

-KPIC

sourcefile

-o shared-object-name

GK01C037

where

Syntax element... Is the...

cc -G -KPIC -o shared-object-name

call to the program that invokes the native UNIX C compiler. linker option that generates a shared object file. compiler option that generates Position Independent Code (PIC) for all user exit routines. switch to the linker. name of your shared object file. This is the name you specify as the: · · INMOD= name parameter in the DEFINE command of your FastLoad job script EXIT name parameter of the NOTIFY command of your FastLoad job script

The shared-object-name can be any valid UNIX file name. Note: When creating a shared object module for an INMOD routine, if the INMOD uses functions from an external library, then that library must be statically linked with the INMOD routine so that the FastLoad utility can resolve the external references. sourcefile UNIX file name(s) of the source file(s) for your INMOD or notify exit routine.

Note: See "Syntax Diagram Conventions" on page A-2 for a description of the syntax diagrams used in this book.

Teradata FastLoad Reference

D ­ 17

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For UNIX

For HP-UX

Use the following syntax to compile and link source files into a shared object module for INMOD notify exit routines on HP-UX client systems:

Compile Syntax

cc

-Aa

-D_HPUX_SOURCE

+z

+ul

-c

-sourcefile.c

2411A001

Link Syntax

ld

-b

objectfile.o

-o shared-object-name

2411A002

where

Syntax element... Is the...

-Aa -b -c cc -D_HPUX_ SOURCE ld objectfile -o

compiler option which enables compiler to conform to the ANSI standard. linker option that generates a shared object file. compile-only option (does not link). call to the program that invokes the native UNIX C compiler. symbol that enables the compiler to access macros and typedefs that are not defined by the ANSI Standard but are provided by the HPUX Operating System. call to the program that invokes the native UNIX linker. file that the compiler generates and linker uses to generate shared-object-name. switch to the linker.

D ­ 18

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For UNIX

Syntax element... Is the...

shared-object-name

name of your shared object file. This is the name you specify as: · · The INMOD modulename parameter of the DEFINE command. The EXIT name parameter for the NOTIFY command of your FastLoad job script.

The shared-object-name can be any valid UNIX file name. Note: When creating a shared object module for an INMOD routine, if the INMOD uses functions from an external library, then that library must be statically linked with the INMOD routine so that the FastLoad utility can resolve the external references.name. +z sourcefile +ul compiler option that generates Position Independent Code for all user exit routines. UNIX file name(s) of the source file(s) for your INMOD or notify exit routine. compiler option that allows pointers to access non-natively aligned data.

For IBM-AIX

Use the following syntax to compile and link source files into a shared object module for INMOD or notify exit routines on IBM-AIX client systems:

Compile Syntax

cc

-brtl

-fPIC

sourcefile.c

2411A003

Teradata FastLoad Reference

D ­ 19

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For UNIX

Link Syntax for INMOD Routine

ld

-G

-eBLKEXIT

-bexpall objectfile.o

A

A

-o shared-object-name

-lm

-lc

2411A004

Link Syntax for Notify Exit Routine

ld -G -e_dynamn -bE:export_dynamn.txt A

A

objectfile.o

-o shared-object-name

-lm

-lc

2411A005

where

Syntax element... Is the...

-be: export_dynamn.txt -bexpall

the linker option that exports the symbol "_dynamn" explicitly and the file export-dynamn.txt contains the symbol. option that exports all global symbols, except imported symbols, un-referenced symbols defined in archive members, and symbols beginning with an underscore (_). option that tells the linkage editor to accept both .so and .a library file types. call to the program that invokes the native UNIX C compiler. option that sets the entry point of the INMOD exit routine to BLKEXIT. option that sets the entry point of the NOTIFY exit routine to _dynamn. compiler option that generates Position Independent Code for all user exit routines. option that produces a shared object-enabled for use with the runtime linker. link with the /lib/libc.a library.

-brtl cc -eBLKEXIT -e_dynamn -fPIC -G -lc

D ­ 20

Teradata FastLoad Reference

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For UNIX

Syntax element... Is the...

ld -lm -o objectfile shared-object-name

call to the program that invokes the native UNIX linker. link with the /lib/libc.a library. switch to the linker. file that the compiler generates and linker uses to generate shared-object-name. name of your shared object file. This is the name you specify as: · · The INMOD modulename parameter of the DEFINE command The EXIT name parameter for the NOTIFY command of your FastLoad job script.

The shared-object-name can be any valid UNIX file name. Note: When creating a shared object module for an INMOD routine, if the INMOD uses functions from an external library, then that library must be statically linked with the INMOD routine so that the FastLoad utility can resolve the external references.name. sourcefile UNIX file name(s) of the source file(s) for your INMOD or notify exit routine.

Teradata FastLoad Reference

D ­ 21

Appendix D: Compiling, Linking, and Executing INMOD and Notify Exit Routines For Windows

For Windows

Using C

The FastLoad INMOD routine is implemented as a DLL. A make file "blkexit.mak" is included to allow you to modify and rebuild this DLL. To rebuild, enter this command:

nmake -f blkexit.mak

When the DLL, blkexit.dll, is rebuilt, you should put the DLL in the same directory as fastload.exe.

D ­ 22

Teradata FastLoad Reference

Glossary

A

Access Module: A software component that provides a standard set of I/O functions to access data on a specific device.

B

BTEQ: Basic Teradata Query facility. A utility that allows users on a workstation to access data on a Teradata RDBMS, and format reports for both print and screen output.

C

CLI: Call-Level Interface. The interface between the application program and the MTDP (for network-attached clients) or TDP (for channel-attached clients). Client: A computer that can access the Teradata RDBMS. COP: Communications Processor. One kind of interface processor (IFP) on the Teradata RDBMS. A COP contains a gateway process for communicating with workstations via a network. COP Interface: Workstation-resident software and hardware, and Teradata RDBMS-resident software and hardware, that allows workstations and the Teradata RDBMS to communicate over networks.

D

DEFINE Statement: A statement preceding the INSERT statement that describes the fields in a record before the record is inserted in the FastLoad table. This statement is similar to the SQL USING clause.

F

Formatted Records: See Records. FastLoad: Fast Data Load utility. A program that loads empty tables on the Teradata RDBMS with data from a network-attached or channel-attached client.

Teradata FastLoad Reference

Glossary ­ 1

Glossary

G

Gateway: A device that connects networks having different protocols.

I

INMOD Routine: Software module that communicates with FastLoad and can be written in C language (for network-attached platforms), or SAS/S, COBOL, PL/I or Assembler (for channel-attached platforms).

L

LAN: Local Area Network. LANs supported by NCR products must conform to the IEEE 802.3 standard (Ethernet LAN). Locks: FastLoad automatically locks any table being loaded and frees a lock only after an END LOADING statement is entered. Therefore, access to a table is available when FastLoad completes.

M

MOSI: Micro Operating System Interface. A library of routines that implement operating system dependent and protocol dependent operations on the workstation. MTDP: Micro Teradata Director Program. A library of routines that implement the session layer on the workstation. MTDP is the interface between CLI and the Teradata RDBMS. MVS (Multiple Virtual Storage): One of the primary operating systems for large IBM computers.

N

Network: In the context of the Teradata RDBMS, a LAN (see LAN). Nullif Option: This option allows the user to null a column in a table under certain conditions; it is only used in conjunction with DEFINE statements.

P

Protocol: The rules for the format, sequence and relative timing of messages exchanged on a network.

Glossary ­ 2

Teradata FastLoad Reference

Glossary

R

Records: When using the FastLoad utility, both formatted and un-formatted records are accepted for loading. A formatted record, in the Teradata RDBMS world, consists of a record created by a Teradata RDBMS utility, such as BTEQ, where the record is packaged with begin- and end-record bytes specific to the Teradata RDBMS. Un-formatted records are any records not originating on a Teradata RDBMS, such as Lotus 1-2-3 files. These files contain records that must be defined before loading onto the Teradata RDBMS.

S

Session: A session begins when the user logs on to the Teradata RDBMS and ends when the user logs off the Teradata RDBMS. Also called a Teradata RDBMS session. SQL: (Structured Query Language) See Teradata SQL.

T

TCP/IP: Transmission Control Protocol/Internet Protocol. Teradata SQL: The Teradata RDBMS dialect of the relational language SQL, having data definition and data manipulation statements. A data definition statement would be a CREATE TABLE statement and a data manipulation statement would be a data retrieval statement (a SELECT statement).

U

Unformatted Records: See Records. Unicode: A fixed-width (16 bits) encoding of virtually all characters present in all languages in the world.

V

Varbyte: A data type that represents a variable-length binary string. Varchar: A data type that represents a variable-length non-numeric character. Vargraphic: A data type that represents a variable-length string of characters. VM (Virtual Machine): One of the primary operating systems for large IBM computers.

Teradata FastLoad Reference

Glossary ­ 3

Glossary

W

Workstation: A network-attached client.

Glossary ­ 4

Teradata FastLoad Reference

Index

Symbols * (asterisk character), as the minimum and maximum sessions specification, SESSIONS command 3­61 .* default VALUES clause, INSERT statement 3­37 Bypass-label (BPL) tapes, not supported 1­14 BYTE data type specification, DEFINE command 3­13 input length and field description 3­14 NULLIF restrictions and limitations 3­17 BYTEINT data type specification, DEFINE command 3­13 input length and field description 3­14 NULLIF restrictions and limitations 3­18

A Access privileges, required BEGIN LOADING command 3­6 INSERT statement 3­38 acctid specification, LOGON command 3­44 acronyms iv ANSI/SQL DateTime data types 2­22 DEFINE command 3­23 INSERT statement 3­39 specifications, table of 3­23 ANSI/SQL DateTime specifications 2­23 ANSIDATE keyword, DATEFORM command 3­10 ASCII character set code 1­5 specification, SET SESSION CHARSET command 3­70 ASCII character set code 2­6 Assembler program examples compiling, linking and executing on MVS systems D­9 on VM systems D­2 INMOD routine C­2 AXSMOD character set 1­7, 2­22 AXSMOD command 1­16, 3­2

C c specification, SET RECORD command 3­65 Character sets AXSMOD 1­7, 2­22 determining 2­6, 3­71 programming considerations 2­22 specifications 2­5 supported 1­5 CHARACTERS data type specification, DEFINE command 3­13 input length and field description 3­14 NULLIF restrictions and limitations 3­18 character-to-date data conversions 1­8 character-to-numeric data conversions 1­8 CHARSET configuration file specification 2­21 charsetcode specification, SET SESSION CHARSET command 3­70 CHECKPOINT keyword, BEGIN LOADING command 3­5, 3­7 Checkpoints description 1­9 internal 3­28 trade-offs 2­23 CLEAR command 1­16, 3­9 using with HELP TABLE commands 3­35 cname specification INSERT statement 3­37 COBOL program examples compiling, linking and executing on MVS systems D­11 on VM systems D­3 INMOD routine C­6 command specification, OS command 3­51 Commands DATEFORM 1­16, 3­10

B BEGIN LOADING command 1­16, 3­4 example 3­8 BINARY specification, SET RECORD command 3­65 BLKEXIT.C INMOD routine calling 2­37 description 2­37 BLKEXITR.C INMOD routine calling 2­37 description 2­36 BUFSIZE configuration file specification 2­21

Teradata FastLoad Reference

Index ­1

Index

DEFINE 1­16, 3­12 END LOADING 1­16, 3­28 ERRLIMIT 1­16, 3­30 commands AXSMOD 1­16, 3­2 BEGIN LOADING 1­16, 3­4 CLEAR 1­16, 3­9 HELP 1­15, 3­32 HELP TABLE 1­15, 3­35 LOGOFF 1­15, 3­41 LOGON 1­15, 3­43 NOTIFY 1­15, 3­46 OS 1­15 QUIT 1­15, 3­56 RECORD 1­16, 3­58 SESSIONS 1­15, 3­61 SET RECORD 1­17, 3­64 SET SESSION CHARSET 1­17, 3­70 SHOW 1­16, 3­72 SHOW VERSIONS 1­16, 3­74 SLEEP 1­16, 3­76 TENACITY 1­16, 3­78 Comments, programming considerations 2­24 Compiling and linking routines HP-UX D­18 UNIX D­17 Concatenated data sets, not supported 1­14 Concurrent load utility tasks, restrictions and limitations 2­24 Configuration file 2­21 contents 2­21 file name and location 2­21 optional specification 2­2 overriding internal utility defaults 2­20 parameters overridden by runtime parameters 2­20 BUFSIZE 2­4 CHARSET 2­5 MAXSESS 2­7 MINSESS 2­7 SLEEP 2­7 TENACITY 2­8 parameters overridden by utility commands 2­20 SESSIONS 3­61 SET SESSION CHARSET 3­70 SLEEP 3­76 TENACITY 3­78 processing by the FastLoad utility 2­22 specifications 2­20 using 2­20 configuration file 2­21 Conversions numeric-to-numeric 1­8

CREATE TABLE statement, Teradata SQL 1­17

D Data formats, SET RECORD command 3­65 handling, defined 1­15 length field, input record 1­11, 1­13 transfer capabilities 1­8 type specifications, DEFINE command 3­14 input length and field descriptions 3­14, 3­15,

3­16

Data conversions capabilities 1­8 example 2­25 limiting factors 2­25 character-to-date 1­8 character-to-numeric 1­8 date-to-character 1­8 integer-to-decimal 1­8 numeric-to-numeric 1­8 data types ANSI/SQL DateTime 2­23 DATABASE statement, Teradata SQL 1­17 datatype specification, DEFINE command 3­13 DATE data type specification, DEFINE command 3­13 input length and field description 3­15 DATEFORM command 1­16, 3­10 DateTime specifications 2­23 DateTime specifications, table of 3­23 date-to-character data conversions 1­8 dbname specification BEGIN LOADING command 3­4 HELP TABLE command 3­35 DDNAME=filename data source specification, DEFINE command 3­13 Decimal format 2­25 zoned 1­8 DECIMAL data type specification, DEFINE command 3­13 input length and field description 3­15 NULLIF restrictions and limitations 3­19 DEFINE command 1­16, 3­12 using more than one 3­22 using with HELP TABLE command 3­35 using with INSERT statements 3­38 DELETE, Teradata SQL statement 1­17 Delimiter characters, in comments 2­24 DISPLAY_ERRORS keyword specification, SET

Index ­2

Teradata FastLoad Reference

Index

RECORD command 3­65 documentation installation ii on the Web iii related to FastLoad iii DROP TABLE, Teradata SQL statement 1­17 Duplicate records, in FastLoad error tables 3­6 rows, not inserted by the FastLoad utility 1­2,

command 3­6 Exit modules, creating for NOTIFY 3­50 EXIT name specification, NOTIFY command 3­46,

3­47

F FastExport utility programming considerations character set specifications 2­22 FastLoad job scripts programming considerations 2­20 checkpoint trade-offs 2­23 data conversion factors 2­25 file size restrictions 2­28 non-unique index sorts 2­26 range constraints 2­27 record mode load anomaly 2­28 session limits 2­28 space requirements and limitations 2­28 writing 2­39 FastLoad utility character sets supported 1­5 data conversion capabilities 1­8 data transfer capabilities 1­8 file requirements 2­2 file types supported 1­10 formatted data 1­11 input data formats 1­10 input record fields 1­11, 1­13 invoking 2­2 in batch mode 2­3 in interactive mode 2­2 operating features and capabilities 1­4 operating modes 1­4 overview 1­2 pausing 3­41, 3­56 programming considerations character set specifications 2­22 comments 2­24 restarting 3­58 NOTIFY implications 3­49 starting, LOGON command 3­45 terminating 2­12 abort 2­13 normal 2­12 unformatted data 1­12 unsupported data sets and tapes 1­14 variable-length text 1­13 Field values, inserting 3­38 fieldname specification DEFINE command 3­13

2­26, 2­46

E EBCDIC character set codes 2­5 EBCDIC character set codes 1­5 END LOADING command 1­16, 3­28 End-loading phase 3­28 End-of-record field, input record 1­11 endrecordnumber specification, RECORD command 3­58 ERRLIMIT command 1­16, 3­30 Error tables correcting errors recorded in 2­48, 2­50 dropped if empty 3­28 format of 2­47 reusing table names 3­6 ERRORFILES keyword, BEGIN LOADING command 3­4 errorname1 specification, BEGIN LOADING command 3­5, 3­6 errors constraint violations 2­46 conversion errors 2­46 correcting 2­48, 2­50 in the first error table 2­48 in the second error table 2­50 duplicate rows 2­46 error recording 2­47 error table format 2­47, 3­6 handling 2­46 NOTIFY implications 3­49 insertion errors limiting 3­30 restarting after 3­30 specifying the ERRLIMIT value 2­26 unavailable AMP conditions 2­46 unique primary index violations 2­46 errortname1 specification, BEGIN LOADING command 3­5 errortname2 specification, BEGIN LOADING

Teradata FastLoad Reference

Index ­3

Index

INSERT statement 3­37 FILE=filename data source specification, DEFINE command 3­13 Files size restrictions 2­28 types supported 1­10 FLOADCFG 2­21 FLOADCFG, configuration file label 2­21 floadcfg.dat, configuration file name 2­21 FLOAT data type specification, DEFINE command 3­13 input length and field description 3­15 NULLIF restrictions and limitations 3­19 Foreign key references 2­26 Formatted data 1­11 FORMATTED keyword specification, SET RECORD command 3­64

G GRAPHIC data type specifications DEFINE command 3­13, 3­16, 3­17 input length and field description 3­16 NULLIF restrictions and limitations 3­20

H HELP command 1­15, 3­32 HELP TABLE command 1­15, 3­35 HIGH specification, NOTIFY command 3­46 hours specification, TENACITY command 3­78 HP-UX compiling and linking routines D­18

I IBM-AIX compiling and linking routines D­19 Indicator bit positions, illustration 3­8 bytes field, input record 1­11, 1­13 INDICATORS keyword, BEGIN LOADING command 3­5, 3­7, 3­8 init-string specification, AXSMOD command 3­2 INMOD routines and programs Assembler program listing C­2 BLKEXIT.C calling 2­37

description 2­37 BLKEXITR.C calling 2­37 description 2­36 COBOL program listing C­6 compiling, linking and executing Assembler on MVS D­9 Assembler on VM D­2 C on Windows NT or Windows 95 D­22 COBOL on MVS D­11 COBOL on VM D­3 PL/I on MVS D­13 PL/I on VM D­5 SAS/C on MVS D­15 SAS/C on VM D­7 creating your own 2­38 definition 2­30 entry points 2­32 examples 2­36 FastLoad-to-INMOD interface 2­32 INMOD type specification 2­7 INMOD-to-FastLoad interface 2­33 PL/I program listing C­9 platforms and programming languages 2­30 programming structure 2­31 SAS/C program listing C­10, C­11 using 2­30 INMOD=name specification, DEFINE command 3­13 INMODRETURN configuration file specification 2­21 input data fields, input record 1­11, 1­13 record fields 1­11, 1­13 INSERT statement, Teradata SQL 1­17, 3­37 insertion errors limiting 3­30 restarting after 3­30 installation information ii INTEGER data type specification, DEFINE command 3­13 input length and field description 3­15 NULLIF restrictions and limitations 3­21 integer, CHECKPOINT specification, BEGIN LOADING command 3­4, 3­5, 3­7 INTEGERDATE keyword, DATEFORM command 3­10 integer-to-decimal data conversions 1­8 INTO tname specification, INSERT statement 3­37 Invalid record numbers 3­59

Index ­4

Teradata FastLoad Reference

Index

J job scripts definition 2­39 example 2­39 multifile example first output file B­2 second output file B­4 third output file B­6 programming considerations error limits 2­26 JOB, SCOPE parameter specification, NOTIFY command 3­47 Join index, restrictions 2­26

MINSESS 2­22

2­21

minutes specification, SLEEP command 3­76 mm minor release number, SHOW VERSIONS command 3­75 MP-RAS compiling and linking routines D­17 MSG string specification, NOTIFY command 3­47 multifile FastLoad jobs definition 2­42 output file examples first output file B­2 second output file B­4 third output file B­6 running 2­42 MULTISET tables duplicate rows not inserted 1­2, 2­26 using the MultiLoad utility 1­23 MVS program examples for creating and using INMOD routines in Assembler D­9 in COBOL D­11 in PL/I D­13 in SAS/C D­15 for invoking FastLoad 2­9 INMOD routines using Assembler C­2 using COBOL C­6 using PL/I C­9 using SAS/C C­10, C­11

K KANJI character set codes 1­5, 2­5 KANJIEUC_0U specification, SET SESSION CHARSET command 3­70 KANJISJIS_0S specification, SET SESSION CHARSET command 3­70 KATAKANA character set codes 1­5, 2­5

L Locked tables, paused FastLoad jobs 3­41, 3­56 LOGOFF command 1­15, 3­41 using to pause jobs 3­41, 3­56 LOGON command 1­15, 3­43 LONG VARCHAR data type specification, DEFINE command 3­13 input length and field description 3­15 LONG VARGRAPHIC data type specification, DEFINE command 3­13 input length and field description 3­16 LOW specification, NOTIFY command 3­46

N name specification AXSMOD command 3­2 DEFINE command 3­13 NOTIFY command 3­47 Nested comments 2­24 Network File System, datasets, not using 1­4 new features i New-line characters in ASCII text files, accommodating 1­12 NFS. See Network File System NOBLOCK parameter, NOTIFY command 3­47 Nonlabel (NL) tapes, not supported 1­14 Non-unique index sorts 2­26 secondary indexes 1­23 NOSTOP keyword specification, SET RECORD command 3­65 NOTIFY command 1­15, 3­46 NULLIF option, DEFINE command 3­13

M max, session specification, SESSIONS command 3­61 maximum sessions 2­7 MAXSESS 2­22 configuration file specification 2­21 MEDIUM specification, NOTIFY command 3­46 min, sessions specification, SESSIONS command 3­61 minimum sessions 2­7

Teradata FastLoad Reference

Index ­5

Index

data type restrictions and limitations 3­17, 3­18,

3­19, 3­20, 3­21

Numeric fields, DEFINE command 3­21 numeric-to-numeric dataconversions 1­8

O OFF specification, NOTIFY command 3­46 option specification, NOTIFY command 3­47 Order of preference, utility variables 2­20 OS command 1­15 Output buffer size specification 2­4

P password specification, LOGON command 3­43 Paused FastLoad jobs definition 2­14 restarting 2­14 after a database overfill 2­15 after a system or FastLoad failure 2­14 after a Teradata RDBMS failure 2­15 after an AP reset 2­16 after an unrecoverable error 2­17 factors affecting 2­18 procedure 2­18 PL/I program examples compiling, linking and executing on MVS systems D­13 on VM systems D­5 INMOD routine C­9 prerequisites ii

Record mode load anomaly 2­28 Record numbers, invalid 3­59 Redundant conversions example 2­25 supported 1­8 release definition ii release summary ii Required access privileges BEGIN LOADING command 3­6 INSERT statement 3­38 Restart log table, PUBLIC access for BEGIN LOADING command 3­6 Restrictions and limitations, concurrent load tasks 2­24 RETRY command. Obsolete, see TENACITY runtime option specification return codes 2­12, 3­41, 3­56 RNAME parameter, NOTIFY command 3­47 rr minor release number, SHOW VERSIONS command 3­75

S Sample procedure, invoking FastLoad on MVS systems 2­10 on VM systems 2­8 SAS/C program examples compiling, linking and executing on MVS systems D­15 on VM systems D­7 INMOD routine C­10, C­11 SCOPE parameter, NOTIFY command 3­47 Secondary indexes not supported by the FastLoad utility 1­2, 1­8,

2­26

Q QUEUE option specification, NOTIFY command 3­47 using with LOW 3­49 QUIT command 1­15, 3­56 using the MultiLoad utility 1­23 Semicolon characters, in FastLoad commands and Teradata SQL statements 3­1 session character set AXSMOD 1­7, 2­22 Sessions control commands, defined 1­15 limits 2­28, 3­63 maximum 2­7 minimum 2­7 number of 3­45 invalid 3­63 reported 3­45, 3­63 optimal number 3­62 SESSIONS command 1­15, 3­61 entering before the LOGON command 3­63

R Range constraints 2­27 examples 2­27 types 2­27 RECORD command 1­16, 3­58 entering before INSERT statements 3­58

Index ­6

Teradata FastLoad Reference

Index

SET RECORD command 1­17, 3­64, 3­65 SET SESSION CHARSET command 1­17, 3­70 SHOW command 1­16, 3­72 SHOW TABLE statement, Teradata SQL. Not supported 3­38 SHOW VERSIONS command 1­16, 3­74 single sign-on 3­44 LOGON syntax 3­43 SLEEP command 1­16, 3­76 configuration file specification 2­21 runtime option specification 2­7 SMALLINT data type specification, DEFINE command 3­13 input length and field description 3­15 NULLIF restrictions and limitations 3­21 software releases, supported i SOLARIS compiling and linking routines D­17 Space requirements and limitations 2­28 SSO LOGON command single sign-on 3­44 Standard error file 2­2 input file 2­2 name specification 2­7 output file 2­2 name specification 2­7 startrecordnumber specification, RECORD command 3­58 Status codes FastLoad-to-INMOD interface 2­32 INMOD-to-FastLoad interface 2­33 string specification, NOTIFY command 3­47 Syntax, how to read A­1 SYSTEM/SYSTEMS specification SCOPE parameter NOTIFY command 3­47

DATABASE 1­17 DELETE 1­17 DROP TABLE 1­17 INSERT 1­17, 3­37 using BTEQ to enter 3­28 terminating return codes 2­12, 3­41, 3­56 TEXT keyword specification 3­65 TEXT string specification, NOTIFY command string specification, NOTIFY command 3­47 THRU endrecordnumber specification, RECORD command 3­59 THRU keyword (endrecordnumber) specification, RECORD command 3­58 tname specification BEGIN LOADING command 3­4 HELP TABLE command 3­35 INSERT statement 3­37

U Unformatted data 1­12 records 3­65 UNFORMATTED keyword specification, SET RECORD command 3­64 Unicode data, using 3­22, 3­39 UNIX compiling and linking routines D­17 Unsupported data sets and tapes 1­14 username specification, LOGON command 3­43 Utility variables, order of preference 2­20

V value, NULLIF specification, DEFINE command 3­13 VALUES keyword, INSERT statement 3­37 VARBYTE data type specification, DEFINE command 3­13 input length and field description 3­15 NULLIF restrictions and limitations 3­21 VARCHAR data type specification, DEFINE command 3­13 input length and field description 3­15 NULLIF restrictions and limitations 3­21 VARGRAPHIC data type specification, DEFINE command 3­13 input length and field description 3­16 NULLIF restrictions and limitations 3­20

T Table definitions, using to define data 3­21 tdpid specification, LOGON command 3­43 technical support iii TENACITY command 1­16, 3­78 configuration file specification 2­21 runtime option specification 2­8 Teradata SQL statements CREATE TABLE 1­17

Teradata FastLoad Reference

Index ­7

Index

Variable-length fields, using 3­67 text 1­13 VARTEXT keyword specification, SET RECORD command 3­65 VARTEXT records 3­66 VERSIONS keyword, SHOW VERSIONS command 3­74 VM program examples for creating and using INMOD routines in Assembler D­2 in COBOL D­3 in PL/I D­5 in SAS/C D­7 for invoking FastLoad 2­8 INMOD routines using Assembler C­2 using COBOL C­6 using PL/I C­9 using SAS/C C­10, C­11

X xx maintenance release number, SHOW VERSIONS command 3­75

Z Zoned decimal Decimal, zoned 2­25 Format, supported 1­8

Index ­8

Teradata FastLoad Reference

Information

Teradata FastLoad Reference

250 pages

Find more like this

Report File (DMCA)

Our content is added by our users. We aim to remove reported files within 1 working day. Please use this link to notify us:

Report this file as copyright or inappropriate

372718