HP NonStop SQL/MX Release 2.3.3

The most recent NonStop SQL Release 2.3.3 delivers a feature rich, high quality, and stable product including many new features and defects repaired required by our customers. SQL/MX 2.3.3 is Generally Available as of August 2009.

SQL/MX 2.3.3 features include:

• 32k blocks for SQL/MX tables
  • Previous releases of SQL/MX support only 4kb physical data (disk) block size for the database objects.
    
    With support for 32k blocks being made available in 2.3.3, it allows customers to create tables on physical disk blocks of size 32KB, instead of the default 4 KB data block size. Increasing the data block size allows packing more records in each disk block resulting in improved performance of sequential reading of multiple records. However for random access where only one or a few records are accessed at a time, larger block size may not be beneficial.


• Backup restore (BR2) enhancements
  • Customers that have large sized databases and who wish to have finer granularity in the way tables are currently backed up would benefit from this enhancement in B/R2. As of now B/R 2 treats tables as a single unit, even though tables have partitions and indexes. Indexes consist of one or more index partitions. With the introduction of NonStop SQL R2.3.3, B/R 2 adds ability to backup (and restore) indexes and index partitions similar to the already supported capability of backup and restore of tables and table partitions.


• Multi union
  • In large data warehouse type applications it is common practice to split a data table into multiple tables where they logically belong to the same data set. These data sets are unified by using a view that is a union of all the tables. One of the commonly used methods is to organize data based on date range or regions or country, etc. If the data set is broken into a large number of tables, say 52 tables where each table represents a week worth of data, it causes resource constraints and performance problems.
    
    Prior to R2.3.3, the SQL/MX compiler runs out of virtual memory while compiling a large number of unions. Current union operator in SQL/MX can support only two children and as a result the large union has to be represented as the backbone of unions. This increases the complexity of optimization and plan selection.
    
    Multi union is a new union operator that supports “n” number of child nodes for union. This reduces the complexity of optimization and pressure on memory. This operator also helps in improving the run-time performance as the number of levels in the tree is reduced.


• Constraints based pruning
  • In large data warehouse type applications it is common practice to split a data table into multiple tables where they logically belong to the same data set. These data sets are unified by using a view that is a union of all the tables. One of the commonly used methods is to organize data based on date range or regions or country, etc. If the queries supplied on these tables have clear time windows (temporally bound queries) that contain data from only a subset of tables, then running these queries will result in over utilization of system resources and poor performance.
    
    Eliminating the scan of tables from the query plans that do not contain the data by taking into consideration selection predicates and the constraints defined on the table improves the resource utilization and compile and run time performance.


• Fast copy tool
  • Allow fast copy of partitioned tables/indexes.
  • Leverage sidetree inserts to make the operation faster.
  • Parallelize copy of table and indexes to improve throughput.


• Plan quality improvements 2
  • Implement OR rule with N-Way union
  • Push sort nodes below join
  • Remove random file scans with OR rule


• JDBC and ODBC performance improvement with module file caching.
  • Module File Caching (MFC) is a means to share prepared statement plans saved as static module files across MXOSRVR/Database Connections. MFC helps in reducing the SQL compiles during the application’s steady state thereby reducing cpu and memory resource consumption thus improving compile performance


• Improved quality and reliability
  • At least 80% code coverage for existing code and 90% for added code.
  • Removal of all critical and major defects uncovered by static analysis
  • Automated regression test libraries
  • Improved QA test libraries.