Docstoc

Teradata BTEQ

Document Sample
Teradata BTEQ Powered By Docstoc
					Teradata Utilities: BTEQ

Reprinted for KV Satish Kumar, IBM
kvskumar@in.ibm.com

Reprinted with permission as a subscription benefit of Books24x7,
http://www.books24x7.com/
                                                                                                                                                   i


                                               Table of Contents
Chapter 2: BTEQ..............................................................................................................................1
      Why it is Called BTEQ?.........................................................................................................1
      Logging onto BTEQ...............................................................................................................1
      Using BTEQ to Submit Queries.............................................................................................2
          Submitting SQL in BTEQ's Interactive Mode...................................................................2
                                .
      WITH BY Statement..............................................................................................................3
      Transactions in Teradata Mode.............................................................................................5
      Alternative Transactions in Teradata Mode...........................................................................7
      Transactions in ANSI Mode...................................................................................................8
      Rollback.................................................................................................................................9
      Advantages to ANSI Mode             ...................................................................................................10
      Creating a Batch Script for BTEQ........................................................................................11
      Running your Batch Script in BTEQ                 .....................................................................................12
                                                      .
      Results from a BTEQ Batch Script......................................................................................13
      Placing our BTEQ Output to a file........................................................................................14
      Reading out BTEQ output from the Text File.......................................................................14
      Using BTEQ Conditional Logic............................................................................................15
      Using BTEQ to Export Data.................................................................................................16
      Executing our BTEQ Script to Export Data..........................................................................16
      BTEQ Export Modes Explained...........................................................................................17
      BTEQ EXPORT Example Using Record (DATA) Mode                                    .......................................................18
      BTEQ EXPORT Example Using Field (Report) Mode.........................................................19
      BTEQ IMPORT Example.....................................................................................................21
      Determining Out Record Lengths........................................................................................23
      BTEQ Commands................................................................................................................25
          Session Control Commands..........................................................................................25
          File Control Commands.................................................................................................26
      Sequence Control Commands.............................................................................................26
          Format Control Commands                ............................................................................................26
 Chapter 2: BTEQ
         "Civilization advances by extending the number of important operations which we
         can perform without thinking about them."
         - Alfred Whitehead


Why it is Called BTEQ?
Why is BTEQ available on every Teradata system ever built? Because the Batch TEradata Query
(BTEQ) tool was the original way that SQL was submitted to Teradata as a means of getting an
answer set in a desired format. This is the utility that I used for training at Wal-Mart, AT&T, Anthem
Blue Cross and Blue Shield, and SouthWestern Bell back in the early 1990's. BTEQ is often referred
to as the Basic TEradata Query and is still used today as an effective tool.

Here is what is excellent about BTEQ:

       • BTEQ can be used to submit SQL in either a batch or interactive environment. Interactive
         users can submit SQL and receive an answer set on the screen. Users can also submit
         BTEQ jobs from batch scripts, have error checking and conditional logic, and allow for the
         work to be done in the background.


       • BTEQ outputs a report format, where Queryman outputs data in a format more like a
         spreadsheet. This allows BTEQ a great deal of flexibility in formatting data, creating
         headings, and utilizing Teradata extensions, such as WITH and WITH BY that Queryman
         has problems in handling.


       • BTEQ is often used to submit SQL, but is also an excellent tool for importing and exporting
         data.
               ♦ Importing Data: Data can be read from a file on either a mainframe or LAN attached
                 computer and used for substitution directly into any Teradata SQL using the INSERT,
                 UPDATE or DELETE statements.


               ♦ Exporting Data: Data can be written to either a mainframe or LAN attached computer
                 using a SELECT from Teradata. You can also pick the format you desire ranging
                 from data files to printed reports to spread sheet formats.




There are other utilities that are faster than BTEQ for importing or exporting data. We will talk about
these in future chapters, but BTEQ is still used for smaller jobs.



 Logging onto BTEQ

         "It's choice – not change – that determines your destiny."
         – Jean Nidetch

By taking a chance in this industry, you've chosen to arm yourself with an unlimited arsenal of
knowledge. But you can't use that knowledge if you can't log onto the system! This next slide is
going to teach you how to logon to BTEQ. Remember that you will be prompted for the password
since it's an interactive interface. BTEQ commands begin with a period (.) and do not require a
semi-colon (;) to end the statement. SQL commands do not ever start with a period and they must
always be terminated with a semi-colon.
Reprinted for ibmkvskumar@in.ibm.com, IBM      Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                              2

Let's logon to BTEQ and show all information in the Employee_Table:




Before you can use BTEQ, you must have user access rights to the client system and privileges to
the Teradata DBS. Normal system access privileges include a user ID and a password. Some
systems may also require additional user identification codes depending on company standards and
operational procedures. Depending on the configuration of your Teradata DBS, you may need to
include an account identifier (acctid) and/or a Teradata Director Program Identifier (TDPID).



 Using BTEQ to Submit Queries
