DB2 9.5 Windows SQL参考指南（第2卷）：2009年更新

需积分: 10 178 浏览量更新于2024-07-21 收藏 7.31MB PDF 举报

"《DB2 for Windows SQL Reference, Volume 2》是一本针对DB2 9.5版本的详细参考手册，适用于Linux、UNIX和Windows平台。该文档于2009年4月更新，包含了对DB2数据库在这些操作系统上的高级功能和操作指南。书中强调，在使用前务必阅读附录B中的"注意事项"，了解版权信息、许可证协议以及产品支持的相关条款。本书的核心内容覆盖了DB2 Version 9.5的各种SQL语言特性，包括但不限于查询优化、数据类型、存储过程、事务处理、安全性管理和备份恢复策略。它还提供了系统管理工具的使用方法，如监控、配置和性能调优。对于Windows平台的特定特性，例如集成Windows服务、网络连接选项以及与Windows应用程序的交互，也有深入的阐述。版权限制方面，书中包含了IBM的专有信息，受版权保护，并非所有内容都包含产品保修声明。购买和获取IBM出版物可以通过在线IBM Publications Center（<https://www.ibm.com/shop/publications/>）或联系本地IBM代表进行。美国和加拿大的用户可以直接拨打1-800-IBM-4YOU (426-4968)进行订购。用户在向IBM发送信息时，需同意IBM的非独家使用条款。此外，读者应注意到，本参考手册旨在提供技术指导，但并非官方的技术支持文档，遇到问题时可能需要寻求IBM正式技术支持或查阅更详尽的产品手册。总体而言，《DB2 for Windows SQL Reference, Volume 2》是开发人员、管理员和系统分析师不可或缺的参考资料，帮助他们充分利用DB2在Windows环境下的强大功能。"

Table 4. SQL transaction statements

SQL Statement Purpose

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

by that unit of work.

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

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

by that unit of work.

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

Table 5. SQL connection statements

SQL Statement Purpose

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

unit of work.

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

application-directed distributed unit of work.

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

work.

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

“SET CONNECTION” on page 808 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 616 Obtains information about an object.

“DESCRIBE INPUT” on page 616 Obtains information about the input parameter markers of a

prepared statement.

“DESCRIBE OUTPUT” on page 619 Obtains information about a prepared statement or information about

the select list columns in a prepared SELECT statement.

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

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

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

Table 7. SQL session statements

SQL Statement Purpose

“DECLARE GLOBAL TEMPORARY TABLE”

on page 603

Defines the Global Temporary Table.

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

“SET COMPILATION ENVIRONMENT” on

page 807

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 DECFLOAT ROUNDING

MODE” on page 809

Verifies that the specified rounding mode is the value that is

currently set for the CURRENT DECFLOAT ROUNDING MODE

special register.

“SET CURRENT DEFAULT TRANSFORM

GROUP” on page 811

Changes the value of the CURRENT DEFAULT TRANSFORM

GROUP special register.

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

SQL statements

6 SQL Reference, Volume 2

Table 7. SQL session statements (continued)

SQL Statement Purpose

“SET CURRENT EXPLAIN MODE” on page

813

Changes the value of the CURRENT EXPLAIN MODE special

“SET CURRENT EXPLAIN SNAPSHOT” on

page 816

Changes the value of the CURRENT EXPLAIN SNAPSHOT special

“SET CURRENT FEDERATED

ASYNCHRONY” on page 818

Changes the value of the CURRENT FEDERATED ASYNCHRONY

special register.

“SET CURRENT IMPLICIT XMLPARSE

OPTION” on page 819

Changes the value of the CURRENT IMPLICIT XMLPARSE OPTION

special register.

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

“SET CURRENT LOCK TIMEOUT” on page

821

Changes the value of the CURRENT LOCK TIMEOUT special

“SET CURRENT MAINTAINED TABLE

TYPES FOR OPTIMIZATION” on page 822

Changes the value of the CURRENT MAINTAINED TABLE TYPES

FOR OPTIMIZATION special register.

“SET CURRENT MDC ROLLOUT MODE” on

