db2-起航

最新推荐文章于 2020-12-30 05:50:11 发布

cuicang5610

最新推荐文章于 2020-12-30 05:50:11 发布

阅读量135

点赞数

oracle，mssql，db2,mysql 既然入了这行就别抱怨了，一路学下去吧

4、查看版本补丁

[root@dg1 ~]# db2ls -q -b /opt/ibm/db2/V9.7/

5、创建实例
1）删除以前实例

[root@dg1 instance]# su - db2inst1
[db2inst1@dg1 ~]$ db2stop
[root@dg1 ~]# cd /opt/ibm/db2/V9.7/instance/
[root@dg1 instance]# ./db2ilist
db2inst1
[root@dg1 instance]# ./db2idrop db2inst1
DBI1070I Program db2idrop completed successfully

2）清理用户

点击(此处)折叠或打开

[root@dg1 instance]# ll /home
total 20
drwx------ 3 db2fenc1 db2fadm1 4096 Feb 25 10:34 db2fenc1
drwxr-xr-x 8 db2inst1 db2inst1 4096 Mar 1 10:30 db2inst1
drwx------ 3 db2inst2 db2inst2 4096 Feb 28 15:47 db2inst2
drwx------ 3 grid oinstall 4096 Sep 2 15:36 grid
drwx------ 4 oracle oinstall 4096 Feb 26 11:09 oracle
[root@dg1 instance]# userdel -r db2inst1
[root@dg1 instance]# userdel -r db2fenc1
[root@dg1 instance]# ll /home
total 12
drwx------ 3 db2inst2 db2inst2 4096 Feb 28 15:47 db2inst2
drwx------ 3 grid oinstall 4096 Sep 2 15:36 grid
drwx------ 4 oracle oinstall 4096 Feb 26 11:09 oracle

3）添加用户
useradd db2inst1
passwd db2inst1

useradd db2fenc1
passwd db2fenc1

4）创建实例
1）create instance
[root@dg1 instance]# ./db2icrt -u db2fenc1 db2inst1
DBI1070I Program db2icrt completed successfully.

[root@dg1 instance]# ./db2ilist
db2inst1

2)change prot and tcpip
[root@dg1 instance]# su - db2inst1

[db2inst1@dg1 ~]$ db2 update dbm cfg using svcename 53000
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

[db2inst1@dg1 ~]$ db2set DB2COMM=TCPIP

[db2inst1@dg1 ~]$ db2set -all
[i] DB2COMM=TCPIP
[g] DB2FCMCOMM=TCPIP4
[g] DB2SYSTEM=dg1
[g] DB2INSTDEF=db2inst1

3)restart instance

[db2inst1@dg1 ~]$ db2 get instance
The current database manager instance is: db2inst1

[db2inst1@dg1 ~]$ db2stop
03/01/2014 11:18:13 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.

[db2inst1@dg1 ~]$ db2start
03/01/2014 11:18:28 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.

6、创建sample样本库
[db2inst1@dg1 ~]$ db2sampl

Creating database "SAMPLE"...
Connecting to database "SAMPLE"...
Creating tables and data in schema "DB2INST1"...
Creating tables with XML columns and XML data in schema "DB2INST1"...

'db2sampl' processing complete.

[db2inst1@dg1 ~]$ db2 connect to sample

Database Connection Information

Database server        = DB2/LINUXX8664 9.7.6
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE

[db2inst1@dg1 ~]$ db2list tables
-bash: db2list: command not found

[db2inst1@dg1 ~]$ db2 list tables

Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
ACT DB2INST1 T 2014-03-01-11.23.07.685755

7、远程访问
1）db2 本机另外用户如何访问已创建实例？？？？？

2）如何访问其他机器的数据库

3）查看本地node,db

8、创建自定义数据库
1）需求定义
dbname= zqdb
storage=auto
storage path=/home/db2inst1/zqdb/ast
db path=/home/db2inst1/zqdb
codeset= utf-8
territory=cn

[root@dg1 ~]# mkdir -p /home/db2inst1/zqdb/ast

db2 "create database zqdb automatic storage yes
on /home/db2inst1/zqdb/ast
dbpath on /home/db2inst1/zqdb
using codeset utf-8
territory cn
collate using system"

9、创建bufferpool
[db2inst1@dg1 NODE0000]$ db2 connect to zqdb

Database Connection Information

Database server        = DB2/LINUXX8664 9.7.6
SQL authorization ID   = DB2INST1
Local database alias   = ZQDB

