SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-4
Ability to Query SQL/MX Database Objects
For more information, see the SQL/MX Programming Manual for C and COBOL.
Stored Procedures in Java
NonStop SQL/MX supports stored procedures written in the Java programming
language. The stored procedures are contained in a Java class, registered in SQL/MX
system metadata tables, and invoked by NonStop SQL/MX when an application issues
a CALL statement. Stored procedures extend the functionality of the database and
enable you to share business logic among different applications without duplicating
code.
For more information, see the
SQL/MX Guide to Stored Procedures in Java.
Ability to Query SQL/MX Database Objects
SQL/MX applications can still query SQL/MP database objects. An advantage of an
SQL/MX application is that it can query both existing SQL/MP database objects and
new SQL/MX database objects. That way, you can gradually migrate your SQL/MP
database to NonStop SQL/MX while you develop new ANSI-compliant SQL/MX
database objects. For more information, see Section 11, Converting an SQL/MP
Database to SQL/MX Tables.
Redesigned Optimizer and Executor
NonStop SQL/MX uses a redesigned optimizer and executor. The top-down, cost-
based, rules-driven optimizer considers more plans than NonStop SQL/MP.
Extensibility is provided through the compiler because you can use rules to easily add
new transformations to the optimizer.
The redesigned executor also provides extensibility that uses a data-flow architecture.
Queries that are complex and that have large result sets perform better in the data-flow
architecture of NonStop SQL/MX than in NonStop SQL/MP. The executor takes
advantage internally of completely no-waited operations and provides for more work in
the Data Access Manager (DAM), including multiple forms of parallelism and scalability
on multiple levels.
In addition to considering many more plans, the SQL/MX optimizer allows you to push
down certain plans that involve compound statements or nested joins into the Data
Access Manager. You can influence a query plan by rewriting portions of a plan with
the CONTROL QUERY SHAPE command. NonStop SQL/MX provides histogram
statistics that provide more accurate statistics of the data in the database. The
optimizer also uses these statistics to create efficient query plans.
For more information about optimizer features, see the
SQL/MX Comparison Guide for
SQL/MP Users and the SQL/MX Query Guide.
Note. NonStop SQL/MX does not support stored procedure result sets, which might prevent
some stored procedures from being ported to NonStop SQL/MX.