Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 218 219 220 221 222 223 < 224 > 225 226 227 228 229 230 .. 241 >> Next


REPLACE MACRO CollInstallMulti (CollationName CHAR(30)) AS (ABORT 'Argument cannot be MULTINATIONAL collation'

WHERE :CollationName='MULTINATIONAL';

ABORT 'Specified collation has not been defined'

WHERE :CollationName NOT IN (SELECT CollName FROM DBC.Collation);

G-34

Teradata RDBMS for UNIX SQL Reference
International and Japanese Character Support

Defining Your Own Collation Sequence

Columns of the DBC.Collations View

Inserting Your Collation Codes

DELETE FROM DBC.Collations WHERE CollName='MULTINATIONAL';

INSERT INTO DBC.Collations

SELECT 'MULTINATIONAL', 'Y', CollEqvClass, CollOrderCS, CollOrderUC FROM DBC.Collation WHERE CollName=:CollationName;);

The name, attributes, and use of each column in DBC.Collations are as follows:

• CollName CHAR(30) NOT NULL

Enter any legal name except ’MULTINATIONAL’. (When executed, the CollInstallMulti macro accept any legal name other than ’MULTINATIONAL’.)

• CollInstall CHAR(I) NOT NULL

Enter the letter ’N’ (no). (When executed, CollInstallMulti will insert a ’Y’ indicating that this row is to be installed.)

• CollEqvClass BYTE (256) BETWEEN 0x00 AND OxFF NOT NULL

Specify, for each character, the value of its equivalent class.

For standard Japanese collations, each value in this field must be OxOO

• CollOrderCS BYTE (256) BETWEEN 0.00 AND 0xFF NOT NULL

Specify, for each character, the relative order of the character within its class for case-specific comparison.

• CollOrderUC BYTE (256) BETWEEN 0x00 AND 0xFF NOT NUL

Specify, for each character, the relative order of the character within its class for uppercase comparison.

Do not access the DBC.CollationTbl system table directly. Instead, use the DBC.Collations view.

Teradata RDBMS for UNIX SQL Reference

G-35
International and Japanese Character Support

Defining Your Own Collation Sequence

To populate the table through the view, perform the following procedure.

Step Action
1 Create a BTEQ batch file containing a CollName, a CollInstall value, and the hexadecimal definition of every character in the /new collation sequence. Be sure that entries exist for every column of the DBC.Collations view. The value of CollName cannot be MULTINATIONAL. Also, the first Teradata SQL statement in the job file should be a DELETE statement to remove any existing row that might have the same CollName (see the example below)
2 Log on to Teradata as system user DBC or SYSADMIN.
3 Verify that the DIP file named dipvws.sql was loaded during installation into system database DBC. This file creates the system tables and views of the Teradata RDBMS Data Dictionary. To verify the contents of DBC, enter the following statement: HELP DATABASE DBC; In order to implement a user-defined collation sequence, the following objects must appear in the HELP listing for DBC: • A table named CollationTbl • A view named Collations • Two macros, named: • CollAddStandard • CollInstallMulti
4 Run the BTEQ batch job you created in step 1. The row you insert into the DBC.Collations view will automatically populate the DBC.CollationTbl

Installing Your Collation Codes

To install the collation codes you inserted in the DBC.Collations view, perform the following procedure.

Step

Action

Execute the CollInstallMulti macro, as follows:

EXECUTE CollInstallMulti ('CollationName');

where ’CollationName’ is the CollName you specified in your BTEQ job.

Restart the Teradata RDBMS (enter the restart tpa command from the DB window of the system console) to install the codes as the new default collation sequence.

1

2

G-36

Teradata RDBMS for UNIX SQL Reference
International and Japanese Character Support

Defining Your Own Collation Sequence

From now on, the collation sequence you just installed will be the default when the COLLATION option is set to MULTINATIONAL.

Example of Inserting and Installing a User-Defined Collation Sequence

Note: You must restart the Teradata RDBMS to complete the installation.

/*

* First delete from DBC.Collations any existing definitions and then insert the new definitions */

DELETE FROM DBC.Collations WHERE CollName = 'NON_STANDARD';

INSERT INTO DBC.Collations ( CollName , CollInstall , CollEqvClass , CollOrderCS , CollOrderUC )

VALUES

( 'NON_STANDARD'

, 'N'

* CollEqvClass. Equivalence class. */
* 0 1 2 3 4 5 6 7 8 9 A B C D E F */
'000102 03 04 0506 0708090A0B0C0D0E0F xb /* 0 */
|'101112131415161718191A1B1C1D1E1F xb /* 1 */
|'2021222 3242 52 62 72 8292A2B2C2D2E2F xb /* 2 */
|'303132 3 3 34 3 53 6 3 73 8393A3B3C3D3E3F xb /* 3 */
|'406162 6 3 64 6 56 6 6 76 8696A6B6C6D6E6F xb /* 4 */
|'707172737475767778797AB0B1B2B3B4 xb /* 5 */
|'B56162 6 3 64 6 56 6 6 76 86 96A6B6C6D6E6F xb /* 6 */
|'707172737475767778797AB6B7B8B9BA xb /* 7 */
|'EAEBECEDBBBCBD80BFC0C1C2C3C4C5C6 xb /* 8 */
|'C7C8C9CACBCCCDCEEEEFF0CFD0D1D2D3 xb /* 9 */
|'F1D4D5D6F2D7F3D8D9DADBDCF4F5F6F7 xb /* A */
Previous << 1 .. 218 219 220 221 222 223 < 224 > 225 226 227 228 229 230 .. 241 >> Next