NonStop SQL

View Current Viewing Revision #19 from 12/10/2019 2:03 a.m.

NonStop SQL is a database management system designed for 24x7 availability, high scalability, and excellent performance of online transaction processing (OLTP). The availability is achieved by isolation -- the transactions affected by hardware or software failures will be recovered without affecting other transactions. As for scalability, NonStop SQL is able to scale up almost linearly. It's regarded as a base for highly parallel systems. The fast OLTP is due to the row-partition storage model and fine granularity. The lock on data can be at the individual row level. NonStop SQL is widely used in stock exchanges, banks, and telecommunications.

History

NonStop SQL is the first SQL implementation with fast performance of online transaction processing (OLTP). Previously, all SQL database management systems had a slow OLTP performance, but they were still widely used due to the simplicity of SQL. To compensate, people often used a second, non-SQL database management system for OLTP-heavy applications. The company which developed NonStop SQL -- Tandem -- decided to relieve people from the double database usage. 25 developers of Tandem spent 3 years to write .5 million lines of code as the original code base of NonStop SQL.

Query Compilation

Code Generation

The query compiler transforms the SQL source commands into executable query plans in host language. The executable query plans and the SQL source commands are stored in a newly created object program file.

Indexes

B+Tree

NonStop SQL supports B+ tree index. By default, the primary key is used as the index of a table. To speed up the non-primary key look up, an index table can be created based on selected columns.

Concurrency Control

Two-Phase Locking (Deadlock Prevention)

The users can set the lock granularity, lock protocol, and lock waits styles in the CONTROL TABLE. The deadlock prevention is based on lock timeouts.

Logging

Logical Logging

Every single transaction has one single corresponding log record. This log record contains information about how to perform redo, undo, and media recovery.

Views

Virtual Views

Whenever a view is created, NonStop SQL stores a view definition instead of the materialized table. Whenever a user selects data from a view, the data is directly retrieved from the underlying base tables.

Joins

Nested Loop Join Hash Join Sort-Merge Join Semi Join

NonStop SQL supports hash join, nested loop join, and sort-merge join. The developers of NonStop SQL didn't devote lots of effort on distributed join optimization, because they thought those optimization makes little sense. To the best of our knowledge, NonStop SQL supports semi-join.

Storage Architecture

Disk-oriented

The data of every table is stored in one or more disk files. Each disk file has a label storing the name and catalog information of the associated table.

Data Model

Relational

NonStop SQL supports the basic features of relational database management systems. What distinguish it from many other relational DBMS are the additional features providing excellent OLTP performance, availability, and scalability.

Storage Model

N-ary Storage Model (Row/Record)

NonStop SQL uses the N-ary Storage Model, which is appropriate for heavy OLTP workloads.

Query Execution

Tuple-at-a-Time Model

Revision #19 | Updated 12/10/2019 2:03 a.m.

View Current Viewing Revision #19 from 12/10/2019 2:03 a.m.

Website

https://www.hpe.com/us/en/servers/nonstop.html

Tech Docs

https://support.hpe.com/hpsc/doc/public/display?docId=emr_na-c02148175

Developer

Tandem

Country of Origin

Start Year

1987

Acquired By

Project Type

Commercial

Written in