DB2单表排序查询报错分析及解决办法

数据和云

发布于 2021-03-11 14:10:19

6960

发布于 2021-03-11 14:10:19

文章被收录于专栏：数据和云

墨墨导读：客户DB2环境对单表的排序查询报错SQL1585N，本文模拟此报错并进行说明。

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等大字段类型加和的长度即可

作者

常国民，熟悉DB2、Oracle数据库；长期在客户一线工作，服务过高速，电信，银行，保险，证券，口腔等客户。

墨天轮原文链接：https://www.modb.pro/db/44663（复制到浏览器中打开或者点击“阅读原文”立即查看）

–END–

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-03-09，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

本文分享自数据和云微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

数据库

sql

登录后参与评论

0 条评论

热度