Working with Export Widths - Teradata Database

International Character Set Support

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-09-25
dita:id
B035-1125
lifecycle
previous
Product Category
Teradata® Database

The number of characters or bytes the Teradata Database exports can exceed or be less than the number of characters or bytes the client application expects to receive unless the export width is set properly.

The export width defines how the system reserves space for each character field in result set rows returned to Teradata clients. The global export width is set by default, but you can change the setting. In addition, users with the required privileges can override the global setting with a session-level export width setting.

You can use the DBS Control utility to set field 23, the Export Width Table ID field, to define the default export width definition the system uses when returning a result set to a client.

 

Export Width Table ID

DBSControl Setting

Description

Expected Defaults
(default setting)

0

Provides reasonable default widths for the character data type and client form of use.

Compatibility Default

1

Enables Unicode data to be processed by applications that written to process Latin or KANJI1 data.

Maximum Default

2

Provides maximum default width of the character data type and client form of use.

Also see the next topic, About Export Width Rules, and “Export Width Table ID” in Utilities.

The character conversion parameters for all export width definitions, including any custom definitions you create, are stored as a rule set in the DBC.ExportWidth table, for example:

SELECT * FROM DBC.ExportWidth;
ExportDefinitionName    ExportWidthRuleSet
--------------------  	 -----------------------------------------
EXPECTED                1112211111222232222211121111112222322222
MAXIMUM                 1322323221332232322321123111122322323222
COMPATIBILITY           1112211111111231111111121111112222322222
CustomDefinition1       4112211111222232222211121111112222322224

Note: Also see “Creating a Custom Export Width Definition” on page 134.

Interpreting the Export Width Rule Set for EXPECTED

To determine the export width for a character set combination:

1 Identify the ExportWidthRuleSet values that correspond to the server character set, for example, for the DBS Control export definition setting EXPECTED.

 

Server Character Set

Applicable Export Width Rule Set

LATIN

First set of 10 digits (1112211111)

UNICODE

Second set of 10 digits (2222322222)

KANJISJIS

Third set of 10 digits (1112111111)

GRAPHIC

Fourth set of 10 digits (2222322222)

Note: The ExportWidthRuleSet for a server character set is always in the same position for any ExportDefinitionName. For example, LATIN is always the first set of 10 digits.

2 Within each set of 10 digits for a server character set, you can find the export width conversion multiplier for a session character set.

 

This position in each group of 10 digits...

Is the Conversion Multiplier for....