[db2inst1@dg1 NODE0000]$ db2 "create bufferpool bp32k size 10000 pagesize 32k"

10、创建表空间
1）DMS管理表空间
[db2inst1@dg1 ~]$ mkdir -p /home/db2inst1/zqdb/dts/

db2 "create large tablespace tbs_data pagesize 32k managed by database
using (file '/home/db2inst1/zqdb/dts/tbs_data01' 100M,file '/home/db2inst1/zqdb/dts/tbs_data02' 100M)
extentsize 32 prefetchsize automatic
bufferpool bp32k no file system caching"

2）增加数据文件(通过add增加容器会在容器间进行数据rebalance)

db2 "alter tablespace tbs_data add (file '/home/db2inst1/zqdb/dts/tbs_data03' 100M)"

3)begin new stripe set不会在容器间进行rebanlance,当已有容器用完后，再使用新加的容器
db2 "alter tablespace tbs_data begin new stripe set (file '/home/db2inst1/zqdb/dts/tbs_data04' 10M)"

[db2inst1@dg1 ~]$ ll -h /home/db2inst1/zqdb/dts/tbs_data04
-rw------- 1 db2inst1 db2inst1 10M Mar 1 16:32 /home/db2inst1/zqdb/dts/tbs_data04

4）使用extend 扩容数据文件大小，制定的大小是增加量
db2 "alter tablespace tbs_data extend (file '/home/db2inst1/zqdb/dts/tbs_data04' 20M)"

[db2inst1@dg1 ~]$ ll -h /home/db2inst1/zqdb/dts/tbs_data04
-rw------- 1 db2inst1 db2inst1 30M Mar 1 16:36 /home/db2inst1/zqdb/dts/tbs_data04

5）查询表空间信息
[db2inst1@dg1 ~]$ db2 list tablespaces show detail

[db2inst1@dg1 ~]$ db2 list tablespace containers for 4 show detail

Tablespace Containers for Tablespace 4

Container ID                         = 0
Name                                 = /home/db2inst1/zqdb/dts/tbs_data01
Type                                 = File
Total pages                          = 3200
Useable pages                        = 3168
Accessible                           = Yes
Container ID                         = 1
Name                                 = /home/db2inst1/zqdb/dts/tbs_data02
Type                                 = File
Total pages                          = 3200
Useable pages                        = 3168
Accessible                           = Yes
Container ID                         = 2
Name                                 = /home/db2inst1/zqdb/dts/tbs_data03
Type                                 = File
Total pages                          = 3200
Useable pages                        = 3168
Accessible                           = Yes
Container ID                         = 3
Name                                 = /home/db2inst1/zqdb/dts/tbs_data04
Type                                 = File
Total pages                          = 960
Useable pages                        = 928
Accessible                           = Yes

11、创建临时表空间(sms 表空间)
db2 "create temporary tablespace tbs_temp pagesize 32k managed by system using ('/home/db2inst1/zqdb/dts/tbs_temp') bufferpool bp32k"

12、创建auto management tablespace
[db2inst1@dg1 ~]$ db2 "create tablespace tbs_index pagesize 32k bufferpool bp32k"
DB20000I The SQL command completed successfully.

[db2inst1@dg1 ~]$ db2 "create tablespace tbs_data2 initialsize 10M increasesize 10M maxsize 10G"
DB20000I The SQL command completed successfully.

[db2inst1@dg1 ~]$ db2 list tablespace containers for 6 show detail

Tablespace Containers for Tablespace 6

Container ID                         = 0
Name                                 = /home/db2inst1/zqdb/ast/db2inst1/NODE0000/ZQDB/T0000006/C0000000.LRG
Type                                 = File
Total pages                          = 1024
Useable pages                        = 992
Accessible                           = Yes

[db2inst1@dg1 ~]$ db2 list tablespace containers for 7 show detail

Tablespace Containers for Tablespace 7

Container ID                         = 0
Name                                 = /home/db2inst1/zqdb/ast/db2inst1/NODE0000/ZQDB/T0000007/C0000000.LRG
Type                                 = File
Total pages                          = 2560
Useable pages                        = 2528
Accessible                           = Yes

14、数据库文件说明

[db2inst1@dg1 SQL00001]$ pwd
/home/db2inst1/zqdb/db2inst1/NODE0000/SQL00001

