DB2（iSeries）中从CHAR到VARCHAR的转换

最新推荐文章于 2021-02-28 03:09:41 发布

翻译最新推荐文章于 2021-02-28 03:09:41 发布 · 1.5k 阅读

文章标签：

本文探讨了在DB2 for iSeries环境中，如何有效地将CHAR字段转换为VARCHAR以节省存储空间。介绍了问题背景，转换的必要性，转换步骤，包括如何选择合适的字段和ALLOCATE值，以及转换可能对性能的影响。还提供了SQL脚本来帮助选择合适的转换列和分析数据。

部署运行你感兴趣的模型镜像

背景

通常，在对遗留应用程序进行建模时，架构师的重要任务之一就是要充分利用数据库的存储功能。大多数旧版应用程序在开发时都会受到可用技术的限制，因此，根据当前情况并不是最佳的。其中一种情况是CHAR字段的广泛使用，这对于当前的空间存储而言并不是最佳解决方案。本文将重点介绍在DB2 for iSeries中将CHAR数据字段转换为VARCHAR时需要采取的系统方法。

问题陈述

CHAR数据类型的基本约束是它缺乏灵活性，无法获得存储所需的最佳空间。 CHAR（50）字段将在数据库中保留50个字节，而不管该字段的数据内容如何。如果此字段的大多数值小于长度35，则剩余的15个字节将浪费大量空间，否则数据库将无法利用它。这将导致额外的磁盘空间。

解

在这种情况下，节省存储空间的最重要和最直接的选择之一就是让数据库仅存储相关数据而没有不必要的尾随空间。为此，架构师只需将CHAR字段转换为VARCHAR。

这真的是解决方案吗？

只需将所有CHAR列都转换为VARCHAR是合适的解决方案，否则，什么是最佳方法？这就是真正的陷阱所在，而忽略了可能导致性能下降的问题，这在以后的阶段将很难跟踪。

理想情况下，这是一个两步过程：

（a）标识要转换的相关列：将所有列从CHAR盲目更改为VARCHAR可能会付出巨大的努力，因为不仅需要相应地更改表定义，而且还要相应地更改插入/更新/删除这些列的相关程序。因此，仅应选择节省空间很大的那些列。请参考“建筑师建议”部分以选择适当的列。

（b）将已识别列和相关程序的数据定义从CHAR转换为VARCHAR：这将需要对iSeries上的DB2如何实现VARCHAR的存储有一点了解。请参考下面的“在DB2（iSeries）中实现VARCHAR存储”部分。

在DB2（iSeries）中实现VARCHAR存储

可变长度列（VARCHAR）支持使您可以将表中任意数量的列定义为可变长度。如果使用VARCHAR支持，通常可以减少表的大小。可变长度列中的数据在内部存储在两个区域中：固定长度或ALLOCATE区域和溢出区域。 ALLOCATE区域的行为与CHAR数据类型的行为类似，即，不管实际数据长度如何，数据库保留的存储字节数与分配区域中指定的字节数相同。任何超出此长度的数据都将存储在变量或溢出区中。如果指定了默认值，则分配的长度至少与该值一样大。

给建筑师的建议

以下几点可帮助您确定使用存储区域的最佳方法。如果主要目标是：

节省空间：在定义表定义时，仅使用VARCHAR数据类型（而不是CHAR）。

➢性能：将VARCHAR与ALLOCATE子句一起使用。使用ALLOCATE关键字创建表

CREATE TABLE选项卡（col1 VARCHAR（40）ALLOCATE（10），

Col2 VARCHAR（40）分配（10），

COL3 VARCHAR（40）分配（7））

在许多应用中，必须考虑性能。如果使用默认的VARCHAR数据类型，即ALLOCATE（0），它将使磁盘机流量增加一倍。 ALLOCATE（0）需要两次读取；一种读取行的固定长度部分，另一种读取溢出空间。通过精心选择的ALLOCATE，可变长度实现可以最大程度地减少溢出和空间，并提高性能。

通过正确使用ALLOCATE子句，可以平衡空间节省和性能。当定义具有可变长度数据的表时，必须确定ALLOCATE区域的宽度，即ALLOCATE区域应足够宽以合并至少90％到95％的列值。

现在的问题是如何获取需要从CHAR转换为VARCHAR的列，以及如何获取列的ALLOCATE子句的神奇数字？因为选择不相关的列进行转换可能会导致较高的工作量但收益较少。通常，数据长度变化很大的列最适合转换。诸如备注，注释等列属于此类别，并且该类别中的列通常具有35或更大的长度。同样，对应的表应具有足够的行数，以证明所涉及的工作量合理，因为节省空间与行数成正比。因此，要获取相关列及其对应表的列表，请运行下面列出的SQL脚本：

选择a.table_name，a.column_name，a.data_type，a.length，b.number_rows

从qsys2.sys列a到qsys2.systablestat b

在哪里a.table_schema ='lib'

AND a.length> = 35

AND b.table_schema = a.table_schema

