DB2 9.5 for Linux, UNIX, and Windows SQL Reference

需积分: 0 152 浏览量更新于2024-07-21 收藏 7.4MB PDF 举报

"db2 WindowsSQL Reference, Volume 1" DB2是IBM开发的一款关系型数据库管理系统，被广泛应用于Linux、UNIX以及Windows等操作系统环境。"DB2 Version 9.5 for Linux, UNIX, and Windows SQL Reference, Volume 1" 是一本针对DB2 9.5版本的SQL参考手册，它在2009年4月进行了更新，文档编号为SC23-5861-02。这本书是DB2用户和技术人员的重要参考资料，详细介绍了该版本DB2的SQL语法、功能和操作。本参考手册旨在提供关于DB2 9.5版本的SQL语言使用的详尽指南，内容可能包括以下关键知识点： 1. **SQL语言基础**：涵盖SQL的基本概念，如数据定义语言（DDL）、数据操纵语言（DML）和数据控制语言（DCL）。这包括创建数据库对象（如表、视图、索引），插入、更新和删除数据，以及权限管理和事务控制。 2. **SQL查询**：详细解释了SELECT语句的使用，包括如何进行简单的选择、投影、联接、分组、排序以及使用子查询和集合操作。 3. **函数与操作符**：描述了SQL支持的各种内置函数，如数学函数、字符串函数、日期时间函数，以及逻辑和比较操作符，帮助用户进行复杂的数据处理和分析。 4. **存储过程和触发器**：介绍了如何在DB2中定义、调用和管理存储过程，以及创建和使用触发器来自动执行特定操作。 5. **游标和事务管理**：讲解了游标的使用，用于逐行处理查询结果，以及事务的概念，包括事务的开始、提交、回滚以及隔离级别。 6. **并发控制和锁机制**：讨论了DB2如何处理多用户并发访问数据时的冲突，以及锁的类型和使用策略。 7. **性能优化**：提供关于查询优化、索引策略、统计信息收集和调优工具的使用方法，以提高数据库的性能。 8. **备份与恢复**：介绍如何规划和执行DB2数据库的备份计划，以及在数据丢失或系统故障时进行恢复。 9. **安全性**：阐述了如何设置用户权限、角色管理和审计，确保数据库的安全性。 10. **XML支持**：DB2 9.5版本加强了对XML数据的支持，手册可能包含如何存储、检索和处理XML文档的指导。请注意，在使用此信息和其所支持的产品之前，读者应阅读附录O中的“注意事项”部分，了解相关的法律条款和使用限制。此外，IBM提供了在线和通过当地IBM代表订购出版物的方式，方便用户获取最新的技术资料。对于美国和加拿大的用户，可以直接通过电话1-800-IBM-4YOU (426-4968)联系DB2 Marketing and Sales订购相关出版物。无论您在全球何处，都可以通过IBM的网站找到联系信息，以便获取所需的支持和服务。当向IBM发送信息时，用户需同意授予IBM非独家权利，这可能涉及到知识产权的授权问题。

Related documentation

xiv SQL Reference, Volume 1

Chapter 1. Concepts

Databases

A DB2 database is a relational database. The database stores all data in tables that are

related to one another. Relationships are established between tables such that data

is shared and duplication is minimized.

A relational database is a database that is treated as a set of tables and manipulated

in accordance with the relational model of data. It contains a set of objects used to

store, manage, and access data. Examples of such objects are tables, views, indexes,

functions, triggers, and packages. Objects can be either defined by the system

(system-defined objects) or defined by the user (user-defined objects).

A distributed relational database consists of a set of tables and other objects that are

spread across different but interconnected computer systems. Each computer

system has a relational database manager to manage the tables in its environment.

The database managers communicate and cooperate with each other in a way that

allows a given database manager to execute SQL statements on another computer

system.

A partitioned relational database is a relational database whose data is managed

across multiple database partitions. This separation of data across database

partitions is transparent to users of most SQL statements. However, some data

definition language (DDL) statements take database partition information into

