Native tables in NonStop SQL/MX

Native Tables in NonStop SQL/MX
Frans Jongma,
Hewlett-Packard
Advanced Technology Center
NonStop Enterprise Division
Version 1.0
Date Nov 22, 2013.

Abstract
HP NonStop SQL/MX is the relational database management system (RDBMS) that is based on ANSI
SQL:1999 with extensions from ANSI SQL:2003. The product can be seen as combination of a SQL engine
and a SQL file system. The engine can be used to access the tables of previous RDBMS called NonStop
SQL/MP as well as the SQL/MX native tables in the native SQL/MX file system.
Many customers of NonStop SQL/MP are using the SQL/MX engine to access the data that is stored in
SQL/MP tables. They enjoy the features of ANSI DML and use the JDBC drivers in Java programs and ODBC
drivers for off-platform applications written in other languages.
This document summarizes the advantages of using the NonStop SQL/MX native tables. It is intended for
architects, designers, developers of applications written for or being ported to NonStop Servers as well as
database administrators (DBAs) that manage SQL/MP or SQL/MX databases.

Introduction
HP NonStop SQL/MX is the relational database management system (RDBMS) that is based on ANSI
SQL:1999 with extensions from ANSI SQL:2003. The product can be seen as a SQL engine and a SQL file
system. The engine can be used to access the tables of previous RDBMS called NonStop SQL/MP as well as
the SQL/MX native tables.
Many customers of NonStop SQL/MP are using the SQL/MX engine to access the data that is stored in
SQL/MP tables. They enjoy the features of ANSI DML and use the JDBC drivers in Java programs and ODBC
drivers for off-platform applications written in other languages.
Long-time NonStop customers may recall some of the SQL/MX features, such as the support for
Referential Integrity (RI), and GRANT/REVOKE for access control to the data. However, in the last few
years, many new features have been added to the SQL/MX file system and customers that have been
using the engine only may not be aware of the complete feature set that the newer file system might
bring them. In this document, the features are grouped into the following categories:





Security and data integrity: For example, GRANT/REVOKE, Referential Integrity, triggers.
File characteristics: Hash partitioning, support for large (up to 32KB) rows and large keys.
Extended data type support: For example, big numbers.
Other features: Meta data, tools and utilities.

HP NonStop Advanced Technology Center
It is assumed that the reader is familiar already with the HP Nonstop SQL/MX Comparison Guide for SQL/MP
Users manual. Additional references to additional documentation are provided in the sections where they
apply. The NonStop SQL/MX manuals can be found in the NonStop Technical Library at:
www.hp.com/go/nonstop-docs. From this page, select the appropriate server model, H-Series or J-Series.
This document refers to the manual titles as they appear in the Technical Library without the release
number, which is 3.2.1 at the time of this writing.

Security and Data Integrity features
The security and data integrity features of the NonStop SQL/MX file system enhance the quality of the
data by enforcing the ANSI model of access control to the data in addition to the integrity checks prior to
deleting or adding a row to the database. These checks include removal of dependent rows if a parent row
is deleted in a cascaded delete operation. Database triggers may be used to create more complex
validation rules.

ANSI user access control
Access to SQL/MX objects is granted to PUBLIC, the set of user IDs known to the system, or to specific
users of the system by their Guardian user ID. SQL/MX allows the creation of a Security Administrator
group. This group contains the users that are allowed to GRANT and REVOKE object-privileges to other
users without having access to the objects themselves. This feature is also referred to as “Separation of
Duties” and was introduced in Release 3.1. In older releases, the Super-ID, or an operator-ID was used to
create and own the objects, but this owner was also allowed to access the data.
Users are added to the Security Administrators by granting them security administrator
(SECURITY_ADMIN) rights.
The list of security administrators can be obtained by the following command in mxci.
>>get all security_admins;
--- SQL operation complete.
>>

If no Security Administrator group exists, the Super-ID has the privilege to maintain metadata, such as
granting other users to create catalogs, grant users to create schemas in a catalog that is not owned by
these users and to revoke permissions. If a Security Administrator group exists, only the members of this
group have this privilege, and the Super-ID only has the privilege if it has been explicitly added to this
group.

Referential Integrity constraints
Referential Integrity (RI) constraints define relations between tables and may restrict addition or delete
operations on one table based on the existence of data in another table. The typical example is a
constraint on an employee table that only allows adding an employee if the department number exists in
the department table. And, optionally, the restriction to delete a department row if there is still an
employee row referencing it. Without the DBMS enforcing these rules, program code must be written to
do the same. Using RI constraints can therefore simplify the application development, especially where


