Session Management

SQL contains commands to control the environment of SQL sessions, and to manage transactions within them.

Commands for Session Management

The following commands can be used from within a SQL session to control the session environment. The commands are available in both interactive sessions (for instance, a session created via SQLLine and programmatic sessions created via JDBC.

For more details on using SQLline, see the SQLline guide.

SET CATALOG

Sets the name of the catalog from which schemas, tables, and other database objects will be resolved. The default catalog is called “LOCALDB”. You can override the catalog by using fully-qualified object names:

SELECT * FROM LOCALDB.SALES.Emps.

SET SCHEMA

Sets the name of the default schema from which tables, streams and other database objects will be resolved. For more details, see the topic SET SCHEMA * in the s-Server Streaming SQL Reference Guide.

SET PATH

Sets the path where the system looks to find plugins such as user-defined functions and foreign servers. For more details, see the topic SET PATH * in the s-Server Streaming SQL Reference Guide.

Transactional commands

SQLstream has limited support for transations. The SQL standard provides the following commands, but they may have no effect, or have effect only on data stored in tables.

The commands are available in both interactive and programmatic sessions.

CHECKPOINT

This command is unsupported at this time. SQLstream processes every record with a best effort guarantee.

COMMIT

This affects only DML operations (INSERT, UPDATE, DELETE) upon tables. An INSERT to a stream is automatically committed when it is sent to a SQLstream s-Server. For performance reasons, rows inserted into a stream at not necessarily sent to SQLstream s-Server from the client immediately. Commit behaves like a flush in that respect. Autocommit has the same effect but on every tuple. So to achieve the best performance, autocommit should be disabled and commit shouldn’t be called.

For tuples inserted into tables, commit behaves in the same fashion as traditional databases. It causes data to be flushed to disk and ensures that it won’t be lost upon a failure to another operation.

ROLLBACK

This affects only DML syntax (INSERT, UPDATE, DELETE) upon tables. An INSERT to a stream cannot be rolled back once it has been sent to a SQLstream s-Server. This command has the standard SQL behavior regarding commits to tables.

SAVEPOINT

This command is unsupported at this time.

ROLLBACK TO SAVEPOINT

This command is unsupported at this time.

Administrative commands

There are commands available to an administrator to kill sessions and statements of other users. For more detail, see the subtopics KILL_SESSION, KILL_STATEMENT, KILL_STATEMENT_MATCH in Managing and Monitoring.

Client commands for session management

Most interactive JDBC clients have extensive commands for session management. These commands are often confused with SQL session management commands, especially if they have similar syntax. For example, in SQLLine you can say

!set timeout 10

Commands of this nature are not sent to the server, and are specific to the client. For details of these commands, consult the documentation for your JDBC client.

For more details on using SQLline, see the SQLline guide.

JDBC and Connection Pools

What SQLstream calls a session, JDBC calls a connection. When you create a connection using the SQLstream JDBC driver, a session is created on the SQLstream s-Server. When you close that connection, the session is closed.

You may choose to use a connection pool to manage sessions. A connection pool reduces the overhead of creating connections by creating connections in advance, storing unused connections in a pool, and giving clients a connection from the pool when they ask for a new connection.

Connection pools are popular in transaction processing environments and application servers, where there are many threads using connections for short periods of time, which can make the overhead of creating a connection more significant. In a SQLstream system, statements (and therefore connections) tend to have long lifetimes. The overhead of creating connections is lower, and therefore connection pools are not as important.