IBM DB2 9 SQL参考手册：Linux, UNIX, Windows版

需积分: 10 137 浏览量更新于2024-07-24 收藏 7.1MB PDF 举报

"db2 9 SQL Reference Volume 是 IBM 官方提供的一份关于 DB2 Version 9 在 Linux、UNIX 和 Windows 平台上的 SQL 参考手册。这份文档包含了专有信息，受版权法保护，并且可能需要在特定的许可协议下使用。" DB2 SQL 参考手册是数据库管理员、开发人员和数据分析师的重要资源，它详细介绍了 DB2 Version 9 支持的 SQL 语法、语句、函数和操作。手册的内容通常包括以下几个核心部分： 1. **SQL 语法**：涵盖基本的 SQL 命令，如 SELECT、INSERT、UPDATE、DELETE，以及更复杂的 DDL（Data Definition Language）和 DML（Data Manipulation Language）语句，用于创建、修改和管理数据库对象。 2. **数据类型**：描述 DB2 支持的各种数据类型，如数值类型、字符串类型、日期/时间类型等，以及它们的使用方法和限制。 3. **函数和运算符**：详述 SQL 中可用的内置函数，包括聚合函数（如 SUM、AVG、COUNT）、数学函数、字符串函数、日期/时间函数等，以及各种逻辑和比较运算符。 4. **查询优化**：讨论如何编写高效的 SQL 查询，包括索引的使用、查询计划的解释以及如何使用 DB2 的查询优化器。 5. **视图和存储过程**：解释如何创建和使用视图来抽象数据，以及定义和执行存储过程，这些是封装复杂业务逻辑和提高代码复用性的关键工具。 6. **事务处理和并发控制**：介绍事务的概念、ACID 属性、锁定机制以及并发控制策略，这些都是确保数据一致性与安全性的基础。 7. **游标和动态SQL**：讲解如何使用游标进行逐行处理，以及如何编写动态 SQL 语句，以适应不同的运行时条件。 8. **安全性与权限**：阐述 DB2 的用户管理和权限控制，包括角色、授权和访问控制列表（ACL）。 9. **备份与恢复**：提供有关数据库备份、恢复和故障恢复的策略和技术，这对于数据保护至关重要。 10. **性能监控和调优**：描述如何监控数据库性能，使用工具分析瓶颈，并提供性能调优的建议。通过这份参考手册，用户可以深入理解 DB2 Version 9 的 SQL 功能，从而更好地设计、实施和维护数据库解决方案。对于需要处理大量数据的系统，理解和掌握这些内容对于提升系统的稳定性和效率具有决定性作用。同时，手册还可能包含实例和示例，帮助读者将理论知识应用于实际工作场景。

Table 1. SQL schema statements (continued)

SQL Statement Purpose

“COMMENT ” on page 138 Replaces or adds a comment to the description of an object.

“CREATE ALIAS ” on page 182 Defines an alias for a table, view, or another alias.

“CREATE BUFFERPOOL ” on page 185 Creates a new buffer pool.

“CREATE DATABASE PARTITION GROUP ”

on page 189

Defines a database partition group.

“CREATE DISTINCT TYPE ” on page 191 Defines a distinct data type.

“CREATE EVENT MONITOR ” on page 197 Specifies events in the database to monitor.

“CREATE FUNCTION ” on page 214 Registers a user-defined function.

“CREATE FUNCTION (External Scalar) ” on

page 215

Registers a user-defined external scalar function.

“CREATE FUNCTION (External Table) ” on

page 239

Registers a user-defined external table function.

“CREATE FUNCTION (OLE DB External

Table) ” on page 256

Registers a user-defined OLE DB external table function.

“CREATE FUNCTION (Sourced or Template)

” on page 263

Registers a user-defined sourced function.

“CREATE FUNCTION (SQL Scalar, Table, or

Row) ” on page 272

Registers and defines a user-defined SQL function.

“CREATE FUNCTION MAPPING ” on page

281

Defines a function mapping.

“CREATE INDEX ” on page 285 Defines an index on a table.

“CREATE INDEX EXTENSION ” on page 301 Defines an extension object for use with indexes on tables with

structured or distinct type columns.

“CREATE METHOD ” on page 307 Associates a method body with a previously defined method

specification.

“CREATE NICKNAME ” on page 313 Defines a nickname.