-2-

the effect of an update or delete requires cascading to other tables. But more importantly, without these
RI constraints, one should not allow end-user tools to access the database in update mode.

Triggers
A trigger is a mechanism to perform certain actions automatically in response to specified database
events such as INSERT, UPDATE and DELETE. These actions can be performed before or after the event.
There are many uses for triggers, not only in relation to security and data integrity.
Triggers may call Stored Procedures to perform more complex code than the trigger definition allows,
which can be useful when porting code from other DBMSes.
References
SQL/MX reference manual: Chapter 1, Security.

File characteristics
SQL/MX native tables are key-sequenced, TMF-audited tables that are defined in SQL/MX catalogs and
schemas. SQL Access to these tables can only happen using their three-part ANSI name
(catalog.schema.table_name). The physical locations of these tables are in Guardian subvolumes that
have 8 character names starting with ZSD. SQL/MX tables are represented by two files: one holding the
data, the other containing system information. This latter file is also referred to as the resource fork. A
DBA can assign a 6 character Guardian name to the table; however, in most cases these names are
system-generated. The last two position of the file name denotes whether it is the data fork (value 00), or
the resource fork (value 01).
With the new file system, new features were introduced.

Increased block, row and key sizes
SQL rows are stored by the Disk Access Manager (DAM) in blocks of either 4K or 32K bytes. The DBA can
define the block size when the table is created. The system default is defined by the DEFAULT_BLOCKSIZE
Control Query Default (CQD) and is 4096 bytes.
SQL/MX tables can have 4K block and 32K block sizes if the row size is less than 4036 bytes. The
maximum row size available to users is 32708 bytes. Row definitions that exceed 4K bytes, can only be
stored in blocks of 32K (32768) bytes. It may be beneficial to store small rows in 32K blocks, because the
blocks are also used to store the index structures of the keys. Larger blocks can hold more index entries
resulting in a lower number of index levels.
The maximum primary key size of a SQL/MX native table is 2048 bytes when no triggers are defined. With
triggers defined, the maximum size is 2032 bytes. Tables with 4K blocks support a maximum clustering
key size of 2010 bytes.
Indexes are subject to the same restrictions as the base tables. The sum of the columns that form the key
of the index may not exceed 2048 bytes when the index is stored in 32K blocks. Note that the length of
the key of an index is the sum of the lengths of the columns that form the key plus the sum of the length
of the clustering key of the base table if the index is non-unique.


-3-


Multiple partitions per volume
All SQL objects of a schema reside in the same Guardian subvolume, but multiple partitions of a table or
index may be present on the same disk/subvolume. This is different from SQL/MP partitioned tables
where every disk can only have one partition of a table or index because all partitions have the same
Guardian file name. This feature allows a development system to have the same number of partitions as a
production system but on a smaller amount of physical disks.

Decoupling of clustering and partitioning keys
SQL/MX allows more freedom in ordering and partitioning the data compared to SQL/MP tables. In
SQL/MP, the order of the columns in the primary key determines the order of the rows placed on disk as
well as the way the data can be partitioned. In a table or index definition, SQL/MX recognizes three key
types: the storage or clustering key, the partitioning key and the primary key.
Storage or Clustering key
The storage or clustering key determines the order of the rows in each partition of the table. All SQL/MX
tables are key-sequenced and the index blocks within the base table contain the index records in the order
of the storage key. The set of columns that make up the clustering key must guarantee uniqueness. If
necessary, SQL/MX will add an additional column to the clustering key to create uniqueness, the SYSKEY
column.
The example shows the creation of a table without a primary key, but with a storage key specified. The
order of the rows within the table is based on region. Even without a primary key, partitioning is possible:
the table is partitioned on the REGION column.
>>CREATE TABLE ORDER_EXAMPLE
+>(ORDERNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
+>PARTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
+>REGION CHARACTER(10) NO DEFAULT NOT NULL,
+>UNIT_PRICE NUMERIC (8,2) NO DEFAULT NOT NULL,
+>QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL )
+>STORE BY (REGION)
+>LOCATION $DATA01 NAME PARTITION_01
+>RANGE PARTITION BY (REGION)
+>( ADD FIRST KEY 'REG_01' LOCATION $DATA01 NAME PARTITION_02,
+> ADD FIRST KEY 'REG_50' LOCATION $DATA02 NAME PARTITION_03
+>);
>>