consideration (for example, CREATE DATABASE PARTITION GROUP). DDL is the

subset of SQL statements used to describe data relationships in a database.

A federated database is a relational database whose data is stored in multiple data

sources (such as separate relational databases). The data appears as if it were all in

a single large database and can be accessed through traditional SQL queries.

Changes to the data can be explicitly directed to the appropriate data source.

Structured Query Language (SQL)

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 treated

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.

SQL statements are executed by a database manager. One of the functions of the

database manager is to transform the specification of a result table into a sequence

of internal operations that optimize data retrieval. The transformation occurs in

two phases: preparation and binding.

All executable SQL statements must be prepared before they can be executed. The

result of preparation is the executable or operational form of the statement. The

method of preparing an SQL statement and the persistence of its operational form

distinguish static SQL from dynamic SQL.

Queries and table expressions

A query is a component of certain SQL statements; it specifies a (temporary) result

table.

A table expression creates a temporary result table from a simple query. Clauses

further refine the result table. For example, you can use a table expression as a

query to select all of the managers from several departments, specify that they

must have over 15 years of working experience, and be located at the New York

branch office.

A common table expression is like a temporary view within a complex query. It can

be referenced in other places within the query, and can be used in place of a view.

Each use of a specific common table expression within a complex query shares the

same temporary view.

Recursive use of a common table expression within a query can be used to support

applications such as airline reservation systems, bill of materials (BOM) generators,

and network planning.

DB2 Call level interface (CLI) and open database connectivity (ODBC)

The DB2 call level interface is an application programming interface that provides

functions for processing dynamic SQL statements to application programs. CLI

programs can also be compiled using an open database connectivity Software

Developer’s Kit (available from Microsoft

or other vendors), which enables access

to ODBC data sources. Unlike embedded SQL, this interface requires no

precompilation. Applications can be run against a variety of databases without

having to be compiled against each of these databases. Applications use procedure

calls at run time to connect to databases, issue SQL statements, and retrieve data

and status information.

The DB2 CLI interface provides many features not available in embedded SQL. For

example:

v CLI provides function calls that support a way of querying database catalogs

that is consistent across the DB2 family. This reduces the need to write catalog

queries that must be tailored to specific database servers.

v CLI provides the ability to scroll through a cursor:

– Forward by one or more rows

– Backward by one or more rows

– Forward from the first row by one or more rows

– Backward from the last row by one or more rows

– From a previously stored location in the cursor.

Stored procedures called from application programs that were written using CLI

can return result sets to those programs.

Java database connectivity (JDBC) and embedded SQL for Java

(SQLJ) programs

DB2 implements two standards-based Java

™

programming APIs: Java database

connectivity (JDBC) and embedded SQL for Java (SQLJ). Both can be used to create

Java applications and applets that access DB2:

Queries and table expressions

2 SQL Reference, Volume 1

v JDBC calls are translated into DB2 CLI calls through Java native methods. JDBC

requests flow from the DB2 client through DB2 CLI to the DB2 server. JDBC

cannot use static SQL.

v SQLJ applications use JDBC as a foundation for such tasks as connecting to

databases and handling SQL errors, but can also contain embedded static SQL

statements in the SQLJ source files. An SQLJ source file must be translated by

the SQLJ translator before the resulting Java source code can be compiled.

Schemas

A schema is a collection of named objects; it provides a way to group those objects

logically. A schema is also a name qualifier; it provides a way to use the same

natural name for several objects, and to prevent ambiguous references to those

objects.

For example, the schema names ’INTERNAL’ and ’EXTERNAL’ make it easy to

distinguish two different SALES tables (INTERNAL.SALES, EXTERNAL.SALES).

Schemas also enable multiple applications to store data in a single database

without encountering namespace collisions.

A schema is distinct from, and should not be confused with, an XML schema,

which is a standard that describes the structure and validates the content of XML

documents.

A schema can contain tables, views, nicknames, triggers, functions, packages, and

other objects. A schema is itself a database object. It is explicitly created using the

