DB2开发者指南第四版：深入解析与实战技巧

需积分: 3 132 浏览量更新于2024-08-02 收藏 10.77MB PDF 举报

"(ebook-pdf) - sams - db2 developer's guide-fourth edition" 《DB2 Developer's Guide》第四版是由Craig S. Mullins编写的，ISBN号为0672318288，由Sams出版社在2000年出版，共1639页。这本书是关于DB2数据库系统的重要指南，尤其针对DB2版本6在OS/390平台上的新特性进行了深入探讨。无论是对于初学者还是经验丰富的DB2程序员来说，这本书都是不可或缺的参考资源。书中的内容不仅解析了DB2的新变化，还详尽地阐述了这些新特性如何影响DB2的使用。作者深入浅出地介绍了DB2的技术基础，同时讨论了实际操作中的性能优化和实施问题。此外，第四版还特别涵盖了与互联网相关的Java特性，使得读者能够了解如何利用DB2进行现代Web开发。目录如下： 1. 第一部分：SQL工具、技巧和策略 - 第1章：魔法词语 - 12页 - 第2章：数据操作指南 - 43页 - 第3章：使用DB2函数 - 70页 - 第4章：使用DB2用户自定义函数和数据类型 - 81页 - 第5章：数据定义指南 - 99页 - 第6章：使用DB2触发器确保数据完整性 - 159页 - 第7章：大型对象和对象/关系数据库 - 169页书中涵盖的主题广泛，从SQL的基本概念到高级特性，如数据操纵、函数应用、用户自定义函数和数据类型，再到数据定义规则和数据完整性维护。另外，关于大型对象（LOBs）和对象/关系数据库的章节揭示了DB2在处理复杂数据结构方面的灵活性。此书对于想要掌握DB2数据库开发的人来说具有极高的价值，它不仅提供了理论知识，还提供了实用技巧和最佳实践，帮助开发者更有效地管理和优化他们的DB2环境。通过阅读本书，读者可以深入了解DB2的核心机制，从而提升其在数据库设计、开发和管理中的专业能力。

- 16 -

Figure 1.2: Record-at-a-time processing versus set-at-a-time processing.

The power of SQL becomes increasingly evident when you compare SQL to COBOL (and flat files to

relational databases). Consider the following SQL statement:

UPDATE DSN8610.EMP

SET BONUS = 1000

WHERE EMPNO = '000340';

This single SQL statement accomplishes the same job as the following, comparably complex COBOL

psuedocode program:

Must set up IDENTIFICATION and

ENVIRONMENT DIVISIONS.

DATA DIVISION.

FILE-SECTION.

Must define input and output files.

WORKING-STORAGE SECTION.

Must declare all necessary variables.

01 EMPLOYEE-LAYOUT.

05 EMPNO PIC X(6).

05 FIRSTNME PIC X(12).

05 MIDINIT PIC X.

05 LASTNAME PIC X(15).

05 WORKDEPT PIC X(3).

05 PHONENO PIC X(4).

05 HIREDATE PIC X(10).

05 JOB PIC X(8).

05 EDLEVEL PIC S9(4) COMP.

05 SEX PIC X.

05 BIRTHDATE PIC X(10).

05 SALARY PIC S9(7)V99 COMP-3.

- 17 -

05 BONUS PIC S9(7)V99 COMP-3.

05 COMM PIC S9(7)V99 COMP-3.

77 EOF-FLAG PIC X VALUE 'N'.

PROCEDURE DIVISION.

MAIN-PARAGRAPH.

PERFORM OPEN-FILES.

PERFORM PROCESS-UPDATE

UNTIL EOF-FLAG = 'Y'.

PERFORM CLOSE-FILES.

STOP RUN.

OPEN-FILES.

OPEN INPUT INPUT-DATASET.

OPEN OUTPUT OUTPUT-DATASET.

PROCESS-UPDATE.

READ INPUT-DATASET

INTO EMPLOYEE-LAYOUT

AT END MOVE 'Y' TO EOF-FLAG.

IF EOF-FLAG = 'Y'

GO TO PROCESS-UPDATE-EXIT.

IF EMPNO = '000340'

MOVE +1000.00 TO BONUS.

WRITE OUTPUT-DATASET

FROM EMPLOYEE-LAYOUT.

PROCESS-UPDATE-EXIT.

EXIT.

CLOSE-FILES.

CLOSE INPUT-DATASET

OUTPUT-DATASET.

Indeed, many required lines in the COBOL program have been eliminated. Both the SQL statement and

the sample COBOL program change the bonus of employee number 000340 to $1,000.00. The SQL