Submitting SQL in BTEQ's Interactive Mode
Once you logon to Teradata through BTEQ, you are ready to run your queries. Teradata knows the
SQL is finished when it finds a semi-colon, so don't forget to put one at the end of your query. Below
is an example of a Teradata table to demonstrate BTEQ operations.

         Employee_Table
         Employee_No Last_Name                   First_Name           Salary Dept_No
           2000000    Jones                      Squiggy             32800.50   ?
           1256349    Harrison                   Herbert             54500.00  400
           1333454    Smith                      John                48000.00  200
           1121334    Strickling                 Cletus              54500.00  400



Figure 2-1




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                              3

         BTEQ execution
                                                                                                 Type at command
          .LOGON cdw/sql01                                                                       prompt: Logon
          Password: XXXXX                                                                        with TDPID and
                                                                                                 USERNAME.
                                                                                                 Then enter
                                                                                                 PASSWORD at
                                                                                                 the second
                                                                                                 prompt.
                                                                                                 BTEQ will
          Enter your BTEQ/SQL Request or BTEQ Command.                                           respond and is
                                                                                                 waiting for a
                                                                                                 command.
                                                                                                 An SQL
          SELECT * FROM Employee_Table                                                           Statement
          WHERE Dept_No = 400;
                                                                                                 BTEQ displays
          *** Query Completed. 2 rows found. 5 Columns returned.                                 information about
          *** Total elapsed time was 1 second.                                                   the answer set.
                                                                                                 The result set
          Employee_No Last_name                 First_name             Salary          Dept_No
          1256349     Harrison                  Herbert              54500.00              400
          1121334     Strickling                Cletus               54500.00              400




Figure 2-2


 WITH BY Statement

         "Time is the best teacher, but unfortunately, it kills all of its students."
         – Robin Williams

Investing time in Teradata can be a killer move for your career. We can use the WITH BY statement
in BTEQ, whereas we cannot use it with Nexus or SQL Assistant. The WITH BY statement works
like a correlated subquery in the fact that you can us aggregates based on a distinct column value.

BTEQ has the ability to use WITH BY statements:




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                              4




         "I've learned that you can't have everything and do everything at the same time."
         – Oprah Winfrey

The great thing about the WITH statement is that you can do everything to a specific group while
having everything done to a column as a whole. We can get a grand total or an overall average with
the WITH statement, just leave out BY. Here's a good example:

Using WITH on a whole column:




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                              5




 Transactions in Teradata Mode

         "He who every morning plans the transaction of the day and follows out that plan,
         carries a thread that will guide him through the maze of most busy life."
         - Victor Hugo

Victor couldn't have summed up Teradata any better. However, Victor did seem more worried about
the hunchback than the rollback. Turning your queries into a single transaction is often the best
plan, but can sometimes make one Miserables.

Often in Teradata we'll see multiple queries within the same transaction. We can use the BT/ET
keywords to bundle several queries into one transaction. You also need to end every query with a
semi-colon (;), which isn't the case in Nexus or SQL assistant. For example:

In Teradata mode, we're going to put four single statements into a single transaction




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                              6




         "What is defeat? Nothing but education; nothing but the first step to something
         better."
         – Wendell Phillips

The final query in our last transaction is what caused our updates to fail. This was not the sweet
taste of victory, but instead the smell of de Feet! Actually, it really was an education leading to
something better. When using BT/ET in your transaction, you're telling Teradata that when it comes
to committing, we either want all or none. Since our last query in the transaction failed the Transient
Journal rolled back all the queries in our entire transaction. Make sure that your syntax is correct
when using the method of BT and ET because a mistake causes a massive rollback.

The last query in our set did not work:




Now let's take a look at the Employee_Table:




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                              7




Our updates didn't work! That's because we bundled all four queries into one transaction. Since our
last query failed, the tables were rolled back to their original state before the transaction took place.




 Alternative Transactions in Teradata Mode

         "It's not enough that we do our best; sometimes we have to do what's required."
         – Sir Winston Churchill

Sometimes we're required to use an alternative method to get the job done if we want to win like
Winston. Here's another way to set up a bundle of queries into one transaction. Notice where we
place the semi-colon in our queries and you will understand this technique. Remember that the
semi-colon must be at the very beginning of the next line for a query to be considered as part of the
same transaction. Because we are in Teradata mode if any query fails then all queries that are part
of the same transaction roll back. How many queries are parts of the same transaction below? Four!

Another way to perform a multi-statement transaction in Teradata mode:




Placing the semi-colon at the beginning of the next line (followed by another statement) will bundle
those statements together as one transaction. Notice that our Employee_Table was not updated,
Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                              8

just like in the first example.




 Transactions in ANSI Mode

         "The man who views the world at 50 the same as he did at 20 has wasted 30 years
         of his life."
         – Muhammad Ali

ANSI (American National Standard Institution) allows us to view the same queries in a different way.
To change to ANSI mode, simply type '.set session transaction ANSI' and be sure to do it before
you actually logon to BTEQ. Then, you can logon like always, but this time you will be in ANSI
mode. All queries in ANSI mode will also work in Teradata mode and vice versa. However, three
things will be different in ANSI mode versus Teradata mode. Those things are how case sensitivity
is handled, how transactions are committed and rolled back, and how truncation is accepted.

Let's log back onto BTEQ, but this time change it to ANSI mode:




         "Be not afraid of growing slowly, be afraid only of standing still."
         -Chinese Proverb

