0002-创建数据库和表空间

ramos@db2awse:~/testdb> mkdir dbauto
ramos@db2awse:~/testdb> mkdir database
ramos@db2awse:~/sqllib/db2dump> db2 "create database testdb automatic storage yes on /home/ramos/testdb/dbauto dbpath on /home/ramos/testdb/database using codeset UTF-8 territory CN collate using system"
DB20000I  The CREATE DATABASE command completed successfully.


ramos@db2awse:~/sqllib/db2dump> db2 create bufferpool bp32k size 5000 pagesize 32k
DB20000I  The SQL command completed successfully.


-----------------------------no need to create path /home/ramos/testdb/database/ramos/tbs_data/cont0 and /home/ramos/testdb/database/ramos/tbs_data/cont1 manually
ramos@db2awse:~/tbs_data/cont0> db2 "create large tablespace tbs_data pagesize 32k managed by database using (file '/home/ramos/testdb/database/ramos/tbs_data/cont0' 20m, file '/home/ramos/testdb/database/ramos/tbs_data/cont1' 20m) extentsize 32 prefetchsize automatic bufferpool bp32k no file system caching"
DB20000I  The SQL command completed successfully.
ramos@db2awse:~/tbs_data/cont0>


ramos@db2awse:~/testdb/database/ramos> db2 "create temporary tablespace tbs_temp pagesize 32k managed by system using ( '/home/ramos/testdb/database/ramos/tbs_temp') bufferpool bp32k"
DB20000I  The SQL command completed successfully.
ramos@db2awse:~/testdb/database/ramos>


ramos@db2awse:~/testdb/database/ramos> clear all
ramos@db2awse:~/testdb/database/ramos> db2 "create user temporary tablespace tbs_user_temp pagesize 32k managed by system using('/home/ramos/testdb/database/ramos/tbs_user_temp') bufferpool bp32k"
DB20000I  The SQL command completed successfully.
ramos@db2awse:~/testdb/database/ramos> db2 create tablespace tab_index pagesize 32k bufferpool bp32k
DB20000I  The SQL command completed successfully.
ramos@db2awse:~/testdb/database/ramos> db2 "create tablespace tbs_data2 initialsize 10m increasesize 10m maxsize 100m"
DB20000I  The SQL command completed successfully.
ramos@db2awse:~/testdb/database/ramos>


ramos@db2awse:~/testdb/database/ramos> db2 alter bufferpool bp32k size 1000
DB20000I  The SQL command completed successfully.
ramos@db2awse:~/testdb/database/ramos>


db2 get snapshot for tablespaces on testdb
db2 list tablespaces
db2 list tablespace containers for 8
db2 list tablespace containers for 8 show details
db2pd -d testdb -tablespaces


-----------quiesce--------share


db2inst@db2awse:~> db2 quiesce tablespaces for table req.access_req share
DB20000I  The QUIESCE TABLESPACES command completed successfully.
db2inst@db2awse:~> db2 select count(*) from req.access_req
-bash: syntax error near unexpected token `('
db2inst@db2awse:~> db2 select "count(*) from req.access_req"


1
-----------
          8


  1 record(s) selected.


db2inst@db2awse:~> db2 "update req.access_req set request_id=11111"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0290N  Table space access is not allowed.  SQLSTATE=55039
db2inst@db2awse:~> db2 list tablespaces show detail


           Tablespaces for Current Database


 Tablespace ID                        = 5
 Name                                 = kaka
 Type                                 = System managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0001
   Detailed explanation:
     Quiesced: SHARE
 Total pages                          = 98
 Useable pages                        = 98
 Used pages                           = 98
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Number of quiescers                  = 1
   Quiescer 1:
     Tablespace ID                    = 5
     Object ID                        = 2
db2inst@db2awse:~> db2 quiesce tablespaces for table req.access_req reset
DB20000I  The QUIESCE TABLESPACES command completed successfully.
db2inst@db2awse:~>