Configuring SQLstream s-Server

This page covers the following subtopics:

System Parameter Configuration

You can customize the operation of the SQLstream s-Server in a number of ways, through SQLstream s-Studio (the user interface) and other components such as plugins and drivers. Note that a Plugin JAR file added to the standard $SQLSTREAM_HOME/plugin directory is not automatically added to the SQLstream s-Server CLASSPATH unless a symlink to that JAR is put into the \$SQLSTREAM_HOME/plugin/autocp directory (see the Readme file in the \$SQLSTREAM_HOME/plugin/autocp directory).

This topic examines each of these mechanisms and the standard parameters they control.

You can set and read parameters in several ways. It is important to distinguish how and where you do so because, for example, you need to know which parameters override others. In general, changes to properties should be put in $SQLSTREAM_HOME/aspen.custom.properties: see Overriding Java Parameters.

In broad terms, the parameters you can configure are these:

Data and Wallclock time

SQLstream strongly recommends that administrators use NTP (Network Time Protocol) on all computers to ensure that system clocks are closely synchronized. However, the system will still operate correctly if there is an offset between clocks.

Time Zone

By default, s-Server and s-Studio are set to operate in UTC/GMT. SQLstream strongly recommends leaving this setting intact unless you are absolutely sure that all SQLstream components (including all agents) are running in the same time zone. Time zone is set by a JVM parameter called user.timezone defined in the defintAspenRuntime.sh script.

Configuring Ports for Guavus SQLstream

The following diagram summarizes the ports that operate between Guavus SQLstream components:

Guavus SQLstream uses the following ports:

s-Server

s-Server, the central component of the Guavus SQLstream system, listens on port 5570 (SDP2).

In order for applications (including s-Studio and WebAgent) to access s-Server directly from a remote computer, the firewall on the host computer for s-Server must allow incoming traffic on port 5570. The firewall on the computer hosting the application must similarly allow outbound traffic on port 5570.

WebAgent

WebAgent, the HTTP server that exchanges data with s-Server, listens on port 5580. The firewall on its host computer must allow outbound traffic on port 5570.

In order for applications (including StreamLab and s-Dashboard) to access WebAgent directly, the firewall on the host computer for WebAgent must allow incoming traffic on port 5580. The firewall on the computer hosting the application must similarly allow outbound traffic on port 5580. Since WebAgent handles connections to s-Server, these applications do not need to be open to s-Server (and likely should not be).

StreamLab

StreamLab listens on port 5585 (ROSE) and 5590. It uses WebAgent to connect to s-Server. In order to communicate with WebAgent, the firewall on its host computer must allow outbound traffic on port 5580.

In order for users to access StreamLab through a browser, the firewall on the host computer for StreamLab must allow outgoing traffic on ports 5585 and 5590.

s-Dashboard

s-Dashboard listens on port 5595. It uses WebAgent to connect to s-Server. In order to communicate with WebAgent, the firewall on its host computer must allow outbound traffic on port 5580.

In order for users to access s-Dashboard through a browser, the firewall on the host computer for s-Dashboard must allow outgoing traffic on port 5595.

Repository Parameters

The SQLstream repository contains all the definitions of objects (schemas, streams, views, etc.) defined in your system. In addition to these objects, the repository holds various parameters regarding the internal operation of the repository itself, called system parameters.

You set repository parameters using SQL:

ALTER SYSTEM SET <name> = <value>;
ALTER SESSION SET <name> = <value>;

Note that parameter names are mixed-case identifiers, so you will need to enclose them in double quotes. For example, to set the databaseMaxSize parameter to 100000, you would type ALTER SYSTEM SET “databaseMaxSize” = 100000;

The SYSTEM and SESSION designators determine the scope of the parameter being set. There are three levels of scope for a repository parameter: SYSTEM, DEFAULT, and SESSION, as explained in the following table.

Scope level Meaning
SYSTEM Value applies to the whole system (all nodes, all sessions).
DEFAULT Value applies to all SESSIONs that do not specifically override the value.
SESSION Value applies only to the current session.

SYSTEM and DEFAULT scope persist across restarts of the server. SESSION scope applies only to a running session. For details, see the topics ALTER SESSION and ALTER SYSTEM in the SQLstream SQL Reference Guide.

System Parameters

System parameters determine the extent and behavior of the SQLstream s-Server. You can set the values for the entire system or for a particular session, using the ALTER SYSTEM and ALTER SESSION commands. Different parameters may take effect at different times, as indicated by the “Update Policy” entries in the following table.

Most parameter changes take place either immediately or the next time the system is started. A few are immutable, but are included in the table for reference; treat them as read-only parameters. Note, however, that configuring SQLstream s-Server to accept JDBC driver connections from other hosts, even if the server is behind a NAT router, requires additional preparation as described in the subtopic Configuring JDBC Driver Connections from Other Hosts below.

