BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]DB2 存储结构(db2dart分析)

2016-02-23
DB2

“通过db2dart数据块/索引块,了解DB2存储结构”

摘要

通过db2dart数据块/索引块,了解DB2存储结构

测试环境:AIX7.1 64bit/DB2 V10.1FP5/db2inst1/sample


1.表结构,表数量,表所在的表空间和表ID,表存储信息的获取

1.1 表结构和数量

*当前schema是db2inst1(以下都省略)

db2 describe table DEPARTMENT

db2inst1:/dbhome/db2inst1$ db2 describe table DEPARTMENT

								Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
DEPTNO                          SYSIBM    CHARACTER                    3     0 No    
DEPTNAME                        SYSIBM    VARCHAR                     36     0 No    
MGRNO                           SYSIBM    CHARACTER                    6     0 Yes   
ADMRDEPT                        SYSIBM    CHARACTER                    3     0 No    
LOCATION                        SYSIBM    CHARACTER                   16     0 Yes   

  5 record(s) selected.

db2inst1:/dbhome/db2inst1$ db2 "select count(*) from department"

1          
-----------
		 14

  1 record(s) selected.

db2inst1:/dbhome/db2inst1$ 

1.2 表所在的表空间id和表id

db2 "select tableid,tbspaceid from syscat.tables where tabname='DEPARTMENT'"

db2inst1:/dbhome/db2inst1$ db2 "select tableid,tbspaceid from syscat.tables where tabname='DEPARTMENT'"

TABLEID TBSPACEID
------- ---------
	  5         2

  1 record(s) selected.

db2inst1:/dbhome/db2inst1$ 

1.3 表存储的信息

db2dart $Database /demp /tsi 2 /oi 5

db2inst1:/dbhome/db2inst1/testdir$ db2dart sample /demp /tsi 2 /oi 5

		 The requested DB2DART processing has completed successfully!
				  Complete DB2DART report found in: SAMPLE.RPT
db2inst1:/dbhome/db2inst1/testdir$ ls -ltr
total 72
-rw-r-----    1 db2inst1 db2inst1      13709 Feb 23 23:59 SAMPLE.BAK
-rw-------    1 db2inst1 db2inst1       1145 Feb 24 00:01 DART.INF
-rw-r-----    1 db2inst1 db2inst1      13484 Feb 24 00:01 SAMPLE.RPT
db2inst1:/dbhome/db2inst1/testdir$ 

备注:db2dart最好在offline情况下使用,保证数据一致性(online情况会不一致或者执行不了)

[DB2 LUW] db2dart 実行手順 (Linux/UNIX 版)

http://www-01.ibm.com/support/docview.wss?uid=swg21617135

db2inst1:/dbhome/db2inst1$ db2dart sample /demp /tsi 2 /oi 5

FYI: An active connection to the database has been detected.
	 False errors may be reported.  
	 Deactivate all connections and re-run to verify.

Warning: The database state is not consistent.
		 False errors may be reported.

Warning: Errors reported about reorg rows may be due to the inconsistent state of the database.
				  DB2DART Processing completed with warning(s)!
				  Complete DB2DART report found in: SAMPLE.RPT
db2inst1:/dbhome/db2inst1$ 	



db2inst1:/dbhome/db2inst1/testdir$ cat SAMPLE.RPT

______________________________________________________________________________



		_______                    DART                   _______ 

   D a t a b a s e   A n a l y s i s   a n d   R e p o r t i n g   T o o l

						   IBM    DB2    6000





______________________________________________________________________________

DART (V10.1)  Report:
2016-02-24-00.01.35.342688

			Database Name: SAMPLE
			Report name: SAMPLE.RPT
			Old report back-up: SAMPLE.BAK
			Database Subdirectory: /db/db2inst1/db2inst1/NODE0000/SQL00002/MEMBER0000
			Operational Mode: Database Inspection Only (INSPECT)

______________________________________________________________________________
------------------------------------------------------------------------------


Action option: DEMP 
Table-object-ID: 5; Tablespace-ID: 2

Creating the agent environment...

DART formatted EMP page dump:
-----------------------------


   Traversing extent map for:
   Object ID: 5
   Table space: 2

   Pool specific info:
   -------------------
   Name             =   USERSPACE1
   Pool extent size =   32   	----------------------- `extend size 32`
   # of containers  =   1

   Container names:
	  /db/db2inst1/db2inst1/NODE0000/SAMPLE/T0000002/C0000000.LRG


   Object specific mapping info:
   -----------------------------
   DAT extent anchor: 160
   Traversing extent map for object type: 0
	  Tablespace ID: 2, Tablespace Seed: 2, Object: 5 EMP page class: 64,
	  EMP pool page: 160, # entries: 1
	  Page LSN = 0000000000FA3E74
	  Pool relative page #'s :
		192 					----------------------- `160+32`
	  Tablespace ID: 2, Tablespace Seed: 2, Object: 5 EMP page class: 64,
	  EMP pool page: 161, # entries: 0
	  Page LSN = 0000000000FA3E28
