ITPub博客

首页 > 数据库 > 数据库开发技术 > db2-起航

db2-起航

原创 数据库开发技术 作者:zuoqi_71 时间:2014-03-01 13:01:49 0 删除 编辑

oracle,mssql,db2,mysql 既然入了这行就别抱怨了,一路学下去吧
  
4、查看版本补丁
  1. [root@dg1 ~]# db2ls -q -b /opt/ibm/db2/V9.7/
 5、创建实例
1)删除以前实例
  1. [root@dg1 instance]# su - db2inst1
  2.  [db2inst1@dg1 ~]$ db2stop
  3.  [root@dg1 ~]# cd /opt/ibm/db2/V9.7/instance/

  4.  [root@dg1 instance]# ./db2ilist
  5.  db2inst1

  6.  [root@dg1 instance]# ./db2idrop db2inst1
  7.  DBI1070I Program db2idrop completed successfully

 2)清理用户
 

点击(此处)折叠或打开

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

  8. [root@dg1 instance]# userdel -r db2inst1
  9.  [root@dg1 instance]# userdel -r db2fenc1

  10.  [root@dg1 instance]# ll /home
  11.  total 12
  12.  drwx------ 3 db2inst2 db2inst2 4096 Feb 28 15:47 db2inst2
  13.  drwx------ 3 grid oinstall 4096 Sep 2 15:36 grid
  14.  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


 


  

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

lsn: log sequence number
每条数据变化都在日志中对应一个lsn号

pagelsn:数据文件上每个数据页都会记录最后一更新的lsn号
当恢复时,如果日志lsn


minbufflsn
最老的脏块的lsn


lowtranlsn
最老的未提交事务的lsn


min(minbufflsn,lowtranlsn)
选择两者最小的lsn号为崩溃恢复起始点


lsn: log sequence number
每条数据变化都在日志中对应一个lsn号

pagelsn:数据文件上每个数据页都会记录最后一更新的lsn号
当恢复时,如果日志lsn

minbufflsn
最老的脏块的lsn

lowtranlsn
最老的未提交事务的lsn

min(minbufflsn,lowtranlsn)
选择两者最小的lsn号为崩溃恢复起始点

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22193071/viewspace-1097568/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-02-26

  • 博文量
    48
  • 访问量
    143786