Sessions

Sessions

Whatever
happens in terms of communication between an RDBMS server and a user accessing
it happens in the context of a session. In a multiuser
environment, one of the primary concerns is data integrity. When a client
application establishes a connection to an RDBMS server, it is said that it
opens a session. The session becomes this application's
private communication channel. The user of the application may change some
preferences within the session (for example, default language or default date
format); these settings would affect only the session environment and remain
valid only for the duration of the session. The details of the implementation
and default behavior of the sessions might differ among the RDBMS, but these
basic principles always remain the same.

By now, you ought to be acquainted with at
least one of the tools provided by Oracle, IBM, or Microsoft to access their
respective databases. Each RDBMS package is a resource intensive piece of
software, and in general it is recommended not to install all of them onto the
same machine. Once you've installed your RDBMS of choice, you could run
multiple instances of Oracle's SQL Plus to access Oracle
9i RDBMS, Microsoft's OSQL (if you've selected MS SQL
Server 2000), or IBM's Command Line Processor for IBM DB2 UDB from the same
computer where your RDBMS is installed, and each instance will open its own
session, which would be isolated from every other session established to the
RDBMS server.

The SQL standard specifies a number of
parameters that could be set in a session (listed in
Table
7-1). None of these are implemented directly by the RDBMS, though some
elements made it into proprietary syntax, ditching the letter, preserving the
spirit.

Table 7-1: SQL Standard SET Statements

SQL
Statement

Description

SETCONNECTION

If more than one connection is
opened by a user to an RDBMS, this statement allows that user to switch between
the connections.

SETCATALOG

This statement defines the
default catalog for the session.

SETCONSTRAINTSMODE

Changes the constraints mode
between
DEFERRED, and
IMMEDIATE.

SETDESCRIPTOR

Stores values in the descriptor
area.

SETNAMES

Defines the default character set
for the SQL statements.

SETSCHEMA

Sets the schema to be used as a
default qualifier for all unqualified objects.

SETSESSIONAUTHORIZATION

Sets the authorization ID for
the session, no other IDs can be used.

SETTIMEZONE

Sets the default time zone for
the session.

In Oracle, a
user must have a system privilege
CREATESESSION in order to establish a database
connection. Initially, all the default parameter values for the session are
loaded from a special Oracle configuration file; the file could be modified
only by a database administrator, or someone who has the necessary privileges.
Once the connection is established (a session is created), a user can alter the
session according to his/her preferences and job requirements.

Cross-References

See
Chapter
12 for more information on privileges.

The session parameters in Oracle can be
modified using an
ALTERSESSION statement. The syntax of the
statement is relatively complicated and usually belongs to advanced database
topics. Even the parameters that can be changed with this statement are
somewhat irrelevant to SQL programming, like
DB_BLOCK_CHECKING,
HASH_JOIN_ENABLED, or
MAX_DUMP_FILE_SIZE. These statements deal
more with RDBMS administration and optimization, and belong to an advanced
Oracle book.

The format in which the output of the
TO_CHAR function appears is determined by
the initialization parameter
NLS_DATE_FORMAT, which is the default for
each new session. After the session is altered, the format of the displayed
date is changed:

Oracle's command-line utility SQL*Plus has
its own parameters that can be set within the session initiated through it.
These parameters affect the way data is fetched, manipulated, and displayed —
in the SQL*Plus utility. The following is a short list of some options that
could be
SET in SQL *plus.

SET
Option

Description

AUTO[COMMIT]{ON|OFF|IMMEDIATE}

This command sets up default
behavior for the pending data changes in the database. Setting it to
OFF (default value) requires
users to commit changes manually, issuing the
COMMIT
statement.

[LIN]ESIZEn

This option sets up the
maximum number of the characters that SQL*Plus can display on one line; range
is from 1 to a system-dependent maximum.

NULL<text>

This option sets up the text
you'd like to be displayed when data containing
NULL is
returned.

[PAGES]IZEn

Sets up the maximum number of
lines per page for displaying the results of a query.