Remember the first rule of ANSI mode: all transactions must be committed by the user actually
using the word 'COMMIT'. Also, in ANSI mode after any DDL statement (CREATE, DROP, ALTER,
DATABASE) we have to use the 'commit' command immediately. This tells Teradata to commit to
what's been done. Our query below will attempt to find anyone with a last_name of 'larkins'. It will
fail even though we have 'Mike' 'Larkins' in our table. This is because ANSI is case sensitive and we
did not capitalize the 'L' in 'Larkins'.

Let's run a few queries in ANSI mode:
Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                              9




Notice that we have to COMMIT after any DDL or Update before the transaction is committed. We
even have to COMMIT after setting our DATABASE or we will get an error.

We didn't have any rows return, but we know there's a Mike Larkins within the table. That's because
BTEQ is case sensitive. Change 'larkins' to 'Larkins'.




 Rollback

         "Insanity: doing the same thing over and over again and expecting different results."
         – Albert Einstein

The Rollback keyword is the SQL mulligan of Teradata. Rollback will erase any changes made to a
table. This can be very useful if something didn't work. However, you cannot rollback once you've
used the commit keyword. Not keeping rollback in your arsenal would be insane.




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             10




 Advantages to ANSI Mode
ANSI mode is great because when you bundle several queries into one transaction and one doesn't
work, the rest won't be rolled back to their original state. Using commit will ensure that your
successes aren't hidden by your failures.

Now notice that I will have multiple statements in the same transaction and that I purposely fail the
last SQL statement:




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             11

Which statements were rolled back?




         "All truths are easy to understand once they are discovered; the point is to discover
         them."
         – Galileo Galilei

Discovering the advantages in using ANSI will only make SQL easier to write. It might take a little bit
more typing, but a little work now can save you lots of time later.

The Employee_Table was updated!




In ANSI mode, only failed transactions are rolled back when it comes to multi-statement
transactions.




 Creating a Batch Script for BTEQ

         "The cure for boredom is curiosity. There is no cure for curiosity."
         – Dorothy Parker

If you've been bored waiting for your queries to finish, then I'm sure you're curious as to how we can
fix the situation. Batch scripting allows us to write out pages and pages of queries and execute
those queries in one single swoop. BTEQ can also run in batch mode under UNIX (IBM AIX,
Hewlett-Packard HP-UX, NCR MP-RAS, Sun Solaris), DOS, Macintosh, Microsoft Windows and
OS/2 operating systems. To submit a job in batch mode, do the following:


Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             12

      1. Invoke BTEQ (using dos prompt)


      2. Type in the input file name


      3. Type in the location and output file name.



The following example shows how to create a batch script and how to invoke the script using BTEQ
from a DOS command. When using Batch scripting, your password will not be prompted. Instead,
just add the password after your login name, and a comma separates the two. Be sure to end with
either a .quit or .logoff so that your queries aren't left hanging.

Simply open up notepad and type in the following, then save it. I recommend calling it
'BTEQ_First_Batch_Script.txt' and save it in the C:\Temp folder. However, as long as you can
remember what you named it and where you saved it, you'll be fine. Be sure that you save it as a
.txt file.

Using Batch scripting with BTEQ




 Running your Batch Script in BTEQ

         "I do not fear computers. I fear the lack of them."
         – Isaac Asimov

The BTEQ utility enables us to run our scripts in batch mode. To run our new batch script, we have
to access the BTEQ utility via dos prompt. Simply use command prompt to access the utility, and
follow the steps below:

Let's run our query in Batch!




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             13




Once you're in DOS, type in the following: 'BTEQ < c:\temp\BTEQ_First_Script.txt', then hit enter.
BTEQ will automatically open in DOS, and then it will access the file from the location you listed.



 Results from a BTEQ Batch Script

         "Don't be afraid to take a big step when one is indicated. You can't cross a chasm in
         two small steps."
         -David Lloyd George

BTEQ will run your query in steps to produce the answer you're looking for. Whether you're
accessing a small table or crossing over a chasm of information, BTEQ will ensure that the steps it
takes will be big enough to get the job done.

Our results are returned Interactively




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             14




 Placing our BTEQ Output to a file

         "The secret to creativity is knowing how to hide your sources."
         – Albert Einstein

We can use BTEQ to export our results to another text document. Exporting data also works very
well when you're trying to document your query along with the results.

We can export our results in batch as well




Notice that the BTEQ command is immediately followed by the '<BTEQ_First_Script.txt' to tell BTEQ
which file contains the commands to execute. Then, the '>BTEQ_First_Export.txt' names the file
where the output messages are written.

Since putting password information into a script is scary for security reasons, inserting the password
directly into a script that is to be processed in batch mode may not be a good idea. It is generally
recommended and a common practice to store the logon and password in a separate file that that
can be secured. That way, it is not in the script for anyone to see. For example, the contents of a file
called "mylogon.txt" might be: '.LOGON cdw/sql00,whynot'. Then, the script should contain the
following command instead of a .LOGON: .RUN FILE=c:\temp\mylogon.txt. To submit results to
two different files at once, simply initialize BTEQ as follows:
BTEQ < c:\filename > c:\location1 > c:\location2




 Reading out BTEQ output from the Text File

         "The more original a discovery, the more obvious it seems afterwards."
         – Arthur Koestler