“CREATE PROCEDURE ” on page 325 Registers a procedure.

“CREATE PROCEDURE (External) ” on page

326

Registers an external procedure.

“CREATE PROCEDURE (Sourced) ” on page

339

Registers a procedure (the sourced procedure) that is based on

another procedure (the source procedure). In a federated system, a

federated procedure is a sourced procedure whose source procedure

is at a supported data source.

“CREATE PROCEDURE (SQL) ” on page 344 Registers an SQL procedure.

“CREATE SCHEMA ” on page 350 Defines a schema.

“CREATE SECURITY LABEL COMPONENT ”

on page 355

Creates a component that is to be used as part of a security policy.

“CREATE SECURITY LABEL ” on page 353 Creates a security label.

“CREATE SECURITY POLICY ” on page 358 Creates a security policy.

“CREATE SEQUENCE ” on page 360 Defines a sequence.

“CREATE SERVER ” on page 364 Defines a data source to a federated database.

“CREATE TABLE ” on page 368 Defines a table.

“CREATE TABLESPACE ” on page 433 Defines a table space.

“CREATE TRANSFORM ” on page 447 Defines transformation functions.

Supported SQL statements

2 SQL Reference Volume 2

Table 1. SQL schema statements (continued)

SQL Statement Purpose

“CREATE TRIGGER ” on page 454 Defines a trigger.

“CREATE TYPE (Structured) ” on page 465 Defines a structured data type.

“CREATE TYPE MAPPING ” on page 489 Defines a mapping between data types.

“CREATE USER MAPPING ” on page 495 Defines a mapping between user authorizations.

“CREATE VIEW ” on page 497 Defines a view of one or more table, view or nickname.

“CREATE WRAPPER ” on page 511 Registers a wrapper.

“DROP ” on page 540 Deletes objects in the database.

“GRANT (Database Authorities) ” on page

598

Grants authorities on the entire database.

“GRANT (Exemption) ” on page 602 Grants an exemption on an access rule for a specified label-based

access control (LBAC) security policy.

“GRANT (Index Privileges) ” on page 604 Grants the CONTROL privilege on indexes in the database.

“GRANT (Package Privileges) ” on page 606 Grants privileges on packages in the database.

“GRANT (Routine Privileges) ” on page 609 Grants privileges on a routine (function, method, or procedure).

“GRANT (Schema Privileges) ” on page 613 Grants privileges on a schema.

“GRANT (Security Label) ” on page 616 Grants a label-based access control (LBAC) security label for read

access, write access, or for both read and write access.

“GRANT (Sequence Privileges) ” on page 618 Grants privileges on a sequence.

“GRANT (Server Privileges) ” on page 620 Grants privileges to query a specific data source.

“GRANT (SETSESSIONUSER Privilege) ” on

page 622

Grants the privilege to use the SET SESSION AUTHORIZATION

statement.

“GRANT (Table Space Privileges) ” on page

624

Grants privileges on a table space.

“GRANT (Table, View, or Nickname

Privileges) ” on page 626

Grants privileges on tables, views and nicknames.

“GRANT (XSR object privileges) ” on page

633

Grants USAGE privilege on an XSR object.

“REFRESH TABLE ” on page 677 Refreshes the data in a materialized query table.

“RENAME ” on page 683 Renames an existing table.

“RENAME TABLESPACE ” on page 685 Renames an existing table space.

“REVOKE (Database Authorities) ” on page

692

Revokes authorities from the entire database.

“REVOKE (Exemption) ” on page 696 Revokes the exemption on an access rule for a specified label-based

access control (LBAC) security policy.

“REVOKE (Index Privileges) ” on page 697 Revokes the CONTROL privilege on given indexes.

“REVOKE (Package Privileges) ” on page 699 Revokes privileges from given packages in the database.

“REVOKE (Routine Privileges) ” on page 702 Revokes privileges on a routine (function, method, or procedure).

“REVOKE (Schema Privileges) ” on page 705 Revokes privileges on a schema.

“REVOKE (Security Label) ” on page 707 Revokes a label-based access control (LBAC) security label for read

access, write access, or for both read and write access.

“REVOKE (Sequence Privileges)” on page 708 Revokes privileges on a sequence.

“REVOKE (Server Privileges) ” on page 710 Revokes privileges to query a specific data source.