[WRA]P(WRA){N|OFF}

This command determines how
the output data is displayed:
ON enables a returned row that
is longer than the current setting to be wrapped to the next line,
OFF truncates it to the size of
the line.

All these options (and many more, not
listed here) could be
SET within the SQL*Plus environment using
the standard syntax:

SET <option>
[<value>]

The options set up during the session are
usually lost once the session is ended. You can save these custom options into
a script file, that later could be conveniently loaded into SQL*Plus to restore
your custom session environment.

To view all parameters set for any given
session, the
SHOWALL command is used:

SQL> show
all appinfo is ON and set to "SQL*Plus" arraysize 15 autocommit OFF autoprint
OFF autorecovery OFF autotrace OFF blockterminator "." (hex 2e) btitle OFF and
is the first few characters of the next SELECT statement cmdsep OFF colsep " "
compatibility version NATIVE concat "." (hex 2e) copycommit 0 . . . underline
"-" (hex 2d) USER is "ACME" verify ON wrap : lines will be
wrapped

There are many more parameters than are
shown here. Refer to the Oracle documentation for more information.

The changes made with an
ALTERSESSION statement are valid for the
duration of the session. To make changes permanent, the
ALTERSYSTEM statement should be used.

Cross-References

You may also control privileges afforded
to the session by issuing a
SETROLE statement. Refer to
Chapter
12 for more information.

IBM DB2 UDB provides surprisingly little
control for the user over the session environment. It lists the keyword
SESSION as reserved for future use,
alongside with
SESSION_USER.

The closest it comes to providing session
control is with the
SETPASSTHRU statement, which opens and closes
a session for submitting SQL data directly to the database. Also, a global
temporary table created during the session may be qualified with the
SESSION component as a schema. (It is used
to prevent ambiguity in accessing the table, when the temporary table name is
the same as some persistent table, and in some other just as obscure
cases.)

Microsoft SQL Server 2000 has a number of
statements that you can specify to alter the current session (some of them are
shown in
Table 7-2
and
Table
7-3). These statements are not part of SQL standard, being rather part
of the Transact-SQL dialect. They can be grouped in several categories:
statements that affect date and time settings, query execution statements,
statistics statements, locking and transaction statements, SQL-92 settings
statements, and — the all-time favorite — miscellaneous settings.

Table 7-2: Microsoft SQL Server 2000 SQL-92
Settings

SET
Statement

Description

SETANSI_DEFAULTS{ON|OFF}

Specifies that all the defaults
used for the duration of the session should be these of ANSI defaults. This
option is provided for compatibility with SQL Server 6.5 or
later

SETANSI_NULL_DFLT_OFF{ON|OFF}

Specifies whether columns could
contain
NULL value by default. If set to
ON, the new columns created would
allow
NULL values (unless
NOTNULL is specified); otherwise it
would raise an error. It has no effect on the columns explicitly set for
NULL. It is used to override
default nullability of new columns when the
ANSI null default option for the
database is
TRUE.

SETANSI_NULL_DFLT_ON{ON|OFF}

Essentially, the same as the
statement above, with one exception: it is used to override default nullability
of new columns when the ANSI null default option for the database is
FALSE.

Specifies how the values that are
shorter than the column size for
CHAR,
VARCHAR,
BINARY, and
VARBINARY data types are
displayed.

SETANSI_WARNINGS{ON|OFF}

Specifies whether a warning
should be issued when any of the following conditions occur: presence of
NULL values in the columns
evaluated in the aggregate functions (like
SUM,
AVG,COUNT, etc.); divide-by-zero and arithmetic overflow
errors generate an error message and the statement rolls back when this option
is set to
ON; specifying
OFF would cause a
NULL value to be returned in the
case.

Table 7-3: Microsoft SQL Server 2000 SET Statements

SET
Statement

Description

SETDATEFORMAT{<format>|@<formatID>}

Specifies the order of the date
parts for
DATETIME and
SMALLDATETIME
input.

SETCONCAT_NULL_YIELDS_NULL {ON|OFF}

