BD2安装、管理part01
概述：
主流db2版本： 9.7 10.1 10.5
Db2 express-c express 只能运行在linux和windos平台上。 最大支持2个cpu 4G内存。
Db2 workgroup 能够运行在unix和linux版本，16 cor 64G 内存。
Db2 enterprise 对资源没有限制，支持各种版本
Db2 advanced enterprise 高级功能 多机集群。
检查操作系统版本是否符合db2安装需求 兼柔性列表：
http://www-03.ibm.com/software/products/en/db2enterprise-server-edition/?S_TACT=M1610PPW
LINUX REDHAT 5.9 x64 安装db2
#文件传输使用二进制
解压文件：
运行检查脚本：
[root@dbuser server_t]# ./db2prereqcheck
0.环境信息
os： Red Hat Enterprise Linux Server release 6.0 (Santiago)
DB： v10.5fp1_linuxx64_expc
检查结果包括缺少的安装包其中如下需要注意：
1、部分包缺失，32位包缺失
安装之前一定要先把下面的包直接补充安装一下，默认是不安装的，另外要记得修改配置，直接连32位包也一并安装上。RH6.0以后，好多32包都默认不安装的，会引发不少问题。
echo 'multilib_policy=all' >> /etc/yum.conf
yum install -y glibc* libstdc*
2、关闭iptables和selinux
service iptables stop
sed -i 's/=enforcing/=disabled/g' /etc/selinux/config
(或 vi /etc/selinux/config
[root@TSMClt expc]# cat /etc/selinux/config |grep SELINUX=
# SELINUX= can take one of these three values:
SELINUX=disabled
)
3、安装之前，做一下db2precheck，检查日志
4、libpam.so问题，不一定遇得上，看人品。
db2setup安装过程中，报错内容如下：
Validating "/lib/libpam.so*" ...
DBT3514W The db2prereqcheck utility failed to find the following 32-bit libr
ary file: "/lib/libpam.so*".
WARNING : Requirement not matched.
解决办法如下：
[root@TSMClt expc]# find / -name libpam.so*
/usr/lib64/libpam.so
/lib64/libpam.so.0.82.2
/lib64/libpam.so.0
很明显，缺失32位包
[root@TSMClt expc]# rpm -qf /usr/lib64/libpam.so
pam-devel-1.1.1-4.el6.x86_64
[root@TSMClt expc]# rpm -qf /lib64/libpam.so.0.82.2
pam-1.1.1-4.el6.x86_64
[root@TSMClt expc]# rpm -qf /lib64/libpam.so.0
pam-1.1.1-4.el6.x86_64
yum install -y pam*
DB2 LINUX 安装：
1、 配置yum：
[root@DB2 mnt]# rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
[root@DB2 mnt]# vi /etc/yum.
yum.conf yum.repos.d/
[root@DB2 mnt]# vi /etc/yum.repos.d/local.repo
[root@DB2 mnt]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.3 (Santiago)
[root@DB2 mnt]# export DISPLAY=172.16.3.1:0.0
[root@DB2 /]# mount -o loop /tmp/rhel-server-6.3-x86_64-dvd.iso /yumyuan
[root@DB2 /]# cd yumyuan/
[root@DB2 yumyuan]# ls
EFI RELEASE-NOTES-es-ES.html RELEASE-NOTES-si-LK.html
EULA RELEASE-NOTES-fr-FR.html RELEASE-NOTES-ta-IN.html
GPL RELEASE-NOTES-gu-IN.html RELEASE-NOTES-te-IN.html
HighAvailability RELEASE-NOTES-hi-IN.html RELEASE-NOTES-zh-CN.html
images RELEASE-NOTES-it-IT.html RELEASE-NOTES-zh-TW.html
isolinux RELEASE-NOTES-ja-JP.html repodata
LoadBalancer RELEASE-NOTES-kn-IN.html ResilientStorage
media.repo RELEASE-NOTES-ko-KR.html RPM-GPG-KEY-redhat-beta
Packages RELEASE-NOTES-ml-IN.html RPM-GPG-KEY-redhat-release
README RELEASE-NOTES-mr-IN.html ScalableFileSystem
RELEASE-NOTES-as-IN.html RELEASE-NOTES-or-IN.html Server
RELEASE-NOTES-bn-IN.html RELEASE-NOTES-pa-IN.html TRANS.TBL
RELEASE-NOTES-de-DE.html RELEASE-NOTES-pt-BR.html
RELEASE-NOTES-en-US.html RELEASE-NOTES-ru-RU.html
[root@DB2 yumyuan]# cd S
ScalableFileSystem/ Server/
[root@DB2 yumyuan]# cd Server/
[root@DB2 Server]# ls
listing repodata TRANS.TBL
[root@DB2 Server]# cd ..
[root@DB2 yumyuan]# cd Packages/
[root@DB2 Packages]# pwd
/yumyuan/Packages
[root@DB2 Packages]# vi /etc/yum.repos.d/local.repo
[DISK]
name=DISK
baseurl=file:///yumyuan/Server
gpgchekc=0
enable=1
开始安装前的检查：
[root@DB2 server_t]# ./db2prereqcheck
检查内容省略安装屏幕报错修复。
开始图像化安装
LINUX命令行安装
Db2软件安装：
修改安装路径：
数据库软件安装完毕。
Db2 实例安装
创建用户组：
[root@DB2 server_t]# groupadd db2adm2
[root@DB2 server_t]# groupadd db2fen2
[root@DB2 server_t]#
[root@DB2 server_t]# useradd -d /home/db2inst2 -m db2inst2 -g db2adm2
[root@DB2 server_t]# useradd -d /home/db2fenc2 -m db2fenc2 -g db2fen2
[root@DB2 server_t]# cd /home
[root@DB2 home]# ls
dasusr1 db2fenc1 db2fenc2 db2inst1 db2inst2 lost+found
[root@DB2 home]# passwd db2inst2
Changing password for user db2inst2.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@DB2 home]# passwd db2fenc2
Changing password for user db2fenc2.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@DB2 home]# passwd db2inst2
Changing password for user db2inst2.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@DB2 home]# passwd db2fenc2
Changing password for user db2fenc2.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
安装实例：
[root@DB2 instance]# ./db2icrt -u db2fenc2 db2inst2
DBI1446I The db2icrt command is running.
DB2 installation is being initialized.
Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)
Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end
Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end
Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end
Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end
The execution completed successfully.
For more information see the DB2 installation log at "/tmp/db2icrt.log.40464".
DBI1070I Program db2icrt completed successfully.
检查是否安装成功：
[root@DB2 instance]# su - db2inst2
[db2inst2@DB2 ~]$
[db2inst2@DB2 ~]$ db2start
SQL8007W There are "89" day(s) left in the evaluation period for the product
"DB2 Advanced Enterprise Server Edition". For evaluation license terms and
conditions, refer to the License Agreement document located in the license
directory in the installation path of this product. If you have licensed this
product, ensure the license key is properly registered. You can register the
license by using the db2licm command line utility. The license key can be
obtained from your licensed product CD.
05/18/2016 01:33:40 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
在实例里创建数据库
[db2inst2@DB2 ~]$ db2 create db wudi
DB20000I The CREATE DATABASE command completed successfully.
查看数据库：
[db2inst2@DB2 ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = WUDI
Database name = WUDI
Local database directory = /home/db2inst2
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
连接数据库：
[db2inst2@DB2 ~]$ db2 connect to wudi
Database Connection Information
Database server = DB2/LINUXX8664 10.5.7
SQL authorization ID = DB2INST2
Local database alias = WUDI
[db2inst2@DB2 ~]$ db2 connect reset 断开连接
查看数据库表空间：
[db2inst2@DB2 ~]$ db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Norma
l
到此db2数据库安装完成。
理解实例
1、 实例为运行数据库提供运行时的环境。
2、 实例是一组后台进程和共享内存组成的。
3、 实例是逻辑的数据库是物理的，实例为数据量提供了运行时的环境。
4、 一个db2可以创建多个实例，每个实例都有自己的配置文件，每个实例都有自己的独立共享内存和进程结构。
实例管理
实例与操作系统用户的管理：
1、 Windows下不用创建用户默认实例名为DB2
2、 Linux&unix 下需要创建 实例名和用户名一样的实例，原因在于需要使用home路径下的与实例相关的目录信息。
创建实例的命令：
db2icrt
[db2inst2@DB2 instance]$ pwd
/opt/ibm/db2/v10.5two/instance
[db2inst2@DB2 instance]$ ls db2icrt
db2icrt –u fencxx instance
db2icrt 命令解释：
-u 指定受防护的用户。 -Fenced 用户 用于db2数据库所使用的地址空间之外运行用户自己定义的函数和存储过程，和开发有关，为必须创建。
3、 在linux和unix下创建instance需要root 并需要先创建用户和组
[root@DB2 instance]# ./db2icrt -u db2fenc2 db2inst2
4 、windows下创建实例：
db2icrt instance_name
DB2实例启动：
[db2inst2@DB2 ~]$ db2start
SQL8007W There are "89" day(s) left in the evaluation period for the product
"DB2 Advanced Enterprise Server Edition". For evaluation license terms and
conditions, refer to the License Agreement document located in the license
directory in the installation path of this product. If you have licensed this
product, ensure the license key is properly registered. You can register the
license by using the db2licm command line utility. The license key can be
obtained from your licensed product CD.
05/18/2016 01:33:40 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
连接断开实例：
连接db2 attach to instance_name
linux 下 一个用户一个instance 多用于远程连接。
Windows 下 用于连接到instance_name
[db2inst2@DB2 ~]$ db2 detach
DB20000I The DETACH command completed successfully.
停止实例
需要具有相应权限：
实例用户具有最高权限。
db2stop 当有应用程序连接进来时 使用db2stop无法停止需要使用：db2stop force
实例参数：
1、 查看实例参数：
[db2inst2@DB2 ~]$ db2 get dbm cfg
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Database manager configuration release level = 0x1000
CPU speed (millisec/instruction) (CPUSPEED) = 1.692567e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
Max number of concurrently active databases (NUMDB) = 32
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) = /home/db2inst2/sqllib/java/jdk64
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /home/db2inst2/sqllib/db2dump/
Current member resolved DIAGPATH = /home/db2inst2/sqllib/db2dump/
Alternate diagnostic data directory path (ALT_DIAGPATH) =
Current member resolved ALT_DIAGPATH =
Size of rotating db2diag & notify logs (MB) (DIAGSIZE) = 0
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) = OFF
SYSADM group name (SYSADM_GROUP) = DB2ADM2
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) =
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 =
Database manager authentication (AUTHENTICATION) = SERVER
Alternate authentication (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
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) = /home/db2inst2
Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(90)
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Global instance memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(868587)
Member instance memory (4KB) = GLOBAL
Agent stack size (AGENT_STACK_SZ) = 1024
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) = 65535
Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10
Priority of agents (AGENTPRI) = SYSTEM
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
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) = DB21
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
TCP/IP Service name (SVCENAME) =
Discovery mode (DISCOVER) = SEARCH
Discover server instance (DISCOVER_INST) = ENABLE
SSL server keydb file (SSL_SVR_KEYDB) =
SSL server stash file (SSL_SVR_STASH) =
SSL server certificate label (SSL_SVR_LABEL) =
SSL service name (SSL_SVCENAME) =
SSL cipher specs (SSL_CIPHERSPECS) =
SSL versions (SSL_VERSIONS) =
SSL client keydb file (SSL_CLNT_KEYDB) =
SSL client stash file (SSL_CLNT_STASH) =
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(4096)
No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
Inter-node comm. parallelism (FCM_PARALLELISM) = 1
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
WLM dispatcher enabled (WLM_DISPATCHER) = NO
WLM dispatcher concurrency (WLM_DISP_CONCUR) = COMPUTED
WLM dispatcher CPU shares enabled (WLM_DISP_CPU_SHARES) = NO
WLM dispatcher min. utilization (%) (WLM_DISP_MIN_UTIL) = 5
Communication buffer exit library list (COMM_EXIT_LIST) =
Current effective arch level (CUR_EFF_ARCH_LVL) = V:10 R:5 M:0 F:7 I:0 SB:0
Current effective code level (CUR_EFF_CODE_LVL) = V:10 R:5 M:0 F:7 I:0 SB:0
Keystore type (KEYSTORE_TYPE) = NONE
Keystore location (KEYSTORE_LOCATION) =
2、 修改参数：
[db2inst2@DB2 ~]$ db2 update dbm cfg using START_STOP_TIME 20
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
3、重置参数
[db2inst2@DB2 ~]$ db2 reset dbm cfg
DB20000I The RESET DATABASE MANAGER CONFIGURATION command completed
successfully.
删除实例
删除实例需具有root权限，执行删除前需要停止实例
命令：db2idrop
[root@DB2 instance]# pwd
/opt/ibm/db2/v10.5two/instance
[root@DB2 instance]# ./db2idrop
db2idrop db2idrop_local
[root@DB2 instance]# ./db2idrop
db2idrop db2idrop_local
[root@DB2 instance]# ./db2idrop db2inst2
查看实例
[db2inst2@DB2 ~]$ db2ilist
db2inst2
实例开机自启动
[db2inst2@DB2 ~]$ db2iauto -on db2inst2
DB2的环境变量
变量优先级为已最后遇见的环境变量为主。
查看已经设置的实例变量：
I 是实例级变量 ，g 全局的实例变量。
实例目录
实例目录一旦创建不能更改，linux&unix下位于user_home/sqllib下.
[db2inst2@DB2 db2dump]$ ls
clientrecords db2diag.log db2eventlog.000 db2inst2.nfy DIAG0000 events stmmlog
[db2inst2@DB2 db2dump]$ pwd
/home/db2inst2/sqllib/db2dump
Db2diag.log 实例诊断日志文件。
Db2system 数据库配置文件
管理服务器
DAS 用于同时管理多个实例。
创建DB2数据库
1、 数据库由实例创建和管理数据库中包括一到多个表空间，表存储在表空间里。
Db2内部存储结构：
1、 数据库的最小存储单元是数据页（datapage）。
2、 区:连续的数据页整数倍组成。（extent）
3、 表空间由一到多个容器组成。
4、 表索引存放在表空间中
5、 容器位于底层的物理存储
6、 表空间是物理存储于逻辑存储的统一
7、 容器可以是目录、文件、或者设备
创建数据库详解
命令创建数据库
CREATE DATABASE语法树
Read syntax diagramSkip visual syntax diagram
>>-CREATE--+-DATABASE-+----------------------------------------->
'-DB-------'
>----database-name--+-----------------------------+------------><
+-AT DBPARTITIONNUM-----------+
'-| Create Database options |-'
Create Database options
.-AUTOMATIC STORAGE--YES-.
|--+------------------------+----------------------------------->
'-AUTOMATIC STORAGE--NO--'
>--+---------------------------------------------+-------------->
| .-,---------. |
| V | |
'-ON----+-path--+-+--+----------------------+-'
'-drive-' '-DBPATH ON--+-path--+-'
'-drive-'
>--+-----------------------+------------------------------------>
'-ALIAS--database-alias-'
>--+----------------------------------------------+------------->
'-USING CODESET--codeset--TERRITORY--territory-'
>--+-----------------------------------------------+------------>
| .-SYSTEM---------------------. |
'-COLLATE USING--+-COMPATIBILITY--------------+-'
+-IDENTITY-------------------+
+-IDENTITY_16BIT-------------+
+-UCA400_NO------------------+
+-UCA400_LSK-----------------+
+-UCA400_LTH-----------------+
+-language-aware-collation---+
+-locale-sensitive-collation-+
'-NLSCHAR--------------------'
1、 AUTOMATIC STORAGE NO | YES 指定自动存储打开或关闭。默认是打开的。
[db2inst2@DB2 db2inst2]$ mkdir db01
[db2inst2@DB2 db2inst2]$ mkdir db02
[db2inst2@DB2 db2inst2]$ mkdir db04
[db2inst2@DB2 db2inst2]$ mkdir db03
[db2inst2@DB2 db2inst2]$ mkdir db05
[db2inst2@DB2 db2inst2]$ ls
db01 db02 db03 db04 db05 NODE0000
第一种情况：
数据库路径 和自动存储路径 都为/db01
[db2inst2@DB2 ~]$ db2 create db db01 on /db01 自动存储路径 为默认yes
DB20000I The CREATE DATABASE command completed successfully.
[db2inst2@DB2 ~]$ cd /db01/
[db2inst2@DB2 db01]$ ls
db2inst2
[db2inst2@DB2 db01]$ cd db2inst2/
[db2inst2@DB2 db2inst2]$ ls
NODE0000
[db2inst2@DB2 db2inst2]$ cd NODE0000/
DB01/ SQL00001/ sqldbdir/
[db2inst2@DB2 db2inst2]$ cd NODE0000/
[db2inst2@DB2 NODE0000]$ ls
DB01 SQL00001 sqldbdir
[db2inst2@DB2 NODE0000]$ cd DB01/
[db2inst2@DB2 DB01]$ ls
T0000000 T0000001 T0000002
[db2inst2@DB2 DB01]$
第二种情况不使用自动存储特性：数据库路径 /db02
[db2inst2@DB2 DB01]$ db2 create db db02 automatic storage no on /db02
DB20000I The CREATE DATABASE command completed successfully.
[db2inst2@DB2 DB01]$ cd /db02/
[db2inst2@DB2 db02]$ ls
db2inst2
[db2inst2@DB2 db02]$ cd db2inst2/NODE0000/
SQL00001/ sqldbdir/
路径下没有DB02的自动存储路径。
第三种情况：数据库路径为/db03 自动存储路径为/db04
[db2inst2@DB2 db02]$ db2 create db db03 on /db04 dbpath on /db03
数据库路径
[db2inst2@DB2 NODE0000]$ cd /db03
[db2inst2@DB2 db03]$ ls
db2inst2
[db2inst2@DB2 db03]$ cd db2inst2/NODE0000/
[db2inst2@DB2 NODE0000]$ ls
SQL00001 sqldbdir
数据库自动存储路径：
[db2inst2@DB2 db04]$ cd /db04
[db2inst2@DB2 db04]$ ls
db2inst2
[db2inst2@DB2 db04]$ cd db2inst2/
[db2inst2@DB2 db2inst2]$ ls
NODE0000
[db2inst2@DB2 db2inst2]$ cd NODE0000/
[db2inst2@DB2 NODE0000]$ ls
DB03
其他参数：
ALIAS database-alias
在database directory中，数据库的别名。如果没指定，就用数据库名称。
USING CODESET codeset
指定数据进入数据库所使用的编码集。创建数据库后，不能修改。
TERRITORY territory
指定数据库进入数据库所使用的区域标示符或本地标示符。创建数据库后，不能修改。code set和territory的组合，必须是有效的。
COLLATE USING
指定数据库使用的比较顺序规则。创建数据库后，不能修改。
在unicode的数据库中，catalog tables和views总是用IDENTITY collation，无论数据库指定了哪种collation。在non-unicode数据库里，catalog tables和views使用数据库的collation创建。
COMPATIBILITY
DB2 version 2的比较规则。一些已经加强了。这个选项指定了之前的版本会用使用。
IDENTITY
字符串是逐字节比较的。
IDENTITY_16BIT
CESU-8 (Compatibility Encoding Scheme for UTF-16: 8-Bit(www.unicode.org)，只能在unicode数据库使用。
UCA400_NO
The UCA (Unicode Collation Algorithm) collation sequence that is based on the Unicode Standard version 4.0.0 with normalization implicitly set to ON. 只能在unicode数据库使用。
UCA400_LSK
The UCA (Unicode Collation Algorithm) collation sequence based on the Unicode Standard version 4.0.0 but will sort Slovak characters in the appropriate order. 只能在unicode数据库使用。
UCA400_LTH
The UCA (Unicode Collation Algorithm) collation sequence that is based on the Unicode Standard version 4.0.0 but will sort all Thai characters according to the Royal Thai Dictionary order.只能在unicode数据库使用。
language-aware-collation
只能在unicode数据库使用。对于non-unicode数据库，是基于system cllation。字符串必须是SYSTEM_codepage_territory的格式，否则创建数据库会失败。
当create database在V9上使用，这个选项不会被使用。默认，unicode数据库在v9上会事SYSTEM collation。
locale-sensitive-collation
只能在unicode数据库使用。
NLSCHAR
只能用在Thai code page(CP874)，否则出错。为指定的set/territory设置独特的collation。
SYSTEM
这是创建数据库时，collation的默认选项。对于non-unicode数据库，collation是基于territory。对于unicode数据库，根据客户的code set和territory，对应一个language-aware-collation，如何没有合适的language-aware可用，会使用IDENTITY。
示例
[db2inst2@DB2 /]$ db2 create db db04 on /db05 dbpath on /db06 pagesize 8192 dft_extent_sz 8
DB20000I The CREATE DATABASE command completed successfully.
[db2inst2@DB2 /]$ db2 list db directory
System Database Directory
Number of entries in the directory = 5
Database 1 entry:
Database alias = WUDI
Database name = WUDI
Local database directory = /home/db2inst2
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 2 entry:
Database alias = DB01
Database name = DB01
Local database directory = /db01
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 3 entry:
Database alias = DB02
Database name = DB02
Local database directory = /db02
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 4 entry:
Database alias = DB03
Database name = DB03
Local database directory = /db03
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
Database 5 entry:
Database alias = DB04
Database name = DB04
Local database directory = /db06
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
图形化向导创建数据库：
数据库目录
1和2 互为备份，一个实例可以创建多个数据库，一个数据库只能属于一个实例由sqlinslk实现。
特定成员目录：
Db2表空间管理
1、 目录表空间
存储DB2的系统编目，即数据字典
默认名称：syscatspace 不能缺少的表空间
2、 临时表空间
用于存储分组排序连接重组创建索引等操作的中间结果，至少有一个临时表空间默认
tempspace1
3、 用户临时表空间
默认不会创建。
4、 用户表空间
默认userspace1 如何创建用户不指定表空间将使用默认表空间
存储类型：
数据库支持三种类型的表空间：
1、 系统管理的表空间 sms
2、 数据库管理的表空间dms
3、 自动管理的表空间 Ams
三中存储的表空间可以共存在同一数据库。
SMS表空间：
DMS表空间：
AMS:表空间：
表空间页设置：
创建表空间
创建表空间参数：
LARGE :创建大型表空间
REGUIAR 创建常规表空间
SYSTEM TEMPORARY 创建临时表空间
User TEMPORARY 用户临时表空间
Tablespace_name 指定表空间的名字，不能以sys开头。
4k 8k 16 32k
MANAGED BY 指定表空间管理方式：
EXTENTSIZE 指定区的大小
PREFETCHSIZE 指定预取页的大小，调优的重要参数。一般选择自动
实例：
1、 创建表空间最简单命令“
[db2inst2@DB2 ~]$ db2 create tablespace wudispace01
DB20000I The SQL command completed successfully.
2、 创建一个大型表空间：
[db2inst2@DB2 ~]$ db2 create large tablespace wudilarge01DB20000I The SQL command completed successfully.
3、 创建用户临时表空间：
[db2inst2@DB2 ~]$ db2 create user temporary tablespace wuditempuser01
DB20000I The SQL command completed successfully.
4、 创建一个8K页大小的表空间：
[db2inst2@DB2 ~]$ db2 create tablespace wudipagelimit01 pagesize 8K
DB20000I The SQL command completed successfully.
5、 创建一个数据库管理的表空间
[db2inst2@DB2 ~]$ db2 "create tablespace wudidms managed by database using (file '/db05/db2inst2/NODE0000/dms01.dbf' 10M,file '/db05/db2inst2/NODE0000/dms02.dbf' 10M)"
DB20000I The SQL command completed successfully.
查看表空间：
[db2inst2@DB2 NODE0000]$ db2 list tablespaces show detail
Tablespaces for Current Database
Tablespace ID = 0 #表空间id
Name = SYSCATSPACE #表空间名字
Type = Database managed space #表空间类型共3种
Contents = All permanent data. Regular table space.
State = 0x0000 #表空间状态 0000正常
Detailed explanation:
Normal
Total pages = 16384
Useable pages = 16380
Used pages = 14976
Free pages = 1404
High water mark (pages) = 14976
Page size (bytes) = 8192
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 8192
Extent size (pages) = 8
Prefetch size (pages) = 8
Number of containers = 1
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4088
Used pages = 24
Free pages = 4064
High water mark (pages) = 24
Page size (bytes) = 8192
Extent size (pages) = 8
Prefetch size (pages) = 8
Number of containers = 1
Tablespace ID = 3
Name = WUDILARGE01
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4088
Used pages = 24
Free pages = 4064
High water mark (pages) = 24
Page size (bytes) = 8192
Extent size (pages) = 8
Prefetch size (pages) = 8
Number of containers = 1
Tablespace ID = 4
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4092
Used pages = 104
Free pages = 3988
High water mark (pages) = 104
Page size (bytes) = 8192
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 5
Name = WUDITEMPUSER01
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 8192
Extent size (pages) = 8
Prefetch size (pages) = 8
Number of containers = 1
Tablespace ID = 6
Name = WUDIPAGELIMIT01
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 4096
Useable pages = 4088
Used pages = 24
Free pages = 4064
High water mark (pages) = 24
Page size (bytes) = 8192
Extent size (pages) = 8
Prefetch size (pages) = 8
Number of containers = 1
Tablespace ID = 7
Name = WUDIDMS
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 2560
Useable pages = 2544
Used pages = 24
Free pages = 2520
High water mark (pages) = 24
Page size (bytes) = 8192
Extent size (pages) = 8
Prefetch size (pages) = 16
Number of containers = 2
管理维护表空间
查看表空间的容器：
[db2inst2@DB2 ~]$ db2 list tablespace containers for 7
Tablespace Containers for Tablespace 7
Container ID = 0
Name = /db05/db2inst2/NODE0000/dms01.dbf
Type = File
Container ID = 1
Name = /db05/db2inst2/NODE0000/dms02.dbf
Type = File
[db2inst2@DB2 ~]$ db2 list tablespace containers for 6
Tablespace Containers for Tablespace 6
Container ID = 0
Name = /db05/db2inst2/NODE0000/DB04/T0000006/C0000000.LRG
Type = File
[db2inst2@DB2 ~]$ db2 list tablespace containers for 5
Tablespace Containers for Tablespace 5
Container ID = 0
Name = /db05/db2inst2/NODE0000/DB04/T0000005/C0000000.UTM
Type = Path #system manager
7和6为数据管理 5为系统管理。
表空间修改,添加和扩展容器：
1、 添加容器
[db2inst2@DB2 ~]$ db2 "alter tablespace wudidms add (file '/db05/db2inst2/NODE0000/dms03.dbf' 10M,file '/db05/db2inst2/NODE0000/dms04.dbf' 10M)"
2、 扩展容器
Extend 和resize
[db2inst2@DB2 NODE0000]$ db2 "alter tablespace wudidms resize (file '/db05/db2inst2/NODE0000/dms03.dbf' 5M,file '/db05/db2inst2/NODE0000/dms04.dbf' 5M)"
DB20000I The SQL command completed successfully.
[db2inst2@DB2 NODE0000]$ db2 list tablespace containers for 7 show detail
Tablespace Containers for Tablespace 7
Container ID = 0
Name = /db05/db2inst2/NODE0000/dms01.dbf
Type = File
Total pages = 1280
Useable pages = 1272
Accessible = Yes
Container ID = 1
Name = /db05/db2inst2/NODE0000/dms02.dbf
Type = File
Total pages = 1280
Useable pages = 1272
Accessible = Yes
Container ID = 2
Name = /db05/db2inst2/NODE0000/dms03.dbf
Type = File
Total pages = 640
Useable pages = 632
Accessible = Yes
Container ID = 3
Name = /db05/db2inst2/NODE0000/dms04.dbf
Type = File
Total pages = 640
Useable pages = 632
Accessible = Yes
3、 删除容器DROP
[db2inst2@DB2 NODE0000]$ db2 "alter tablespace wudidms drop (file '/db05/db2inst2/NODE0000/dms03.dbf' ,file '/db05/db2inst2/NODE0000/dms04.dbf' )"
DB20000I The SQL command completed successfully.
[db2inst2@DB2 NODE0000]$ db2 list tablespace containers for 7 show detail
Tablespace Containers for Tablespace 7
Container ID = 0
Name = /db05/db2inst2/NODE0000/dms01.dbf
Type = File
Total pages = 1280
Useable pages = 1272
Accessible = Yes
Container ID = 1
Name = /db05/db2inst2/NODE0000/dms02.dbf
Type = File
Total pages = 1280
Useable pages = 1272
Accessible = Yes
4、 重命名表空间
[db2inst2@DB2 NODE0000]$ db2 rename tablespace wudidms to wudidmsrn
DB20000I The SQL command completed successfully.
5、 删除表空间
Db2 drop tablespaces wudidms
多温度存储器：
1、 多温度就是华为的分级存储。
[db2inst2@DB2 ~]$ db2 connect db04
SQL0104N An unexpected token "db04" was found following "CONNECT". Expected
tokens may include: "END-OF-STATEMENT". SQLSTATE=42601
[db2inst2@DB2 ~]$ db2 connect to db04
Database Connection Information
Database server = DB2/LINUXX8664 10.5.7
SQL authorization ID = DB2INST2
Local database alias = DB04
[db2inst2@DB2 ~]$ db2pd -d db04 -storagegroup
Database Member 0 -- Database DB04 -- Active -- Up 0 days 00:06:57 -- Date 2016-05-20-12.53.38.730075
Storage Group Configuration:
Address SGID Default DataTag Name
0x00007FA0DE72D820 0 Yes 0 IBMSTOGROUP
Storage Group Statistics:
Address SGID State Numpaths NumDropPen
0x00007FA0DE72D820 0 0x00000000 1 0
Storage Group Paths:
Address SGID PathID PathState PathName
0x00007FA0DE751000 0 0 InUse /db05
只有一个默认存储组。
创建存储组
[db2inst2@DB2 ~]$ db2 "create stogroup stg01 on '/db07'"
DB20000I The SQL command completed successfully.
创建表空间并指定存储器组：
[db2inst2@DB2 ~]$ db2 create tablespace stg01 using stogroup stg01
DB20000I The SQL command completed successfully.
[db2inst2@DB2 ~]$ db2 list tablespace
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"TABLESPACE". Expected tokens may include: "CONTAINERS". SQLSTATE=42601
[db2inst2@DB2 ~]$ db2 list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 3
Name = WUDILARGE01
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 4
Name = SYSTOOLSPACE
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 5
Name = WUDITEMPUSER01
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 6
Name = WUDIPAGELIMIT01
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 7
Name = WUDIDMSRN
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 8
Name = STG01
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
查看表空间与存储的对应关系：
[db2inst2@DB2 ~]$ db2 "select tbspace, sgname,sgid from syscat.tablespaces"
删除存储器
无法删除存储器组与表空间关联的存储器组;
[db2inst2@DB2 ~]$ db2 drop stogroup stg01
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0478N The statement failed because one or more dependencies exist on the
target object. Target object type: "STOGROUP". Name of an object that is
dependent on the target object: "STG01". Type of object that is dependent on
the target object: "TABLESPACE". SQLSTATE=42893
先更改表空间的存储器组在删除：
[db2inst2@DB2 ~]$ db2 alter tablespace stg01 using stogroup IBMSTOGROUP
word 文件下载：http://www.aixchina.net/Document/detail/tid/210245
后续持续更新.................................................................
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞0作者其他文章
评论 0 · 赞 0
评论 0 · 赞 0
评论 0 · 赞 0
评论 0 · 赞 0
评论 0 · 赞 0
添加新评论0 条评论