Parameter Type and Usage Default Value Update Policy
cachePagesInit 32-bit integer (number of pages) Sets number of memory buffers to allocate from the OS for Fennel’s data cache (also used as a pool for volatile scratch memory allocated by ExecStreams). Increasing it after startup causes new free buffers to be allocated; decreasing it causes buffers to be released (flushing them first if they contain dirty data). Note that this memory is NOT part of the JVM heap, although it shares the same virtual memory address space. 5000 Immediate
cachePageSize 32-bit integer (number of bytes; must be a power of 2). Determines contiguous size of memory buffers allocated by cache. Also determines contiguous size of pages stored on disk; these are the elemental allocation units for all disk-based data structures such as indexes. 65536 Immutable after database creation.
cachePagesMax 32-bit integer (number of pages). Determines upper bound for number of pages cache can allocate. On startup, actual upper bound is set to the larger of this parameter and cachePagesInit. Subsequently, changes to cachePagesInit are limited by upper bound calculated at startup. 70000 Takes effect on startup.
cacheReservePercentage 32-bit integer (percentage between 1 and 99, inclusive). Usage: The percentage of the Fennel data cache that should be set aside for global resource usage. The remainder will be available to allocate to individual statements. 5 Immediate.
calcVirtualMachine String, one of CALCVM_FENNEL, CALCVM_JAVA, or CALCVM_AUTO. Usage: Controls which calculator virtual machine is used for evaluating SQL row expressions. CALCVM_FENNEL uses a virtual machine implemented inside Fennel. CALCVM_JAVA generates and compiles Java code on the fly. CALCVM_AUTO allows the optimizer to choose a blend of the two calculators based on cost or other factors. Changing this parameter causes all entries to be discarded from the code cache. CALCVM_AUTO Immediate.
checkpointInterval 32-bit integer (seconds, 0 to disable timer-based checkpoints). Sets the interval between automatic timed checkpoints. Regardless of this setting, checkpoints may occur at other times as well (e.g. when the system detects that free log space is low, or when an administrator issues the explicit CHECKPOINT command). 300 Takes effect on startup.
codeCacheMaxBytes 64-bit integer (number of bytes, 0 for disabled, -1 for unlimited). Usage: Sets the size of the Farrago code cache, which is used for optimizer plan caching and SQL/MED data wrapper pooling. Setting to MAX causes the cache to grow without bound except for the JVM heap size. Setting to MIN disables the code cache, discarding any currently cached entries. Setting to a finite value causes LRU victimization according to rough estimates on per-object memory usage. 2000000 Immediate.
databaseIncrementSize 32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the db.dat file when all existing pages have been allocated. 1000 Takes effect on startup.
databaseInitSize 32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of Fennel’s db.dat file. 1000 Only used at time of database creation; immutable after that.
databaseMaxSize 32-bit integer (number of pages, 0 for unlimited). Determines maximum size to which db.dat file can grow. Further allocations will fail even if space remains in the file system. 0 Takes effect on startup.
databaseShadowLogIncrementSize 32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the shadowlog.dat file when all existing pages have been allocated. 1000 Takes effect on startup.
databaseShadowLogInitSize 32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of the shadowlog.dat file. This file is recreated every time Fennel is loaded, and is used as part of physical recovery. 2000 Takes effect on startup.
databaseTxnLogIncrementSize 32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the txnlog.dat file when all existing pages have been allocated. 1000 Takes effect on startup.
databaseTxnLogInitSize 32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of the txnlog.dat file. This file is recreated every time Fennel is loaded, and is used as part of logical recovery. 2000 Takes effect on startup.
deviceSchedulerType String (name of scheduler implementation). Controls the scheduler used for disk I/O. threadPool means to fake asynchronous I/O via a thread pool, and use buffered file access. On Linux, aioLinux means to use libaio for asynchronous I/O, with O_DIRECT for unbuffered file access. The default is to to use libaio if available, otherwise to fallback to threadPool. On Windows, ioCompletionPort (the default) means to use asynchronous I/O via completion ports and unbuffered file access. In all cases, if an unrecognized or unsupported value is set, the system silently reverts to default behavior to avoid causing startup failures. null (let system decide)
expectedConcurrentStatements 32-bit integer (number of statements). Indicates to the resource governor what is expected to be the maximum number of concurrent statements executing at any given time. It is used by the resource governor to determine the maximum resource allocation for each statement. 4 Immediate.
fennelDisabled boolean. Determines whether Fennel native code components are loaded when the system starts up. false Takes effect on startup.
groupCommitInterval 32-bit integer (milliseconds). Determines the amount of time to wait during commit in the hope that the commit’s disk write can be combined with that of other concurrent transactions. 30 Takes effect on startup.
jniHandleFile String (file-system path). See - Fennel Jni Handle Tracing {empty string} Takes effect on startup.
resourceDir String (file-system path). Sets the location of FennelResource.properties (or localized variant). \$SQLSTREAM_HOME/s-Server/catalog/fennel
sdpListenerPort 32-bit integer, -1 for personality-based defaults. Sets the port number on which to listen for SDP connections (This is the port number specified in client JDBC URL’s). However, for connections from other hosts, see also the Network Configuration subsection below entitled - Configuring JDBC Driver Connections from Other Hosts. -1 (implies 5570 for SQLstream personality) Takes effect on startup.
tempIncrementSize 32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the temp.dat file when all existing pages have been allocated. 1000 Takes effect on startup.
tempInitSize 32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of the temp.dat file. This file is recreated every time Fennel is loaded. 1000 Takes effect on startup.

Reading system parameters

To read the values of the system parameters, you can execute the following two queries, either using sqllineClient* or SQLstream s-Studio:

SELECT * FROM SYS_FEM."Config"."FennelConfig";
SELECT * FROM SYS_FEM."Config"."FarragoConfig";

Each system parameter has its own column, named for the parameter. The value in the column is the current value of the parameter.

Java parameters

Most of the SQLstream system is written in Java, so some of its behavior can be controlled using Java properties. Some of those parameters are passed on the command-line to the Java virtual machine by the startup script, s-Server and the auxiliary script defineAspenRuntime.sh. You can also set parameter values for the server by using the file $SQLSTREAM_HOME/aspen.properties. Java properties are only used to control bootstrap behavior (startup activities before the server is up and running) such as setting initial tracing and network configuration.