example obviously is easier to code and maintain because of the limited size of the statement and the

set-level processing inherent in SQL. The COBOL example, though straightforward to a COBOL

programmer, is more difficult for most beginning users to code and understand.

Notes

Set-level processing differs from record-level processing because:

 All operations act on a complete set of rows.

 Fewer operations are necessary to retrieve the desired information.

 Data manipulation and retrieval instructions are simpler.

The set-level processing capabilities of SQL have an immediate and favorable impact on DB2's

capability to access and modify data. For example, a single SQL SELECT statement can produce an

entire report. With the assistance of a query-formatting tool, such as QMF, a general SQL processor,

such as DSNTEP2, or one of many Windows-based query tools, such as Crystal Reports or Forest &

Trees, hours of coding report programs can be eliminated.

In addition, all of the data-modification capabilities of DB2 act on a set of data, not row by row. So a

single UPDATE or DELETE statement can impact zero, one, or many rows. For example, consider the

following statement:

UPDATE DSN8510.PROJACT

SET PROJNO = '222222'

WHERE PROJNO = '111111';

- 18 -

This statement will change the PROJNO for every row where the PROJNO is currently set to the value

111111. The value will be changed whether there is only 1 row that applies or 1 million rows. If the

WHERE clause were not specified, every row would be changed to the value 222222, regardless of its

current value.

The set-level benefits of SQL provide great power to the SQL UPDATE and DELETE statements.

Because UPDATE and DELETE can act on sets of data, a single SQL statement can be used to update

or delete all rows meeting certain conditions. Great care must be taken always to provide the

appropriate WHERE clause or more data may be changed than desired.

Another benefit of the set-level processing capabilities of DB2 is that SQL can append rows to one table

based on data retrieved from another table. The following statement assigns every employee of

department E21 to activity 1 of project 222222.

INSERT

INTO DSN8610.EMPPROJACT

(SELECT EMPNO, '222222', 1, 0.10,

'1991-12-30', '1991-12-31'

FROM DSN8610.EMP

WHERE WORKDEPT = 'E21');

Types of SQL

SQL is many things to many people. The flexibility of SQL can make it difficult to categorize. Definitive SQL

types or categories, however, can be used to group the components of SQL.

Perhaps the most obvious categorization of SQL is based on its functionality. SQL can be used to

control, define, and manipulate data, as follows:

 The Data Control Language (DCL) provides the control statements that govern data

security with the GRANT and REVOKE verbs.

 The Data Definition Language (DDL) creates and maintains the physical data

structure with the CREATE, DROP, and ALTER SQL verbs.

 The Data Manipulation Language (DML) accesses and modifies data with the

SELECT, INSERT, DELETE, and UPDATE verbs.

Figure 1.3

depicts this breakdown of SQL statements by functionality.

Figure 1.3: SQL statement types.

Another way to categorize SQL is by execution type. SQL can be planned and executed as embedded

SQL in an application program, or it can be unplanned (ad hoc). The execution of planned SQL usually

is referred to as a production environment. The production environment is stable and well-defined, and

can be planned before the execution of the SQL. This approach to data processing is the traditional

one, and SQL fits into it nicely. Batch processing, on-line transaction processing (OLTP), canned

reporting, and administrative jobs typify the common production SQL environment. Typical applications

in the production environment include accounts receivable, order entry, and inventory control systems.

- 19 -

Ad hoc SQL, on the other hand, usually is undefined until an immediate need is identified. Upon

identification, an unplanned or, at best, hastily planned query is composed and executed. Decision-

support processing, data warehouse queries, on-line analytical processing (OLAP), power user queries,

new query testing, and critical unplanned reporting needs typify the common ad hoc SQL environment.

The ad hoc environment is just as critical, if not more so in some cases, to the ongoing business of the

organization as the production environment.

Another type of SQL can be thought of as existential SQL. SQL has an existence that relies on the

vehicle that maintains and supports it. SQL statements can exist either embedded in an application

program or as stand-alone entities.

Yet another way to categorize SQL is according to its dynamism. This fourth and final category is

probably the most difficult to define, and provides the greatest flexibility of all the categories. SQL can

be either static or dynamic. Static SQL is embedded in an application program written in a high-level

language. Dynamic SQL is either typed in at a terminal for real-time execution or constructed in an

application program's algorithms at runtime. This complex type of SQL is examined in greater detail

