Introduction to DB2/DB2 Concents and Facilities
The DB2 Environment
The DB2 environment is made up of these DB2 objects:
- STOGROUPs (storage groups)-To automate the allocation of
the underlying linear VSAM data sets
- DATABASES-To group related objects together
- TABLESPACES-The physical file allocations that contain
your data
- TABLES-The logical row layout of your data and the level at
which you will be coding SQL statements
- INDEXES-To speed access to your data. Once defined on the
table, DB2 handles all maintenance of the information and
determines via the optimizer when to use the index.
At the time of object creation, the DB2 catalog is updated with the
name and characteristics of each object.
Only the creation of tablespaces and indexes has any effect on your
physical MVS environment. The creation of tablespaces and indexes
physically allocates the primary space quantity requested for the
tablespace or index (unless you have already created your own
VSAM data sets). This is the space that will be used when data is
loaded into the tablespace and the indexes are built.
As part of a table definition, columns are named and each named
column is assigned a data quality known as an attribute.
In addition to the attribute assigned to each column, there must also
be an attribute qualifier assigned to each column.
The purpose of the attribute qualifier is to signal to DB2:
- When the user will always have to supply a value
- Whether or not a default value should automatically be assigned
when the user supplies no value
- What the default value should be since a default is allowed
- Whether or not this single column is the Primary Key or needs
to be unique
Columns needing a numeric data quality can be assigned one of the
following:
- SMALLINT-Data is stored in binary format. Useful for
colunms that will always be whole numbers and used in
arithmetic operations.
- INTEGER-Data is stored in binary format. Same as SMALLINT, but with a
larger value range.
- DECIMAL(p,s)-Data is stored in packed format. More often
used than either SMALLINT or INTEGER because of its ability
to represent decimal values. Used for columns that will hold
dollar values or any other decimal values. The total number of
numeric digits (precision) is p, while the number to the richt of
the decimal point (scale) is s. Also valid is NUMERIC (P,S).
- FLOAT(n)-Data is stored in either short floating point
(REAL) or long floating point (DOUBLE) format, depending
on the number of decimal digits indicated. Used exclusively for
scientific applications where extreme precision is mandated.
Columns needing a character data quality can be assigned one of the
following:
- CHAR(x)-Data is stored in fixed-length character format.
Character data length must be between 1 and 254. Any attempt
to store data values longer than the fixed length of the column
will result in truncation to the length of the column. Character
data is left justified in the column.
- VARCHAR(x)-Data is stored in variable length character
format (a 27 byte length field precedes the actual column data).
Only needed if DASD space is at a premium. Even then
VARCHAR should only be used for columns that are a
minimum of 18 bytes. Also, the range from minimum length to
maximum length should vary greatly. The x states the maximum
length for the column.
- LONG VARCHAR-Data is stored in variable-length
character format. The maximum length of the column is all the
remaining, space left on the page. DB2 will calculate what is
needed.
Using the CHAR/VARCHAR data types for columns will make it
possible for those columns to be searched usina the wild card
functions built into SQL.
Graphic data types are needed for certain foreign languages whose
character sets exceed 256 characters and symbols. Graphic data types
use the Double-Byte Character Set (DBCS).
The characteristics of graphic data types are the same as character
data types except that graphic data requires two bytes of storage for
each character or symbol.
Columns may be assigned one of the following date/time data types:
When data is stored within a column with one of these data types, the
internal representation is defined by DB2 and is transparent to the
user. Data retrieved from a column of this type will be placed into a
character string format. Various external formats are available for
both date and time.
External formats for columns containing date or time can be specified
for the entire site when DB2 is installed, or can be set through the
date or time precompiler options.
To insert or update data values in a column of this type, you can make
use of the following DB2 special registers:
- CURRENT DATE or CURRENT_DATE
- CURRENT TIME or CURRENT_TIME
- CURRENT TIMESTAMP or CURRENT_TIMESTAMP
- CURRENT TIMEZONE
CURRENT TIMEZONE is the difference between CURRENT TIME
and Greenwich Mean Time (hhmmss).
A VIEW is defined to be an alternative representation of data from
one or more tables. There is no data physically stored in a view. It is
a logical definition of the columns and/or rows that will be presented
to an authorized user. It can also be used to restrict the privileges of
INSERT, UPDATE, and DELETE to specific columns and/or rows.
Views make it possible to tailor the way data is presented to an
application or end user.
Views provide a means of achieving row and colunm sensitive
security and true data independence.
- Data independence is the notion that a program, user, or
application be allowed to access only the amount of data that it
requires-and no more.
- Views in DB2 conform to the guidelines contained within the
relational model discussed in the Relational Database Concepts
chapter.
DB2, like other relational databases, requires the use of the
Structured Query Lanauaae (SQL) to define and manipulate data.
SQL is the language used to manipulate:
- DB2 objects
- Table data
- DB2 authorizations
SQL has three major components:
- DDL (Data Definition Language) is used for creating, altering
and dropping DB2 objects. The DB2 catalog is affected
whenever a DDL statement is successfully executed.
- DML (Data Manipulation Language) is used to populate,
retrieve, manipulate, and delete end-user data. The effective use
of this language is one of the primary thrusts of this course.
- DCL (Data Control Language) is used to grant or revoke
authorizations given to users in the DB2 environment.
Whenever a DCL command is successfully executed, the DB2
catalog is updated.
OIT Applications Support
Last Modified:
©2001 All Rights Reserved