The currently-implemented Java parameters all deal with Network Configuration. For more details, see the topic Network Configuration in this guide.

Overriding Java parameters

You should not edit the file aspen.properties itself. Rather, if you want to override or add a value, put that parameter into a file called aspen.custom.properties in the $SQLSTREAM_HOME/s-Server directory. This enables you to make your own set of changes without having to modify the standard parameters.

When the SQLstream s-Server starts up, it reads its Java parameters in the following order, with each successive source potentially overriding the preceding ones:

  • Parameters from aspen.properties (read only)
  • Parameters from aspen.config (read only)
  • Parameters from aspen.custom.properties
  • Java system parameters with names beginning with “aspen.”

As you can see, your entries in aspen.custom.properties can override the standard settings distributed in aspen.properties or aspen.config, so there is no need to modify those files.

If you need to change the parameters passed to the Java runtime, such as the class path or memory sizes, you will need to make those changes to the script $SQLSTREAM_HOME/bin/defineAspenRuntime.sh.

Parameter substitution

Java parameters can include other variables in their definitions. The syntax for these references is much like that used for variables in a shell script, a dollar sign followed by the name of the referenced variable inside curly braces, such as ${aspen.home.dir}. Undefined parameters are treated as empty strings.

Thus, you can define file paths relative to the SQLstream installation directory, for example: ​ my.file.name=${aspen.home.dir}/temp/myfile.name ​ The same syntax can also be used within SQL/MED and adapter parameters.

Configuring JDBC Driver Connections from Other Hosts

The instructions in this section enable you to configure the SQLstream s-Server to accept JDBC driver connections from other hosts, even if the server is behind a NAT router.

SDP Requirements

The SQLstream JDBC driver connects to SQLstream s-Server using SDP. SDP requires that the hostnames match at both ends of a remote connection. That means that the server must have - An IP address reachable from client systems - A host name that resolves to that address for the client (either through DNS or an explicit host name mapping such as an entry in the client’s /etc/hosts* file) - Configuration files that use the resolvable host name or the explicit IP address

Here are the configuration requirements:

/etc/hosts

Many Linux systems will, by default, assign a system’s host name to the loopback interface (IP address 127.0.0.1). For a server installation that other systems will connect to, you need to ensure that the host name is explicitly assigned to the external IP address:

​``` 127.0.0.1 localhost a.b.c.d hostName.domain hostName


#### $SQLSTREAM\_HOME/aspen.properties
The aspen.properties file needs to specify the host name of the server in a way that can be resolved by client systems or else use the IP address:
​```
aspen.sdp.host=<hostName or a.b.c.d>

JDBC URI

The client system connects to the server via a URI that uses the host name (or IP address) just as specified in aspen.properties: ​

jdbc:sqlstream:sdp://<hostName>:<port>, autoCommit=false
jdbc:sqlstream:sdp://<a.b.c.d>:<port>, autoCommit=false

The port specified in aspen.controlnode.url must match aspen.sdp.port. The hostName or IP address specified in aspen.controlnode.sdp.host and aspen.controlnode.url must be resolveable/visible from both the client and the server.

Repository Parameters

The SQLstream repository contains all the definitions of objects (schemas, streams, views, etc.) defined in your system. In addition to these objects, the repository holds various parameters regarding the internal operation of the repository itself, called system parameters. You set repository parameters using SQL:

ALTER SYSTEM SET <name> = <value>;
ALTER SESSION SET <name> = <value>;

Note that parameter names are mixed-case identifiers, so you will need to enclose them in double quotes. For example, to set the databaseMaxSize parameter to 100000, you would type

ALTER SYSTEM SET "databaseMaxSize" = 100000;

The SYSTEM and SESSION designators determine the scope of the parameter being set. There are three levels of scope for a repository parameter: SYSTEM, DEFAULT, and SESSION, as explained in the following table. | Scope level | Meaning | | SYSTEM | Value applies to the whole system (all nodes, all sessions).| | SESSION | Value applies only to the current session. |

SYSTEM and DEFAULT scope persist across restarts of the server. SESSION scope applies only to a running session. For details, see ALTER SESSION * and ALTER SYSTEM * in the SQLstream SQL Reference Guide .

Adapter and SQL/MED parameters

SQLstream adapters use parameters specified as OPTIONs in the DDL (as do other SQL/MED methods to access external data) for their FOREIGN DATA WRAPPERs, SERVERs and any FOREIGN STREAMs based on them.

To find the configuration options for a particular adapter, see the documentation for that adapter. SQL/MED and related adapter matters are covered in the topic SQLstream Software Development Kit (SDK) in the s-Server Integration Guide. Note that, like Java parameters, adapter and other SQL/MED parameters can perform variable expansion and substitution.

Note that, like Java parameters, adapter and other SQL/MED parameters can perform variable expansion and substitution. The variable reference takes the form of a dollar sign followed by the name of the variable in curly braces. Such a property is evaluated at the time the DDL is executed (define time).

For example, the following foreign server definition uses the variable aspen.home.dir to set the root directory for its log files under the SQLstream installation directory:

CREATE OR REPLACE SERVER "WebAppServer"
   FOREIGN DATA WRAPPER ECDA
   OPTIONS (ROOT '${aspen.home.dir}/temp')
   DESCRIPTION 'Server for webapp login stream';

Variables whose values are undefined are empty. For example, if aspen.home.dir were undefined, the ROOT parameter in the above example would evaluate as ‘/temp’.

