A brief description of the DB2 tablespace. DB2 tablespace simple description 1. new Test Database: [db2inst2 @ localhostshell] $ db2createdatabaseghanondb2inst2db2inst2usingcodesetutf-8territorycncollateusingsystem DB2 tablespace simple description
1. create a test database:
[Db2inst2 @ localhost shell] $ db2 "create database ghan on/db2inst2/db2inst2 using codeset UTF-8 territory cn collate using system"
The DB20000I create database command is successfully completed.
[Db2inst2 @ localhost shell] $ db2 connect to ghan
Database Connection information
Database server = DB2/LINUXX8664 9.7.5
SQL authorization id = DB2INST2
Local database alias = GHAN
[Db2inst2 @ localhost shell] $
II. create a buffer pool:
[Db2inst2 @ localhost shell] $ db2 "create bufferpool bp32k pagesize 32 k"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost shell] $
Db2inst2 @ localhost ~] $ Db2 "SELECT * from syscat. BUFFERPOOLS"
BPNAME BUFFERPOOLID DBPGNAME NPAGES PAGESIZE ESTORE NUMBLOCKPAGES BLOCKSIZE NGNAME
Certificate------------- certificate------------ ------ ------------- ----------- certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Ibmdefabp BP 1--2 4096 N 0 0-
BP32K 2--2 32768 N 0 0-
Two records are selected.
3. create a large table space for database management (DMS)
[Db2inst2 @ localhost shell] $ db2-tsvf create_tabs. SQL
Create large tablespace tbs_data pagesize 32 k managed by database using (file '/db2inst2/data_tb/cont0 '2017 M, File'/db2inst2/data_tb/cont1 '2017 M) EXTENTSIZE 32 prefetchsize automatic bufferpool bp32k no file system caching
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost shell] $ cd/db2inst2/data_tb/
[Db2inst2 @ localhost data_tb] $ ll
Total 205008
-Rw ------- 1 db2inst2 db2inst2 104857600 10-20 08:48 cont0
-Rw ------- 1 db2inst2 db2inst2 104857600 10-20 08:48 cont1
[Db2inst2 @ localhost data_tb] $
1. reset the DMS tablespace size
Alter tablespace tbs_data extend (file '/db2inst2/data_tb/cont0 '2017 M, File'/db2inst2/data_tb/cont1 '2017 M)
4. create a temporary tablespace
[Db2inst2 @ localhost shell] $ db2 "create temporary tablespace tbs_temp pagesize 32 k managed by system using ('/db2inst2/data_tb/tab_temp') bufferpool bp32k"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost shell] $
[Db2inst2 @ localhost tab_temp] $ db2 "create user temporary tablespace tbs_usertemp pagesize 32 k managed by system using ('/db2inst2/data_tb/tab_usertemp') bufferpool bp32k"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost tab_temp] $
5. create automatic tablespace management
[Db2inst2 @ localhost tab_temp] $ db2 "create tablespace tab_data2 initialsize 100 M increasesize 100 m maxsize 10G"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost tab_temp] $
[Db2inst2 @ localhost tab_temp] $ db2 "create tablespace tab_data3 pagesize 32 k bufferpool bp32k"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost tab_temp] $
6. create a DMS tablespace
Create tablespace tab_sp4 managed by database using (FILE '/db2inst2/data_tb/tab_sp4' 100 M)
7. add a database layer to automatically manage storage
[Db2inst2 @ localhost autodb] $ db2 "alter database ghan add storage on '/db2inst2/autodb '"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost autodb] $ db2pd-d ghan-storagepaths
8. modify the DMS tablespace to automatically store and manage the tablespace.
[Db2inst2 @ localhost T0000008] $ db2 "alter tablespace tab_sp4 managed by automatic storage"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost T0000008] $ db2 "alter tablespace tab_sp4 rebalance"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost T0000008] $ db2pd-d ghan-tablespaces
[Db2inst2 @ localhost ~] $ Db2pd-d ghan-tablespaces
Database Partition 0 -- Database GHAN -- Active -- Up 0 days 00:00:05 -- Date 10/22/2015 03:02:24
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe lastconsecpname
0x00002b8000064e400 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 SYSCATSPACE
0x00002b8000064fb80 1 SMS 0000mp 4096 32 Yes 64 1 1 On 2 0 31 TEMPSPACE1
0x00002b800006534c0 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 USERSPACE1
0x00002B8106654C40 3 DMS Large 4096 4 Yes 4 1 1 Off 1 0 3 then oolspace
0x00002B81066563C0 4 DMS Large 32768 32 Yes 64 2 2 Off 2 0 31 TBS_DATA
0x00002b80000657d40 5 SMS 0000mp 32768 32 Yes 32 2 On 1 0 31 TBS_TEMP
0x00002b8000065b00006 SMS UsrTmp 32768 32 Yes 32 2 On 1 0 31 TBS_USERTEMP
0x00002b8000065ebc0 7 DMS Large 4096 32 Yes 64 1 Off 2 0 31 TAB_DATA2
0x00002B8106660540 8 DMS Large 32768 32 Yes 64 2 2 Off 2 0 31 TAB_DATA3
0x00002B8106661EC0 9 DMS Large 4096 32 Yes 64 1 1 Off 2 0 31 TAB_SP4
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped
0x00002b8000064e400 0 24576 24572 22884 0 1688 22884 0x22884 0 No
0x00002b8000064fb80 1 2 2 2 0 0 0 0 0x00000000 0 No
0x00002b800006534c0 2 8192 8160 96 0 8064 96 96 0x00000000 0 No
0x00002B8106654C40 3 8192 8188 152 0 8036 152 0x152 0 No
0x00002B81066563C0 4 6400 6336 96 0 6240 96 96 0x00000000 0 No
0x00002b80000657d40 5 1 1 0 0 0 0x00000000 0 No
0x00002b8000065b00006 1 1 1 0 0 0 0x00000000 0 No
0x00002b8000065ebc0 7 51200 51136 96 0 51040 96 96 0x00000000 0 No
0x00002B8106660540 8 2048 1984 96 0 1888 96 96 0x00000000 0 No
0x00002B8106661EC0 9 256 192 96 0 96 96 96 0x00000000 0 No
Tablespace Autoresize Statistics:
Address Id as ar InitSize IncSize IIP MaxSize LastResize LRF
0x00002b8000064e400 0 Yes 33554432-1 No None No
0x00002b8000064fb80 1 Yes No 0 0 No 0 None No
0x00002b800006534c0 2 Yes 33554432-1 No None No
0x00002B8106654C40 3 Yes 33554432-1 No None No
0x00002B81066563C0 4 No 0 0 No 0 None No
0x00002b80000657d40 5 No 0 No 0 None No
0x00002b8000065b00006 No 0 No 0 None No
0x00002b8000065ebc0 7 Yes 104857600 104857600 No 10737418240 None No
0x00002B8106660540 8 Yes 33554432-1 No None No
0x00002B8106661EC0 9 Yes 786432-1 No None No
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00002b8000064f940 0 0 File 24576 24572 0 0/db2inst2/db2inst2/db2inst2/NODE0000/GHAN/T0000000/C0000000.CAT
0x00002b80000651060 1 0 Path 1 1 0 0/db2inst2/db2inst2/db2inst2/NODE0000/GHAN/T0000001/C0000000.TMP
0x00002b80000651270 1 1 Path 1 1 1 0/db2inst2/autodb/db2inst2/NODE0000/GHAN/T0000001/C0000001.TMP
0x00002B8106654A00 2 0 File 8192 8160 0 0/db2inst2/db2inst2/db2inst2/NODE0000/GHAN/T0000002/C0000000.LRG
0x00002B8106656180 3 0 File 8192 8188 0 0/db2inst2/db2inst2/db2inst2/NODE0000/GHAN/T0000003/C0000000.LRG
0x00002b80000657900 4 0 File 3200 3168-0/db2inst2/data_tb/cont0
0x00002b80000657b10 4 1 File 3200 3168-0/db2inst2/data_tb/cont1
0x00002b80000659220 5 0 Path 1 1-0/db2inst2/data_tb/tab_temp
0x00002b8000065c960 6 0 Path 1 1-0/db2inst2/data_tb/tab_usertemp
0x00002B8106660100 7 0 File 25600 25568 0 0/db2inst2/db2inst2/db2inst2/NODE0000/GHAN/T0000007/C0000000.LRG
0x00002B8106660310 7 1 File 25600 25568 1 0/db2inst2/autodb/db2inst2/NODE0000/GHAN/T0000007/C0000001.LRG
0x00002B8106661A80 8 0 File 1024 992 0 0/db2inst2/db2inst2/db2inst2/NODE0000/GHAN/T0000008/C0000000.LRG
0x00002B8106661C90 8 1 File 1024 992 1 0/db2inst2/autodb/db2inst2/NODE0000/GHAN/T0000008/C0000001.LRG
0x00002b81066634009 0 File 128 96 0 0/db2inst2/db2inst2/db2inst2/NODE0000/GHAN/T0000009/C0000000.LRG
0x00002B8106663610 9 1 File 128 96 1 0/db2inst2/autodb/db2inst2/NODE0000/GHAN/T0000009/C0000001.LRG
9. Test table space quiesce
[Db2inst2 @ localhost ~] $ Db2 quiesce tablespaces FOR table empl share
The DB20000I quiesce tablespaces command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2-tsvf create-tm. SQL
Create table EMPLdd (eno integer, lastname varchar (30), hiredate date, salary integer) IN TAB_SP4
DB21034E this command is treated as SQL
Statement, because it is an invalid "command line processor. In SQL
During processing, it returns:
SQL0290N does not allow access to tablespaces. SQLSTATE = 55039
Tablespace id = 9
Name = TAB_SP4
Type = database management space
Content = all persistent data. Large tablespace.
Status = 0x0001
Explanation:
Paused: SHARE
Total pages = 8448
Available pages = 8384
Used pages = 5504
Available pages = 2880
High water mark (page) = 5504
Page size (in bytes) = 4096
Extended data block size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
Number of pauses = 1
Pause 1:
Tablespace id = 9
OBJECT id = 4
[Db2inst2 @ localhost ~] $ Db2 quiesce tablespaces FOR table empl reset
The DB20000I quiesce tablespaces command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2-tsvf create-tm. SQL
Create table EMPLdd (eno integer, lastname varchar (30), hiredate date, salary integer) IN TAB_SP4
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $
Tablespace id = 9
Name = TAB_SP4
Type = database management space
Content = all persistent data. Large tablespace.
Status = 0x0000
Explanation:
Normal
Total pages = 8448
Available pages = 8384
Used pages = 5568
Available pages = 2816
High water mark (page) = 5568
Page size (in bytes) = 4096
Extended data block size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
[Db2inst2 @ localhost ~] $ Db2 drop table empl
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2 list tablespaces show detail
Tablespace id = 9
Name = TAB_SP4
Type = database management space
Content = all persistent data. Large tablespace.
Status = 0x0000
Explanation:
Normal
Total pages = 8448
Available pages = 8384
Used pages = 160
Available pages = 8224
Large data block size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
[Db2inst2 @ localhost ~] $ Db2 "alter tablespace tab_sp4 lower high water mark"
The DB20000I SQL command is successfully completed.
Tablespace id = 9
Name = TAB_SP4
Type = database management space
Content = all persistent data. Large tablespace.
Status = 0x0000
Explanation:
Normal
Total pages = 8448
Available pages = 8384
Used pages = 160
Available pages = 8224
High water mark (page) = 160
Page size (in bytes) = 4096
Extended data block size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
Water level mark (page) = 5568
Page size (in bytes) = 4096
Expansion
[Db2inst2 @ localhost ~] $ Db2-tsvf showtables
Select substr (tbsp_name, 1024) as TABLESPACE_NAME, substr (tbsp_content_type, 1024) as TABLESPACE_TYPE, sum (Bytes)/as TOTAL_MB, sum (tbsp_used_size_kb)/as USED_MB, sum (tbsp_free_size_kb)/1024 as FREE_MB, tbsp_page_size AS PAGE_SIZE from SYSIBMADM. TBSP_UTILIZATION group by tbsp_name, tbsp_content_type, tbsp_page_size order by 1
TABLESPACE_NAME TABLESPACE_TYPE TOTAL_MB USED_MB FREE_MB PAGE_SIZE
----------------------------------------------------------------------------------------------------------
Syscatspace any 96 89 6 4096
Systoolspace large 32 0 31 4096
TAB_DATA2 LARGE 200 0 199 4096
TAB_DATA3 LARGE 64 3 59 32768
TAB_SP4 LARGE 33 0 32 4096
TBS_DATA LARGE 200 3 195 32768
TBS_TEMP SYSTEMP 0 0 32768
TBS_USERTEMP USRTEMP 0 0 32768
TEMPSPACE1 SYSTEMP 0 0 4096
USERSPACE1 LARGE 32 0 31 4096
10 records are selected.
[Db2inst2 @ localhost ~] $ Db2-tsvf insert. SQL
Insert into empl with dt (ENO) AS (VALUES (1) union all select eno + 1 from dt where eno <500000) select eno, TRANSLATE (CHAR (INTEGER (RAND () * 1000000), case mod (ENO, 4) WHEN 0 THEN 'aeiou' | 'bcdfg' WHEN 1 THEN 'aeiou' | 'hjklm 'WHEN 2 THEN 'aeiou' | 'npqrs' ELSE 'aeiou' |' twxyz 'end, '20140901') as lastname, current date-(RAND () * 1234567890) days as hiredate, INTEGER (10957 + RAND () * 10000) AS SALARY FROM DT
DB21034E this command is treated as SQL
Statement, because it is an invalid "command line processor. In SQL
During processing, it returns:
SQL0289N failed to allocate a new page in the tablespace "TBS_DATA. SQLSTATE = 57011
[Db2inst2 @ localhost ~] $ Db2-tsvf showtables
Select substr (tbsp_name, 1024) as TABLESPACE_NAME, substr (tbsp_content_type, 1024) as TABLESPACE_TYPE, sum (Bytes)/as TOTAL_MB, sum (tbsp_used_size_kb)/as USED_MB, sum (tbsp_free_size_kb)/1024 as FREE_MB, tbsp_page_size AS PAGE_SIZE from SYSIBMADM. TBSP_UTILIZATION group by tbsp_name, tbsp_content_type, tbsp_page_size order by 1
TABLESPACE_NAME TABLESPACE_TYPE TOTAL_MB USED_MB FREE_MB PAGE_SIZE
----------------------------------------------------------------------------------------------------------
Syscatspace any 96 89 6 4096
Systoolspace large 32 0 31 4096
TAB_DATA2 LARGE 200 0 199 4096
TAB_DATA3 LARGE 64 3 59 32768
TAB_SP4 LARGE 33 0 32 4096
TBS_DATA LARGE 200 198 0 32768
TBS_TEMP SYSTEMP 0 0 32768
TBS_USERTEMP USRTEMP 0 0 32768
TEMPSPACE1 SYSTEMP 0 0 4096
USERSPACE1 LARGE 32 0 31 4096
10 records are selected.
[Db2inst2 @ localhost ~] $
Tablespace id = 4
Name = TBS_DATA
Type = database management space
Content = all persistent data. Large tablespace.
Status = 0x0000
Explanation:
Normal
Total pages = 6400
Available pages = 6336
Used pages = 6336
Available pages = 0
High water mark (page) = 6336
Page size (in bytes) = 32768
Extended data block size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
Add a container to the DMS tablespace:
Alter tablespace tbs_data extend (file '/db2inst2/data_tb/cont0 '2017 M, File'/db2inst2/data_tb/cont1 '2017 M)
Http://www.bkjia.com/PHPjc/1071440.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/1071440.htmlTechArticleDB2 tablespace simple description 1. new Test database: [db2inst2 @ localhost shell] $ db2 "create database ghan on/db2inst2/db2inst2 using codeset UTF-8 territory cn collate using system...