[db2inst1@dg1 SQL00001]$ ll
total 4496
drwxr-x--- 3 db2event
-rw-r----- 1 db2rhist.asc  db history file,使用list histroy浏览
-rw-r----- 1 db2rhist.bak
-rw------- 1 SQLBP.1    bufferpool control file,使用alter bufferpool控制
-rw------- 1 SQLBP.2    bufferpool control file,使用alter bufferpool控制
-rw------- 1 SQLDBCON
-rw------- 1 SQLDBCONF   参数配置文件，使用db2 get db cfg/update db cfg
-rw-r----- 1 SQLINSLK
-rw------- 1 SQLOGCTL.LFH.1
-rw------- 1 SQLOGCTL.LFH.2
drwxr-x--- 2 SQLOGDIR
-rw------- 1 SQLOGMIR.LFH  db log control file,无法浏览和人工修改
-rw------- 1 SQLSGF.1
-rw------- 1 SQLSGF.2
-rw------- 1 SQLSPCS.1   tablespace control file,使用list/alter tablespace控制
-rw------- 1 SQLSPCS.2   tablespace control file,使用list/alter tablespace控制
-rw-r----- 1 SQLTMPLK

15、数据库编码
[db2inst1@dg1 SQL00001]$ db2 get db cfg for zqdb| grep code
Database code page                                      = 1208
Database code set                                       = utf-8
Database country/region code                            = 86

version 9.5以前，如果不指定，默认使用system locale code
version 9.5以后，默认为utf-8,

16、表空间是否使用了auto storage
db2 get snapshot for tablespaces on zqdb| more

Tablespace name                            = TBS_DATA
Tablespace ID                            = 4
Tablespace Type                          = Database managed space
Tablespace Content Type                  = All permanent data. Large table space.
Tablespace Page size (bytes)             = 32768
Tablespace Extent size (pages)           = 32
Automatic Prefetch size enabled          = Yes
Buffer pool ID currently in use          = 2
Buffer pool ID next startup              = 2
Using automatic storage                  = No

Tablespace name                            = TBS_DATA2
Tablespace ID                            = 7
Tablespace Type                          = Database managed space
Tablespace Content Type                  = All permanent data. Large table space.
Tablespace Page size (bytes)             = 4096
Tablespace Extent size (pages)           = 32
Automatic Prefetch size enabled          = Yes
Buffer pool ID currently in use          = 1
Buffer pool ID next startup              = 1
Using automatic storage                  = Yes

17、buffer pool和tablespace的关系
bufferpool从tablespace的容器中取得数据，缓存在内存中，
提高数据库的处理速度和利用率

18、表空间的查看
db2 list tablespaces
列出所有表空间，可以区分出sms和dms,但不能区分出auto storage

[db2inst1@dg1 SQL00001]$ db2 list tablespaces show detail
Tablespace ID                        = 0
Name                                 = SYSCATSPACE
Type                                 = Database managed space
Contents                             = All permanent data. Regular table space.
State                                = 0x0000
Detailed explanation:       Normal

查看表空间状态
normal 0x0
backup pending 0x20

db2pd -d zqdb -tablespaces
列出表空间使用情况，列出数据文件位置

db2 get snapshot for tablespaces on zqdb
通过Using automatic storage = Yes|NO 判断auto storage

sysibmadm.snaptbsp
sysibmadm.snapcontainer
两个view更容易通过脚本管理

20、db2diag.log
[db2inst1@dg1 SQLOGDIR]$ db2 get dbm cfg| grep -i diag
Diagnostic error capture level (DIAGLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump/
Alternate diagnostic data directory path (ALT_DIAGPATH) =

23、数据库目录删除了，再次建立同名数据库会怎样？

1）create 时回报
SQL1005N The database alias "testdb1" already exists in either the local
2）首先从db catalog 中uncatalog
[db2inst1@dg1 ~]$ db2 uncatalog db testdb1
3)再次create就ok了

24、schema?

schema是姓，对象是名，shema.对象组成了一个完整的对象名称

在oracle中schema已一个数据库用户表表示 username.对象
在db2中没有数据库用户的概念，schema同样是一个对象，需要create

1)显示create schema zq
[db2inst1@dg1 ~]$ db2 "create schema zq"
DB20000I The SQL command completed successfully.

2)隐式create schema szq
[db2inst1@dg1 ~]$ db2 "create table szq.table1 (col1 char(10))"
DB20000I The SQL command completed successfully.

3)查询数据库中的所有schema
db2 "select schemaname from syscat.schemata"

SCHEMANAME
--------------------------------------------
NULLID
SQLJ
SYSCAT          系统视图，数据库对象信息
SYSFUN
SYSIBM      数据库字典表
SYSIBMADM       v9引入，系统管理视图
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT         统计视图，为优化器提供信息
SYSTOOLS
SZQ
ZQ

