SQL Bible [Electronic resources] نسخه متنی

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.

In Oracle, a
user must have a system privilege
CREATE
SESSION 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.

The session parameters in Oracle can be
modified using an
ALTER
SESSION 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.

Here we are going to demonstrate the concept
of altering the session to suit your particular needs using one of the
parameters
NLS_DATE_FORMAT.

You can use this parameter to alter date
format returned by your SQL query, as it specifies the default date format
returned by the
TO_CHAR and
TO_DATE functions.

SQL> SELECT TO_CHAR(SYSDATE)
nls_date FROM dual; NLS_DATE ----------------- 06 - 10 - 03

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:

SQL> ALTER SESSION SET
NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Session altered. SQL > SELECT
TO_CHAR(SYSDATE) nls_date FROM dual; NLS_DATE
---------------------------- 06-OCT-2003 10:33:44

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.

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
SHOW
ALL 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
ALTER
SESSION statement are valid for the
duration of the session. To make changes permanent, the
ALTER
SYSTEM statement should be used.

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
SET
PASSTHRU 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.

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.

1> SET ANSI_NULLS ON 2> GO
1> SELECT phone_phonenum_s 2> FROM phone 3> WHERE phone_salesmanid_fn
<> NULL 4> GO PHONE_PHONENUM_S -------------------- (0 row(s)
affected)

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

1> SET ANSI_NULLS OFF 2> GO
1> SELECT phone_phonenum_s 2> FROM phone 3> WHERE phone_salesmanid_fn
<> NULL 4> GO PHONE_PHONENUM_S -------------------- (305) 555-8502
(626) 555-4435 (717) 555-5479 (718) 555-7879 (718) 555-5091 (814) 555-0324
(305) 555-8501 (626) 555-4434 (717) 555-5478 (718) 555-7878 (718) 555-5091
(814) 555-0323 (12 row(s) affected)

SELECT phone_phonenum_s FROM
phone WHERE phone_salesmanid_fn IS 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

1> DBCC
USEROPTIONS Set Option Value -------------------------- ---------------------
textsize 64512 language us_english dateformat mdy datefirst 7 quoted_identifier
SET arithabort SET ansi_null_dflt_on SET ansi_defaults SET
ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET
(12 row(s) affected) DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

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.

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 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.