Discovering how easy it is to export your data in batch mode is a key step in learning Teradata
utilities. Here are our results, including the original query and what BTEQ did to generate its answer
set. Simply go to the folder where you saved the exported data (the previous examples saved the
file as c:\temp\BTEQ_First_Export.txt).

Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             15

What you'll find in our new text document




 Using BTEQ Conditional Logic

         "I was walking down the street wearing glasses when the prescription ran out."
         - Steven Wright

Below is a BTEQ batch script example. The initial steps of the script will establish the logon, the
database, and then delete all the rows from the Employee_Table. If the table does not exist, the
BTEQ conditional logic will instruct Teradata to create it. However, if the table already exists, then
Teradata will move forward and insert data.

Note: In script examples, the left panel contains BTEQ base commands and the right panel
provides a brief description of each command.

                                                                                                                 BTEQ conditional
.RUN FILE = c:\temp\mylogon.txt                                                                                  logic that will
DATABASE SQL_Class;                                                                                              check to ensure
DELETE FROM Employee_Table;
                                                                                                                 that the delete
.IF ERRORCODE = 0 THEN .GOTO INSEMPS[*]                                                                          worked or if the
/* ERRORCODE is a reserved word that contains the outcome                                                        table even
status for every SQL statement executed in BTEQ. A zero (0)                                                      existed. If the
indicates that statement worked.                          */                                                     table did not
                                                                                                                 exist, then BTEQ
                                                                                                                 will create it. If
                                                                                                                 the table does
                                                                                                                 exist, the Create
                                                                                                                 table step will be
                                                                                                                 skipped and
                                                                                                                 directly GOTO
                                                                                                                 INSEMPS.
                                                                                                                 The Label
CREATE TABLE Employee_Table                                                                                      INSEMPS
  (Employee_No INTEGER,                                                                                          provides code so
Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             16

  Last_name     CHAR(20),                                                   the BTEQ Logic
  First_name    CHAR(12),                                                   can go directly to
  Salary    DECIMAL(8,2),
  Dept_No       SMALLINT)                                                   inserting records
UNIQUE PRIMARY INDEX (Employee_No);                                         into the
                                                                            Employee_Table.
                                                                            Once the table
.LABEL INSEMPS[*]                                                           has been created,
INSERT INTO Employee_Table (1232578, 'Chambers', 'Mandee', 48850.00,        Teradata will then
100);
INSERT INTO Employee_Table (1256349, 'Harrison', 'Herbert', 54500.00, 400); insert the two
.QUIT                                                                       new rows into the
                                                                            empty table.
[*]Both labels have to be identical or it will not work.




 Using BTEQ to Export Data

         "The trouble with facts is that there are so many of them."
         – Samuel McChord Crothers

Creating flat files is one of the most important tasks in Teradata, and that's a fact. BTEQ allows data
to be exported directly from Teradata to a file on a mainframe or network-attached computer. In
addition, the BTEQ export function has several export formats that a user can choose from
depending on the desired output. Generally, users will export data to a flat file format that is
composed of a variety of characteristics. These characteristics include: field mode, indicator mode,
or dif mode.

Syntax of a basic EXPORT command:
EXPORT <Mode (example: data)> FILE = <filename>

Creating a flat file of what's on the Employee_Table




 Executing our BTEQ Script to Export Data

         "The past is a foreign country; they do things differently there."
         L. P. Hartley

Transferring data from table to another without the use of a flat file is a thing of the past. Teradata
does things differently now, which is why it's still considered the future of data warehousing. The flat
files we create are merely used to store information contained within a table. The information on
Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             17

these files is written in binary code, which is why the text seems garbled. It may look garbled, but it
is perfectly written. When we Fastload the data back to a table it will look beautiful.

Executing our fastload_creating_flatfile01.txt




What our flat file looks like:




And I thought French was tough; it's like they have a different word for everything…




We now have a flat file that contains all information found in the Employee_Table. We will be able to
use this flat file for future exercises.



 BTEQ Export Modes Explained
Below is a list and description of our three data modes:

Record Mode: (also called DATA mode): This is set by .EXPORT DATA. This will bring data back
as a flat file. Each parcel will contain a complete record. Since it is not a report, there are no
headers or white space between the data contained in each column and the data is written to the
file (e.g., disk drive file) in native format. For example, this means that INTEGER data is written as a
4-byte binary field. Therefore, it cannot be read and understood using a normal text editor.

Field Mode (also called REPORT mode): This is set by .EXPORT REPORT. This is the default
mode for BTEQ and brings the data back as if it was a standard SQL SELECT statement. The
output of this BTEQ export would return the column headers for the fields, white space, expanded
packed or binary data (for humans to read) and can be understood using a text editor.

Indicator Mode: This is set by .EXPORT INDICDATA. This mode writes the data in data mode, but
also provides host operating systems with the means of recognizing missing or unknown data
(NULL) fields. This is important if the data is to be loaded into another Relational Database System
(RDBMS).

