DB2 LUW

Table of Contents

1 DB2 LUW

Since the 1970s, when IBM Research invented the Relational Model and the Structured Query Language (SQL), IBM has developed a complete family of data servers. Development started on mainframe platforms such as Virtual Machine (VM), Virtual Storage Extended (VSE), and Multiple Virtual Storage (MVS). In 1983, DB2 for MVS Version 1 was born. Name of "DB2" was used to indicate a shift from hierarchical databases—such as the Information Management System (IMS) popular at the time—to the new relational databases.

DB2 for Linux, UNIX and Windows is a database server product developed by IBM. Sometimes called DB2 LUW for brevity, it is part of the DB2 family of database products.

参考:
DB2 10.5 for Linux, UNIX, and Windows: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/c0004100.html?cp=SSEPGG_10.5.0/2-12&lang=en

2 安装和启动

2.1 安装DB2

解压安装包后,可以用图形界面方式(db2setup)和命令行交互方式(db2_install)安装。这里仅介绍命令行交互方式的安装。

推荐用root用户安装DB2,否则无法定制安装路径(db2_install的-b选项仅在root用户下才效)。

$ sudo su
# ./db2_install -b /opt/ibm/db2/V9.7/

再按提示一步一步安装即可。

参考:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0023669.html?cp=SSEPGG_10.1.0/3-6-2-6-6&lang=en

2.2 创建DB2运行所需的用户和组

DB2 requires a minimum of three users and groups: an instance owner (db2inst1 by default), a fenced user, and the Database Administration Server (DAS) user.

$ sudo su
# groupadd db2grp1
# groupadd db2fgrp1
# groupadd db2agrp1
# useradd -g db2grp1 -d /home/db2inst1 -m -s /bin/bash db2inst1
# useradd -g db2fgrp1 -d /home/db2fenc -m -s /bin/bash db2fenc1
# useradd -g db2agrp1 -d /home/db2das -m -s /bin/bash db2das1
# passwd db2inst1
# passwd db2fenc1
# passwd db2das1

2.3 创建实例

$ sudo su
# cd /opt/ibm/db2/V9.7/instance
# ./dascrt -u db2das1
# ./db2icrt -u db2inst1 db2inst1

说明:
dascrt :创建DB2 adminstration server,每台服务器只有一个这种server,为进行DB2管理(比如运行控制中心)所必须,同时指定其管理用户是db2das1。
db2icrt :创建实例,实例名字一般和管理用户名一样,这里均为db2inst1。

2.4 启动DB2

# su - db2das
$ db2admin start               ## 启动DB2管理服务器
$ su - db2inst1
$ db2start                     ## 启动数据库实例

2.5 配置DB2自动启动

$ sudo su
# cd /opt/ibm/db2/V9.7/instance/
# ./db2iauto -on db2inst1

2.6 配置网络

# su - db2inst1
$ db2 update dbm cfg using SVCENAME 50000  ## 改变DB2的远程访问端口,默认是5000
$ db2set DB2COMM=TCPIP                     ## 修改DB2的连接方式为TCPIP

2.7 实例的启动和关闭

# su - db2inst1
$ db2 force applications all               ## 关闭所有和DB2相关的应用程序
$ db2stop                                  ## 关闭数据库实例
$ db2start                                 ## 启动数据库实例
$ db2ilist                                 ## 查看所有实例
$ db2 get instance                         ## 查看当前所运行的实例

说明:在关闭数据库实例前,请先用命令 db2 force applications all 关闭DB2相关的应用程序,否则db2stop可能不成功,出现 SQL1025N 错误:

$ db2stop
SQL1025N  The database manager was not stopped because databases are still active.

2.8 创建数据库

# su - db2inst1
$ db2 create database test1                 ## 创建数据库test1
DB20000I The CREATE DATABASE command completed successfully.

2.9 实例owner连接数据库

$ su - db2inst1
$ db2
db2 => connect to test1
SQL1084C  Shared memory segments cannot be allocated.  SQLSTATE=57019
db2 => quit
DB20000I  The QUIT command completed successfully.

用下面方法可解决问题"SQL1084C Shared memory segments cannot be allocated. SQLSTATE=57019":

$ su - db2inst1
$ db2stop force
$ db2start
$ db2 update db cfg for test1 using UTIL_HEAP_SZ 5000 DATABASE_MEMORY 50000
$ db2
db2 => connect to test1

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TEST1
db2 => quit