Configuring JDBC Driver Connections from Other Hosts

The instructions in this section enable you to configure the SQLstream s-Server to accept JDBC driver connections from other hosts, even if the server is behind a NAT router.

SDP Requirements

The SQLstream JDBC driver connects to SQLstream s-Server using SDP. SDP requires that the hostnames match at both ends of a remote connection. That means that the server must have

  • An IP address reachable from client systems
  • A host name that resolves to that address for the client (either through DNS or an explicit host name mapping such as an entry in the client’s /etc/hosts file)
  • Configuration files that use the resolvable host name or the explicit IP address

Here are the configuration requirements:

/etc/hosts

Many Linux systems will, by default, assign a system’s host name to the loopback interface (IP address 127.0.0.1). For a server installation that other systems will connect to, you need to ensure that the host name is explicitly assigned to the external IP address:

127.0.0.1 localhost
a.b.c.d hostName.domain hostName

\$SQLSTREAM_HOME/aspen.properties

The aspen.properties file needs to specify the host name of the server in a way that can be resolved by client systems or else use the IP address:

aspen.sdp.host=<hostName or a.b.c.d>

JDBC URI

The client system connects to the server via a URI that uses the host name (or IP address) just as specified in aspen.properties:

jdbc:sqlstream:sdp://<hostName>:<port>, autoCommit=false
jdbc:sqlstream:sdp://<a.b.c.d>:<port>, autoCommit=false

The port specified in aspen.controlnode.url must match aspen.sdp.port. The hostName or IP address specified in aspen.controlnode.sdp.host and aspen.controlnode.url must be resolvable/visible from both the client and the server.

System Parameters

System parameters determine the extent and behavior of the SQLstream s-Server. You can set the values for the entire system or for a particular session, using the ALTER SYSTEM * and ALTER SESSION * commands. Different parameters may take effect at different times, as indicated by the “Update Policy” entries in the following table.

Most parameter changes take place either immediately or the next time the system is started. A few are immutable, but are included in the table for reference; treat them as read-only parameters. Note, however, that configuring SQLstream s-Server to accept JDBC driver connections from other hosts, even if the server is behind a NAT router, requires additional preparation as described in Configuring JDBC Driver Connections from Other Hosts.

Parameter Type and Usage Default Value Update Policy
connectionTimeoutMillis Long integer. Sets the timeout value for JDBC connections. -1 means never time out 86400000 milliseconds (24 hours) Takes effect on startup
cachePagesInit 32-bit integer (number of pages)Sets number of memory buffers to allocate from the operating system for internal data cache (also used as a pool for volatile scratch memory allocated by ExecStreams). Increasing it after startup causes new free buffers to be allocated; decreasing it causes buffers to be released (flushing them first if they contain dirty data). Note: This memory is not part of the JVM heap, although it shares the same virtual memory address space. 5000 Immediate.
cachePageSize 32-bit integer (number of bytes; must be a power of 2). Determines contiguous size of memory buffers allocated by system cache. Also determines size of contiguous pages stored on disk. These are the elemental allocation units for all disk-based data structures such as indexes. 65536 Immutable after database creation.
cachePagesMax 32-bit integer (number of pages). Determines upper bound for number of pages cache can allocate. On startup, actual upper bound is set to the larger of this parameter and cachePagesInit. Subsequently, changes to cachePagesInit are limited by upper bound calculated at startup. 70000 Takes effect on startup.
cacheReservePercentage 32-bit integer (percentage between 1 and 99, inclusive). Usage: The percentage of the data cache that should be set aside for global resource usage. The remainder will be available to allocate to individual statements. 5 Immediate.
calcVirtualMachine Usage: Only used for testing. CALCVM_FENNEL: use internal VM for expression evaluation. CALCVM_JAVA: use java VM for expression evaluation. CALCVM_AUTO: let optimizer decide. CALCVM_AUTO Immediate.
checkpointInterval 32-bit integer (in seconds, 0 to disable timer-based checkpoints). Sets the interval between automatic timed checkpoints. Regardless of this setting, checkpoints may occur at other times as well. 300 Takes effect on startup.
codeCacheMaxBytes 64-bit integer (number of bytes, 0 for disabled, -1 for unlimited). Usage: Sets the size of the Farrago code cache, which is used for optimizer plan caching and SQL/MED data wrapper pooling. Setting to MAX causes the cache to grow without bound except for the JVM heap size. Setting to MIN disables the code cache, discarding any currently cached entries. Setting to a finite value causes LRU victimization according to rough estimates on per-object memory usage. 2000000 Immediate.
databaseIncrementSize 32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the db.dat file when all existing pages have been allocated. 1000 Takes effect on startup.
databaseInitSize 32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of db.dat file. 1000 Only used at time of database creation; immutable after that.
databaseMaxSize 32-bit integer (number of pages, 0 for unlimited). Determines maximum size to which db.dat file can grow. Further allocations will fail even if space remains in the file system. 0 Takes effect on startup.
databaseShadowLogIncrementSize 32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the shadowlog.dat file when all existing pages have been allocated. 1000 Takes effect on startup.
databaseShadowLogInitSize 32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of the shadowlog.dat file. This file is recreated every time s-Server is started, and is used as part of physical recovery. 2000 Takes effect on startup.
databaseTxnLogIncrementSize 32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the txnlog.dat file when all existing pages have been allocated. 1000 Takes effect on startup.
databaseTxnLogInitSize 32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of the txnlog.dat file. This file is recreated every time Fennel is loaded, and is used as part of logical recovery. 2000 Takes effect on startup.
deviceSchedulerType String (name of scheduler implementation). Controls the scheduler used for disk I/O. threadPool means to fake asynchronous I/O via a thread pool, and use buffered file access. On Linux, aioLinux means to use libaio for asynchronous I/O, with O_DIRECT for unbuffered file access. The default is to to use libaio if available, otherwise to fallback to threadPool. On Windows, ioCompletionPort (the default) means to use asynchronous I/O via completion ports and unbuffered file access. In all cases, if an unrecognized or unsupported value is set, the system silently reverts to default behavior to avoid causing startup failures. null (let system decide) Takes effect on startup.
expectedConcurrentStatements 32-bit integer (number of statements). Indicates to the resource governor what is expected to be the maximum number of concurrent statements executing at any given time. It is used by the resource governor to determine the maximum resource allocation for each statement. 4 Immediate.
fennelDisabled boolean. Determines whether Fennel native code components are loaded when the system starts up.Note: Do not change this setting. It will cause s-Server to stop functioning. false Takes effect on startup.
groupCommitInterval 32-bit integer (milliseconds). Determines the amount of time to wait during commit in the hope that the commit’s disk write can be combined with that of other concurrent transactions. 30 Takes effect on startup.
jniHandleTraceFile String (file-system path). See Fennel Jni Handle Tracing {empty string} Takes effect on startup.
resourceDir String (file-system path). Sets the location of FennelResource.properties (or localized variant). $SQLSTREAM_HOME/s-Server/catalog/fennel Takes effect on startup.
sdpListenerPort 32-bit integer, -1 for personality-based defaults. Sets the port number on which to listen for SDP connections (This is the port number specified in client JDBC URL’s). However, for connections from other hosts, see also the Network Configuration subsection below entitled Configuring JDBC Driver Connections from Other Hosts. -1 (implies 5570 for SQLstream personality) Takes effect on startup.
tempIncrementSize 32-bit integer (number of pages, 0 to disable auto-increment). Determines number of pages by which to automatically extend the temp.dat file when all existing pages have been allocated. 1000 Takes effect on startup.
tempInitSize 32-bit integer (number of pages). Determines the number of pages desired when calculating the initial size of the temp.dat file. This file is recreated every time Fennel is loaded. 1000 Takes effect on startup.

