DB2 UDB V9.1 SQL Cookbook - 最新版

5星 · 超过95%的资源需积分: 10 199 浏览量更新于2024-08-02 收藏 1.25MB PDF 举报

"DB2 UDB V9.1 SQL Cookbook 是一本由 Graeme Birchall 编写的关于 DB2 通用数据库的英文原版教程，适用于 Windows、Unix、Linux 等平台，但不适用于 DB2 for z/OS 除非运行在 V8 新功能模式或更好的 V9 模式。本书内容频繁更新，建议读者获取最新版以获得最佳示例。" DB2 Universal Database (UDB) 是 IBM 提供的一种关系型数据库管理系统，版本 V9.1 在该系列中引入了多项增强功能和优化。这本书主要针对的是 SQL（结构化查询语言）的使用，SQL 是用于管理关系数据库的标准语言，包括数据查询、数据操纵、数据定义和数据控制等操作。书中内容可能涵盖以下几个关键知识点： 1. **SQL 基础**：介绍 SQL 的基本语法，如 SELECT 语句用于查询数据，INSERT 用于插入数据，UPDATE 用于修改数据，DELETE 用于删除数据，以及如何创建、修改和删除表（CREATE, ALTER, DROP TABLE）。 2. **数据类型**：讨论 DB2 支持的数据类型，如数值类型（INTEGER, DECIMAL）、字符类型（VARCHAR, CHAR）、日期时间类型（DATE, TIME, TIMESTAMP）等。 3. **索引与查询优化**：讲解如何创建和管理索引以提高查询性能，以及使用 EXPLAIN 分析查询计划，理解数据库如何执行 SQL 查询。 4. **事务处理**：介绍事务的概念，包括 ACID 属性（原子性、一致性、隔离性和持久性），以及 COMMIT 和 ROLLBACK 语句在确保数据一致性中的作用。 5. **视图与存储过程**：讲解如何创建和使用视图来抽象和简化复杂的查询，以及编写存储过程来封装复杂的业务逻辑。 6. **安全性与权限管理**：涉及用户账户管理，角色的使用，以及如何设置和控制访问权限（GRANT, REVOKE）。 7. **备份与恢复**：讨论数据库的备份策略，如完整备份、增量备份和差异备份，以及如何进行数据库恢复。 8. **性能监控与调优**：解释如何使用 DB2 提供的工具（如 db2pd, db2top）监控数据库性能，并提供调优建议。 9. **并发与锁机制**：介绍并发控制，如死锁的概念和解决方法，以及行级锁定与页级锁定的工作原理。 10. **分区与集群**：讲解 DB2 的分区特性，如范围分区、列表分区和散列分区，以及数据库集群的概念。这本书通过实例和“烹饪食谱”的方式，帮助读者理解和应用这些概念，以解决实际工作中遇到的问题。书中的例子通常经过精心设计，以展示最佳实践和潜在陷阱。作者也鼓励读者通过其个人网站获取最新版本，以获取最优化的示例和最新的技术更新。同时，他也提醒读者，尽管书中有许多实用建议，但任何技术文档都可能存在错误，因此在实际操作时应谨慎行事。

Graeme Birchall ©

16 Index of Concepts

Append Rows

To add (append) one set of rows to another set of rows, use a union (see page 239).

EMP_NM EMP_JB SELECT * ANSWER

+----------+ +--------+ FROM emp_nm =========

|ID|NAME | |ID|JOB | WHERE name < 'S' ID 2

|--|-------| |--|-----| UNION -- ------

|50|Hanes | +--------+ ORDER BY 1,2; 20 Pernal

+----------+ 50 Hanes

Figure 4, Union example

Assign Output Numbers

To assign line numbers to SQL output, use the ROW_NUMBER function (see page 100).

EMP_JB SELECT id

+--------+ ,job ANSWER

|ID|JOB | ,ROW_NUMBER() OVER(ORDER BY job) AS R ==========

|--|-----| FROM emp_jb ID JOB R

|10|Sales| ORDER BY job; -- ----- -

|20|Clerk| 20 Clerk 1

+--------+ 10 Sales 2

Figure 5, Assign row-numbers example

Assign Unique Key Numbers

The make each row inserted into a table automatically get a unique key value, use an identity

column, or a sequence, when creating the table (see page 257).

