SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-59
Creating Collation Objects
Creating Collation Objects
To create SQL collation objects, you must have the collation compiler compiled as an
SQL program and registered in a catalog, as explained under Installing SQL/MP on
page 2-2. When you issue the CREATE COLLATION statement, SQL invokes the
collation compiler to compile the source file you specify.
Create all the collations for your database first, before you create the objects that refer
to collations. To create a collation, you can specify an existing collation source file in a
CREATE COLLATION statement, as in this example:
>> CREATE COLLATION COLL1
>> FROM =COLLATE1
>> CATALOG =SALES ;
The DEFINE =COLLATE1 specifies the source file.
You can also create a collation like another collation, without specifying a source file:
>> CREATE COLLATION COLL2
>> LIKE COLL1
>> CATALOG =INVENT ;
Each collation has a single-byte character set associated with it, stored in the
CHARACTERSET column of the CPRULES catalog table. This character set must
match the character set associated with a column when you use the COLLATE clause
to define or specify an index for a column.
Securing Collations
When you define a collation, SQL assigns it your current default security. You can alter
this security, but be careful. If you alter the security so that fewer users have access to
the collation, you also restrict the access to any table, index, view, or program that
uses the collation.
For example, suppose that the database administrator creates a collation and makes it
available to an application developer, and the developer creates a table that uses the
collation. Then the DBA alters the security to a string that takes access to the collation
away from the developer. When the developer attempts to compile SQL statements
that refer to the table, the compilations fail because of security violations. SQL reports
the name of the collation causing the violations, however.
To alter the security of a collation, you must have authority to read and write to the
collation and the catalog in which the collation is registered. You can alter a collation's
security by using the ALTER COLLATION statement or the SECURE command.
Note. Consider the locations for your collations carefully. Moving a collation after you create
dependent objects is difficult. To move a collation, you must stop transaction processing on all
dependent objects, save the object definitions and data, delete the dependent objects, copy the
collation to the new location, delete the original collation, and then re-create the dependent
objects.