SAS Federation Server Driver for MDS

About the Memory Data Store (MDS)
The MDS Data Service
Data Service Connection Options for MDS
MDS Database Memory
Limiting Memory Size
The MEMORY Table
FedSQL Views and Data Caching with MDS

About the Memory Data Store (MDS)

Memory Data Store, or MDS, is a transactional in-memory data store that can be used with SAS Federation Server. MDS must be used with FedSQL. MDS runs strictly in memory with no backup data store. Therefore, changes are lost when the database is dropped or the server is restarted.
The database is created in memory when the first user connects to the database. The database remains in memory until one of the following conditions is met:
  • The server is shut down.
  • The data service or the catalog associated with the data service is dropped.
Note: You cannot drop a MDS data service or catalog if users are connected to the data service.
You can rename the database and change the memory value while users are connected, but you cannot drop the database while users are connected. To drop or rename a schema, the table within the schema cannot be in use. Users can be connected to the database, but they cannot have a table open in the schema. Also, you cannot drop a table if it is referenced by a prepared statement or has a pending transaction with uncommitted changes.
MDS supports optimistic concurrency providing a snapshot transaction, which means that a transaction sees a consistent version of the data when the transaction is started. When an MDS transaction starts, the state of the database is logically frozen at that point in time. The transaction sees the database consistently but changes made by other transactions are not visible until the transaction is committed or rolled back, and its state synchronized so that it sees the new state of the database.
To access data in an MDS table, you must first configure an MDS data service and DSN.

The MDS Data Service

You can configure an MDS data service and table using one of these methods:
You can create multiple data services if needed. See SAS Federation Server Driver for MDS for a list of connection options for MDS.
Each MDS data service catalog contains a pre-defined, read-only schema, named SYSTEMINFO. The SYSTEMINFO schema contains an auto-generated MEMORY table. You will need to define at least one additional schema to use MDS. To define a schema use the CREATE SCHEMA DDL statement or the New Schema function in SAS Federation Server Manager. Schemas cannot be modified, renamed or dropped while there are active connections to the database.

Data Service Connection Options for MDS

MDS supports the following connection string options.
Option
Description
LOCALE
LOCALE=SAS locale identifer
Specifies the locale for message text and character conversion, both ‘to’ and ‘from’.
ENCODING
ENCODING=encoding-value
Specifies character encoding for the MDS table. The default is the encoding used for the SAS session. If SAS is not used, the operating system encoding is used as the default.
DB | DATABASE
DATABASE=database name
Specifies the in-memory database instance. DATABASE must be specified if CONOPTS= is not specified. The database defaults to the catalog name if a database name is not specified.
CATALOG
CATALOG=catalog name;
Specifies the catalog name. CATALOG must be specified if CONOPTS= is not specified.
CONOPTS
CONOPTS=connection string options
Specifies the connection string options for the driver to cache in memory. If a connection string is not specified, the default is in memory only.
COMMIT
COMMIT=Y|N
Specifies if the in-memory changes are written to the CONOPTS= driver. COMMIT must be used with the CONOPTS= option.
BULKLOAD
BULKLOAD=Y|N
Specifies if data is inserted immediately, which bypasses transactions. The BULKLOAD option is valid only when CONOPTS= is not specified.
NUMERICS
NUMERICS=Y|N
Allows numeric data types or treats them as double precision. The default is Y (Yes).
RETAIN
RETAIN=Y|N
Specifies if the in-memory database is dropped after the last client disconnects. The default is N (No).
CT_PRESERVE
CT_PRESERVE = STRICT | SAFE | FORCE | FORCE_COL_SIZE
Allows users to control how data types are mapped. Note that data type mapping is disabled when CT_PRESERVE is set to STRICT. If the requested type does not exist on the target database, an error is returned. The options are as follows:
  • STRICT The requested type must exist in the target database. No type promotion occurs. If the type does not exist, an error is returned.
  • SAFE Target data types are upscaled only if they do not result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure all characters can be stored in the new encoding.
  • FORCE This is the default for all drivers. The best corresponding target data type is chosen, even if it could potentially result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure all characters can be stored in the new encoding.
  • FORCE_COL_SIZE This option is the same as FORCE, except that the column size for the new encoding is the same as the original encoding. This option can be used to avoid column size creep. However, the resulting column might be too large or too small for the target data.