Supported SQL statements

Statements 3

Table 1. SQL schema statements (continued)

SQL Statement Purpose

“REVOKE (SETSESSIONUSER Privilege) ” on

page 712

Revokes the privilege to use the SET SESSION AUTHORIZATION

statement.

“REVOKE (Table Space Privileges) ” on page

714

Revokes the USE privilege on a given table space.

“REVOKE (Table, View, or Nickname

Privileges) ” on page 716

Revokes privileges from given tables, views or nicknames.

“REVOKE (XSR object privileges) ” on page

721

Revokes USAGE privilege on an XSR object.

“SET INTEGRITY ” on page 767 Sets the set integrity pending state and checks data for constraint

violations.

“TRANSFER OWNERSHIP ” on page 804 Transfers ownership of a database object.

Table 2. SQL data change statements

SQL Statement Purpose

“DELETE ” on page 526 Deletes one or more rows from a table.

“INSERT ” on page 638 Inserts one or more rows into a table.

“MERGE ” on page 654 Updates a target (a table or view) using data from a source (result of

a table reference).

“UPDATE ” on page 818 Updates the values of one or more columns in one or more rows of a

table.

Table 3. SQL data statements

SQL Statement Purpose

“ALLOCATE CURSOR ” on page 20 Allocates a cursor for the result set identified by the result set locator

variable.

“ASSOCIATE LOCATORS ” on page 123 Gets the result set locator value for each result set returned by a

procedure.

“CLOSE ” on page 136 Closes a cursor.

“DECLARE CURSOR ” on page 513 Defines an SQL cursor.

“DELETE ” on page 526 Deletes one or more rows from a table.

“FETCH ” on page 584 Assigns values of a row to host variables.

“FLUSH EVENT MONITOR ” on page 587 Writes out the active internal buffer of an event monitor.

“FLUSH PACKAGE CACHE ” on page 588 Removes all cached dynamic SQL statements currently in the

package cache.

“FREE LOCATOR ” on page 592 Removes the association between a locator variable and its value.

“INSERT ” on page 638 Inserts one or more rows into a table.

“LOCK TABLE ” on page 650 Either prevents concurrent processes from changing a table or

prevents concurrent processes from using a table.

“MERGE ” on page 654 Updates a target (a table or view) using data from a source (result of

a table reference).

“OPEN ” on page 663 Prepares a cursor that will be used to retrieve values when the

FETCH statement is issued.

“SELECT INTO ” on page 729 Specifies a result table of no more than one row and assigns the

values to host variables.

“SET Variable ” on page 796 Assigns values to NEW transition variables.

Supported SQL statements

4 SQL Reference Volume 2

Table 3. SQL data statements (continued)

SQL Statement Purpose

“UPDATE ” on page 818 Updates the values of one or more columns in one or more rows of a

table.

“VALUES INTO ” on page 830 Specifies a result table of no more than one row and assigns the

values to host variables.

Table 4. SQL transaction statements

SQL Statement Purpose

“COMMIT ” on page 148 Terminates a unit of work and commits the database changes made

by that unit of work.

“RELEASE SAVEPOINT ” on page 682 Releases a savepoint within a transaction.

“ROLLBACK ” on page 722 Terminates a unit of work and backs out the database changes made

by that unit of work.

“SAVEPOINT ” on page 725 Sets a savepoint within a transaction.

Table 5. SQL connection statements

SQL Statement Purpose

“CONNECT (Type 1) ” on page 168 Connects to an application server according to the rules for remote

unit of work.

“CONNECT (Type 2) ” on page 175 Connects to an application server according to the rules for

application-directed distributed unit of work.

“DISCONNECT ” on page 537 Terminates one or more connections when there is no active unit of

work.

“RELEASE (Connection) ” on page 680 Places one or more connections in the release-pending state.

“SET CONNECTION ” on page 733 Changes the state of a connection from dormant to current, making

the specified location the current server.

Table 6. SQL dynamic statements

SQL Statement Purpose

“DESCRIBE ” on page 533 Describes the result columns of a prepared SELECT statement.

“EXECUTE ” on page 569 Executes a prepared SQL statement.

“EXECUTE IMMEDIATE ” on page 575 Prepares and executes an SQL statement.

“PREPARE ” on page 668 Prepares an SQL statement (with optional parameters) for execution.