2.10 其他用户连接数据库

DB2使用操作系统的用户和密码进行认证。连接前设置环境变量DB2INSTANCE即可。

如:

$ export DB2INSTANCE=db2inst1
$ /opt/ibm/db2/V9.7/bin/db2
db2 => connect to test user OSUser1 using OSUser1PW

3 配置DB2

There are two types of configuration files:

  • The database manager configuration file for each DB2 instance.
  • The database configuration file for each individual database.

db2_database_objects_and_cfg_files.gif

Figure 1: Relationship between database objects and configuration files

说明:在DB2中instance可以认为是database manager,它下面可管理多个database(这点和Oracle中instance的概念很不同)。

参考:
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/c0004555.html?cp=SSEPGG_9.7.0/2-2-6
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/r0005181.html?cp=SSEPGG_9.7.0/2-2-6-1&lang=en

3.1 Database Manager Configuration(对应instance)

要查看当前database manager的配置(即instance的配置),可运行命令 db2 get dbm cfg

$ db2 get dbm cfg
              Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

 Database manager configuration release level            = 0x0c00

 Maximum total of files open               (MAXTOTFILOP) = 16000
 CPU speed (millisec/instruction)             (CPUSPEED) = 4.251098e-007
 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+002

 Max number of concurrently active databases     (NUMDB) = 8
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) =

 Default charge-back account           (DFT_ACCOUNT_STR) =

 Java Development Kit installation path       (JDK_PATH) =

 Diagnostic error capture level              (DIAGLEVEL) = 3
 Notify Level                              (NOTIFYLEVEL) = 3
 Diagnostic data directory path               (DIAGPATH) =

 Default database monitor switches
   Buffer pool                         (DFT_MON_BUFPOOL) = OFF
   Lock                                   (DFT_MON_LOCK) = OFF
   Sort                                   (DFT_MON_SORT) = OFF
   Statement                              (DFT_MON_STMT) = OFF
   Table                                 (DFT_MON_TABLE) = OFF
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = OFF
 Monitor health of instance and databases   (HEALTH_MON) = ON

 SYSADM group name                        (SYSADM_GROUP) =
 SYSCTRL group name                      (SYSCTRL_GROUP) =
 SYSMAINT group name                    (SYSMAINT_GROUP) =
 SYSMON group name                        (SYSMON_GROUP) =

 Client Userid-Password Plugin          (CLNT_PW_PLUGIN) =
 Client Kerberos Plugin                (CLNT_KRB_PLUGIN) = IBMkrb5
 Group Plugin                             (GROUP_PLUGIN) =
 GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) =
 Server Plugin Mode                    (SRV_PLUGIN_MODE) = UNFENCED
 Server List of GSS Plugins      (SRVCON_GSSPLUGIN_LIST) =
 Server Userid-Password Plugin        (SRVCON_PW_PLUGIN) =
 Server Connection Authentication          (SRVCON_AUTH) = NOT_SPECIFIED
 Cluster manager                           (CLUSTER_MGR) =

 Database manager authentication        (AUTHENTICATION) = SERVER
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trust all clients                      (TRUST_ALLCLNTS) = YES
 Trusted client authentication          (TRUST_CLNTAUTH) = CLIENT
 Bypass federated authentication            (FED_NOAUTH) = NO

 Default database path                       (DFTDBPATH) = C:

 Database monitor heap size (4KB)          (MON_HEAP_SZ) = AUTOMATIC
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 2048
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = AUTOMATIC
 Backup buffer default size (4KB)            (BACKBUFSZ) = 1024
 Restore buffer default size (4KB)           (RESTBUFSZ) = 1024

 Agent stack size                       (AGENT_STACK_SZ) = 16
 Minimum committed private memory (4KB)   (MIN_PRIV_MEM) = 32
 Private memory threshold (4KB)        (PRIV_MEM_THRESH) = 20000

 Sort heap threshold (4KB)                  (SHEAPTHRES) = 0

 Directory cache support                     (DIR_CACHE) = YES

 Application support layer heap size (4KB)   (ASLHEAPSZ) = 15
 Max requester I/O block size (bytes)         (RQRIOBLK) = 32767
 Query heap size (4KB)                   (QUERY_HEAP_SZ) = 1000

 Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

 Priority of agents                           (AGENTPRI) = SYSTEM
 Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC

 Keep fenced process                        (KEEPFENCED) = YES
 Number of pooled fenced processes         (FENCED_POOL) = AUTOMATIC
 Initial number of fenced processes     (NUM_INITFENCED) = 0

 Index re-creation time and redo index build  (INDEXREC) = RESTART

 Transaction manager database name         (TM_DATABASE) = 1ST_CONN
 Transaction resync interval (sec)     (RESYNC_INTERVAL) = 180

 SPM name                                     (SPM_NAME) = KEON14
 SPM log size                          (SPM_LOG_FILE_SZ) = 256
 SPM resync agent limit                 (SPM_MAX_RESYNC) = 20
 SPM log path                             (SPM_LOG_PATH) =

 NetBIOS Workstation name                        (NNAME) =

 TCP/IP Service name                          (SVCENAME) = db2c_DB2
 Discovery mode                               (DISCOVER) = SEARCH
 Discover server instance                (DISCOVER_INST) = ENABLE

 Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
 Enable intra-partition parallelism     (INTRA_PARALLEL) = NO

 Maximum Asynchronous TQs per query    (FEDERATED_ASYNC) = 0

 No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC
 No. of int. communication channels   (FCM_NUM_CHANNELS) = AUTOMATIC
 Node connection elapse time (sec)         (CONN_ELAPSE) = 10
 Max number of node connection retries (MAX_CONNRETRIES) = 5
 Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

 db2start/db2stop timeout (min)        (START_STOP_TIME) = 10