First

  • Any session character set that ends in the string '_0I'
  • The session character set 'KATAKANAEBCDIC'
  • Second

    Any session character set that ends in the string '_0U'

    Third

    Any session character set that ends in the string '_0S'

    Fourth

    The session character set 'UTF16'

    Fifth

    The session character set 'UTF8'

    Sixth

    Any site‑defined session character set with STATEMACHINE EUC1211

    Seventh

    Any site‑defined session character set with STATEMACHINE EUC1223

    Eighth

    Any site‑defined session character set with STATEMACHINE S80

    Ninth

    Any site‑defined session character set with STATEMACHINE S80A1E0

    Tenth

    Any site‑defined session character set with STATEMACHINE SOSI0E0F

    For example:

  • When exporting from the server character set LATIN (first set of 10 digits, 1112211111), for the session character set UTF8 (the fifth position in the set), the export width is 2.
  • When exporting from the server character set UNICODE (second set of 10 digits, 2222322222) for the session character set UTF8 (the fifth position in the set), the export width 3.
  • When exporting from the server character set KANJISJIS (third set of 10 digits, 1112111111), for the session character set UTF16 (the fourth position in the set), the export width is 2.
  • Export Width Space Considerations

    The export width value is a multiplier, so that if data in the server character set is n bytes, and the export width is 2, the same data in the result set occupies 2n bytes.

    You can use HELP SESSION to view the current export widths for the session. Based on the rule set shown in “Interpreting the Export Width Rule Set for EXPECTED” on page 132, and an EXPECTED user default export width, the request returns output similar to the following:

    HELP SESSION ;
     
    User Name SPEC2
    Account Name DBC
    Logon Date 02/08/11
    Logon Time 10:58:56
    Current DataBase DBC
    Collation UTF8
    Character Set UTF8
    Current DateForm IntegerDate
    Session Time Zone -08:00
    Default Character Type LATIN
    Export Latin 2
    Export Unicode 3
    Export Unicode Adjust 0
    Export KanjiSJIS 1
    Export Graphic 3
    Export Width Rules 2313
    ...

    where:

     

    Value

    Description

    Export Latin 2

    Reports the export width value for each server character data type for the session character set (UTF8).

    Note: A 0 value means that character conversion between server and client character sets is not possible, for example, from Graphic (double-byte) to ASCII (single-byte).

    Export Unicode 3

    Export Unicode Adjust 0

    Export KanjiSJIS 1

    Export Graphic 3

    Export Width Rules 2313

    The export width values for converting the session character set to Latin, Unicode, KanjiSJIS, and Graphic.

    Note: No export width is reported for the Kanji1 character data type because the export width is always 1.

    You can create a custom export width definition by executing the ReplaceExportDefinition stored procedure, which is included with an installation of or upgrade to the current Teradata Database release.

    Note: The user executing the stored procedure must have EXECUTE privileges on the stored procedure or on the SYSLIB database.

    1 Supply values for the required stored procedure arguments. For example:

    Ex. call syslib.ReplaceExportDefinition('export_definition_name',
    'export_width_rule_set'xb,msg);

    where:

     

    Stored Procedure Argument

    Description

    export_definition_name

    A unique name for the custom export definition that conforms to the requirements for Teradata Database object names.

    export_width_rule_set

    A byte string of 40 hexadecimal digits; 10 digits for each of the 4 server character sets. For the meaning of each position n the string, see “About Export Width Rules” on page 132.

    Each position can have a value of 1, 2, 3, or 4, except the fourth position in each 10 character set (UTF16), which must have a value of either 2 or 4.

    xb

    The standard literal suffix.

  • x = hexadecimal
  • b = bytes
  • msg

    The output value of the stored procedure.

    2 Execute the stored procedure.

    The system returns an error message for any arguments that are not valid. If all arguments are valid, the system writes the new rule set to the DBC.ExportWidth table.

    The following standard error messages are generated by the ReplaceExportDefinition stored procedure when it detects syntax that is not valid.

    Export Definition Name Not Valid

    Example input:

    Ex. call syslib.ReplaceExportDefinition('EXPECTED',
    '4112211111222232222211121111112222322224'xb,results);

    Error message:

    Cannot create or update the reserve ExportDefinitionName (EXPECTED/COMPATIBILITY/MAXIMUM)

    Export Width Range Not Valid

    Example input:

    Ex. call syslib.ReplaceExportDefinition('aaa',
    '5672211111222232222211121111112222322224'xb,results);

    Error message:

    Valid range of values for the ExportWidthRuleSet are 1,2,3,4'

    Export Width Not Valid for a Location

    Example input:

    Ex. call syslib.CreateExportDefinition('testing',
    '4113211111222232222211121111112222322224'xb,results);

    Error message:

    At position fourth of ExportWidthRuleSet, valid value is 2 or 4

    You can modify an export definition with the ReplaceExportDefinition stored procedure, using the same arguments shown in “Creating a Custom Export Width Definition” on page 134.

    1 Specify the name of an existing definition that you want to modify.

    Note: You can modify only custom definitions that you create, and cannot modify the global default definitions EXPECTED, COMPATIBILITY and MAXIMUM.

    2 Specify a new export width rule set. All 40 digits are required, even if some of them have not changed.

    3 Execute the stored procedure.

    The new definition overwrites the old definition in the DBC.ExportWidth table.

    The new export width definition takes effect at the next transaction for each affected user. In process transactions continue with the existing export width definition.

    For information about the DBC.ExportWidth table, see Data Dictionary.

    You can delete an existing custom export width definition using the RemoveExportDefinition stored procedure, for example:

    Ex. call syslib.RemoveExportDefinition('exportname',msg);

    The system deletes the specified definition from the DBC.ExportWidth table. In process transactions using the removed definition continue, but it is unavailable to new transactions.

    Note: The user executing the stored procedure must have EXECUTE privileges on the stored procedure or on the SYSLIB database.

    You can define the source of export widths for a user with the CREATE USER or MODIFY USER statement, for example:

    CREATE USER username
    ...
    EXPORTWIDTH = ‘export_width_definition_name’;

    where export_width_definition_name can be:

  • One of the standard export width tables used for DBSControl settings:
  • EXPECTED
  • COMPATIBILITY
  • MAXIMUM
  • DEFAULT (defers to the current DBSControl global setting)
  • A custom ExportDefinitionName name. See “Creating a Custom Export Width Definition” on page 134.
  • See “CREATE USER” in SQL Data Definition Language Syntax and Examples.

    When determining the export width for a session:

  • If the user initiating the session has an assignment in the EXPORTWIDTH option for the user definition, the system uses the assigned export width definition.
  • If the user initiating the session does not have an assignment in the EXPORTWIDTH option in the user definition, the system uses the global default export width definition specified in the DBSControl Export Width Table ID field.
  • Note: The session‑level table ID is automatically saved in DBC.SessionTbl and is restored after a restart.