The issue is that there is no standard character defined to represent either a numeric or character
NULL. So, every system uses a zero for a numeric NULL and a space or blank for a character
NULL. If this data is simply loaded into another RDBMS, it is no longer a NULL, but a zero or space.

To remedy this situation, INDICATA puts a bitmap at the front of every record written to the disk.
This bitmap contains one bit per field/column. When a Teradata column contains a NULL, the bit for
that field is turned on by setting it to a "1". Likewise, if the data is not NULL, the bit remains a zero.

Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             18

Therefore, the loading utility reads these bits as indicators of NULL data and identifies the column(s)
as NULL when data is loaded back into the table, where appropriate.

Since both DATA and INDICDATA store each column on disk in native format with known lengths
and characteristics, they are the fastest method of transferring data. However, it becomes
imperative that you be consistent. When it is exported as DATA, it must be imported as DATA and
the same is true for INDICDATA.

Again, this internal processing is automatic and potentially important. Yet, on a network-attached
system, being consistent is our only responsibility. However, on a mainframe system, you must
account for these bits when defining the LRECL in the Job Control Language (JCL). Otherwise, your
length is too short and the job will end with an error.

To determine the correct length, the following information is important. As mentioned earlier, one bit
is needed per field output onto disk. However, computers allocate data in bytes, not bits. Therefore,
if one bit is needed a minimum of eight (8 bits per byte) are allocated. Therefore, for every eight
fields, the LRECL becomes 1 byte longer and must be added. In other words, for nine columns
selected, 2 bytes are added even though only nine bits are needed.

With this being stated, there is one indicator bit per field selected. INDICDATA mode gives the Host
computer the ability to allocate bits in the form of a byte. Therefore, if one bit is required by the host
system, INDICDATA mode will automatically allocate eight of them. This means that from one to
eight columns being referenced in the SELECT will add one byte to the length of the record. When
selecting nine to sixteen columns, the output record will be two bytes longer.

When executing on non-mainframe systems, the record length is automatically maintained.
However, when exporting to a mainframe, the JCL (LRECL) must account for this additional 2 bytes
in the length.

DIF Mode: Known as Data Interchange Format, which allows users to export data from Teradata to
be directly utilized for spreadsheet applications like Excel, FoxPro and Lotus.

The optional LIMIT is to tell BTEQ to stop returning rows after a specific number (n) of rows. This
might be handy in a test environment to stop BTEQ before the end of transferring rows to the file.



 BTEQ EXPORT Example Using Record (DATA) Mode
The following is an example that displays how to utilize the export Record (DATA) option. Notice the
periods (.) at the beginning some of script lines. A period starting a line indicates a BTEQ command.
If there is no period, then the command is an SQL command.

When doing an export on a mainframe or a network-attached (e.g., LAN) computer, there is one
primary difference in the .EXPORT command. The difference is the following:


        • Mainframe .EXPORT DATA DDNAME = data definition statement name (JCL)
          syntax:



        • LAN             .EXPORT DATA FILE = actual file name
          syntax:



The following example uses a Record (DATA) Mode format. The output of the exported data will be
a flat file.



Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             19

         Employee_Table
         Employee_No Last_Name                   First_Name         Salary       Dept_No
         2000000       Jones                     Squiggy            32800.50     ?
         1256349       Harrison                  Herbert            54500.00     400
         1333454       Smith                     John               48000.00     200
         1121334       Strickling                Cletus             54500.00     400
                                                                                 Logon to TERADATA
          .LOGON CDW/sql01,whynot;
                                                                                 This Export statement will
          .EXPORT DATA FILE = C:\EMPS.TXT                                        be in record (DATA) mode.
                                                                                 The EMPS.TXT file will be
          SELECT * FROM SQL_Class.Employee_Table;                                created as a flat file
                                                                                 Finish the execution.
          .QUIT




Figure 2-6


 BTEQ EXPORT Example Using Field (Report) Mode
The following is an example that displays how to utilize the export Field (Report) option. Notice the
periods (.) at the beginning some of script lines. A period starting a line indicates a BTEQ command
and needs no semi-colon. Likewise, if there is no period, then the command is an SQL command
and requires a semi-colon.

                                                   Logon to TERADATA
.LOGON CDW/sql01,whynot;
                                                   This Export statement will be in field (REPORT) mode.
DATABASE SQL_Class;                                The EMPS.TXT file will be created as a report.
.EXPORT REPORT FILE = C:\EMPS.TXT
SELECT * FROM Employee_Table;
                                                   BTEQ checks to ensure no errors occurred and selects
.IF ERRORCODE > 0 THEN .GOTO Done more rows – else GOTO Done.
SELECT * FROM Department_Table;
                                                   Reverse previous export command and fall through to
.EXPORT RESET                                      Done.
.LABEL Done
.QUIT


Figure 2-7

After this script has completed, the following report will be generated on disk.

Employee_No         Last_name      First_name      Salary   Dept_No
2000000             Jones          Squiggy         32800.50       ?
1256349             Harrison       Herbert         54500.00     400
1333454             Smith          John            48000.00     200
1121334             Strickling     Cletus          54500.00     400
1324657             Coffing        Billy           41888.88     200
2341218             Reilly         William         36000.00     400
1232578             Chambers       Mandee          56177.50     100

Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             20

