Db2 简明教程

DB2 - Databases

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

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

Database architecture

database archi

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

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.

  1. Global deadlock write-to-file event monitoring files

  2. Table space information files [SQLSPCS.1, SQLSPCS.2]

  3. Storage group control files [SQLSGF.1, SQLSGF.2]

  4. Temporary table space container files. [/storage path/ /T0000011/C000000.TMP/SQL00002.MEMBER0001.TDA]

  5. Global Configuration file [SQLDBCONF]

  6. History files [DB2RHIST.ASC, DB2RHIST.BAK, DB2TSCHG.HIS, DB2TSCHG.HIS]

  7. Logging-related files [SQLOGCTL.GLFH.1, SQLOGCTL.GLFH.2]

  8. Locking files [SQLINSLK, SQLTMPLK]

  9. Automatic Storage containers

Member specific directory

目录位置：/NODExxxx/SQLxxxx/MEMBER0000

Directory location : /NODExxxx/SQLxxxx/MEMBER0000

此目录包含：

This directory contains:

  1. Objects associated with databases

  2. Buffer pool information files [SQLBP.1, SQLBP.2]

  3. Local event monitoring files

  4. Logging-related files [SQLOGCTL.LFH.1, SQLOGCTL.LFH.2, SQLOGMIR.LFH].

  5. Local configuration files

  6. 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:

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:

Syntax:

db2 list database directory

Example:

Example:

db2 list database directory

Output:

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:

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:

Syntax:

db2 connect to <database name>

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

Example: [To Connect Database one to current CLI]

db2 connect to one

Output:

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:

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:

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:

  1. System Catalog Tables

  2. User Table Data

  3. Long Field Data

  4. Large Object (LOB) Data

  5. Index Space

  6. Temporary Work Space

  7. XML data

  8. Log file space

  9. Local database directory

  10. 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:

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:

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:

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:

Output:

DB20000I The DROP DATABASE command completed successfully