Db2 简明教程

DB2 Quick Guide

DB2 Introduction

本章介绍了 DB2 的历史、版本、版本及其各自的功能。

This chapter describes history of DB2, its versions, editions and their respective features.

Overview

DB2 是 IBM 的一项数据库产品，也是一款关系数据库管理系统 (RDBMS)。DB2 旨在高效存储、分析和检索数据。DB2 产品在 XML 的支持下扩展了面向对象特征和非关系结构。

DB2 is a database product from IBM. It is a Relational Database Management System (RDBMS). DB2 is designed to store, analyze and retrieve the data efficiently. DB2 product is extended with the support of Object-Oriented features and non-relational structures with XML.

History

最初，IBM 为其特定平台开发了 DB2 产品。自 1990 年以来，IBM 决定开发通用数据库 (UDB) DB2 服务器，可以在任何权威操作系统（如 Linux、UNIX 和 Windows）上运行。

Initially, IBM had developed DB2 product for their specific platform. Since year 1990, it decided to develop a Universal Database (UDB) DB2 Server, which can run on any authoritative operating systems such as Linux, UNIX, and Windows.

Versions

对于 IBM DB2，UDB 当前版本 10.5 同时具有 BLU 加速的功能，其代号为“开普勒”。迄今为止，所有版本的 DB2 如下所示：

For IBM DB2, the UDB current version is 10.5 with the features of BLU Acceleration and its code name as 'Kepler'. All the versions of DB2 till today are listed below:

Version	Code Name
3.4	Cobweb
8.1, 8.2	Stinger
9.1	Viper
9.5	Viper 2
9.7	Cobra
9.8	It added features with Only PureScale
10.1	Galileo
10.5	Kepler

Data server editions and features

根据 DB2 必需功能的要求，组织可以选择适当的 DB2 版本。下表显示了 DB2 Server 版本及其功能：

Depending upon the requirement of needful features of DB2, the organizations select appropriate DB2 version. The following table shows DB2 server editions and their features:

Editions

Features

Advanced Enterprise Server Edition and Enterprise Server Edition (AESE / ESE)

It is designed for mid-size to large-size business organizations. Platform - Linux, UNIX, and Windows. Table partitioning High Availability Disaster Recovery (HARD) Materialized Query Table (MQTs) Multidimensional Clustering (MDC) Connection concentrator Pure XML Backup compression Homogeneous Federations

Workgroup Server Edition (WSE)

It is designed for Workgroup or mid-size business organizations. Using this WSE you can work with - High Availability Disaster Recovery (HARD) Online Reorganization Pure XML Web Service Federation support DB2 Homogeneous Federations Homogeneous SQL replication Backup compression

Express -C

It provides all the capabilities of DB2 at zero charge. It can run on any physical or virtual systems with any size of configuration.

Express Edition

It is designed for entry level and mid-size business organizations. It is full featured DB2 data server. It offers only limited services. This Edition comes with - Web Service Federations DB2 homogeneous federations Homogeneous SQL Replications Backup compression

Enterprise Developer Edition

It offers only single application developer. It is useful to design, build and prototype the applications for deployment on any of the IBM server. The software cannot be used for developing applications.

DB2 Server Installation

本章介绍了 DB2 服务器的安装步骤。

This chapter describes installation steps of DB2 server.

Introduction

您可以从 www.ibm.com 下载 DB2 服务器试用版或购买产品许可证。有两种独立的 DB2 服务器可供下载，具体取决于计划在其上执行操作的操作系统的规模。例如，如果您想下载适用于 32 位 Linux 或 UNIX 操作系统的 DB2 服务器，则需要下载 32 位 DB2 服务器。64 位 DB2 服务器也适用此规则。

You can download the DB2 Server trial version or purchase the product license from www.ibm.com. There are two separate DB2 servers available for downloading, depending upon the size of operating system, on which it is intended to execute. For example, if you want to download a DB2 server for 32bit Linux or UNIX operating system, then you need to download a 32 bit DB2 server. The same applies for 64bit DB2 server.

Hardware requirements

处理器：最低 Core 2Duo

Processor : Minimum Core 2Duo

RAM：最低 1GB

Ram : 1GB minimum

硬盘：最低 30GB

Hard disk : 30GB minimum

Software requirements

在安装 DB2 服务器之前，您的系统需要做好准备，其中需要包含所需的软件。对于 Linux，您需要安装“libstdc++6.0”。

Before installing the DB2 server, your system needs to get ready with the required software on it. For Linux, you need to install “libstdc++6.0”.

Checking system compatibility

在安装 DB2 服务器之前，您需要验证您的系统是否与 DB2 服务器兼容。若要确认兼容性，您需要在命令控制台上调用“db2prereqcheck”命令。

Before installing DB2 Server, you need to verify if your system is compatible with the DB2 server. For confirming the compatibility, you need to call 'db2prereqcheck' command on command console.

Installing DB2 on Linux operating system

打开终端并在控制台上使用“CD <DB2 安装文件夹>”命令设置 db2 安装映像文件夹路径。然后键入“./db2prereqcheck”命令，该命令会确认您的系统与 DB2 服务器的兼容性。

Open the Terminal and set the db2 installation image folder path on console using “CD <DB2 installation folder>” command. Then type “./db2prereqcheck” command, which confirms the compatibility of your system with DB2 server.

./db2prereqcheck

图 1 显示了 Linux 操作系统和硬件系统的兼容性要求。

Figure-1 shows the compatibility requirements of Linux operating system and hardware system.

按照给定的步骤在您的 Linux 系统上安装 DB2：

Follow the given steps for installing DB2 on your Linux system:

Open the terminal.
Login as root user.
Open DB2 Installation folder.
Type “./db2setup” and press Enter.

该进程将启动 DB2 服务器设置的执行。

This process will start execution of DB2 server setup.

在根终端键入“./db2setup”并按 Enter 键以启动 DB2 服务器的设置流程。

Type “./db2setup” and press Enter on root terminal to start setup process of DB2 Server.

执行此操作后，将出现“设置 LaunchPad”屏幕。[图 2]

On doing so, the “Set up Launch Pad” screen appears. [Figure-2]

在设置 LaunchPad 页面上，从左侧菜单中选择“安装产品”选项。选择“DB2 高级企业服务器版”选项。选择“新建安装”按钮。

On Setup Launch pad page, select “Install a Product” option from left side menu. Select option “DB2 Advanced Enterprise Server Edition”. Select “Install New” Button.

将出现一个名为“DB2 设置向导”的新框架。单击“下一步”。[图 3]

A new frame appears with name “DB2 setup wizard”. Click “Next”. [Figure-3]

将出现下一个屏幕并显示 DB2 许可协议。选择“我接受条款…”单击“下一步”。[图 4]

The next screen appears with DB2 license agreement. Select “I accept the terms…” Click “Next”. [Figure-4]

下一个屏幕会显示安装类型选项，该选项默认设置为“典型”。

Next screen comes up with offer of Installation type, which is set to “Typical” by default.

保持同样的选择。单击“下一步”。[图 5]

Keep the same selection. Click “Next”. [Figure-5]

下一个屏幕将显示安装操作。

The next screen appears with installation action.

选择“安装 DB2 高级企业服务器版…”

Select “Install DB2 Advanced Enterprise Server Edition…”

单击“下一步”。[图 6]

Click “Next”. [Figure-6]

在下一个屏幕上，设置程序会要求您选择安装目录。

On the next screen, the setup program asks for selection of installation directory.

保持默认，然后单击“下一步”。

Keep the default and click “Next”.

下一个屏幕会显示用户身份验证。输入“dasusr1”用户的密码。

The next screen comes up with the user authentication. Enter your password for “dasusr1” user.

（您的密码可以与用户名相同，这样便于记忆。）

(Your password can be identical to username so that it is convenient to remember.)

在以下屏幕中，设置会要求您创建 DB2 服务器实例。

On the following screen, the setup asks you for creation of DB2 Server Instance.

在此处，它会创建一个名为“db2ins1”的 DB2 实例。

Here, it is creating a DB2 instance with name “db2inst1”.

下一个屏幕询问您默认实例需要的分区数。

The next screen asks you the number of partitions you require for your default instance.

您可以选择“单一或多重”分区。

You have a choice of “single or Multiple” partitions.

选择“单一分区实例”。单击"下一步"。

Select “single partition instance”. Click “next”.

在下一个屏幕，该设置会要求您为正创建的 DB2 实例进行身份验证。

On the next screen, the setup asks you for authentication for DB2 instance being created.

此处，默认情况下，用户名被创建为“db2inst1”。您可以输入相同于用户名的密码。

Here, by default username is created as “db2inst1”. You can enter password same as username.

单击“下一步”。

Click “Next”.

在下一个屏幕，该设置要求输入“db2fenc”用户的身份验证信息。

On the next screen, the setup asks to enter authentication information for “db2fenc” user.

此处，您可以输入相同于用户名的密码。

Here, you can enter password same as username.

单击“下一步”。

Click “Next”.

在下一个屏幕，您可以选择“现在不为您的 db2 服务器设置发送通知”选项。

On the next screen, you can select “Do not setup your db2 server to send notifications at this time” option.

单击“下一步”。

Click ”Next”.

下一个屏幕向您显示有关 db2 设置的信息。

The next screen shows you the information about db2 setup.

单击“完成”。

Click “Finish”.

此时，DB2 安装过程已完成。

The DB2 Installation procedure is complete at this stage.

Verifying DB2 installation

您需要验证 DB2 服务器的安装，以进行实用性检查。完成 DB2 服务器安装后，注销当前用户模式并登录到“db2inst1”用户。在“db2inst1”用户环境中，您可以打开终端并执行以下命令来验证您的 db2 产品是否安装正确。

You need to verify the installation of DB2 server for its usefulness. On completing the DB2 Server installation, logout from current user mode and login to “db2inst1” user. In “db2inst1” user environment, you can open terminal and execute the following commands to verify if your db2 product is installed properly or not.

db2level

该命令显示当前实例中已安装 DB2 产品的当前版本和服务级别。

This command shows the current version and service level of the installed DB2 product for current instance.

Syntax:

db2level

Example:

db2level

Output:

DB21085I Instance "db2inst2" uses "64" bits
And DB2 code release "SQL10010" with level
identifier "0201010E". Informational tokens
are "DB2 v10.1.0.0", "s120403",
"LINUXAMD64101", and Fix Pack "0".
Product is installed at "/home/db2inst2/sqllib".

db2licm

此命令显示我们 DB2 产品的所有许可相关信息。

This command shows all the license related information of our DB2 Product.

Syntax:

db2licm <parameter>

Example:

db2licm -l

Output:

Product name:                     "DB2 Advanced Enterprise Server Edition"
License type:                     "Trial"
Expiry date:                      "10/02/2014"
Product identifier:               "db2aese"
Version information:              "10.1"
Product name:                     "DB2 Connect Server"
License type:                     "Trial"
Expiry date:                      "10/02/2014"
Product identifier:               "db2consv"
Version information:              "10.1"

Command Line Processor (CLP)

CLP 可以在三种模式中的一种中启动：

The CLP can be started in one of the three modes:

Command mode: In this mode, each command and SQL statement must be prefixed by “db2”. For example, query “db2 activate database sample”.
Interactive input mode: you can launch this mode by using the “db2” command. Here, you can pass SQL statements without prefix. For example, “activate database sample”.
Batch mode: Here, you need to create a script file, which contains all SQL queries of requirements and save the file with “.db2” extension. You can call this in command line using syntax “db2 –tf <filename.db2>”.

DB2 Instance

Introduction

实例是 DB2 数据库管理器的逻辑环境。使用实例，您可以管理数据库。根据我们的要求，您可以在一台物理机上创建多个实例。实例目录的内容为：

An Instance is a logical environment for DB2 Database Manager. Using instance, you can manage databases. Depending on our requirements, you can create multiple instances on one physical machine. The contents of Instance directory are:

Database Manager Configuration file
System Database Directory
Node Directory
Node Configuration File [db2nodes.cfg]
Debugging files, dump files

对于 DB2 数据库服务器，默认实例为“DB2”。不可能在创建实例目录后更改其位置。一个实例可以管理多个数据库。在一个实例中，每个数据库具有唯一名称、其自己的目录表集、配置文件、权限和特权。

For DB2 Database Server, the default instance is “DB2”. It is not possible to change the location of Instance directory after its creation. An instance can manage multiple databases. In an instance, each database has a unique name, its own set of catalog tables, configurations files, authorities and privileges.

Architecture of instance in DB2 product

Multiple instances

您可以在 Linux、UNIX 和 Windows 上的一个 DB2Server 中创建多个实例。可以在一台物理机上安装多个 DB2Server。

You can create multiple instances in one DB2Server on Linux, UNIX and Windows. It is possible to install multiple DB2Servers on a physical machine.

Creating instance on Linux

如果将 DB2 服务器安装为 root 用户，则可以在 Linux 和 UNIX 上创建多个实例。一个实例可以在 Linux 和 UNIX 上独立地同时运行。一次只能在一个数据库管理器实例内工作。

You can create multiple instances on Linux and UNIX if DB2 Server is installed as root user. An instance can run simultaneously on Linux and UNIX independently. You can work within a single instance of the database manager at a time.

实例文件夹包含数据库配置文件和文件夹。实例目录在 Windows 上的存储位置根据操作系统不同而异。

An Instance folder contains database configuration files and folders. The Instance directory is stored at different locations on Windows depending on the operating system versions.

Listing instances

以下命令用于列出实例：

The following command is used to list instances:

db2ilist

此命令列出系统上可用的所有实例。

This command lists all the instances that are available on a system.

Syntax:

db2ilist

Example: [查看在 DB2 副本中创建的实例数量]

Example:[To see how many instances are created in DB2 copy]

db2ilist

Output:

db2inst1
db2inst2
db2inst3

Instance environment commands

这些命令可用于在 DB2 CLI 中安排实例。

These commands are useful to work with arrangement of instance in the DB2 CLI.

Get instance

此命令显示当前正在运行的实例的详细信息。

This command shows details of the currently running instance.

Syntax:

db2 get instance

Example: [查看激活当前用户的当前实例]

Example:[To see the current instance which activated the current user]

db2 get instance

Output:

The current database manager instance is : db2inst1

Set instance

要在 DB2 UDB 上启动或停止某个实例的数据库管理器，可针对当前实例执行以下命令。

To start or stop the database manager of an instance on DB2 UDB, the following command is executed for the current instance.

Syntax:

set db2instance=<instance_name>

Example: [为当前用户安排 “db2inst1” 环境]

Example:[ To arrange the “db2inst1” environment to current user]

set db2instance=db2inst1

db2start

使用此命令，可以启动一个实例。执行此操作之前，您需要运行 “set instance”。

Using this command, you can start an instance. Before this, you need to run “set instance”.

Syntax:

db2start

Example: [启动一个实例]

Example:[To start an instance]

db2start

Output:

SQL1063N DB2START processing was successful

db2stop

使用此命令，可以停止一个正在运行的实例。

Using this command you can stop a running instance.

Syntax:

db2stop

Output:

SQL1064N DB2STOP processing was successful.

Creating an instance

让我们了解一下如何创建一个新实例。

Let us see how to create a new instance.

db2icrt

如果要创建一个新实例，您需要使用 root 登录。实例 ID 不是 root ID 或 root 名称。

If you want to create a new instance, you need to log in with root. Instance id is not a root id or a root name.

以下是创建新实例的步骤：

Here are the steps to create a new instance:

Step1 ：为实例创建一个操作系统用户。

Step1: Create an operating system user for instance.

Syntax:

useradd -u <ID> -g <group name> -m -d <user location> <user name>
-p <password>

Example ： [在组 “db2iadm1” 中为用户名为 “db2inst2” 的实例创建一个用户，密码为 “db2inst2”]

Example: [To create a user for instance with name ‘db2inst2’ in group ‘db2iadm1’ and password ‘db2inst2’]

useradd -u 1000 -g db2iadm1 -m -d /home/db2inst2 db2inst2 -p db2inst2

Step2 : 以 root 用户身份访问 DB2 实例目录，创建新实例。

Step2: Go to the DB2 instance directory in root user for create new instance.

Location:

cd /opt/ibm/db2/v10.1/instance

Step3 : 使用以下语法创建实例：

Step3: Create instance using the syntax below:

Syntax:

./db2icrt -s ese -u <inst id> <instance name>

Example : [使用 ESE（企业服务器版）特性以用户 db2inst2 创建新实例 db2inst2]

Example: [To create a new instance ‘db2inst2’ in user ‘db2inst2’ with the features of ‘ESE’ (Enterprise Server Edition)]

./db2icrt -s ese -u db2inst2 db2inst2

Output:

DBI1446I The db2icrt command is running, please wait.
 ….
 …..
DBI1070I Program db2icrt completed successfully.

Arranging communication port and host for an instance

编辑 /etc/services 文件并添加端口号。在给出的语法中，“inst_name”表示实例名称，“inst_port”表示实例的端口号。

Edit the /etc/services file and add the port number. In the syntax given below, ‘inst_name’ indicates the Instance name and ‘inst_port’ indicates port number of instance.

Syntax:

db2c_<inst name> <inst_port>/tcp

Example : [在 services 文件中为实例 db2inst2 添加变量 db2c_db2inst2 的端口号 50001/tcp]

Example: [Adding ‘50001/tcp’ port number for instance ‘db2inst2’ with variable ‘db2c_db2inst2’ in ‘services’ file]

db2c_db2inst2 50001/tcp

Syntax 1 : [使用服务名称更新数据库管理器配置。以下语法中的 svcename 表示实例服务名称，inst_name 表示实例名称]

Syntax 1: [Update Database Manager Configuration with service name. The following syntax ‘svcename’ indicates the instance service name and ‘inst_name’ indicates the instance name]

db2 update database manager configuration using svcename db2c_&<inst_name>

