Teradata C09-0001-07 User manual
User's Guide .
C09-0001-07
Release 3.0
DBCjl012 Data Base Computer
User's Guide·
C09-0001-07
Release 3.0
Copyright,
1987,
Teradata
Corporation
All
rights
reserved.
No
part
of
this
document
may
be
copied
or
reproduced
in
any
form
or
by
any
means
without
the
prior
written
consent
of
Teradata.
Teradata
Corporation,
12945
Jefferson
Boulevard,
Los
Angeles,
CA
90066.
ii
C09-OOOI-07
EFFECTIVE
PAGES
This
is
the
-07
version
of
the
DBC/1012(R)
Data
Base
Computer
User's
Guide.
Revision
levels
of
individual
pages
are
given
below.
Page
Revision
Title
Page
-07
Effective
Pages
-07
Preface
(v
through
xx)
-07
Contents
-07
1-
1
through
1-
8
-07
2-
1
through
2-
2
-07
3-
1
through
3-30
-07
4-
1
through
4-
8
-07
5-
1
through
5-20
-07
6-
1
through
6-38
-07
7-
1
through
7-20
-07
8-
1
through
8-6
-07
9-
1
through
9-
10
-07
10-1
through
10-12
-07
11-1
through
11-14
-07
Appendix
A-1
-07
Appendix
B-1
through
B-22
-07
Appendix
C-1
through
C-2
-07
Appendix
D-1
through
D-4
-07
Index
X-1
through
X-13
-07
DBC/1012
is
a
registered
trademark
of
Teradata
Corporation.
C09-0001-07
iii
Design
Change
Requests
(DCRs)
Reflected
in
this
Revision:
DCR
3101
DCR
2214
DCR
3167
DCR
2507
DCR
3785
DCR
2775
DCR
3578
DCR
2515
Discrepancy
Reports
(DRs)
Reflected
in
this
Revision:
DR
7729
DR
7789
DR
8202
DR
7811
DR
8202
iv
C09-0001-07
PREFACE
This
preface
describes
the
structure
of
the
DBC/1012
Data
Base
Computer
User's
Guide
and
tells
you
what
we
expect
you
to
know
before
you
begin
to
read
the
guide.
ABOUT
YOU
If
you
are
not
familiar
with
the
DBC/1012
Data
Base
Computer,
you
will
find
it
useful
to
read
DBC/1012
Data
Base Computer
Concepts
and
Facilities
before
using
this
guide.
Except
for
Chapter
4,
which
requires
some
knowledge
of
MVS/TSO
and
VM/CMS,
you
needn't
have
a
background
in
data
processing
to
use
this
guide.
However,
you
will
benefit
from
some
familiarity
with
computers
and
data
base
management
systems.
Because
a
large
portion
of
this
guide
is
devoted
to
communicating
with
the
DBC/1012
Data
Base
Computer from an
interactive
terminal,
you
should
be
familiar
with
the
operation
of
your
3270-type
keyboard
terminal.
Read
the
user's
guide
for
that
terminal
before
you
read
this
guide.
ABOUT
THIS
DOCUMENT
This
document
is
the
DBC/1012
Data
Base
Computer
User's
Guide.
Its
purpose
is
to
describe
how
to
communicate
with
the
DBC/1012
in
order
to
work
with
data
stored
in
the
DBC/1012.
This
document
has
11
chapters:
•
Chapter
1
gives
you an
overview
of
the
relational
data
base
concept
and
describes
the
facilities
for
communicating
with
the
DBC/1012.
•
Chapter
2 shows
you
how
to
get
established
as
a
DBC/1012
user
and
log
on
to
your
organization's
host
computer
in
order
to
communicate
with
the
DBC/1012.
•
Chapter
3 shows you
how
to
enter,
edit,
and
view
the
results
of
DBC/SQL
statements
during
a
session
with
ITEQ,
the
interactive
facility
for
communicating
with
the
DBC/1012.
•
Chapter
4 shows
you
how
to
enter
jobs
that
consist
of
a
number
of
DBC/SQL
statements
using
BTEQ,
the
batch
facility
for
communicating
with
the
DBC/I012.
•
Chapter
5 shows
you
how
to
use
DBC/SQL
features
with
ITEQ
or
BTEQ
commands
to
create
attractive,
informative
-reports.
C09-OOOl-07 v
•
Chapter
6 shows
you
how
to
enter
DBC/SQL
SELECT
statements,
which
are
used
to
query
data
stored
on
the
DBC/1012.
•
Chapter
7 shows you
how
to
create
data
structures
(tables
and
views)
on
the
DBC/1012.
•
Chapter
8 shows
you
how
to
insert,
update,
and
remove
data
from a
table.
•
Chapter
9 shows you
how
to
create
a
macro
(a
sequence
of
DBC/SQL
statements
that
may
be
stored
and
used
repeatedly
to
operate
on DBC/1012
data).
•
Chapter
10 shows
you
how
to
create
data
bases
and
users,
and
give
users
privileges
for
working
with
your
data.
•
Chapter
11 shows you
how
to
obtain
information
about
data
that
is
stored
on
the
DBC/1012.
Chapters
1
through
5
describe
the
facilities
--
ITEQ
and
BTEQ
--
that
enable
you
to
communicate
with
the
DBC/1012
using
DBC/SQL
statements.
Chapters
6
through
11
describe
the
use
of
the
DBC/SQL
statements
themselves.
Appendixes
provide
supplemental
information.
This
guide
contains
four
appendixes:
•
Appendix
A
is
a
fold-out
copy
of
the
Personnel
data
base,
which
is
used
to
illustrate
examples
throughout
this
guide.
•
Appendix
B
provides
a
syntax
summary
of
the
various
language
components
that
are
used
to
communicate
with
the
DBC/I012.
•
Appendix
C
lists
the
functions
that
the
system
automatically
assigns
to
PF
keys
on
your
terminal,
keyboard,
and
that
are
used
regularly
when
communicating
with
the
DBC/1012
through
ITEQ.
•
Appendix
D shows
you
how
to
define
output
files
that
are
used
for
storing
and
printing
results
during
an
interactive
session
with
the
DBC/I012.
This
document
also
contains
an
index.
From
time
to
time,
the
material
in
this
document
is
revised.
To
help
you
keep
track
of
the
various
revisions,
we
will
provide
you
with
the
following
information
for
each
revision:
vi
C09-0001-07
• The
date
of
the
revision
• The
software
release
of
the
revision
• Change
bars
in
the
margin
to
indicate
what
information
has
changed.
ABOUT
DBC/IOI2
DOCUMENTS
The DBC/IOI2
User's
Guide
is
one
of
a
set
of
manuals
that
describe
the
DBC/IOI2
Data
Base
Computer.
The
complete
set
includes:
• DBC/IOI2
Data
Base
Computer
Concepts
and
Facilities
(document
number
C02-0001)
The
concepts
document
is
written
for
senior
executives,
managers,
and
technical
personnel.
The
document
presents
an
overview
of
the
DBC/IOI2
Data
Base
Computer
System,
addressing
such
topics
as
architecture,
user
facilities,
system
facilities,
hardware
and
software
structure,
operating
characteristics,
and
configuration
specifications.
• DBC/IOI2
Data
Base
Computer
User's
Guide
(document
number C09-OOOI)
The
user's
guide
is
written
for
the
non-DP
user.
The
guide
presents
a
basic
introduction
to
ITEQ,
addressing
such
topics
as
ITEQ
sessions,
on-line
edits,
queries,
print
formats,
and
table
creation
and
modification.
It
also
explains
macros,
privileges,
and
the
Data
Dictionary/Directory.
• DBC/IOI2
Data
Base
Primer
(document
number
C09-0002)
The
Primer
is
written
for
new
computer
users.
It
teaches
basic
DBC/IOI2
query
statements,
offering
hands-on
examples
for
users
to
try
at
their
own
pace.
• DBC/IOI2
ITEQ
Keypad
Template
(document
number
Cg9-0002)
The
template,
which
fits
over
the
terminal
keyboard's
PF-key
keypad,
shows
the
assignment
of
PF
keys
to
ITEQ
commands.
C09-OOOI-07
vii
viii
• DBC/IOI2
Data
Base
Computer
Reference
Manual
(document
number
C03-0001)
The
reference
manual
is
written
for
technical
personnel.
The
manual
presents
the
details
of
language
syntax,
DBC/SQL
statements,
ITEQ
commands,
BTEQ
commands, and
the
Data
Dictionary/Directory.
• DBC/IOI2
Data
Base
Computer
Messages
Reference
Manual
(document
number
C03-0002)
The
messages
manual
is
written
for
all
users.
It
lists
and
explains
all
error
messages
and
return
codes
generated
by
the
by
DBC/IOI2
Data
Base
Computer.
• DBC/IOI2
Data
Base
Computer
Reference
Cards
(document
numbers
C04-0001,
C04-0002,
C04-0003)
The
reference
cards
are
written
for
all
users.
There
are
three
cards
to
a
set.
Each
card
is
a
multi-panel,
fan-folded
summary
of
language
notation,
syntax,
and
acceptable
abbreviations.
The
first
card
lists
DBC/SQL
statements
and
Data
Dictionary/Directory
views,
the
second
card
lists
ITEQ
and
BTEQ
commands, and
the
third
card
lists
COBOL
and
PL/I
Preprocessor
statements.
• DBC/IOI2
Data
Base
Computer
Operator's
Guide
(document
number CIS-OOOI)
The
operator's
guide
is
written
for
DBC/IOI2
operators.
The
guide
presents
features
of
the
DBC/lOI2 and
its
console,
as
well
as
their
operating
procedures,
programs,
and
status
indicators.
• DBC/lOI2
Data
Base
Computer
Utilities
Reference
Manual
(document
number Cll-OOOI)
The
utilities
manual
is
written
for
DBC/IOI2
operators
and
technical
personnel.
The
manual
presents
the
utilities
that
are
used
to
load,
dump,
and
restore
data,
initialize
and
configure
a DBC/IOI2
system,
and
perform
system
maintenance.
• DBC/IOI2
Data
Base
Computer
Support
Utilities
Manual
(document
number
B07-0031)
The
support
utilities
manual
describes
the
utilities
used
by
Teradata
support
personnel
to
format
disks,
add
and
delete
AMPs,
copy
data
from
one
AMP
to
another,
initially
load
software,
and
rebuild
user
tables.
C09-DOOI-D7
• DBC/IOI2
Data
Base
Computer
System
Manual
(document
number CIO-OOOI)
The
system
manual
is
written
for
system
programmers,
application
programmers,
and
data
base
administrators.
The
manual
presents
the
many
considerations
and
trade-
offs
for
designing
and
querying
DBC/IOI2
data
bases
and
tables,
as
well
as
the
details
of
performance,
productivity,
startup
and
shutdown,
and
software
maintenance.
• DBC/IOI2
Data
Base
Computer
Host
Interface
Manual
(document
number
CI2-0001)
The
host
interface
manual
is
written
for
programmers
who
use
the
Call-Level
Interface
Version
I
(CLIvI)
rather
than
a
language
preprocessor
to
communicate
with
the
DBC/IOI2
system.
The
manual
presents
the
details
of
information
flow,
data
structures,
and
the
interface
routines.
The
manual
covers
CLIvI
for
hosts.
• DBC/IOI2
Data
Base
Computer
Call-Level
Interface
Manual
(document
number
C12-0006)
The
Call-Level
Interface
manual
is
written
for
programmers
who
use
the
Call-Level
Interface
Version
2
(CLIv2)
rather
than
a
language
preprocessor
to
communicate
with
the
DBC/I012
system.
The
manual
presents
the
details
of
information
flow,
data
structures,
and
the
interface
routines.
The
manual
covers
CLIv2
for
workstations.
• DBC/I012
Data
Base
Computer
COP
Interface
Manual
(document
number
CI2-000S)
The
COP
interface
manual
is
written
for
system
programmers.
The
manual
presents
the
details
of
information
flow,
data
structures,
and
interface
routines
in
the
COP
Interface
software.
C09-OOOI-07
ix
x
• DBC/1012
Data
Base
Computer
Network
Reference
Manual
(document
number
C03-0003)
The
network
reference
manual
is
written
for
network
administrators.
The
manual
gives
an
overview
of
the
COP
interface
and
the
components
involved.
It
also
describes
how
to
change
the
configuration
of
a DBC/1012
to
add
COPs
and
LANS
and
how
to
install
and
configure
Teradata's
workstation-resident
software.
The
steps
and
an
example
are
provided
for
IBM
PCs
and
compatibles
using
TCP/IP,
IBM
PCs and
compatibles
using
ISO/OSI,
and
AT&T
3B2s
using
TCP/IP.
• DBC/1012
Data
Base
Computer
Workstation
User's
Guide
(document
number
C09-0003)
The
user's
guide
covers
the
use
of
BTEQ,
showing
examples
of
using
BTEQ
on-line,
debugging
and
submitting
BTEQ
scripts,
converting
from
screen
displays
to
reports
sent
to
a
print
file,
and
using
DBC/SQL
macros.
For
each
set
of
BTEQ
commands,
it
describes
when
and
how
to
use
them
and
how
the
commands
are
related.
It
also
describes
each
command
in
detail.
• DBC/lOl2
Data
Base
Computer CICS
Interface
Manual
(document
number
C12-0002)
The CICS
interface
manual
is
written
for
programmers
who
access
the
resources
of
the
DBC/lOl2
system
through
CICS.
• DBC/1012
Data
Base
Computer
MVS
and
VM
Host
Software
Manual
(document
number
C13-000l)
The
MVS/VM
host
software
manual
is
written
for
programmers
who
must
understand
the
Teradata
software
that
resides
on
the
MVS
or
VM
host.
The
manual
describes
SVC
mode
and
cross
memory
services
mode
under
MVS,
and
corresponding
operation
under
VM.
• DBC/1012
Data
Base
Computer
Planning
Guide
(document
number
C07-000l)
The
planning
guide
is
written
for
personnel
who
are
responsible
for
the
hardware,
software,
and
facility
preparation
for
the
DBC/1012. The
guide
presents
physical
planning
issues
and
environmental
characteristics,
as well
as
software
planning
and
installation
considerations.
C09-DOOl-07
• DBC/1012
Data
Base
Computer
MVS
Software
Installation
Guide
(document
number
C16-000l)
The
MVS
software
installation
guide
is
written
for
technical
personnel.
The
guide
provides
procedures
for
installing
Teradata
MVS
interface
software.
• DBC/1012
Data
Base
Computer
Preprocessor
Reference
Manual
(document
number
C03-000S)
The
Preprocessor
manual
is
written
for
the
COBOL
and
PL/I
application
programmer.
This
manual
presents
details
of
preprocessor
use
and
includes
examples
in
COBOL
and
PL/I.
• DBC/1012
Data
Base
Computer
Glossary
(document
number GOI-OOOI)
S.
Leamy
July
1987
This
Glossary
is
intended
for
anyone
who
uses
the
Teradata
DBC/IOI2
data
base
computer.
This
Glossary
is
a
comprehensive
document
of
terms,
phrases,
accronyms,
etc.,
that
apply
to
any
hardware,
software,
or
firmware
matter
that
pertains
to
the
Teradata
DBC/lOl2
data
base
computer.
Los
Angeles,
California
C09-0001-07
xi
xii
C09-OOOl-07
CONTENTS
Chapter
Page
CHAPTER
1
1.1
1.2
1.3
1.4
CHAPTER
2
2.1
2.2
2.3
CHAPTER
3
3.1
3.1.1
3.1.2
3.1.3
3.1.4
3.1.5
3.1.6
3.2
3.2.1
3.2.1.1
3.2.1.1.1
3.2.1.1.2
3.2.1.1.3
3.2.1.1.4
3.2.1.2
3.2.1.3
3.2.1.4
3.2.1.5
3.2.2
3.2.3
3.2.3.1
3.2.3.2
3.3
3.3.1
3.3.2
3.3.3
3.3.3.1
3.3.3.2
3.3.3.3
3.3.4
3.3.5
C09-0001-07
WHAT
IS
THE
DBC/1012
DATA
BASE
COMPUTER?
••
1-
1
HOW
A DBC/1012
DATA
BASE
IS
ORGANIZED
HOW
YOU
COMMUNICATE
THROUGH
ITEQ • •
HOW
YOU
COMMUNICATE
THROUGH
BTEQ
SUMMARY
AND
PREVIEW
• • • • • • • •
1-
3
1-
6
1-
7
1-
7
GETTING
ESTABLISHED
AS
A DBC/1012
USER
•
2-
1
GETTING
ESTABLISHED
AS
A
USER
• • • • •
2-
1
LOGGING
ON
TO
THE
HOST
COMPUTER
2-
2
SUMMARY
AND
PREVIEW
• • • • • • • • • •
2-
2
COMMUNICATING
WITH
THE
DBC/1012
USING
ITEQ
•
COMMUNICATING
IN
AN
ITEQ SESSION • . . •
Starting
ITEQ
• • • • • • • • • • • •
Logging
on
to
the
DBC/1012 • • . • •
Understanding
System
Status
Messages
Interpreting
the
ITEQ
Display
Screen
Entering
Commands
and
Statements
Ending
an
ITEQ
Session
• • • • • • •
EDITING DBC/SQL
STATEMENTS
• • • • • • • • •
Entering
a
Statement
from
the
Input
Area
Using
PF
Keys
to
Execute
Commands
Using
Default
Assignments
•••••
Assigning
PF
Keys
During
a
Session
Assigning
PF
Keys
During
Startup
Displaying
PF
Key
Assignments
• • •
Changing
the
Size
of
the
Input
Area
Entering
a
Statement
• • • • • • • . •
Editing
a
Statement
•••••••••
Entering
a
New
Statement
• • • • • • •
Editing
a
Statement
in
the
Display
Area
•
Aborting
a
Statement
• • • • • •
Aborting
a
Statement
Under
TSO
Aborting
a
Statement
Under
VM
• •
VIEWING
STATEMENT
RESULTS
• • •
Using
Display
Commands • • • • • • •
Choosing
a
Formatting
Mode • • •
Paging
Through
a
Result
• • • • • • •
Paging
Forward
• • • • • • • •
Paging
Backward
•••
• • • • • • • •
Redisplaying
the
Current
Result
Page
•
Viewing
a Wide
Result
•
••
••
• • •
.
Changing
Formatting
Mode
During
Display.
3-
1
3-
1
3-
1
3-
3
3-
5
3-
6
3-
7
3-
8
3-
9
3-11
3-11
3-11
3-12
3-13
3-13
3-14
3-14
3-15
3-17
3-17
3-18
3-19
3-20
3-22
3-22
3-24
3-24
3-25
3-26
3-27
3-27
3-29
xiii
3.3.6
3.4
CHAPTER
4
4.1
4.2
4.2.1
4.2.2
4.3
4.4
CHAPTER
5
5.1
5.1.1
5.1.2
5.1.2.1
5.1.2.2
5.1.2.3
5.1.2.4
5.1.2.5
5.1.3
5.1.3.1
5.1.3.2
5.1.3.3
5.1.4
5.2
5.3
CHAPTER
6
6.1
6.2
6.3
6.4
6.4.1
6.4.2
6.4.3
6.4.4
6.4.5
6.4.6
6.4.7
6.4.8
6.4.9
6.4.10
6.4.11
6.4.11.1
6.4.11.2
6.4.11.3
6.5
6.5.1
xiv
Filing
a
Result
for
Later
Use
SUMMARY
AND
PREVIEW
• • • • • • • • · . . .
· . . .
USING
BTEQ
• • . . . . . . . . . . .
BTEQ
COMMANDS
• • • • • • • • •
RUNNING
A
BTEQ
JOB
• • • • • • • • • • • • •
Running
BTEQ
Under
TSO
•
RUNNING
BTEQ
UNDER
VM/CMS
. .
EXTRACTING
DBC/1012
DATA
TO
A
HOST
DATA
SET
SUMMARY
AND
PREVIEW
• • • • • • • • • • • •
CREATING
REPORTS
USING
ITEQ
AND
BTEQ
•
CREATING
A
REPORT
USING
ITEQ
• • • • • • • •
Using
Format
Defaults
• • • • • • • • • •
Setting
Format
Specifications
• • • • • •
Displaying
Format
Specifications
• • •
Viewing
the
Effect
of
Format
Commands
Def
ining
a
Report
Ti
tIe
• • • • • • •
Specifying
a
Null
Character
• • •
Suppressing
Repeating
Values
• • •
Using
DBC/SQL
Report
Writing
Aids
••••
Defining
Summaries
(WITH
Clause)
•••
Specifying
Column
Format
• • • • • • •
Defining
Headings
and
Summary
Titles
•
printing
a
Report
• •
••
••••••
CREATING
A
REPORT
USING
BTEQ
••
••••
SUMMARY
AND
PREVIEW
• • • • • • • •
QUERYING
TABLE
DATA
. . . . . . · . . .
STRUCTURING
A
DBC/SQL
STATEMENT
• • • •
ESTABLISHING
A
DEFAULT
DATA
BASE
••
••
SELECTING
COLUMNS
• • • • •
••
••••
SELECTING
ROWS
• • . • • • • •
••
•••
Specifying
Order
(ORDER
BY)
• • • • •
Eliminating
Duplicate
Rows
(DISTINCT) • •
Satisfying
Several
Conditions
(AND)
•••
Satisfying
One
of
Many
Conditions
(OR)
Narrowing
a
Search
Condition
(NOT)
•••
Obtaining
Matching
Values
(IN,
NOT
IN)
Specifying
a Range
(BETWEEN
•••
AND)
•••
Matching
Characters
(LIKE)
•••••••
Satisfying
a
Calculated
Condition
• • • •
Searching
For
NULL
Values
• • • • • • • •
Combining
SELECT
statements
• • • • • • •
UNION
Operator
• • • • • • • • • • • •
INTERSECT
Operator
• •
••
••••
MINUS • • • • • • • • • • • • • • • •
OBTAINING
RESULTS
ARITHMETICALLY
• • • • • •
Using
Arithmetic
Expressions
••••
3-29
3-29
4-
1
4-
2
4-
4
4-
4
4-
5
4-
6
4-
8
5-
1
5-
1
5-
1
5-
3
5-
4
5-
4
5-
5
5-
5
5-
5
5-
6
5-
7
5-
8
5-11
5-12
5-14
5-20
6-
1
6-
1
6-
2
6-
3
6-
5
6-
6
6-
7
6-
8
6-
9
6-
9
6-11
6-13
6-14
6-15
6-16
6-17
6-18
6-19
6-21
6-21
6-21
C09-000l-07
6.5.2
6.6
6.6.1
6.6.2
6.6.3
6.7
6.7.1
6.7.2
6.7.2.1
6.7.2.2
6.8
6.8.1
6.8.2
6.9
6.10
6.11
6.12
6.13
CHAPTER
7
7.1
7.1.1
7.1.1.1
7.1.1.2
7.1.1.3
7.1.1.4
7.1.1.5
7.1.2
7.1.2.1
7.1.2.2
7.1.3
7.1.4
7.1.4.1
7.1.4.2
7.1.4.3
7.2
7.3
7.3.1
7.3.2
7.3.3
7.3.4
7.3.5
7.3.6
7.4
7.4.1
7.4.2
7.4.3
7.5
7.6
7.7
7.8
C09-0001-07
Using
Aggregate
Operations
• • • • • • •
OPERATING
ON
DATES
• • • • • • • • • • • • •
Using
Arithmetic
Operations
•••
Using
Comparison
Operations
• • • • • • •
Converting
to
Another
Format
or
Notation
CHARACTER
STRING
EXPRESSIONS
• • • • • • • •
Concatenation
Operator
• • • • . • • • •
String
Functions
• • • • • • • •
SUBSTR
• • • • • • • • • • • • • • • •
INDEX
• • • • • • • • • • • •
SUMMARIZING
INFORMATION
BY
GROUPS
.••••
Selecting
Specific
Groups
••••••.•
Selecting
Specific
Rows
• • • • • • • • •
SELECTING
RELATED
DATA
FROM
SEVERAL
TABLES
•
SELECTING
RELATED
DATA
FROM
THE
SAME
TABLE
•
BUILDING
SEARCH
CONDITIONS
• • • • • • • • •
LOCKING
A
TABLE
FOR
ACCESS
•
••••••
SUMMARY
AND
PREVIEW
• • • •
••
••••
DEFINING
AND
MANAGING
DATA
. . .
CREATING
TABLES
• • • • • •
••
••••
Specifying
Column
Attributes
• • • •
Specifying
Data
Type •
••
.••.
Specifying
Default
Control
• • • • • •
Specifying
Case
• • •
••
••••
Specifying
Format
••••••
• • • •
Specifying
a
Title
••••••••••
Specifying
Data
Protection
• • • • • • •
Providing
for
Fallback
Data
• • • • •
Providing
for
Journal
Tables
• • • • •
COMPRESSing
Field
Entries
• • • • • • • •
Establishing
Indexes
••••••••••
Defining
a
primary
Index
• • • • • • •
Defining
a
Secondary
Index
• • • • • •
Defining
Unique
Indexes
• • •
LOADING
A
NEW
TABLE
WITH
EXISTING
DATA
• • •
ALTERING
A
TABLE
DEFINITION • • • .
Adding
and
Dropping
Columns • • • • • • •
Changing
Attributes
• • • • • • • • • • •
Changing
the
Fallback
Option
••
• • • •
Changing
the
JOURNAL
Option
• • • • • • •
Changing
the
Data
Type
Attribute
• • • •
Redefining
a
Primary
Index
• • • • • • •
USING
VIEWS
• • • • • • • • • • • •
Creating
a View • • • • • • • • • • • • •
Creating
a View
with
a
Locking
Clause
Replacing
a View • • • • • • • • • • • •
DOCUMENTING
TABLES,
COLUMNS,
VIEWS
•••••
RENAMING
TABLES
AND
VIEWS
• • • • • • • • •
REMOVING
TABLES
AND
VIEWS
• • •
SUMMARY
AND
PREVIEW
• • • . • •
••••
6-22
.6-26
6-26
6-27
6-27
6-28
6-28
6-30
6-30
6-31
6-31
6-32
6-33
6-33
6-34
6-35
6-37
6-38
7-
1
7-
2
7-
2
7-
4
7-
6
7-
6
7-
6
7-
6
7-
6
7-
7
7-
7
7-
8
7-
8
7-
9
7-
9
7-10
7-10
7-11
7-12
7-12
7-13
7-13
7-13
7-14
7-15
7-16
7-17
7-17
7-18
7-19
7-20
7-20
xv
CHAPTER
8
ADDING
AND
CHANGING
TABLE
DATA
• · · • · · •
8-
1
8.1
INSERTING
ROWS
• · · · · • · · · · · • · · ·
8-
1
8.1.1
Specifying
Insert
Data
· · · · · • ·
8-
2
8.1.2
Inserting
Data
by
Query
· • • · • · ·
8-
2
8.2
UPDATING
ROW
DATA
· · · · · · · · · · · · ·
8-
3
8.2.1
Specifying
New
Data
••••••••
·
8-
3
8.2.2
Specifying
an
Arithmetic
Expression
· • ·
8-
3
8.3
DELETING
ROW
DATA
· · · · · · · · ·
8-
4
8.4
USING
A
VIEW
TO
ADD
OR
CHANGE
DATA
· • ·
8-
4
8.5
SUMMARY
AND
PREVIEW
· · · • • · · · · ·
8-
6
CHAPTER
9
USING
MACROS
. · · · · · · · · · · · · ·
9-
1
9.1
CREATING
A
MACRO
· · · · · · • • • · · ·
9-
2
9.1.1
Identifying
Parameters
· · · · · · · · ·
9-
3
9.1.2
Defining
the
Macro · · · · · · · · ·
9-
3
9.1.3
Documenting
a Macro · · · · · · ·
9-
3
9.1.4
Aborting
a Macro · · · · · · · ·
9-
4
9.2
EXECUTING
A
MACRO
· · · · · · · · · · ·
9-
5
9.3
DEBUGGING
A
MACRO
· · · · ·
9-
6
9.4
REPLACING
A
MACRO
· · · · · · · · · · ·
9-
7
9.5
RENAMING
A
MACRO
· · · · · · · · · · · ·
9-
8
9.6
FORMATTING
MACRO
RESULTS
· · · · · · · ·
9-
a
9.7
DISPLAYING
A
FORMATTED
MACRO
RESULT
9-
9
9.8
REMOVING
A
MACRO
· · · · · · · ·
9-10
9.9
SUMMARY
AND
PREVIEW
· · · · · · · ·
9-10
CHAPTER
10
SHARING
DBC/I012 FACILITIES · · · · · · · ·
10-
1
10.1
WHAT
ARE
PRIVILEGES? · · · · · · · · · ·
10-
1
10.2
GRANTING
PRIVILEGES · · · · · · · · · ·
10-
4
10.2.1
Granting
Privileges
to
a
User
· · · · · ·
10-
6
10.2.2
Granting
All
privileges
to
a
User
· ·
10-
7
10.2.3
Granting
Privileges
to
a
Group
of
Users
·
10-
7
10.2.4
Revoking
Privileges
· · · · · · · · ·
10-
7
10.3
CREATING
USERS
· · · · · · · · · · · · ·
lO-
a
10.4
CREATING
DATA
BASES
· · · · · · · ·
10-
9
10.5
MODIFYING
USERS
AND
DATA
BASES
· · · · ·
10-10
10.6
REMOVING
USERS
AND
DATA
BASES
· · · · ·
10-11
10.7
TRANSFERRING
DATA
BASE
OWNERSHIP
• · · ·
10-12
10.8
SUMMARY
AND
PREVIEW
· · · · · · · · · ·
10-12
CHAPTER
11
VIEWING
DATA
BASE
INFORMATION
· · • · ·
Il-
l
11.1
QUERYING
DATA
DICTIONARY/DIRECTORY
VIEWS
· ·
Il-
l
11.1.1
Querying
The
Databases
View · · · · • · ·
11-
2
11.1.2
Querying
The
Tables
View • · · · · · • ·
11-
4
11.1.3
Querying
The Columns View · · · · · · · ·
lI-
S
11.1.4
Qu~rying
The
UserGrantedRights
View · · ·
11-
7
11.1.5
Querying
The
UserRights
View · · · · • ·
11-
9
11.1.6
Querying
The
SessionInfo
View · · · · · ·
11-
9
xvi
C09-0001-07
11.2
11.2.1
11.2.2
Appendix
APPENDIX
A
APPENDIX
B
B.1
B.2
B.3
B.4
B.5
APPENDIX
C
APPENDIX
D
D.1
D.2
INDEX
Figure
1-1
1-2
1-3
3-1
3-2
3-3
4-1
10-1
C09-0001-07
· . . · . . . . .
USING
THE
HELP
STATEMENT
•
Usage
Notes
• • • • • •
Examples
• • • • • • • · . . · . . .
· . . . . .
APPENDIXES
PERSONNEL
DATA
BASE
· . . . . . . .
...
. .
SYNTAX
SUMMARY
. . · · · · · · · ·
DBC/SQL
STATEMENTS
· · · · · · · · · ·
DBC/SQL
MODIFIERS
· · · · · ·
ITEQ
COMMANDS
. . · · · · · · · · · ·
BTEQ
COMMANDS
· · · · · · · · · ·
DATA
DICTIONARY/DIRECTORY
VIEW
FORMATS
DEFAULT
PF
KEYS
FOR
ITEQ
COMMANDS
· ·
DEFINING
ITEQ
OUTPUT
FILES · . .
DEFINING
A PRINT
OUTPUT
FILE • • • • •
DEFINING
A
RESULT
OUTPUT
FILE • • • •
· · ·
· · ·
· · ·
· · ·
· · ·
·
· · ·
· . .
. . . . . . . . . . . . . . . . . . . . . .
ILLUSTRATIONS
Communicating
with
the
DBC/1012
(MVS)
Table
in
a
Data
Base
• • • • • • •
Example
Tables
in
a
Personnel
Data
Base
ITEQ
Startup
Screen
•••
• • • • • •
ITEQ
Display
Screen
••••••
• • •
Result
of
a
SELECT
Statement
• • • • •
Selecting
and
Storing
Data
Using
BTEQ
Creating
Data
Bases
and
Users
• • • •
·
·
·
·
·
·
·
· ·
· ·
• ·
· ·
· ·
· ·
· ·
11-10
11-11
11-12
A- 1
B- 1
B- 2
B-10
B-11
B-15
B-20
C- 1
D-
1
D- 1
D-
3
X-
1
Page
I-
I
1-
3
1-
4
3-
1
3-
6
3-
7
4-
7
10-
3
xvii
Table
3-1
3-2
3-3
3-4
3-5
3-6
3-7
3-8
4-1
5-1
5-2
5-3
6-1
6-2
6-3
6-4
6-5
7-1
7-2
10-1
10-2
11-1
11-2
C-l
C-2
xviii
ITEQ
Status
and
System
Messages
• • • • • •
3-
5
Edit
Commands
Used
for
Input,
Display
Areas
3-
9
Edit
Commands
Only
For
Display
Area
••
• •
3-10
Default
PF
Key
Assignments,
Edit
Commands
3-11
Aborting
a
Statement
Under
TSO
• • • • •
3-19
Aborting
a
Statement
Under
VM
• • • • • • •
3-21
Display
Commands • • • • • • • • • • • • • •
3-22
Default
PF
Assignments,
Display
Commands • •
3-23
BTEQ
Commands
(2
parts)
•••.••••
4-
2
ITEQ
Format
Commands • • • • • • • • • •
5-
3
Format
Characters
• • • • • • • • • • • • •
5-
9
BTEQ
Formatting
Commands
(2
parts)
• • •
••
5-15
Comparison
Operations
•••
••
••••
6-
5
Logical
Operations
• • • • • • •
•• ••
6-
8
Expressions
Using
Set
Operators
• •
6-11
Ari
thmetic
Operators
. • • • • • • •
6-15
Aggregate
Operators
. . • • • • •
•.•
•
6-22
Data
Type
Phrases
(2
parts)
•••
•••
7-
4
Default
Control
Phrases
• . •
••
•••
7-
6
Privileges
Needed
for
Statement
(2
parts)
•
10-
4
Privileges
Implicitly
Granted
to
Creator
•
10-
5
End
User
Dictionary/Directory
Views
••.•
11-
1
Privilege
Codes
••.•
• • • • • • • • . •
11-
7
Default
PF
Keys
for
Edit,
PRINT Commands C- 1
Default
PF
Keys
for
Display
Commands • • c- 1
C09-000l-07
CHAPTER
I
WHAT
IS
THE
DBC/IOI2
DATA
BASE
COMPUTER?
The DBC/IOI2
Data
Base
Computer
is
a
processing
system
that
lets
you
manage
and
use
data
stored
in
the
data
base
computer.
The
DBC/IOI2
Data
Base
Computer
is
connected
to
and
operated
through
your
organization's
main
computer.
You
may
get
at
the
data
stored
in
the
DBC/I012
Data
Base
Computer
through
one
of
the
following
means:
•
Interactive
TEradata
Query
(ITEQ)
Facility
ITEQ
(pronounced
"eye-teek")
enables
you
to
use
an
interactive
CRT
(display)
terminal
with
a
typewriter
keyboard.
On
the
keyboard,
you
key
(enter)
requests
(queries)
for
the
data
you
need
and
instru~tions
for
changing
the
data.
•
Batch
TEradata
Query
(BTEQ)
Facility
BTEQ
(pronounced
"bee-teek")
enables
you
to
submit
requests
and
instructions
for
the
DBC/I012
data
base
computer
in
batch
mode.
•
Application
Program
Using
the
COBOL
Preprocessor
or
the
PL/l
Preprocessor,
you
may
write
a
COBOL
or
PL/I
application
program
that
contains
requests
and
instructions
for
the
DBC/I012
Data
Base Computer embedded
directly
in
the
source
code.
The
preprocessors
are
described
in
the
DBC/I012
Data
Base
Computer
Preprocessor
Reference
Manual.
Using
the
Call-Level
Interface
(CLI),
you
may
write
applications
containing
requests
and
instructions
in
high-level
languages
that
have
a
CALL
statement
(including
COBOL
and
PL/l).
The
CLI
is
described
in
the
DBC/I012
Data
Base
Computer
Host
Interface
Manual.
Through
ITEQ,
BTEQ,
or
an
application
program,
you
tell
the
DBC/I012
Data
Base
Computer
what
to
do
using
a
simple
language
called
DBC/SQL.
Based on
English
words,
DBC/SQL
is
easily
used
by
people
with
little
or
no
knowledge
of
computers.
DBC/SQL
(Structured
Query
Language)
syntax
is
broadly
compatible
with
SQL,
the
emerging
industry
standard.
As
shown
in
Figure
1-1,
which
illustrates
three
concurrent
DBC/lOI2
sessions
under
an
MVS
system,
a
DBC/SQL
request
is
communicated
to
the
Teradata
Director
Program
(TDP). The
TDP
creates
a
request
message
and
sends
it
over
a
block
multiplexer
channel
to
the
DBC/lOl2.
C09-OOOl-07
1-1
Table of contents
