How do I manually set statistics for a VARGRAPHIC column

[Philip 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

[Serge Rielau]

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

[Serge Rielau]

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

[Philip Sherman]

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,

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

[Philip Sherman]

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,

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

[Philip Sherman]

I did some more checking and discovered some additional strangeness.
I ran additional tests and discovered the following:

The RUNSTATS utility is inserting the character string "g'Zloty'"
(double quotes are my delimiters of the actual string) into HIGH2KEY. I
can't find any way to get this string into the column with an UPDATE
SQL statement. I can't even update the existing value to the same
value. Any suggestions?
Phil Sherman

Serge Rielau wrote:

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

[Philip Sherman]

I did some more checking and discovered some additional strangeness.
I ran additional tests and discovered the following:

The RUNSTATS utility is inserting the character string "g'Zloty'"
(double quotes are my delimiters of the actual string) into HIGH2KEY. I
can't find any way to get this string into the column with an UPDATE
SQL statement. I can't even update the existing value to the same
value. Any suggestions?
Phil Sherman

Serge Rielau wrote:

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

[Pierre Saint-Jacques]

Have you tried using db2look with the -m parm to extract the statsd of
that table?
The oputput is a command that has the proper structure ( I should hope
at least).
You could then see if your command matches and what db2look and db2 do
with the vargraphic stats
HTH, Pierre.

Philip Sherman wrote:

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

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[Pierre Saint-Jacques]

Have you tried using db2look with the -m parm to extract the statsd of
that table?
The oputput is a command that has the proper structure ( I should hope
at least).
You could then see if your command matches and what db2look and db2 do
with the vargraphic stats
HTH, Pierre.

Philip Sherman wrote:

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

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[Philip Sherman]

db2look generates the following update statement:
UPDATE SYSSTAT.COLUMNS
SET COLCARD=176, NUMNULLS=0, SUB_COUNT=-1,
SUB_DELIM_LENGTH=-1, AVGCOLLEN=28,
HIGH2KEY=X'5A6C6F7479',
LOW2KEY=X'416C67657269616E2044696E6172'
WHERE COLNAME = '........'
AND TABNAME = '.........
AND TABSCHEMA = '........';

Execution FAILS with an SQL1227N Reason=3 error.

The data value in the HIGH2KEY column (from a select) is: g'Zloty'
(hex: 67275A6C6F747927) - not the same thing that db2look gave me.

The solution to this is buried in the table definition of the column as
VARGRAPHIC. db2look recognizes this and passes back the vargraphic
string 'Zloty' in hex. A select of the column value yields: g'Zloty',
the actual string stored; indicating a vargraphic data value. Note that
I have no idea what would happen if the data value included a UTF-8
character that lies outside the standard ASCII range and required
multiple bytes for its representation.

The correct update statement is formed as follows:
UPDATE SYSSTAT.COLUMNS
SET HIGH2KEY='g'''||X'5A6C6F7479'||''''

Since I'm doing this with an application program; It'll be easy to test
the retrieved value for HIGH2KEY and LOW2KEY to detect the VARGRAPHIC
encoding and make appropriate modifications to the data.
Phil Sherman

Pierre Saint-Jacques wrote:

Have you tried using db2look with the -m parm to extract the statsd of
that table?
The oputput is a command that has the proper structure ( I should hope
at least).
You could then see if your command matches and what db2look and db2 do
with the vargraphic stats
HTH, Pierre.

Philip Sherman wrote:

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

[Philip Sherman]

db2look generates the following update statement:
UPDATE SYSSTAT.COLUMNS
SET COLCARD=176, NUMNULLS=0, SUB_COUNT=-1,
SUB_DELIM_LENGTH=-1, AVGCOLLEN=28,
HIGH2KEY=X'5A6C6F7479',
LOW2KEY=X'416C67657269616E2044696E6172'
WHERE COLNAME = '........'
AND TABNAME = '.........
AND TABSCHEMA = '........';

Execution FAILS with an SQL1227N Reason=3 error.

The data value in the HIGH2KEY column (from a select) is: g'Zloty'
(hex: 67275A6C6F747927) - not the same thing that db2look gave me.

The solution to this is buried in the table definition of the column as
VARGRAPHIC. db2look recognizes this and passes back the vargraphic
string 'Zloty' in hex. A select of the column value yields: g'Zloty',
the actual string stored; indicating a vargraphic data value. Note that
I have no idea what would happen if the data value included a UTF-8
character that lies outside the standard ASCII range and required
multiple bytes for its representation.

The correct update statement is formed as follows:
UPDATE SYSSTAT.COLUMNS
SET HIGH2KEY='g'''||X'5A6C6F7479'||''''

Since I'm doing this with an application program; It'll be easy to test
the retrieved value for HIGH2KEY and LOW2KEY to detect the VARGRAPHIC
encoding and make appropriate modifications to the data.
Phil Sherman

Pierre Saint-Jacques wrote:

Have you tried using db2look with the -m parm to extract the statsd of
that table?
The oputput is a command that has the proper structure ( I should hope
at least).
You could then see if your command matches and what db2look and db2 do
with the vargraphic stats
HTH, Pierre.

Philip Sherman wrote:

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

[Philip Sherman]

Update statistics apparently uses the source table's column definition
to determine how to handle the string containing the contents to be stored.

When the source column is VARGRAPHIC; the input string must be a
VARGRAPHIC string - each character must be 16 or more bits! Db2look
retrieves the VARCHAR string stored in the statistics column - a value
that CANNOT be re-inserted to SYSSTATS. The retrieved string is
identified as a VARGRAPHIC string by the leading 'g' and the quotes
surrounding the string.

To insert this string into the stats table, do the following:
1. Strip off the 'g' and the leading/trailing quotes.
2. Use the remaining string as input to the VARGRAPHIC function.
This will correctly convert the string to UTF-16.
3. The output of the VARGRAPHIC function should be used as the string to
be inserted in the SYSSTAT table.
Philip Sherman


Philip Sherman wrote:

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

[Philip Sherman]

Update statistics apparently uses the source table's column definition
to determine how to handle the string containing the contents to be stored.

When the source column is VARGRAPHIC; the input string must be a
VARGRAPHIC string - each character must be 16 or more bits! Db2look
retrieves the VARCHAR string stored in the statistics column - a value
that CANNOT be re-inserted to SYSSTATS. The retrieved string is
identified as a VARGRAPHIC string by the leading 'g' and the quotes
surrounding the string.

To insert this string into the stats table, do the following:
1. Strip off the 'g' and the leading/trailing quotes.
2. Use the remaining string as input to the VARGRAPHIC function.
This will correctly convert the string to UTF-16.
3. The output of the VARGRAPHIC function should be used as the string to
be inserted in the SYSSTAT table.
Philip Sherman


Philip Sherman wrote:

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