1000234             Smythe         Richard         64300.00              10
2312225             Larkins        Loraine         40200.00             300

I remember when my mom and dad purchased my first Lego set. I was so excited about building my
first space station that I ripped the box open, and proceeded to follow the instructions to complete
the station. However, when I was done, I was not satisfied with the design and decided to make
changes. So I built another space ship and constructed another launching station. BTEQ export
works in the same manner. As a user gains experience with BTEQ export, the easier it will get to
work with the utility.

With that being said, the following is an example that displays a more robust example of utilizing the
Field (Report) option. This example will export data in Field (Report) Mode format. The output of
the exported data will appear like a standard output of a SQL SELECT statement. In addition,
aliases and a title have been added to the script.

                                                              Logon to TERADATA
.LOGON CDW/sql01,whynot;
                                                              Set the format parameters for the final report
.SET WIDTH 90
.SET FORMAT ON
.SET HEADING 'Employee Profiles'
                                         This Export statement will be in field (REPORT)
.EXPORT REPORT FILE = C:\EMP_REPORT.TXT  mode. The EMP_REPORT.TXT file will be
                                         created as a report.
                                         Specifies the columns that are being selected.
SELECT Employee_No AS "Employee Number", Notice that the columns have an alias.
          Last_name   AS "Last Name",
          First_name AS "First Name",
          Salary      AS "Salary",
          Dept_No     AS "Department Number"
FROM      Employee_Table;
                                                              Reverse previous export command effects
.EXPORT RESET
.QUIT


Figure 2-8

After then following script has been completed, the following report will be generated on disk.

                                  Employee Profiles
Employee Number             Last Name First Name                       Salary      Department
                                                                                      Number
                2000000 Jones                 Squiggy              32800.50                 ?
                1256349 Harrison              Herbert              54500.00               400
                1333454 Smith                 John                 48000.00               200
                1121334 Strickling            Cletus               54500.00               400
                1324657 Coffing               Billy                41888.88               200
                2341218 Reilly                William              36000.00               400
                1232578 Chambers              Mandee               56177.50               100
                1000234 Smythe                Richard              64300.00                10
                2312225 Larkins               Loraine              40200.00               300

From the above example, a number of BTEQ commands were added to the export script. Below is a
review of those commands.




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             21

       • The WIDTH specifies the width of screen displays and printed reports, based on characters
         per line.


       • The FORMAT command allows the ability to enable/inhibit the page-oriented format option.


       • The HEADING command specifies a header that will appear at the top every page of a
         report.




 BTEQ IMPORT Example
BTEQ can also read a file from the hard disk and incorporate the data into SQL to modify the
contents of one or more tables. In order to do this processing, the name and record description of
the file must be known ahead of time. These will be defined within the script file.

Format of the IMPORT command:
.IMPORT <mode> { FILE | DNAME } = <filename> [,SKIP=n]

The script below introduces the IMPORT command with the Record (DATA) option. Notice the
periods (.) at the beginning some of script lines. A period starting a line indicates a BTEQ command.
If there is no period, then the command is an SQL command.

The SKIP option is used when you wish to bypass the first records in a file. For example, a
mainframe tape may have header records that should not be processed. Other times, maybe the job
started and loaded a few rows into the table with a UPI defined. Loading them again will cause an
error. So, you can skip over them using this option.

The following example will use a Record (DATA) Mode format. The input of the imported data will
populate the Employee_Table.

                                                               Specify the number of SESSIONS to establish
.SESSIONS 4                                                    with Teradata
.LOGON CDW/sql01,whynot;
                                                               Logon to TERADATA
.IMPORT DATA FILE = C:\EMPS.TXT, SKIP = 2
                                                               Specify DATA mode, name the file to read
.QUIET ON                                                      "EMPS.TXT", but skip the first 2 records.
.REPEAT *
                                                               Limit messages out. Loop in this script until
USING           Emp_No           (INTEGER),                    end of records in file.
                L_name           (CHAR(20)),
                F_name           (VARCHAR(12)),
                Salary           (DECIMAL(8,2)),
                Dept_No          (SMALLINT)
                                                               The USING Specifies the field in the input file
INSERT INTO Employee_Table                                     and names them.
            (Employee_No,
            Last_name,
            First_name,
            Salary,
            Dept_No)
                                                               Specify the insert parameters for the
VALUES            (:Emp_No,                                    employee_table
                   :L_name,
                   :F_name,                                    Substitutes data from the fields into the SQL
                   :Salary,                                    command.
                   :Dept_No) ;



Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             22

.QUIT                                                          Exit the script after all data read and rows
                                                               inserted.


Figure 2-9

From the above example, a number of BTEQ commands were added to the import script. The next
page contains a review of those commands.



       • .QUIET ON limits BTEQ output to reporting only errors and request processing statistics.
         Note: Be careful how you spell .QUIET, else forgetting the E becomes .QUIT and it will.


       • .REPEAT * causes BTEQ to read a specified number of records or until EOF. The default is
         one record. Using REPEAT 10 would perform the loop 10 times.


       • The USING defines the input data fields and their associated data types coming from the
         host.