When no storage key is defined and no primary key constraint is present, the storage key will be the
SYSKEY column. Partitioning of such a table is not possible, just like in SQL/MP.
The example shows the use of a partition name. SQL/MX tables have partition names that can be used for
a better identification of the partition. For example, a sequence number can be assigned such as is done in
this CREATE TABLE statement. The default partition name is the full file system name
(node_volume_subvolume_filename). When tables are restored to another location using BackupRestore2, the partition name will still refer to the original location of the object.


-4-

Partitioning key
The partitioning key is defined by the RANGE or HASH PARTITION BY clause when a table or index is
created. The partitioning key consists of one or more columns of the storage or clustering key. The order
of the partitioning columns may differ from that of the storage key. In this example, the ORDER_EXAMPLE
table has the rows stored in order of order number, part number and region. The table is partitioned by
region. This means that only within a partition, the rows are ordered by ordernum, partnum and region.
+>QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL )
+>STORE BY (ORDERNUM, PARTNUM, REGION)
+>);
>>

Primary key
The primary key is really a constraint that enforces uniqueness of the row in the table. SQL/MX uses the
primary key (when it is not droppable) as the base table index. In fact, it then becomes the storage key.
The example shows the three columns defined as the primary key. The STORE BY clause is optional and
used for documentation purposes. If the STORE BY clause is specified, it must be the same as or a prefix of
the primary key.
+>QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL
+>, PRIMARY KEY (ORDERNUM, PARTNUM, REGION) )
+>STORE BY PRIMARY KEY
+>);
>>

Droppable Primary Key constraints
SQL/MX tables support the droppable primary key constraint. A unique index on the primary key columns
will be created by the system. Note however, that this automatically created index is not automatically
partitioned. The base table requires a clustering key definition which defines the structure of the base
table index.
>>CREATE TABLE ORDER_HASH_EXAMPLE_DROPPABLE


-5-

+>, PRIMARY KEY (ORDERNUM, PARTNUM, REGION) DROPPABLE )
+>STORE BY (REGION)
+>HASH PARTITION BY (REGION)
+>( ADD LOCATION $DATA01 NAME PARTITION_02 ,
+> ADD LOCATION $DATA02 NAME PARTITION_03
+>);
>>

Hash partitioned tables
NonStop SQL/MX supports range partitioning and hash partitioning for tables and indexes. With range
partitioning, one uses a FIRST KEY clause to define key ranges for each partition. Each record is assigned
to the partition whose range includes the value of its partitioning key.
With hash partitioning, NonStop SQL uses a hash function on the values of the partitioning key (which can
be just a part of the clustering key) and each record is assigned to a partition based on the result.
>>CREATE TABLE ORDER_HASH_EXAMPLE
+>, PRIMARY KEY (ORDERNUM, PARTNUM, REGION) )
+>( ADD LOCATION $DATA01 NAME PARTITION_02,
+>);

Sequence Generators
Sequence generators (SG) are used to create unique numerical values
NonStop SQL/MX supports two types of sequence generators, Internal Sequence Generators, which are
used by columns defined as IDENTITY, and Sequences or External SGs.
Internal Sequence Generator
An Internal Sequence Generator is implicitly created when an IDENTITY column is defined in a CREATE
TABLE statement. The SG is a separate table with only one row associated only with that IDENTITY
column. The example shows how the ORDERNUM column is defined as an IDENTITY column. The
GENERATED ALWAYS AS IDENTITY clause tells the system to assign a unique value for each inserted row. A
GENERATED BY DEFAULT AS IDENTITY clause allows the application to supply a value when inserting a
row, but it can also let the system determine the value.
>>CREATE TABLE ORDER_SG_EXAMPLE
+>(ORDERNUM LARGEINT GENERATED ALWAYS AS IDENTITY
+>
(START WITH 1 INCREMENT BY 1 MINVALUE 1 NO CYCLE),


-6-

+>, PRIMARY KEY (ORDERNUM, PARTNUM, REGION ) )
+>( ADD LOCATION $DATA01 NAME PARTITION_02 ,
+>);
>>prepare x1 from insert into order_sg_example (partnum, region, unit_price,
qty_ordered) values ( ?,?,?,?);
--- SQL command prepared.
>>prepare x2 from insert into order_sg_example values (DEFAULT, ?,?,?,?);
--- SQL command prepared.

