HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide Abstract This manual explains how to migrate NonStop™ SQL/MP databases and applications to NonStop SQL/MX on an HP NonStop system. Product Version NonStop SQL/MX Release 3.0 Supported Release Version Updates (RVUs) This publication supports J06.11 and all subsequent J-series RVUs and H06.22 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications.
Document History Part Number Product Version Published 540435-002 NonStop SQL/MX Releases 2.0, 2.1, 2.2 February 2006 540435-005 NonStop SQL/MX Release 2.0, 2.1, 2.2, 2.3, 2.3.1, 2.3.2, 2.3.3, 2.3.4 April 2010 666211-001 NonStop SQL/MX Release 3.
Legal Notices Copyright 2011 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide Glossary Legal Notices Index Examples Figures Tables 1 What’s New in This Manual vii Manual Information vii New and Changed Information vii About This Manual ix Audience ix What This Manual Does Not Cover Organization x Related Documentation x Notation Conventions xiv ix 1.
Contents 3.
Contents 4.
Contents 5. Converting a SQL/MP Database to SQL/MX Tables Updating Table Statistics 4-59 Displaying and Analyzing Query Execution Plans 4-59 Forcing Query Execution Plans 4-59 Using Row-Value Constructors 4-60 Moving the Application From Development to Production 4-60 Ensuring Optimal Performance in Production 4-60 Steps for Moving an Application to Production 4-61 5.
Contents Examples Examples Figures Figure 1-1. Figure 1-2. Coexistence of NonStop SQL/MP and NonStop SQL/MX 1-3 Migrating SQL/MP Applications to SQL/MX Applications 1-3 Tables Table 1-1. Table 1-2. Table 1-3. Table 1-4. Table 1-5. Table 1-6. Table 1-7. Table 1-8. Table 1-9. Table 1-10. Table 2-1. Table 2-2. Table 2-3. Table 4-1. Table 4-2. Table 4-3. Table 4-4. Table 4-5. Table 4-6. Table 4-7. Table 4-8. Table 4-9. Table 4-10. Table 4-11. Table 4-12. Table 4-13. Table 4-14. Table 4-15. Table 5-1.
Contents Table 5-4. Table 5-5. Table 5-6. Table 5-7. Table 5-8.
What’s New in This Manual Manual Information HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide Abstract This manual explains how to migrate NonStop™ SQL/MP databases and applications to NonStop SQL/MX on an HP NonStop system. Product Version NonStop SQL/MX Release 3.0 Supported Release Version Updates (RVUs) This publication supports J06.11 and all subsequent J-series RVUs and H06.22 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications.
What’s New in This Manual HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001 viii
About This Manual This manual explains how to migrate NonStop SQL/MP databases and applications on a NonStop system to NonStop SQL/MX. NonStop SQL/MX is a relational database management system for the HP NonStop server and is based on the ANSI/ISO/IEC 9075:1999 SQL standard, commonly referred to as SQL:1999. Throughout this manual, SQL/MX Release 3.0, which is the latest version of SQL/MX, is used for reference.
About This Manual Organization Organization Section 1, Overview Defines migration terminology, provides planning considerations, and describes where you can go for further assistance. Section 2, Version Management and Interoperability Describes SQL/MX releases, interoperability of SQL/MX releases, versions of SQL/MX software components, query plan versioning, and managing a mixed-node network.
About This Manual Related Documentation Installation Guides SQL/MX Installation and Management Guide Describes how to plan for, install, create, and manage a SQL/MX database. Explains how to use installation and management commands and utilities. NSM/web Installation Guide Describes how to install NSM/web and troubleshoot NSM/web installations.
About This Manual Related Documentation Application Development Guides SQL/MX Programming Manual for C and COBOL Describes how to embed SQL/MX statements in ANSI C and COBOL programs. SQL/MX Query Guide Describes how to understand query execution plans and write optimal queries for a SQL/MX database. SQL/MX Queuing and Publish/Subscribe Services Describes how NonStop SQL/MX integrates transactional queuing and publish/subscribe services into its database infrastructure.
About This Manual Notation Conventions Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. 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.
About This Manual General Syntax Notation braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example: LISTOPENS PROCESS { $appl-mgr-name } { $process-name } ALLOWSU { ON | OFF } | 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.
About This Manual Notation for Messages !i and !o. In procedure calls, the !i notation follows an input parameter (one that passes data to the called procedure); the !o notation follows an output parameter (one that returns data to the calling program). For example: CALL CHECKRESIZESEGMENT ( segment-id , error ) ; !i !o !i,o. In procedure calls, the !i,o notation follows an input/output parameter (one that both passes data to the called procedure and returns data to the calling program).
About This Manual Change Bar Notation [ ] Brackets. Brackets enclose items that are sometimes, but not always, displayed. For example: Event number = number [ Subject = first-subject-value ] A group of items enclosed in brackets is a list of all possible items that can be displayed, of which one or none might actually be displayed.
1 Overview This chapter describes the guidelines and the plan to migrate from NonStop SQL/MP to NonStop SQL/MX. SQL/MX is the standards based-database for NonStop servers. NonStop SQL/MX inherits the same performance and high-availability characteristics and provides a familiar environment for SQL/MP database users by building on the features offered by NonStop SQL/MP.
Overview Migrating from NonStop SQL/MP to NonStop SQL/MX SQL/MX application. Uses SQL/MX syntax, is SQL-compiled using MXCMP, and can query SQL/MP and SQL/MX database objects. SQL/MX Release 2.x. SQL/MX Release 2.0, SQL/MX Release 2.1, SQL/MX Release 2.2, and subsequent releases until otherwise indicated in a replacement publication. SQL/MX Release 3.0. SQL/MX Release 3.0, unless otherwise specified.
Overview Migrating from NonStop SQL/MP to NonStop SQL/MX Figure 1-1. Coexistence of NonStop SQL/MP and NonStop SQL/MX SQL/MP Application SQL/MX Application SQL/MP database SQL/MX database VST003.vsd Creating new SQL/MX applications and database objects poses less risk than converting SQL/MP applications and database objects to NonStop SQL/MX.
Overview Planning for Migration The migrated applications can continue to access SQL/MP database objects, enabling you to benefit from SQL/MX functionality without converting the SQL/MP database. Converting SQL/MP applications to SQL/MX applications poses more risk than creating new SQL/MX applications because some application downtime occurs. To migrate SQL/MP applications to NonStop SQL/MX, see 4, Converting SQL/MP Applications to SQL/MX Applications.
Overview Determining Your Business Objectives Table 1-1.
Overview Assessing Your Current Application and Database Environment Assessing Your Current Application and Database Environment To plan migration to NonStop SQL/MX, start by assessing the application and database environment on the current system to: determine what applications will be affected by the migration and what changes you will need to make to complete a successful migration to NonStop SQL/MX.
Overview Assessing Your Current Application and Database Environment Table 1-2.
Overview Assessing Your Current Application and Database Environment Table 1-3. Database Applications (page 2 of 2) Obtain this information FORTRAN (NonStop SQL/MX cannot be embedded in this language.) Pascal (NonStop SQL/MX cannot be embedded in this language.) TAL or pTAL (NonStop SQL/MX cannot be embedded in this language.) Whether each source code component uses SQL/MP statements or SQL/MX statements.
Overview Assessing Your Current Application and Database Environment Table 1-4. Application Execution Environment Obtain this information What is the application execution environment—Guardian or OSS? Name of each application and its interactive components: Number of users Devices Remote applications that use it Batch components Supporting utilities Table 1-5.
Overview Choosing Which Database Applications and Database Objects to Migrate Table 1-6. Testing Environment (page 2 of 2) Obtain this information Test scripts identified by their application Test drivers and other related programs identified by their application Test data and test databases identified by their application Table 1-7. Database Obtain this information Databases and data files that are candidates for migration.
Overview Choosing Which Database Applications and Database Objects to Migrate NonStop SQL/MX. Use Table 1-8 and Table 1-9 on page 1-11 to gather information that will help you determine which applications and database objects to migrate to NonStop SQL/MX. Table 1-8. Choosing Which Database Applications to Migrate Obtain this information Identify and prioritize the applications to move to NonStop SQL/MX. 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
Overview Planning the New Application and Database Environment Table 1-9. Choosing Which Database Objects to Migrate (page 2 of 2) Obtain this information 4. 5. 6. 7. 8. 9. 10.
Overview Planning the New Application and Database Environment Table 1-10.
Overview Guidelines for Conducting the Migration If the migration is broken into phases, each phase must include its own complete plan. Migration Plan For more information, see...
Overview Assistance With Migration Assistance With Migration Specific strategies for migrating to NonStop SQL/MX depend on your unique database environment and business needs. It can be time-consuming and costly to determine and implement a migration strategy, without the help of migration experts and special tools.
Overview NonStop Solutions Development and Implementation HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001 1-16
2 Version Management and Interoperability This chapter covers these topics: NonStop SQL/MX Releases on page 2-1 Interoperability of NonStop SQL/MX Releases on page 2-7 Versions of SQL/MX Software Components on page 2-7 NonStop SQL/MX Releases A SQL/MX release identifies a set of SQL/MX products as belonging to a given version of NonStop SQL/MX.
Version Management and Interoperability SQL/MX Releases on Systems Running H-Series RVUs and J-Series RVUs Each system node can support only one release of NonStop SQL/MX at a time. Table 2-1 on page 2-2 shows the NonStop systems that support SQL/MX Release 3.0. Table 2-1. SQL/MX Releases on Systems Running H-Series RVUs SQL/MX Release Product Version Identifier SPR Identifier Availability Date of Initial Release 3.0 H30 N/A General 2.2.0 H22 ACF 2.1.
Version Management and Interoperability Naming Scheme for SQL/MX Releases Naming Scheme for SQL/MX Releases NonStop SQL/MX contains many individual products, which are identified by T-numbers. A SQL/MX release name identifies a set of SQL/MX products as belonging to a given release of NonStop SQL/MX. Each SQL/MX release is identified by a three-part release identifier: SQL/MX Release 3.0.0 (1) Major Release Higher (2) Incremental Release (3) Maintenance Release Migration Risk Lower VST002.vsd 1.
Version Management and Interoperability Naming Scheme for SQL/MX Releases Maintenance Releases Maintenance releases introduce bug fixes and minimal new functionality. These releases occur most frequently and rarely affect all the component products in the SQL/MX product set. This manual refers to maintenance releases only when they introduce new functionality that requires mentioning.
Version Management and Interoperability Product Version and SPR Identifiers For example, this VPROC command displays information for the T1051 product that is associated with SQL/MX Release 3.0.0 for H-series RVUs: T1051H30_14FEB2011_H30_300_1130 The SQL/MX Master Softdoc (T0650) for each SQL/MX release refers to the release name and VPROC information. Product Version and SPR Identifiers Each SQL/MX release is associated with a product version identifier and possibly an SPR identifier.
Version Management and Interoperability Delivery of SQL/MX Releases Delivery of SQL/MX Releases Each SQL/MX release is delivered as a set of Software Product Revisions (SPRs). Some SQL/MX SPRs are first available for downloading from Scout for NonStop Servers and are later available on the next RVU’s Site Update Tape (SUT). Other SQL/MX SPRs are available only on specific RVU SUTs and are not available in Scout for NonStop Servers. Rarely, as is the case for SQL/MX Release 3.
Version Management and Interoperability Interoperability of NonStop SQL/MX Releases Interoperability of NonStop SQL/MX Releases SQL/MX 3.0 does not interoperate with other versions of SQL/MX in an EXPAND network. Versions of SQL/MX Software Components Each SQL/MX software component and persistent entity has a version identifier that identifies its software release and that can be understood by other SQL/MX releases. Versioned software components include the SQL/MX system software version (MXV).
Version Management and Interoperability SQL/MX System Software Version (MXV) Table 2-3. Software Version Identifiers for SQL/MX Release 3.0 Version Item SQL/MX Release Version Identifier 3.0 Current MXV 3000 Earliest supported MXV 3000 Current schema version and OSV 3000 Earliest supported schema version 1200 Default compiler version 3000 Current plan version 3000 Earliest supported plan version 3000 For more Information, see...
Version Management and Interoperability SQL/MX Database Object Versions Schema Version and Object Schema Version A schema version is assigned to each user schema at creation time and is based on the version of the SQL/MX compiler that creates the schema. The Object Schema Version (OSV) of a SQL/MX database object is inherited from the schema where the object resides and determines whether the object can be accessed by a particular version of NonStop SQL/MX or not.
Version Management and Interoperability SQL/MX Query Plan and Module Versions Displaying the OFV This example displays the OFV of a specified database object, where the table name, tlarge is a value that you specify: >>get version of table tlarge; OBJECT SCHEMA VERSION: 3000 OBJECT FEATURE VERSION: 3000 --- SQL operation complete.
Version Management and Interoperability Interoperability Across Schema Versions Module Versions When you compile user modules of embedded SQL/MX applications, the version assigned to a module is the same as the default compiler version of the node where you compile the module. The SQL/MX compilers produce the following module versions: SQL/MX Compiler User Module Version SQL/MX Release 3.
Version Management and Interoperability HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001 2-12
3 Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Before migrating from NonStop SQL/MP to NonStop SQL/MX, follow these planning guidelines: Using NonStop SQL/MX and NonStop SQL/MP Together on page 3-1 Meeting System Hardware Requirements on page 3-1 Meeting Operating System Requirements on page 3-2 Planning for Performance and Capacity on page 3-2 Planning for Fallback on page 3-3 Installing SQL/MX on page 3-3 Installing Corequisite Independent Products on page 3-4 Preparing t
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Migrating From S-Series Systems to NS-Series Systems about installing NonStop SQL/MX, see the SQL/MX Installation and Management Guide. Migrating From S-Series Systems to NS-Series Systems HP strongly recommends that you migrate to an NS-series system, especially if you are planning to create a SQL/MX database and put it in production. For information about migrating to an NS-series system, see the NonStop NS-Series Database Migration Guide.
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Planning for Fallback Planning for Fallback Before you migrate to NonStop SQL/MX, plan for a potential fallback to NonStop SQL/MP.
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Installing Corequisite Independent Products Ordering the SQL/MX DDL License To create SQL/MX database objects, you must purchase and install the SQL/MX DDL license, product T0394. You cannot create SQL/MX database objects without this product. If you try to create SQL/MX database objects without the DDL license, NonStop SQL/MX returns an error message informing you that the system is not licensed.
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Preparing to Migrate SQL/MP Database Objects applications. Determine when to stop the applications, migrate the database, and restart the applications. Estimate the speed of migration, determine how much time you have to complete the migration, and schedule an optimal time for cutover to the new SQL/MX database.
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Preparing to Migrate SQL/MP Database Objects HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001 3-6
4 Converting SQL/MP Applications to SQL/MX Applications SQL/MP applications use SQL/MP syntax, are SQL-compiled using SQLCOMP, and can query only SQL/MP database objects. SQL/MX applications use SQL/MX syntax, are SQL-compiled using MXCMP, and can query SQL/MP and SQL/MX database objects.
Converting SQL/MP Applications to SQL/MX Applications Converting SQL/MP Applications to SQL/MX Applications Converting SQL/MP Applications to SQL/MX Applications HP recommends that you create new applications using NonStop SQL/MX and continue using SQL/MP applications for risk-averse situations.
Converting SQL/MP Applications to SQL/MX Applications New Functionality in NonStop SQL/MX SQL/MX database objects (See Converting SQL/MP Tables to SQL/MX Tables on page 5-1.) For information on the complete list of features, see the SQL/MX Reference Manual. To use these new features, you must either create new SQL/MX applications or convert SQL/MP applications to SQL/MX applications.
Converting SQL/MP Applications to SQL/MX Applications 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.
Converting SQL/MP Applications to SQL/MX Applications Parallel Operations for Scalable High Performance Parallel Operations for Scalable High Performance NonStop SQL/MX can perform some queries in parallel across multiple disk volumes without the use of Executor Server Processes (ESPs). NonStop SQL/MP always requires ESPs for parallel queries. NonStop SQL/MX can execute large queries that scan large portions of the database in parallel.
Converting SQL/MP Applications to SQL/MX Applications Types of SQL/MP Applications to Convert Parallel Operations on Partitioned Tables Currently, unlike NonStop SQL/MP, NonStop SQL/MX does not open partitions in parallel. If a table has many partitions, opens takes longer to complete. ESPs in NonStop SQL/MX are not started in parallel as the ESP Manager does in NonStop SQL/MP. If a table has many partitions, ESP startup takes longer to complete.
Converting SQL/MP Applications to SQL/MX Applications Converting ODBC Client Applications to NonStop SQL/MX 2. Correct all unsupported syntax in the source files. For details, see: Using ETK to Detect Unsupported SQL Syntax on page 4-7 Changes to SQL Syntax on page 4-16 Changes to Embedded SQL Constructs on page 4-41 3. Preprocess and compile the source files.
Converting SQL/MP Applications to SQL/MX Applications Converting JDBC/MP Applications to NonStop SQL/MX Converting JDBC/MP Applications to NonStop SQL/MX For a Java application using NonStop SQL/MP, to take advantage of SQL/MX functionality, you must change the application from using the HP JDBC Driver for Non Stop SQL/MP (the JDBC/MP driver) to the HP JDBC Driver for NonStop SQL/MX (the JDBC/MX driver) or the HP NonStop JDBC Type 4 driver (the Type 4 driver).
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX OBEY Command Files OBEY command files are supported by NonStop SQL/MP in the SQL/MP conversational interface, SQLCI, and by NonStop SQL/MX in the SQL/MX conversational interface, MXCI. Unlike SQLCI, which runs as a TACL process, MXCI runs as an OSS process and must be started in the OSS environment. In NonStop SQL/MP, OBEY command files are EDIT files (type 101) with Guardian names that you run in SQLCI.
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 4-1. SQLCI Commands and MXCI Commands (page 2 of 6) SQLCI Commands Implemented in MXCI? Comments CLEANUP No Utility. See SQL/MX Utilities on page 5-16. CONVERT No Utility. See SQL/MX Utilities on page 5-16. COPY No Utility. See SQL/MX Utilities on page 5-16. CREATE SYSTEM CATALOG No To create the system catalog in NonStop SQL/MX, run the InstallSqlmx script.
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 4-1. SQLCI Commands and MXCI Commands (page 3 of 6) SQLCI Commands Implemented in MXCI? Comments DROP SYSTEM CATALOG No NonStop SQL/MX uses the DROP SQL command to put NonStop SQL/MX in an uninitialized state and drop a system catalog that has no user-created catalogs. For more information, see the SQL/MX Reference Manual. DUP Yes Utility. See SQL/MX Utilities on page 5-16. EDIT No Utility.
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 4-1. SQLCI Commands and MXCI Commands (page 4 of 6) SQLCI Commands Implemented in MXCI? INVOKE Yes Comments The SQL/MX INVOKE command provides similar information, including record descriptions that correspond to a row in the specified table or view. To obtain more information about a SQL/MX object, for example, when you need to re-create it, use the SQL/MX SHOWDDL command.
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 4-1. SQLCI Commands and MXCI Commands (page 5 of 6) SQLCI Commands Implemented in MXCI? Comments PURGE No Utility. See SQL/MX Utilities on page 5-16. PURGEDATA Yes Utility. See SQL/MX Utilities on page 5-16. REPORT FOOTING Yes Report Writer REPORT TITLE Yes Report Writer RESET DEFINE No RESET LAYOUT Yes RESET PARAM Yes RESET PREPARED No RESET REPORT Changed Report Writer command.
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 4-1. SQLCI Commands and MXCI Commands (page 6 of 6) SQLCI Commands Implemented in MXCI? SHOW LAYOUT Yes SHOW PARAM Yes SHOW PREPARED No SHOW REPORT Yes SHOW SESSION Yes SHOW STYLE Yes Report Writer command. See Migrating From SQL/MP Report Writer to SQL/MX Report Writer on page 4-14. SUBTOTAL Yes Report Writer command.
Converting SQL/MP Applications to SQL/MX Applications Steps for Converting SQL/MP Applications to SQL/MX Applications NonStop SQL/MX also has a report writer that performs similar functions in MXCI. To migrate from the SQL/MP Report Writer to the SQL/MX Report Writer, see the SQL/MX Report Writer Guide. Steps for Converting SQL/MP Applications to SQL/MX Applications Converting a SQL/MP application to a SQL/MX application involves: 1. Modifying the Source Code on page 4-16: 2.
Converting SQL/MP Applications to SQL/MX Applications Modifying the Source Code Modifying the Source Code To convert a SQL/MP application to NonStop SQL/MX, first identify the source file of the application and then modify the source code so that it complies with SQL/MX syntax.
Converting SQL/MP Applications to SQL/MX Applications Data Control Language (DCL) Statements Data Control Language (DCL) Statements Some SQL/MP DCL statements are supported in NonStop SQL/MX. Table 4-2 lists the SQL/MP DCL statements that are supported in NonStop SQL/MX and describes the syntax differences: Table 4-2. SQL/MX and SQL/MP DCL Statements SQL/MP DCL Statements Implemented in SQL/MX? CONTROL EXECUTOR No PARALLEL EXECUTION is ON by default in NonStop SQL/MX.
Converting SQL/MP Applications to SQL/MX Applications Data Control Language (DCL) Statements Table 4-3. CONTROL TABLE Directives SQL/MP CONTROL TABLE Directives Implemented in SQL/MX? ACCESS PATH Changed Use CONTROL QUERY SHAPE to force access paths. See Forcing Query Execution Plans on page 4-59. JOIN METHOD Changed Use CONTROL QUERY SHAPE to force join methods. See Forcing Query Execution Plans on page 4-59.
Converting SQL/MP Applications to SQL/MX Applications Transaction Control Statements CONTROL QUERY Directives 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.
Converting SQL/MP Applications to SQL/MX Applications Transaction Control Statements Implicit Transactions NonStop SQL/MP supports implicit transactions in SQLCI but not in embedded statements. NonStop SQL/MX also supports implicit transactions in MXCI and in embedded statements. If you do not specify a BEGIN WORK statement, NonStop SQL/MX initiates a transaction when it encounters a SQL statement that refers to an audited table.
Converting SQL/MP Applications to SQL/MX Applications Data Manipulation Language (DML) Statements Accessing Non-audited SQL/MP Tables In both NonStop SQL/MP and NonStop SQL/MX, TMF works only on audited tables. Therefore, a transaction does not protect operations on non-audited tables. The simplest approach is to make all tables audited. NonStop SQL/MP is designed to avoid any inconsistencies between the base table and an index for non-audited tables.
Converting SQL/MP Applications to SQL/MX Applications INSERT Statements Table 4-6. SQL/MX and SQL/MP DML Statements SQL/MP DML Statements Implemented in SQL/MX? Comments DELETE Yes No changes are required for NonStop SQL/MX. INSERT Changed In NonStop SQL/MX, you can use an ORDER BY clause after you specify the insert source. Also, you can use multiple row-value constructors after the VALUES keyword. See INSERT Statements on page 4-22.
Converting SQL/MP Applications to SQL/MX Applications SELECT Statements In NonStop SQL/MX, each row to be inserted is specified as a list of value expressions or a row subquery. A value in a row can also be a scalar subquery. For example, this SQL/MX statement inserts two rows into table JOB: INSERT INTO JOB VALUES (1000, 'MANAGER'), ((SELECT * FROM JOB1 WHERE JOBCODE = 2000)); In NonStop SQL/MP you usually perform multiple row inserts by setting a single row insert to run in a loop.
Converting SQL/MP Applications to SQL/MX Applications UPDATE Statements GROUP BY Clause and Numerical Arguments In NonStop SQL/MP, the GROUP BY clause allows you to use numerical arguments, for example: SELECT a + b FROM t GROUP BY 1; NonStop SQL/MX does not allow the use of numerical arguments with the GROUP BY clause.
Converting SQL/MP Applications to SQL/MX Applications Access Options and Isolation Levels standard. Isolation levels for transactions are SQL:1999 compliant; you set transaction isolation levels with the SET TRANSACTION statement. Note. Unless you are writing applications for portability, use isolation levels at the statement level. The SET TRANSACTION statement might cause a dynamic recompilation of DML statements within the next transaction.
Converting SQL/MP Applications to SQL/MX Applications SQL Functions SERIALIZABLE or whether you should adjust your error handling procedures. With SERIALIZABLE, you can rely on the fact that when you read a row, no other transaction can change the row while you are processing it. Thus, you are guaranteed not to cause any integrity issues when you do update the row. READ COMMITTED also guarantees integrity but in a different way.
Converting SQL/MP Applications to SQL/MX Applications SQL Functions Table 4-7. SQL/MX and SQL/MP Functions (page 2 of 3) SQL/MP Functions Implemented in SQL/MX? Comments COUNT Yes No changes are required for NonStop SQL/MX. CURRENT Yes, but changes might be required. The SQL/MX CURRENT function cannot return a subset of date-time fields. CURRENT always returns year-to-second data. To return a different range of date-time fields, use one of the SQL/MX date-time functions.
Converting SQL/MP Applications to SQL/MX Applications Predicates Table 4-7. SQL/MX and SQL/MP Functions (page 3 of 3) SQL/MP Functions Implemented in SQL/MX? SUBSTRING Yes NonStop SQL/MP and NonStop SQL/MX differ somewhat in how they handle particular types of character strings. See Character Sets on page 4-34. SUM Yes No changes are required for NonStop SQL/MX. TRIM Yes NonStop SQL/MP and NonStop SQL/MX differ somewhat in how they handle particular types of character strings.
Converting SQL/MP Applications to SQL/MX Applications Predicates Table 4-8. SQL/MX and SQL/MP Predicates (page 2 of 2) SQL/MP Predicates LIKE NULL Quantified Implemented in SQL/MX? Comments Yes, but changes might be required. NonStop SQL/MX does not support the TERMINATE character. For more information, see the LIKE Predicate on page 4-30. Yes, but changes might be required. For possible changes, see: Yes, but changes might be required.
Converting SQL/MP Applications to SQL/MX Applications Predicates Row-Value Constructors Both NonStop SQL/MP and NonStop SQL/MX support the use of row-value constructors within predicates. However, the SQL/MP implementation is not SQL:1999 compliant. In NonStop SQL/MP, you are not required to enclose the sequence of expressions of a row-value constructor in parentheses. In NonStop SQL/MX, it is helpful (although not required) to do so.
Converting SQL/MP Applications to SQL/MX Applications Database Object References Database Object References SQL/MX applications can refer to SQL/MX database objects and most SQL/MP database objects.
Converting SQL/MP Applications to SQL/MX Applications Reserved Words SQL/MP Database Object References When converting a SQL/MP application to a SQL/MX application, you can continue using DEFINE names or Guardian physical names to refer to SQL/MP database objects. When using DEFINE names, make sure to set class MAP DEFINEs in the development environment before compiling the application. To set DEFINEs, see the SQL/MX Installation and Management Guide.
Converting SQL/MP Applications to SQL/MX Applications String Literal Delimiters These examples show a reserved word without double quotes, a nonreserved word without double quotes, and a reserved word with double quotes. Example 1—Reserved Word Without Double Quotes Hewlett-Packard NonStop(TM) SQL/MX Conversational Interface 2.0 (c) Copyright 2003 Hewlett-Packard Development Company, LP.
Converting SQL/MP Applications to SQL/MX Applications Character Sets misinterpret string literals as delimited identifiers. For more information, see the SQL/MX Reference Manual. Character Sets NonStop SQL/MP supports multiple character sets for character string literals and host variables.
Converting SQL/MP Applications to SQL/MX Applications Collations Collations NonStop SQL/MX does not support collations on character string columns, as NonStop SQL/MP does. NonStop SQL/MX supports only the DEFAULT (binary) collation. NonStop SQL/MX cannot access a column of a SQL/MP table that has a collation, other than the default collation.
Converting SQL/MP Applications to SQL/MX Applications Date-Time Data Types and Literals 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 a 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.
Converting SQL/MP Applications to SQL/MX Applications Date-Time Data Types and Literals Supported and Unsupported SQL/MP Date-Time Literals in NonStop SQL/MX SQL/MP date-time literals fall into one of these categories: Those that map directly to SQL:1999 types and are supported by NonStop SQL/MX Those that do not map to SQL:1999 types but are supported by NonStop SQL/MX Those that do not map to SQL:1999 types and are not supported in NonStop SQL/MX SQL/MP date-time literals that are not supported
Converting SQL/MP Applications to SQL/MX Applications Interval Data Types and Literals Table 4-9.
Converting SQL/MP Applications to SQL/MX Applications Interval Data Types and Literals Table 4-10.
Converting SQL/MP Applications to SQL/MX Applications Floating-Point Data Types 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. Interval Literals As in NonStop SQL/MP, a SQL/MX interval literal is either year-month or day-time.
Converting SQL/MP Applications to SQL/MX Applications Data Definition Language (DDL) Statements Table 4-11. SQL/MX and SQL/MP DSL Statements SQL/MP DSL Statements Implemented in SQL/MX? GET CATALOG OF SYSTEM No Remove this statement from the source code, or do not convert the application to NonStop SQL/MX. GET VERSION No Remove this statement from the source code, or do not convert the application to NonStop SQL/MX.
Converting SQL/MP Applications to SQL/MX Applications Host Variable Declarations Host Variable Declarations When converting an embedded SQL/MP application to an embedded SQL/MX application, change the host variable declarations according to these guidelines: Placement of Structure Definitions on page 4-42 SETSCALE Function on page 4-42 Declaration of Character Host Variables on page 4-43 Variable-Length Character Data in Embedded SQL C Programs on page 4-43 INVOKE Directive on page 4-43 Host
Converting SQL/MP Applications to SQL/MX Applications Host Variable Declarations Declaration of Character Host Variables NonStop SQL/MP allows the declaration of character host variables by using arithmetic operations to determine the size of the host variable array. NonStop SQL/MX does not allow arithmetic operations to determine the size of the array. In NonStop SQL/MX, host variables must have constants specifying the size of the array.
Converting SQL/MP Applications to SQL/MX Applications Indicator Variables Host Variables With Date-Time Data Types In NonStop SQL/MP, you must declare date-time and interval host variables as character arrays and then use the TYPE AS clause to cause NonStop SQL/MP to interpret the values as date-time or interval values. In SQL/MX, you can declare host variables with standard date-time and interval data types.
Converting SQL/MP Applications to SQL/MX Applications Embedded SQL Statements In NonStop SQL/MP, the indicator variable is not set to the source length in the case of truncation. It is set to 0 if it is a non-null value and if the value is null, the indicator variable is set to -1. Embedded SQL Statements Most embedded SQL/MP statements exist in NonStop SQL/MX. However, few of the statements, options, and clauses have been dropped because of SQL:1999 ANSI changes. Table 4-12.
Converting SQL/MP Applications to SQL/MX Applications Embedded SQL Statements Table 4-12. Embedded SQL Statements (page 2 of 2) Embedded SQL/MP Statements and Directives Implemented in SQL/MX? Positioned DELETE Yes Positioned UPDATE Changed NonStop SQL/MX allows this statement to be dynamic. PREPARE Yes SQL/MX FROM clause does not allow quoted strings. RELEASE No SELECT INTO Changed See SELECT Statements on page 4-23. WHENEVER Changed Usage changes. See WHENEVER Declarative on page 4-52.
Converting SQL/MP Applications to SQL/MX Applications Dynamic SQL FOR clause, and if ORDER BY or GROUP BY is specified for the cursor or if the query expression defining the cursor specifies a read-only table, the cursor defaults to read-only. It is the query that determines that the table is read-only, not the table’s attributes. Otherwise, the cursor defaults to updatable (without a column list). If you know that you are not going to update a cursor, specify FOR READ ONLY.
Converting SQL/MP Applications to SQL/MX Applications Dynamic SQL PREPARE Prepares (compiles) a dynamic SQL statement for subsequent execution by an EXECUTE statement. DEALLOCATE PREPARE Deallocates a prepared statement and returns the system resources used by the statement; also allows you to reuse the name of the statement. DESCRIBE [OUTPUT] Stores in the output descriptor area information about output values (usually SELECT columns) from a prepared statement.
Converting SQL/MP Applications to SQL/MX Applications Error Handling Error Handling The major difference in error handling between NonStop SQL/MP and NonStop SQL/MX relates to the SQL:1999 standard, as described in the following sections: Format of Error Codes on page 4-49 Equivalent SQLCODE and SQLSTATE Values on page 4-49 WHENEVER Declarative on page 4-52 Diagnostics Area on page 4-52 Format of Error Codes NonStop SQL/MP provides an integer-valued SQLCODE as the mechanism to send error inform
Converting SQL/MP Applications to SQL/MX Applications Error Handling Table 4-13. Mapping SQL/MP SQLCODE to SQL/MX SQLCODE Values (page 2 of 4) SQL/MP SQLCODE SQL/MX SQLCODE SQL/MX SQLSTATE An object’s definition changed since the statement was last SQL compiled, so automatic recompilation was attempted. -8202 -8575 X08FZ An OPEN operation on a table failed. -8204 -8574 01KFY, X08FY A DDL operation changed the AUDIT file attribute of a table, causing automatic recompilation to be attempted.
Converting SQL/MP Applications to SQL/MX Applications Error Handling Table 4-13. Mapping SQL/MP SQLCODE to SQL/MX SQLCODE Values (page 3 of 4) SQL/MP SQLCODE SQL/MX SQLCODE SQL/MX SQLSTATE The cursor specified in an UPDATE WHERE CURRENT OF request did not have the required update column list, so the update operation failed. -8229 -8836 X08N8 The data to be inserted or updated resulted in a row that violated a constraint on the table, so the insert or update operation failed.
Converting SQL/MP Applications to SQL/MX Applications Error Handling Table 4-13. Mapping SQL/MP SQLCODE to SQL/MX SQLCODE Values (page 4 of 4) SQL/MP SQLCODE SQL/MX SQLCODE SQL/MX SQLSTATE Either an invalid date-time value was entered or the result of a date-time expression produced an invalid date-time value, so the statement failed.
Converting SQL/MP Applications to SQL/MX Applications Statistics Area NonStop SQL/MX does not provide the SQL communication area or use the INCLUDE SQLCA directive. Instead, NonStop SQL/MX follows the ANSI SQL:1999 standard by automatically providing a diagnostics area that stores completion and exception information. At the beginning of the execution of a SQL statement, the diagnostics area is emptied. When the statement executes, NonStop SQL/MX places completion and exception information in this area.
Converting SQL/MP Applications to SQL/MX Applications Development Environments for SQL/MX Applications Similarity Checks and Automatic Recompilation of Embedded SQL/MX Applications on page 4-55 Managing Modules of Embedded SQL/MX Applications on page 4-57 Development Environments for SQL/MX Applications The development environment is where you code, compile, and test applications. The type of SQL/MX development environment depends on the type of application and the host-language compilers.
Converting SQL/MP Applications to SQL/MX Applications Similarity Checks and Automatic Recompilation of Embedded SQL/MX Applications execute embedded SQL/MX applications in the Guardian environment. For more information, see the SQL/MX Programming Manual for C and COBOL. If you plan to move to the OSS environment when converting your SQL/MP applications to SQL/MX applications, you might need to convert the TACL macros that compile applications to OSS shell scripts.
Converting SQL/MP Applications to SQL/MX Applications Similarity Checks and Automatic Recompilation of Embedded SQL/MX Applications Table 4-15.
Converting SQL/MP Applications to SQL/MX Applications Managing Modules of Embedded SQL/MX Applications Table 4-15.
Converting SQL/MP Applications to SQL/MX Applications Managing Modules of Embedded SQL/MX Applications information about generating locally placed modules, see the SQL/MX Programming Manual for C and COBOL. In NonStop SQL/MP, SQL program files are registered in a catalog and dependencies among the program and tables, views, and indexes are stored in metadata tables. In NonStop SQL/MX, the module file of the application is not registered in SQL/MX metadata tables.
Converting SQL/MP Applications to SQL/MX Applications Optimizing Query Execution Performance Optimizing Query Execution Performance To ensure optimal performance of SQL/MX applications, you must understand how to influence and optimize query execution plans in NonStop SQL/MX. Setting System Defaults Certain system defaults influence query execution plans. In NonStop SQL/MP, you can specify the default settings by using CONTROL directives.
Converting SQL/MP Applications to SQL/MX Applications Using Row-Value Constructors execution plans on a per table basis, NonStop SQL/MX allows you to use the CONTROL QUERY SHAPE statement to force the entire query execution plan. For more information, see the SQL/MX Query Guide.
Converting SQL/MP Applications to SQL/MX Applications Steps for Moving an Application to Production Steps for Moving an Application to Production 1. On the production system, run UPDATE STATISTICS commands: For each SQL/MP database object that your SQL/MX application accesses, run the SQL/MX UPDATE STATISTICS command and then the SQL/MP UPDATE STATISTICS command.
Converting SQL/MP Applications to SQL/MX Applications Steps for Moving an Application to Production HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001 4-62
5 Converting SQL/MP Tables to SQL/MX Tables This chapter explains how to convert SQL/MP database objects to SQL/MX database objects and covers these topics: Converting SQL/MP Tables to SQL/MX Tables on page 5-1 Differences Between SQL/MP and SQL/MX Databases on page 5-4 SQL/MX DDL Statements and Utilities on page 5-14 Steps for Converting a SQL/MP Database to SQL/MX Tables on page 5-20 Migrating Data From a SQL/MP Database to a SQL/MX Database on page 5-21 Updating Table Statistics on page 5-2
Converting SQL/MP Tables to SQL/MX Tables ANSI Compliance ANSI Compliance NonStop SQL/MX is open, portable, and, most importantly, uses an industry standard: the American National Standards Institute (ANSI) version of SQL. SQL/MX is based on ANSI SQL:1999 and includes support for features from higher levels of the standard. SQL/MX supports the creation of ANSI-compliant catalogs, schemas, and database objects.
Converting SQL/MP Tables to SQL/MX Tables Size of the Database Object Size of the Database Object NonStop SQL/MP limits the number of partitions that can exist for a SQL/MP table. However, NonStop SQL/MX does not limit the number of partitions for a SQL/MX table. If your SQL/MP table is growing, consider converting the SQL/MP table to NonStop SQL/MX. Reasons for Keeping SQL/MP Tables In some cases, it might be preferable to keep SQL/MP tables instead of converting them to SQL/MX tables.
Converting SQL/MP Tables to SQL/MX Tables Differences Between SQL/MP and SQL/MX Databases Unlike NonStop SQL/MP, which supports limited parallel updates, NonStop SQL/MX does not currently perform partition-label updates in parallel. Character Sets and Collations NonStop SQL/MX does not currently support all the character sets that NonStop SQL/MP supports, such as Big5 and ISO88592 through ISO88599. NonStop SQL/MX supports only the DEFAULT collation in a SQL/MX table.
Converting SQL/MP Tables to SQL/MX Tables System Catalogs and Metadata System Catalogs and Metadata In NonStop SQL/MP, the metadata is a combination of system catalog metadata and user catalog metadata. NonStop SQL/MP creates the system catalog when you issue the CREATE SYSTEM CATALOG command, in whatever subvolume you specify. The default is $SYSTEM.SQL. NonStop SQL/MP creates user catalogs when you issue the CREATE CATALOG command in the specified subvolume.
Converting SQL/MP Tables to SQL/MX Tables Database Object Names A SQL/MX user catalog is a named logical object that contains descriptions of a set of schemas. A user catalog can contain multiple schemas, each possibly owned by a different user. A catalog cannot contain other catalogs. To access SQL/MX database objects, use the three-part name (catalog.schema.object) of the actual database object. NonStop SQL/MX uses ANSI-compliant CREATE and DROP statements to create and drop schemas.
Converting SQL/MP Tables to SQL/MX Tables Distributed Database Objects Distributed Database Objects Both NonStop SQL/MP and NonStop SQL/MX support distributed database objects. In NonStop SQL/MP, you must define a catalog on a node to describe the partition resident on that node. Partitioned tables and indexes must have their descriptions stored in the catalog on each node where their partitions are located. This duplication of the table or index description enables local node autonomy.
Converting SQL/MP Tables to SQL/MX Tables Tables Tables A SQL/MX table is described in a SQL/MX schema yet stored in one or more physical files in the Guardian environment. Unlike SQL/MP tables, which have Guardian physical names, a SQL/MX table has an ANSI name of catalog.schema.table, where catalog is the name of the catalog, schema is the name of the schema, and table is the name of the table.
Converting SQL/MP Tables to SQL/MX Tables Tables Table 5-1. File Attributes in NonStop SQL/MP and NonStop SQL/MX (page 2 of 2) SQL/MP Attributes Supported in SQL/MX? LOCKLENGTH No MAXEXTENTS Yes NOPURGEUNTIL No RECLENGTH No TABLECODE No VERIFIEDWRITES No For more information about SQL/MX file attributes, see the SQL/MX Reference Manual. Audited Tables Only SQL/MP tables can be non-audited or audited, but all SQL/MX tables must be audited.
Converting SQL/MP Tables to SQL/MX Tables Tables For more information about SQL/MX data types, see the SQL/MX Reference Manual. Primary Keys In both NonStop SQL/MP and NonStop SQL/MX, primary keys specify a column or group of columns whose values uniquely identify the rows in a table. In NonStop SQL/MP, the primary key defines the way data is organized in the underlying Guardian files. In NonStop SQL/MX, the primary key is a constraint defined by the ANSI SQL standard.
Converting SQL/MP Tables to SQL/MX Tables Partitions unique or primary key) in the referenced table. A referential integrity constraint ensures that the foreign key contains only values that match those in a column or set of columns in the referenced table. A unique constraint ensures that a column or set of columns contain only one occurrence of a non-null value or set of values. For more information about SQL/MX constraints, see the SQL/MX Reference Manual.
Converting SQL/MP Tables to SQL/MX Tables Indexes Table 5-3.
Converting SQL/MP Tables to SQL/MX Tables Views Table 5-4. Index Table Attributes in NonStop SQL/MP and NonStop SQL/MX (page 2 of 2) SQL/MP Attributes Supported in SQL/MX? ISLACK No LOCKLENGTH No MAXEXTENTS Yes NOPURGEUNTIL No SERIALWRITES No SLACK No TABLECODE No VERIFIEDWRITES No For more information about SQL/MX indexes, see the SQL/MX Reference Manual.
Converting SQL/MP Tables to SQL/MX Tables SQL/MX DDL Statements and Utilities SQL/MX DDL Statements and Utilities The next subsections list the SQL/MX DDL statements and utilities that are available in NonStop SQL/MX for creating and maintaining a SQL/MX database: SQL/MX DDL Statements on page 5-14 SQL/MX Utilities on page 5-16 SQL/MX DDL Statements Many of the DDL statements that are supported in NonStop SQL/MP are also supported in NonStop SQL/MX. See Table 5-5.
Converting SQL/MP Tables to SQL/MX Tables SQL/MX DDL Statements Table 5-5. SQL/MP and SQL/MX DDL Statements (page 2 of 2) SQL/MP DDL Statements Implemented in SQL/MX? CREATE CONSTRAINT No In NonStop SQL/MX, you cannot create a constraint separately from the table. A constraint is created at the same time that you create the table. CREATE INDEX Yes For more information, see the SQL/MX Reference Manual. CREATE TABLE Yes For more information, see the SQL/MX Reference Manual.
Converting SQL/MP Tables to SQL/MX Tables SQL/MX Utilities DROP SQL DROP SQLMP ALIAS DROP TRIGGER INITIALIZE SQL These new SQL/MX statements influence the privileges and availability of SQL/MX database objects: GRANT GRANT EXECUTE REGISTER CATALOG REVOKE REVOKE EXECUTE UNREGISTER CATALOG For more information about these statements, see the SQL/MX Reference Manual. SQL/MX Utilities SQL/MX utilities are tools that help you manage the files and data in your SQL/MX database.
Converting SQL/MP Tables to SQL/MX Tables SQL/MX Utilities Table 5-6. Comparing SQL/MP Utilities With SQL/MX Utilities (page 2 of 3) Execution Environment SQL/MX Equivalent Execution Environment DOWNGRADE SYSTEM CATALOG SQLCI None N.A.
Converting SQL/MP Tables to SQL/MX Tables SQL/MX Utilities Table 5-6. Comparing SQL/MP Utilities With SQL/MX Utilities (page 3 of 3) Execution Environment SQL/MX Equivalent Execution Environment MODIFY REGISTER SQLCI None N.A. PERUSE SQLCI PERUSE Guardian PURGE SQLCI None N.A.
Converting SQL/MP Tables to SQL/MX Tables SQL/MX Utilities Table 5-7. New SQL/MX Utilities SQL/MX Utility Execution Environment import OSS or MXCI Imports data from an input file in ASCII or UCS2 format into a SQL/MX table. migrate OSS Copies metadata from SQL/MX Release 2.x into SQL/MX Release 3.0 system metadata tables. MODIFY MXCI Performs partition operations on range and hash partitions of SQL/MX tables and indexes.
Converting SQL/MP Tables to SQL/MX Tables Steps for Converting a SQL/MP Database to SQL/MX Tables Steps for Converting a SQL/MP Database to SQL/MX Tables To convert a SQL/MP database to SQL/MX tables, complete the following steps: 1. Plan and design the database, and determine a strategy for table and index partitions, security, TMF, and recovery mechanisms. For more information about planning a SQL/MX database, see the SQL/MX Installation and Management Guide. 2. Create the SQL/MX database: a.
Converting SQL/MP Tables to SQL/MX Tables Migrating Data From a SQL/MP Database to a SQL/MX Database Migrating Data From a SQL/MP Database to a SQL/MX Database After creating the SQL/MX database, you are ready to migrate data from SQL/MP tables into SQL/MX tables. To avoid disrupting applications that access the SQL/MP database, plan to migrate the data when business traffic and system demands are low.
Converting SQL/MP Tables to SQL/MX Tables Using INSERT-SELECT to Copy Data Using INSERT-SELECT to Copy Data Because NonStop SQL/MX supports DML statements that access both SQL/MP and SQL/MX tables, you can use a simple INSERT-SELECT statement to copy data from a SQL/MP table into a SQL/MX table. Example of Using INSERT-SELECT to Copy Data This example copies the contents of the SQL/MP table MPCAT.SALES.CUSTOMER into the SQL/MX table MXCAT.SALES.CUSTOMER: INSERT INTO mxcat.sales.
Converting SQL/MP Tables to SQL/MX Tables Using DataLoader/MX to Copy Data Steps for Using DataLoader/MX To use DataLoader/MX to load data from a SQL/MP table into a SQL/MX table, complete the following steps: 1. Create a customized DataLoader executable. The customized DataLoader executable converts data from the format provided by the SQLCI COPY command into the format required by the import utility. 2.
Converting SQL/MP Tables to SQL/MX Tables Using DataLoader/MX to Copy Data long* RecOutLenMax, long* RecOutLen, short* Status ) { int *i; double *f; i = (int *) RecIn; f = (double *) (RecIn + 4); sprintf(RecOut, "%5d%10f%5s", *i, *f,(char *) (RecIn + 12)); *RecOutLen = 20; /* size of output buffer*/ *Status = 2; /*output is in new buffer*/ } b. To create a customized DataLoader executable from the preceding source file, issue this command: $ build.sh myexits.
Converting SQL/MP Tables to SQL/MX Tables Using Third-Party Data Replication Tools col=c2,6,10 col=c3,16,5 For more information about DataLoader/MX, see the DataLoader/MX Reference Manual. For more information about import and the format file, see the SQL/MX Reference Manual. Using Third-Party Data Replication Tools Several third-party data replication tools can copy data from a SQL/MP database into a SQL/MX database.
Converting SQL/MP Tables to SQL/MX Tables Updating Table Statistics HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001 5-26
6 Falling Back to NonStop SQL/MP This chapter describes falling back from NonStop SQL/MX to NonStop SQL/MP. Plan for a potential fallback to NonStop SQL/MP before you install NonStop SQL/MX and start developing SQL/MX applications and database objects. That way, if necessary, you can fall back from NonStop SQL/MX more easily. For more information, see Planning for Fallback on page 3-3.
Falling Back to NonStop SQL/MP Steps for Falling Back to NonStop SQL/MP ------------------------------ ------------------ --------- DEFINITION_SCHEMA_VERSION_1200 SYSTEM_SQLJ_SCHEMA SYSTEM_SCHEMA SYSTEM_DEFAULTS_SCHEMA MXCS_SCHEMA DEFINITION_SCHEMA_VERSION_1200 SCH DEFINITION_SCHEMA_VERSION_1200 SALES PERSNL INVENT NONSTOP_SQLMX_FIGARO NONSTOP_SQLMX_FIGARO NONSTOP_SQLMX_FIGARO NONSTOP_SQLMX_FIGARO NONSTOP_SQLMX_FIGARO CAT CAT SAMDBCAT SAMDBCAT SAMDBCAT SAMDBCAT \FIGARO \FIGARO \FIGARO \FIGARO \FIGARO
Falling Back to NonStop SQL/MP Steps for Falling Back to NonStop SQL/MP 4. To uninstall NonStop SQL/MX and reclaim more space on your system: a. Remove SQL/MX user modules and DDL files containing SHOWDDL output: Note. This example uses the system default locations for these files. cd rm cd rm /usr/tandem/sqlmx/USERMODULES * /usr/tandem/sqlmx/ddl * b. Remove the SQL/MX anchor file. The InstallSqlmx installation script creates an anchor file that contains the locations of system metadata tables.
Falling Back to NonStop SQL/MP Steps for Falling Back to NonStop SQL/MP HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001 6-4
A Access options, STABLE access 25 Aggregate predicates 29 ANSI compliance 2 Assistance 15 Auto-abort 20 Automatic recompilation, warnings 55 B BEGIN WORK statement 20 Business objectives, determining 4 C CA release See Controlled availability (CA) release Capacity planning NonStop SQL/MP to NonStop SQL/MX 2 CAST function and descriptor area 48 data conversion 44 Catalogs, user 5 Character sets 34 Clustering keys 10 Coexistence, NonStop SQL/MP and NonStop SQL/MX 2 Collations 35, 13 Column names 9 COMMIT WOR
Embedded SQL/MP constructs, migrating to NonStop SQL/MX 41 Embedded SQL/MP statements, converting to NonStop SQL/MX 45 Embedded SQL/MX applications managing modules 57 Error handling, differences between NonStop SQL/MP and NonStop SQL/MX 49 ETK See HP Enterprise Toolkit (ETK) EXCLUSIVE MODE 26 EXECUTE statement 46 Executor 4 F Fallback definition of 1 falling back to NonStop SQL/MP 1–3 planning 3 procedure 1 FETCH statement 47 File attributes 8 Functions migrating to NonStop SQL/MX 26 SETSCALE 42 G GA relea
Migration paths new NonStop hardware 4 SQL/MP applications to SQL/MX applications 3 Modules managing 57 removing after fallback 3 versions 11 Multivalue predicates See Row-value constructors MXV See SQL/MX system software version (MXV) N National character set 34 NCHAR 34 Nonaudited SQL/MX tables, accessing 21 NonStop Solutions Development and Implementation (SDI) 15 NonStop SQL/MP coexistence with NonStop SQL/MX 2, 3 migrating to NonStop SQL/MX 1 NonStop SQL/MX advantages of 2, 1 coexistence with NonStop S
REVOKE statement 7 ROLLBACK WORK statement 20 Rowsets, compound 3 Row-value constructors 30, 60 S Safeguard security management facility 7 Scalability 5 Schemas comparison between NonStop SQL/MP and NonStop SQL/MX 5 version 8, 9 Scout for NonStop Servers 6 SDI See NonStop Solutions Development and Implementation (SDI) SELECT statements migrating to NonStop SQL/MX 23 placement of GROUP BY clause 24 table references 23 SERIALIZABLE access 25 SETSCALE function 42 Shorthand views 13 Similarity checks, differenc
FILEINFO 11 INVOKE 12 LOAD 12 RESET REPORT 13 SHOWCONTROL 13 VERIFY 14 SQLCI scripts 8 SQLCODE mappings 49 SQLDA 47 SQLSA 53 SQLSTATE mappings 49 STABLE access comparison of NonStop SQL/MP and NonStop SQL/MX 25 description of 25 EXCLUSIVE MODE 26 READ COMMITTED 25 Statistics SQLSA 53 updating 59, 25 Stored procedures in Java (SPJs) 4 String literal delimiters 33 System catalog 5 T Tables 8 Testing, guidelines 14 Third-party data replication tools 25 T-number 4 Transaction control statements, migrating to No
HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001 Index-6
Legal Notices Legal Notices 1 What’s New in This Manual vii Manual Information vii New and Changed Information vii About This Manual ix Audience ix What This Manual Does Not Cover Organization x Related Documentation x Notation Conventions xiii ix 1.
3. Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX SQL/MX Database Object Versions 2-8 SQL/MX Query Plan and Module Versions 2-10 Interoperability Across Schema Versions 2-11 2-11 3.
5.
6.
Figures Figures Figure 1-1. Figure 1-2.
Figures HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001 2
Tables Tables Table 1-1. Table 1-2. Table 1-3. Table 1-4. Table 1-5. Table 1-6. Table 1-7. Table 1-8. Table 1-9. Table 1-10. Table 2-1. Table 2-2. Table 2-3. Table 4-1. Table 4-2. Table 4-3. Table 4-4. Table 4-5. Table 4-6. Table 4-7. Table 4-8. Table 4-9. Table 4-10. Table 4-11. Table 4-12. Table 4-13. Table 4-14. Table 4-15. Table 5-1. Table 5-2. Table 5-3. Table 5-4. Table 5-5. Table 5-6. Table 5-7.
Tables Table 5-8.