Reading system parameters

To read the values of the system parameters, you can execute the following two queries, either using sqllineClient or SQLstream s-Studio:

SELECT * FROM SYS_FEM."Config"."FennelConfig";
SELECT * FROM SYS_FEM."Config"."FarragoConfig";

Each system parameter has its own column, named for the parameter. The value in the column is the current value of the parameter.

Network Configuration for SQLstream s-Server

A SQLstream system currently makes one type of network connections:

  • Clients receiving data by means of SDP, on default port 5570. ( - SDP* is SQLstream’s own streaming data protocol.)

SDP connections use TCP transport and require a valid route from the client driver to the server. However, a server can be configured to use a different port, as described below. Note that the jdbc connection string refers to the SQLstream s-Server using a particular SDP port: jdbc:sqlstream:sdp://host:port*. The suffix port is optional, since the SQLstream client driver automatically supplies “5570” as the default SDP port value. However, if the server is actually using a different SDP port, its number must appear explicitly in the connect string.

Configuration Properties

Both types of connections, for requesting or receiving data, can be configured by setting the properties listed in the table below.

Configuration properties can be found (in $SQLSTREAM_HOME/s-Server/support) in either aspen.config or in the main properties file, aspen.properties. However, those files are not to be edited directly: the properties can be overridden by editing aspen.custom.properties, including the SDP port property aspen.sdp.port.

Configuration Element Configuration Parameter
Port on which the server listens for SDP connections from the driver aspen.sdp.port
Default: 5570
Maximum transmission unit (MTU) for SDP connections.It is the maximum size of a TCP packet in a SDP connection. aspen.sdp.mtu
Default: 65536
Number of milliseconds between sending SDP EchoRequest frames.Echo frames are pings inside of an SDP connection. aspen.sdp.pingTimerMsDefault: 2000
Minimum number of milliseconds of idle time on a given link before timing out the link on the assumption that the connection is broken or that the peer has crashed aspen.sdp.linkTimeoutMsDefault: 10000.
Throw exception: If true and link becomes mis-synchronized, exception is thrown. Exists for debugging; for production, should be set to false. aspen.sdp.throwOnProtocolError
Default: false.
Maximum size of a block of row data sent from the driver to the server. If multiple rows can fit inside of a buffer of size blocksize, then they will be packed into one TCP packet. MTU doesn’t effect how many tuples are in a TCP packet, it only limits the maximum size of a TCP packet. aspen.sdp.blocksize
Default: 65536, the maximum. Minimum value is 512.
Number of independent blocks a driver can receive. Generally the number of statements running on a specific driver. This is rarely used. aspen.sdp.rxblocksize
Default: 4. Minimum value is 2.Maximum value is maximum value of Java Integer class.

Multiple NIC cards

SQLstream generally binds its server to the “ANY” TCP IP address (0.0.0.0) which allows it to listen for network connections from any IP for which the host is receiving traffic. Generally this is only one public IP address and one loopback IP address (usually 127.0.0.1).

On some more complex production server environments, machines can have multiple NIC cards which might handle traffic on different IP addresses and/or networks. In these environments, SQLstream can be configured to listen for network connections upon one and only one IP address if desired. The default is still to bind to all IPs.