IDCASE
IDCASE=SENSITIVE | INSENSITIVE
Specifies if schema, table, column, and alias identifiers are case-sensitive or insensitive. The default is case sensitive. IDCASE is valid only when CONOPTS= is not specified.
DEFAULT_ATTR
DEFAULT_ATTR=(attr=value;...)
Used to specify connection handle or statement handle attributes supported for initial connect-time configuration, where attr=value corresponds to any of the following options:
  • CURSORS=n- Connection handle option. This option controls the driver’s use of client side result set cursors. The possible values are 0, 1 or 2.
    0 Causes the driver to use client-side static cursor emulation if a scrollable cursor is requested but the database server cannot provide one.
    1 Causes the driver to always use client-side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor is not used.
    2 (Default) Causes the driver to never use client-side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor is used if available. Otherwise, the cursor will be forward-only.
    Example: DEFAULT_ATTR=(CURSORS=2)
  • USE_EVP=n - Statement handle option. This option optimizes the driver for large result sets. The possible values are 0 (OFF) or 1 (ON), which is the default. Example: DEFAULT_ATTR=(USE_EVP=0)
  • XCODE_WARN=n - Statement handle option. Used to warn on character transcoding errors that occur during row input or output operations. Possible values are 0 (returns an error), 1 (returns a warning), or 2 (ignore transaction errors). 0 is the default. Example: DEFAULT_ATTR=(XCODE_WARN=1)
DEFSCHEMA
DEFSCHEMA=schema name
Specifies the default schema for identifiers with no schema qualifier. The default is the first SCHEMA= in the connection string. This option is valid only when CONOPTS= is not specified.
SCHEMAS
SCHEMAS=(“schema1”;”schema2”;”schema3”)
Specifies a list of schemas defined in the database. Identify schema names with double quotation marks and separate each name by a semicolon.
SCHEMA
SCHEMA=(NAME=schema-name1);SCHEMA=(NAME=schema-name2);...
Defines one or more schemas in the database. The default is a single schema using the defined catalog name.
REFTYPE
REFTYPE=VARCHAR | NVARCHAR | VARBINARY
Indicates that duplicate column data should be stored once and referenced by result sets rather than having separate instances in each row. This reduces memory usage with large numbers of duplicate data but might slow down performance.
VARCHAR
Create a REFCHAR instead of a VARCHAR when specified. The default is create VARCHAR.
NVARCHAR
Create an NREFCHAR instead of an NVARCHAR when specified. The default is create NVARCHAR.
VARBINARY
Create a REFBINARY instead of a VARBINARY when specified. The default is create VARBINARY.
Note: A REFCHAR(32) uses less space than a VARCHAR(32) if there are many duplicate values in the table or if the data is less than 32 characters. However, a REFCHAR(1) generally uses more memory than a VARCHAR(1) because an extra pointer has to be stored instead of a single character.
MAXDBMEM
MAXDBMEM=number of bytes
Specifies the maximum amount of memory the database can use to store all row data for all tables. The default is 0 which specifies that there is no limit to the amount of memory used. MAXDBMEM=0.

MDS Database Memory

Limiting Memory Size

To limit the memory size for an MDS database, use the connection string option MAXDBMEM= that specifies the maximum size of memory to be used to store all rows of data in the database. This includes committed rows and pending row versions (INSERT, UPDATE, and DELETE operations that have not yet been committed or rolled back). If an INSERT, UPDATE, or DELETE operation exceeds this limit, an out of memory error is returned.

The MEMORY Table

The MEMORY table, SYSTEMINFO.MEMORY, contains information about memory usage and is always available. The table does not actually reflect how much data is in the table. Instead, it shows how much memory is being used by MDS to store the table, along with MEM_PEAK and MEM_LIMIT, if specified. The first row contains statistics about the MDS database. Subsequent rows provide information about each of the tables in the MDS database.
The MEMORY table includes the following columns:
Columns in SYSTEMINFO.MEMORY Table
Column Name
Description
DB_NAME
The name of the current database. This will be the same as the catalog name.
SCHEMA_NAME
The name of the schema for the table (NULL for the database info row).
TABLE_NAME
The name of the table (NULL for the database info row).
ROW_COUNT
The number of rows in the table (NULL for the database info row).
ROW_SIZE
The size of a single row in the table (NULL for the database info row).
MEM_SIZE
The current memory used by the table and database for data.
MEM_PEAK
The peak memory used by the table and database since creation.
MEM_LIMIT
The maximum memory this database can use (NULL for table info rows). This value corresponds to the MAXDBMEM= option specified when the database was created.

FedSQL Views and Data Caching with MDS

You can create federated SQL views and cache data from an MDS table. Because the data is in-memory and does not persist, view definitions are removed when the MDS table is dropped, if a REFRESH has not been set on the cache.
  • If the cached view does not reside in MDS, the cache remains intact but reflects a status of deferred or inactive and can be refreshed.
  • If the cached view resides in MDS, the view and cache objects that are stored in MDS are removed from the system tables.
If REFRESH has been configured on a cache, the cache refreshes at server start up:
  • An in-memory cache is deferred at server start up.
  • A cache that is set to refresh at start up is refreshed, even if it is disabled. If the refresh is successful, a deferred cache becomes active.
  • A cache that is disabled, remains disabled after a refresh.
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: March 6, 2018