Think of it this way:
With collate using identity, the sort is a "bit" more granular. It will
sort ascii a little different than system. The characters like a null '00'
will be
first in a sort with identity and they will be much later with system.
Dan Callahan IT Specialist
603-472-4121 t/l 525-4121
[Email address protected]
[Email address protected]
"D OLLONE Clara "
<db2udbdba-ezmlms
hield-x10856052.x To
[Email Address Removed] "LazyDBA Discussion"
a.com> <[Email address protected]
cc
06/25/2004 10:47
AM Subject
RE: collating sequence
Thank for your reply.
I am using UDB on Window 2000. I know that collating sequence is specified
when creating database and cannot be changed after. But I don't know what
is his role in the life of the database.
And what is the difference between COLLATE USING SYSTEM and COLLATE USING
IDENTITY ?
The below explanation is little complicated for me. I need to understand
its usefulness. Can you summarize that ?
Clara.
> -----Message d'origine-----
> De: Thirumaran [SMTP:[Email address protected]
> Date: vendredi 25 juin 2004 14:54
> À: CLARA.[Email address protected]
> Objet: Re: collating sequence
>
> **A LazyDBA.com subscriber has responded to your lazydba.com post**
> **LazyDBA.com mail shield has forwarded you this email,
> **and removed any attachments, and kept your email address secret
> **from this person, and any viruses/trojans.
> **If you reply to this email, the person will see your email address as
normal
> **Anything below this line is the original email text
>
>
> hi,
>
> Collating Sequence Specified when Database Is Created
> The collating sequence for a database is specified at database creation
> time. Once the database has been created, the collating sequence cannot
be
> changed.
>
> The CREATE DATABASE API accepts a data structure called the Database
> Descriptor Block (SQLEDBDESC). You can define your own collating sequence
> within this structure.
>
> Note:
> You can only define your own collating sequence for a single-byte
> database.
> To specify a collating sequence for a database:
>
> a.. Pass the desired SQLEDBDESC structure, or
> b.. Pass a NULL pointer. The collating sequence of the operating system
> (based on current country/region code and code page) is used. This is the
> same as specifying SQLDBCSS equal to SQL_CS_SYSTEM (0).
> The SQLEDBDESC structure contains:
>
> SQLDBCSS
> A 4-byte integer indicating the source of the database collating
sequence.
> Valid values are:
> SQL_CS_SYSTEM
> The collating sequence of the operating system (based on current
> country/region code and code page) is used.
> SQL_CS_SYSTEM_NLSCHAR
> Collating sequence from user using the NLS version of compare
routines
> for character types
> SQL_CS_IDENTITY_16BIT
> A Unicode database can be created with the SQL_CS_IDENTITY_16BIT
> collation option. SQL_CS_DENTITY_16BIT differs from the default
SQL_CS_NONE
> collation option in that the CHAR, VARCHAR, LONG VARCHAR, and CLOB data
in
> the Unicode database will be collated using the CESU-8 binary order
instead
> of the UTF-8 binary order. CESU-8 is Compatibility Encoding Scheme for
> UTF-16: 8-Bit, and as of this writing, its specification is contained in
the
> Draft Unicode Technical Report #26 available at the Unicode Technical
> Consortium web site (www.unicode.org). CESU-8 is binary identical to
UTF-8
> except for the Unicode supplementary characters, that is, those
characters
> that are defined outside the 16-bit Basic Multilingual Plane (BMP or
Plane
> 0). In UTF-8 encoding, a supplementary character is represented by one
> 4-byte sequence, but the same character in CESU-8 requires two 3-byte
> sequences. In a Unicode database, CHAR, VARCHAR, LONG VARCHAR, and CLOB
data
> are stored in UTF-8, and GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB
> data are stored in UCS-2. For SQL_CS_NONE collation, non-supplementary
> characters in UTF-8 and UCS-2 have identical binary collation, but
> supplementary characters in UTF-8 collate differently from the same
> characters in UCS-2. SQL_CS_IDENTITY_16BIT ensures all characters,
> supplementary and non-supplementary, in a DB2(R) Unicode databases have
the
> same binary collation.
> SQL_CS_USER
> The collating sequence is specified by the value in the SQLDBUDC
field.
> SQL_CS_NONE
> The collating sequence is the identity sequence. Strings are compared
> byte for byte, starting with the first byte, using a simple code point>
> comparison.
> Note:
> These constants are defined in the SQLENV include file.
> SQLDBUDC
> A 256-byte field. The nth byte contains the sort weight of the nth
> character in the code page of the database. If SQLDBCSS is not equal to
> SQL_CS_USER, this field is ignored.
> Related reference
>
> a.. sqlecrea - Create Database
> Regards
> thiru
>
> ----- Original Message -----
> From: "D OLLONE Clara "
> <db2udbdba-ezmlmshield-x59815415.[Email address protected]
> To: "LazyDBA Discussion" <[Email address protected]
> Sent: Friday, June 25, 2004 5:39 PM
> Subject: collating sequence
>
>
> > Hi everybody,
> >
> > What is 'collating sequence' in database ?
> >
> >
> > ---------------------------------------------------------------------
> > PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> > website: http://www.LazyDBA.com
> > To unsubscribe: http://www.lazydba.com/unsubscribe.html
> > For additional commands, e-mail: db2udbdba-[Email address protected]
> >
>
>
>
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: db2udbdba-[Email address protected]
DB2 & UDB email list listserv db2-l LazyDBA home page