Setting string_units

[Troels Arvin]

Hello,

My http://troels.arvin.dk/db/rdbms/ page is getting outdated, so I
thought it was time to play a bit with DB2 10.5, especially after reading
that DB2 10.5 might finally have fixed Unicode string handling, so that
the length of a string like "Fej�" is four (and not five).

But it seems the length function still doesn't get it. The
CHARACTER_LENGTH function gets it, but it requires a special CODEUNITS16
or CODEUNITS32 argument; this is absurd, as a developer shouldn't have to
care about such byte handling stuff. And so many other DBMSes have no
problem handling something as basic as the length of strings; one would
think DB2 can handle such fundamentals.

I then saw the "string_units" DB configuration parameter and thought that
it should be able to help me, but:

$ db2 update db cfg using STRING_UNITS CODEUNITS32
SQL5130N The value specified for the configuration parameter
"string_units" is not in the valid range of "0" to "0".

I strongly believe that the database I had created was a Unicode one;
selected DB cfg parameters in the database:
Database territory = US
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = UCA500R1_LDA_AN_CU_EX_FX_HX_NX_S3
Alternate collating sequence (ALT_COLLATE) =
Varchar2 compatibility = OFF
CHAR output with DECIMAL input (DEC_TO_CHAR_FMT) = NEW
National character string mapping (NCHAR_MAPPING) = GRAPHIC_CU16

Have I done something wrong? Why can't I set STRING_UNITS to CODEUNITS32,
as described at
http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/
com.ibm.db2.luw.admin.config.doc/doc/r0060936.html

--
Troels

[Lennart Jonsson]

I'm puzzled too, I have verified the behaviour you describe on 32bit
10.5 fp 0, and 64 bit 10.5 fp 1. Does anyone know the reason why SYSTEM
is the only allowed value for STRING_UNITS, despite what the
documentation says?

FWIW I agree that the default behaviour is peculiar. If one is
interested in the space allocated by a row one still has to do
additional computations.


/Lennart

[Lennart Jonsson]

On 12/03/2013 08:50 PM, Lennart Jonsson wrote:
[...]

> I'm puzzled too, I have verified the behaviour you describe on 32bit
> 10.5 fp 0, and 64 bit 10.5 fp 1. Does anyone know the reason why SYSTEM
> is the only allowed value for STRING_UNITS, despite what the
> documentation says?
>
> FWIW I agree that the default behaviour is peculiar. If one is
> interested in the space allocated by a row one still has to do
> additional computations.
>

Troels, I gave some feedback in the comment section of the online docs.
The doc people usually gives really good explanations. I'll post back if
I get any feedback from them.

If you are entitled to by your licence you should probably open an PMR.
We are still on 9.7 so I'm playing with express-c.


/Lennart

[Lennart Jonsson]

On 12/05/2013 09:08 AM, Lennart Jonsson wrote:
>
> Troels, I gave some feedback in the comment section of the online docs.
> The doc people usually gives really good explanations. I'll post back if
> I get any feedback from them.
>

This is the answer I got:

The CODEUNITS32 keyword is currently not available, but will be
implemented in a future release of the DB2 product. This limitation will
be reflected in the documentation after the next documentation refresh.
Thanks for pointing that out!

/Lennart

[Frederik Engelen]

Strange, since someone from IBM once told me (pre-v10 days) that this was already implemented as a hidden feature. Anyway, for me the only thing that really works in this regards are vargraphic strings. I was dissapointed too that we didn't get VARCHAR( x CHAR) type that didn't double the storage needs in v10, especially given the extended row size support.

--
Frederik Engelen

[Frederik Engelen]

Reviving this old thread since FP4 came to the rescue.

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0060936.html?cp=SSEPGG_10.5.0%2F2-4-4-8-137

Anyone tried it already?

Frederik

[wayne.p...@sasktel.net]

FP 4 installed
It now supports

db2 update db cfg using STRING_UNITS CODEUNITS32

However initial tests still show length fields are 1 byte too long for each special character such as those you noted.
My IMPORT is failing because 64 byte of data containing 1 special character is too long to load into a varchar(64) column.

SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001

[wayne.p...@sasktel.net]

Fixed...I had to set the Locale in env i.e. to en_US.UTF-8.