The following builds upon the IMPORT Record (DATA) example above. The example below will still
utilize the Record (DATA) Mode format. However, this script adds a CREATE TABLE statement. In
addition, the imported data will populate the newly created Employee_Profile table.

                                                             Specify the number of SESSIONS to establish
.SESSIONS 2                                                  with Teradata
                                                             Logon to TERADATA
.LOGON CDW/sql01,whynot;
                                                             Make the default database SQL_Class
DATABASE SQL_Class;
                                                             This statement will create the Employee_Profile
CREATE TABLE Employee_Profile            table.
  (
  Employee_No INTEGER,
  Last_name    CHAR(20),
  First_name   VARCHAR(12),
  Salary         DECIMAL(8,2),
  Dept_No        SMALLINT
  )
UNIQUE PRIMARY INDEX (Employee_No) ;
.IMPORT INDICDATA FILE = C:\IND-EMPS.TXT
                                                             This import statement specifies INDICDATA
.QUIET ON                                                    mode. The input file is from a LAN file called
                                                             IND-EMPS.TXT.
                                                             Quiet on limits the output to reporting only
.REPEAT 120                                                  errors and processing statistics.
                                                             This causes BTEQ to read the first 120 records
USING             Employee_No        (INTEGER),              from the file.
                  Last_name          (CHAR(20)),
                  First_name         (VARCHAR(12)),
                  Salary             (DECIMAL(8,2)),
                  Dept_No            (SMALLINT)
                                                             The USING Specifies the parameters of the input
INSERT INTO Employee_Profile                                 file.
            (Employee_No,
            Last_name,
            First_name,
            Salary,
            Dept_No)


Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             23

VALUES            (:Employee_No,                             Specify the insert parameters for the
                   :Last_name,                               employee_profile.
                   :First_name,
                   :Salary,
                   :Dept_No) ;
                                                             Substitute the values to be inputted into the SQL
.LOGOFF                                                      command.
.QUIT


Figure 2-10
Notice that some of the scripts have a .LOGOFF and .QUIT. The .LOGOFF is optional because
when BTEQ quits, the session is terminated. A logoff makes it a friendly departure and also allows
you to logon with a different user name and password.



 Determining Out Record Lengths
Some hosts, such as IBM mainframes, require the correct LRECL (Logical Record Length)
parameter in the JCL, and will abort if the value is incorrect. The following page will discuss how to
figure out the record lengths.

There are three issues involving record lengths and they are:

       • Fixed columns


       • Variable columns


       • NULL indicators



Fixed Length Columns: For fixed length columns you merely count the length of the column. The
lengths are:

                 4 bytes
        • INTEGER


                 2 bytes
        • SMALLINT


                       1 byte
        • BYTEINT


                 10 bytes
        • CHAR(10)


                       4 bytes
        • CHAR(4)


                       4 bytes
        • DATE

Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             24



                 4 bytes (packed data, total digits / 2 +1)
        • DECIMAL(7,2)


                 7 bytes
        • DECIMAL(12,2)



Variable columns: Variable length columns should be calculated as the maximum value plus two.
The two bytes are for the number of bytes for the binary length of the field. In reality you can save
much space because trailing blanks are not kept. The logical record will assume the maximum and
add two bytes as a length field per column.

                10 bytes
        • VARCHAR(8)


                12 bytes
        • VARCHAR(10)



Indicator columns: As explained earlier, the indicators utilize a single bit for each field. If your record
has 8 fields (which require 8 bits), then you add one extra byte to the total length of all the fields. If
your record has 9-16 fields, then add two bytes.

BTEQ Return Codes

Return codes are two-digit values that BTEQ returns to the user after completing each job or task.
The value of the return code indicates the completion status of the job or task as follows:

Return Code Descirption


       • 00 Job completed with no errors.


       • 02 User alert to log on to the Teradata DBS.


       • 04 Warning error.


       • 08 User error.


       • 12 Severe internal error.



You can over-ride the standard error codes at the time you terminate BTEQ. This might be handy
for debugging purposes. The error code or "return code" can be any number you specify using one
of the following:

Override Code Description

       • .QUIT 15


Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             25

       • .EXIT 15




 BTEQ Commands
The BTEQ commands in Teradata are designed for flexibility. These commands are not used
directly on the data inside the tables. However, these 60 different BTEQ commands are utilized in
four areas.

       • Session Control Commands


       • File Control Commands


       • Sequence Control Commands


       • Format Control Commands



Session Control Commands
ABORT          Abort any and all active running requests and transactions for a session, but
               do not exit BTEQ.
DEFAULTS       Reset all BTEQ Format command options to their defaults. This will utilize
               the default configurations.
EXIT           Immediately end the current session or sessions and exit BTEQ.
HALT EXECUTION Abort any and all active running requests and transactions and EXIT BTEQ.
LOGOFF         End the current session or sessions, but do not exit BTEQ.
LOGON          Starts a BTEQ Session. Every user, application, or utility must LOGON to
               Teradata to establish a session.
QUIT           End the current session or sessions and exit BTEQ.
SECURITY       Specifies the security level of messages between a network-attached
               system and the Teradata Database.