If-Then-Else Logic

To include if-then-else logical constructs in SQL stmts, use the CASE phrase (see page 43).

EMP_JB SELECT id ANSWER

+--------+ ,job ===============

|ID|JOB | ,CASE ID JOB STATUS

|--|-----| WHEN job = 'Sales' -- ----- ------

|10|Sales| THEN 'Fire' 10 Sales Fire

|20|Clerk| ELSE 'Demote' 20 Clerk Demote

+--------+ END AS STATUS

FROM emp_jb;

Figure 6, Case stmt example

Get Dependents

To get all of the dependents of some object, regardless of the degree of separation from the

parent to the child, use recursion (see page 287).

FAMILY WITH temp (persn, lvl) AS ANSWER

+-----------+ (SELECT parnt, 1 =========

|PARNT|CHILD| FROM family PERSN LVL

|-----|-----| WHERE parnt = 'Dad' ----- ---

|GrDad|Dad | UNION ALL Dad 1

|Dad |Dghtr| SELECT child, Lvl + 1 Dghtr 2

|Dghtr|GrSon| FROM temp, GrSon 3

|Dghtr|GrDtr| family GrDtr 3

+-----------+ WHERE persn = parnt)

SELECT *

FROM temp;

Figure 7, Recursion example

Convert String to Rows

To convert a (potentially large) set of values in a string (character field) into separate rows

(e.g. one row per word), use recursion (see page 380).

DB2 UDB V9.1 Cookbook ©

Quick Find 17

INPUT DATA Recursive SQL ANSWER

================= ============> ===========

"Some silly text" TEXT LINE#

----- -----

Some 1

silly 2

text 3

Figure 8, Convert string to rows

Be warned - in many cases, the code is not pretty.

Convert Rows to String

To convert a (potentially large) set of values that are in multiple rows into a single combined

field, use recursion (see page 381).

INPUT DATA Recursive SQL ANSWER

=========== ============> =================

TEXT LINE# "Some silly text"

----- -----

Some 1

silly 2

text 3

Figure 9, Convert rows to string

Fetch First "n" Rows

To fetch the first "n" matching rows, use the FETCH FIRST notation (see page 30).

EMP_NM SELECT * ANSWER

+----------+ FROM emp_nm =========

|ID|NAME | ORDER BY id DESC ID NAME

|--|-------| FETCH FIRST 2 ROWS ONLY; -- ------

|10|Sanders| 50 Hanes

|20|Pernal | 20 Pernal

|50|Hanes |

+----------+

Figure 10, Fetch first "n" rows example

Another way to do the same thing is to assign row numbers to the output, and then fetch those

rows where the row-number is less than "n" (see page 101).

Fetch Subsequent "n" Rows

To the fetch the "n" through "n + m" rows, first use the ROW_NUMBER function to assign

output numbers, then put the result in a nested-table-expression, and then fetch the rows with

desired numbers (see page 101).

Fetch Uncommitted Data

To retrieve data that may have been changed by another user, but which they have yet to

commit, use the WITH UR (Uncommitted Read) notation.

EMP_NM SELECT * ANSWER

+----------+ FROM emp_nm ==========

|ID|NAME | WHERE name like 'S%' ID NAME

|--|-------| WITH UR; -- -------

|10|Sanders| 10 Sanders

|20|Pernal |

|50|Hanes |

+----------+

Figure 11, Fetch WITH UR example

Using this option can result in one fetching data that is subsequently rolled back, and so was

never valid. Use with extreme care.

Graeme Birchall ©

18 Index of Concepts

Summarize Column Contents

Use a column function (see page 83) to summarize the contents of a column.

EMP_NM SELECT AVG(id) AS avg ANSWER

+----------+ ,MAX(name) AS maxn =================

|ID|NAME | ,COUNT(*) AS #rows AVG MAXN #ROWS

|--|-------| FROM emp_nm; --- ------- -----

|10|Sanders| 26 Sanders 3

|20|Pernal |

|50|Hanes |

+----------+

Figure 12, Column Functions example

Subtotals and Grand Totals

To obtain subtotals and grand-totals, use the ROLLUP or CUBE statements (see page 191).

SELECT job ANSWER

,dept =======================

,SUM(salary) AS sum_sal JOB DEPT SUM_SAL #EMP

