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

sql

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

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"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版

SQL Reference DB2

DB2v9 SQL Reference Vol.1

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

IBM DB2 Universal Database SQL Reference Volume 1

db2 执行 sql脚本

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

db2 source sql脚本

db2 回滚sql1042c 58004

db2用SQL语句 生成copy as sql

db2用SQL语句 生成copy as sql 至查询界面

DB2 sql用户授权

db2用SQL语句 生成copy as sql 为一个长字符串

db2 报SQL 3129W

DB2的sql拼接字段去重

DB2数据库sql json拼接

db2 sql1034c

db2 sql调优方式

db2sql1042c 58004

db2数据库怎么运行sql

db2 sql循环语句

最新资源

db2用SQL语句生成copy as sql

db2用SQL语句生成copy as sql 至查询界面

db2用SQL语句生成copy as sql 为一个长字符串