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%2F2-12&lang=en
2. 安装和启动
参考:
http://www.2cto.com/database/201205/130463.html
IBM DB2 Express-C for Linux HOWTO
http://www-01.ibm.com/software/data/db2/express-c/download.html
2.1. 安装 DB2
解压安装包后,可以用图形界面方式(db2setup)和命令行交互方式(db2_install)安装。这里仅介绍命令行交互方式的安装。
推荐用 root 用户安装 DB2,否则无法定制安装路径(db2_install 的-b 选项仅在 root 用户下才效)。
$ sudo su # ./db2_install -b /opt/ibm/db2/V9.7/
再按提示一步一步安装即可。
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. 创建实例
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.
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%2F2-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%2F2-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%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
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%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
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=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0010410.html
第三步,终止数据库连接
$ db2 quit
4.4. 查看当前用户下所有数据表
查看当前 schema 下的所有数据表:
db2 => list tables
4.5. 限定字段输出的宽度
用 cast 函数限定字段输出的宽度。如,限定 field1 为 20 个字节宽:
SELECT cast(field1 AS VARCHAR(20)), field2 FROM table1
如果想限定多列的宽度,写起来很麻烦!每个都得加 cast
5. DB2 CLPPlus
DB2 的 CLPPlus 与 Oracle 的 SQL*Plus 兼容。可以在终端中输入 clpplus
启动 DB2 CLPPlus。
参考:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.swg.im.dbclient.clpplus.doc/doc/c0053823.html?cp=SSEPGG_10.5.0%2F3-6-2-1&lang=en
http://book.51cto.com/art/201112/310729.htm
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%2F3-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.
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.
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.
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