This chapter provides a high-level view of concepts that are important
to understand when using Structured Query Language (SQL). The reference material
contained in the rest of this manual provides a more detailed view.
Relational database
A relational database is
a database that can be perceived as a set of tables and can be manipulated
in accordance with the relational model of data. The relational database
contains a set of objects used to store, access, and manage data.
The set of objects includes tables, views, indexes, aliases, user
defined types, functions, procedures, sequences, variables, and packages.
Structured Query Language
Structured Query Language (SQL) is a standardized language
for defining and manipulating data in a relational database. In accordance
with the relational model of data, the database is perceived as a
set of tables, relationships are represented by values in tables,
and data is retrieved by specifying a result table that can be derived
from one or more base tables.
Schemas
The objects in a relational database are organized into
sets called schemas. A schema provides a logical classification of
objects in a relational database.
Tables Tables are logical structures maintained by the
database manager. Tables are made up of columns and rows. There is
no inherent order of the rows within a table. At the intersection
of every column and row is a specific data item called a value.
A column is a set of values of the same type. A row is
a sequence of values such that the nth value
is a value of the nth column of the table.
Views
A view provides an alternative way of looking at
the data in one or more tables.
User-defined types
A user-defined type is a data type that is defined
to the database using a CREATE statement.
Aliases
An alias is an alternate name for a table or view.
Packages and access plans
A package is an object that contains control structures
used to execute SQL statements.
Sequences
A sequence is a stored object that simply generates a sequence
of numbers in a monotonically ascending (or descending) order. Sequences
provide a way to have the database manager automatically generate
unique integer and decimal primary keys, and to coordinate keys across
multiple rows and tables.
Authorization, privileges and object ownership
Users (identified by an authorization ID) can successfully
execute SQL statements only if they have the authority to perform
the specified function. To create a table, a user must be authorized
to create tables; to alter a table, a user must be authorized to alter
the table; and so forth.
Catalog
The database manager maintains a set of tables containing
information about objects in the database. These tables and views
are collectively known as the catalog. The catalog tables contain
information about objects such as tables, views, indexes, packages,
and constraints.
Application processes, concurrency, and recovery
All SQL programs execute as part of an application process.
In the IBM i operating
system, an application process is called a job. In the case of ODBC,
JDBC, OLE DB, .NET, and DRDA®,
the application process ends when the connection ends even though
the job they are using does not end and may be reused.
Isolation level
The isolation level used during the execution of
SQL statements determines the degree to which the activation group
is isolated from concurrently executing activation groups.
Storage Structures
The System i® product
is an object-based system. All database objects in DB2 for
i (tables and indexes
for example) are objects in the IBM i operating system.
The single-level storage manager manages all storage of objects of
the database, so database specific storage structures (for example,
table spaces) are unnecessary.
Character conversion
A string is a sequence of bytes that may represent characters.
Within a string, all the characters are represented by a common coding representation.
In some cases, it might be necessary to convert these characters to a different
coding representation. The process of conversion is known as character
conversion.
Collating sequence
A collating sequence (also called a sort sequence) defines
how characters in a character set relate to each other when they are
compared and ordered.
Distributed relational database
A distributed relational database consists of a
set of tables and other objects that are spread across different but
interconnected computer systems or logical partitions on the same
computer system. Each computer system has a relational database manager
that manages the tables in its environment. The database managers
communicate and cooperate with each other in a way that allows a database
manager to execute SQL statements on another computer system.