如何手动设置VARGRAPHIC列的统计信息 [英] How do I manually set statistics for a VARGRAPHIC column
问题描述
我正在尝试将生产统计数据复制到测试数据库中，并且不能
设置VARGRAPHIC列的列统计信息以匹配RUNSTATS
生成的生产。
原因代码和我做的一些测试表明
low2key值的长度太长。几乎看起来要存储的数据的潜在长度是根据十六进制字符串的长度计算的;
而不考虑字符串的两个字节代表单个字符串
字节的数据。当我将
截断为8字节时，我能够成功存储low2key值。
失败声明：
更新sysstat .columns set colcard = 176，high2key = x''67275A6C6F747927''，
low2key = x''6727416C67657269616E2044696E617227''，avgcollen = 28，numnulls = 0
where tabchema =''......''和colname =''....''和tabname =''.........''
错误代码：
COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI驱动程序] [DB2 / LINUX] SQL1227N
目录统计g''Algerian Dinar'' "对于列LOW2KEY对于其目标列，其格式无效，或者与其他统计信息相比不一致
。原因代码=3。 SQLSTATE = 23521
Phil Sherman
I''m trying to copy production statistics to a test database and can''t
set the column statistics for a VARGRAPHIC column to match what RUNSTATS
generated on production.
The reason code and some testing I did indicates that the length of the
low2key value is too long. It almost looks like the potential length of
data to be stored is being calculated on the length of the hex string;
without consideration that two bytes of the string represent a single
byte of data. I was able to successfully store the low2key value when I
truncated it to 8 bytes.
Failing statement:
update sysstat.columns set colcard=176, high2key=x''67275A6C6F747927'',
low2key=x''6727416C67657269616E2044696E617227'', avgcollen=28, numnulls=0
where tabschema=''......'' and colname=''....'' and tabname=''.........''
Error code:
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/LINUX] SQL1227N
The catalog statistic "g''Algerian Dinar''" for column "LOW2KEY" is out of
range for its target column, has an invalid format, or is inconsistent
in relation to some other statistic. Reason Code = "3". SQLSTATE=23521
Phil Sherman
推荐答案
Phil，
盲目猜测。 avgcollen = 28。是字节还是字符？
（VAR）GRAPHIC外部使用字符长度，但DB2内部
乘以2得到字节长度。
在你尝试用b $ b来捏造它之前看看有什么价值会很有趣。
干杯
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
Phil,
Blind guess. The avgcollen=28. Is that bytes or characters?
(VAR)GRAPHIC externally uses character length, but DB2 internally
multiplies by 2 to get byte length.
Would be interesting to see what teh values was set to before you tried
to fudge it.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Phil，
盲猜。 avgcollen = 28。是字节还是字符？
（VAR）GRAPHIC外部使用字符长度，但DB2内部
乘以2得到字节长度。
在你尝试用b $ b来捏造它之前看看有什么价值会很有趣。
干杯
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
Phil,
Blind guess. The avgcollen=28. Is that bytes or characters?
(VAR)GRAPHIC externally uses character length, but DB2 internally
multiplies by 2 to get byte length.
Would be interesting to see what teh values was set to before you tried
to fudge it.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
更新语句是通过读取RUNSTATS生成的统计信息
生成的。我试图加载的统计信息必须有效，因为
RUNSTATS能够将它们插入到生产目录中。
之前没有值试图设置它们，因为更新是对不包含数据的数据库运行的
将被用于
仅用于解释工作。
我希望RUNSTATS会截断插入的值为
HIGH2KEY和LOW2KEY列符合统计表
最大允许长度为33（见SQL1227N原因3）。收集VARGRAPHIC列的统计信息时，确实会引发有趣的问题。
对于UTF-8数据库; ecah字符
存储时将占用1-4个字节。这个特定数据是ASCII，应该可以存储为1
字节/字符。我试图插入的值肯定比最大33字节更短。
。我也不知道33字节最大值是多少来自b $ b来自哪里。两列（HIGN2KEY和LOW2KEY）定义为128
bytes VARCHAR，这使得VARGRAPHIC数据存储在其中是一个有趣的
命题。
Phil Sherman
Serge Rielau写道：
The update statement was generated by reading the statistics generated
by RUNSTATS. The statistics I''m trying to load must be valid because
RUNSTATS was able to insert them into the production catalog.
There were no values before I tried to set them because the updates are
being run against a database that contains no data and will be used
solely for explain work.
I would expect that RUNSTATS would truncate the value being inserted for
the HIGH2KEY and LOW2KEY columns to conform with the stats table
maximum allowed length of 33 (See SQL1227N reason 3). This does raise
interesting questions when gathering statistics for a VARGRAPHIC column.
For a UTF-8 database; ecah "character" will occupy 1-4 bytes when
stored. This specific data is ASCII and should be storable as 1
byte/character. The value I''m attempting to insert is definitely shorter
than the 33 byte maximum. I also don''t know where the 33 byte maximum
comes from. The two columns (HIGN2KEY and LOW2KEY) are defined as 128
bytes VARCHAR which makes storing VARGRAPHIC data in them an interesting
proposition.
Phil Sherman
Serge Rielau wrote:
Phil，
盲目猜测。 avgcollen = 28。是字节还是字符？
（VAR）GRAPHIC外部使用字符长度，但DB2内部
乘以2得到字节长度。
看看之前设置的值是多么有趣你试过去捏它。
干杯
Serge
Phil,
Blind guess. The avgcollen=28. Is that bytes or characters?
(VAR)GRAPHIC externally uses character length, but DB2 internally
multiplies by 2 to get byte length.
Would be interesting to see what teh values was set to before you tried
to fudge it.
Cheers
Serge
这篇关于如何手动设置VARGRAPHIC列的统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
