DB2 Table Space Simple description
First, the new test library:
[Db2inst2@localhost shell]$ DB2 "CREATE Database Ghan on/db2inst2/db2inst2 using codeset Utf-8 territory CN collate using System
db20000i CREATE DATABASE Command completed successfully.
[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]$
Second, new buffer pool:
[Db2inst2@localhost shell]$ DB2 "Create Bufferpool bp32k pagesize 32k"
Db20000i SQL Command completed successfully.
[Db2inst2@localhost shell]$
Db2inst2@localhost ~]$ DB2 "SELECT * from SYSCAT. Bufferpools "
Bpname bufferpoolid dbpgname npages PAGESIZE ESTORE numblockpages BLOCKSIZE ngname
--------------------------------------------------------------------------------------------------------------- ----------------- ------------ ---------------------------------------------------------------------------------- ---------------------------------------------- ----------- ----------- ------ ------------- ----------- ------------- --------------------------------------------------------------------------------------------------------------- ----
IBMDEFAULTBP 1--2 4096 N 0 0-
bp32k 2--2 32768 N 0 0-
2 records have been selected.
Iii. New database Management (DMS) Large table space
[Db2inst2@localhost shell]$ DB2-TSVF Create_tabs.sql
Create large tablespace tbs_data pagesize 32k managed by database using (file '/db2inst2/data_tb/cont0 ' 100M, file '/db2in St2/data_tb/cont1 ' 100M) extentsize prefetchsize automatic bufferpool bp32k no file system caching
Db20000i SQL Command completed successfully.
[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 DMS Table space size
Alter tablespace tbs_data extend (file '/db2inst2/data_tb/cont0 ' 100M, file '/db2inst2/data_tb/cont1 ' 500M)
Iv. New temporary table space
[Db2inst2@localhost shell]$ DB2 "Create temporary tablespace tbs_temp pagesize 32k managed by system using ('/db2inst2/dat A_tb/tab_temp ') Bufferpool bp32k "
Db20000i SQL Command completed successfully.
[Db2inst2@localhost shell]$
[Db2inst2@localhost tab_temp]$ DB2 "Create user temporary tablespace tbs_usertemp pagesize 32k managed by system using ('/ Db2inst2/data_tb/tab_usertemp ') Bufferpool bp32k "
Db20000i SQL Command completed successfully.
[Db2inst2@localhost tab_temp]$
V. New Automatic Management table space
[Db2inst2@localhost tab_temp]$ DB2 "Create tablespace tab_data2 initialsize 100M increasesize 100M MAXSIZE 10G"
Db20000i SQL Command completed successfully.
[Db2inst2@localhost tab_temp]$
[Db2inst2@localhost tab_temp]$ DB2 "Create tablespace tab_data3 pagesize 32k bufferpool bp32k"
Db20000i SQL Command completed successfully.
[Db2inst2@localhost tab_temp]$
Vi. New DMS Management table space
CREATE tablespace tab_sp4 MANAGED by DATABASE USING (FILE '/DB2INST2/DATA_TB/TAB_SP4 ' 100M)
Vii. Adding a database tier to automatically manage storage
[Db2inst2@localhost autodb]$ DB2 "ALTER DATABASE Ghan add storage on '/db2inst2/autodb '"
Db20000i SQL Command completed successfully.
[Db2inst2@localhost autodb]$ db2pd-d ghan-storagepaths
Viii. Modify DMS table space to automatically store the management table space
[Db2inst2@localhost t0000008]$ DB2 "ALTER tablespace tab_sp4 MANAGED by AUTOMATIC STORAGE"
Db20000i SQL Command completed successfully.
[Db2inst2@localhost t0000008]$ DB2 "alter tablespace TAB_SP4 rebalance"
Db20000i SQL Command completed successfully.
[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 LASTCONSECPG Name
0x00002b810664e400 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 Syscatspace
0x00002b810664fb80 1 SMS systmp 4096 1 1 on 2 0 TEMPSPACE1
0X00002B81066534C0 2 DMS Large 4096 1 1 Off 1 0 USERSPACE1
0X00002B8106654C40 3 DMS Large 4096 4 Yes 4 1 1 Off 1 0 3 Systoolspace
0X00002B81066563C0 4 DMS Large 32768 2 2 Off 2 0 tbs_data
0X00002B8106657D40 5 SMS systmp 32768 2 2 on 1 0 tbs_temp
0x00002b810665b480 6 SMS usrtmp 32768 2 2 on 1 0 tbs_usertemp
0X00002B810665EBC0 7 DMS Large 4096 1 1 Off 2 0 tab_data2
0x00002b8106660540 8 DMS Large 32768 2 2 Off 2 0 tab_data3
0X00002B8106661EC0 9 DMS Large 4096 1 1 Off 2 0 TAB_SP4
Tablespace Statistics:
Address Id totalpgs usablepgs usedpgs pndfreepgs freepgs HWM Max HWM State minrectime nquiescers pathsdropped
0x00002b810664e400 0 24576 24572 22884 0 1688 22884 22884 0x00000000 0 0 No
0X00002B810664FB80 1 2 2 2 0 0 0 0 0x00000000 0 0 No
0X00002B81066534C0 2 8192 8160 0 8064 0 0 No
0X00002B8106654C40 3 8192 8188 0 8036 0x00000000 0 0 No
0X00002B81066563C0 4 6400 6336 0 6240 0 0 No
0X00002B8106657D40 5 1 1 1 0 0 0 0 0x00000000 0 0 No
0x00002b810665b480 6 1 1 1 0 0 0 0 0x00000000 0 0 No
0X00002B810665EBC0 7 51200 51136 0 51040 0 0 No
0x00002b8106660540 8 2048 1984 0 1888 0 0 No
0X00002B8106661EC0 9 192 0, 0 0 No
Tablespace autoresize Statistics:
Address Id as AR initsize incsize IIP MaxSize lastresize LRF
0x00002b810664e400 0 Yes 33554432-1 no none Yes None
0x00002b810664fb80 1 Yes no 0 0 no 0 None
0X00002B81066534C0 2 Yes 33554432-1 no none Yes None
0X00002B8106654C40 3 Yes 33554432-1 no none Yes None
0X00002B81066563C0 4 No no 0 0 No 0 None
0X00002B8106657D40 5 No no 0 0 No 0 None
0x00002b810665b480 6 No no 0 0 No 0 None
0X00002B810665EBC0 7 Yes 104857600 104857600 no 10737418240 None
0x00002b8106660540 8 Yes 33554432-1 no none Yes None
0X00002B8106661EC0 9 Yes 786432-1 no none Yes None
Containers:
Address tspid containnum Type totalpgs useablepgs pathid stripeset Container
0x00002b810664f940 0 0 File 24576 24572 0 0/db2inst2/db2inst2/db2inst2/node0000/ghan/t0000000/c0000000.cat
0x00002b8106651060 1 0 Path 1 1 0 0/db2inst2/db2inst2/db2inst2/node0000/ghan/t0000001/c0000000. Tmp
0x00002b8106651270 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
0x00002b8106657900 4 0 File 3200 3168-0/db2inst2/data_tb/cont0
0X00002B8106657B10 4 1 File 3200 3168-0/db2inst2/data_tb/cont1
0x00002b8106659220 5 0 Path 1 1-0/db2inst2/data_tb/tab_temp
0x00002b810665c960 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 992 0 0/db2inst2/db2inst2/db2inst2/node0000/ghan/t0000008/c0000000. LRG
0X00002B8106661C90 8 1 File 992 1 0/db2inst2/autodb/db2inst2/node0000/ghan/t0000008/c0000001. LRG
0x00002b8106663400 9 0 File 0 0/db2inst2/db2inst2/db2inst2/node0000/ghan/t0000009/c0000000. LRG
0x00002b8106663610 9 1 File 1 0/db2inst2/autodb/db2inst2/node0000/ghan/t0000009/c0000001. LRG
Nine, about the table space Quiesce test
[Db2inst2@localhost ~]$ DB2 quiesce tablespaces for table Empl share
db20000i QUIESCE tablespaces Command completed successfully
[Db2inst2@localhost ~]$ DB2-TSVF Create-tm.sql
CREATE TABLE empldd (ENO integer, LASTNAME VARCHAR (), HireDate DATE, SALARY INTEGER) in TAB_SP4
db21034e the command is treated as a SQL
Statement to handle because it is not valid for the "command line Processor" command. In the SQL
During processing, it returns:
SQL0290N does not allow access to table spaces. sqlstate=55039
Tablespace ID = 9
Name = TAB_SP4
Type = database management space
Content = All persistent data. Large table space.
Status = 0x0001
Detailed Explanation:
Paused: SHARE
Total Pages = 8448
Available pages = 8384
Pages used = 5504
Available pages = 2880
High Water mark (page) = 5504
Page size (in bytes) = 4096
Extended chunk Size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
Number of people who paused = 1
Pause 1:
Tablespace ID = 9
Object id = 4
[Db2inst2@localhost ~]$ DB2 quiesce tablespaces for table Empl Reset
db20000i QUIESCE tablespaces Command completed successfully.
[Db2inst2@localhost ~]$ DB2-TSVF Create-tm.sql
CREATE TABLE empldd (ENO integer, LASTNAME VARCHAR (), HireDate DATE, SALARY INTEGER) in TAB_SP4
Db20000i SQL Command completed successfully.
[Db2inst2@localhost ~]$
Tablespace ID = 9
Name = TAB_SP4
Type = database management space
Content = All persistent data. Large table space.
Status = 0x0000
Detailed Explanation:
Normal
Total Pages = 8448
Available pages = 8384
Pages used = 5568
Available pages = 2816
High Water mark (page) = 5568
Page size (in bytes) = 4096
Extended chunk Size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
[Db2inst2@localhost ~]$ DB2 drop TABLE Empl
Db20000i SQL Command completed successfully.
[Db2inst2@localhost ~]$ DB2 list tablespaces Show Detail
Tablespace ID = 9
Name = TAB_SP4
Type = database management space
Content = All persistent data. Large table space.
Status = 0x0000
Detailed Explanation:
Normal
Total Pages = 8448
Available pages = 8384
Pages used = 160
Available pages = 8224
High data block Size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
[Db2inst2@localhost ~]$ DB2 "alter tablespace TAB_SP4 Lower high water mark"
Db20000i SQL Command completed successfully.
Tablespace ID = 9
Name = TAB_SP4
Type = database management space
Content = All persistent data. Large table space.
Status = 0x0000
Detailed Explanation:
Normal
Total Pages = 8448
Available pages = 8384
Pages used = 160
Available pages = 8224
High Water mark (page) = 160
Page size (in bytes) = 4096
Extended chunk Size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
Water mark (page) = 5568
Page size (in bytes) = 4096
Spread
[Db2inst2@localhost ~]$ DB2-TSVF Showtables
Select substr (tbsp_name,1,20) as Tablespace_name,substr (tbsp_content_type,1,10) as Tablespace_type,sum (Tbsp_total_ SIZE_KB)/1024 as Total_mb,sum (tbsp_used_size_kb)/1024 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 0 32768
Tbs_usertemp usrtemp 0 0 0 32768
TEMPSPACE1 systemp 0 0 0 4096
USERSPACE1 LARGE 32 0 31 4096
10 records have been 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 (C HAR (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, ' 1234567890 ') as LASTNAME, current DATE-(RAND () *10957) days as HireDate, INTEGER (10000+rand () *200000) as SA Lary from DT
db21034e the command is treated as a SQL
Statement to handle because it is not valid for the "command line Processor" command. In the 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,1,20) as Tablespace_name,substr (tbsp_content_type,1,10) as Tablespace_type,sum (Tbsp_total_ SIZE_KB)/1024 as Total_mb,sum (tbsp_used_size_kb)/1024 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 0 32768
Tbs_usertemp usrtemp 0 0 0 32768
TEMPSPACE1 systemp 0 0 0 4096
USERSPACE1 LARGE 32 0 31 4096
10 records have been selected.
[Db2inst2@localhost ~]$
Tablespace id = 4
Name = Tbs_data
Type = database management space
Content = All persistent data. Large table space.
Status = 0x0000
Detailed Explanation:
Normal
Total Pages = 6400
Available pages = 6336
Pages used = 6336
Available pages = 0
High Water mark (page) = 6336
Page size (in bytes) = 32768
Extended chunk Size (page) = 32
Prefetch size (page) = 64
Number of containers = 2
DMS table Space Add container:
Alter tablespace tbs_data extend (file '/db2inst2/data_tb/cont0 ' 100M, file '/db2inst2/data_tb/cont1 ' 500M)
http://www.bkjia.com/PHPjc/1071440.html www.bkjia.com true http://www.bkjia.com/PHPjc/1071440.html techarticle DB2 Table Space Simple description First, new test library: [Db2inst2@localhost shell]$ DB2 "CREATE Database Ghan on/db2inst2/db2inst2 using CodeSet utf-8 t Erritory CN collate using System ...