The two prepare statements demonstrate how an application can supply parameterized values. When no
column names are provided as in statement x2, the keyword DEFAULT can be used as a value for the
IDENTITY column.
Sequences
An external sequence generator is explicitly created using the CREATE SEQUENCE statement. The external
sequence generator is a schema level database object that the application uses to generate values for a
numeric column. The values generated by the external sequence generator are unique for that sequence
generator and can be used to create unique values across a set of tables in a schema.
The next example shows simple usage of a sequence. It is created as a SQL object; in the example all the
defaults are used. To get the next value of the sequence, one uses a SQL SELECT statement to select the
pseudo column NEXTVAL. The DUAL view 1is used to return only one value, and since this is the first call to
select NEXTVAL SQL/MX returns 1. This value remains the current value until another call to select nextval
is issued.
>>create sequence myseq;
>>select myseq.nextval from dual;
NEXTVAL
-------------------1
--- 1 row(s) selected.
>>select myseq.currval from dual;
CURRVAL
-------------------1
>>select myseq.nextval from dual;
1

The DUAL view is a view that has the same purpose as the DUAL pseudo table in other DBMSes. It returns one row.
The DUAL view and metadata views for SQL/MX are described in the paper called Concepts of NonStop SQL/MX,
Introduction to SQL/MX Metadata (part three in the series).


-7-


NEXTVAL
-------------------2

>>
References
SQL/MX reference manual: Chapter 2, CREATE TABLE Statement.
SQL/MX reference manual: Chapter 2, IDENTITY Columns and internal Sequence Generators.
SQL/MX reference manual: Chapter 6, Language Elements: KEYS.

DML variants that are allowed only on native tables
In the earlier releases, all SQL/MX DML could be executed on SQL/MP tables by the SQL/MX engine. With
releases newer than 3.0, some new DML statements have been introduced that will only work on native
tables. New, native-only DML features are: updatable primary keys, self referencing updates and multicommit delete. More native-only DML features may be added in future releases.

Updating the primary key value
With the release of SQL/MX 3.2, updates on the primary keys are allowed. Note however, that this update
is implemented as a delete of the row(s) followed by and an insert. The reason is simple: as a result of the
update of the primary key the row may have to move from one partition (on one volume) to another.

Self referencing updates
Self referencing updates are updating a table that is read within the same statement using a subquery.
Imagine a gaming application where you want existing rows to be copied to new rows:
>>insert into tx1 select identifier + 1000, user_name, 'New Game' from tx1;
--- 500 row(s) inserted.

This example shows how to clean up rows that are below an average.
>>delete from tx1 where u < (select avg(u) from tx1);
--- 100 row(s) deleted.

SQL/MX assures that rows are only processed once, to avoid getting into a loop as is the risk with the
INSERT statement where new rows with incremented identifiers are inserted at the end of the table.

Multi-commit delete
DELETE operations on very large tables can be very long running, locking many rows and may lead to lock
escalations or even worse, a TMF timeout that causes the delete to be backed out. The Multi-commit
option for the DELETE statement breaks up the delete into multiple smaller deletes. These deletes will be
performed by separate Executor Server Processes (ESP), one for each partition. Currently, the multiNative Tables in NonStop SQL/MX

-8-

commit option does not allow host variables in the WHERE clause. It is therefore more useful in a dynamic
SQL environment.
>>delete with multi commit every 200 rows from tx1 where u < 500;
--- 4 row(s) deleted.

References
SQL/MX reference manual: Chapter 2, DELETE Statement.

Data types
NonStop SQL/MX native tables support floating point data in IEEE format, which differs from the Tandemfloat format that is used in SQL/MP tables. Character Large Objects (CLOBS) and Binary Large Objects
(BLOBS) are supported by the JDBC drivers. The new data types are supported by the native tables.

Floating point data types
When SQL/MP tables are migrated to SQL/MX native tables, the floating point data columns need to be
examined for compatibility. SQL/MP uses a different format to implement floating point data than IEEE
which SQL/MX uses. For example, there is no equivalent for a Tandem REAL in the IEEE data type which
preserves the precision and exponent. The IEEE REAL uses less storage but supports a smaller exponent
size. Alternatively, for more exponent and precision, the column can be defined as FLOAT or DOUBLE
PRECISION.

Extended numeric precision
Columns defined as NUMERIC (precision, scale) can have a scale of maximum 128 digits, which is an
increase from 18 in older releases.

