SQL contains commands to control the environment of SQL sessions, and to manage transactions within them.
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.
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.
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.
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.
This command is unsupported at this time. SQLstream processes every record with a best effort guarantee.
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.
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.
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.
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.
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.