To bind to a single IP, you must enter the desired IPv4 address in the *aspen.custom.properties*file as both the aspen.controlnode.sdp.host property and aspen.controlnode.url property. In the case of the aspen.controlnode.url property, enter the value in the following format sdp://:. The port may be omitted and in this case you should remove the trailing : as well.

  • Firewalls. Make sure that the SDP ports used by SQLstream are open and that valid routes between the driver and server exist.
  • NATs. Drivers can be behind NATs but if the server is behind a NAT it will require port forwarding unless both the driver and server are behind the same NAT. This is because a driver must be able to open connections on the server.

Configuring s-Server to Use SSL

This document describes how to enable s-Server to use the Secure Sockets Layer (SSL) protocol for secure communication between itself, sqlline client, and other clients that access s-Server through JDBC. SSL lets you use server authentication, client authentication, and data encryption. It also contains steps for generating sample certificates and sample test procedures to verify SSL communication status. Most users will likely want to use server certification only.

s-Server uses openSSL. We recommend having some familiarity with openSSL before using this feature. Some knowledge of SSL-related concepts such as certificates, trust chains, certificate authority concepts, and so on would also be helpful. Information about these can be found here: https://www.digitalocean.com/community/tutorials/openssl-essentials-working-with-ssl-certificates-private-keys-and-csrs

To use client certificates, you need some familiarity with keytools and Java key storage concepts. Information about these can be found here: https://www.digitalocean.com/community/tutorials/java-keytool-essentials-working-with-java-keystores

This subtopic covers the following topics:

  • Enabling SSL in s-Server.  (This enables encrypted data transfer.)
  • Generating and Installing Self-Signed Certificates  (This enables encrypted data transfer using certificates known to an enterprise.)
  • Modifying Your Client to Work with a Secure s-Server (This enables the client to work with a secure s-Server.)
  • Using Client Certificates with s-Server (This adds client authentication in addition to encrypted data transfer.)

Overview of s-Server Technologies and Certificate Storage

s-Server is a streaming database that is written in both C++ and Java. The database runtime and the networking layer is written in C++. It uses boost+openssl for SSL/TLS and cryptography. These use the openSSL keystore to store certificates. The JDBC client and client side network layer is written in Java and uses apache-mina and java security classes. These use the java keystore to store certificates. s-Server is capable of using more recent encryption methods like ECDHE (Elliptic curve Diffie–Hellman with ephemeral keys) for encryption. ECDHE has shorter keys, faster performance and supports forward secrecy. For more information about ECDHE, see the following links. https://en.wikipedia.org/wiki/Elliptic_curve_Diffie%E2%80%93Hellman https://blog.cloudflare.com/staying-on-top-of-tls-attacks/

Enabling SSL in s-Server

The steps below describe how to enable encrypted data transfer using a server certificate and key (but not a client certificate or key). s-Server requires both a certificate and a key to use SSL. Sample self-signed certificates are installed with s-Server. These let you get started quickly. Once you are familiar with s-Server’s implementation of SSL, you should replace these with your own. When SSL is enabled, s-Server will conduct a basic TLS handshake as described here:

https://en.wikipedia.org/wiki/Transport_Layer_Security#Basic_TLS_handshake

Before beginning, make sure that your certificate and key are in \$SQLSTREAM_HOME.

These are called cert.pem and key.pem. They need to be pem formatted.

Note: When you create your own certificate and key, these must also be named cert.pem and key.pem.

You can also keep them in a separate location and create symbolic links with the names above.

To enable SSL:

  1. Add or change the following line in aspen.properties: aspen.sdp.tcpListenEnableSSL=true
  2. Start sqllineclient with the following parameters: /opt/sqlstream/<VERSION>/s-Server/bin/sqllineClient --tcpTrustAllSSL=true --tcpListenEnableSSL=true 9904 We recommend placing $SQLSTREAM_HOME/bin in your PATH. If you do so, you can run the command above as sqllineClient --tcpTrustAllSSL=true --tcpListenEnableSSL=true 9904 Once connected, all communication between sqlline client and s-Server will be encrypted via SSL.

The tcpTrustAllSSL parameter is optional and is primarily used with self signed certificates.

You can require a private key password for SSL. See Passing a Private Key Password to s-Server below.

To test that s-Server is communicating via SSL, take the following steps:

1.  After changing aspen.sdp.tcpListenEnableSSL in aspen.properties, start s-Server. 2.  Run the following openSSL command that acts as a client

openssl s_client -connect localhost:9904

It will connect and display connection information. You should see something like the following:

subject=/C=US/ST=CA/L=San Francisco/O=sqlstream.com/OU=development/CN=development.sqlstream.com
issuer=/C=US/ST=CA/L=San Francisco/O=sqlstream.com/OU=development/CN=development.sqlstream.com
---
No client certificate CA names sent
---
SSL handshake has read 1176 bytes and written 421 bytes
---
New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES256-GCM-SHA384
Server public key is 1024 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
SSL-Session:
   Protocol  : TLSv1.2
   Cipher    : ECDHE-RSA-AES256-GCM-SHA384

To test that sqlline Client Connects via SSL, take the following steps: Run the following openSSL command that acts as a server.

openssl s_server  -accept 9904 -key ./key.pem -cert ./cert.pem  -debug -msg [ -CApath /etc/ssl/certs ]

Run the sqllineClient command below

./sqllineClient --tcpTrustAllSSL=true --tcpListenEnableSSL=true 9904

OpenSSL will display connection information along the following lines:

<<< TLS 1.2 Handshake [length 0010], Finished
.......
CIPHER is ECDHE-RSA-AES128-SHA256
Secure Renegotiation IS supported
......