Example 1 : [使用值 db2c_db2inst2 为实例 db2inst2 变量 svcename 更新 DBM 配置]

Example 1: [Updating DBM Configuration with variable svcename with value ‘db2c_db2inst2’ for instance ‘db2inst2’

db2 update database manager configuration using svcename db2c_db2inst2

Output

DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.

Syntax 2 : 为当前实例设置 tcpip 通信协议

Syntax 2: set the “tcpip” communication protocol for the current instance

db2set DB2COMM=tcpip

Syntax 3 : [停止并启动当前实例以从数据库管理器配置获取更新的值]

Syntax 3: [Stopping and starting current instance to get updated values from database manager configuration]

db2stop
db2start

Updating an instance

你可以使用以下命令更新实例：

You can update an instance using following command:

db2iupdt

此命令用于在同一版本发布中更新实例。在执行该命令之前，你需要使用 db2stop 命令停止实例数据库管理器。在语法中，“inst_name”表示要更新为较新版本或已安装的 DB2 服务器版本的先前发布或已安装的 DB2 服务器实例名称。

This command is used to update the instance within the same version release. Before executing this command, you need to stop the instance database manager using “db2stop” command. The syntax below “inst_name” indicates the previous released or installed db2 server instance name, which you want to update to newer release or installed db2 server version.

Syntax 1 : 以正常模式更新实例

Syntax 1: To update an instance in normal mode

db2iupdt <inst_name>

Example1:

./db2iupdt db2inst2

Syntax 2 : 以调试模式更新实例

Syntax 2: To update an instance in debugging mode

db2iupdt -D <inst_name>

Example

db2iupdt -D db2inst2

Upgrading an instance

你可以将实例从以前的 DB2 副本版本升级到当前最新安装的 DB2 副本版本。

You can upgrade an instance from previous version of DB2 copy to current newly installed version of DB2 copy.

db2iupgrade

在 Linux 或 UNIX 系统上，该命令位于 DB2DIR/instance 目录中。在以下语法中，“inst_name”表示以前的版本 DB2 实例，“inst_username”表示当前安装版本 DB2 副本实例用户。

On Linux or UNIX system, this command is located in DB2DIR/instance directory. In the following syntaxes, “inst_name” indicates the previous version DB2 instance and “inst_username” indicates the current installed version DB2 copy instance user.

Syntax 2 :

Syntax 2:

db2iupgrade -d -k -u <inst_username> <inst_name>

Example :

Example:

db2iupgrade -d -k -u db2inst2 db2inst2

Command Parameters:

-d : 启用调试模式。

-d : Turns debugging mode on.

-k : 如果 DB2 副本（你在其中运行此命令）中支持预升级实例类型，将保留该类型。

-k : Keeps the pre-upgrade instance type if it is supported in the DB2 copy, from where you are running this command.

如果在 Linux 上对 DB2iupgrade 命令使用超级用户 (su)，你必须使用 “-” 选项发出 “su” 命令。

If you are using the Super User (su) on Linux for db2iupgrade command, you must issue the “su” command with the “-” option.

Dropping an instance

你可以放弃或删除 “db2icrt” 命令创建的实例。

You can drop or delete the instance, which was created by “db2icrt” command.

db2idrop

在 Linux 和 UNIX 操作系统中，此命令位于 DB2_installation_folder/instance 目录中。

On Linux and UNIX operating system, this command is located in the DB2_installation_folder/instance directory.

Syntax : [在以下语法中，“inst_username” 表示实例用户名，而“inst_name” 表示实例名称]

Syntax: [in the following syntax, ‘inst_username’ indicates username of instance and ‘inst_name’ indicates instance name]

db2idrop -u <inst_username> <inst_name>

Example : [要放弃 db2inst2]

Example: [To drop db2inst2]

./db2idrop -u db2inst2 db2inst2

Using other commands with instance

要找出我们现在正在处理哪个 DB2 实例的命令。

Command to find out which DB2 instance we are working on now.

Syntax 1 : [要检查数据库管理器激活的当前实例]

Syntax 1: [to check the current instance activated by database manager]

db2 get instance

Output:

The current database manager instance is:  db2inst1

Syntax 2 : [要查看具有操作系统位和发行版本的当前实例]

Syntax 2: [To see the current instance with operating bits and release version]

db2pd -inst | head -2

Example:

db2pd -inst | head -2

Output:

Instance db2inst1 uses 64 bits and DB2 code release SQL10010

Syntax 3 : [要检查当前工作实例的名称]

Syntax 3: [To check the name of currently working instance]

db2 select inst_name from sysibmadm.env_inst_info

Example:

db2 select inst_name from sysibmadm.env_inst_info

Output:

INST_NAME  --------------------------------------
db2inst1
1 record(s) selected.

Syntax : [要将新实例设置为默认值]

Syntax: [To set a new instance as default]

db2set db2instdef=<inst_name> -g

Example : [要将新创建的实例排列为默认实例]

Example: [To array newly created instance as a default instance]

db2set db2instdef=db2inst2 -g

DB2 Databases

本章介绍如何使用相关语法创建、激活和停用数据库。

This chapter describes creating, activating and deactivating the databases with the associated syntax.

Database architecture

数据库是表、模式、缓冲池、日志、存储组和表空间的集合，它们协同工作以高效处理数据库操作。

A database is a collection of Tables, Schemas, Bufferpools, Logs, Storage groups and Tablespaces working together to handle database operations efficiently.

Database directory

数据库目录是数据库的有序存储库。创建数据库时，数据库的所有详细信息都存储在数据库目录中，例如默认存储设备、配置文件和临时表列表等的详细信息。

Database directory is an organized repository of databases. When you create a database, all the details about database are stored in a database directory, such as details of default storage devices, configuration files, and temporary tables list etc.

分区全局目录在实例文件夹中创建。此目录包含与数据库相关的所有全局信息。此分区全局目录命名为 NODExxxx/SQLyyy，其中 xxxx 为数据分区号，yyy 为数据库令牌。

Partition global directory is created in the instance folder. This directory contains all global information related to the database. This partition global directory is named as NODExxxx/SQLyyy, where xxxx is the data partition number and yyy is the database token.

在分区全局目录中，会创建一个面向成员的目录。此目录包含本地数据库信息。面向成员的目录命名为 MEMBERxxxx，其中 xxxx 是成员号。DB2 Enterprise Server Edition 环境在单个成员上运行，并且仅有一个面向成员的目录。此面向成员的目录被唯一命名为 MEMBER0000。

In the partition-global directory, a member-specific directory is created. This directory contains local database information. The member-specific directory is named as MEMBERxxxx where xxxx is a member number. DB2 Enterprise Server Edition environment runs on a single member and has only one member specific directory. This member specific directory is uniquely named as MEMBER0000.

Partitioned global directory

目录位置：<实例>/NODExxx/SQLxxx

Directory Location : <instance>/NODExxx/SQLxxx

分区全局目录包含如下列出的与数据库相关联的文件。

The partition-global directory contains database related files as listed below.

Global deadlock write-to-file event monitoring files
Table space information files [SQLSPCS.1, SQLSPCS.2]
Storage group control files [SQLSGF.1, SQLSGF.2]
Temporary table space container files. [/storage path/ /T0000011/C000000.TMP/SQL00002.MEMBER0001.TDA]
Global Configuration file [SQLDBCONF]
History files [DB2RHIST.ASC, DB2RHIST.BAK, DB2TSCHG.HIS, DB2TSCHG.HIS]
Logging-related files [SQLOGCTL.GLFH.1, SQLOGCTL.GLFH.2]
Locking files [SQLINSLK, SQLTMPLK]
Automatic Storage containers

Member specific directory

目录位置：/NODExxxx/SQLxxxx/MEMBER0000

Directory location : /NODExxxx/SQLxxxx/MEMBER0000

此目录包含：

This directory contains:

Objects associated with databases
Buffer pool information files [SQLBP.1, SQLBP.2]
Local event monitoring files
Logging-related files [SQLOGCTL.LFH.1, SQLOGCTL.LFH.2, SQLOGMIR.LFH].
Local configuration files
Deadlocks event monitor file. The detailed deadlock events monitor files are stored in the database directory of the catalog node in case of ESE and partitioned database environment.

Creating database

您可以使用 CREATE DATABASE 命令在实例中创建数据库。所有数据库均使用在创建实例时创建的“IBMSTOGROUP”默认存储组创建。在 DB2 中，所有数据库表都存储在“表空间”中，后者使用各自的存储组。

You can create a database in instance using the “CREATE DATABASE” command. All databases are created with the default storage group “IBMSTOGROUP”, which is created at the time of creating an instance. In DB2, all the database tables are stored in “tablespace”, which use their respective storage groups.

数据库权限被自动设置为 PUBLIC [CREATETAB, BINDADD, CONNECT, IMPLICIT_SCHEMA 和 SELECT]，但是，如果存在 RESTRICTIVE 选项，则不会以 PUBLIC 授予权限。

The privileges for database are automatically set as PUBLIC [CREATETAB, BINDADD, CONNECT, IMPLICIT_SCHEMA, and SELECT], however, if the RESTRICTIVE option is present, the privileges are not granted as PUBLIC.

Creating non-restrictive database

此命令用于创建非限制性数据库。

This command is used to create a non-restrictive database.

Syntax : [要在创建新数据库。'database_name' 表示要创建的新数据库名称。]

Syntax: [To create a new Database. ‘database_name’ indicates a new database name, which you want to create.]

db2 create database <database name>

Example : [要创建名为 ‘one’ 的新非限制性数据库]

Example: [To create a new non-restrictive database with name ‘one’]

db2 create database one

Output:

DB20000I The CREATE DATABASE command completed successfully.

Creating restrictive database

调用此命令后将创建限制性数据库。

Restrictive database is created on invoking this command.

Syntax : [在下面的语法中，“db_name” 表示数据库名称。]

Syntax: [In the syntax below, “db_name” indicates the database name.]

db2 create database <db_name> restrictive

Example : [要创建名为 “two” 的新限制性数据库]

Example: [To create a new restrictive database with the name ‘two’]

db2 create database two restrictive

Creating database with different user defined location

在不同路径上使用“IBMSTOGROUP”默认存储组创建数据库。先前，您调用“create database”命令时没有指定用户定义的位置来存储或创建特定位置的数据库。若要使用用户定义的数据库位置创建数据库，请按照以下步骤操作：

Create a database with default storage group “IBMSTOGROUP” on different path. Earlier, you invoked the command “create database” without any user-defined location to store or create database at a particular location. To create the database using user- defined database location, the following procedure is followed:

Syntax : [在以下语法中，“db_name” 表示“database name” ， “data_location” 表示必须将数据存储在哪些文件夹中， “db_path_location” 表示 “data_location” 的驱动程序位置。]

Syntax: [In the syntax below, ‘db_name’ indicates the ‘database name’ and ‘data_location’ indicates where have to store data in folders and ‘db_path_location’ indicates driver location of ‘data_location’.]

db2 create database '<db_name>' on '<data location>' dbpath on '<db_path_location>'

Example : [要创建名为 “four” 的数据库，其中数据存储在 “data1” 中，并且此文件夹存储在 “dbpath1” 中]

Example: [To create database named ‘four’, where data is stored in ‘data1’ and this folder is stored in ‘dbpath1’]

db2 create database four on '/data1' dbpath on '/dbpath1'

Viewing local or system database directory files

执行此命令以查看当前实例中提供目录列表。

You execute this command to see the list of directories available in the current instance.

Syntax:

db2 list database directory

Example:

db2 list database directory

Output:

 System Database Directory
 Number of entries in the directory = 6
 Database 1 entry:
 Database alias                       = FOUR
 Database name                        = FOUR
 Local database directory             =
 /home/db2inst4/Desktop/dbpath
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
Database 2 entry:
Database alias                       = SIX
Database name                        = SIX
Local database directory             = /home/db2inst4
Database release level               = f.00
Comment                              =
Directory entry type                 = Indirect
Catalog database partition number    = 0
Alternate server hostname            =
Alternate server port number         =

Activating database

此命令启动特定数据库所需的所有服务，这样应用程序才可使用此数据库。

This command starts up all necessary services for a particular database so that the database is available for application.

Syntax :[“db_name” 表示数据库名称]

Syntax:[‘db_name’ indicates database name]

db2 activate db <db_name>

Example : [激活数据库“one”]

Example: [Activating the database ‘one’]

db2 activate db one

Deactivating database

使用此命令，您可以停止数据库服务。

Using this command, you can stop the database services.

Syntax:

db2 deactivate db <db_name>

Example : [停用数据库“one”]

Example: [To Deactivate database ‘one’]

db2 deactivate db one

Connecting to database

创建数据库后，要投入使用，需要连接或启动数据库。

After creating a database, to put it into use, you need to connect or start database.

Syntax:

db2 connect to <database name>

Example : [将数据库 one 连接到当前 CLI]

Example: [To Connect Database one to current CLI]

db2 connect to one

Output:

 Database Connection Information
 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = DB2INST4
 Local database alias   = ONE

Verifying if database is restrictive

要检查此数据库是否有限制，以下是语法：

To check if this database is restrictive or not, here is the syntax:

Syntax : [在以下语法中，“db” 表示数据库，“cfg” 表示配置，“db_name” 表示数据库名]

Syntax: [In the following syntax, ‘db’ indicates Database, ‘cfg’ indicates configuration, ‘db_name’ indicates database name]

db2 get db cfg for <db_name> | grep -i restrict

Example : [要检查数据库“one”是否有限制]

Example: [To check if ‘one’ database is restricted or not]

db2 get db cfg for one | grep -i restrict

Output:

Restrict access                       = NO

Configuring the database manager and the database

实例配置（Database Manager 配置）存储在名为“db2system”的文件中，而数据库相关配置则存储在名为“SQLDBCON”的文件中。这些文件无法直接编辑。您可以使用调用 API 的工具编辑这些文件。使用命令行处理器，您可以使用这些命令。

Instance configuration (Database manager configuration) is stored in a file named 'db2system' and the database related configuration is stored in a file named 'SQLDBCON'. These files cannot be edited directly. You can edit these files using tools which call API. Using the command line processor, you can use these commands.

Database Manager Configuration Parameters

Syntax : [获取实例数据库管理程序的信息]

Syntax: [To get the information of Instance Database manager]

db2 get database manager configuration

db2 get dbm cfg

Syntax : [更新实例数据库管理程序]

Syntax: [To update instance database manager]

db2 update database manager configuration

db2 update dbm cfg

Syntax : [重置先前的配置]

Syntax: [To reset previous configurations]

db2 reset database manager configuration

db2 reset dbm cfg

Database Configuration Parameters

Syntax : [获取数据库的信息]

Syntax: [To get the information of Database]

db2 get database configuration

db2 get db cfg

Syntax : [更新数据库配置]

Syntax: [To update the database configuration]

db2 update database configuration

db2 update db cfg

Syntax : [重置数据库配置中先前配置的值]

Syntax: [To reset the previously configured values in database configuration

db2 reset database configuration

db2 reset db cfg

Syntax : [检查当前活动数据库的大小]

Syntax: [To check the size of Current Active Database]

db2 "call get_dbsize_info(?,?,?,-1)"

Example : [确认当前激活数据库的大小]

Example: [To verify the size of Currently Activate Database]

db2 "call get_dbsize_info(?,?,?,-1)"

Output:

Value of output parameters
--------------------------
Parameter Name  : SNAPSHOTTIMESTAMP
Parameter Value : 2014-07-02-10.27.15.556775
Parameter Name  : DATABASESIZE
Parameter Value : 105795584
Parameter Name  : DATABASECAPACITY
Parameter Value : 396784705536
Return Status = 0

Estimating space required for database

要估计数据库的大小，必须考虑以下因素的贡献：

To estimate the size of a database, the contribution of the following factors must be considered:

System Catalog Tables
User Table Data
Long Field Data
Large Object (LOB) Data
Index Space
Temporary Work Space
XML data
Log file space
Local database directory
System files

Checking database authorities

可以使用以下语法来检查授予公共用户非限制性数据库的哪些数据库权限。

You can use the following syntax to check which database authorities are granted to PUBLIC on the non-restrictive database.

Step 1 ：使用认证用户 Id 和实例密码连接到数据库。

Step 1: connect to database with authentication user-id and password of instance.

Syntax ：[使用用户名和密码连接到数据库]

Syntax: [To connect to database with username and password]

db2 connect to <db_name> user <userid> using <password>

Example ：[使用用户 Id“db2inst4”和密码“db2inst4”连接“one”数据库]

Example: [To Connect “one” Database with the user id ‘db2inst4’ and password ‘db2inst4’]

db2 connect to one user db2inst4 using db2inst4

Output:

 Database Connection Information
 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = DB2INST4
 Local database alias   = ONE

Step2 ：验证数据库的权限。

Step2: To verify the authorities of database.

Syntax ：[下面的语法显示了当前数据库的权限服务的结果]

Syntax: [The syntax below shows the result of authority services for current database]

db2 "select substr(authority,1,25) as authority, d_user, d_group,
d_public, role_user, role_group, role_public,d_role from table(
sysproc.auth_list_authorities_for_authid ('public','g'))as t
order by authority"

Example:

db2 "select substr(authority,1,25) as authority, d_user, d_group,
d_public, role_user, role_group, role_public,d_role from table(
sysproc.auth_list_authorities_for_authid ('PUBLIC','G'))as t
order by authority"

Output:

AUTHORITY                 D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL                *      *       N        *         *          N           *
BINDADD                   *      *       Y        *         *          N           *
CONNECT                   *      *       Y        *         *          N           *
CREATETAB                 *      *       Y        *         *          N           *
CREATE_EXTERNAL_ROUTINE   *      *       N        *         *          N           *
CREATE_NOT_FENCED_ROUTINE *      *       N        *         *          N           *
CREATE_SECURE_OBJECT      *      *       N        *         *          N           *
DATAACCESS                *      *       N        *         *          N           *
DBADM                     *      *       N        *         *          N           *
EXPLAIN                   *      *       N        *         *          N           *
IMPLICIT_SCHEMA           *      *       Y        *         *          N           *
LOAD                      *      *       N        *         *          N           *
QUIESCE_CONNECT           *      *       N        *         *          N           *
SECADM                    *      *       N        *         *          N           *
SQLADM                    *      *       N        *         *          N           *
SYSADM                    *      *       *        *         *          *           *
SYSCTRL                   *      *       *        *         *          *           *
SYSMAINT                  *      *       *        *         *          *           *
SYSMON                    *      *       *        *         *          *           *
WLMADM                    *      *       N        *         *          N           *
20 record(s) selected.

Dropping Database

使用 Drop 命令，可以从实例数据库目录中移除我们的数据库。此命令可以删除其所有对象、表、空间、容器和关联文件。

Using the Drop command, you can remove our database from instance database directory. This command can delete all its objects, table, spaces, containers and associated files.

Syntax ：[从实例中删除任意数据库]

Syntax: [To drop any database from an instance]

db2 drop database <db_name>

Example ：[从实例中删除“six”数据库]

Example: [To drop ‘six’ database from instance]

db2  drop database six

Output:

DB20000I The DROP DATABASE command completed successfully

DB2 Bufferpools

本章为您介绍了数据库中的缓冲池。

This chapter introduces you to Bufferpools in the database.

Introduction

缓冲池是指数据库管理器分配的主存储空间部分。缓冲池的目的是从磁盘缓存表和索引数据。所有数据库都有自己的缓冲池。在新数据库创建时，会创建一个默认缓冲池。它被称为“IBMDEFAULTBP”。根据用户要求，可以创建多个缓冲池。在缓冲池中，数据库管理器将表行数据作为一个页面放置。此页面会一直驻留在缓冲池中，直到关闭数据库或用新数据写入该空间。缓冲池中已更新数据但未写入磁盘的页面称为“脏”页面。在缓冲池中更新的数据页面写入磁盘后，缓冲池就准备好接收其他数据。

The bufferpool is portion of a main memory space which is allocated by the database manager. The purpose of bufferpools is to cache table and index data from disk. All databases have their own bufferpools. A default bufferpool is created at the time of creation of new database. It called as “IBMDEFAULTBP”. Depending on the user requirements, it is possible to create a number of bufferpools. In the bufferpool, the database manager places the table row data as a page. This page stays in the bufferpool until the database is shutdown or until the space is written with new data. The pages in the bufferpool, which are updated with data but are not written onto the disk, are called “Dirty” pages. After the updated data pages in the bufferpool are written on the disk, the bufferpool is ready to take another data.

Relationship between tablespaces and bufferpools

每个表空间都与数据库中的特定缓冲池相关联。一个表空间与一个缓冲池相关联。缓冲池和表空间的大小必须相同。多个缓冲池允许您配置数据库使用的内存，以提高其整体性能。

Each table space is associated with a specific buffer pool in a database. One tablespace is associated with one bufferpool. The size of bufferpool and tablespace must be same. Multiple bufferpools allow you to configure the memory used by the database to increase its overall performance.

Bufferpool sizes

在使用“CREATE DATABASE”命令时设置缓冲池页面的大小。如果不指定页面大小，它将采用默认页面大小，即 4KB。创建缓冲池后，以后无法修改页面大小

The size of the bufferpool page is set when you use the “CREATE DATABASE” command. If you do not specify the page size, it will take default page size, which is 4KB. Once the bufferpool is created, it is not possible to modify the page size later

Listing the available bufferpools in the current database directory

Syntax : [下面的语法显示数据库中所有可用的缓冲池]

Syntax: [The syntax below shows all available bufferpools in database]

db2 select * from syscat.bufferpools

Example : [要查看当前数据库中的可用缓冲池]

Example: [To see available bufferpools in current database]

db2 select * from syscat.bufferpools

Output:

BPNAME      BUFFERPOOLID DBPGNAME   NPAGES      PAGESIZE    ESTORE
NUMBLOCKPAGES BLOCKSIZE   NGNAME
------------------------------------------------------------
IBMDEFAULTBP
 1 -
 -2        4096 N                  0           0 -

 1 record(s) selected.

Creating the bufferpool

要为数据库服务器创建新的缓冲池，您需要两个参数，即“缓冲池名称”和“页面大小”。执行以下查询以创建新的缓冲池。

To create a new bufferpool for database server, you need two parameters namely, “bufferpool name” and “size of page”. The following query is executed to create a new bufferpool.

语法：[在下面的语法中，“bp_name”表示缓冲池名称，“size”表示您需要为缓冲池声明的页面大小 (4K、8K、16K、32K)]

Syntax: [In the syntax below,‘bp_name’ indicates bufferpool name and ‘size’ indicates size for page you need to declare for bufferpools (4K,8K,16K,32K)]

db2 create bufferpool <bp_name> pagesize <size>

Example : [要创建一个名称为“bpnew”、大小为“8192”(8Kb) 的新缓冲池。]

Example: [To create a new bufferpool with name “bpnew” and size “8192”(8Kb).]

db2 create bufferpool bpnew pagesize 8192

Output

DB20000I The SQL command completed successfully.

Dropping the bufferpool

在删除缓冲池之前，需要检查是否有表空间已分配给它。

Before dropping the bufferpool, it is required to check if any tablespace is assigned to it.

Syntax : [要删除缓冲池]

Syntax: [To drop the bufferpool]

drop bufferpool <bp_name>

Example : [要删除名为“bpnew”的缓冲池]

Example: [To drop ‘bpnew’ named bufferpool]

db2 drop bufferpool bpnew

Output

DB20000I The SQL command completed successfully.

DB2 Tablespaces

本章将详细说明表空间

This chapter describes the tablespaces in detail

Introduction

表空间是一种存储结构，其中包含表、索引、大型对象和长数据。可用于将数据库中的数据组织到与数据在系统上的存储位置相关的逻辑存储组中。这些表空间存储在数据库分区组中

A table space is a storage structure, it contains tables, indexes, large objects, and long data. It can be used to organize data in a database into logical storage group which is related with where data stored on a system. This tablespaces are stored in database partition groups

Benefits of tablespaces in database

表空间在数据库中有以下多种好处：

The table spaces are beneficial in database in various ways given as follows:

Recoverability : 表空间让备份和恢复操作更方便。使用单个命令，您可以在表空间中备份或恢复所有数据库对象。

Recoverability: Tablespaces make backup and restore operations more convenient. Using a single command, you can make backup or restore all the database objects in tablespaces.

Automatic storage Management : 数据库管理器会根据需要创建和扩展容器。

Automatic storage Management: Database manager creates and extends containers depending on the needs.

Memory utilization : 单个缓冲池可以管理多个表空间。您可以为临时表空间指定它们自己的缓冲池，以提高诸如排序或联接之类的活动的性能。

Memory utilization: A single bufferpool can manage multiple tablespaces. You can assign temporary tablespaces to their own bufferpool to increase the performance of activities such as sorts or joins.

Container

表空间包含一个或多个容器。容器可以是目录名、设备名或文件名。在数据库中，单个表空间可以在同一物理存储设备上拥有多个容器。如果表空间是通过自动存储表空间选项创建的，则容器的创建和管理将由数据库管理器自动处理。如果未使用自动存储表空间选项创建，则您需要自己定义和管理容器。

Tablespaces contains one or more containers. A container can be a directory name, a device name, or a filename. In a database, a single tablespace can have several containers on the same physical storage device. If the tablespace is created with automatic storage tablespace option, the creation and management of containers is handled automatically by the database manager. If it is not created with automatic storage tablespace option, you need to define and manage the containers yourself.

Default tablespaces

当创建一个新数据库时，数据库管理器会为数据库创建一些默认表空间。此表空间用于存储用户和临时数据。每个数据库至少需要包含三个表空间，如下所示：

When you create a new database, the database manager creates some default tablespaces for database. These tablespace is used as a storage for user and temporary data. Each database must contain at least three tablespaces as given here:

Catalog tablespace : 其中包含数据库的系统目录表。它被命名为 SYSCATSPACE，并且无法删除。

Catalog tablespace: It contains system catalog tables for the database. It is named as SYSCATSPACE and it cannot be dropped.

User tablespace : 此表空间包含用户定义的表。在数据库中，我们有一个默认的用户表空间，名为 USERSPACE1。如果您在创建表时没有指定为此表指定用户定义表空间，则数据库管理器将为您选择默认用户表空间。

User tablespace: This tablespace contains user-defined tables. In a database, we have one default user tablespace, named as USERSPACE1. If you do not specify user-defined tablespace for a table at the time you create it, then the database manager chooses default user tablespace for you.

Temporary tablespace : 临时表空间包含临时表数据。此表空间包含系统临时表空间或用户临时表空间。

Temporary tablespace: A temporary tablespace contains temporary table data. This tablespace contains system temporary tablespaces or user temporary tablespace.

系统临时表空间在数据库管理器执行诸如排序或联接之类的操作时保存所需临时数据。数据库必须至少有一个系统临时表空间，它被命名为 TEMPSPACE1。它在创建数据库时创建。用户临时表空间保存来自表的临时数据。使用 DECLARE GLOBAL TEMPORARY TABLE 或 CREATE GLOBAL TEMPORARY TABLE 语句创建。在创建数据库时不会默认创建此临时表空间。

System temporary tablespace holds temporary data required by the database manager while performing operation such as sorts or joins. A database must have at least one system temporary tablespace and it is named as TEMPSPACE1. It is created at the time of creating the database. User temporary tablespace holds temporary data from tables. It is created with DECLARE GLOBAL TEMPORARY TABLE or CREATE GLOBAL TEMPORARY TABLE statement. This temporary tablespace is not created by default at the time of database creation.

Tablespaces and storage management:

根据您希望如何使用表空间可以使用不同的方法设置表空间。您可以设置操作系统来管理表空间分配、也可以让数据库管理器分配空间或者为您的数据选择自动分配表空间。

Tablespaces can be setup in different ways, depending on how you want to use them. You can setup the operating system to manage tablespace allocation, you can let the database manager allocate space or you can choose automatic allocation of tablespace for your data.

有以下三种类型的管理空间可用：

The following three types of managed spaces are available:

System Managed Space (SMS) : 操作系统的文件系统管理器分配和管理存储表的空间。按需分配存储空间。此模型包含表示数据库对象的多个文件。此表空间类型已在用户定义的表空间的 10.1 版中弃用，并未在目录和临时表空间中弃用。

System Managed Space (SMS): The operating system’s file system manager allocates and manages the space where the table is stored. Storage space is allocated on demand. This model consists of files representing database objects. This tablespace type has been deprecated in Version 10.1 for user-defined tablespaces, and it is not deprecated for catalog and temporary tablespaces.

Database Managed Space (DMS) : 数据库服务器控制存储空间。按您在创建 DMS 表空间时指定的容器定义在文件系统上预分配存储空间。从用户定义的表空间的 10.1 版修复包 1 中弃用，但并未在系统表空间和临时表空间中弃用。

Database Managed Space (DMS): The Database Server controls the storage space. Storage space is pre- allocated on the file system based on container definition that you specify when you create the DMS table space. It is deprecated from version 10.1 fix pack 1 for user-defined tablespaces, but it is not deprecated for system tablespace and temporary tablespace.

Automatic Storage Tablespace : 可以自动管理数据库服务器。数据库服务器根据数据库中的数据创建和扩展容器。使用自动存储管理，无需提供容器定义。数据库服务器会负责创建和扩展容器，以利用分配给数据库的存储。如果向存储组添加存储空间，则在现有容器达到其最大容量时会自动创建新的容器。如果您想立即使用新添加的存储，则可以重新平衡表空间。

Automatic Storage Tablespace: Database server can be managed automatically. Database server creates and extends containers depend on data on database. With automatic storage management, it is not required to provide container definitions. The database server looks after creating and extending containers to make use of the storage allocated to the database. If you add storage space to a storage group, new containers are automatically created when the existing container reach their maximum capacity. If you want to use the newly-added storage immediately, you can rebalance the tablespace.

Page, table and tablespace size:

临时 DMS 和自动存储表空间，您为数据库选择的页面大小确定表空间大小的最大限制。对于表 SMS 和临时自动存储表空间，页面大小限制表本身的大小。页面大小可以是 4kb、8kb、16kb 或 32kb。

Temporary DMS and automatic storage tablespaces, the page size you choose for your database determines the maximum limit for the tablespace size. For table SMS and temporary automatic storage tablespaces, the page size constrains the size of table itself. The page sizes can be 4kb, 8kb, 16kb or 32kb.

Tablespace type

4K page size limit

8K page size limit

16K page size limit

32K page size limit

DMS, non-temporary automatic storage tablespace regular

64G

128G

256G

512G

DMS, temporary DMS and non- temporary automatic storage table space large

1892G

16384G

32768G

65536G

DB2 Storagegroups

本章描述了数据库存储组。

This chapter describes the Database Storagegroups.

Introduction

存储数据库表或对象的一组存储路径是一个存储组。您可以将表空间分配给存储组。当您创建一个数据库时，所有表空间都采用默认存储组。数据库的默认存储组是“IBMSTOGROUP”。当您创建一个新的数据库时，如果在“CREATE DATABASE”命令的末尾传递了“AUTOMATIC STOGROUP NO”参数，则默认存储组为活动状态。数据库没有任何默认存储组。

A set of Storage paths to store database table or objects, is a storage group. You can assign the tablespaces to the storage group. When you create a database, all the tablespaces take default storagegorup. The default storage group for a database is ‘IBMSTOGROUP’. When you create a new database, the default storage group is active, if you pass the “AUTOMATIC STOGROUP NO” parameter at the end of “CREATE DATABASE” command. The database does not have any default storage groups.

Listing storagegroups

您可以列出数据库中的所有存储组。

You can list all the storagegroups in the database.

Syntax : [查看当前数据库中可用存储组的列表]

Syntax: [To see the list of available storagegroups in current database]

db2 select * from syscat.stogroups

Example : [查看当前数据库中可用存储组的列表]

Example: [To see the list of available storagegorups in current database]

db2 select * from syscat.stogroups

Creating a storagegroup

以下是数据库中创建存储组的语法：

Here is a syntax to create a storagegroup in the database:

Syntax : [创建一个新的 stogroup。“stogropu_name”表示新存储组的名称，“path”表示存储数据（表）的位置]

Syntax: [To create a new stogroup. The ‘stogropu_name’ indicates name of new storage group and ‘path’ indicates the location where data (tables) are stored]

db2 create stogroup  on ‘path’

Example : [在路径“data1”文件夹上创建一个新的 stogroup “stg1”]

Example: [To create a new stogroup ‘stg1’ on the path ‘data1’ folder]

db2 create stogroup stg1 on ‘/data1’

Output:

DB20000I The SQL command completed succesfully

Creating tablespace with stogroup

以下是使用 storegroup 创建表空间的方法：

Here is how you can create a tablespace with storegroup:

Syntax : [使用已存在的存储组创建一个新的表空间]

Syntax: [To create a new tablespace using existed storage group]

db2 create tablespace <tablespace_name>  using stogroup <stogroup_name>

Example : [使用已存在的存储组“stg1”创建一个名为“ts1”的新表空间]

Example: [To create a new tablespace named ‘ts1’ using existed storage group ‘stg1’]

db2 create tablespace ts1 using stogroup stg1

Output:

DB20000I The SQL command completed succesfully

Altering a storagegroup

您可以使用以下语法更改 storegroup 的位置：

You can alter the location of a storegroup by using following syntax:

Syntax : [将存储组从旧位置移动到新位置]

Syntax: [To shift a storage group from old location to new location]

db2 alter stogroup  add ‘location’, ‘location’

Example : [针对名为“sg1”的存储组，修改位置从旧位置到新位置的路径]

Example: [To modify location path from old location to new location for storage group named ‘sg1’]

db2 alter stogroup sg1 add ‘/path/data3’, ‘/path/data4’

Dropping folder path of storagegroup

在删除存储组的文件夹路径之前，您可以使用 alter 命令为存储组添加新位置。

Before dropping folder path of storagegroup, you can add new location for the storagegroup by using alter command.

Syntax : [从存储组位置中删除旧路径]

Syntax: [To drop old path from storage group location]

db2 alter stogroup  drop ‘/path’

Example : [从“stg1”中删除存储组位置]

Example: [To drop storage group location from ‘stg1’]

db2 alter stogroup stg1 drop ‘/path/data1’

Rebalancing a tablespace

当在数据库上进行事务且表空间变满时，为存储组或表空间创建新文件夹时需要重新平衡表空间。重新平衡使用新存储组更新数据库配置文件。

Rebalancing the tablespace is required when we create a new folder for storagegroup or tablespaces while the transactions are conducted on the database and the tablespace becomes full. Rebalancing updates database configuration files with new storagegroup.

Syntax : [将表空间从旧存储组路径重新平衡到新存储组]

Syntax: [To rebalance the tablespace from old storage group path to new storage group]

db2 alter tablspace <ts_name> rebalance

Example ：[重新配置]

Example: [To rebalance]

db2 alter tablespace ts1 rebalance

Renaming a storagegroup

Syntax ：[修改现有存储名称的名称]

Syntax: [To modify the name of existing storage name]

db2 rename stogroup <old_stg_name> to <new_stg_name>

Example ：[将存储组的名称从“sg1”修改为新名称“sgroup1”]

Example: [To modify the name of storage group from ‘sg1’ to new name ‘sgroup1’]

db2 rename stogroup sg1 to sgroup1

Dropping a storage group

Step 1 ：在删除任何存储组之前，您可以为表空间分配其他不同的存储组。

Step 1: Before dropping any storagegroup, you can assign some different storagegroup for tablespaces.

Syntax ：[为表空间指定另一个存储组]

Syntax: [To assign another storagegroup for table space.]

db2 alter tablspace <ts_name> using stogroup <another sto_group_name>

Example ：[为表空间“ts1”从一个旧的存储组更改为新的名为“sg2”的存储组]

Example: [To change from one old stogroup to new stogroup named ‘sg2’ for tablespace ‘ts1’]

db2 alter tablespace ts1 using stogroup sg2

Step 2:

Syntax ：[删除现有存储组]

Syntax: [To drop the existing stogroup]

db2 drop stogorup <stogroup_name>

Example ：[从数据库中删除存储组“stg1”]

Example: [To drop stogroup ‘stg1’ from database]

db2 drop stogroup stg1

DB2 Schemas

本章介绍和描述了模式的概念。

This chapter introduces and describes the concept of Schema.

Introduction

模式是在数据库中按照逻辑分类的已命名对象的集合。

A schema is a collection of named objects classified logically in the database.

在数据库中，您无法创建名称相同的多个数据库对象。为此，模式提供了一个组环境。您可以在数据库中创建多个模式，并且可以创建名称相同但模式组不同的多个数据库对象。

In a database, you cannot create multiple database objects with same name. To do so, the schema provides a group environment. You can create multiple schemas in a database and you can create multiple database objects with same name, with different schema groups.

模式可以包含表、函数、索引、表空间、过程、触发器等。例如，您可以为“employee”数据库创建两个名为“Professional”和“Personal”的不同模式。可以创建两个名称相同的不同表“Employee”。在此环境中，一个表包含专业信息，另一个表包含员工的个人信息。尽管有两个名称相同的表，但它们有两个不同的模式“Personal”和“Professional”。因此，用户可以在不遇到任何问题的情况下使用这两个表。当对表命名有约束时，此功能非常有用。

A schema can contain tables, functions, indices, tablespaces, procedures, triggers etc. For example, you create two different schemas named as “Professional” and “Personal” for an “employee” database. It is possible to make two different tables with the same name “Employee”. In this environment, one table has professional information and the other has personal information of employee. In spite of having two tables with the same name, they have two different schemas “Personal” and “Professional”. Hence, the user can work with both without encountering any problem. This feature is useful when there are constraints on the naming of tables.

让我们看看与模式相关的一些命令：

Let us see few commands related to Schema:

Getting currently active schema

Syntax:

db2 get schema

Example : [获取当前数据库模式]

Example: [To get current database schema]

db2 get schema

Setting another schema to current environment

Syntax:

db2 set schema=<schema_name>

Example : [将“schema1”组织到当前实例环境]

Example: [To arrange ‘schema1’ to current instance environment]

db2 set schema=schema1

Creating a new Schema

Syntax : [使用授权用户 ID 创建新的模式]

Syntax: [To create a new schema with authorized user id]

db2 create schema <schema_name> authroization <inst_user>

Example : [创建使用“db2inst2”授权的“schema1”模式]

Example: [To create “schema1” schema authorized with ‘db2inst2”]

db2 create schema schema1 authorization db2inst2

Exercise

让我们创建两个名称相同但模式不同的表。在此，您使用两个不同的模式创建雇员表，一个用于个人信息，另一个用于专业信息。

Let us create two different tables with same name but two different schemas. Here, you create employee table with two different schemas, one for personal and the other for professional information.

Step 1 : 创建两个架构。

Step 1: Create two schemas.

Schema 1 : [创建名为 professional 的架构]

Schema 1: [To create schema named professional]

db2 create schema professional authorization db2inst2

Schema 2 : [创建名为 personal 的架构]

Schema 2: [To create schema named personal]

db2 create schema personal authorization db2inst2

Step 2 : 创建两个表格，名称相同，用于存放员工详细信息

Step 2: Create two tables with the same name for Employee details

Table1 : professional.employee

Table1: professional.employee

db2 create table professional.employee(id number, name
varchar(20), profession varchar(20), join_date date,
salary number);

Table2 : personal.employee

Table2: personal.employee

db2 create table personal.employee(id number, name
varchar(20), d_birth date, phone bigint, address
varchar(200));

执行完这些步骤后，你将得到两个同名表格 'employee'，它们有两个不同的架构。

After executing these steps, you get two tables with same name ’employee’, with two different schemas.

DB2 Data Types

本章介绍了 DB2 中使用的各种数据类型。

This chapter introduces various data types used in DB2.

Introduction

在 DB2 数据库表格中，根据开发人员的要求，每列都有各自的数据类型。数据类型是指表格列中值的类型和范围。

In DB2 Database tables, each column has its own data type depending on developer’s requirements. The data type is said to be type and range of the values in columns of a table.

Built-in data types

Datetime*TIME*: It represents the time of the day in hours, minutes and seconds.TIMESTAMP: It represents seven values of the date and time in the form of year, month, day, hours, minutes, seconds and microseconds.DATE: It represents date of the day in three parts in the form of year, month and day.
*String*Character
CHAR (fixed length): Fixed length of Character strings. Varying length
VARCHAR: Varying length character strings.
CLOB: large object strings, you use this when a character string might exceed the limits of the VARCHAR data type. Graphic
GRAPHIC*Fixed length*: Fixed length graphic strings that contains double-byte charactersVarying length
VARGRAPHIC: Varying character graphic string that contains double bye characters.
DBCLOB: large object type Binary
BLOB (varying length): binary string in large object
BOOLEAN: In the form of 0 and 1.
*Signed numeric*Exact
Binary integer*SMALLINT : Using this you can insert small int values into columns*INTEGER [32BIT]: Using this you can insert large int values into columns*BIGINT [64BIT]*: Using this you can insert larger int values into columns
Decimal*DECIMAL (packed)*DECFLOAT (decimal floating point): Using this, you can insert decimal floating point numbersApproximate
Floating points*REAL* (single precision): Using this data type, you can insert single precision floating point numbers.DOUBLE (double precision): Using this data type, you can insert double precision floating point numbers.
eXtensible Mark-up Language*XML*: You can store XML data into this data type column.

DB2 Tables

表是由数据库管理器维护的逻辑结构。在表中，每个垂直块称为列（元组），每个水平块称为行（实体）。以列和行为形式存储的数据集合称为表。在表中，每一列都有不同的数据类型。表用于存储持久性数据。

Tables are logical structure maintained by Database manager. In a table each vertical block called as column (Tuple) and each horizontal block called as row (Entity). The collection of data stored in the form of columns and rows is known as a table. In tables, each column has different data type. Tables are used to store persistent data.

Type of tables

Base Tables: They hold persistent data. There are different kinds of base tables, including: Regular Tables: General purpose tables, Common tables with indexes are general purpose tables.Multidimensional Clustering Table (MDC): This type of table physically clustered on more than one key, and it used to maintain large database environments. These type of tables are not supported in DB2 pureScale.Insert time clustering Table (ITC): Similar to MDC tables, rows are clustered by the time they are inserted into the tables. They can be partitioned tables. They too, do not support pureScale environment.Range-Clustered tables Table (RCT): These type of tables provide fast and direct access of data. These are implemented as sequential clusters. Each record in the table has a record ID. These type of tables are used where the data is clustered tightly with one or more columns in the table. This type of tables also do not support in DB2 pureScale.Partitioned Tables: These type of tables are used in data organization schema, in which table data is divided into multiple storage objects. Data partitions can be added to, attached to and detached from a partitioned table. You can store multiple data partition from a table in one tablespace.Temporal Tables: History of a table in a database is stored in temporal tables such as details of the modifications done previously.
Temporary Tables: For temporary work of different database operations, you need to use temporary tables. The temporary tables (DGTTs) do not appear in system catalog, XML columns cannot be used in created temporary tables.
Materialized Query Tables: MQT can be used to improve the performance of queries. These types of tables are defined by a query, which is used to determine the data in the tables.

Creating Tables

以下语法创建表：

The following syntax creates table:

Syntax : [要创建新表]

Syntax: [To create a new table]

db2 create table <schema_name>.<table_name>
(column_name column_type....) in <tablespace_name>

Example : 我们在“professional”架构中创建一个表来存储“employee”详细信息。此表有“id、name、jobrole、joindate、salary”字段，此表数据将存储在表空间“ts1”中。

Example: We create a table to store “employee” details in the schema of “professional”. This table has “id, name, jobrole, joindate, salary” fields and this table data would be stored in tablespace “ts1”.

db2 create table professional.employee(id int, name
varchar(50),jobrole varchar(30),joindate date,
salary double) in ts1

Output:

DB20000I The SQL command completed successfully.

Listing table details

以下语法用于列出表详细信息：

The following syntax is used to list table details:

Syntax : [要查看使用架构创建的表的列表]

Syntax: [To see the list of tables created with schemas]

db2 select tabname, tabschema, tbspace from syscat.tables

Example : [要查看当前数据库中的表的列表]

Example: [To see the list of tables in the current database]

db2 select tabname, tabschema, tbspace from syscat.tables

Output:

TABNAME      TABSCHEMA     TBSPACE
------------ ------------- --------
EMPLOYEE     PROFESSIONAL    TS1


 1 record(s) selected.

Listing columns in a table

以下语法列出表格中的列：

The following syntax lists columns in a table:

Syntax : [要查看表的列和数据类型]

Syntax: [To see columns and data types of a table]

db2 describe table <table_name>

Example : [要查看表“employee”的列和数据类型]

Example: [To see the columns and data types of table ‘employee’]

db2 describe table professional.employee

Output:

             Data type                   Column
Column name  schema    Data type name    Length    Scale Nulls
------ ----- --------- ----------------- --------- ----- ------
ID           SYSIBM    INTEGER             4         0     Yes
NAME         SYSIBM    VARCHAR             50        0     Yes
JOBROLE      SYSIBM    VARCHAR             30        0     Yes
JOINDATE     SYSIBM    DATE                4         0     Yes
SALARY       SYSIBM    DOUBLE              8         0     Yes

  5 record(s) selected.

Hidden Columns

可以隐藏表中的整个列。如果调用“select * from”查询，则结果表中不会返回隐藏的列。将数据插入表时，没有列列表的“INSERT”语句不会期望任何隐式隐藏列的值。这些类型的列在物化查询表中高度引用。这些类型的列不支持创建临时表。

You can hide an entire column of a table. If you call “select * from” query, the hidden columns are not returned in the resulting table. When you insert data into a table, an “INSERT” statement without a column list does not expect values for any implicitly hidden columns. These type of columns are highly referenced in materialized query tables. These type of columns do not support to create temporary tables.

Creating table with hidden column

以下语法使用隐藏列创建表：

The following syntax creates table with hidden columns:

Syntax : [要创建带有隐藏列的表]

Syntax: [To create a table with hidden columns]

db2 create table <tab_name> (col1 datatype,col2 datatype
implicitly hidden)

Example : [要创建带有隐藏列“phone”的“customer”表]

Example: [To create a ‘customer’ table with hidden columns ‘phone’]

db2 create table professional.customer(custid integer not
null, fullname varchar(100), phone char(10)
implicitly hidden)

Inserting data values in table

以下语法在表中插入值：

The following syntax inserts values in the table:

Syntax : [要在表中插入值]

Syntax: [To insert values into a table]

db2 insert into <tab_name>(col1,col2,...)
 values(val1,val2,..)

Example : [要在“customer”表中插入值]

Example: [To insert values in ‘customer’ table]

db2 insert into professional.customer(custid, fullname, phone)
values(100,'ravi','9898989')


db2 insert into professional.customer(custid, fullname, phone)
values(101,'krathi','87996659')


db2 insert into professional.customer(custid, fullname, phone)
values(102,'gopal','768678687')

Output:

DB20000I  The SQL command completed successfully.

Retrieving values from table

以下语法从表中检索值：

The following syntax retrieves values from the table:

Syntax ：[从表中检索值]

Syntax: [To retrieve values form a table]

db2 select * from &lttab_name>

Example ：[从“customer”表中检索值]

Example: [To retrieve values from ‘customer’ table]

db2 select * from professional.customer

Output:

CUSTID      FULLNAME
----------- ------------------------
        100 ravi

        101 krathi

        102 gopal

  3 record(s) selected.

Retrieving values from a table including hidden columns

以下语法从选定列中检索值：

The following syntax retrieves values from selected columns:

Syntax ：[从表中检索选定的隐藏列值]

Syntax: [To retrieve selected hidden columns values from a table]

db2 select col1,col2,col3 from <tab_name>

Example ：[从表中检索选定列值结果]

Example: [To retrieve selected columns values result from a table]

db2 select custid,fullname,phone from professional.customer

Output:

CUSTID  FULLNAME    PHONE
------- ---------   ------------
100     ravi        9898989

101     krathi      87996659

102     gopal       768678687

  3 record(s) selected.

如果您想要查看隐藏列中的数据，您需要执行“DESCRIBE”命令。

If you want to see the data in the hidden columns, you need to execute “DESCRIBE” command.

Syntax ：

Syntax:

db2 describe table <table_name> show detail

Example:

db2 describe table professional.customer show detail

Output:

Column name     Data type schema     Data type name  Column
           column    Partitionkey  code
                                       Length   Scale    Nulls
number     sequence      page     Hidden      Default
--------------- -------------------- --------------- -------- ----
---- -------- ---------- ------------- -------- ----------- ------
---
CUSTID          SYSIBM               INTEGER         4        0
No       0          0         0        No
FULLNAME        SYSIBM               VARCHAR         100      0
Yes      1          0        1208     No

PHONE           SYSIBM               CHARACTER       10       0
Yes      2          0             1208     Implicitly

3 record(s) selected.

Altering the type of table columns

您可以使用此“alter”命令修改我们的表结构，如下所示：

You can modify our table structure using this “alter” command as follows:

Syntax ：

Syntax:

db2 alter table <tab_name> alter column <col_name> set data type <data_type>

Example: ：[将员工表的列“id”的数据类型从“int”修改为“bigint”]

Example: [To modify the data type for column “id” from “int” to “bigint” for employee table]

db2 alter table professional.employee alter column id set data type bigint

Output: ：

Output::

DB20000I The SQL command completed successfully.

Altering column name

您可以更改列名，如下所示：

You can change column name as shown below:

Syntax ：[将表的列名从旧名称修改为新名称]

Syntax: [To modify the column name from old name to new name of a table]

db2 alter table <tab_name> rename column <old_name> to <new_name>

Example: ：[将“customers”表中列名从“fullname”修改为“custname”]

Example: [To modify the column name from “fullname” to “custname” in “customers” table.]

db2 alter table professional.customer rename column fullname to custname

Dropping the tables

要删除任何表，您需要使用“DROP”命令，如下所示：

To delete any table, you need to use the “DROP” command as follows:

Syntax ：

Syntax:

db2 drop table <tab_name>

Example: ：[从数据库中删除客户表]

Example: [To drop customer table form database]

db2 drop table professional.customers

要删除表的整个层次结构（包括触发器和关系），您需要使用“DROP TABLE HIERARCHY”命令。

To delete the entire hierarchy of the table (including triggers and relation), you need to use “DROP TABLE HIERARCHY” command.

Syntax ：

Syntax:

db2 drop table hierarchy <tab_name>

Example: ：[删除表“customer”的整个层次结构]

Example: [To drop entire hierarchy of a table ‘customer’]

db2 drop table hierarchy professional.customers

DB2 Alias

本章描述了使用数据库对象的别名创建别名和在数据中使用数据库对象的别名。

This chapter describes the creation of alias and retrieving data using alias of database objects.

Introduction

别名是数据库对象的其他名称。它可用于引用数据库对象。您可以说，它是数据库对象的昵称。定义别名是为了使数据库对象的名称简短，从而减小查询大小并增加查询的可读性。

Alias is an alternative name for database objects. It can be used to reference the database object. You can say, it is a nick name for database objects. Alias are defined for the objects to make their name short, thereby reducing the query size and increasing readability of the query.

Creating database object aliases

您可以按如下方式创建数据库对象别名：

You can create database object alias as shown below:

Syntax ：

Syntax:

db2 create alias <alias_name> for <table_name>

Example : 为“professional.customer”表创建别名名称

Example: Creating alias name for table “professional.customer” table

db2 create alias pro_cust for professional.customer

如果您传递“SELECT * FROM PRO_CUST”或“SELECT * FROM PROFESSIONAL.CUSTOMER”，则数据库服务器将显示相同的结果。

If you pass “SELECT * FROM PRO_CUST” or “SELECT * FROM PROFESSIONAL.CUSTOMER” the database server will show the same result.

Syntax :[直接使用模式名称从表中获取值]

Syntax: [To retrieve values from a table directly with schema name]

db2 select * from <schema_name>.<table_name>

Example :[从 customer 表中获取值]

Example: [To retrieve values from table customer]

db2 select * from professional.customer

Output:

CUSTID  FULLNAME    PHONE
------- ---------   ------------
100     ravi        9898989
101     krathi      87996659
102     gopal       768678687

  3 record(s) selected.

Retrieving values using alias name of the table

您可以按如下方式使用数据库中的别名来获取值：

You can retrieve values from database using alias name as shown below:

Syntax :[通过调用表的别名从表中获取值]

Syntax: [To retrieve values from table by calling alias name of the table]

db2 select * from <alias_name>

Example :[使用别名从 customer 表中获取值]

Example: [To retrieve values from table customer using alias name]

db2 select * from pro_cust

Output:

CUSTID  FULLNAME    PHONE
------- ---------   ------------
100     ravi        9898989
101     krathi      87996659
102     gopal       768678687

  3 record(s) selected.

DB2 Constraints

本章描述了数据库中的各种约束。

This chapter describes various constraints in the database.

Introduction

为了强制执行数据库的完整性，定义了一组规则，称为约束。这些约束允许或禁止列中的值。

To enforce database integrity, a set of rules is defined, called constraints. The constraints either permit or prohibit the values in the columns.

在实时数据库活动中，应该根据某些限制添加数据。例如，在销售数据库中，销售 ID 或交易 ID 应当唯一。约束类型有：

In a Real time database activities, the data should be added with certain restrictions. For example, in a sales database, sales-id or transaction-id should be unique. The constraints types are:

NOT NULL
Unique
Primary key
Foreign Key
Check
Informational

约束仅与表相关。它们仅应用于特定表。它们在创建表时定义和应用于表。

Constraints are only associated with tables. They are applied to only particular tables. They are defined and applied to the table at the time of table creation.

Explanation of each constraint:

NOT NULL

这是禁止表中一个或多个列的空值规则。

It is a rule to prohibit null values from one or more columns within the table.

Syntax:

db2 create table <table_name>(col_name col_type not null,..)

Example : [要创建一个销售表，其中有四列（id、itemname、qty、price），在此向所有列添加“非空”约束以避免在表中形成任何空单元。]

Example: [To create a sales table, with four columns (id, itemname, qty, price) in this adding “not null” constraints to all columns to avoid forming any null cell in the table.]

db2 create table shopper.sales(id bigint not null, itemname
varchar(40) not null, qty int not null,price double not null)

Inserting NOT NULL values into table

你可以按如下方式在表中插入值：

You can insert values in the table as shown below:

Example: [错误查询]

Example: [ERRORoneous Query]

db2 insert into shopper.sales(id,itemname,qty)
values(1,'raagi',12)

Output: [正确查询]

Output: [Correct query]

DB21034E  The command was processed as an SQL statement because
it was not a

valid Command Line Processor command.  During SQL processing
it returned:

SQL0407N  Assignment of a NULL value to a NOT NULL column
"TBSPACEID=5,

TABLEID=4, COLNO=3" is not allowed.  SQLSTATE=23502

Example: [正确查询]

Example: [Correct query]

db2 insert into shopper.sales(id,itemname,qty,price)
values(1,'raagi',12, 120.00)

db2 insert into shopper.sales(id,itemname,qty,price)
values(1,'raagi',12, 120.00)

Output:

DB20000I The SQL command completed successfully.

Unique constraints

使用这些约束，你可以唯一地设置列的值。为此，在创建表时使用“非空”约束声明唯一约束。

Using these constraints, you can set values of columns uniquely. For this, the unique constraints are declared with “not null” constraint at the time of creating table.

Syntax:

db2 create table <tab_name>(<col> <col_type> not null unique, ...)

Example:

db2 create table shopper.sales1(id bigint not null unique,
itemname varchar(40) not null, qty int not null,price
double not null)

Inserting the values into table

Example: 插入具有唯一 ID 1、2、3 和 4 的四行。

Example: To insert four different rows with unique ids as 1, 2, 3 and 4.

db2 insert into shopper.sales1(id, itemname, qty, price)
values(1, 'sweet', 100, 89)

db2 insert into shopper.sales1(id, itemname, qty, price)
values(2, 'choco', 50, 60)

db2 insert into shopper.sales1(id, itemname, qty, price)
values(3, 'butter', 30, 40)

db2 insert into shopper.sales1(id, itemname, qty, price)
values(4, 'milk', 1000, 12)

Example: 插入一个具有“id”值 3 的新行

Example: To insert a new row with “id” value 3

db2 insert into shopper.sales1(id, itemname, qty, price)
values(3, 'cheese', 60, 80)

Output : 当你尝试插入一行已存在的 id 值时，它将显示此结果：

Output: when you try to insert a new row with existed id value it will show this result:

DB21034E  The command was processed as an SQL statement
because it was not a

valid Command Line Processor command.  During
SQL processing it returned:

SQL0803N  One or more values in the INSERT statement,
UPDATE statement, or foreign key update caused by a
DELETE statement are not valid because the primary key,
unique constraint or unique index identified by "1" constrains
table "SHOPPER.SALES1" from having duplicate values for the
index key. SQLSTATE=23505

Primary key

与唯一约束类似，你可以使用“主键”和“外键”约束来声明多个表之间的关系。

Similar to the unique constraints, you can use a “primary key” and a “foreign key” constraint to declare relationships between multiple tables.

Syntax:

db2 create table <tab_name>( ,.., primary
key ())

Example : 创建“salesboys”表，其中“sid”为主键

Example: To create ‘salesboys’ table with “sid” as a primary key

db2 create table shopper.salesboys(sid int not null, name
varchar(40) not null, salary double not null, constraint
pk_boy_id primary key (sid))

Foreign key

外键是一张表中的一组列，必须匹配另一张表中一行的至少一个主键。它是一种引用约束或引用完整性约束。它是一条关于一个或多个表中多列值的逻辑规则。它使表之间存在必需的关系。

A foreign key is a set of columns in a table which are required to match at least one primary key of a row in another table. It is a referential constraint or referential integrity constraint. It is a logical rule about values in multiple columns in one or more tables. It enables required relationship between the tables.

先前，你创建了一张名为“shopper.salesboys”的表。对于此表，主键为“sid”。现在你正在创建一张具有不同模式的销售人员个人详细信息的新表，名为“employee”和表名为“salesboys”。在这种情况下，“sid”是外键。

Earlier, you created a table named “shopper.salesboys” . For this table, the primary key is “sid”. Now you are creating a new table that has sales boy’s personal details with different schema named “employee” and table named “salesboys”. In this case, “sid” is the foreign key.

Syntax:

db2 create table <tab_name>(<col> <col_type>,constraint
<const_name> foreign key (<col_name>)
                  reference <ref_table> (<ref_col>)

Example : [要创建一个名为“salesboys”的表，其中包含外键列“sid”]

Example: [To create a table named ‘salesboys’ with foreign key column ‘sid’]

db2 create table employee.salesboys(
            sid int,
            name varchar(30) not null,
            phone int not null,
            constraint fk_boy_id
            foreign key (sid)
            references shopper.salesboys (sid)
			 on delete restrict
                       )

Example : [将值插入到主键表“shopper.salesboys”]

Example: [Inserting values into primary key table “shopper.salesboys”]

db2 insert into shopper.salesboys values(100,'raju',20000.00),
(101,'kiran',15000.00),
(102,'radha',10000.00),
(103,'wali',20000.00),
(104,'rayan',15000.00)

Example : [将值插入外键表 “employee.salesboys” [无错误]]

Example: [Inserting values into foreign key table “employee.salesboys” [without error]]

db2 insert into employee.salesboys values(100,'raju',98998976),
(101,'kiran',98911176),
(102,'radha',943245176),
(103,'wali',89857330),
(104,'rayan',89851130)

如果您输入了一个未知的数字（未存储在 “shopper.salesboys” 表中），这将向您显示 SQL 错误。

If you entered an unknown number, which is not stored in “shopper.salesboys” table, it will show you SQL error.

Example : [错误执行]

Example: [error execution]

db2 insert into employee.salesboys values(105,'rayan',89851130)

Output:

DB21034E  The command was processed as an SQL statement because it
was not a valid Command Line Processor command.  During SQL
processing it returned: SQL0530N  The insert or update value of
the FOREIGN KEY "EMPLOYEE.SALESBOYS.FK_BOY_ID" is not equal to any
value of the parent key of the parent table.  SQLSTATE=23503

Checking constraint

您需要使用此约束来为表中的特定列添加条件限制。

You need to use this constraint to add conditional restrictions for a specific column in a table.

Syntax:

db2 create table
 (
  primary key (),
  constraint  check (condition or condition)
 )

Example : [使用约束值创建 emp1 表]

Example: [To create emp1 table with constraints values]

db2 create table empl
 (id           smallint not null,
  name         varchar(9),
  dept         smallint check (dept between 10 and 100),
  job          char(5)  check (job in ('sales', 'mgr', 'clerk')),
  hiredate     date,
  salary       decimal(7,2),
  comm         decimal(7,2),
  primary key (id),
  constraint yearsal check (year(hiredate) > 1986 or salary > 40500)
 )

Inserting values

您可以按如下所示将值插入表中：

You can insert values into a table as shown below:

db2 insert into empl values (1,'lee', 15, 'mgr', '1985-01-01' ,
40000.00, 1000.00)

Dropping the constraint

以下列出删除各种约束的语法。

Let us see the syntaxes for dropping various constraints.

Dropping UNIQUE constraint

Syntax:

db2 alter table <tab_name> drop unique <const_name>

Dropping primary key

Syntax:

db2 alter table <tab_name> drop primary key

Dropping check constraint

Syntax:

db2 alter table <tab_name> drop check <check_const_name>

Dropping foreign key

Syntax:

db2 alter table <tab_name> drop foreigh key <foreign_key_name>

DB2 Indexes

本章涵盖索引简介、索引类型、索引创建和丢弃。

This chapter covers introduction to indexes, their types, creation and dropping.

Introduction

索引是一组指针，可以引用表中的行、MDC 或 ITC 表中的块、XML 存储对象中由一个或多个键的值逻辑排序的 XML 数据。在 DB2 表列上创建索引以加快查询的数据访问速度，高效地对数据进行聚集和分区。它还可以提高对视图的操作性能。具有唯一索引的表可以包含具有唯一键的行。根据表要求，你可以采用不同类型的索引。

Index is a set of pointers, which can refer to rows in a table, blocks in MDC or ITC tables, XML data in an XML storage object that are logically ordered by the values of one or more keys. It is created on DB2 table columns to speed up the data access for the queries, and to cluster and partition the data efficiently. It can also improve the performance of operation on the view. A table with a unique index can have rows with unique keys. Depending on the table requirements, you can take different types of indexes.

Types of indexes

Unique and Non-Unique indexes
Clustered and non-clustered indexes

Creating indexes

若要创建唯一索引，请使用以下语法：

For creating unique indexes, you use following syntax:

Syntax:

db2 create unique index <index_name> on
&lttable_name>(<unique_column>) include (<column_names..>)

Example ：为“shopper.sales1”表创建索引。

Example: To create index for “shopper.sales1” table.

db2 create unique index sales1_indx on
shopper.sales1(id) include (itemname)

Dropping indexes

若要丢弃索引，请使用以下语法：

For dropping the index, you use the following syntax:

Syntax:

db2 create unique index <index_name> on
&lttable_name>(<unique_column>) include (<column_names..>)

Example:

db2 drop index sales_index

DB2 Triggers

本章介绍触发器，它们的类型以及触发器的创建和放弃。

This chapter describes triggers, their types, creation and dropping of the triggers.

Introduction

触发器是一组针对数据库中指定表上的 INSERT、UPDATE 或 DELETE 操作进行响应而执行的 actions。触发器一次性存储在数据库中。它们处理数据管理。可以在多个应用程序之间访问和共享它们。使用触发器的优点在于，如果需要在应用程序中进行任何更改，则在触发器处进行，而不是更改访问触发器的每个应用程序。触发器易于维护，且它们迫使应用程序开发更快。触发器使用 SQL 语句 “CREATE TRIGGER” 定义。

A trigger is a set of actions, which are performed for responding to an INSERT, UPDATE or DELETE operation on a specified table in the database. Triggers are stored in the database at once. They handle governance of data. They can be accessed and shared among multiple applications. The advantage of using triggers is, if any change needs to be done in the application, it is done at the trigger; instead of changing each application that is accessing the trigger. Triggers are easy to maintain and they enforce faster application development. Triggers are defined using an SQL statement “CREATE TRIGGER”.

Types of triggers

有两种类型的触发器：

There are two types of triggers:

1. BEFORE triggers

它们在任何 SQL 操作之前执行。

They are executed before any SQL operation.

2. AFTER triggers

它们在 SQL 操作之后执行。

They are executed after any SQL operation.

Creating a BEFORE trigger

让我们看看如何创建触发器序列：

Let us see how to create a sequence of trigger:

Syntax:

db2 create sequence <seq_name>

Example ：为 shopper.sales1 表创建触发器序列

Example: Creating a sequence of triggers for table shopper.sales1

db2 create sequence sales1_seq as int start with 1 increment by 1

Syntax:

db2 create trigger <trigger_name> no cascade before insert on
<table_name> referencing new as <table_object> for each row set
<table_object>.<col_name>=nextval for <sequence_name>

Example ：为 shopper.sales1 表创建触发器以自动插入主键号

Example: Creating trigger for shopper.sales1 table to insert primary key numbers automatically

db2 create trigger sales1_trigger no cascade before insert on
shopper.sales1 referencing new as obj for each row set
obj.id=nextval for sales1_seq

现在尝试插入任何值：

Now try inserting any values:

db2 insert into shopper.sales1(itemname, qty, price)
values('bicks', 100, 24.00)

Retrieving values from table

让我们看看如何从表中检索值：

Let us see how to retrieve values from a table:

Syntax:

db2 select * from <tablename>

Example :

Example:

db2 select * from shopper.sales1

Output ：

Output:

  ID       ITEMNAME       QTY
-------  ------------   ----------
    3      bicks            100
    2      bread            100

  2 record(s) selected.

Creating an AFTER trigger

让我们看看如何创建 after 触发器：

Let us see how to create an after trigger:

Syntax:

db2 create trigger <trigger_name> no cascade before insert on
<table_name> referencing new as <table_object> for each row set
 <table_object>.<col_name>=nextval for <sequence_name>

Example: [插入和检索值]

Example: [To insert and retrieve the values]

db2 create trigger sales1_tri_after after insert on shopper.sales1
for each row mode db2sql begin atomic update shopper.sales1
set price=qty*price; end

Output:

//inseting values in shopper.sales1
db2 insert into shopper.sales1(itemname,qty,price)
values('chiken',100,124.00)
//output
ID    ITEMNAME       QTY         PRICE
----- -------------- ----------- -----------
    3 bicks          100         2400.00
    4 chiken         100         12400.00
    2 bread          100         2400.00

	3 record(s) selected.

Dropping a trigger

以下是如何删除数据库触发器：

Here is how a database trigger is dropped:

Syntax:

db2 drop trigger <trigger_name>

Example:

db2 drop trigger slaes1_trigger

DB2 - Sequences

本章将介绍序列、序列创建、序列查看和序列删除的概念。

This chapter introduces you to the concept of sequence, creation of sequence, viewing the sequence, and dropping them.

Introduction

序列是一种软件函数，它在一定范围内生成升序或降序整数，以生成主键并在表之间协调其他键。您可以使用序列来利用整数，比如，employee_id 或 transaction_id。序列可以支持 SMALLINT、BIGINT、INTEGER 和 DECIMAL 数据类型。序列可以在多个应用程序中共享。无论事务如何，序列都会递增或递减。

A sequence is a software function that generates integer numbers in either ascending or descending order, within a definite range, to generate primary key and coordinate other keys among the table. You use sequence for availing integer numbers say, for employee_id or transaction_id. A sequence can support SMALLINT, BIGINT, INTEGER, and DECIMAL data types. A sequence can be shared among multiple applications. A sequence is incremented or decremented irrespective of transactions.

通过 CREATE SEQUENCE 语句创建序列。

A sequence is created by CREATE SEQUENCE statement.

Types of Sequences

有两种类型的序列：

There are two type of sequences available:

NEXTVAL: It returns an incremented value for a sequence number.
PREVIOUS VALUE: It returns recently generated value.

Parameters of sequences

以下参数用于序列：

The following parameters are used for sequences:

Data type :这是返回递增值的数据类型。（SMALLINT、BIGINT、INTEGER、NUMBER、DOUBLE）

Data type: This is the data type of the returned incremented value. (SMALLINT, BIGINT, INTEGER, NUMBER, DOUBLE)

START WITH ：序列从该参考值开始。

START WITH: The reference value, with which the sequence starts.

MINVALUE ：序列开始使用的最小值。

MINVALUE: A minimum value for a sequence to start with.

MAXVALUE ：序列的最大值。

MAXVALUE: A maximum value for a sequence.

INCREMENT BY ：序列递增的步长值。

INCREMENT BY: step value by which a sequence is incremented.

Sequence cycling ：CYCLE 子句导致重复生成序列。通过引用已由前序序列生成并存储到数据库中的返回的值来进行序列生成。

Sequence cycling: the CYCLE clause causes generation of the sequence repeatedly. The sequence generation is conducted by referring the returned value, which is stored into the database by previous sequence generation.

Creating a sequence

你可以使用以下语法创建序列：

You can create sequence using the following syntax:

Syntax:

db2 create sequence <seq_name>

Example ：[用从 1 开始的递增值创建具有“sales1_seq”名称的新序列]

Example: [To create a new sequence with the name ‘sales1_seq’ and increasing values from 1]

db2 create sequence sales1_seq as int start
with 1 increment by 1

Viewing the sequences

你可以使用以下语法查看序列：

You can view a sequence using the syntax given below:

Syntax:

db2 value <previous/next> value for <seq_name>

Example ：[查看序列“sales1_seq”中先前更新过的值的列表]

Example: [To see list of previous updated value in sequence ‘sales1_seq’]

db2 values previous value for sales1_seq

Output:

 1
-----------
  4
  1 record(s) selected.

Dropping the sequence

若要移除序列，你需要使用 “DROP SEQUENCE” 命令。以下是如何操作：

To remove the sequence, you need to use the “DROP SEQUENCE ” command. Here is how you do it:

Syntax:

db2 drop sequence <seq_name>>

Example ：[从数据库中丢弃序列“sales1_seq”]

Example: [To drop sequence ‘sales1_seq’ from database]

db2 drop sequence sales1_seq

Output:

 DB20000I The SQL command completed successfully.

DB2 Views

本章介绍了视图的介绍、创建、修改和删除视图。

This chapter describes introduction of views, creating, modifying and dropping the views.

Introduction

视图是表示存储在表中的数据的另一种方式。它不是一个真实表，并且没有永久存储。视图提供了一种查看一个或多个表中的数据的方法。它是针对结果表的命名规范。

A view is an alternative way of representing the data stored in the tables. It is not an actual table and it does not have any permanent storage. View provides a way of looking at the data in one or more tables. It is a named specification of a result table.

Creating a view

可以使用以下语法创建视图：

You can create a view using the following syntax:

Syntax:

db2 create view <view_name> (<col_name>,
<col_name1...) as select <cols>..
from <table_name>

Example ：为 shopper.sales1 表创建视图

Example: Creating view for shopper.sales1 table

db2 create view view_sales1(id, itemname, qty, price)
as select id, itemname, qty, price from
shopper.sales1

Modifying a view

可以使用以下语法修改视图：

You can modify a view using the following syntax:

Syntax:

db2 alter view <view_name> alter <col_name>
add scope <table_or_view_name>

Example ：[向现有视图“view_sales1”添加新表列]

Example: [To add new table column to existing view ‘view_sales1’]

db2 alter view view_sales1 alter id add
scope shopper.sales1

Dropping the view

可以使用以下语法删除视图：

You can drop a view using the following syntax:

Syntax:

db2 drop view <view_name>

Example:

db2 drop view sales1_view

DB2 with XML

本章介绍在 DB2 中使用 XML。

This chapter describes use of XML with DB2.

Introduction

有了 PureXML 功能，你可以在数据库表的列中存储格式良好的 XML 文档。这些列具有 XML 数据库。将 XML 数据存储在 XML 列中，可以使其以本机分层形式保持数据。DB2 数据库服务器功能可以访问和管理已存储的 XML 数据。将 XML 数据存储在其本机分层形式中能够高效地搜索、检索和更新 XML。若要更新 XML 数据中的值，你需要使用 XQuery、SQL 或两者的组合。

PureXML feature allows you to store well-formed XML documents in columns of database tables. Those columns have XML database. Data is kept in its native hierarchical form by storing XML data in XML column. The stored XML data can be accessed and managed by DB2 database server functionality. The storage of XML data in its native hierarchical form enables efficient search, retrieval, and update of XML. To update a value in XML data, you need to use XQuery, SQL or combination of both.

Creating a database and table for storing XML data

通过发布以下语法创建一个数据库：

Create a database by issuing the following syntax:

Syntax:

db2 create database xmldb

默认情况下，数据库使用 UTF-8 (UNICODE) 编码集。激活数据库并连接到它：

By default, databases use UTF-8 (UNICODE) code set. Activate the database and connect to it:

Syntax:

db2 activate db <db_name>
db2 connect to <db_name>

Example:

db2 activate db xmldb
db2 connect to xmldb

创建一个格式良好的 XML 文件，并创建一个数据类型为“XML”的表。必须将包含在双引号中的 SQL 查询传递到 XML 语法。

Create a well-formed XML file and create a table with data type of the column as ‘XML’. It is mandatory to pass the SQL query containing XML syntax within double quotation marks.

Syntax:

db2 “create table <schema>.<table>(col <datatype>,
col <xml datatype>)”

Example:

db2 "create table shope.books(id bigint not null
primary key, book XML)"

使用 SQL 语句“INSERT”将 XML 值插入到表中，格式良好的 XML 文档被插入到 XML 类型列中。

Insert xml values into table, well-formed XML documents are inserted into XML type column using SQL statement ‘INSERT’.

Syntax:

db2 “insert into <table_name> values(value1, value2)”

Example:

db2 "insert into shope.books values(1000, '<catalog>
<book>

<author> Gambardella Matthew</author>
<title>XML Developers Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating application
with XML</description>
</book>

</catalog>')"

Updating XML data in a table

您可以使用以下语法更新表中的 XML 数据：

You can update XML data in a table by using the following syntax:

Syntax:

db2 “update <table_name> set <column>=<value> where
<column>=<value>”

Example:

db2 "update shope.books set book='<catalog>

<book>
<author> Gambardella, Matthew</author>
<title>XML Developers Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth XML</description>

</book>

</catalog>' where id=1000"

DB2 - Backup and Recovery

本章介绍数据库的备份和恢复方法。

This chapter describes backup and restore methods of database.

Introduction

备份和恢复方法旨在保证我们信息的安全性。在命令行界面 (CLI) 或图形用户界面 (GUI) 中使用备份和恢复实用程序，您可以在 DB2 UDB 中备份或恢复数据库的数据。

Backup and recovery methods are designed to keep our information safe. In Command Line Interface (CLI) or Graphical User Interface (GUI) using backup and recovery utilities you can take backup or restore the data of databases in DB2 UDB.

Logging

日志文件包含错误日志，这些错误日志用于从应用程序错误中恢复。日志保留数据库中更改的记录。如下所述，日志记录有两种类型：

Log files consist of error logs, which are used to recover from application errors. The logs keep the record of changes in the database. There are two types of logging as described below:

Circular logging

这是一种方法，即在需要分配新事务日志文件时，覆盖旧的事务日志，从而擦除日志文件序列并重新使用它们。您只允许在脱机模式下进行完全备份。即，数据库必须脱机才能进行完全备份。

It is a method where the old transaction logs are overwritten when there is a need to allocate a new transaction log file, thus erasing the sequences of log files and reusing them. You are permitted to take only full back-up in offline mode. i.e., the database must be offline to take the full backup.

Archive logging

此模式支持使用称为向前恢复的日志文件进行在线备份和数据库恢复。可以通过将 logretain 或 userexit 设置为 ON，将备份模式从循环更改为归档。对于归档日志记录，备份设置数据库需要一个对 DB2 进程可写的目录。

This mode supports for Online Backup and database recovery using log files called roll forward recovery. The mode of backup can be changed from circular to archive by setting logretain or userexit to ON. For archive logging, backup setting database require a directory that is writable for DB2 process.

Backup

使用 Backup 命令，您可以复制整个数据库。此备份副本包括数据库系统文件、数据文件、日志文件、控制信息等。

Using Backup command you can take copy of entire database. This backup copy includes database system files, data files, log files, control information and so on.

可以同时在脱机和联机时进行备份。

You can take backup while working offline as well as online.

Offline backup

Syntax: [列出活动应用程序/数据库]

Syntax: [To list the active applications/databases]

db2 list application

Output:

Auth Id  Application    Appl.      Application Id
DB       # of
         Name           Handle
Name    Agents
-------- -------------- ---------- ---------------------
----------------------------------------- -------- -----
DB2INST1 db2bp          39
*LOCAL.db2inst1.140722043938
ONE      1

Syntax: [强制应用程序使用应用程序。已处理 ID]

Syntax: [To force application using app. Handled id]

db2 "force application (39)"

Output:

DB20000I  The FORCE APPLICATION command completed
successfully.

DB21024I  This command is asynchronous and may not
be effective immediately.

Syntax: [要终止数据库连接]

Syntax: [To terminate Database Connection]

db2 terminate

Syntax: [要停用数据库]

Syntax: [To deactivate Database]

db2 deactivate database one

Syntax: [要获取备份文件]

Syntax: [To take the backup file]

db2 backup database <db_name> to <location>

Example:

db2 backup database one to /home/db2inst1/

Output:

Backup successful. The timestamp for this backup image is :
20140722105345

Online backup

首先，您需要将模式从 Circular logging 更改为 Archive Logging 。

To start, you need to change the mode from Circular logging to Archive Logging.

Syntax: [要检查数据库是否使用循环记录还是存档记录]

Syntax: [To check if the database is using circular or archive logging]

db2 get db cfg for one | grep LOGARCH

Output:

First log archive method (LOGARCHMETH1) = OFF
 Archive compression for logarchmeth1  (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1              (LOGARCHOPT1) =
 Second log archive method             (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2  (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2              (LOGARCHOPT2) =

在上述输出中，高亮显示的值 [logarchmeth1 和 logarchmeth2] 处于关闭模式，这意味着当前数据库处于“循环记录”模式。如果您需要使用“存档记录”模式，则需要更改或在配置文件中存在的变量 logarchmeth1 和 logarchmeth2 中添加路径。

In the above output, the highlighted values are [logarchmeth1 and logarchmeth2] in off mode, which implies that the current database in “CIRCULLAR LOGGING” mode. If you need to work with ‘ARCHIVE LOGGING’ mode, you need to change or add path in the variables logarchmeth1 and logarchmeth2 present in the configuration file.

Updating logarchmeth1 with required archive directory

Syntax: [要创建目录]

Syntax: [To make directories]

mkdir backup
mkdir backup/ArchiveDest

Syntax: [要为文件夹提供用户权限]

Syntax: [To provide user permissions for folder]

chown db2inst1:db2iadm1 backup/ArchiveDest

Syntax: [要更新配置 LOGARCHMETH1]

Syntax: [To update configuration LOGARCHMETH1]

db2 update database configuration for one using LOGARCHMETH1
'DISK:/home/db2inst1/backup/ArchiveDest'

您可以进行离线备份以确保安全，激活数据库并连接到数据库。

You can take offline backup for safety, activate the database and connect to it.

Syntax: [要执行联机备份]

Syntax: [To take online backup]

db2 backup database one online to
/home/db2inst1/onlinebackup/ compress include logs

Output:

db2 backup database one online to
/home/db2inst1/onlinebackup/ compress include logs

使用以下命令验证备份文件：

Verify Backup file using following command:

Syntax:

db2ckbkp <location/backup file>

Example:

db2ckbkp
/home/db2inst1/ONE.0.db2inst1.DBPART000.20140722112743.001

列出备份文件的历史记录

Listing the history of backup files

Syntax:

db2 list history backup all for one

Output:

                    List History File for one

Number of matching file entries = 4

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
Backup ID
 -- --- ------------------ ---- --- ------------ ------------
 --------------
  B  D  20140722105345001   F    D  S0000000.LOG S0000000.LOG

 ------------------------------------------------------------
 ----------------

 Contains 4 tablespace(s):
 00001 SYSCATSPACE

 00002 USERSPACE1

 00003 SYSTOOLSPACE

 00004 TS1
  ------------------------------------------------------------
  ----------------
  Comment: DB2 BACKUP ONE OFFLINE

 Start Time: 20140722105345

   End Time: 20140722105347

     Status: A
 ------------------------------------------------------------
 ----------------
 EID: 3 Location: /home/db2inst1


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
 Backup ID
 -- --- ------------------ ---- --- ------------ ------------
 --------------
  B  D  20140722112239000   N       S0000000.LOG S0000000.LOG
 ------------------------------------------------------------
 -------------------------------------------------------------
 -------------------------------

 Comment: DB2 BACKUP ONE ONLINE

 Start Time: 20140722112239

   End Time: 20140722112240

     Status: A
 ------------------------------------------------------------
 ----------------
  EID: 4 Location:
SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2413   sqlerrml: 0

 sqlerrmc:
 sqlerrp : sqlubIni
 sqlerrd : (1) 0                (2) 0                (3) 0

           (4) 0                (5) 0                (6) 0

 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)

           (7)      (8)      (9)      (10)       (11)
 sqlstate:

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
 Backup ID
  -- --- ------------------ ---- --- ------------ ------------
  --------------
   B  D  20140722112743001   F    D  S0000000.LOG S0000000.LOG

 ------------------------------------------------------------
 ----------------
 Contains 4 tablespace(s):

 00001 SYSCATSPACE

 00002 USERSPACE1

 00003 SYSTOOLSPACE

 00004 TS1
  -------------------------------------------------------------
  ----------------
  Comment: DB2 BACKUP ONE OFFLINE

 Start Time: 20140722112743

   End Time: 20140722112743

     Status: A
 -------------------------------------------------------------
  ----------------
 EID: 5 Location: /home/db2inst1

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
 Backup ID
  -------------------------------------------------------------
  ----------------

R  D  20140722114519001   F
20140722112743

 ------------------------------------------------------------
 ----------------
 Contains 4 tablespace(s):

 00001 SYSCATSPACE

  00002 USERSPACE1

 00003 SYSTOOLSPACE

 00004 TS1
 ------------------------------------------------------------
 ----------------
Comment: RESTORE ONE WITH RF

 Start Time: 20140722114519

   End Time: 20140722115015
     Status: A

 ------------------------------------------------------------
 ----------------
  EID: 6 Location:

Restoring the database from backup

要从备份文件恢复数据库，您需要按照给定的语法进行操作：

To restore the database from backup file, you need to follow the given syntax:

Syntax:

db2 restore database <db_name> from <location>
taken at <timestamp>

Example:

db2 restore database one from /home/db2inst1/ taken at
20140722112743

Output:

SQL2523W  Warning!  Restoring to an existing database that is
different from

the database on the backup image, but have matching names.
The target database

will be overwritten by the backup version.  The Roll-forward
recovery logs

associated with the target database will be deleted.

Do you want to continue ? (y/n) y

DB20000I  The RESTORE DATABASE command completed successfully.

向前滚动日志目录中位于的所有日志，包括磁盘驱动器故障前的最新更改。

Roll forward all the logs located in the log directory, including latest changes just before the disk drive failure.

Syntax:

db2 rollforward db <db_name> to end of logs and stop

Example:

db2 rollforward db one to end of logs and stop

Output:

                                 Rollforward Status
 Input database alias                   = one
 Number of members have returned status = 1
 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000000.LOG -
 S0000001.LOG
 Last committed transaction            = 2014-07-22-
 06.00.33.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.

DB2 - Database Security

本章介绍数据库安全性。

This chapter describes database security.

Introduction

可以通过两种不同的安全控制模式管理 DB2 数据库和函数：

DB2 database and functions can be managed by two different modes of security controls:

Authentication

认证是确认用户仅根据他授权执行的活动权限登录的过程。用户认证可以在操作系统级别或数据库级别本身执行。通过使用诸如视网膜和指纹等生物特征认证工具来防止数据库遭受黑客或恶意用户的破坏。

Authentication is the process of confirming that a user logs in only in accordance with the rights to perform the activities he is authorized to perform. User authentication can be performed at operating system level or database level itself. By using authentication tools for biometrics such as retina and figure prints are in use to keep the database from hackers or malicious users.

可以在 db2 数据库系统外部管理数据库安全性。以下是一些类型的安全认证流程：

The database security can be managed from outside the db2 database system. Here are some type of security authentication process:

Based on Operating System authentications.
Lightweight Directory Access Protocol (LDAP)

对于 DB2，安全服务作为独立产品成为操作系统的一部分。对于认证，它需要两个不同的凭据，它们分别是用户 ID 或用户名和密码。

For DB2, the security service is a part of operating system as a separate product. For Authentication, it requires two different credentials, those are userid or username, and password.

Authorization

您可以在 DB2 数据库系统中访问 DB2 数据库及其功能，这些功能由 DB2 数据库管理器管理。授权是由 DB2 数据库管理器管理的一个过程。管理器获取有关当前经过认证用户的的信息，以指示用户可以执行或访问哪些数据库操作。

You can access the DB2 Database and its functionality within the DB2 database system, which is managed by the DB2 Database manager. Authorization is a process managed by the DB2 Database manager. The manager obtains information about the current authenticated user, that indicates which database operation the user can perform or access.

以下是可以用于授权的不同权限方式：

Here are different ways of permissions available for authorization:

Primary permission ：直接授予授权 ID。

Primary permission: Grants the authorization ID directly.

Secondary permission ：如果用户是成员，则授予组和角色

Secondary permission: Grants to the groups and roles if the user is a member

Public permission ：公开授予所有用户。

Public permission: Grants to all users publicly.

Context-sensitive permission ：授予受信任上下文角色。

Context-sensitive permission: Grants to the trusted context role.

可以根据以下类别向用户授予授权：

Authorization can be given to users based on the categories below:

System-level authorization
System administrator [SYSADM]
System Control [SYSCTRL]
System maintenance [SYSMAINT]
System monitor [SYSMON]

权威机构对实例级别的功能提供控制。权威机构会提供组权限，以控制维护和权限操作。例如，数据库和数据库对象。

Authorities provide of control over instance-level functionality. Authority provide to group privileges, to control maintenance and authority operations. For instance, database and database objects.

Database-level authorization
Security Administrator [SECADM]
Database Administrator [DBADM]
Access Control [ACCESSCTRL]
Data access [DATAACCESS]
SQL administrator. [SQLADM]
Workload management administrator [WLMADM]
Explain [EXPLAIN]

权限机构在数据库内部提供控制。数据库的其他权限包括 LDAD 和 CONNECT。

Authorities provide controls within the database. Other authorities for database include with LDAD and CONNECT.

Object-Level Authorization: Object-Level authorization involves verifying privileges when an operation is performed on an object.
Content-based Authorization: User can have read and write access to individual rows and columns on a particular table using Label-based access Control [LBAC].

DB2 表和配置文件用于记录与授权名称相关联的权限。当用户尝试访问数据时，记录的权限会验证以下权限：

DB2 tables and configuration files are used to record the permissions associated with authorization names. When a user tries to access the data, the recorded permissions verify the following permissions:

Authorization name of the user
Which group belongs to the user
Which roles are granted directly to the user or indirectly to a group
Permissions acquired through a trusted context.

在使用 SQL 语句时，DB2 授权模型会考虑以下权限的组合：

While working with the SQL statements, the DB2 authorization model considers the combination of the following permissions:

Permissions granted to the primary authorization ID associated with the SQL statements.
Secondary authorization IDs associated with the SQL statements.
Granted to PUBLIC
Granted to the trusted context role.

Instance level authorities

让我们探讨一些与实例相关的权限。

Let us discuss some instance related authorities.

System administration authority (SYSADM)

这是实例级别最高级别的管理权限。拥有 SYSADM 权限的用户可以在实例内执行一些数据库和数据库管理器命令。拥有 SYSADM 权限的用户可以执行以下操作：

It is highest level administrative authority at the instance-level. Users with SYSADM authority can execute some databases and database manager commands within the instance. Users with SYSADM authority can perform the following operations:

Upgrade a Database
Restore a Database
Update Database manager configuration file.

System control authority (SYSCTRL)

这是系统控制权限中最高级别。它用于针对数据库管理器实例及其数据库执行维护和实用操作。这些操作可能会影响系统资源，但它们不允许直接访问数据库中的数据。

It is the highest level in System control authority. It provides to perform maintenance and utility operations against the database manager instance and its databases. These operations can affect system resources, but they do not allow direct access to data in the database.

拥有 SYSCTRL 权限的用户可以执行以下操作：

Users with SYSCTRL authority can perform the following actions:

Updating the database, Node, or Distributed Connect Service (DCS) directory
Forcing users off the system-level
Creating or Dropping a database-level
Creating, altering, or dropping a table space
Using any table space
Restoring Database

System maintenance authority (SYSMAINT)

它是系统控制权限的第二级。它提供对数据库管理器实例及其数据库执行维护和实用操作的功能。这些操作影响系统资源，但不会允许直接访问数据库中的数据。该权限旨在让用户维护包含敏感数据的数据库管理器实例中的数据库。

It is a second level of system control authority. It provides to perform maintenance and utility operations against the database manager instance and its databases. These operations affect the system resources without allowing direct access to data in the database. This authority is designed for users to maintain databases within a database manager instance that contains sensitive data.

只有具有 SYSMAINT 或更高级别系统权限的用户才能执行以下任务：

Only Users with SYSMAINT or higher level system authorities can perform the following tasks:

Taking backup
Restoring the backup
Roll forward recovery
Starting or stopping instance
Restoring tablespaces
Executing db2trc command
Taking system monitor snapshots in case of an Instance level user or a database level user.

具有 SYSMAINT 的用户可以执行以下任务：

A user with SYSMAINT can perform the following tasks:

Query the state of a tablespace
Updating log history files
Reorganizing of tables
Using RUNSTATS (Collection catalog statistics)

System monitor authority (SYSMON)

有了此权限，用户可以监视或获取数据库管理器实例或其数据库的快照。SYSMON 权限允许用户运行以下任务：

With this authority, the user can monitor or take snapshots of database manager instance or its database. SYSMON authority enables the user to run the following tasks:

GET DATABASE MANAGER MONITOR SWITCHES
GET MONITOR SWITCHES
GET SNAPSHOT
LIST LIST ACTIVE DATABASESLIST APPLICATIONSLIST DATABASE PARTITION GROUPSLIST DCS APPLICATIONSLIST PACKAGESLIST TABLESLIST TABLESPACE CONTAINERSLIST TABLESPACESLIST UTITLITIES
RESET MONITOR
UPDATE MONITOR SWITCHES

Database authorities

每个数据库权限都持有执行数据库上某些操作的授权 ID。这些数据库权限与特权不同。以下是一些数据库权限的列表：

Each database authority holds the authorization ID to perform some action on the database. These database authorities are different from privileges. Here is the list of some database authorities:

ACCESSCTRL ：允许授予和撤消所有对象特权和数据库权限。

ACCESSCTRL: allows to grant and revoke all object privileges and database authorities.

BINDADD ：允许在数据库中创建新程序包。

BINDADD: Allows to create a new package in the database.

CONNECT ：允许连接到数据库。

CONNECT: Allows to connect to the database.

CREATETAB ：允许在数据库中创建新表。

CREATETAB: Allows to create new tables in the database.

CREATE_EXTERNAL_ROUTINE ：允许创建应用程序和数据库用户使用的过程。

CREATE_EXTERNAL_ROUTINE: Allows to create a procedure to be used by applications and the users of the databases.

DATAACCESS ：允许访问存储在数据库表中的数据。

DATAACCESS: Allows to access data stored in the database tables.

DBADM ：充当数据库管理员。它授予所有其他数据库权限，但 ACCESSCTRL、DATAACCESS 和 SECADM 除外。

DBADM: Act as a database administrator. It gives all other database authorities except ACCESSCTRL, DATAACCESS, and SECADM.

EXPLAIN ：允许解释查询计划，而不要求他们拥有访问表中数据的权限。

EXPLAIN: Allows to explain query plans without requiring them to hold the privileges to access the data in the tables.

IMPLICIT_SCHEMA ：允许用户通过使用 CREATE 语句创建对象来隐式创建架构。

IMPLICIT_SCHEMA: Allows a user to create a schema implicitly by creating an object using a CREATE statement.

LOAD ：允许将数据加载到表中。

LOAD: Allows to load data into table.

QUIESCE_CONNECT ：允许在数据库静默（暂时禁用）时访问数据库。

QUIESCE_CONNECT: Allows to access the database while it is quiesce (temporarily disabled).

SECADM ：允许充当数据库的安全管理员。

SECADM: Allows to act as a security administrator for the database.

SQLADM ：允许监控和调整 SQL 语句。

SQLADM: Allows to monitor and tune SQL statements.

WLMADM ：允许充当工作负载管理员

WLMADM: Allows to act as a workload administrator

Privileges

SETSESSIONUSER

授权标识符权限涉及授权标识符上的操作。只有一个称为 SETSESSIONUSER 权限的权限。它可以授予用户或组，并允许会话用户将其身份切换到授予了权限的任何授权标识符。此权限由用户 SECADM 权限授予。

Authorization ID privileges involve actions on authorization IDs. There is only one privilege, called the SETSESSIONUSER privilege. It can be granted to user or a group and it allows to session user to switch identities to any of the authorization IDs on which the privileges are granted. This privilege is granted by user SECADM authority.

Schema privileges

此权限涉及对数据库中架构的操作。架构的所有者具有操作架构对象（如表、视图、索引、程序包、数据类型、函数、触发器、过程和别名）的所有权限。可以向用户、组、角色或 PUBLIC 授予以下权限中的任何一种：

This privileges involve actions on schema in the database. The owner of the schema has all the permissions to manipulate the schema objects like tables, views, indexes, packages, data types, functions, triggers, procedures and aliases. A user, a group, a role, or PUBLIC can be granted any user of the following privileges:

CREATEIN: allows to create objects within the schema
ALTERIN: allows to modify objects within the schema.

DROPIN

这允许删除架构中的对象。

This allows to delete the objects within the schema.

Tablespace privileges

这些权限涉及数据库中表空间上的操作。可以向用户授予表空间的 USE 权限。然后这些权限允许他们在表空间中创建表。当创建表空间时，权限所有者可以使用具有 GRANT OPTION 命令的 USE 权限授予权限。而 SECADM 或 ACCESSCTRL 权限具有表空间上 USE 权限的权限。

These privileges involve actions on the tablespaces in the database. User can be granted the USE privilege for the tablespaces. The privileges then allow them to create tables within tablespaces. The privilege owner can grant the USE privilege with the command WITH GRANT OPTION on the tablespace when tablespace is created. And SECADM or ACCESSCTRL authorities have the permissions to USE privileges on the tablespace.

Table and view privileges

用户必须对数据库有 CONNECT 权限才能使用表和视图权限。表和视图的权限如下所述：

The user must have CONNECT authority on the database to be able to use table and view privileges. The privileges for tables and views are as given below:

CONTROL

它提供了表或视图的所有权限，包括删除和授予、撤销向用户授予的单个表权限。

It provides all the privileges for a table or a view including drop and grant, revoke individual table privileges to the user.

ALTER

它允许用户修改表。

It allows user to modify a table.

DELETE

它允许用户从表或视图中删除行。

It allows the user to delete rows from the table or view.

INDEX

它允许用户将行插入到表或视图中。它还可以运行导入实用程序。

It allows the user to insert a row into table or view. It can also run import utility.

REFERENCES

它允许用户创建并删除外键。

It allows the users to create and drop a foreign key.

SELECT

它允许用户从表或视图中检索行。

It allows the user to retrieve rows from a table or view.

UPDATE

它允许用户在表、视图中更改条目。

It allows the user to change entries in a table, view.

Package privileges

用户必须对数据库具有 CONNECT 权限。包是一个数据库对象，其中包含数据库管理器以在特定应用程序中以最有效方式访问数据的信息。

User must have CONNECT authority to the database. Package is a database object that contains the information of database manager to access data in the most efficient way for a particular application.

CONTROL

它为用户提供了重新绑定、删除或执行包的权限。拥有此权限的用户将被授予 BIND 和 EXECUTE 权限。

It provides the user with privileges of rebinding, dropping or executing packages. A user with this privileges is granted to BIND and EXECUTE privileges.

BIND

它允许用户绑定或重新绑定该包。

It allows the user to bind or rebind that package.

EXECUTE

允许执行包。

Allows to execute a package.

Index privileges

此权限自动在索引上接收 CONTROL 权限。

This privilege automatically receives CONTROL privilege on the index.

Sequence privileges

序列会自动在序列上接收 USAGE 和 ALTER 权限。

Sequence automatically receives the USAGE and ALTER privileges on the sequence.

Routine privileges

它涉及数据库中例程（例如函数、过程和方法）的操作。

It involves the action of routines such as functions, procedures, and methods within a database.

DB2 - Roles

Introduction

角色是一个可以分配给用户、组、PUBLIC 或其他角色的多个权限的数据库对象，这通过使用 GRANT 语句来实现。

A role is a database object that groups multiple privileges that can be assigned to users, groups, PUBLIC or other roles by using GRANT statement.

Restrictions on roles

A role cannot own database objects.
Permissions and roles granted to groups are not considered when you create the following database objects. Package Containing static SQLViewsMaterialized Query Tables (MQT)TriggersSQL Routines

Creating and granting membership in roles

Syntax: [创建新角色]

Syntax: [To create a new role]

db2 create role <role_name>

Example : [创建名为“sales”的新角色以添加某些表，这些表将由某些用户或组管理]

Example: [To create a new role named ‘sales’ to add some table to be managed by some user or group]

db2 create role sales

Output:

DB20000I The SQL command completed successfully.

Granting role from DBADM to a particular table

Syntax: [向表授予角色的权限]

Syntax: [To grant permission of a role to a table]

db2 grant select on table <table_name> to role <role_name>

Example : [向角色“sales”添加管理表“shope.books”的权限]

Example: [To add permission to manage a table ‘shope.books’ to role ‘sales’]

db2 grant select on table shope.books to role sales

Output:

DB20000I  The SQL command completed successfully.

安全管理员向必需用户授予角色。（在你使用此命令之前，你需要创建用户。）

Security administrator grants role to the required users. (Before you use this command, you need to create the users.)

Syntax: [向角色添加用户]

Syntax: [To add users to a role]

db2 grant role <role_name> to user <username>

Example : [向角色“sales”添加用户“mastanvali”]

Example: [To add a user ‘mastanvali’ to a role ‘sales’]

db2 grant sales to user mastanvali

Output:

DB20000I  The SQL command completed successfully.

Role hierarchies

要为角色创建层级结构，需要向每个角色授予权限/成员资格，以便与另一个角色关联。

For creating a hierarchies for roles, each role is granted permissions/ membership with another role.

Syntax: [在此语法之前，创建一个名为“production”的新角色]

Syntax: [before this syntax create a new role with name of “production”]

db2 grant role <roll_name> to role <role_name>

Example : [向另一个角色“production”提供角色“sales”的权限]

Example: [To provide permission of a role ‘sales’ to another role ‘production’]

db2 grant sales to role production

DB2 - LDAP

Introduction

LDAP 是轻量级目录访问协议。LDAP 是一个基于客户端-服务器模型且在 TCP/IP 栈上层运行的全局目录服务行业标准协议。LDAP 提供了一个连接、访问、修改和搜索 Internet 目录的工具。

LDAP is Lightweight Directory Access Protocol. LDAP is a global directory service, industry-standard protocol, which is based on client-server model and runs on a layer above the TCP/IP stack. The LDAP provides a facility to connect to, access, modify, and search the internet directory.

LDAP 服务器包含信息，这些信息以目录树的形式组织。客户端要求服务器提供信息或针对特定信息执行某些操作。服务器通过提供所需的（如果有的话）信息来应答客户端，或将客户端转给其他服务器处理所需的请求。然后，客户端从其他服务器获取所需信息。

The LDAP servers contain information which is organized in the form of a directory tree. The clients ask server to provide information or to perform some operation on a particular information. The server answers the client by providing required information if it has one, or it refers the client to another server for action on required information. The client then acquires the desired information from another server.

目录的树形结构在所有参与服务器上始终保持相同。这是 LDAP 目录服务的一个显著特性。因此，无论客户端参考的是哪台服务器，客户端始终会以无差错的方式获取所需信息。这里，我们使用 LDAP 对 IBM DB2 进行认证，以替代操作系统认证。

The tree structure of directory is maintained same across all the participating servers. This is a prominent feature of LDAP directory service. Hence, irrespective of which server is referred to by the client, the client always gets required information in an error-free manner. Here, we use LDAP to authenticate IBM DB2 as a replacement of operating system authentication.

LDAP 有两种类型：

There are two types of LDAP:

我们来看看如何配置透明 LDAP。

Let us see how to configure a transparent LDAP.

Configuring transparent LDAP

要开始配置透明 LDAP，您需要配置 LDAP 服务器。

To start with configuration of transparent LDAP, you need to configure the LDAP server.

LDAP server configuration

创建一个 SLAPD.conf 文件，其中包含 LDAP 中所有有关用户和组对象的信息。在安装 LDAP 服务器时，默认情况下，它在机器上配置了基本的 LDAP 目录树。

Create a SLAPD.conf file, which contains all the information about users and group object in the LDAP. When you install LDAP server, by default it is configured with basic LDAP directory tree on your machine.

下表指示修改后的文件配置。

The table shown below indicates the file configuration after modification.

以黄色突出显示代码框中的文本表示以下内容：

The text highlighted with yellow the code box means for the following:

DBA 用户 ID = “db2my1”，组 = “db1my1adm”，密码 = “db2my1” 管理员用户 ID = “my1adm”，组 = “dbmy1ctl”。

DBA user-id = “db2my1”, group = “db1my1adm”, password= “db2my1” Admin user-id = “my1adm”, group = “dbmy1ctl”.

# base dn: example.com
dn: dc=example,dc=com
dc: example
o: example
objectClass: organization
objectClass: dcObject
# pc box db
dn: dc=db697,dc=example,dc=com
dc: db697
o: db697
objectClass: organization
objectClass: dcObject
#
# Group: dbadm
#
dn: cn=dbmy1adm,dc=db697,dc=example,dc=com
cn: dbmy1adm
objectClass: top
objectClass: posixGroup
gidNumber: 400
objectClass: groupOfNames
member: uid=db2my1,cn=dbmy1adm,dc=db697,dc=example,dc=com
memberUid: db2my1
#
# User: db2
#
dn: uid=db2my1,cn=dbmy1adm,dc=db697,dc=example,dc=com
cn: db2my1
sn: db2my1
uid: db2my1
objectClass: top
objectClass: inetOrgPerson
objectClass: posixAccount
uidNumber: 400
gidNumber: 400
loginShell: /bin/csh
homeDirectory: /db2/db2my1
#
# Group: dbctl
#
dn: cn=dbmy1ctl,dc=db697,dc=example,dc=com
cn: dbmy1ctl
objectClass: top
objectClass: posixGroup
gidNumber: 404
objectClass: groupOfNames
member: uid=my1adm,cn=dbmy1adm,dc=db697,dc=example,dc=com
memberUid: my1adm
#
# User: adm
#
dn: uid=my1adm,cn=dbmy1ctl,dc=db697,dc=example,dc=com
cn: my1adm
sn: my1adm
uid: my1adm
objectClass: top
objectClass: inetOrgPerson
objectClass: posixAccount
uidNumber: 404
gidNumber: 404
loginShell: /bin/csh
homeDirectory: /home/my1adm

使用名称“/var/lib/slapd.conf”保存上述文件，然后执行此文件，通过以下命令将这些值添加到 LDAP 服务器中。这是一条 Linux 命令，而不是 db2 命令。

Save the above file with name ‘/var/lib/slapd.conf’, then execute this file by following command to add these values into LDAP Server. This is a linux command; not a db2 command.

ldapadd r- -D ‘cn=Manager,dc=example,dc=com” –W –f
/var/lib/slapd.conf

在 LDAP 服务器上注册 DB2 用户和 DB2 组后，登录到已安装实例和数据库的特定用户。您需要配置 LDAP 客户端，以向客户端确认您的服务器位于何处（远程或本地）。

After registering the DB2 users and the DB2 group at the LDAP Server, logon to the particular user where you have installed instance and database. You need to configure LDAP client to confirm to client where your server is located, be it remote or local.

LDAP client configuration

LDAP 客户端配置保存在文件“ldap.conf”中。有两个文件可用于配置参数，一个通用，另一个特定。您应该在“/etc/ldap.conf”中找到第一个文件，而另一个位于“/etc/openldap/ldap.conf”中。

The LDAP Client configuration is saved in the file ‘ldap.conf’. There are two files available for configuration parameters, one is common and the other is specific. You should find the first one at ‘/etc/ldap.conf’ and the latter is located at ‘/etc/openldap/ldap.conf’.

通用 LDAP 客户端配置文件中提供了以下数据

The following data is available in common LDAP client configuration file

# File: /etc/ldap.conf
# The file contains lots of more entries and many of them
# are comments. You show only the interesting values for now
host localhost
base dc=example,dc=com
ldap_version 3
pam_password crypt
pam_filter objectclass=posixAccount
nss_map_attribute uniqueMember member
nss_base_passwd dc=example,dc=com
nss_base_shadow dc=example,dc=com
nss_base_group dc=example,dc=com

您需要根据 DB2 配置更改服务器和域信息的位置。如果我们在同一个系统中使用服务器，那么在“host”处将其指定为“localhost”，在“base”处，您可以配置 LDAP 服务器的“SLAPD.conf”文件中提到的内容。

You need to change the location of server and domain information according to the DB2 configuration. If we are using server in same system then mention it as ‘localhost’ at ‘host’ and at ‘base’ you can configure which is mentioned in ‘SLAPD.conf’ file for LDAP server.

可插拔认证模型 (PAM) 是用于认证服务的 API。这是一个用于使用加密密码和特定类型为 posixAccount 的 LDAP 对象的 LDAP 认证的公用接口。此类型的所有 LDAP 对象都表示包含可移植操作系统接口 (POSIX) 属性的抽象帐户。

Pluggable Authentication Model (PAM) is an API for authentication services. This is common interface for LDAP authentication with an encrypted password and special LDAP object of type posixAccount. All LDAP objects of this type represent an abstraction of an account with portable Operating System Interface (POSIX) attributes.

网络安全服务 (NSS) 是一组支持跨平台开发支持安全性的客户端和服务器应用程序的库。其中包括 SSL、TLS、PKCS S/MIME 等库以及其他安全标准。

Network Security Services (NSS) is a set of libraries to support cross-platform development of security-enabled client and server applications. This includes libraries like SSL, TLS, PKCS S/MIME and other security standards.

您需要为此接口和两个其他映射属性指定基准 DN。OpenLDAP 客户端配置文件包含下面给出的条目：

You need to specify the base DN for this interface and two additional mapping attributes. OpenLDAP client configuration file contains the entries given below:

host localhost
base dc=example,dc=com

到此为止，您只需定义 LDAP 服务的主机和基准 DN。

Till this you just define the host of LDAP serve and the base DN.

Validating OpenLDAP environment

在配置 LDAP 服务器和 LDAP 客户端后，请验证两者是否能通信。

After you configured your LDAP Server and LDAP Client, verify both for communication.

Step1 ：使用以下命令检查您的本地 LDAP 服务器是否正在运行：

Step1: Check your Local LDAP server is running. Using below command:

ps -ef | grep -i ldap

此命令应列出表示您 LDAP 服务器的 LDAP 程序：

This command should list the LDAP deamon which represents your LDAP server:

/usr/lib/openldap/slapd -h ldap:/// -u ldap -g ldap -o slp=on

这表明您的 LDAP 服务器正在运行并正在等待来自客户端的请求。如果没有上述命令的进程，您可以使用“rcldap”命令启动 LDAP 服务器。

This indicates that you LDAP server is running and is waiting for request from clients. If there is no such process for previous commands you can start LDAP server with the ’rcldap’ command.

rcldap start

当服务器启动后，您可以通过发出以下命令在“/var/log/messages/”文件中对其进行监视。

When the server starts, you can monitor this in the file ‘/var/log/messages/ by issuing the following command.

tail –f /var/log/messages

Testing connection to LDAP server with ldapsearch

ldapsearch 命令打开与 LDAP 服务器的连接，绑定到它，并执行一个搜索查询，可以使用特殊参数“x”指定此搜索查询，通过使用 -x 参数而不是更加复杂的安全认证层（SASL）之类的机制来连接到您的 LDAP 服务器，以进行简单身份验证。

The ldapsearch command opens a connection to an LDAP server, binds to it and performs a search query which can be specified by using special parameters ‘-x’ connect to your LDAP server with a simple authentication mechanism by using the –x parameter instead of a more complex mechanism like Simple Authentication and Security Layer (SASL)

ldapsearch –x

LDAP 服务器应发送下面显示的响应，包含所有以 LDAP 数据交换格式（LDIF）存储的 LDAP 条目。

LDAP server should reply with a response given below, containing all of your LDAP entries in a LDAP Data Interchange Format(LDIF).

# extended LDIF
#
# LDAPv3
# base <> with scope subtree
# filter: (objectclass=*)
# requesting: ALL
# example.com
dn: dc=example,
dc=com  dc: example
o: example
objectClass: organization
objectClass: dcObject
# search result
search: 2
result: 0 Success
# numResponses: 2
# numEntries: 1

Configuring DB2

在处理 LDAP 服务器和客户端后，您需要配置 DB2 数据库以用于 LDAP。让我们讨论如何安装和配置数据库，以便在 DB2 用户身份验证过程中使用 LDAP 环境。

After working with LDAP server and client, you need to configure our DB2 database for use with LDAP. Let us discuss, how you can install and configure your database to use our LDAP environment for the DB2 user authentication process.

Configuring DB2 and LDAP interaction plug-ins

IBM 提供了一个带有 DB2 LDAP 插件的免费软件包。DB2 软件包包括三个 DB2 安全插件，各针对以下一项：

IBM provides a free package with LDAP plug-ins for DB2. The DB2 package includes three DB2 security plug-ins for each of the following:

server side authentication
client side authentication
group lookup

根据您的需求，您可以使用上述三个插件中的任何一个或全部。该插件不支持某些用户在 LDAP 中定义，而其他用户在操作系统中定义的环境。如果您决定使用 LDAP 插件，则需要在 LDAP 服务器中定义与数据库关联的所有用户。该原则也适用于该组插件。

Depending upon your requirements, you can use any of the three plug-ins or all of them. This plugin do not support environments where some users are defined in LDAP and others in the operating Systems. If you decide to use the LDAP plug-ins, you need to define all users associated with the database in the LDAP server. The same principle applies to the group plug-in.

您必须决定哪些插件对我们的系统是强制性的。在客户端系统上发生 CONNECT 或 ATTACH 语句时提供用户 ID 和密码验证的情形中，使用客户端身份验证插件。因此，必须将数据库管理器配置参数 SRVCON_AUTH 或 AUTHENTICATION 设置为值 CLIENT。很难保护客户端身份验证，通常不建议使用。通常建议使用服务器插件，因为如果客户端执行 CONNECT 或 ATTACH 语句，它会执行用户 ID 和密码的服务器端验证，这是安全的方式。服务器插件还提供了一种将 LDAP 用户 ID 映射到 DB2 授权 ID 的方法。

You have to decide which plug-ins are mandatory for our system. The client authentication plug-ins used in scenarios where the user ID and the password validation supplied on a CONNECT or ATTACH statement occurs on the client system. So the database manager configuration parameters SRVCON_AUTH or AUTHENTICATION need to be set to the value CLIENT. The client authentication is difficult to secure and is not generally recommended. Server plug-in is generally recommended because it performs a server side validation of user IDs and passwords, if the client executes a CONNECT or ATTACH statement and this is secure way. The server plug-in also provides a way to map LDAP user IDs DB2 authorization IDs.

现在您可以开始安装和配置 DB2 安全插件，您需要考虑 DB2 所需的目录信息树。DB2 使用间接授权，这意味着用户属于一个组，而该组被授予较少的权限。您需要在 LDAP 目录中定义所有 DB2 用户和 DB2 组。

Now you can start installation and configuration of the DB2 security plug-ins, you need to think about the required directory information tree for DB2. DB2 uses indirect authorization which means that a user belongs to a group and this group was granted with fewer authorities. You need to define all DB2 users and DB2 groups in LDAP directory.

Image

LDIF 文件 openldap.ldif 应包含以下代码：

The LDIF file openldap.ldif should contain the code below:

#
# LDAP root object
# example.com
#
dn: dc=example,
dc=com
dc: example
o: example
objectClass: organization
objectClass: dcObject
 #
 # db2 groups
 #
 dn: cn=dasadm1,dc=example,dc=com
 cn: dasadm1
 objectClass: top
 objectClass: posixGroup
 gidNumber: 300
 objectClass: groupOfNames
 member: uid=dasusr1,cn=dasadm1,dc=example,dc=com
 memberUid: dasusr1
 dn: cn=db2grp1,dc=example,dc=com
 cn: db2grp1
 objectClass: top
 objectClass: posixGroup
 gidNumber: 301
 objectClass: groupOfNames
 member: uid=db2inst2,cn=db2grp1,dc=example,dc=com  memberUid: db2inst2
 dn: cn=db2fgrp1,dc=example,dc=com
 cn: db2fgrp1
 objectClass: top
 objectClass: posixGroup
 gidNumber: 302
 objectClass: groupOfNames
 member: uid=db2fenc1,cn=db2fgrp1,dc=example,dc=com
 memberUid: db2fenc1
 #
 # db2 users
 #
 dn: uid=dasusr1,
 cn=dasadm1,
 dc=example,dc=com
 cn: dasusr1
 sn: dasusr1
 uid: dasusr1
 objectClass: top
 objectClass: inetOrgPerson
 objectClass: posixAccount
 uidNumber: 300
 gidNumber: 300
 loginShell: /bin/bash
 homeDirectory: /home/dasusr1
 dn: uid=db2inst2,cn=db2grp1,dc=example,dc=com
 cn: db2inst2
 sn: db2inst2
 uid: db2inst2
 objectClass: top
 objectClass: inetOrgPerson
 objectClass: posixAccount
 uidNumber: 301
 gidNumber: 301
 loginShell: /bin/bash
 homeDirectory: /home/db2inst2
 dn: uid=db2fenc1,cn=db2fgrp1,dc=example,dc=com
 cn: db2fenc1
 sn: db2fenc1
 uid: db2fenc1
 objectClass: top
 objectClass: inetOrgPerson
 objectClass: posixAccount
 uidNumber: 303
 gidNumber: 303
 loginShell: /bin/bash
 homeDirectory: /home/db2fenc1

创建一个名为“db2.ldif”的文件，并将上述示例粘贴到其中。使用此文件，将已定义的结构添加到 LDAP 目录。

Create a file named ‘db2.ldif’ and paste the above example into it. Using this file, add the defined structures to your LDAP directory.

要将 DB2 用户和 DB2 组添加到 LDAP 目录，您需要将用户绑定为“rootdn”到 LDAP 服务器以获得确切的权限。

To add the DB2 users and DB2 groups to the LDAP directory, you need to bind the user as ‘rootdn’ to the LDAP server in order to get the exact privileges.

执行以下语法，使用 LDIF 文件“db2.ldif”中定义的所有对象填充 LDAP 信息目录

Execute the following syntaxes to fill the LDAP information directory with all our objects defined in the LDIF file ‘db2.ldif’

ldapadd –x –D “cn=Manager, dc=example,dc=com” –W –f <path>/db2.ldif

执行具有更多参数的搜索结果

Perform the search result with more parameter

ldapsearch –x |more

Preparing file system for DB2 usage

为我们的 LDAP 用户 db2inst2 创建实例。此用户需要包含空文件的目录。在创建新实例之前，您需要创建一个成为该实例所有者的用户。

Creating instance for our LDAP user db2inst2. This user requires home directory with two empty files inside the home directory. Before you create a new instance, you need to create a user who will be the owner of the instance.

在创建实例用户后，您必须在用户主目录（DB2 将对其进行修改）中创建文件“.profile”和“.login”。要在该目录中创建此文件，请执行以下命令：

After creating the instance user, you should have to create the file ‘.profile’ and ‘.login’ in user home directory, which will be modified by DB2. To create this file in the directory, execute the following command:

mkdir /home/db2inst2
mkdir /home/db2inst2/.login
mkdir /home/db2inst2/.profile

您已经在 LDAP 目录中注册了所有与 DB2 相关联的用户和组，现在您可以使用实例所有者 ID“db2inst2”和 fenced 用户 ID“db2fenc1”创建一个名为“db2inst2”的实例，这是运行用户定义函数 (UDF) 或存储过程所必需的。

You have registered all users and groups related with DB2 in LDAP directory, now you can create an instance with the name ‘db2inst2’ with the instance owner id ‘db2inst2’ and use the fenced user id ‘db2fenc1’, which is needed for running user defined functions (UDFs)or stored procedures.

/opt/ibm/db2/V10.1/instance/db2icrt –u db2fenc1 db2inst2
DBI1070I Program db2icrt completed successfully.

现在检查实例主目录。您会看到一个名为“sqllib”的新子目录，以及为 DB2 使用而定制的 .profile 和 .login 文件。

Now check the instance home directory. You can see new sub-directory called ‘sqllib’ and the .profile and .login files customized for DB2 usage.

Configuring authentication public-ins for LDAP support in DB2

将所需的 LDAP 插件复制到相应的 DB2 目录：

Copy the required LDAP plug-ins to the appropriate DB2 directory:

cp            ///v10/IBMLDAPauthserver.so
/home/db2inst2/sqllib/security/plugin/server/.

cp            ///v10/IBMLDAPgroups.so
/home/db2inst2/sqllib/security
/plugin/group/.

插件复制到指定目录后，您调整为 DB2 实例所有者登录并更改数据库管理器配置以使用这些插件。

Once the plug-ins are copied to the specified directory, you toned to login to DB2 instance owner and change the database manager configuration to use these plug-ins.

Su – db2inst2
db2inst2> db2 update dbm cfg using svrcon_pw_plugin
IBMLDAPauthserver
db2inst2> db2 update dbm cfg using group_plugin
IBMLDAPgroups
db2inst2> db2 update dbm cfg using authentication
SERVER_ENCRYPT
db2inst2> db2stop
db2inst2> db2start

该修改在您启动 DB2 实例后生效。在重新启动该实例后，您需要安装和配置主 DB2 LDAP 配置文件“IBMLDAPSecurity.ini”，以使 DB2 插件与当前 LDAP 配置一起使用。

This modification comes into effect after you start DB2 instance. After restarting the instance, you need to install and configure the main DB2 LDAP configuration file named “IBMLDAPSecurity.ini” to make DB2 plug-ins work with the current LDAP configuration.

IBMLDAPSecurity.ini 文件包含

IBMLDAPSecurity.ini file contains

;-----------------------------------------------------------
; SERVER RELATED VALUES
;-----------------------------------------------------------
; Name of your LDAP server(s).
; This is a space separated list of LDAP server addresses,
; with an optional port number for each one:
; host1[:port] [host2:[port2] ... ]
; The default port number is 389, or 636 if SSL is enabled.
LDAP_HOST = my.ldap.server
;-----------------------------------------------------------
; USER RELATED VALUES
;-----------------------------------------------------------
rs
; LDAP object class used for use USER_OBJECTCLASS = posixAccount
; LDAP user attribute that represents the "userid"
; This attribute is combined with the USER_OBJECTCLASS and
; USER_BASEDN (if specified) to construct an LDAP search
; filter when a user issues a DB2 CONNECT statement with an
; unqualified userid. For example, using the default values
; in this configuration file, (db2 connect to MYDB user bob
; using bobpass) results in the following search filter:
OrgPerson)(uid=bob)
; &(objectClass=inet USERID_ATTRIBUTE = uid
representing the DB2 authorization ID
; LDAP user attribute, AUTHID_ATTRIBUTE = uid
;-----------------------------------------------------------
; GROUP RELATED VALUES
;-----------------------------------------------------------
ps
; LDAP object class used for grou GROUP_OBJECTCLASS = groupOfNames
at represents the name of the group
; LDAP group attribute th GROUPNAME_ATTRIBUTE = cn
; Determines the method used to find the group memberships
; for a user. Possible values are:
; SEARCH_BY_DN - Search for groups that list the user as
; a member. Membership is indicated by the
; group attribute defined as
; GROUP_LOOKUP_ATTRIBUTE.
; USER_ATTRIBUTE - A user's groups are listed as attributes
; of the user object itself. Search for the
; user attribute defined as
TRIBUTE to get the groups.
; GROUP_LOOKUP_AT GROUP_LOOKUP_METHOD = SEARCH_BY_DN
; GROUP_LOOKUP_ATTRIBUTE
; Name of the attribute used to determine group membership,
; as described above.
llGroups
; GROUP_LOOKUP_ATTRIBUTE = ibm-a GROUP_LOOKUP_ATTRIBUTE = member

现在定位当前实例目录中的文件 IBMLDAPSecurity.ini 文件。将上述示例内容复制到其中。

Now locate the file IBMLDAPSecurity.ini file in the current instance directory. Copy the above sample contents into the same.

Cp
//db2_ldap_pkg/IBMLDAPSecurity.ini
/home/db2inst2/sqllib/cfg/

现在，您需要使用以下两个语法重新启动 DB2 实例：

Now you need to restart your DB2 instance, using two syntaxes given below:

db2inst2> db2stop

Db2inst2> db2start

此时，如果您尝试“db2start”命令，您会收到安全错误消息。因为尚未针对您的 LDAP 环境正确配置 DB2 安全配置。

At this point, if you try ‘db2start’ command, you will get security error message. Because, DB2 security configuration is not yet correctly configured for your LDAP environment.

Customizing both configurations

将 LDAP_HOST 名称准备好，该名称在 slapd.conf 文件中进行配置。

Keep LDAP_HOST name handy, which is configured in slapd.conf file.

现在，编辑 IMBLDAPSecurity.ini 文件并键入 LDAP_HOST 名称。所述两个文件中的 LDAP_HOST 名称必须相同。

Now edit IMBLDAPSecurity.ini file and type the LDAP_HOST name. The LDAP_HOST name in both the said files must be identical.

文件的内容如下所示：

The contents of file are as shown below:

      ;-----------------------------------------------------------
      ; SERVER RELATED VALUES
      ;-----------------------------------------------------------
      LDAP_HOST = localhost
      ;-----------------------------------------------------------
      ; USER RELATED VALUES
      ----------------------------
      ;-------------------------------
      USER_OBJECTCLASS = posixAccount
      USER_BASEDN = dc=example,dc=com
      USERID_ATTRIBUTE = uid
      AUTHID_ATTRIBUTE = uid
      ;-----------------------------------------------------------
      ; GROUP RELATED VALUES
      ;-----------------------------------------------------------
      GROUP_OBJECTCLASS = groupOfNames
	  GROUP_BASEDN = dc=example,dc=com
      GROUPNAME_ATTRIBUTE = cn
      GROUP_LOOKUP_METHOD = SEARCH_BY_DN
      GROUP_LOOKUP_ATTRIBUTE = member

更改这些值后，LDAP 会立即生效，而您的 DB2 环境搭配 LDAP 也可完美运行。

After changing these values, LDAP immediately takes effect and your DB2 environment with LDAP works perfectly.

您可以注销并重新登录到“db2inst2”用户。

You can logout and login again to ‘db2inst2’ user.

现在，您的实例正在与 LDAP 目录一起工作。

Now your instance is working with LDAP directory.