......
	  Tablespace ID: 2, Tablespace Seed: 2, Object: 5 EMP page class: 64,
	  EMP pool page: 191, # entries: 0
	  Page LSN = 0000000000FA3E28
	  Pool relative page #'s :

   INX extent anchor: 224
   Traversing extent map for object type: 1
	  Tablespace ID: 2, Tablespace Seed: 2, Object: 5 EMP page class: 65,
	  EMP pool page: 224, # entries: 1
	  Page LSN = 0000000000FA57F7
	  Pool relative page #'s :
		256 				----------------------- `224+32`
	  Tablespace ID: 2, Tablespace Seed: 2, Object: 5 EMP page class: 65,
	  EMP pool page: 225, # entries: 0
	  Page LSN = 0000000000FA57AB
....
	  Pool relative page #'s :
	  Tablespace ID: 2, Tablespace Seed: 2, Object: 5 EMP page class: 65,
	  EMP pool page: 255, # entries: 0
	  Page LSN = 0000000000FA57AB
	  Pool relative page #'s :

   XDA extent anchor: 0

   LF extent anchor: 0

   LOB extent anchor: 0

   LOBA extent anchor: 0

   BKM extent anchor: 0



					 ______________________________________

		 The requested DB2DART processing has completed successfully!
					 All operation completed without error;
				   no problems were detected in the database.
					 ______________________________________

				  Complete DB2DART report found in: SAMPLE.RPT

	_______    D A R T    P R O C E S S I N G    C O M P L E T E    _______
db2inst1:/dbhome/db2inst1/testdir$ 

题外话题:

db2 INSPECT CHECK TABLESPACE NAME USERSPACE1 RESULTS KEEP inspect_userspace1.insf

db2inspf /dblogs/db2dump/db2inst1/inspect_userspace1.insf inspect_userspace1.format

INSPECT文件在 DIAGPATH 目录下(db2 get dbm cfg grep -i DIAGPATH)。

/dblogs/db2dump/db2inst1/inspect_userspace1.insf


2.导出对象表的数据(/DDEL选项)

/DDEL选项说明

/DDEL Dumps formatted table data in delimited ASCII format.

根据1的结果 department的信息是:

Table ID or name, 5 or DEPARTMENT

tablespace ID, 2

first page, 0

num of pages: 99999999 (尽可能大的数据)

参考资料

参考资料

db2dart -h

Inspect actions:
/DB (default) Inspects entire database.
/T Inspects one or more tables. (See notes 1, 3, 13)
/TSF Inspects only the table space files and containers.
/TSC Inspects the table space constructs of one or more
table spaces (but does not inspect tables).
/TS Inspects one or more table spaces and their tables.
(/TSC and /TS require a table space ID or a list of table
space IDs to be specified. See notes 1 and 2.)
/ATSC Inspects constructs of all table spaces (but not their tables).

Data format actions:
/DD Dumps formatted table data. (See notes 1, 4, 13, 15)
/DM Dumps formatted block map data. (See notes 1, 4, 13, 15)
/DI Dumps formatted index data. (See notes 1, 4, 12, 15)
/DXA Dumps formatted xda data in ASCII. (See notes 1, 4, 13, 15) /DXH Dumps formatted xda data in Hex. (See notes 1, 4, 13, 15)
/DP Dumps pages in hex format. (See notes 1, 6, 13)
/DTSF Dumps formatted table space file information.
/DEMP Dumps formatted EMP information for a DMS table.
(See notes 1, 3, 13)
/DDEL Dumps formatted table data in delimited ASCII format.
(See note 13, 15)
/DHWM Dumps high water mark information. (See notes 1, 2, 14)
/LHWM Suggests ways of lowering high water mark.
(See notes 1, 7, 14)

Repair actions:
Make sure the database is offline for these actions.
/MI Marks index object as invalid.
(Database must be offline. See notes 1, 5)
/ETS Extends the table limit in a 4K DMS table space, if possible. (This action requires a table space id. See notes 1, 2)
/RHWM Reduces high water mark through empty SMP extents.
(See notes 1, 2)

Change state actions:
Make sure the database is offline for this action.
/CHST Change a state of the database.

