Concepts
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. - Routines
A routine is an executable SQL object. - 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 IBM 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.
Parent topic: DB2 for i SQL reference