查看实例配置命令的完整语法格式为:

>>-GET--+-DATABASE MANAGER-+--+-CONFIGURATION-+--+-------------+-><
        +-DB MANAGER-------+  +-CONFIG--------+  '-SHOW DETAIL-'
        '-DBM--------------'  '-CFG-----------'

要修改实例配置,如修改DIAGLEVEL为4,可以运行 db2 update dbm cfg using DIAGLEVEL 4

修改实例配置命令的完整语法格式为:

>>-UPDATE--+-DATABASE MANAGER-+--+-CONFIGURATION-+-------------->
           +-DB MANAGER-------+  +-CONFIG--------+
           '-DBM--------------'  '-CFG-----------'

          .--------------------------------------.
          V                                      |
>--USING----config-keyword--+-value------------+-+-------------->
                            +-value--AUTOMATIC-+
                            +-AUTOMATIC--------+
                            '-MANUAL-----------'

   .-IMMEDIATE-.
>--+-----------+-----------------------------------------------><
   '-DEFERRED--'

实例相关的所有config-keyword可参见:
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/c0060794.html?cp=SSEPGG_9.7.0/2-2-6-6

参考:
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001955.html?cp=SSEPGG_9.7.0/3-6-2-4-46
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001988.html?cp=SSEPGG_9.7.0/3-6-2-4-139

3.2 Database Configuration(对应database)

要查看数据库配置(必须先连接数据库),可以运行命令 db2 get db cfg

查看数据库配置命令的完整语法格式为:

>>-GET--+-DATABASE-+--+-CONFIGURATION-+------------------------->
        '-DB-------'  +-CONFIG--------+
                      '-CFG-----------'

>--+-------------------------+--+-------------+----------------><
   '-FOR----database-alias---'  '-SHOW DETAIL-'

用下面命令可修改数据库配置。

>>-UPDATE--+-DATABASE-+--+-CONFIGURATION-+---------------------->
           '-DB-------'  +-CONFIG--------+
                         '-CFG-----------'

>--+---------------------+-------------------------------------->
   '-FOR--database-alias-'

>--+----------------------------------+------------------------->
   '-DBPARTITIONNUM--db-partition-num-'

          .--------------------------------------.
          V                                      |
>--USING----config-keyword--+-value------------+-+-------------->
                            +-value--AUTOMATIC-+
                            +-AUTOMATIC--------+
                            '-MANUAL-----------'

   .-IMMEDIATE-.
>--+-----------+-----------------------------------------------><
   '-DEFERRED--'

数据库相关的所有config-keyword可参见:
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.config.doc/doc/c0060795.html?cp=SSEPGG_9.7.0/2-2-6-7

