HP NonStop SQL/MX Comparison Guide for SQL/MP Users Abstract This guide describes SQL differences between HP NonStop™ SQL/MP and NonStop SQL/MX. Product Version NonStop SQL/MX Releases 2.0 and 2.1 Supported Release Version Updates (RVUs) This publication supports G06.23 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
Document History Part Number Product Version Published 429827-001 NonStop SQL/MX Release 1.0 March 2001 429857-001 NonStop SQL/MX Release 1.8 December 2002 523735-001 NonStop SQL/MX Release 2.0 June 2004 523735-002 NonStop SQL/MX Release 2.0 August 2004 523735-003 NonStop SQL/MX Release 2.
HP NonStop SQL/MX Comparison Guide for SQL/MP Users Index Figures What’s New in This Manual v Manual Information v New and Changed Information Tables v About This Guide vii Audience vii Related Documentation vii Notation Conventions x 1.
. Embedded SQL Contents Identifiers (continued) Date-time Literals 3-6 Interval Literals 3-8 Expressions and Functions 3-8 Numeric Value Expressions and Functions 3-9 Datetime Functions 3-9 Interval Value Expressions 3-10 Mathematical Functions 3-10 Sequence Functions 3-10 Aggregate Functions 3-11 Predicates 3-11 LIKE Predicate 3-11 Sort Operations 3-12 SQL DML Statements 3-12 Nonaudited Tables 3-13 INSERT Statement 3-14 SELECT Statement 3-15 UPDATE Statement 3-16 Access Options and Isolation Levels 3-16
. Optimizer and Executor Contents Embedded SQL Statements 4-4 EXECUTE Statement 4-5 Updatable Cursors 4-6 FETCH Statement 4-6 Positioned UPDATE Statement 4-6 Dynamic SQL 4-6 Descriptor Area 4-7 Error Handling 4-8 Format of Error Codes 4-8 WHENEVER Declarative 4-9 Diagnostics Area 4-9 Statistics Area 4-10 Program Development 4-10 SQL/MP Compiler 4-10 SQL/MX Compiler 4-11 Stored Procedures in Java 4-13 SQL/MP Stored Procedures 4-13 SQL/MX Stored Procedures 4-13 Similarity Checks and Automatic Recompilation
7. Utility Differences Contents Tables 6-4 Clustering Keys 6-4 Table Attributes 6-4 File Types 6-4 Constraints 6-4 Indexes 6-5 Partitions 6-5 Views 6-6 SQL/MX Views 6-6 SQL/MP Views 6-6 Object Security 6-6 7. Utility Differences DISPLAY USE OF Command 7-1 IMPORT Command 7-1 MXTOOL Utility 7-1 Partition Management: The MODIFY Utility POPULATE INDEX Command 7-2 SHOWDDL Command 7-2 SHOWLABEL Command 7-3 7-2 Index Figures Figure 4-1. Figure 4-2.
What’s New in This Manual Manual Information HP NonStop SQL/MX Comparison Guide for SQL/MP Users Abstract This guide describes SQL differences between HP NonStop™ SQL/MP and NonStop SQL/MX. Product Version NonStop SQL/MX Releases 2.0 and 2.1 Supported Release Version Updates (RVUs) This publication supports G06.23 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
What’s New in This Manual New and Changed Information HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003 vi
About This Guide This guide describes SQL differences between NonStop SQL/MP and NonStop SQL/MX. This release of NonStop SQL/MX enables applications to use the SQL/MX highperformance database compiler and executor to access SQL/MP and SQL/MX databases. You access tables, views, indexes, and catalogs by using SQL/MX Data Manipulation Language (DML) statements.
Related Documentation About This Guide SQL/MX Messages Manual Describes SQL/MX messages. SQL/MX Glossary Defines SQL/MX terminology. Programming Manuals SQL/MX Programming Manual for C and COBOL Describes how to embed SQL/MX statements in ANSI C and COBOL programs. SQL/MX Programming Manual for Java Describes how to embed SQL/MX statements in Java programs according to the SQLJ standard.
Related Documentation About This Guide Glossary Help Terms and definitions from the SQL/MX Glossary. NSM/web Help Context-sensitive help topics that describe how to use the NSM/web management tool. Visual Query Planner Help Context-sensitive help topics that describe how to use the Visual Query Planner graphical user interface. The NSM/web and Visual Query Planner help systems are accessible from their respective applications.
Notation Conventions About This Guide This figure shows the manuals in the SQL/MX library: Programming Manuals Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Programming Manual for C and COBOL SQL/MX Quick Start SQL/MX Programming Manual for Java SQL/MX Guide to Stored Procedures in Java Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual SQL/MX Glossary SQL/MX Queuing and Publish/ Subscribe Services SQL/MX Query Guide SQL/MX Report Writer Guide DataLoader/M
General Syntax Notation About This Guide General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: MAXATTACH lowercase italic letters. Lowercase italic letters indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name computer type.
General Syntax Notation About This Guide | Vertical Line. A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. For example: INSPECT { OFF | ON | SAVEABEND } … Ellipsis. An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times.
1 Comparing the Differences in the Products NonStop SQL/MX is HP’s next-generation relational database management system designed for business-critical applications. SQL/MX software brings traditional NonStop fundamentals—high availability, scalability, reliability, and parallel processing—to a distributed database. NonStop SQL/MP is HP’s relational database management system that was first introduced in 1987. Since then, many new features and performance enhancements have been added.
Comparing the Differences in the Products Frequently Asked Questions About Differences Frequently Asked Questions About Differences The following list of questions about the interaction between NonStop SQL/MP and NonStop SQL/MX was generated from customer response at a recent user conference. • Will customers be forced to migrate applications from NonStop SQL/MP to NonStop SQL/MX? No.
Comparing the Differences in the Products Frequently Asked Questions About Differences Yes, but DEFINEs for table naming are not necessary in NonStop SQL/MX. DEFINES can be used only for SQL/MP objects referenced through the SQL/MX application. SQL/MX objects cannot be referenced with DEFINES. In addition, DEFINEs must be available to NonStop SQL/MX at run time, or you will receive an error. NonStop SQL/MP does not have this requirement.
Comparing the Differences in the Products ° ° What’s New in NonStop SQL/MX? #XFILENAMES #XFILES In general, the commands work the same and return the same information for SQL/MX objects as they do for non-SQL/MX objects. Exceptions are noted in the TACL Reference Manual.
Comparing the Differences in the Products Manageability the ability to divide the data to be processed into partitions and work on each partition in parallel. In a partitioned parallel plan, multiple operators all work on the same plans, and results are merged by using multiple pipelines. Although NonStop SQL/MP uses some partitioned parallelism to process in parallel, NonStop SQL/MX has been designed to take advantage of both executor server process (ESP) parallelism and Data Access Manager parallelism.
Comparing the Differences in the Products Other Features Other Features Many other features have been added to NonStop SQL/MX that are not discussed in this guide. Some of those features include: • • • Data mining Publish and subscribe Compound statements and rowsets For additional information regarding these topics, see the SQL/MX Data Mining Guide, the SQL/MX Queuing and Publish/Subscribe Services, and the SQL/MX Programming Guide for C and COBOL.
2 Quick Reference to Differences Table 2-1 lists the content of NonStop SQL/MP as found in the SQL/MP Reference Manual, the SQL/MX Reference Manual, and other manuals. The column Implemented In NonStop SQL/MX? indicates whether the item is supported in NonStop SQL/MX (yes, no, or changed.) “Changed” means that the item is supported differently in NonStop SQL/MX than it was in NonStop SQL/MP. NonStop SQL/MX supports Report Writer. In the table, Report Writer commands are identified in the Comments column.
Quick Reference to Differences Table 2-1.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 3 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? CATALOGS table Changed Metadata tables are implemented differently.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 4 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? Comments Concurrency Yes See SQL/MX Reference Manual Constraints Yes In CREATE TABLE and ALTER TABLE statements. See SQL/MX Reference Manual CONSTRNT table Changed Metadata tables are implemented differently.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 5 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? Comments CREATE CONSTRAINT statement Changed Performed with CREATE TABLE statement. See SQL/MX Reference Manual CREATE INDEX statement Yes Some differences.
Quick Reference to Differences Table 2-1.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 7 of 18) Implemented in NonStop SQL/MX? Comments DOWNGRADE SYSTEM CATALOG command No See SQL/MP Reference Manual DROP statement Changed Replaced by specific DROP object statements. See SQL/MX Reference Manual DROP SYSTEM CATALOG statement Changed Replaced by DROP SQL statement.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 8 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? EXTEND function No EXTENT file attribute Yes See SQL/MX Reference Manual FC command Yes See SQL/MX Reference Manual FETCH statement Yes See SQL/MX Reference Manual File attributes Changed See SQL/MP Reference Manual File organization Changed See SQL/MP Reference Manual FILEINFO command Changed Replaced SHOWLABEL utility.
Quick Reference to Differences Table 2-1.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 10 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? INSERT statement Yes See SQL/MX Reference Manual and SQL/MX Programming Guide for C and COBOL INTERVAL data type Yes See SQL/MX Reference Manual INTERVAL literal Yes See SQL/MX Reference Manual INVOKE directive and command Changed Some functions replaced by SQL/MX SHOWDDL command.
Quick Reference to Differences Table 2-1.
Quick Reference to Differences Table 2-1.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 13 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? Program invalidation No PROGRAMS table Changed Metadata tables are implemented differently.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 14 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? SAVE command No Search conditions Yes See SQL/MX Reference Manual SECURE command No Replaced by GRANT and REVOKE statements. See SQL/MX Reference Manual SECURE file attribute No Security Yes See SQL/MP Reference Manual SELECT statement Yes Some differences.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 15 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? Similarity checks Changed SLACK file attribute No SMF No SQL/MX objects cannot be on SMF-managed volumes.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 16 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? Statistics Changed See Section 5, Optimizer and Executor and SQL/MX Reference Manual HP NonStop Storage Management Foundation (SMF) No SQL/MX objects cannot be on SMF-managed volumes.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 17 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? TIME data type Yes See Section 3, DML Features and SQL/MX Reference Manual TIMESTAMP data type Yes See Section 3, DML Features and SQL/MX Reference Manual TMF transactions Yes See Transaction Management Facility (TMF) Introduction TOTAL command No TRANSID table Changed Metadata tables are implemented differently.
Quick Reference to Differences Table 2-1. NonStop SQL/MP Contents Compared to NonStop SQL/MX (page 18 of 18) NonStop SQL/MP Item Implemented in NonStop SQL/MX? Versions No VERSIONS table Changed Metadata tables are implemented differently. See SQL/MX Reference Manual Views Changed No protection or shorthand views. See Views on page 6-6. VIEWS table Changed Metadata tables are implemented differently.
3 DML Features NonStop SQL/MX contains many SQL/MP extensions to the ANSI standard to enable users to make a smooth transition to NonStop SQL/MX. See “Using NonStop SQL/MX to Access SQL/MP Databases” in the SQL/MX Reference Manual if you have questions about how to use SQL/MP tables with the SQL/MX compiler and executor. Also, see the specific information you need to use date-time data types and literals, INTERVAL data types and literals, and CHARACTER string data types and literals.
Interval Data Type DML Features SELECT MPDateTimeCol FROM MPTable; MPDateTimeCol ------------... 03-12 ... Selecting FRACTION-Only DATETIME Columns If you attempt to select data from a FRACTION-only DATETIME column, the value is returned as a string of '#' characters with the same display length as the length of the column. Suppose that an SQL/MP table has a DATETIME column defined as: MPDateTimeCol DATETIME FRACTION(6) DEFAULT DATETIME '123456' FRACTION(6) You cannot select the data from this column.
Selecting FRACTION-Only Interval Columns DML Features This table lists the mapping between the interval data types used in NonStop SQL/MP and those used in NonStop SQL/MX: NonStop SQL/MP Interval Type Mapping in NonStop SQL/MX INTERVAL DAY(n) TO SECOND INTERVAL DAY(n) TO SECOND(0) INTERVAL DAY(n) TO FRACTION(f) INTERVAL DAY(n) TO SECOND(f) INTERVAL HOUR(n) TO SECOND INTERVAL HOUR(n) TO SECOND(0) INTERVAL HOUR(n) TO FRACTION(f) INTERVAL HOUR(n) TO SECOND(f) INTERVAL MINUTE(n) TO SECOND INTERVAL
Floating-Point Data Types DML Features NonStop SQL/MX returns a warning indicating that you selected an unsupported data type with undefined contents. If you try to use the unsupported data type in any sort of expression, NonStop SQL/MX returns an error. Floating-Point Data Types NonStop SQL/MP supports floating-point data types of FLOAT, REAL, and DOUBLE PRECISION.
Identifiers DML Features Identifiers NonStop SQL/MP supports only regular identifiers, formed from alphanumeric characters and the underscore character. Regular identifiers are not case sensitive. In NonStop SQL/MP, an SQL identifier can contain up to 30 letters (A through Z or a through z), digits (0 through 9), or underscore (_) characters. The first character must be a letter. NonStop SQL/MX supports both regular and delimited identifiers. An identifier of either type can contain up to 128 characters.
Date-time Literals DML Features Date-time Literals In NonStop SQL/MP, a date-time literal is a DATETIME, DATE, TIME, or TIMESTAMP literal that you can use in an expression and which can appear in default, USA, or European format. In NonStop SQL/MX, a date-time literal is a DATE, TIME, or TIMESTAMP literal. An SQL/MX date-time literal begins with the DATE, TIME, or TIMESTAMP keyword and can appear in default, USA, or European format.
Date-time Literals DML Features Table 3-1.
Interval Literals DML Features Table 3-1. Support For NonStop SQL/MP DATETIME Literals in NonStop SQL/MX (page 3 of 3) NonStop SQL/MP DATETIME Type Supported in NonStop SQL/MX INTERVAL start to FRACTION(n) Yes INTERVAL start to SECOND(n) All other INTERVAL start-end combinations Yes Same as in NonStop SQL/MP Maps To ANSI Type *Limited support as dummy values in SELECT statements only Interval Literals As in NonStop SQL/MP, an SQL/MX interval literal is either year-month or day-time.
Numeric Value Expressions and Functions DML Features Table 3-2. NonStop SQL/MP and NonStop SQL/MX Functions NonStop SQL/MP Function Implemented in NonStop SQL/MX? COMPUTE_TIMESTAMP No CURRENT Changed In NonStop SQL/MX, supported to return a range of date-time values only in NonStop SQL/MP stored text. Otherwise, in NonStop SQL/MX, same as CURRENT_TIMESTAMP. See mapping under Datetime Functions. EXTEND Changed In NonStop SQL/MX, supported only for use in NonStop SQL/MP stored text.
Interval Value Expressions DML Features Table 3-3.
Aggregate Functions DML Features Aggregate Functions In a few cases, NonStop SQL/MP allows a subquery within an aggregate in a predicate. NonStop SQL/MX follows the ANSI standard and does not allow any subqueries in an aggregate. In order to comply with ANSI standards, NonStop SQL/MX does not move aggregate predicates from the WHERE clause to a HAVING clause and does not move nonaggregate predicates from the HAVING clause to the WHERE clause, as NonStop SQL/MP does.
Sort Operations DML Features Rewritten by NonStop SQL/MX column1 LIKE SUBSTRING(?expr2 FROM 1 FOR (POSITION (?trm in ?expr2) - 1)) column1 LIKE SUBSTRNG(?expr2 FROM 1 FOR (POSITION(?trm IN ?expr2) - 1)) ESCAPE ?esc Sort Operations NonStop SQL/MP uses the FastSort sort/merge program for certain operations. These operations are described in the SQL/MP Installation and Management Guide. You use the EXPLAIN utility to determine if a sort occurs.
Nonaudited Tables DML Features Table 3-4. SQL/MX and SQL/MP DML Statements SQL/MP DML Statements Implemented in NonStop SQL/MX? INSERT Changed In NonStop SQL/MX, an ORDER BY clause can be used following the specification of the insert source. Also, multiple row-value constructors can be used following the VALUES keyword. See INSERT Statement on page 3-14. SELECT Changed In NonStop SQL/MX, an SQL value expression can be used in a select list.
INSERT Statement DML Features disabled. See the Defaults Table entry in the SQL/MX Reference Manual for further information. INSERT Statement RETURNING LASTSYSKEY Clause NonStop SQL/MP supports the RETURNING LASTSYSKEY clause for the INSERT statement. NonStop SQL/MX does not support this option. Insert Rows in Order NonStop SQL/MX allows you to insert rows in order with an ORDER BY clause on the INSERT statement.
SELECT Statement DML Features SELECT Statement Table Reference Within FROM Clause In NonStop SQL/MP, a table reference within a FROM clause of a SELECT statement can be a table, view, or joined table. The maximum number of tables that can be specified in a FROM clause is 16. This maximum includes the underlying base tables of views. In NonStop SQL/MX, a table reference can also be a query expression or, stated more precisely, the derived table determined by the evaluation of a query expression.
UPDATE Statement DML Features UPDATE Statement Source Value for a Column Referenced in the SET Clause In NonStop SQL/MX, the source value for a column referenced in the SET clause of an UPDATE statement is an SQL value expression. In particular, a value expression can be a scalar subquery: UPDATE JOB SET jobdesc=(SELECT jobdesc from JOB1 WHERE jobcode=2000) WHERE jobcode=2000; In NonStop SQL/MP, the expression cannot be a subquery.
STABLE Access DML Features • • STABLE (NonStop SQL/MP) and STABLE (NonStop SQL/MX) have the same implementation. This option is available only for the SELECT statement. REPEATABLE (NonStop SQL/MP and NonStop SQL/MX) and SERIALIZABLE (NonStop SQL/MX) have the same implementation; this option holds locks on data until the end of the transaction. Note.
Transaction Management DML Features STABLE Access and EXCLUSIVE MODE In both NonStop SQL/MX and NonStop SQL/MP, if you use STABLE access with EXCLUSIVE MODE, you do not get an error, because the next transaction must wait for access to the row. Transaction Management A TMF transaction is the basic recoverable unit in case of a failure or transaction interruption. TMF transactions can be defined during an MXCI session or in a host program. The typical order of events is: 1. Transaction is started. 2.
SET TRANSACTION Statement DML Features Even if a transaction is initiated implicitly, you must end a transaction explicitly with the COMMIT WORK statement or the ROLLBACK WORK statement so that either the entire SQL statement executes or none of it is does. Auto-Abort NonStop SQL/MX Release 1.8 automatically aborted transactions if an error occurred while performing a SQL DELETE, INSERT, UPDATE, or DDL statement. By default, NonStop SQL/MX Release 2.
SET TRANSACTION Statement DML Features TMF or MXCS, or inherited from other processes. NonStop SQL/MP provides a similar SQLCI command, called AUTOWORK. See Cursor Operations (Embedded SQL) on page 3-21 for information about using the AUTOCOMMIT option with cursors. The next statement sets the AUTOCOMMIT option to ON: SET TRANSACTION AUTOCOMMIT ON; If this option is set to ON, at the end of each statement execution, NonStop SQL/MX automatically commits changes made to the database.
CONTROL TABLE Directive DML Features BEGIN WORK; --Explicit transaction started UPDATE ... UPDATE ... COMMIT WORK; --AUTOCOMMIT OFF --Explicit transaction committed --AUTOCOMMIT STILL ON INSERT ...; INSERT ...; . . .
CONTROL TABLE Directive DML Features Table 3-5. CONTROL TABLE Directives SQL/MP CONTROL TABLE Directives Implemented in NonStop SQL/MX? ACCESS PATH Changed Access paths can be forced using CONTROL QUERY SHAPE. See Forcing Query Execution Plans on page 5-8. JOIN METHOD Changed Join methods can be forced using CONTROL QUERY SHAPE. See Forcing Query Execution Plans on page 5-8. JOIN SEQUENCE Changed Join sequence can be defined with a CONTROL QUERY DEFAULT setting, JOIN_ORDER_BY_USER.
CONTROL QUERY Directive DML Features CONTROL QUERY Directive In NonStop SQL/MP, the CONTROL QUERY compiler directive controls plans for queries. Options specify whether to resolve names at execution time or startup time, whether to include hash join algorithms considered for executing queries, and whether to optimize query response time for returning a few rows or all rows. In NonStop SQL/MX, the CONTROL QUERY directive has been changed to CONTROL QUERY DEFAULT. Many options have been changed.
Query Caching DML Features Query Caching In NonStop SQL/MP, you control statement and query caching with MXCS and SQL/MP configuration attributes, system defaults, and control statements. These settings persist until a user changes a Guardian user ID, a server is reinitialized, or a rollback condition occurs. Caching takes place at a high level. In NonStop SQL/MX, caching is controlled by control query default statements.
Query Caching DML Features Feature NonStop SQL/MP NonStop SQL/MX When caching change takes effect On next system configuration refresh. At next query compile. Ownership of object Cache per ODBC/MP server. Cache per MXCMP process. When cache is flushed When Guardian user ID changes. CONTROL statements only affect the next statement compilation, which is avoided when a statement is used from cache. Cache is not preserved over critical condition termination.
Query Caching DML Features Feature NonStop SQL/MP NonStop SQL/MX Cache counters Number of statements, lookups, hits, drops. Extended to indicate whether statement was not cached because it is parallel, and parallel was not activated.
Query Type Comparison DML Features Query Type Comparison Simple data manipulation language (insert, update, delete, select) statements without predicates, or with key equality predicates that are guaranteed to affect at most one row, are cached in both NonStop SQL/MP and NonStop SQL/MX. An equality predicate is cachable if one of the operands is a literal and the other operand is a fully specified clustering (or primary) key.
Query Type Comparison DML Features Table 3-6.
Versioning DML Features Table 3-6.
Versioning DML Features HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003 3- 30
4 Embedded SQL As in NonStop SQL/MP, to access an SQL/MX database, you can execute SQL statements interactively by using MXCI (the NonStop SQL/MX equivalent to SQLCI) or programmatically by embedding, or including, SQL statements in a host program. In NonStop SQL/MX, you can write the host programs by using embedded SQL in the ANSI C, C++, COBOL, or Java programming languages.
Variable-Length Character Data in Embedded SQL C Programs Embedded SQL SQL/MP Example This C program fragment uses SETSCALE with an INSERT statement to create a new row with the value 98.34 in the PARTS.PRICE column after storing the value in host variable unit_price. The value is multiplied by 100 for storing as a whole number.
INVOKE Directive Embedded SQL INVOKE Directive In NonStop SQL/MP, use the INVOKE directive interactively through SQLCI to create host variable declarations in an EDIT file. The EDIT file can then be included in an embedded SQL program. In NonStop SQL/MX, use the INVOKE directive to create host variable declarations only directly in an embedded SQL program.
Embedded SQL Statements Embedded SQL For example, suppose the columns PARTDESC and QTY_AVAILABLE in the PARTS table allow null. After the SELECT statement executes, this C example tests the indicator variable for null or a truncated value: EXEC SQL SELECT partnum, partdesc, price, qty_available INTO :parts_rec.partnum, :parts_rec.partdesc INDICATOR :parts_rec.partdesc_i, :parts_rec.price, :parts_rec.qty_available INDICATOR :parts_rec.qty_available_i, FROM sales.
EXECUTE Statement Embedded SQL Table 4-1. Embedded SQL Statements SQL/MP Embedded SQL Statements and Directives Implemented in NonStop SQL/MX? FREE RESOURCES No INCLUDE STRUCTURES No See Descriptor Area, Diagnostics Area, Statistics Area. INCLUDE SQLCA No See Diagnostics Area. INCLUDE SQLDA No See Descriptor Area. INCLUDE SQLSA No See Statistics Area. INSERT Changed No RETURNING clause in NonStop SQL/MX. See INSERT Statement on page 3-14.
Updatable Cursors Embedded SQL UNLOCK command or exit the MXCI session. You can also use standalone UPDATE, INSERT or DELETE statements instead of using PREPARE and EXECUTE. Only SQL/MP tables can be non-audited. Updatable Cursors In NonStop SQL/MP, delete operations using a cursor do not require the FOR UPDATE OF clause of the DECLARE CURSOR statement; you must use this clause when you update rows, but it is optional when you delete rows.
Descriptor Area Embedded SQL NonStop SQL/MX provides for the allocation of a descriptor area with the use of SQL:1999 dynamic SQL statements. This descriptor area consists of multiple item descriptor areas, together with a count of the number of those item descriptor areas. For further information about using the descriptor area, see Descriptor Area on page 4-7. The next list summarizes the dynamic SQL statements that you use with an SQL/MX descriptor area.
Error Handling Embedded SQL DESCRIPTOR statements. After the parameters have been described (by using the DESCRIBE statement as in NonStop SQL/MP), you can modify and retrieve information from the descriptor areas only by using the SET DESCRIPTOR and GET DESCRIPTOR statements, respectively. If you have dynamic input parameters in a prepared SQL statement, you must code the appropriate 3GL statements and use SET DESCRIPTOR to set the values in the descriptor area.
WHENEVER Declarative Embedded SQL SQL_PARTITION_NOT_AVAIL -8239 * * SQL_FILE_SYSTEM_ERROR -8300 -8551 - SQL_NO_INDICATOR_VAR -8423 -8420 22002 SQL_LOST_OPEN_WARN 8204 8574 - SQL_LOST_OPEN_ERROR -8204 -8574 - * Not available. The current release of NonStop SQL/MX does not support the SQL/MP CONTROL TABLE SKIP UNAVAILABLE PARTITION option.
Statistics Area Embedded SQL Suppose your application has executed an SQL statement and the WHENEVER statement reports an error. To find out more about the results of the executed statements, code your application to execute a procedure that uses GET DIAGNOSTICS: For example, in a C program, the code might look like this: /* First, get the count of the number of exception conditions. */ EXEC SQL GET DIAGNOSTICS :hv_num = NUMBER, :hv_cmdfcn = COMMAND_FUNCTION, :hv_dynfcn = DYNAMIC_FUNCTION, ...
SQL/MX Compiler Embedded SQL Figure 4-1. SQL/MP Compilation Process Source File Binder Process to Combine the Host Language Object File With Other Object Files Host Language and SQL Statements Host Language Compiler Binder Process Host Language Object File With SQL Source Statements Host Language Object File SQL Compiler SQL Program File Valid SQL Program File Ready for Execution SQL/MX Compiler The SQL/MX program compilation process is different from that of NonStop SQL/MP.
SQL/MX Compiler Embedded SQL 3. Run the SQL/MX compiler to create execution plans for the SQL statements and store the plans in a module file. 4. Run the C or COBOL compiler to compile the statements in the source file. 5. Execute the program in the OSS environment, as described in the SQL/MX Programming Guide for C and COBOL. Figure 4-2.
Stored Procedures in Java Embedded SQL Stored Procedures in Java Both NonStop SQL/MP and NonStop SQL/MX allow you to use stored procedures, with these differences: SQL/MP Stored Procedures • • • Only callable from ODBC/MP. The called stored procedure must be a Pathway server with embedded SQL, written in COBOL, C, or TAL. Cannot use SQL/MX features like Publish/Subscribe.
Similarity Checks and Automatic Recompilation Embedded SQL Difference between compile and run time module Does SQL/MP similarity check pass? Does SQL/MX similarity check pass? Number of indexes - more indexes at run time than at compile time Yes No for INSERT, UPDATE, DELETE queries. Yes for SELECT. Number of indexes - fewer indexes at run time than at compile time Yes, if index is not used by plan No for INSERT, UPDATE, delete queries. Yes, for SELECT, if index is not used by plan.
5 Optimizer and Executor Both the compiler and the executor for NonStop SQL/MX are different from NonStop SQL/MP.
Optimizer Optimizer and Executor SQL/MP Optimizer SQL/MX Optimizer Search engine uses dynamic programming (bottom-up optimization) to enumerate possible join orders. Considers Cartesian products as part of the join enumeration. Search engine uses branch and bound programming (top-down optimization). Multipass design; in the pilot phase, tables are ordered in ascending order, and large tables are scanned last.
Executor Optimizer and Executor SQL/MP Optimizer SQL/MX Optimizer Costing and statistics: Costing and statistics: • • • • Uses captured statistics to estimate the number of rows. Consists of a number of values—UEC, second high and second low values—on a percolumn basis. Numeric data requires data be uniformly distributed (between the second high and second low values). Costing based on simple arithmetic. Cost is represented by a single floating point value.
SQL/MP Iterator Model Optimizer and Executor This table highlights the differences between the SQL/MP executor and the SQL/MX executor. SQL/MP Executor SQL/MX Executor Control flow iterator model. See SQL/MP Iterator Model. Data-flow, scheduler-driven task model. See SQL/MX Task Model. Nested join, hash join, and merge join supported. Nested join, hash join, and merge join supported. Within a join, also supports range partitioning. Aggregate and sort GROUP BY queries can be executed in DAM.
SQL/MX Task Model Optimizer and Executor Nested Join 1 8 4 Function call Scan A 2 for scan to DAM 5 Scan B 7 Function call 6 3 for scan to DAM SQL/MX Task Model NonStop SQL/MX uses a data flow and scheduler-driven task model to execute queries. After a query is optimized, the optimizer generates an optimized, executable query plan that goes to the executor. The executor creates an operator tree based on the executable query plan and prepares a node for each operator.
SQL/MX Task Model Optimizer and Executor The initial request from the join to the left scan queue must occur before the other operations can begin. After the initial request is processed, both the left and right scans can be processing information through their queues simultaneously. Join 1 Scan 2 3 4 Scan The task model makes it easy to perform all internal operations asynchronously so that a single server thread can have multiple I/Os outstanding.
Optimizer and Executor Ways to Influence the Query Execution Plan Ways to Influence the Query Execution Plan In NonStop SQL/MP, you can write queries in different ways to affect how a query is optimized. You must know how the SQL/MP optimizer handles predicates so that a proper query design yields an optimal plan. You can write queries in such a way as to eliminate certain types of joins.
EXPLAIN Features Optimizer and Executor This table lists some of the differences in default values between NonStop SQL/MP and NonStop SQL/MX. If you want to mimic an SQL/MP system using NonStop SQL/MX, you need to reset some default values to get the NonStop SQL/MP behavior.
Optimizer and Executor Statistics Statistics In NonStop SQL/MP, the UPDATE STATISTICS statement updates the statistics stored in the catalog for the specified table. In NonStop SQL/MX, the UPDATE STATISTICS statement updates the histogram statistics for a group of columns within a table. NonStop SQL/MX provides a method for generating histograms that shows how data is distributed for a column within a table.
Optimizer and Executor HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003 5- 10 Statistics
6 Data Definition Language (DDL) Differences With NonStop SQL/MX Release 2, NonStop SQL/MX can now create objects. Prior to Release 2, NonStop SQL/MX did not allow creation of objects. NonStop SQL/MX can access NonStop SQL/MX as well as SQL/MP files. New Data Definition Language (DDL) statements allow you to create and modify application files. For details on these statements, see “SQL/MX Statements” in the SQL/MX Reference Manual.
Data Definition Language (DDL) Differences Organization of Catalogs and Schemas collections of schemas. Schema names must be unique within a given catalog. Multiple objects with the same name can exist provided that each belongs to a different namespace. NonStop SQL/MX supports various types of namespaces. See “SQL/MX Language Elements” in the SQL/MX Reference Manual for details about these namespaces. You reference SQL/MP database objects by using their Guardian physical names.
Metadata Data Definition Language (DDL) Differences same node. Normally, an SQL program is registered in a catalog, too. For more information, see the SQL/MP Reference Manual. NonStop SQL/MP allows you to create catalogs and to drop system catalogs. Metadata The system catalog is NONSTOP_SQLMX_nodename. SQL/MX metadata is organized within these system schemas: SYSTEM_SCHEMA System schema tables that list information such as catalogs and schemas in the system.
Data Definition Language (DDL) Differences Tables Tables You create constraints on an SQL/MX table with the CREATE TABLE and ALTER TABLE ADD CONSTRAINT statements. You create constraints on SQL/MP tables with the CREATE CONSTRAINT statement. Clustering Keys You assign a clustering key for an SQL/MX table by using the STORE BY clause of the CREATE TABLE statement. If you enter STORE BY PRIMARY KEY, NonStop SQL/MX bases the clustering key on the primary key.
Data Definition Language (DDL) Differences Indexes Indexes You can create indexes for SQL/MX files, with these differences from NonStop SQL/MP: • Index table attribute NonStop SQL/MX allows you to set these index table attributes: ALLOCATE, AUDITCOMPRESS, BLOCKSIZE, CLEARONPURGE, EXTENT, and MAXEXTENTS.
Data Definition Language (DDL) Differences Views NonStop SQL/MP allows no more than one partition of an object on each disk. SQL/MP partitions have Guardian names. You manage partitions differently in NonStop SQL/MX and NonStop SQL/MP. For details, see Partition Management: The MODIFY Utility on page 7-2 Views SQL/MX Views The distinction between protection and shorthand views does not exist for SQL/MX views. To create a view, you must have SELECT privileges for the objects underlying the view.
Data Definition Language (DDL) Differences Object Security NonStop SQL/MP uses the SECURE clause to specify a Guardian security string, “RWEP” to secure objects. security is controlled by the Guardian security vector stored in the label.
Data Definition Language (DDL) Differences HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003 6 -8 Object Security
7 Utility Differences SQL/MX 2.0 includes utilities that perform tasks such as populating indexes, displaying information about files or DDL, managing partitions, and so on.
Partition Management: The MODIFY Utility Utility Differences GOAWAY Similar to SQL/MP’s GOAWAY command INFO Displays ANSI names and versions of Guardian files used by an object VERIFY Similar to SQL/MP’s VERIFY command For more information, see “SQL/MX Utilities” in the SQL/MX Reference Manual. Partition Management: The MODIFY Utility In both NonStop SQL/MX and NonStop SQL/MP, you create partitioned tables and indexes with CREATE TABLE and CREATE INDEX.
SHOWLABEL Command Utility Differences objects. This output can be used to re-create the specified object, including its dependent objects. SHOWDDL is similar to SQL/MP’s INVOKE command, which produces a record description that corresponds to a row in a specified table or view. See “MXCI Commands” in the SQL/MX Reference Manual for more information. SHOWLABEL Command SQL/MX’s SHOWLABEL command displays file-label and resource-fork information for SQL/MX tables, worktables, views, and indexes.
Utility Differences SHOWLABEL Command HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003 7 -4
Index A B Access options differences 3-16 STABLE access 3-17 summary 2-1 Access plans forcing 5-8 influencing 5-7 reviewing with EXPLAIN function 5-8 ADD DEFINE 2-1 Aggregate functions 2-1, 3-11 Aliases 2-1 ALLOCATE file attribute 2-1 ALTER CATALOG statement 2-1 ALTER COLLATION statement 2-1 ALTER DEFINE statement 2-1 ALTER INDEX statement 2-1 ALTER PROGRAM statement 2-1 ALTER TABLE statement 2-1 ALTER VIEW statement 2-1 ANSI compatibility 1-5 APPEND command 2-1 APPENDCANCEL command 2-1 APPENDRESTART comm
D Index COMMENT statement 2-3 Comments 2-3 COMMENTS table 2-3 COMMIT option 2-3 COMMIT WORK statement 3-19 Comparison predicate 2-3 Compile SQL/MP 4-10 SQL/MX 4-11 COMPUTE_TIMESTAMP function 2-3 CONCAT clause 2-3 Constraints 2-4, 6-4 CONSTRNT table 2-4 CONTINUE statement 2-4 CONTROL EXECUTOR directive 2-4 CONTROL QUERY directive 3-23 CONTROL TABLE directive 3-21 Conversion, data types 4-3 CONVERT command 2-4 CONVERTTIMESTAMP function 2-4 COPY command 2-4 Correlation names 2-4 COUNT function 2-4 CPRLSRCE t
E Index DETAIL command 2-6 Diagnostics area 4-9 Differences, NonStop SQL/MP and NonStop SQL/MX 1-2 DISPLAY STATISTICS command 2-6 DISPLAY USE OF command 7-1 DISTINCT clause 2-6 DML features 3-1 DML statements 3-12 DOWNGRADE CATALOG command 2-6 DOWNGRADE SYSTEM CATALOG command 2-7 DROP statement 2-7 DROP SYSTEM CATALOG statement 2-7 DSL (Data Status Language) statements 2-7 DSLACK file attribute 2-7 DUP command 2-7 Dynamic SQL 4-6 E EDIT command 2-7 Embedded SQL 4-1 END DECLARE SECTION statement 2-7 ENV c
H Index Group manager 2-8 Guardian names 2-8 H HEADING clause 2-8 HEADINGS option 2-8 HELP command 2-9 HELP TEXT statement 2-9 Histograms 5-9 HISTORY command 2-9 Host identifiers 2-9 Host programs 2-9 Host variables declaring 4-1 summary 2-9 I ICOMPRESS file attribute 2-9 Identifiers 3-5 IF/THEN/ELSE clause 2-9 IMPORT command 7-1 IN predicate 2-9 INCLUDE SQLCA directive 2-9 INCLUDE SQLDA directive 2-9 INCLUDE SQLSA directive 2-9 INCLUDE STRUCTURES directive 2-9 Index keys 2-9 Indexes description of 6-5
M Index M Mathematical functions 3-10 MAX function 2-11 MAXEXTENTS file attribute 2-11 Message file 2-11 Metadata 6-3 MIN function 2-11 MODIFY CATALOG command 2-11 MODIFY LABEL command 2-11 MODIFY REGISTER command 2-11 Multibyte character sets 2-11 MXTOOL utility 7-1 N NAME command 2-11 NAME option 2-11 Name resolution 1-3 Names 2-11, 6-1 Namespace 6-1 NEWLINE_CHAR option 2-11 NO INVALIDATE option, indexes 6-5 Nonaudited tables 2-11 NonStop SQL/MP 1-1 NonStop SQL/MX 1-1 description of 1-1 parallel operat
Q Index Program invalidation 2-13 PROGRAMS table 2-13 Protection view 2-13 PURGE command 2-13 PURGEDATA command 2-13 Q Qualified fileset list 2-13 Quantified predicate 2-13 Query caching cache location 3-24 considerations 3-24 controls 3-24 counter 3-26 description of 3-24 efficiency increase 3-24 setting 3-24 size limit 3-24 statistics 3-25 types of queries 3-26 Query expressions 3-15 Query type comparison 3-27 Queues 5-5 R READ UNCOMMITTED access, compared to BROWSE access 3-16 RECLENGTH file attribut
S Index Report Writer commands (continued) SPACE option 2-15 SUBTOTAL command 2-16 SUBTOTAL_LABEL option 2-16 TIME_FORMAT option 2-16 UNDERLINE_CHAR option 2-17 VARCHAR_WIDTH option 2-17 WINDOW option 2-18 Reserved words 3-5 RESET DEFINE statement 2-13 RESET LAYOUT command 2-13 RESET PARAM command 2-13 RESET PREPARED command 2-13 RESET REPORT command 2-13 RESET SESSION command 2-13 RESET STYLE command 2-13 RESETBROKEN file attribute 2-13 RIGHT_MARGIN option 2-13 ROLLBACK WORK statement 3-19 ROWCOUNT optio
T Index SQL/MX catalogs 6-2 STABLE access description of 3-17 EXCLUSIVE MODE 3-18 READ COMMITTED 3-17 SQL/MX compared to SQL/MP 3-17 Standards conformance 2-15 Statements DDL (Data Definition Language) 6-1 DML (Data Manipulation Language) 3-12 DSL (Data Status Language) 2-7 Statistics SQLSA 4-10 updating 5-9 Stored procedures 2-16 String functions 2-16 String literals 2-16 Subqueries 2-16 SUBSTRING function 2-16 SUBTOTAL command 2-16 SUBTOTAL_LABEL option 2-16 SUM function 2-16 Super ID 2-16 SYSKEY 2-16 S
V Index V VARCHAR data type 4-2 VARCHAR_WIDTH option 2-17 VERIFIEDWRITES file attribute 2-17 VERIFY command 7-2 Versioning 3-29 Versions 2-18 VERSIONS table 2-18 Views 6-6 VIEWS table 2-18 VOLUME command 2-18 W WHENEVER declarative 4-9 WHENEVER directive 2-18 WHERE clause 2-18 WINDOW option 2-18 WITH SHARED ACCESS option 2-18 Special Characters ! command 2-18 HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003 Index -9
Index Special Characters HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003 Index -10