Unicode data type
SQL/MX native tables support ISO88591 for single-byte characters and UCS2 (Unicode). Unicode is a
universal encoded character set that lets you store data from any language using a single character set.
NonStop SQL/MX uses the UTF-16BE (16 bits, big endian) encoding for UCS2. The full range of UTF-16
characters is allowed, but surrogate pairs are not recognized as characters. (Surrogate pairs are used to
expand the code space of UTF-16 beyond the UCS-2 character encoding.)

Large Objects (CLOBs and BLOBs)
Native tables provide support for large binary and character objects, referred to as BLOBs and CLOBS.
Because the largest supported row size is approximately 32KB, an object that is larger than this maximum
cannot be stored in a single row. The JDBC drivers support these objects by inserting the objects into a
dedicated table in as many 32K chunks as necessary to store the object. The driver takes care of assigning
a key value for the object and to store the data into the table.
References
SQL/MX reference manual: Chapter 6, Language Elements: Data Types.
JDBC Type 2 Driver Programmer’s Reference: Chapter 4, Working with BLOB and CLOB Data

-9-

NonStop JDBC Type 4 Driver Programmer’s reference: Chapter 5, Working with BLOB and CLOB Data

Other functionality
This section describes the miscellaneous features of SQL/MX native tables. For example, the SQL/MX
metadata is stored in SQL/MX native tables; the FASTCOPY utility can efficiently copy native tables and
indexes and the GUARDIAN FUP program can show information about native tables using their native
(ANSI) names. Backup and restore of native tables must be done by Backup/Restore2 instead of
Backup/Restore.

Metadata tables
Users of SQL/MP with the SQL/MX engine use the SQL/MX metadata only to a small extent. This metadata
is often only used to map SQL/MP tables to SQL/MX aliases to allow the use of full ANSI names to access
the data stored in the MP tables. When native tables are used, all the information about them is stored in
highly normalized SQL tables. The layout of the tables is described in Chapter 10 of the SQL/MX reference
manual. The NonStop Advanced Technology Center provides a utility, mxschema , that creates a set of
convenience views on the metadata that allow a DBA to view information about a objects in a schema
using simple SQL queries instead of writing complex joins. The utility is downloadable from the HP
Software Depot free of charge, but note that it is provided “as-is”, without warranty of any kind.

Distributed databases
Native tables are accessed via the catalog they are registered in. When tables are distributed across
multiple NonStop servers, the catalog must be registered on each of the nodes using the REGISTER
CATALOG command to make the catalog visible to these nodes. If the catalog is not visible, a program
cannot get access to the data. This is a different approach from SQL/MP tables where remote data could
be accessed by their file names such as NODE.$DATA.SQL.TBL. SQL/MX tables will always be called by
their catalog.schema.table name regardless of the system they are located.
A user must be granted access via GRANT/REVOKE, but in order to access remote data, GUARDIAN
REMOTEPASSWORDS must be set up, just as is required to access SQL/MP remote data.

Stored Procedures
Stored procedures can access native tables as well as SQL/MP tables using the same API and the same
(JDBC T2) driver. However, the SPJ must be defined in the SQL/MX catalog.

Views
SQL/MX views can only refer to SQL/MX native tables. The SQL/MX view text is not limited to the 3,000
characters of SQL/MP.

Utilities
Many new utilities exist for SQL/MX and this paper will only touch upon a few of them that are important
to know about.
Fastcopy
Fastcopy is a command that is similar to SQL/MP LOAD or COPY. It can copy data from one large table to
another faster than a normal insert/select. The most important things to know about fastcopy are:

-10-











Fastcopy performs the functionality of INSERT INTO <target_table> (*) SELECT * FROM
<source_table>. This means that the source and target tables must be similar, have the same
number of columns, compatible data types and so on.
The target table will be made non-audited for the duration of the command, to allow fast inserts.
Due to the initialization, fastcopy of small tables will take longer than a normal insert-select.
The command can be executed by user programs using dynamic SQL.
Indexes, when defined on the target and online, are copied in a second step the same way the
table was copied, in the order of the alternate key. This is more efficient than to create the index
separately.
The source table will be accessible for read-only operation while fastcopy executes.
The syntax allows for separate commands to copy a table and index. Fastcopy will detect if any
updates on the source have been applied and will not release the targets unless it completed
successfully.
System generated key values, such as SYSKEYS and IDENTITY columns are copied to the target.
The target table and indexes will be compacted as if a reload had been performed.