AND b.table_name = a.table_name

AND b.number_rows> 5000

在这里，lib是数据库表所在的库的名称。该SQL将提供所有相关列的列表。建议架构师根据其应用的性质来审查a.length和b.number_rows的合适值。现在，我们需要分析现有数据并找到ALLOCATE数字，该数字应该适合于保存90-95％的数据。为此，请在相关数据库中运行此SQL，其中col1和tab1是该列及其正在考虑的数据库表。

SELECT LENGTH（RTRIM（col1））Column_Length，count（*）计数

从tab1

按长度分组（RTRIM（col1））

ORDER BYLENGTH（RTRIM（col1））

此SQL将按列的数据长度提供列的分类。将此信息粘贴到excel电子表格中，并得出累计金额和相关百分比。下面附加的是一列的类似数据，其中父表中的行数为1626986，并且列的长度定义为CHAR（50）。

列长度计数Cumm。总百分比

空997280 997280

0 581 997861 0.613318738

1 116 997977 0.613390035

2188 998165 0.613505586

3603 998768 0.61387621

4961 999729 0.614466873

5 14 999743 0.614475478

6 2 999745 0.614476707

7 23 999768 0.614490844

8 14 999782 0.614499449

9 26 999808 0.614515429

10 2786 1002594 0.616227798

11 500 1003094 0.616535115

12 2454 1005548 0.618043425

13 4438 1009986 0.620771168

14 12623 1022609 0.628529686

15 21423 1044032 0.641696978

16 33377 1077409 0.662211599

17 43236 1120645 0.68878589

18 57116 1177761 0.723891293

19 59734 1237495 0.760605807

20 65324 1302819 0.800756122

21 64123 1366942 0.840168262

22 59600 1426542 0.876800415

23 49011 1475553 0.906924214

24 40671 1516224 0.931921971

25 30856 1547080 0.9508871

26 23382 1570462 0.96525846

27 17017 1587479 0.975717677

28 12170 1599649 0.983197766

29 8537 1608186 0.988444891

30 5674 1613860 0.991932321

31 4108 1617968 0.994457236

32 2778 1620746 0.996164687

33 1922 1622668 0.997346013

34 1315 1623983 0.998154256

35 1030 1625013 0.998787328

36 686 1625699 0.999208967

37523 1626222 0.99953042

38 280 1626502 0.999702517

39169 1626671 0.99980639

40 145 1626816 0.999895512

41 56 1626872 0.999929932

42 37 1626909 0.999952673

43 20 1626929 0.999964966

44 10 1626939 0.999971112

45 19 1626958 0.99998279

46 5 1626963 0.999985863

47 3 1626966 0.999987707

50 20 1626986 1

几乎95％的数据长度为25或小于25，因此选择ALLOCATE（25）将节省大约35MB的空间，而对性能几乎没有影响。可以大致估计为：

（（（Total_rows *百分位*保存的空间）–（Total_rows *剩余百分位*每个溢出行的平均变量空间））。

由于后半部分不重要，因此前半部分的近似值可以提供粗略估计。而且，我们不必太具体；任何接近的估计都可以帮助我们做出决定。对于具有数百万行的表，仅一列就可以节省多达300MB。

该分析的结果还将有助于确定此列是否适合转换，因为较少的存储节省表明该列没有花费精力。

可以轻松地对现有列进行这种类型的分析，但是在设计新表时，“分配”大小应该是多少？为此，数据架构师最初必须根据该字段的功能选择一个适当的值（如果没有其他输入来辅助决策，则最初可以认为总长度的75％是适当的）。在拥有大量生产数据之后，数据架构师应使用下面列出的SQL检查此字段的性能：

SELECT DOUBLE_PRECISION（溢出）/（number_行）

来自qsys2.systablestat

WHERE table_name =“ tab1”

AND table_schema =“ lib”

低于5％的任何值都可以，高于5％的任何值都意味着需要增加ALLOCATE长度。

对代码的可能影响

除了表定义之外，在使用主机变量的情况下，取决于这些字段的程序也可能需要更改。如果使用主机变量插入或更新可变长度列，则主机变量应为可变长度。由于不会从固定长度的主机变量中截断空白，因此使用固定长度的主机变量将导致更多行溢出到溢出空间中。这将增加表的大小。在此示例中，固定长度的主机变量用于在表中插入一行：

01 COL1 PIC X（40）。 ...

将“ SMITH”移动到COL1。

EXEC SQL INSERT INTO选项卡1值

（：COL1，：COL2，：COL3）

结束执行。

主机变量COL1的长度不是可变的。字符串“ SMITH”，后跟45个空格，被插入VARCHAR列COL1中。该值大于分配大小25。溢出区域中有45个尾随空白中的25个。在此示例中，可变长度的主机变量用于在表中插入一行：

01 VCol1。

49 Col1-LEN PIC S9（4）二进制

49 Col1-DATA PIC X（40）。

...

将“ SMITH”移动到Col1-DATA。

将5移动到Col1-LEN。