Table 7. SQL session statements

SQL Statement Purpose

“DECLARE GLOBAL TEMPORARY TABLE ”

on page 518

Defines the Global Temporary Table.

“EXPLAIN ” on page 578 Captures information about the chosen access plan.

“SET COMPILATION ENVIRONMENT ” on

page 731

Changes the current compilation environment in the connection to

match the values contained in the compilation environment provided

by a deadlock event monitor.

“SET CURRENT DEFAULT TRANSFORM

GROUP ” on page 735

Changes the value of the CURRENT DEFAULT TRANSFORM

GROUP special register.

“SET CURRENT DEGREE ” on page 737 Changes the value of the CURRENT DEGREE special register.

Supported SQL statements

Statements 5

Table 7. SQL session statements (continued)

SQL Statement Purpose

“SET CURRENT EXPLAIN MODE ” on page

739

Changes the value of the CURRENT EXPLAIN MODE special

“SET CURRENT EXPLAIN SNAPSHOT ” on

page 742

Changes the value of the CURRENT EXPLAIN SNAPSHOT special

“SET CURRENT FEDERATED

ASYNCHRONY ” on page 745

Changes the value of the CURRENT FEDERATED ASYNCHRONY

special register.

“SET CURRENT IMPLICIT XMLPARSE

OPTION ” on page 747

Changes the value of the CURRENT IMPLICIT XMLPARSE OPTION

special register.

“SET CURRENT ISOLATION ” on page 748 Changes the value of the CURRENT ISOLATION special register.

“SET CURRENT LOCK TIMEOUT ” on page

749

Changes the value of the CURRENT LOCK TIMEOUT special

“SET CURRENT MAINTAINED TABLE

TYPES FOR OPTIMIZATION ” on page 751

Changes the value of the CURRENT MAINTAINED TABLE TYPES

FOR OPTIMIZATION special register.

“SET CURRENT PACKAGE PATH ” on page

753

Assigns a value to the CURRENT PACKAGE PATH special register.

“SET CURRENT PACKAGESET ” on page 757 Sets the schema name for package selection.

“SET CURRENT QUERY OPTIMIZATION ”

on page 759

Changes the value of the CURRENT QUERY OPTIMIZATION special

“SET CURRENT REFRESH AGE ” on page

762

Changes the value of the CURRENT REFRESH AGE special register.

“SET ENCRYPTION PASSWORD ” on page

764

Sets the password for encryption.

“SET EVENT MONITOR STATE ” on page

765

Activates or deactivates an event monitor.

“SET PASSTHRU ” on page 785 Opens a session for submitting data source native SQL directly to the

data source.

“SET PATH ” on page 787 Changes the value of the CURRENT PATH special register.

“SET SCHEMA ” on page 789 Changes the value of the CURRENT SCHEMA special register.

“SET SERVER OPTION ” on page 791 Sets server option settings.

“SET SESSION AUTHORIZATION ” on page

793

Changes the value of the SESSION USER special register.

Table 8. SQL embedded host language statements

SQL Statement Purpose

“BEGIN DECLARE SECTION ” on page 125 Marks the beginning of a host variable declaration section.

“END DECLARE SECTION ” on page 568 Marks the end of a host variable declaration section.

“GET DIAGNOSTICS ” on page 593 Used to obtain information about the previously executed SQL

statement.

“INCLUDE ” on page 636 Inserts code or declarations into a source program.

“RESIGNAL ” on page 688 Used to resignal an error or warning condition.

“SIGNAL ” on page 801 Used to signal an error or warning condition.

“WHENEVER ” on page 832 Defines actions to be taken on the basis of SQL return codes.

Supported SQL statements

6 SQL Reference Volume 2

剩余882页未读，继续阅读

chinabluedolphin

粉丝: 6
资源: 10

IBM DB2 9 SQL参考手册：Linux, UNIX, Windows版

IBM DB2 9 SQL参考手册：Linux, UNIX, Windows版

IBM DB2 Universal Database SQL Reference Volume 1

DB2 9.5 for Linux, UNIX, and Windows SQL Reference

db2 WindowsSQL Reference, Volume 2

db2 WindowsSQL Reference, Volume 1

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

DB2 V9 sql Reference

DB2 8 SQL Reference

DB2 sql Reference v9.1

db2SQL_Reference_1_950

最新资源