1. 报错如下
SQL1585N A temporary table could not be created because there is no available
system temporary table space that has a compatible page size. SQLSTATE=54048
2. 报错可能原因
* The row length of the system temporary table being generated is
larger than can be accommodated by the system temporary table space
with the largest page size that is currently in "normal" state.
* The number of columns required in a system temporary table exceeded
the limit that can be accommodated in the largest system temporary
table space in the database.
* A tablespace might be offline.
翻译:
* 正在生成的系统临时表的行长度大于具有最大页面大小且当前处于"正常"状态的系统临时表空间能够容纳的长度。
* 系统临时表中所需的列数超过了数据库中最大系统临时表空间中可接受的限制。
* 表空间可能已脱机。
3. 报错原因分析
-查看表空间没有脱机,不是此原因
-查看查询的表字段151个字段,少于500个字段(4k的页最多允许500字段),不是此原因
-需要使用的系统临时表空间的pagesize超出了系统临时表的pagesize值,应该是此原因导致;推测和EXTENDED_ROW_SZ参数有关系;EXTENDED_ROW_SZ参数可以允许行长度超过一个pagesize,但是在进行排序时如果超过系统默认表空间的pagesize,可能导致系统临时表空间报错
4. 数据库参数EXTENDED_ROW_SZ说明
数据库参数EXTENDED_ROW_SZ在设置为ENABLE时可以允许创建表时定义的行长度超过一个pagesize;超出部分的数据子集存储为LOB数据;此参数在10.5开始出现,默认是ENABLE;如果从低版本升级过来默认是DISABLE.
5. 模拟报错
5.1 查看表空间
所有表空间的pagesize都是4k
$ db2pd -d enmo -table
Database Member 0 -- Database ENMO -- Active -- Up 0 days 02:05:40 -- Date 2021-02-02-21.30.05.612619
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007FFF82BD5CA0 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007FFF82BE2E40 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 4294967295 No TEMPSPACE1
0x00007FFF82BEFFE0 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
0x00007FFF82BFD180 3 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE
0x00007FFF82C0A320 4 SMS UsrTmp 4096 4 Yes 4 1 1 On 1 0 3 No SYSTOOLSTMPSPACE
0x00007FFF82C26020 5 DMS Regular 4096 32 Yes 32 1 1 Def 1 0 31 Yes TEST
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x00007FFF82BD5CA0 0 57344 57340 52660 0 4680 54416 54416 0x00000000 0 0 No Dirty
0x00007FFF82BE2E40 1 1 1 1 0 0 - - 0x00000000 0 0 No Dirty
0x00007FFF82BEFFE0 2 73728 73696 23776 0 49920 41280 41280 0x00000000 1606115081 0 No Dirty
0x00007FFF82BFD180 3 8192 8188 168 0 8020 196 196 0x00000000 0 0 No Dirty
0x00007FFF82C0A320 4 1 1 1 0 0 - - 0x00000000 0 0 No Dirty
0x00007FFF82C26020 5 8192 8160 896 0 7264 896 896 0x00000000 1612265749 0 No Dirty
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00007FFF82BD5CA0 0 Yes Yes 33554432 -1 No None None No
0x00007FFF82BE2E40 1 Yes No 0 0 No 0 None No
0x00007FFF82BEFFE0 2 Yes Yes 33554432 -1 No None None No
0x00007FFF82BFD180 3 Yes Yes 33554432 -1 No None None No
0x00007FFF82C0A320 4 Yes No 0 0 No 0 None No
0x00007FFF82C26020 5 Yes Yes 33554432 -1 No None None No
Tablespace Storage Statistics:
Address Id DataTag Rebalance SGID SourceSGID
0x00007FFF82BD5CA0 0 0 No 0 -
0x00007FFF82BE2E40 1 0 No 0 -
0x00007FFF82BEFFE0 2 0 No 0 -
0x00007FFF82BFD180 3 0 No 0 -
0x00007FFF82C0A320 4 0 No 0 -
0x00007FFF82C26020 5 -1 No 0 -
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00007FFF8073D8A0 0 0 File 57344 57340 0 0 /u01/enmo/db2inst1/NODE0000/ENMO/T0000000/C0000000.CAT
0x00007FFF8071DD40 1 0 Path 1 1 0 0 /u01/enmo/db2inst1/NODE0000/ENMO/T0000001/C0000000.TMP
0x00007FFF80719C20 2 0 File 73728 73696 0 0 /u01/enmo/db2inst1/NODE0000/ENMO/T0000002/C0000000.LRG
0x00007FFF8071ECC0 3 0 File 8192 8188 0 0 /u01/enmo/db2inst1/NODE0000/ENMO/T0000003/C0000000.LRG
0x00007FFF807155A0 4 0 Path 1 1 0 0 /u01/enmo/db2inst1/NODE0000/ENMO/T0000004/C0000000.UTM
0x00007FFF80715C00 5 0 File 8192 8160 0 0 /u01/enmo/db2inst1/NODE0000/ENMO/T0000005/C0000000.USR
5.2 查看EXTENDED_ROW_SZ
开启扩展行特性
$ db2 get db cfg |grep -i extend
Extended row size support (EXTENDED_ROW_SZ) = ENABLE
5.3 创建表
表空间最大pagesize是4k，每页最多可以存不到4005字节，按说创建超过此长度的表应该会报错，但是现在创建为报错
$ db2 "create table test123(v1 varchar(3000),v2 varchar(3000)) in userspace1"
DB20000I The SQL command completed successfully.
5.4 查看表是否使用了extend_row_sz
EXTENDED_ROW_SIZE表示使用了extend_row_sz的特性
$ db2 "select substr(tabname,1,10) tabname,EXTENDED_ROW_SIZE from syscat.tables where tabname='TEST123'"
TABNAME EXTENDED_ROW_SIZE
---------- -----------------
TEST123 Y
1 record(s) selected.
5.5 插入数据并检查字段长度
查看字段长度都是4,但是占用空间已经是超过一个pagesize
$ db2 "insert into test123 values('aaaa','bbbb')"
$ db2 "select length(v1),length(v2) from test123"
1 2
----------- -----------
4 4
1 record(s) selected.
5.6 排序查询
虽然数据表空间和系统临时表空间的pagesize都是4k,但是由于extend_row_sz参数设置,可以让数据存超过1个pagesize的行数据,但是在排序时系统临时表空间pagesize无法支持extend_row_sz特性,导致此报错
$ db2 "select * from test123 order by v1"
SQL1585N A temporary table could not be created because there is no available
system temporary table space that has a compatible page size. SQLSTATE=54048
6. 解决办法
创建系统临时表空间大于行数据定义长度(3000+3000=6000)的pagesize的表空间,此时可以创建8k大小的表空间
$ db2 "create bufferpool bp8k size 1000 pagesize 8k"
$ db2 "create system temporary tablespace temp8k pagesize 8k bufferpool bp8k"
$ db2 "select * from test123 order by v1" --排序查询恢复正常
7. 其他说明
创建表时直接定义的lob字段,在排序时不计算lob的长度,此时只需要系统临时表空间的pagesize大于除lob\long等大字段类型加和的长度即可
–END–
最后修改时间：2021-02-02 22:58:57
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
