Changing the Standard Multinational Default Collation

Teradata Database International Character Set Support

brand
Software
prodname
Teradata Database
vrm_release
15.00
15.10
category
Configuration
User Guide

After running the DIP utility, a privileged user can execute the CollInstallMulti macro to change the default sequence for multinational collation. The EXECUTE privilege on this macro is initially assigned to system users SYSADMIN and DBC.

After the change is installed, the new ordering is enforced for the session of any user whose COLLATION attribute is MULTINATIONAL, or who submits the following SQL statement:

   SET SESSION COLLATION MULTINATIONAL;

During a multinational session, the Teradata Database checks a system table for a row containing the value MULTINATIONAL in the CollName column. The hexadecimal codes in that row determine the sort sequence for the session.

The Teradata Database provides a set of objects in database DBC with which you can manipulate, build, and maintain codes for the various collation options for Multinational users. These objects include:

  • A system table that you can access by means of the DBC.CollationsV view in which each row contains the definition of one collation sequence.
  • Note that only one definition can be in effect at any given time. The default collation is based on the codes in the row where:

       CollName=MULTINATIONAL
       CollInstall=Y
       
  • A system view named DBC.CollationsV.
  • Insert site-defined collation codes through this view to populate a row in the underlying system table. The view is created with the clause GRANT SELECT TO PUBLIC to make it accessible to all Teradata Database users.

  • A system macro named DBC.CollInstallMulti.
  • This macro is invoked by a privileged user to replace the current MULTINATIONAL collation with Teradata Database or site-defined codes that have been loaded into a row of the system table.

    A system restart is required before the new collation becomes the current MULTINATIONAL collation.

  • A system macro named DBC.CollAddStandard.
  • This macro is run automatically when the DIP utility is executed during the Teradata Database installation procedure.

    To change the Teradata standard MULTINATIONAL collation to the standard Swedish or Norwegian collation, perform the following procedure:

    1 Log on to a BTEQ session as user SYSADMIN or user DBC.

    2 Execute the DBC.CollInstallMulti macro, specifying the desired standard as the CollationName parameter for the macro.

    For example, any of the following:

    EXECUTE DBC.CollInstallMulti (‘NORWEGIAN_STANDARD’);
    EXECUTE DBC.CollInstallMulti (‘SWEDISH_STANDARD’);
    EXECUTE DBC.CollInstallMulti (‘KANJI5026_STANDARD’);
    EXECUTE DBC.CollInstallMulti (‘KANJI5035_STANDARD’);
    EXECUTE DBC.CollInstallMulti (‘KATAKANA_STANDARD’);

    3 On the Command Line of the Database Window of the system console, enter the restart tpa command to restart the Teradata Database.

    During restart processing, the Teradata Database displays a message indicating whether the specified collation was installed.

    To uninstall a nonstandard collation and return to the Teradata standard MULTINATIONAL collation, perform the following procedure:

    1 Log on to a BTEQ session as system user SYSADMIN or DBC.

    2 Do one of the following:

  • Delete the row from DBC.CollationsV where CollName=’MULTINATIONAL’
  • Change to N (no) the value of the CollInstall column in the row where CollName=‘MULTINATIONAL’
  • 3 To reload the Teradata Standard Multinational collation, enter the following command to execute the DBC.CollAddStandard macro:

    EXECUTE DBC.CollAddStandard;

    4 Restart the Teradata Database.

    You can also execute the DBC.CollAddStandard macro to reload the Teradata Standard Multinational collation if the contents of the system table become corrupted. Remember to restart the Teradata Database to register the codes.

    The following script is an example of uninstalling and installing a standard Japanese character collation:

       DELETE FROM DBC.CollationsV 
         WHERE CollName = 'KANJI5026_STANDARD';
       INSERT INTO DBC.CollationsV
        ( CollName
        , CollInstall
        , CollEqvClass
        , CollOrderCS
        , CollOrderUC )
        VALUES
        ( 'KANJI5026_STANDARD'
        , 'N'
        /* CollEqvClass. Equivalence class.             */
        /*  0 1 2 3 4 5 6 7 8 9 A B C D E F             */
        , '00000000000000000000000000000000'xb  /* 00__ */
        ||'00000000000000000000000000000000'xb  /* 01__ */
        ||'00000000000000000000000000000000'xb  /* 02__ */
        ||'00000000000000000000000000000000'xb  /* 03__ */
        ||'00000000000000000000000000000000'xb  /* 04__ */
        ||'00000000000000000000000000000000'xb  /* 05__ */
        ||'00000000000000000000000000000000'xb  /* 06__ */
        ||'00000000000000000000000000000000'xb  /* 07__ */
        ||'00000000000000000000000000000000'xb  /* 08__ */
        ||'00000000000000000000000000000000'xb  /* 09__ */
        ||'00000000000000000000000000000000'xb  /* 0a__ */
        ||'00000000000000000000000000000000'xb  /* 0b__ */
        ||'00000000000000000000000000000000'xb  /* 0c__ */
        ||'00000000000000000000000000000000'xb  /* 0d__ */
        ||'00000000000000000000000000000000'xb  /* 0e__ */
        ||'00000000000000000000000000000000'xb  /* 0f__ */
        /*  0 1 2 3 4 5 6 7 8 9 A B C D E F             */
        /* CollOrderCS. Case-specific ordering          */
        /*  0 1 2 3 4 5 6 7 8 9 A B C D E F             */ 
        , '00010203372d2e2f1605250b0c0d0e0f'xb  /* 00__ */
        ||'b14a5fb2a03d322618193f271c1d1e1f'xb  /* 01__ */
        ||'405a7f7be06c507d4d5d5c4e6b604b61'xb  /* 02__ */
        ||'f0f1f2f3f4f5f6f7f8f97a5e4c7e6e6f'xb  /* 03__ */
        ||'7cc1c2c3c4c5c6c7c8c9d1d2d3d4d5d6'xb  /* 04__ */
        ||'d7d8d9e2e3e4e5e6e7e8e9705b80b06d'xb  /* 05__ */
        ||'79626364656667686971727374757677'xb  /* 06__ */
        ||'788b9babb3b4b5b6b7b8b9c04fd0a107'xb  /* 07__ */
        ||'102122232415061728292a2b2c090a1b'xb  /* 08__ */
        ||'30311a333435360838393a3b04143eff'xb  /* 09__ */
        ||'11414243444546474849515253545556'xb  /* 0a__ */
        ||'588182838485868788898a8c8d8e8f90'xb  /* 0b__ */
        ||'9192939495969798999a9d9e9fa2a3a4'xb  /* 0c__ */
        ||'a5a6a7a8a9aaacadaeafbabbbcbdbebf'xb  /* 0d__ */
        ||'2057596a9ccacbcccdcecfdadbdcddde'xb  /* 0e__ */
        ||'dfe1eaebecedeeeffafbfcfd3c1213fe'xb  /* 0f__ */
        /*  0 1 2 3 4 5 6 7 8 9 A B C D E F             */
        /* CollOrderUC. uppercase ordering              */
        /*  0 1 2 3 4 5 6 7 8 9 A B C D E F             */
        , '00010203372d2e2f1605250b0c0d0e0f'xb  /* 00__ */
        ||'b14a5fb2a03d322618193f271c1d1e1f'xb  /* 01__ */
        ||'405a7f7be06c507d4d5d5c4e6b604b61'xb  /* 02__ */
        ||'f0f1f2f3f4f5f6f7f8f97a5e4c7e6e6f'xb  /* 03__ */
        ||'7cc1c2c3c4c5c6c7c8c9d1d2d3d4d5d6'xb  /* 04__ */
        ||'d7d8d9e2e3e4e5e6e7e8e9705b80b06d'xb  /* 05__ */
        ||'79c1c2c3c4c5c6c7c8c9d1d2d3d4d5d6'xb  /* 06__ */
        ||'d7d8d9e2e3e4e5e6e7e8e9c04fd0a107'xb  /* 07__ */
        ||'102122232415061728292a2b2c090a1b'xb  /* 08__ */
        ||'30311a333435360838393a3b04143eff'xb  /* 09__ */
        ||'11414243444546474849515253545556'xb  /* 0a__ */
        ||'588182838485868788898a8c8d8e8f90'xb  /* 0b__ */
        ||'9192939495969798999a9d9e9fa2a3a4'xb  /* 0c__ */
        ||'a5a6a7a8a9aaacadaeafbabbbcbdbebf'xb  /* 0d__ */
        ||'2057596a9ccacbcccdcecfdadbdcddde'xb  /* 0e__ */
        ||'dfe1eaebecedeeeffafbfcfd3c1213fe'xb  /* 0f__ */
        /*  0 1 2 3 4 5 6 7 8 9 A B C D E F             */
        );