参考:
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001954.html?cp=SSEPGG_9.7.0/3-6-2-4-45
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001987.html?cp=SSEPGG_9.7.0/3-6-2-4-138

4 DB2 CLP

4.1 连接和断开数据库

要启动DB2 CLP,可直接在终端中输入db2,会出现DB2 CLP的提示符。

$ db2

## 使用下面命令可以访问一个数据:
db2 => connect to <dbname> [[user <userid>] using <pwd>]
## 例如:以用户名db2svr,密码db2svr,登录到sample数据库。
db2 => connect to sample user db2svr using db2svr

## 断开当前数据库连接
db2 => disconnect current

4.2 查看命令帮助

在命令前加上问号(?)将显示命令的帮助

db2 => ? connect
CONNECT [USER username [{USING password
[NEW new-password CONFIRM confirm-password] |
CHANGE PASSWORD}]]

CONNECT RESET

CONNECT TO database-alias
[IN {SHARE MODE | EXCLUSIVE MODE [ON SINGLE DBPARTITIONNUM]}]
[USER username [{USING password
[NEW new-password CONFIRM confirm-password] |
CHANGE PASSWORD}]]

4.3 执行sql文件

第一步,连接数据库
$ db2 connect to sample user db2svr using db2svr

第二步,执行sql脚本
$ db2 -tvf file.sql

注1:file.sql中的每个SQL语句必须以分号结尾。
注2:-tvf中各个选项的作用:http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.cmd.doc/doc/r0010410.html

第三步,终止数据库连接
$ db2 quit

4.4 查看当前用户下所有数据表

查看当前schema下的所有数据表:
db2 => list tables

4.5 限定字段输出的宽度

用cast函数限定字段输出的宽度。如,限定field1为20个字节宽:
SELECT cast(field1 AS VARCHAR(20)), field2 FROM table1

如果想限定多列的宽度,写起来很麻烦!每个都得加cast

参考:
http://stackoverflow.com/questions/15838018/how-can-i-limit-the-display-length-of-a-field-returned-in-db2-clp-z-os

5 DB2 CLPPlus

6 DB2 system commands

DB2有很多system commands,它们都是独立的程序(在安装目录的子目录bin下),可在终端中启动,不是在DB2 CLP中输入。

完整system commands列表,可参考:http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/com.ibm.db2.luw.admin.cmd.doc-gentopic4.html?cp=SSEPGG_10.5.0/3-6-2-6&lang=en

6.1 db2cc(DB2 Control Center)

Use the Control Center to manage and administer systems, DB2® database instances, DB2 for z/OS® subsystems, databases, and database objects such as tables and views.

Since the IBM® DB2® Control Center tools have been deprecated in DB2 9.7, users have been encouraged to adopt IBM Data Studio and IBM InfoSphere® Optim™ tools for managing DB2 databases.

db2cc.png

Figure 2: DB2 Control Center

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.wn.doc/doc/i0054250.html

6.2 db2ca(DB2 Configuration Assistant)

The Configuration Assistant is a graphical interface that is used to manage DB2® database configuration such as database manager configuration, DB2 registry, node directory, database directory and DCS directory.

This command has been deprecated and might be removed in a future release because the Control Center and its associated components have been deprecated in Version 9.7.

https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0023995.html?cp=SSEPGG_9.7.0/3-6-2-6-16

6.3 db2look(导出DDL)

导出某用户下所有的表的结构到一个文件:
db2look -d database -e -a -i user -w password -o DB_DDL1.sql

导出指定的数据库表 (-t table)的结构到一个文件:
db2look -d database -e -a -t TAB1 -i user -w password -o DB_TAB1_DDL1.sql

7 DB2 Connect

DB2 Connect™ provides connectivity to mainframe and midrange databases from Linux, UNIX, and Windows operating systems.

参考:http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.qb.dbconn.doc/doc/r0059046.html?cp=SSEPGG_10.5.0/6-0&lang=en

7.1 通过DB2 Connect连接到DB2 for z/OS

用DB2 Connect可以连接到大机上的DB2服务器。

参考:
Configuring connections to IBM mainframe database servers: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.qb.dbconn.doc/doc/t0022681.html?lang=en


Author: cig01

Created: <2013-04-28 Sun 00:00>

Last updated: <2015-12-02 Wed 22:30>

Creator: Emacs 25.1.1 (Org mode 9.0.7)