db2 "create database test2 on /testdata/db2inst1 DBPATH on /home/db2inst1 pagezise 32K"
可以看到系统自动创建了三个表空间，都是32K 页大小。如果不指定pagesize则数据库会默认创建3个pagesize是4K的表空间，SYSCATSPACE存放系统表，
TEMPSPACE1是临时表空间，USERSPACE1是用户数据表空间
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test2 -tablespaces
Database Member 0 -- Database TEST2 -- Active -- Up 0 days 00:00:04 -- Date 2023-08-25-13.54.43.273977
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007FBF2A4D2D40 0 DMS Regular 32768 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007FBF2A4DFEE0 1 SMS SysTmp 32768 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007FBF2A4ED080 2 DMS Large 32768 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00007FBF2A4CC120 0 0 File 7168 7164 0 0 /testdata/db2inst1/db2inst1/NODE0000/TEST2/T0000000/C0000000.CAT
0x00007FBF2A4FB000 1 0 Path 1 1 0 0 /testdata/db2inst1/db2inst1/NODE0000/TEST2/T0000001/C0000000.TMP
0x00007FBF27784C00 2 0 File 1024 992 0 0 /testdata/db2inst1/db2inst1/NODE0000/TEST2/T0000002/C0000000.LRG
通过对tablespaces的快照可以看到四个表空间都使用了自动储存，自动储存的意思是只指定表空间的目录，不指定大小，表空间大小可以自增长，只有目录磁盘有空间，表空间就不会满。
而对应的是非自动表空间，指定了FILE或者DEVICE，必须指定大小，不会自动增长，而且恢复数据库的时候需要重定向恢复，因为新的系统可能没有非自动表空间的vFILE或者DEVICE，需要重新指定。
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 get snapshot for tablespaces on test2 |grep -i "Using automatic storage"
Using automatic storage = Yes
Using automatic storage = Yes
Using automatic storage = Yes
Using automatic storage = Yes
通过db2pd -d test1 -storage可以看到数据库定义的存储路径，可以看到只有一个IBMSTOGROUP /testdata/db2inst1
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test -storage
Database Member 0 -- Database TEST -- Active -- Up 0 days 00:13:06 -- Date 2023-09-04-16.29.55.541728
Storage Group Configuration:
Address SGID Default DataTag Name
0x00007FBF28B06D20 0 Yes 0 IBMSTOGROUP
Storage Group Statistics:
Address SGID State Numpaths NumDropPen
0x00007FBF28B06D20 0 0x00000000 1 0
Storage Group Paths:
Address SGID PathID PathState PathName
0x00007FBF28B37000 0 0 InUse /testdata/db2inst1
如何创建用户表空间呢？其实db2 "CREATE TABLESPACE myspacetest"命令默认忽略了USING STOGROUP IBMSTOGROUP managed by AUTOMATIC STORAGE;就是使用"create database on /testdata/db2inst1"时指定的on /testdata/db2inst1这个存储组
db2 "CREATE TABLESPACE myspacetest"
0x00007FBF3173AD20 4 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes MYSPACETEST
0x00007FBF31730000 4 0 File 8192 8160 0 0 /testdata/db2inst1/db2inst1/NODE0000/TEST/T0000004/C0000000.LRG
那可以将用户表创建在系统表空间吗？不可以
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "create table mytabletest (id int) in SYSCATSPACE"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0287N SYSCATSPACE cannot be used for user objects. SQLSTATE=42838
多了一个表空间是因为用了snapshot，触发了自动创建了SYSTOOLSPACE
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test2 -tablespaces
Database Member 0 -- Database TEST2 -- Active -- Up 0 days 00:19:12 -- Date 2023-08-25-14.13.51.227689
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007FBF2A4D2D40 0 DMS Regular 32768 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007FBF2A4DFEE0 1 SMS SysTmp 32768 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007FBF2A4ED080 2 DMS Large 32768 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
0x00007FBF38870080 3 DMS Large 32768 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE
如果不指定pagesize则数据库会默认创建3个pagesize是4K的表空间
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test1 -tablespaces
Database Member 0 -- Database TEST1 -- Active -- Up 0 days 00:00:03 -- Date 2023-08-25-14.17.17.439951
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007FBF8F81CD40 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007FBF8F829EE0 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007FBF8F837080 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
0x00007FBF8F844220 3 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE
db2pd -d test1 -bufferpools可以看出系统自动创建了4K 8K 16K 32K页大小的bufferpool，和一个指定pagesize的IBMDEFAULTBP。如果表空间不指定使用的buffpool，则会使用默认的IBMDEFAULTBP
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test1 -bufferpools
Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize NumTbsp PgsToRemov CurrentSz PostAlter SuspndTSCt Automatic
0x00007FBF8CA99920 1 IBMDEFAULTBP 4096 5000 0 0 4 0 5000 5000 0 True
0x00007FBF8CA65F20 4096 IBMSYSTEMBP4K 4096 16 0 0 0 0 16 16 0 False
0x00007FBF8CA72DA0 4097 IBMSYSTEMBP8K 8192 16 0 0 0 0 16 16 0 False
0x00007FBF8CA7FC20 4098 IBMSYSTEMBP16K 16384 16 0 0 0 0 16 16 0 False
0x00007FBF8CA8CAA0 4099 IBMSYSTEMBP32K 32768 16 0 0 0 0 16 16 0 False
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test2 -bufferpools
Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize NumTbsp PgsToRemov CurrentSz PostAlter SuspndTSCt Automatic
0x00007FBF27749920 1 IBMDEFAULTBP 32768 5000 0 0 4 0 5000 5000 0 True
0x00007FBF27715F20 4096 IBMSYSTEMBP4K 4096 16 0 0 0 0 16 16 0 False
0x00007FBF27722DA0 4097 IBMSYSTEMBP8K 8192 16 0 0 0 0 16 16 0 False
0x00007FBF2772FC20 4098 IBMSYSTEMBP16K 16384 16 0 0 0 0 16 16 0 False
0x00007FBF2773CAA0 4099 IBMSYSTEMBP32K 32768 16 0 0 0 0 16 16 0 False
如果我们删除USERSPACE1表空间后，创建表不指定表空（create table IN tablespace-name,... 可以指定一个或者多个表空间），那么DB2会如何选着表空间？
删除USERSPACE1表空间后，当我们去创建表时，为给表指定表空间，创建的表会根据表中字段总大小来自动选着表空间。
例如：表字段占字符串总大小0~8092k之间，创建表时会创建在8K的表空间中，
8093k~16384K之间，创建表时会创建在16K的表空间中，
16385K~32768K之间，创建表时会创建在32K的表空间中.
实验步骤：
1.删除默认表空间，并创建8K 16K 32K表空间
drop tablespace USERSPACE1;
DB20000I The SQL command completed successfully
可以看到创建表空间不指定bufferpool就会使用默认的IBMDEFAULTBP
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "create tablespace TSP8K pagesize 8 k"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1582N The PAGESIZE of the table space "TSP8K" does not match the PAGESIZE
of the bufferpool "IBMDEFAULTBP" associated with the table space.
SQLSTATE=428CB
可以看到如果使用IBMSYSTEMBP8K则会报错，说明这个系统的BUFFERPOOL不能使用
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "create tablespace TSP8K pagesize 8 k bufferpool IBMSYSTEMBP8K"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "IBMSYSTEMBP8K" is an undefined name. SQLSTATE=42704
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "create bufferpool BP8K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "create bufferpool BP16K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2 "create bufferpool BP32K"
DB20000I The SQL command completed successfully.
可以看到BP8K，BP16K，BP32K都是默认的Automatic自动增长，可以手动指定大小，也可以指定是否自增长。
[db2inst1@t3-dtpoc-dtpoc-web04 ~]$ db2pd -d test1 -bufferpools
Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize NumTbsp PgsToRemov CurrentSz PostAlter SuspndTSCt Automatic
0x00007FBF8CA99920 1 IBMDEFAULTBP 4096 5000 0 0 4 0 5000 5000 0 True
0x00007FBF95307B20 2 BP8K 4096 1000 0 0 0 0 1000 1000 0 True
0x00007FBF9531C040 3 BP16K 4096 1000 0 0 0 0 1000 1000 0 True
0x00007FBF95330040 4 BP32K 4096 1000 0 0 0 0 1000 1000 0 True
2.创建三张测试表。
通过报错可以看到系统在寻找满足比行大小最小的PAGEzize的表空间，比如TEST_2000发现大于等于4K的表空间一个也没有。
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 -tvf 0825.sql
CREATE TABLE TEST_2000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(2000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0286N A table space could not be found with a page size of at least "4096"
that authorization ID "DB2INST1" is authorized to use. SQLSTATE=42727
CREATE TABLE TEST_6000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(2000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0286N A table space could not be found with a page size of at least "8192"
that authorization ID "DB2INST1" is authorized to use. SQLSTATE=42727
CREATE TABLE TEST_9000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(1000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0286N A table space could not be found with a page size of at least
"16384" that authorization ID "DB2INST1" is authorized to use. SQLSTATE=42727
CREATE TABLE TEST_36000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(4000) DEFAULT 'unknow', CNAME3 VARCHAR(4000) DEFAULT 'unknow', CNAME4 VARCHAR(4000) DEFAULT 'unknow', CNAME5 VARCHAR(4000) DEFAULT 'unknow', CNAME6 VARCHAR(4000) DEFAULT 'unknow', CNAME7 VARCHAR(4000) DEFAULT 'unknow', CNAME8 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0286N A table space could not be found with a page size of at least
"32768" that authorization ID "DB2INST1" is authorized to use. SQLSTATE=42727
db2 "create tablespace TSP8K pagesize 8 k bufferpool BP8K"
db2 "create tablespace TSP8K pagesize 16 k bufferpool BP16K"
db2 "create tablespace TSP8K pagesize 32 k bufferpool BP32K"
发现创建bufferpool的是没有指定Pagezise导致也不能创建表空间
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create tablespace TSP8K pagesize 8 k bufferpool BP8K"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1582N The PAGESIZE of the table space "TSP8K" does not match the PAGESIZE
of the bufferpool "BP8K" associated with the table space. SQLSTATE=428CB
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 drop bufferpool BP8K
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 drop bufferpool BP16K
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 drop bufferpool BP32K
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create bufferpool BP8K pagesize 8K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create bufferpool BP16K pagesize 16K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create bufferpool BP32K pagesize 32K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create tablespace TSP8K pagesize 8 k bufferpool BP8K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create tablespace TSP16K pagesize 16 k bufferpool BP16K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create tablespace TSP32K pagesize 32 k bufferpool BP32K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 -tvf 0825.sql
CREATE TABLE TEST_2000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(2000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
CREATE TABLE TEST_6000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(2000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
CREATE TABLE TEST_9000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(1000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
CREATE TABLE TEST_36000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(4000) DEFAULT 'unknow', CNAME3 VARCHAR(4000) DEFAULT 'unknow', CNAME4 VARCHAR(4000) DEFAULT 'unknow', CNAME5 VARCHAR(4000) DEFAULT 'unknow', CNAME6 VARCHAR(4000) DEFAULT 'unknow', CNAME7 VARCHAR(4000) DEFAULT 'unknow', CNAME8 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
可以看到会使用比行大小最小的pagesize来存储数据。
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "SELECT NAME,TYPE,COLCOUNT,TBSPACE,CTIME FROM SYSIBM.SYSTABLES WHERE CREATOR='DB2INST1'"
NAME TYPE COLCOUNT TBSPACE CTIME
-------------------------------------------------------------------------------------------------------------------------------- ---- -------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------
TEST_2000 T 2 TSP8K 2023-08-25-15.20.42.544211
TEST_36000 T 10 TSP32K 2023-08-25-15.20.42.814276
TEST_6000 T 3 TSP8K 2023-08-25-15.20.42.659042
TEST_9000 T 4 TSP16K 2023-08-25-15.20.42.733818
4 record(s) selected.
那如果有两个或者更多32K大小的tablespace,不指定表空间，会怎么选择呢，是一直使用一个还是轮询使用呢？做个测试
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create tablespace TSP32K02 pagesize 32 k bufferpool BP32K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create tablespace TSP32K03 pagesize 32 k bufferpool BP32K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "create tablespace TSP32K04 pagesize 32 k bufferpool BP32K"
DB20000I The SQL command completed successfully.
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ vi 0825_new.sql
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 -tvf 0825_new.sql
CREATE TABLE TEST02_36000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(4000) DEFAULT 'unknow', CNAME3 VARCHAR(4000) DEFAULT 'unknow', CNAME4 VARCHAR(4000) DEFAULT 'unknow', CNAME5 VARCHAR(4000) DEFAULT 'unknow', CNAME6 VARCHAR(4000) DEFAULT 'unknow', CNAME7 VARCHAR(4000) DEFAULT 'unknow', CNAME8 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
CREATE TABLE TEST03_36000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(4000) DEFAULT 'unknow', CNAME3 VARCHAR(4000) DEFAULT 'unknow', CNAME4 VARCHAR(4000) DEFAULT 'unknow', CNAME5 VARCHAR(4000) DEFAULT 'unknow', CNAME6 VARCHAR(4000) DEFAULT 'unknow', CNAME7 VARCHAR(4000) DEFAULT 'unknow', CNAME8 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
CREATE TABLE TEST04_36000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(4000) DEFAULT 'unknow', CNAME3 VARCHAR(4000) DEFAULT 'unknow', CNAME4 VARCHAR(4000) DEFAULT 'unknow', CNAME5 VARCHAR(4000) DEFAULT 'unknow', CNAME6 VARCHAR(4000) DEFAULT 'unknow', CNAME7 VARCHAR(4000) DEFAULT 'unknow', CNAME8 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
CREATE TABLE TEST05_36000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(4000) DEFAULT 'unknow', CNAME3 VARCHAR(4000) DEFAULT 'unknow', CNAME4 VARCHAR(4000) DEFAULT 'unknow', CNAME5 VARCHAR(4000) DEFAULT 'unknow', CNAME6 VARCHAR(4000) DEFAULT 'unknow', CNAME7 VARCHAR(4000) DEFAULT 'unknow', CNAME8 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
CREATE TABLE TEST06_36000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(4000) DEFAULT 'unknow', CNAME3 VARCHAR(4000) DEFAULT 'unknow', CNAME4 VARCHAR(4000) DEFAULT 'unknow', CNAME5 VARCHAR(4000) DEFAULT 'unknow', CNAME6 VARCHAR(4000) DEFAULT 'unknow', CNAME7 VARCHAR(4000) DEFAULT 'unknow', CNAME8 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
CREATE TABLE TEST07_36000 ( CID CHARACTER(10) NOT NULL, CNAME VARCHAR(4000) DEFAULT 'unknow', CNAME1 VARCHAR(4000) DEFAULT 'unknow', CNAME2 VARCHAR(4000) DEFAULT 'unknow', CNAME3 VARCHAR(4000) DEFAULT 'unknow', CNAME4 VARCHAR(4000) DEFAULT 'unknow', CNAME5 VARCHAR(4000) DEFAULT 'unknow', CNAME6 VARCHAR(4000) DEFAULT 'unknow', CNAME7 VARCHAR(4000) DEFAULT 'unknow', CNAME8 VARCHAR(4000) DEFAULT 'unknow', CONSTRAINT IDX_TEST_CID PRIMARY KEY (CID) )
DB20000I The SQL command completed successfully.
可以看到数据库并不会轮询使用表空间，而是一直选择一个，所以也是为什么为了负载均衡，创建了多个表空间，在建表的时候一定要人工轮询使用表空间，已达到负载均衡
[db2inst1@t3-dtpoc-dtpoc-web04 liys]$ db2 "SELECT NAME,TYPE,COLCOUNT,TBSPACE,CTIME FROM SYSIBM.SYSTABLES WHERE CREATOR='DB2INST1'"
NAME TYPE COLCOUNT TBSPACE CTIME
-------------------------------------------------------------------------------------------------------------------------------- ---- -------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------
TEST02_36000 T 10 TSP32K 2023-08-25-15.27.51.463949
TEST03_36000 T 10 TSP32K 2023-08-25-15.27.51.642819
TEST04_36000 T 10 TSP32K 2023-08-25-15.27.51.793127
TEST05_36000 T 10 TSP32K 2023-08-25-15.27.52.057796
TEST06_36000 T 10 TSP32K 2023-08-25-15.27.52.208901
TEST07_36000 T 10 TSP32K
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
