DB2 LUW

解压安装包后，可以用图形界面方式(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%2F3-6-2-6-6&lang=en

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

$ 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。

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

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

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

# 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.

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

$ 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

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

如：

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

要查看当前 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%2F2-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%2F3-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%2F3-6-2-4-139

要查看数据库配置（必须先连接数据库），可以运行命令 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%2F2-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%2F3-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%2F3-6-2-4-138

要启动 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

在命令前加上问号（?）将显示命令的帮助

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}]]

第一步，连接数据库
$ 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=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0010410.html

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

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

用 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

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.

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

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%2F3-6-2-6-16

导出某用户下所有的表的结构到一个文件：
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

用 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