Specifies what would be the
result of concatenation of the column values (or expressions) should any or
both of them contain
NULL.

SETLANGUAGE{ <language>|@<languageID>}

Specifies the default language
for the session. This setting affects the datetime format, and system messages
returned by SQL Server.

SETNOCOUNT{ON|OFF}

SQL Server usually returns a
message indicating how many rows were affected by any given statement. Issuing
this command would stop this message.

SETNUMERIC_ROUNDABORT{ON|OFF}

Specifies the severity of an
error that results in loss of precision; if set to
OFF the rounding generates no
error; when it is set to
ON, then an error will be generated
and no results returned. Depending on some other settings, a
NULL might be
returned.

SETROWCOUNT <integer>

If this statement is used,
Microsoft SQL Server stops processing a query after the required number of rows
(specified in the
SET statement) is
returned.

While detailed discussion of these settings
and their implications are well beyond the scope of our SQL topic,
nevertheless, we are going to discuss some of the most important statements and
how they may affect your SQL statements executed against Microsoft SQL Server
2000.

Here is an example of how setting
ANSI_NULLS affects the output in the
current session. The SQL-92 standard mandates that the comparison operations
involving
NULL always evaluate to
FALSE. The following statement is supposed
to bring all the records from the
PHONE table of the ACME database when the
PHONE_SALESMANID_FN filed is not
NULL.

The query returns zero records in spite of
the fact that there are supposed to be 12 records satisfying this criterion.
Setting the
ANSI_NULLSOFF changes the situation (valid in
Microsoft SQL Server only; neither Oracle nor IBM DB2 UDB supports this
feature):

This situation could be completely avoided if the
ISNULL syntax is used. The query

SELECT phone_phonenum_s FROM
phone WHERE phone_salesmanid_fn IS NULL

would return correct results in all three RDBMS. Since
NULL is not a specific value, it has to
be treated differently. Neither Oracle 9i nor IBM DB2 UDB
have such a setting as ANSI_NULLS. Refer to
Chapter
3 for more information about
NULL.

It is possible to specify multiple options
with
ON or
OFF settings, using one
SET statement. For example, the following
statement will set two options at the same time.

1> SET NOCOUNT, ANSI_DEFAULTS
ON 2> GO

To check the options set for your session,
use the following statement. It returns all the active options that have been
set for this particular session within which you execute this statement

The DataBase Console Command (DBCC) package
is a toolbox of all the DBA utilities, with some options accessible to a user.
There are over 60 DBCC commands that handle various aspects of SQL Server
configuration, administration, status checking, and so on.

Note

If the
SET statement is set in the stored
procedure, it is valid within the session for the duration of the stored
procedure execution, and reverts to its previous value once the execution
stops. When using Dynamic SQL (see
Chapter
15), the
SET statement affects only the batch it
is specified in; subsequent statements will not be affected by this
setting.

Some other
SET statements pertaining to transactions
and locks will be discussed in the corresponding paragraphs of this
chapter.

When a client terminates a session — either
voluntarily or abnormally — all values set for various session parameters
disappear. In addition, for all pending transactions, an implicit commit will
be issued in the case of voluntary termination or rolled back when the session
has terminated abnormally. The session can be killed or disconnected by a DBA;
syntax for the statements vary among RDBMS.

Orphaned Sessions

Orphaned sessions occur when a client
application terminates abruptly without the ability to terminate its open
session to RDBMS server. Usually, it is the responsibility of the operating
system to detect that the client exited, and notify the server. (In some
implementations, the server would query the client whether it is still present
after some period of inactivity.) Certain situations, however, might prevent a
proper client exit (e.g., sudden network failure). If the session was active
(i.e., RDBMS was processing some command at the time), it will detect the
absence of the client automatically and terminate the session. However, if the
session was inactive, waiting for command from the client, such a session
remains valid for the server.

Such sessions consume system resources
and should be cleaned up. Usually it is done automatically after a certain
interval configured for the server; or these sessions may be resolved manually
by the DBA.