4)查看当前模式下的表即登录用户db2inst1
[db2inst1@dg1 ~]$ db2 list tables

Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------

0 record(s) selected.

5)某个schema用户的表
[db2inst1@dg1 ~]$ db2 list tables for schema zq

Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
ZQTABLE1 ZQ T 2014-03-02-10.29.12.457009

1 record(s) selected.
6）所有schema用户的表
[db2inst1@dg1 ~]$ db2 list tables for all

Table/View                      Schema          Type Creation time
------------------------------- --------------- ----- --------------------------
ATTRIBUTES                      SYSCAT          V     2014-03-01-13.48.40.786141
AUDITPOLICIES                   SYSCAT          V     2014-03-01-13.48.42.375567

25、看表结构
1)describe table
[db2inst1@dg1 ~]$ db2 describe table zq.zqtable1

Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL1                            SYSIBM    VARCHAR                     10     0 Yes

2)describe indexes
[db2inst1@dg1 ~]$ db2 describe indexes for table zq.zqtable1 show detail

3)schema的表明及tablespace
[db2inst1@dg1 ~]$ db2 "select substr(tabschema,1,32) as tabschema,substr(tabname,1,32) as tabname, tbspaceid from syscat.tables
where tabschema='ZQ'"

TABSCHEMA TABNAME TBSPACEID
-------------------------------- -------------------------------- ---------
ZQ ZQTABLE1 7

db2 "select tabschema,tabname, tbspaceid from syscat.tables
where tabschema='zq'"

26、视图

一个复杂sql的别名

27、自增字段
generated always as identity
用户不能指定值

generated by default as identity
用户能指定值

28、lob

clob 存大string,一般超过32k时使用，否则使用varchar(32k)
bloc 存大二进制数据，一般超过32k时使用，否则使用varchar(32k) for bit data
一般将lob单独存在一张独立的表中，制定单独的表空间开启file system caching

inline lob（新概念）

29、数据迁移

1）export
db2 "export to emp.del of del messages emp.msg select * from emp"

[db2inst1@dg1 ~]$ cat emp.del
"000010","CHRISTINE","I","HAAS","A00","3978",19950101,"PRES ",18,"F",19630824,+0152750.00,+0001000.00,+0004220.00

2) 修改字符串定界符
db2 "export to emp2.del of del modified by chardel'' messages emp2.msg select * from emp"

[db2inst1@dg1 ~]$ cat emp2.del
'000010','CHRISTINE','I','HAAS','A00','3978',19950101,'PRES ',18,'F',19630824,+0152750.00,+0001000.00,+0004220.00

chardel 字符串定界符
coldel 字段定界符
timestampformat 日期字段格式
用户要确保导出的数据中不包含分割符，否则导入时会异常

3）导出包含lob
db2 "export to emp_resume.del of del lobs to /home/db2inst1/lobs modified by lobsinfile select * from emp_resume"
如果想每个lob都单独存在一个文件中使用modified by lobsinsepfiles

4）import
[db2inst1@dg1 ~]$ db2 "import from emp.del of del messages emp.log insert into emp"

Number of rows read         = 42
Number of rows skipped      = 0
Number of rows inserted     = 0
Number of rows updated      = 0
Number of rows rejected     = 42
Number of rows committed    = 42

违反了主键约束，所以42条都被拒绝了
insert 用于追加，不改变原有数据
insert_update ,与表中有主键冲突时update,否则insert
replace 首先delete表中数据，然后insert
commitcount 每导入n条commit，或有db自动判断commit
restartcount/skipcount n 从n+1行开始导入
import时会在目标上加x锁（排他锁）不允许其他应用访问
用allow no access在目标加ix锁，允许其他应用读写，只能用在inert和insert_update

5) 使用分隔符chardel'',allow write access ,commitcount automatic,insert_update

db2 "import from emp2.del of del modified by chardel'' allow write access
commitcount automatic insert_update into emp"

Number of rows read         = 42
Number of rows skipped      = 0
Number of rows inserted     = 0
Number of rows updated      = 42
Number of rows rejected     = 0
Number of rows committed    = 42

6)导入大对象
db2 "import from emp_resume.del of del lobs from /home/db2inst1/lobs modified by lobsinfile insert_update into emp_resume"

Number of rows read         = 8
Number of rows skipped      = 0
Number of rows inserted     = 0
Number of rows updated      = 8
Number of rows rejected     = 0
Number of rows committed    = 8

7)load,db2look，db2move,db2dart
另找时间研究

30、db log