,COUNT(*) AS #emps ----- ---- -------- ----

FROM staff Clerk 15 24766.70 2

WHERE dept < 30 Clerk 20 27757.35 2

AND salary < 20000 Clerk - 52524.05 4

AND job < 'S' Mgr 10 19260.25 1

GROUP BY ROLLUP(job, dept) Mgr 20 18357.50 1

ORDER BY job Mgr - 37617.75 2

,dept; - - 90141.80 6

Figure 13, Subtotal and Grand-total example

Enforcing Data Integrity

When a table is created, various DB2 features can be used to ensure that the data entered in

the table is always correct:

• Uniqueness (of values) can be enforced by creating unique indexes.

• Check constraints can be defined to limit the values that a column can have.

• Default values (for a column) can be defined - to be used when no value is provided.

• Identity columns (see page 257), can be defined to automatically generate unique nu-

meric values (e.g. invoice numbers) for all of the rows in a table. Sequences can do the

same thing over multiple tables.

• Referential integrity rules can created to enforce key relationships between tables.

• Triggers can be defined to enforce more complex integrity rules, and also to do things

(e.g. populate an audit trail) whenever data is changed.

See the DB2 manuals for documentation or page 319 for more information about the above.

Hide Complex SQL

One can create a view (see page 20) to hide complex SQL that is run repetitively. Be warned

however that doing so can make it significantly harder to tune the SQL - because some of the

logic will be in the user code, and some in the view definition.

Summary Table

Some queries that use a GROUP BY can be made to run much faster by defining a summary

table (see page 243) that DB2 automatically maintains. Subsequently, when the user writes

the original GROUP BY against the source-data table, the optimizer substitutes with a much

simpler (and faster) query against the summary table.

20 SQL Components

When using the DB2 Command Processor (batch) script, the default statement terminator can

be set using the "-tdx" option, where "x" is the value have chosen.

NOTE: See the section titled Special Character Usage on page 38 for notes on how to re-

fer to the statement delimiter in the SQL text.

Statement Delimiter

DB2 SQL does not come with a designated statement delimiter (terminator), though a semi-

colon is often used. A semi-colon cannot be used when writing a compound SQL statement

(see page 73) because that character is used to terminate the various sub-components of the

statement.

SQL Components

DB2 Objects

DB2 is a relational database that supports a variety of object types. In this section we shall

overview those items which one can obtain data from using SQL.

Table

A table is an organized set of columns and rows. The number, type, and relative position, of

the various columns in the table is recorded in the DB2 catalogue. The number of rows in the

table will fluctuate as data is inserted and deleted.

The CREATE TABLE statement is used to define a table. The following example will define

the EMPLOYEE table, which is found in the DB2 sample database.

CREATE TABLE employee

(empno CHARACTER (00006) NOT NULL

,firstnme VARCHAR (00012) NOT NULL

,midinit CHARACTER (00001) NOT NULL

,lastname VARCHAR (00015) NOT NULL

,workdept CHARACTER (00003)

,phoneno CHARACTER (00004)

,hiredate DATE

,job CHARACTER (00008)

,edlevel SMALLINT NOT NULL

,SEX CHARACTER (00001)

,birthdate DATE

,salary DECIMAL (00009,02)

,bonus DECIMAL (00009,02)

,comm DECIMAL (00009,02)

)

DATA CAPTURE NONE;

Figure 17, DB2 sample table - EMPLOYEE

View

A view is another way to look at the data in one or more tables (or other views). For example,

a user of the following view will only see those rows (and certain columns) in the EM-

PLOYEE table where the salary of a particular employee is greater than or equal to the aver-

age salary for their particular department.

剩余439页未读，继续阅读

zyw745293

粉丝: 10
资源: 19

DB2 UDB V9.1 SQL Cookbook - 最新版

SQL Cook Book

DB2 UDB 9.1 For AIX 安装指南

IBM DB2 UDB v9.1 AIX 安装全攻略

DB2 UDB v9.1在AIX系统上的详细安装教程

DB2_UDB_9.1_For_AIX_安装指南.doc

DB2UDB9.1 For AIX安装指南.

DB2 UDB V8.1 管理

DB2 UDB SQL入门

DB2 UDB V8.1管理学习笔记(一)

DB2 UDB 9.1 AIX 安装详解与步骤指南

最新资源