New features in SQL/MX Release 3.5

SQL/MX Release 3.5 introduces the following features:

Database services and multitenancy support

Database Services (DBS) allows you to create and manage user databases in a cloud based environment. The user databases hosted on the NonStop server are isolated from each other with the help of isolation features such as, user isolation, resource isolation, and metadata isolation. The users or applications accessing a user database can access only those objects and resources associated with the database.

A new installation script is added to configure the DBS environment with resources required for user databases. A new OSS command-line tool, named mxdbs, is available for creating and managing user databases. The mxdbs tool can be executed from the OSS command-line or integrated into management tool that provides a self-service model for end users accessing SQL/MX database.

For more information on database services and user databases, see the SQL/MX Database Services Manual.

Database compatibility features

As a part of database compatibility features, SQL/MX now supports additional language and datatype constructs which makes application migration easier to SQL/MX from other databases with less application changes. While most of the features are enabled by default, some of them need to be enabled by setting the CQD DC_MODE to 1. The following are the new features:

  • MERGE statement – The function of the MERGE statement is to update a table if a row already exists and insert a new row into the table if it does not exist, based on the result of the search condition specified in the ON clause. The MERGE statement merges matching rows from one table to another.

  • DATE type2– DATE type2 is a new data type introduced as a part of the database compatibility features. While the current SQL/MX DATE data type displays a date format using year, month, and day values, the DATE type2 stores the year, month, day, hour, minute, and second values.

    The default format for DATE type2 is YYYY-MM-DD. Therefore, by default, the time value of DATE type2 is not displayed. The DATE type2 format can be modified using the session default attribute, DATE_FORMAT.

    When the DC_MODE is set to 1, all DDL and DML statements treat DATE keyword as DATE type2.

  • TO_DATE– The TO_DATE function converts a string containing date or date and time value to a DATE data type.

    When the DC_MODE is 1, the TO_DATE function returns a DATE type2 object, else returns a DATE object.

  • NUMBER– NUMBER is a numeric data type introduced as a part of database compatibility features. NUMBER is an exact numeric data type that can represent a value exactly.

    The CQDs NUMBER_DEFAULT_PRECISION and NUMBER_DEFAULT_SCALE are used to customize the default precision and scale values for NUMBER data type.

  • VARCHAR2– VARCHAR2 is a variable character data type that stores varying length character strings. It is similar to VARCHAR data type. VARCHAR2 supports ISO88591 and UCS2 character sets with the default being ISO88591. VARCHAR2 data type can be accessed from MXCI, RMXCI, and JDBC/ODBC applications.

    VARCHAR2 is not supported for embedded C and COBOL applications. Objects with VARCHAR2 columns are supported in all other SQL/MX utilities.

    For more information on the database compatibility features, see the SQL/MX 3.5 Release Technical Update.

Materialized views

A materialized view can be defined as a query that may be an aggregate query or a join query or an aggregate and join query. SQL/MX 3.5 supports only Materialized Join Views (MJV).

Materializing a view is a commonly used database operation in OLAP and DSS applications. After materialization, the view tuples are stored as in regular table and you can query the materialized view (MV). Using a materialized view provides fast access to data. Materialized views are useful in applications such as data warehousing, replication servers, billing, recording systems and mobile systems.

For more information on the Materialized Views feature, see the SQL/MX 3.5 Release Technical Update.

Support for schema level privileges

SQL/MX now supports both DDL privileges and DML privileges at the schema level. Schema level privileges allows the schema owner to grant or revoke privileges on an entire schema to a user or a group of users using the GRANT and REVOKE statements.

With DDL privileges, it is possible to grant authorization to execute DDL and Utility operations at the schema level. Granting schema level DDL privileges allow the 'grantees' to perform DDL operations on that schema, and on existing and future objects in that schema.

With DML privileges, it is possible to grant authorization to execute DML operations at the schema level, and such authorization applies for individual objects within the schema. Schema level DML privileges allow the types of privileges that can be granted on individual objects to be granted on an entire schema; such privileges will pertain to existing and future objects in that schema.

For more information on the Schema Level Privileges feature, see the SQL/MX 3.5 Release Technical Update.

User management

SQL metadata is extended to represent users, and groups of users, known to SQL. A user will have a Guardian username, a Guardian user id, and an optional external name, and can be member of one or more user groups known as "privilege groups". Database Services also use the user management features. The user management features include the abilities to:

  • Represent Guardian usernames of owners, grantors, and grantees in metadata.

  • Associate an external username with a Guardian username, and use that external name on all commands that currently accept a Guardian username.

  • Create groups of users known as privilege groups.

  • Accept a privilege group as grantee on GRANT and REVOKE commands.

For more information on the User Management feature, see the SQL/MX 3.5 Release Technical Update.

PL/MX and user-defined functions

Procedural Language for NonStop SQL/MX (PL/MX) is a procedural language supported in SQL/MX 3.5 and is similar in many respects to the Oracle PL/SQL and ANSI SQL/PSM. User-defined functions (UDFs) provide a way to implement custom business logic that becomes part of the database. Like functions in other programming languages, UDFs accept parameters and return scalar values. NonStop SQL/MX UDFs are implemented in NonStop SQL/MX UDFs sometimes referred to as stored functions.

SQL/MX Release 3.5 and later versions, support the creation, use, and management of UDFs.

For more information on PL/MX and programming functions using PL/MX, see the Procedural Language for NonStop SQL/MX (PL/MX) Reference Manual.

Configurable IP

Configurable IP allows MXOAS to listen on a single IP address or on all the IP addresses in the system based on the system configuration. Multiple instances of MXOAS can be run on the same system with different IP addresses on same or different port. You can reuse the port number as the environments are not notified of the used IP address in another environment.

AF_UNIX protocol support

ODBC/MX 3.5 supports AF_UNIX protocol for communication between OSS ODBC/MX driver and ODBC/MX Server to ensure that database connections are not impacted with CLIM failures. New evar AF_UNIX must be set to TRUE to use AF_UNIX sockets.

MXDM changes

The SQL/MX Database Manager (MXDM) has been enhanced to support the following features:
  • Support for new DC data types - VARCHAR2, DATE type2, NUMBER
  • Support for PL/MX functions
  • Create and drop Stored Procedures for Java
  • Change ownership of SQL/MX objects
  • Support for Materialized Views
  • Configuration of Workload Management Services
  • Isolation support for Multitenancy
  • Ability to GRANT or REVOKE management and administrative privileges to system users.

For more information on these features, see the MXDM User Guide for SQL/MX Release 3.5.

In addition to the above features, infrastructure for future monitoring capabilities is added. Run Time Statistics (RTS) and Workload Management Services (WMS) are introduced. RTS is the infrastructure for generating the statistics information for both running and completed queries. WMS is the infrastructure for identifying and throttling query workloads running on the system. These are delivered with SQL/MX 3.5 but disabled until future products that require these subsystems will need them to be turned on.