page 824

Assigns a value to the CURRENT MDC ROLLOUT MODE special

“SET CURRENT OPTIMIZATION PROFILE”

on page 825

Assigns a value to the CURRENT OPTIMIZATION PROFILE special

“SET CURRENT PACKAGE PATH” on page

828

Assigns a value to the CURRENT PACKAGE PATH special register.

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

“SET CURRENT QUERY OPTIMIZATION”

on page 833

Changes the value of the CURRENT QUERY OPTIMIZATION special

“SET CURRENT REFRESH AGE” on page 835 Changes the value of the CURRENT REFRESH AGE special register.

“SET ENCRYPTION PASSWORD” on page

837

Sets the password for encryption.

“SET EVENT MONITOR STATE” on page 838 Activates or deactivates an event monitor.

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

data source.

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

“SET ROLE” on page 860 Verifies that the authorization ID of the session is a member of a

specific role.

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

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

“SET SESSION AUTHORIZATION” on page

864

Changes the value of the SESSION USER special register.

Table 8. SQL embedded host language statements

SQL Statement Purpose

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

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

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

statement.

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

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

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

SQL statements

Statements 7

Table 8. SQL embedded host language statements (continued)

SQL Statement Purpose

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

Table 9. SQL control statements

SQL Statement Purpose

“CALL” on page 174 Calls a procedure.

“CASE” on page 179 Selects an execution path based on multiple conditions.

“Compound SQL (Dynamic)” on page 195 Combines one or more other SQL statements into an dynamic block.

“Compound SQL (Embedded)” on page 200 Combines one or more other SQL statements into an executable

block.

“Compound SQL (Procedure)” on page 203 Groups other statements together in an SQL procedure.

“FOR” on page 673 Executes a statement or group of statements for each row of a table.

“GOTO” on page 678 Used to branch to a user-defined label within an SQL procedure.

“IF” on page 717 Selects an execution path based on the evaluation of a condition.

“ITERATE” on page 729 Causes the flow of control to return to the beginning of a labelled

loop.

“LEAVE” on page 730 Transfers program control out of a loop or a compound statement.

“LOOP” on page 732 Repeats the execution of a statement or a group of statements.

“REPEAT” on page 762 Executes a statement or group of statements until a search condition

is true.

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

“RETURN” on page 766 Used to return from a routine.

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

“WHILE” on page 901 Repeats the execution of a statement or group of statements while a

specified condition is true.

How SQL statements are invoked

SQL statements are classified as executable or non-executable.

An executable statement can be invoked in four ways. It can be:

v Embedded in an application program

v Embedded in an SQL procedure.

v Prepared and executed dynamically

v Issued interactively

Depending

on the statement, some or all of these methods can be used.

(Statements embedded in REXX

™

are prepared and executed dynamically.)

A non-executable statement can only be embedded in an application program.

Another SQL statement construct is the select-statement. A select-statement can be

invoked in three ways. It can be:

v Included in DECLARE CURSOR, and executed implicitly by OPEN, FETCH and

CLOSE (static invocation)

SQL statements

8 SQL Reference, Volume 2

v Prepared dynamically, referenced in DECLARE CURSOR, and executed

implicitly by OPEN, FETCH and CLOSE (dynamic invocation)

v Issued interactively

Embedding a statement in an application program

SQL statements can be included in a source program that will be submitted to a

precompiler. Such statements are said to be embedded in the program. An

embedded statement can be placed anywhere in the program where a host

language statement is allowed. Each embedded statement must be preceded by the

keywords EXEC SQL.

An executable statement embedded in an application program is executed every

time a statement of the host language would be executed if it were specified in the

same place. Thus, a statement within a loop is executed every time the loop is

executed, and a statement within a conditional construct is executed only when the

condition is satisfied.

An embedded statement can contain references to host variables. A host variable

referenced in this way can be used in two ways. It can be used:

v As input (the current value of the host variable is used in the execution of the

statement)

v As output (the variable is assigned a new value as a result of executing the

statement)

particular, all references to host variables in expressions and predicates are