CREATE SCHEMA statement, with the current user or a specified authorization ID

recorded as the schema owner. It can also be implicitly created when another

object is created, if the user has IMPLICIT_SCHEMA authority.

A schema name is used as the high order part of a two-part object name. If the

object is specifically qualified with a schema name when created, the object is

assigned to that schema. If no schema name is specified when the object is created,

the default schema name is used (specified in the CURRENT SCHEMA special

For example, a user with DBADM authority creates a schema called C for user A:

CREATE SCHEMA C AUTHORIZATION A

User A can then issue the following statement to create a table called X in schema

C (provided that user A has the CREATETAB database authority):

CREATE TABLE C.X (COL1 INT)

Some schema names are reserved. For example, built-in functions belong to the

SYSIBM schema, and the pre-installed user-defined functions belong to the

SYSFUN schema.

When a database is created, if it is not created with the RESTRICTIVE option, all

users have IMPLICIT_SCHEMA authority. With this authority, users implicitly

create a schema whenever they create an object with a schema name that does not

already exist. When schemas are implicitly created, CREATEIN privileges are

granted which allows any user to create other objects in this schema. The ability to

create objects such as aliases, distinct types, functions, and triggers is extended to

Java database connectivity (JDBC) and embedded SQL for Java (SQLJ) programs

Chapter 1. Concepts 3

implicitly-created schemas. The default privileges on an implicitly-created schema

provide backward compatibility with previous versions.

If IMPLICIT_SCHEMA authority is revoked from PUBLIC, schemas can be

explicitly created using the CREATE SCHEMA statement, or implicitly created by

users (such as those with DBADM authority) who have been granted

IMPLICIT_SCHEMA authority. Although revoking IMPLICIT_SCHEMA authority

from PUBLIC increases control over the use of schema names, it can result in

authorization errors when existing applications attempt to create objects.

Schemas also have privileges, allowing the schema owner to control which users

have the privilege to create, alter, copy, and drop objects in the schema. This

provides a way to control the manipulation of a subset of objects in the database.

A schema owner is initially given all of these privileges on the schema, with the

ability to grant the privileges to others. An implicitly-created schema is owned by

the system, and all users are initially given the privilege to create objects in such a

schema. A user with SYSADM or DBADM authority can change the privileges that

are held by users on any schema. Therefore, access to create, alter, copy, and drop

objects in any schema (even one that was implicitly created) can be controlled.

Tables

Tables are logical structures maintained by the database manager. Tables are made

up of columns and rows.

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 or one of its subtypes. A row is a

sequence of values arranged so that the nth value is a value of the nth column of

the table.

An application program can determine the order in which the rows are populated

into the table, but the actual order of rows is determined by the database manager,

and typically cannot be controlled. Multidimensional clustering (MDC) provides

some sense of clustering, but not actual ordering between the rows.

Constraints

Within any business, data must often adhere to certain restrictions or rules. For

example, an employee number must be unique. The database manager provides

constraints as a way to enforce such rules.

The following types of constraints are available:

v NOT NULL constraints

v Unique (or unique key) constraints

v Primary key constraints

v Foreign key (or referential integrity) constraints

v (Table) Check constraints

v Informational constraints

Constraints

are only associated with tables and are either defined as part of the

table creation process (using the CREATE TABLE statement) or are added to a

table’s definition after the table has been created (using the ALTER TABLE

statement). You can use the ALTER TABLE statement to modify constraints. In

Schemas

4 SQL Reference, Volume 1

剩余806页未读，继续阅读

worldlove2011

粉丝: 0
资源: 4

DB2 9.5 for Linux, UNIX, and Windows SQL Reference

db2 WindowsSQL Reference, Volume 2

db2SQL_Reference_1_950

DB2 Version 9, SQL Reference Volume 2(高清, 英文版)

db2 执行 sql脚本

db2 source sql脚本

如何将db2的sql语句转换为mysql的sql语句？

db2 回滚sql1042c 58004

db2 报SQL 3129W

db2 windows

DB2 sql用户授权

最新资源