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.

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

Setting SQL*Plus Session Parameters

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]ESIZE n	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]IZE n	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 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

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.