SESSIONS       Specifies the number of sessions to use with the next LOGON command.
SESSION        Specifies the name of a character set for the current session or sessions.
CHARSET
SESSION        Specifies a disposition of warnings issued in response to violations of ANSI
SQLFLAG        syntax. The SQL will still run, but a warning message will be provided. The
               four settings are FULL, INTERMEDIATE, ENTRY, and NONE.
SESSION        Specifies whether transaction boundaries are determined by Teradata SQL
TRANSACTION    or ANSI SQL semantics.
SHOW           Displays all of the BTEQ control command options currently configured.
CONTROLS
SHOW VERSIONS Displays the BTEQ software release versions.
TDP            Used to specify the correct Teradata server for logons for a particular session.


Figure 2-11




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             26

File Control Commands
These BTEQ commands are used to specify the formatting parameters of incoming and outgoing
information. This includes identifying sources and determining I/O streams.

CMS                      Execute a VM CMS command inside the BTEQ environment.
ERROROUT                 Write error messages to a specific output file.
EXPORT                   Open a file with a specific format to transfer information directly from the
                         Teradata database.
HALT                     Abort any and all active running requests and transactions and EXIT BTEQ.
EXECUTION
FORMAT                   Enable/inhibit the page-oriented format command options.
IMPORT                   Open a file with a specific format to import information into Teradata.
INDICDATA                One of multiple data mode options for data selected from Teradata. The modes
                         are INDICDATA, FIELD, or RECORD MODE.
OS                       Execute an MS-DOS, PC-DOS, or UNIX command from inside BTEQ.
QUIET                    Limit BTEQ output displays to all error messages and request processing
                         statistics.
RECORDMODE               One of multiple data mode options for data selected from Teradata.
                         (INDICDATA, FIELD, or RECORD).
REPEAT                   Submit the next request a certain amount of times
RUN                      Execute Teradata SQL requests and BTEQ commands directly from a
                         specified run file.
TSO                      Execute an MVS TSO command from inside the BTEQ environment.


Figure 2-12


 Sequence Control Commands
These commands control the sequence in which Teradata commands operate.

ABORT                Abort any active transactions and requests.
ERRORLEVEL           Assign severity levels to particular error numbers.
EXIT                 End the current session or sessions and exit BTEQ.
GOTO                 Skip all intervening commands and resume after branching forward to the
                     specified label.
HANG                 Pause BTEQ processing for a specific amount of time.
IF…THEN              Test a stated condition, and then resume processing based on the test results.
LABEL                The GOTO command will always GO directly TO a particular line of code based
                     on a label.
MAXERROR             Specifies a maximum allowable error severity level.
QUIT                 End the current session or sessions and exit BTEQ.
REMARK               Place a comment on the standard output stream.
REPEAT               Submit the next request a certain amount of times.


Figure 2-13
Format Control Commands
These commands control the formatting for Teradata and present the data in a report mode to the
screen or printer.


Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited
Teradata Utilities: BTEQ, FastLoad, MultiLoad, TPump, and FastExport, Second Edition                                             27

DEFAULTS     Reset all BTEQ Format command options to their defaults. This will utilize the
             default configurations.
ECHOREQ      Enable the Echo required function in BTEQ returning a copy of each
             Teradata SQL request and BTEQ command to the standard output stream.
EXPORT       Open a file with a specific format to transfer information directly from the
             Teradata database.
FOLDLINE     Split or fold each line of a report into multiple lines.
FOOTING      Specify a footer to appear at the bottom of every report page.
FORMAT       Enable/inhibit the page-oriented format command options.
IMPORT       Open a file with a specific format to transfer or IMPORT information directly
             to Teradata.
NULL         Specifies a character or string of characters to represent null values returned
             from Teradata.
OMIT         Omit specific columns from a report.
PAGEBREAK    Ejects a page whenever a specified column changes values.
PAGELENGTH   Specifies the page length of printed reports based on lines per page.
QUIET        Limit BTEQ output displays to all error messages and request processing
             statistics.
RECORDMODE   One of multiple data mode options for data selected from Teradata.
             (INDICDATA, FIELD, or RECORD).
RETCANCEL    Cancel a request when the specified value of the RETLIMIT command option
             is exceeded.
RETLIMIT     Specifies the maximum number of rows to be displayed or written from a
             Teradata SQL request.
RETRY        Retry requests that fail under specific error conditions.
RTITLE       Specify a header appearing at the top of all pages of a report.
SEPARATOR    Specifies a character string or specific width of blank characters separating
             columns of a report.
SHOWCONTROLS Displays all of the BTEQ control command options currently configured.
SIDETITLES   Place titles to the left or side of the report instead of on top.
SKIPLINE     Inserts blank lines in a report when the value of a column changes specified
             values.
SUPPRESS     Replace each and every consecutively repeated value with completely-blank
             character strings.
TITLEDASHES  Display dash characters before each report line summarized by a WITH
             clause.
UNDERLINE    Display a row of dash characters when the specified column changes
             values.
WIDTH        Specifies the width of screen displays and printed reports, based on characters
             per line.


Figure 2-14




Reprinted for ibmkvskumar@in.ibm.com, IBM                  Coffing Data Warehousing, Coffing Publishing (c) 2005, Copying Prohibited

				
DOCUMENT INFO