effectively replaced by current values of the variables; that is, the variables are

used as input.

All executable statements should be followed by a test of the SQL return code.

Alternatively, the WHENEVER statement (which is itself non-executable) can be

used to change the flow of control immediately after the execution of an embedded

statement.

All objects referenced in data manipulation language (DML) statements must exist

when the statements are bound to a database.

An embedded non-executable statement is processed only by the precompiler. The

precompiler reports any errors encountered in the statement. The statement is never

processed during program execution; therefore, such statements should not be

followed by a test of the SQL return code.

Statements can be included in the SQL-procedure-body portion of the CREATE

PROCEDURE statement. Such statements are said to be embedded in the SQL

procedure. Whenever an SQL statement description refers to a host-variable, an

SQL-variable can be used if the statement is embedded in an SQL procedure.

Dynamic preparation and execution

An application program can dynamically build an SQL statement in the form of a

character string placed in a host variable. In general, the statement is built from

some data available to the program (for example, input from a workstation). The

statement (not a select-statement) constructed can be prepared for execution by

means of the (embedded) PREPARE statement, and executed by means of the

How SQL statements are invoked

Statements 9

(embedded) EXECUTE statement. Alternatively, an (embedded) EXECUTE

IMMEDIATE statement can be used to prepare and execute the statement in one

step.

A statement that is going to be dynamically prepared must not contain references

to host variables. It can instead contain parameter markers. (For rules concerning

parameter markers, see “PREPARE”.) When the prepared statement is executed,

the parameter markers are effectively replaced by current values of the host

variables specified in the EXECUTE statement. Once prepared, a statement can be

executed several times with different values for the host variables. Parameter

markers are not allowed in the EXECUTE IMMEDIATE statement.

Successful or unsuccessful execution of the statement is indicated by the setting of

an SQL return code in the SQLCA after the EXECUTE (or EXECUTE IMMEDIATE)

statement completes. The SQL return code should be checked, as described above.

For more information, see “SQL return codes (SQLCODE and SQLSTATE)” on

page 11.

Static invocation of a select-statement

A select-statement can be included as a part of the (non-executable) DECLARE

CURSOR statement. Such a statement is executed every time the cursor is opened

by means of the (embedded) OPEN statement. After the cursor is open, the result

table can be retrieved, one row at a time, by successive executions of the FETCH

statement.

Used in this way, the select-statement can contain references to host variables.

These references are effectively replaced by the values that the variables have when

the OPEN statement executes.

Dynamic invocation of a select-statement

An application program can dynamically build a select-statement in the form of a

character string placed in a host variable. In general, the statement is built from

some data available to the program (for example, a query obtained from a

workstation). The statement so constructed can be prepared for execution by

means of the (embedded) PREPARE statement, and referenced by a

(non-executable) DECLARE CURSOR statement. The statement is then executed

every time the cursor is opened by means of the (embedded) OPEN statement.

After the cursor is open, the result table can be retrieved, one row at a time, by

successive executions of the FETCH statement.

Used in this way, the select-statement must not contain references to host variables.

It can contain parameter markers instead. The parameter markers are effectively

replaced by the values of the host variables specified in the OPEN statement.

Interactive invocation

A capability for entering SQL statements from a workstation is part of the

architecture of the database manager. A statement entered in this way is said to be

issued interactively. Such a statement must be an executable statement that does

not contain parameter markers or references to host variables, because these make

sense only in the context of an application program.

How SQL statements are invoked

10 SQL Reference, Volume 2

剩余944页未读，继续阅读

worldlove2011

粉丝: 0
资源: 4

DB2 9.5 Windows SQL参考指南（第2卷）：2009年更新

IBM DB2 Universal Database SQL Reference Volume 1

DB2 9.5 for Linux, UNIX, and Windows SQL Reference

IBM DB2 9.7 SQL Reference for Linux, UNIX, and Windows

db2 WindowsSQL Reference, Volume 1

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

DB2 V9 sql Reference

db2SQL_Reference_1_950

DB2 V9.5 - Call Level Interface Guide and Reference, Volume 2.pdf

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

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

最新资源