Checking for Client Handshake Errors

To see errors, use the self signed certificate and start the server, and try to connect sqlline without the tcpTrustAllSSL parameter.

The connection will fail. Look at trace/ClientTrace.log around the following errors:

VirtualBox/127.0.1.1:9904) WARNING [15 2016-04-13 15:08:19.184]: com.sqlstream.aspen.sdp2.StreamingDataProtocolImpl exceptionCaught
SSL handshake failed.                                                                               WARNING [15 2016-04-13 15:08:19.184]: com.sqlstream.aspen.sdp2.StreamingDataProtocolImpl exceptionCaught SSL handshake failed

Generating and Installing Self-Signed Certificates

The following commands are used to generate self-signed sample certificates, using elliptic curve secp256r1.

You can use the same steps to generate your own certificate with your own organizational name and other information.

openssl genpkey  -algorithm RSA  rsa_keygen_bits:3072 ec_paramgen_curve:secp256r1 -out server.key
openssl req -new -key server.key -out server.csr
openssl x509 -req -days 3650 -in server.csr -signkey server.key -out server.crt

Once you have generated self-signed certificates, place them in $SQLSTREAM_HOME. These should be called cert.pem and key.pem. They need to be pem formatted.

Modifying Your Client to Work with a Secure s-Server

This enables your Java client communicating over JDBC to work with a secure s-Server. To write Java code for the JDBC Client that Uses SSL, you need to pass the same arguments that the sqllineClient accepts in your Java code either through connection properties or as part of the url.

Below is an excerpt of the actual sqllineClient code that in turn establishes the JDBC connection.

final Properties info = new Properties();
                info.put("user", username);
                info.put("password", password);
                info.put("tcpListenEnableSSL", Boolean.toString(opts.gettcpListenEnableSSL()));
                info.put("tcpTrustAllSSL", Boolean.toString(opts.gettcpTrustAllSSL()));

           connection = theDriver.connect(url, info);
           meta = connection.getMetaData();

Alternatively, you can pass the same arguments as part of the URL string:

jdbc:vjdbc:sdp://localhost:9904;tcpTrustAllSSL=true;tcpListenEnableSSL=true

To try it, again use sqllineClient and escape the special chars at command line.

./sqllineClient -n sa -p test -u jdbc:vjdbc:sdp:'/'/localhost:9904';tcpTrustAllSSL=true;'tcpListenEnableSSL=true

Using Client Certificates with s-Server

