15.00 - Session Parameters - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Session Parameters

The following session parameters can be controlled with keywords or predefined system variables.

 

Parameter

Valid Keywords or System Variables

SQL Flagger

ON

OFF

Transaction Mode

ANSI (COMMIT)

Teradata (BTET)

Session Collation

ASCII

EBCDIC

MULTINATIONAL

HOST

CHARSET_COLL

JIS_COLL

Account and Priority

Account and reprioritization. Within the account identifier, you can specify a performance group or use one of the following predefined performance groups:

  • $R
  • $H
  • $M
  • $L
  • Date Form

    ANSIDATE

    INTEGERDATE

    Character Set

    Indicates the character set being used by the client.

    You can view site-installed client character sets from DBC.CharSetsV or DBC.CharTranslationsV.

    The following client character sets are permanently enabled:

  • ASCII
  • EBCDIC
  • UTF-8
  • UTF-16
  • For more information on character sets, see International Character Set Support.

    SQL Flagger

    When enabled, the SQL Flagger assists SQL programmers by notifying them of the use of non-ANSI and non-entry level ANSI/ISO SQL syntax.

    Enabling the SQL Flagger can be done regardless of whether you are in ANSI or Teradata session mode.

    To set the SQL Flagger on or off for BTEQ, use the .SET SESSION command.

     

    To set this level of flagging …

    Set the flag variable to this value …

    None

    SQLFLAG NONE

    Entry level

    SQLFLAG ENTRY

    Intermediate level

    SQLFLAG INTERMEDIATE

    For more detail on using the SQL Flagger, see “Using the SQL Flagger” on page 189.

    To set the SQL Flagger on or off for embedded SQL, use the SQLCHECK or -sc and SQLFLAGGER or -sf options when you invoke the preprocessor.

    If you are using SQL in other application programs, see the reference manual for that application for instructions on enabling the SQL Flagger.

    Transaction Mode

    You can run transactions in either Teradata or ANSI session modes and these modes can be set or changed.

    To set the transaction mode, use the .SET SESSION command in BTEQ.

     

    To run transactions in this mode …

    Set the variable to this value …

    Teradata

    TRANSACTION BTET

    ANSI

    TRANSACTION ANSI

    For more detail on transaction semantics, see “Transaction Processing” in SQL Request and Transaction Processing.

    If you are using SQL in other application programs, see the reference manual for that application for instructions on setting or changing the transaction mode.

    Session Collation

    Collation of character data is an important and complex option. Teradata Database provides several named collations. The MULTINATIONAL and CHARSET_COLL collations allow the system administrator to provide collation sequences tailored to the needs of the site.

    The collation for the session is determined at logon from the defined default collation for the user. You can change your collation any number of times during the session using the SET SESSION COLLATION statement, but you cannot change your default logon in this way.

    Your default collation is assigned via the COLLATION option of the CREATE USER or MODIFY USER statement. This has no effect on any current session, only new logons.

    Each named collation can be CASESPECIFIC or NOT CASESPECIFIC. NOT CASESPECIFIC collates lowercase data as if it were converted to uppercase before the named collation is applied.

     

    Collation Name

    Description

    ASCII

    Character data is collated in the order it would appear if converted for an ASCII session, and a binary sort performed.

    EBCDIC

    Character data is collated in the order it would appear if converted for an EBCDIC session, and a binary sort performed.

    MULTINATIONAL

    The default MULTINATIONAL collation is a two-level collation based on the Unicode collation standard.

    Your system administrator can redefine this collation to any two-level collation of characters in the LATIN repertoire.

    For backward compatibility, the following are true:

  • MULTINATIONAL collation of KANJI1 data is single level.
  • The system administrator can redefine single byte character collation.
  • This definition is not compatible with MULTINATIONAL collation of non-KANJI1 data. CHARSET_COLL collation is usually a better solution for KANJI1 data.

    See “ORDER BY Clause” in SQL Data Manipulation Language. For information on setting up the MULTINATIONAL collation sequence, see “Collation Sequences” in International Character Set Support.

    HOST

    The default. HOST collation defaults are:

  • EBCDIC collation for mainframe systems.
  • ASCII collation for all others.
  • CHARSET_COLL

    Character data is collated in the order it would appear if converted to the current client character set and then sorted in binary order.

    CHARSET_COLL collation is a system administrator-defined collation.

    JIS_COLL

    Character data is collated based on the Japanese Industrial Standards (JIS).

    JIS characters collate in the following order:

    1 JIS X 0201-defined characters in standard order

    2 JIS X 0208-defined characters in standard order

    3 JIS X 0212-defined characters in standard order

    4 KanjiEBCDIC-defined characters not defined in JIS X 0201, JIS X 0208, or JIS X 0212 in standard order

    5 All remaining characters in Unicode standard order

    For details, see “SET SESSION COLLATION” in SQL Data Definition Language.

    Account and Priority

    You can dynamically downgrade or upgrade the performance group priority for your account.

    Priorities can be downgraded or upgraded at either the session or the request level. For more information, see “SET SESSION ACCOUNT” in SQL Data Definition Language.

    Note: Changing the performance group for your account changes the account name for accounting purposes because a performance group is part of an account name.

    Date Form

    You can change the format in which DATE data is imported or exported in your current session.

    DATE data can be set to be treated either using the ANSI date format (DATEFORM=ANSIDATE) or using the Teradata date format (DATEFORM=INTEGERDATE).

    For details, see “SET SESSION DATEFORM” in SQL Data Definition Language.

    Setting the Client Character Set

    To set the client character set, use one of the following:

  • From BTEQ, use the BTEQ [.] SET SESSION CHARSET ‘name’ command.
  • In a CLIv2 application, call CHARSET name.
  • In the URL for selecting a Teradata JDBC driver connection to a Teradata Database, use the CHARSET=name database connection parameter.
  • where the ‘name’ or name value is ASCII, EBCDIC, UTF-8, UTF-16, or a name assigned to the translation codes that define an available character set.

    If not explicitly requested, the session default is the character set associated with the logon client. This is either the standard client default, or the character set assigned to the client by the database administrator.

    HELP SESSION

    The HELP SESSION statement identifies attributes in effect for the current session, including:

  • Transaction mode
  • Character set
  • Collation sequence
  • Date form
  • Queryband
  • For details, see “HELP SESSION” in SQL Data Definition Language.