later in this chapter (and in Chapter 10, "Dynamic SQL Programming"

As you can see, categorization of SQL is not straightforward. Four categories define the nature of SQL.

Every SQL statement belongs to a component in every one of these categories. For example, a given

SQL statement can be used to manipulate data functionally in a planned production environment

embedded in a COBOL program coded as static SQL. Or, it could be used to control data security in an

ad hoc QMF environment as stand-alone dynamic SQL. At any rate, every SQL statement has four

defining features, as shown in the following groupings:

Functionality

DCL Control

of data

and

security

DDL Data

definition

DML Data

manipula

tion

Execution Type

Production Planned

Ad hoc Unplanned

Existence

Embedded Requires

program

Stand-alone No

program

used

Dynamism

Dynamic SQL Changeable

at runtime

Static SQL Unchangeable

at runtime

SQL Tools of the Trade

SQL, as a relational data sublanguage, must support certain basic functions. These functions, or tools of the

trade, implement the basic features of set-theory functions. You must have a basic understanding of the

capabilities of SQL before you can explore the deeper issues of efficiency, development environments,

performance, and tuning.

- 20 -

The basic functions of SQL are described in the following sections. Use these sections as a refresher

course; they are not meant to teach SQL syntax or provide in-depth coverage of its use.

Selection and Projection

The selection operation retrieves a specified subset of rows from a DB2 table. You use predicates in a

WHERE clause to specify the search criteria. The SQL implementation for selection is shown in the following

example:

SELECT *

FROM DSN8610.PROJ

WHERE DEPTNO = 'D01';

To retrieve all rows from the PROJ table, simply eliminate the WHERE clause from the statement.

The projection operation retrieves a specified subset of columns from a given DB2 table. A DB2 query

can provide a list of column names to limit the columns that are retrieved. Projection retrieves all of the

rows but only the specified columns. The following statement illustrates the SQL implementation for

projection:

SELECT DEPTNO, PROJNO, PROJNAME

FROM DSN8610.PROJ;

Simply, the selection operation determines which rows are retrieved, and the projection operation

determines which columns are retrieved.

The SQL SELECT statement is used to implement both the selection and projection operations. In most

cases, queries combine selection and projection to retrieve data. The following SQL statement

combines the selection and projection operations of the preceding two examples:

SELECT DEPTNO, PROJNO, PROJNAME

FROM DSN8610.PROJ

WHERE DEPTNO = 'D01';

Joins and Subqueries

The capability to query data from multiple tables using a single SQL statement is one of the nicer features of

DB2. The more tables involved in a SELECT statement, however, the more complex the SQL. Complex SQL

statements sometimes cause confusion. Therefore, a basic understanding of the multiple table capabilities of

SQL is essential for all users.

Joining Tables

The capability of DB2 to combine data from multiple tables is called joining. A standard join, also referred to

as an inner join, matches the data from two or more tables, based on the values of one or more columns in

each table. All matches are combined, creating a resulting row that is the concatenation of the columns from

each table where the specified columns match.

The most common method of joining DB2 tables requires SQL SELECT statements to have the

following:

 A string of table names separated by commas in the FROM clause

 A WHERE clause comparing the value of a column in one of the joined tables to the

value of a column in the other joined table (this is usually an equality comparison)

For example, to query employees and their department names, the EMP table is joined to the DEPT

table as follows:

SELECT EMPNO, LASTNAME, DEPTNO, DEPTNAME

FROM DSN8610.EMP,

DSN8610.DEPT

WHERE WORKDEPT = DEPTNO;

This method of coding joins, however, has confused many novice SQL programmers. No join verb need

be coded explicitly in the SQL SELECT statement to implement table joining. A join can be specified by

the presence of more than one table in the FROM clause of the SELECT statement. It is sometimes

difficult to grasp the concept of joining tables without a specific JOIN keyword being used in the SQL

join statement.

剩余1067页未读，继续阅读

dayang2001911

粉丝: 3
资源: 2

DB2开发者指南第四版：深入解析与实战技巧

Odoo 14 Development Cookbook - Fourth Edition(ebook-eglish) 代码

ESRI GIS. Getting Started with ArcGIS (ebook - pdf)

The-Ultimate-Linux-Newbie-Guide-eBook-Edition-January-2017.pdf

m2ebook-pdf

(eBook - PDF) Hugo Cornwall - The Hacker's Handbook.pdf

(ebook - PDF) - Networking - Cisco IP Routing Fundamentals.pdf

(ebook - pdf) - Differential Geometry in Physics.pdf

(ebook-pdf) the c++ programming language 3rd edition (stroustrap).rar

高级java笔试题-EBook-PDF:ITPDF电子书

(ebook - PDF) - Visual C++ Bible.zip

最新资源