You can also set s-Server to accept only JDBC clients that provide client certificates. When enabled, s-Server will authenticate with certificates exchanged between server and client, as described here: [ https://en.wikipedia.org/wiki/Transport_Layer_Security# Client-authenticated_TLS_handshake] You do so by enabling the following setting aspen.properties:

aspen.sdp.ssl.client.verify_fail_if_no_peer_cert

After you enable this setting, s-Server will require client certificates from JDBC clients. Connections without such certificates will fail. The JDBC client should send the certificate to the server under the alias com.sqlstream.client.jdbc.

The server also has a setting called aspen.sdp.ssl.client.CApath for adding a certificate verification path.

Enabling SSL for JDBC Clients

Client side certificates are in addition to the SSL on the server. Once enabled, s-Server will authenticate the client by determining that it has a client side certificate.

To enable SSL for JDBC:

  • Make sure that s-Server is SSL enabled.
  • Store your certificate under the alias com.sqlstream.client.jdbc
  • Acquire a certificate from a certification authority or use the command below for a self signed certificate for testing purposes.
sudo keytool -genkey -alias com.sqlstream.client.jdbc -keyalg RSA -validity 3650 -keystore /usr/lib/jvm/java-8-oracle/jre/lib/security/cacerts

It will ask you for the keystore password. The default password is “changeit”.

The client code honors standard Java keystore system settings and passwords, as described in the following link. http://docs.oracle.com/javase/7/docs/technotes/guides/security/jsse/JSSERefGuide.html# Customization

If no keystore and password are given, then the client uses the default keystore and password.

Using Self-Signed Certificates with JDBC

If you use a self-signed certificate then you need to import it to the server certificate store, so that it can be trusted and verified.

To do so:

Export the self-signed certificate.

keytool -exportcert -alias com.sqlstream.client.jdbc -file com.sqlstream.client.jdbc.der  -keystore /usr/lib/jvm/java-8-oracle/jre/lib/security/cacerts

Convert it to from .der to .pem format.

openssl x509 -inform der -in com.sqlstream.client.jdbc.der -out com.sqlstream.client.jdbc.pem

Copy it to the certificate store

sudo cp com.sqlstream.client.jdbc.pem /etc/ssl/certs

Create a hashed link in the certs directory

cd /etc/ssl/certs
sudo ln -s com.sqlstream.client.jdbc.pem `openssl x509 -hash -noout -in com.sqlstream.client.jdbc.pem`.0

For more information on openSSL and certificates refer to the following link: http://gagravarr.org/writing/openssl-certs/others.shtml# selfsigned-openssl

Check if your certificate can now be verified.

openssl verify [-CApath <ssl-base-dir>certs] com.sqlstream.client.jdbc.pem

If openSSL asks you to set -CApath set during verification, you likely need to set the  verify_fail_if_no_peer_cert option in aspen.properties. Otherwise, the server may not locate the verification path and fail to verify the certificates. Next, run the openSSL server  that requires the client to send certificates:

openssl s_server  -accept 9904 -key ./key.pem -cert ./cert.pem  -debug -msg -Verify 10 [ -CApath /etc/ssl/certs ]  

The Verify argument makes the client send the certificate and the openSSL server reject the connection otherwise.

Verify that the server requires the client to send certificates. In a separate terminal run the openSSL client, using the real/verifable server certificates.

openssl s_client -connect localhost:9904 -cert cert.pem  -debug -msg -CApath /etc/ssl/certs

It should connect and display client certificate information towards the very end. Excerpts are below.

subject=/C=US/ST=CA/L=San Francisco/O=sqlstream.com/OU=development/CN=client.jdbc
issuer=/C=US/ST=CA/L=San Francisco/O=sqlstream.com/OU=development/CN=client.jdbc

Try the same command without appending -cert. It should fail to connect. Connect via sqllineClient

./sqllineClient --tcpListenEnableSSL=true 9904

It should connect and use SSL with client certificates. Start your server and verify that it requires client side certificates

Set sdp.ssl.client.verify_fail_if_no_peer_cert before starting the server.

Connect to your server using openssl s_client with and without -cert flags.

This will verify that your server requires client certificates.

Now, connect with sqllineClient. If it can connect, it means that the client certificates are exchanged and verified.  

When client side certificates are enabled you can control client access based on the SHA1 fingerprint of the certificate. See Accepting or Rejecting Clients Based on Certificate SHA1 Fingerprint below.

Importing Your Client side Certificate and Key into the Java Keystore

You need to import them via a pkcs12 keystore because the java keytool is not capable of importing both cert and key.  Please refer to the relevant sections of this document: https://docs.oracle.com/cd/E19509-01/820-3503/6nf1il6er/index.html

Concatenate your cert and key into a single file referred as cert_key.pem below. Then issue the following commands to import via a .pkcs12 store

cat mykey.pem mycertificate.pem >cert_key.pem
openssl pkcs12 -export -in cert_key.pem -out cert_key.pkcs12 -name com.sqlstrem.client.jdbc -noiter -nomaciter
keytool -importkeystore -srckeystore cert_key.pkcs12 -srcstoretype PKCS12 -destalias com.sqlstream.client.jdbc -destkeystore /usr/lib/jvm/java-8-oracle/jre/lib/security/cacert

Troubleshooting SSL and Certificate Issues

You can use the following system settings to generate debugging information

-Djava.security.debug=all
-Djavax.net.debug=all

Also, the server logs will contain traces using SdpVerifyCallBack tag. Similarly, the client logs will contain traces with sun.security.ssl.

Passing a Private Key Password to s-Server

You can, if you choose, require a private key password for communication with s-Server. To do so, use the following commands to generate a password protected private key.

openssl genpkey  -algorithm RSA  rsa_keygen_bits:3072 ec_paramgen_curve:secp256r1 -out server.key -passout -pass:"changeit" > key.pem
openssl rsa -des3 -in key.pem -out key.new.pem
cp key.new.pem key.pem
openssl req -new -key key.pem -out cert.csr -subj "/CN=testpassword2"
openssl x509 -req -days 3650 -in cert.csr -signkey key.pem -out cert.pem

Verify that you can’t open it unless you have the password.

openssl rsa -noout -text -in server.key

Next, start the s-Server with the parameters above. If your server is started with incorrect password, all the SSL and network communication will be unusable.

Accepting or Rejecting Clients Based on Certificate SHA1 Fingerprint

When client side certificates are enabled you can control client access based on the SHA1 fingerprint of the certificate. s-Server will verify the certificate and if the fingerprint is in the list then the connection will be accepted. s-Server has the following setting for this purpose.

aspen.sdp.ssl.client.acceptFPs

Here, you can designate a comma separated list of SHA1 finger prints. It is a 160 bit(20 byte) number.

It needs to be encoded in hexadecimal with upper case letters., two letters/digit per byte.

aspen.sdp.ssl.client.acceptFPs=D9A5A4C4448C6FEA9865E2827A583F3ADE47F0A9

If the acceptFPs list is empty, then no fingerprint filtering will be done.

Other Settings for Client Communication with SSL

If you are using a version of openSSL other than the one installed on your system, or need to add some other certificate path, you may need to set it manually. Pasted below are all the aspen.properties settings related to clients

With SSL on, require the client certificate, reject the connection if it fails to verify

aspen.sdp.ssl.client.verify_fail_if_no_peer_cert=true
# The Certificate path to add to openssl in addition to the default path. Usually /etc/ssl/certs
# You may need to set this if you have a different openssl installation
# that doesn't use the default on the system
aspen.sdp.ssl.client.CApath=/etc/ssl/certs
# With client certificates enabled, trust all certificates regardless of verification.
# Primarily used during setup while testing certificates
aspen.sdp.ssl.client.tcpTrustAllSSL=true
# With client certificates enabled, accept only the client certificates with matching finger prints.
# A comma separated string of SHA1 fingerprints of the certificates, hex encoded with uppercase letters
# Eg: D9A5A4C4448C6FEA9865E2827A583F3ADE47F0A9
# Can be generated using: openssl x509 -noout -in cert.pem -fingerprint -sha1
# aspen.sdp.ssl.client.acceptFPs=

openSSL and Integrable Versions of s-Server

The integrable version of the product comes with openSSL 1.0.1. We recommend instead using the openSSL that comes on your target system. That way, you can update it using package tools like apt-get and yum. Our product works with openSSL 0.9 through 1.0.2. The installable version of the product uses whatever openSSL is available on the system.

This only applies in very limited situations; generally, you should know if your organization has integrated s-Server into their product.