Input value options:
/OI object-id Specifies the object ID.
For the /T, a comma-separated list of up to 64
object IDs can be specified. If the corresponding
/TSI parameter contains more than one input ID,
only the first ID is used. Duplicate IDs are
skipped. Logical ID can be specified for the
/T parameter.
/TN table-name Specifies the table name.
/TSI tablespace-id Specifies the table space ID.
For the /TS and /TSC, a comma-separated list of
up to 64 physical table space IDs can be specified. Duplicate IDs are skipped.
/ROW sum Identifies whether L/F descriptors, LOB descriptors and control information should be checked.
(1) Checks control information in rows.
(2) Checks long field and LOB descriptors.
(see note 8)
/PS number Specifies the page number to start with.
(When using the /DP action or working with a
pool-relative tablespace, you may suffix
the page number with ‘p’ for pool relative.)
/NP number Specifies the number of pages.
/V Y/N Specifies whether or not to use verbose option.
(Y) Verbose.
(N) No verbose. /RPT path The path to place report output file (optional).
/RPTN file-name The name of the report file (optional).
/SCR Y/M/N Specifies the type of screen output, if any.
(Y) Produces normal screen output.
(M) Produces minimal screen output.
(N) Produces no screen output.
/RPTF Y/E/N Specifies the type of report file output, if any. (Y) Produces normal output.
(E) Sends only error information to report file.
(N) Produces no report file output.
/ERR Y/N/E Specifies the type of log to produce in DART.INF, if any.
(Y) Produces a normal log in DART.INF file.
(default)
(N) Minimizes output to log DART.INF file.
(E) Minimizes DART.INF file and screen output.
Only error information is logged. /WHAT DBBP OFF/ON Specifies the database backup pending state.
(OFF) Off state.
(ON) On state.
/QCK [sum] Specifies a quick option.
The default /QCK value is 1, the same as when
you specify /QCK 1. (see note 8)
Valid values are:
(1) Applies only to the /DB, /T, and /TS actions.
Inspects only page 0 of the DAT objects
and partially inspects the index objects
(does not inspect BMP, LOB, LF objects and
does not traverse the entirety of the DAT
or INX objects).
(2) Applies only to the /DB, /T, /TS, /DD, /DI,
/DM, /DEMP, /DDEL, /DXA, and /DXH actions.
Skips system catalog table look up on
non-partitioned database environments and on
the catalog partition of partitioned database
environments. This option has no effect on
non-catalog partitions of partitioned database
environments. Not applies to the applicable
actions above when /TN with a table name is
specified, or if /OI and /TSI with logical IDs
are specified.
(4) Applies only to the /T, /TS, and /TSC actions.
For /TS, /TSC, skips inspection of the special
system catalog tables. For /T, skips inspection of the system catalog table space constructs.
(8) Applies only to /T and /TS actions.
For /T, skips inspection of all container files. For /TS, inspects only container files that
are associated with the specified table space.
/TYP Specifies the type of object. Valid values are:
(DAT) Object type is DAT.
(INX) Object type is INDEX.
(BKM) Object type is BMP.

Notes:
1- For actions that require additional input values for identifying the data to act on, the input values can be specified as arguments along with the
action. If values are not specified you will be prompted for input values. This does not apply for actions /DDEL. For this, you will be prompted for the required input values.
2- Actions /TSC, /TS, /ETS, /DHWM and /RHWM require 1 input value - the
table space ID.
/TSC and /TS can also take a list of table space IDs as input.
3- Actions /T and /DEMP require two input values consisting of
table space ID, and either of table object ID or table name.
/T can also take a list of table object IDs as input.
4- Actions /DD, /DM, /DXA, DXH and /DI require five input values consisting
of either table object ID or table name, table space ID, page number to
start with, number of pages, and verbose choice.
5- Action /MI requires two input values consisting of table space ID (/TSI)
and index object ID (/OI). These values can be found in syscat.indexes as tbspaceid and index_objectid unless the index is partitioned.
For partitioned indexes, the /MI action uses indpartitionobjectid and
indpartitiontbspaceid from syscat.indexpartitions as the input to the
index object ID (/OI) and table space ID (/TSI) for a specific partition. 6- For DMS table spaces, action /DP requires three input values consisting of table space ID, page number to start with, and number of pages.
For SMS table spaces, action /DP requires five input values consisting of table space ID, object ID, page number to start with, number of pages,
and object type.
7- Action /LHWM requires a table space ID and the number of pages for the
desired high water mark after lowering it. 8- For value options where unique values identify different choices for
the option, sum up the values to get the combination of choices.
9- Default location for report output file is the current directory in a
non-MPP environment, and in the diagnostic directory in a MPP environment. 10- The scope of db2dart is single node.
11- In a MPP environment, you can use db2_all to invoke db2dart at all DB2
logical nodes in a single invocation.
12- For nonpartitioned indexes on a partitioned table, the /DI action uses
index_objectid and tbspaceid from syscat.indexes as the input to the
index object ID (/OI) and table space ID (/TSI). For partitioned indexes it uses partitionobjectid and tbspaceid from syscat.datapartitions as
the input to /OI and /TSI for indexes on a specific partition.
13- For partitioned tables, the /DD, /DM, /DEMP, /DDEL, /DP, /DXA, /DXH
actions use partitionobjectid and tbspaceid from syscat.datapartitions
as the input to the table object ID (/OI) and table space ID (/TSI)
for a specific partition. The table name option (/TN) is not supported
for these actions. The /T action supports the table name or logical table object ID when use with logical table space ID to check the entire table, and also supports using partitionobjectid and tbspaceid from
syscat.datapartitions as the input to /OI and /TSI to check a specific
partition.
14- In general, db2dart requests to be run when the database is offline.
However for /DHWM and /LHWM actions, this request is not strict.
The report can be generated without database being offline, but results
will vary depending on how much write/update activity has occurred
recently (less activity implies more reliable results).
15- Starting with DB2 v9.7, new tablespaces use reclaimable storage, and
thus may only be accessed in an object-relative manner. As a result,
the ‘p’ suffix for the /PS option can only be used with the /DP action
for these tablespaces.

DB2 V10.5的资料如下:



上一篇 Benchmark-TPC

Comments