FUP
Fup has been enhanced over the years to provide some support for SQL/MX native tables. However,
SQL/MP users may not be fully aware of the power of the ANSI name support in FUP. The next paragraphs
highlight some features of the INFO and LISTLOCKS commands.
Enhanced INFO command
FUP INFO requested on a table by its ASNI name shows information on all the partitions of the object
instead of just a single Guardian file. The next example shows the information of the ORDER_EXAMPLE
table. It has three partitions, one on $DATA02 and two on $DATA01. One info command shows the
information of all partitions.
-info 'table frans.perf.order_example'
CODE
EOF
LAST MODIF
OWNER RWEP
TYPE
$DATA01.ZSDFJ001
DK4BGG00
550A+
12288
7:25 211,96 *SQL XPK Ta
VT1BGG00
550A+
0 4Sep2013 6:44 211,96 *SQL PK Ta
$DATA02.ZSDFJ001
L54BGG00
550A+

12288

7:32 211,96

*SQL XPK Ta

REC BL
28
28

4
4

28

4

Enhanced LISTLOCKS command
This example shows the locks placed on the ORDER_DETAIL table when a transaction has deleted one
row, but has not committed yet. The table has three partitions and every partition has an “Intent-lock”
placed on it. The actual row lock is in the partition on $DATA02.
-listlocks

'table frans.perf.order_example'

$DATA01.ZSDFJ001.DK4BGG00
ANSI NAME FRANS.PERF.ORDER_EXAMPLE
LOCK
REQUESTER
TYPE STATE ID
F
GI
NSKIT10(2).1.168356
$DATA01.ZSDFJ001.VT1BGG00
LOCK
REQUESTER
TYPE STATE ID


KEY
LEN

KEY/RECORD ADDRESS

KEY
LEN

KEY/RECORD ADDRESS

-11-

F

GI

NSKIT10(2).1.168356

$DATA02.ZSDFJ001.L54BGG00
LOCK
REQUESTER
KEY
TYPE STATE ID
LEN KEY/RECORD ADDRESS
R
G
NSKIT10(2).1.168356
16 ?0 ?0 ?0 ?4 ?0 ?1 "REG_50
"
R
G
NSKIT10(2).1.168356
16 ?255 ?255 ?255 ?255 ?255
?255 ?255 ?255 ?255 ?255 ?255 ?255 ?255 ?255 ?255 ?255
F
GI
NSKIT10(2).1.168356
-

The LISTLOCKS command can also be used on the schema level by specifying the SCHEMA keyword
instead TABLE.
Warning: The amount of locks displayed may be high on production systems.
Backup and Restore using BR2
SQL/MX objects must be backed up and restored using new utilities. Backup/Restore2 is used to backup
and restore SQL/MX native tables and OSS files to tape.
Customers may already use PAK and UNPAK utilities to backup and restore Guardian objects to disk. PAK2
and UNPAK2 are available but not yet distributed the same way as PAK.PAK2/UNPAK2 are best executed
from the OSS environment and they use the OSS file system, however, a Guardian non-native (code 100)
version exists.
References
SQL/MX reference manual: Chapter 2, FASTCOPY
SQL/MX reference manual: Chapter 10, Metadata Tables
SQL/MX Guide to Stored Procedures in Java
Backup and Restore 2 Manual
Concepts of NonStop SQL/MX, Introduction to SQL/MX Metadata: HP Document ID: 4AA3-6539ENW
Concepts of NonStop SQL/MX, Introduction to SQL/MX Stored Procedures: HP document ID: 4AA4-9428ENW
PAK2/UNPAK2 information: http://193.65.99.19/kku/pak/pak4.html

Conclusion
Many SQL/MX customers are happy with the functionality that the SQL/MX engine provides them
accessing their SQL/MP data. However, today’s new applications often require more functionality that only
native tables provide. This paper has tried to provide an overview of all the features that native tables
support, and the list may be a lot longer than most SQL/MP customers expect. I was surprised to write this
many pages worth of differences!
Today’s applications maintain more data, which results in storing larger rows; they use “meaningless”
keys, generated by the system as –for example- sequences and they assume the DBMS to maintain
Referential Integrity. All of this is supported by SQL/MX native tables. SQL/MX security is enhanced by the
introduction of the Security Administrator role: the SA can manage the security of database objects

-12-

without having access to the data itself. Note that SQL/MX native tables require different backup and
restore procedures than SQL/MP data. Backups of native tables require the Backup/Restore2 product and
can be backed up to disk using the pak2/unpak2 utilities.


-13-

Native tables in NonStop SQL/MX

More Related Content

What's hot

Similar to Native tables in NonStop SQL/MX

Recently uploaded

Native tables in NonStop SQL/MX