Upgrade Guide for Microsoft Windows > Preupgrade Tasks > Preparing a Database for Upgrade >

Verifying Database Sort Order

Sort order (also called collation sequence) is specified during the initial installation of a database and defines the way in which the database sorts character data. Sort order support depends on both the code page of the database and whether it is used in a development or a production environment.

NOTE:  The settings for binary are unique for each database platform; for example, binary sort order is Identity sort order on IBM DB2 UDB and binary collation sequence on Microsoft SQL Server. See Siebel System Requirements and Supported Platforms to determine supported settings for your database platform.

Sort Order Considerations for Siebel Databases

If your deployment requires that you use a nonbinary sort order (for example, if your local language does not use binary sort order), you must consider several functional limitations that particularly affect development environment upgrades. If these limitations are unacceptable, consider recreating your database to use binary sort order (called Identity sort order on DB2 UDB).

See Siebel System Requirements and Supported Platforms to verify sort order and code page requirements for your deployment. Read the following instructions to verify that your database was created using the correct sort order.

Verifying Collation Sequence on Microsoft SQL Server

On MS SQL Server, the collation name of a database instance is specified during database creation and defines the way in which the instance sorts character data.

Although each SQL Server system database and each object within a database can have its own unique collation, it is strongly recommended that you set the collation at the SQL Server instance level at the time of your installation of SQL Server. Each database created under this instance that is used by the Siebel application inherits the collation characteristics from the instance. (The sort order at the instance level and the database level should be the same to prevent a repository merge failure with errors related to collation sequence.)

NOTE:  Latin1_General_BIN is the value that translates to SQL 7.0 Unicode General Binary for Unicode and 1252 databases.

Siebel support for a given sort order depends on both the code page of the database and whether it is used in a development or a production environment. Review Sort Order Considerations for Siebel Databases, then review Siebel System Requirements and Supported Platforms to verify sort order and code page requirements for your implementation.

CAUTION:  Latin1_General_BIN is not the default collation sequence on SQL Server (the default installation setting is typically dictionary). When installing MS SQL Server, the instance is set by default to dictionary sort order and, if not changed, every database inherits this setting. The master database cannot be changed without rebuilding the instance. Siebel Systems, therefore, strongly recommends that the instance collation sequence be set to Latin1_General_BIN at installation time. Please consult your Microsoft documentation for instructions on setting database collation.

To verify that your database was created using a binary collation sequence

  1. In the Query Analyzer window, enter the following command:

    sp_helpsort

    This command provides a sort order description.

  2. Review the sort order description to verify binary sort order; for example,

    Latin1_General_BIN

    If you find that your Microsoft SQL Server database was not created using a binary collation sequence, you must rebuild your database and reload your data. Please review Microsoft documentation for detailed instructions.

Converting Oracle Databases to a Supported Code Page

Several code pages are no longer supported in Release 7.5 because these code pages might contain 1252 characters (for example, the euro symbol, smart quotes, or hyphens) that become corrupted during an upgrade. Carefully review Siebel System Requirements and Supported Platforms to determine which code pages are supported.

If you want to continue to use a non-Unicode Western European character set code page, you must covert your Oracle database to a supported code page prior to upgrading to Release 7.5.

CAUTION:  Limitations to code page support apply to both the database server and the Client. Check Oracle Client settings for all of your Clients and servers.

To convert your Oracle database to a supported code page, review Siebel System Requirements and Supported Platforms for supported database code page information, then refer to Siebel SupportWeb for detailed instructions for using database vendor utilities to convert your Oracle database to a supported code page.

Verifying Sort Order on Oracle

See Siebel System Requirements and Supported Platforms to verify sort order and code page requirements for your deployment. Siebel Systems strongly recommends binary sort order on your Oracle database, due to limitations of databases that use nonbinary sort. (See Sort Order Considerations for Siebel Databases.)

Sort order on Oracle is determined by the NLS_SORT parameter on the Oracle Client. Perform the steps below to set NLS_SORT to BINARY, or choose a NLS_LANG setting that includes binary.

CAUTION:  The NLS_LANG parameter must be set to the same value throughout your enterprise, and it must match the database character set. The NLS_LANG parameter is required for conversion from a non-Unicode code page to Unicode; an incorrect setting could lead to data loss.

The NLS_NCHAR_CHARACTERSET parameter is not used by a non-Unicode Siebel Application.

To verify that your database was created using binary sort order

  1. Use SQLPlus to connect the Oracle database.
  2. Issue the following query:

    SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

  3. Review the returned parameters for NLS_SORT, and verify that the value for this parameter is BINARY.
    • If NLS_SORT has a value of BINARY, then the default sort order is binary and no action is required.
    • If NLS_SORT is anything other than BINARY, then you must re-create the database so that it uses binary sort order. Review Sort Order Considerations for Siebel Databases, then see Siebel System Requirements and Supported Platforms for supported values.

Verifying Sort Order on IBM DB2 UDB

See Siebel System Requirements and Supported Platforms to verify sort order and code page requirements for your deployment. Siebel Systems requires Identity sort order on DB2 UDB development databases. Identity sort order is also highly recommended for DB2 UDB production environment databases.

To verify that your database was created using Identity sort order

  1. Run the following query on Siebel database:

    select count (*) from S_APP_VER where '$' > '/'

  2. Review the result.
    • If sort order is correct, the result is

    1
    --------------
    0
    (1) record selected.

    • If sort order is incorrect, you must re-create the database, using the option:

    COLLATE USING IDENTITY

NOTE:  Sort order is specified during creation of the database. If you find that your IBM DB2 UDB development database was not created using Identity sort order, you must re-create your database using the option COLLATE USING IDENTITY.

If sort order is correct, but you are still encountering errors, contact Siebel Technical Services for further analysis.


 Upgrade Guide for Microsoft Windows
 Published: 20 October 2003