HP NonStop SQL/MX 2.3.x to SQL/MX 3.0 Database and Application Migration Guide Abstract This manual explains how to migrate databases and applications from HP NonStop SQL/MX 2.3.x to SQL/MX Release 3.0 and how to manage different versions of NonStop SQL/MX. 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 666210-001 NonStop SQL/MX Release 3.0 June 2011 666210-002 NonStop SQL/MX Release 3.
Legal Notices Copyright 2013 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.
What’s New in This Manual Manual Information HP NonStop SQL/MX 2.3.x to SQL/MX 3.0 Database and Application Migration Guide Abstract This manual explains how to migrate databases and applications from HP NonStop SQL/MX 2.3.x to SQL/MX Release 3.0 and how to manage different versions of NonStop SQL/MX. 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.
What’s New in This Manual Modified Installing SQL/MX 2.x on page 5-7. HP NonStop SQL/MX 2.3.x to SQL/MX 3.
HP NonStop SQL/MX 2.3.x to SQL/MX 3.0 Database and Application Migration Guide Index Legal Notices Examples Figures Tables 1 What’s New in This Manual v Manual Information v New and Changed Information v About This Manual vii Audience vii What This Manual Does Not Cover Organization viii Related Documentation viii Notation Conventions x vii 1. Overview of SQL/MX Migration Migration Terminology 1-1 Upgrading to SQL/MX Release 3.
Contents 3. Version Management and Interoperability Working with v3000 and v1200 objects in SQL/MX Release 3.0 2-7 Conversions from numeric to char or varchar 2-7 Using the read-only transaction access mode 2-10 Recommendations for Migrating to SQL/MX Release 3.x 2-13 3.
Index Contents Installing SQL/MX 2.x 5-7 Migrating Applications after System Fallback Changes Required After Falling Back 5-7 5-7 Index Examples Figures Figure 2-1. Figure 2-2. Figure 2-3. Figure 2-4. Figure 2-5. Figure 2-6. Figure 2-7. Figure 2-8. Figure 2-9. Figure 5-1. Figure 5-2. Figure 5-3. Migration Path 2-1 Falling Back from SQL/MX 3.0 to SQL/MX 2.3.4 2-2 Falling back to a SQL/MX 2.x version earlier than SQL/MX 2.3.
Contents HP NonStop SQL/MX 2.3.x to SQL/MX 3.
About This Manual This manual explains how to migrate databases and applications from NonStop SQL/MX 2.3.x to NonStop SQL/MX Release 3.0 and how to manage different versions of 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, references to SQL/MX Release 2.x indicate SQL/MX Release 2.0, SQL/MX Release 2.1, SQL/MX Release 2.
Organization About This Manual Organization Section 1, Overview of SQL/MX Migration Defines migration terminology, describes possible migration paths to NonStop SQL/MX, provides planning considerations, and describes where you can go for further assistance. Section 2, Considerations for upgrading to SQL/MX Release 3.0 Discusses the factors that you must consider before upgrading to SQL/MX Release 3.0.
About This Manual Related Documentation Connectivity Manuals SQL/MX Connectivity Service Manual Describes how to install and manage the HP NonStop SQL/MX Connectivity Service (MXCS), which enables applications developed for the Microsoft Open Database Connectivity (ODBC) application programming interface (API) and other connectivity APIs to use NonStop SQL/MX.
About This Manual Notation Conventions SQL/MX Queuing and Publish/Subscribe Services Describes how NonStop SQL/MX integrates transactional queuing and publish/subscribe services into its database infrastructure. SQL/MX Guide to Stored Procedures in Java Describes how to use stored procedures that are written in Java within NonStop SQL/MX. Online Help Reference Help Overview and reference entries from the SQL/MX Reference Manual.
About This Manual General Syntax Notation 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. Computer type letters within text indicate C and Open System Services (OSS) keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: myfile.c italic computer type.
General Syntax Notation About This Manual An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: "s-char…" Punctuation. Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. For example: error := NEXTFILENAME ( file-name ) ; LISTOPENS SU $process-name.
Notation for Messages About This Manual !o:i. In procedure calls, the !o:i notation follows an output buffer parameter that has a corresponding input parameter specifying the maximum length of the output buffer in bytes. For example: error := FILE_GETINFO_ ( filenum , [ filename:maxlen ] ) ; !i !o:i Notation for Messages This list summarizes the notation conventions for the presentation of displayed messages in this manual. Bold Text. Bold text in an example indicates user input typed at the terminal.
About This Manual Change Bar Notation either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example: obj-type obj-name state changed to state, caused by { Object | Operator | Service } process-name State changed from old-objstate to objstate { Operator Request. } { Unknown. } | Vertical Line. A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces.
1 Overview of SQL/MX Migration This section introduces the process of migrating to NonStop SQL/MX and discusses these topics: Migration Terminology on page 1-1 Upgrading to SQL/MX Release 3.0 on page 1-1 Planning for Migration on page 1-2 Guidelines for Conducting the Migration on page 1-2 Assistance With Migration on page 1-4 Migration Terminology You must be familiar with the following terms, which are used throughout the manual: Version.
Planning for Migration Overview of SQL/MX Migration compliance is increased for numeric to character conversions. The conversion of numeric columns to character columns fails if truncation occurs. In SQL/MX Release 2.x, truncation during conversion does not cause an error. You must review and test applications for the presence of truncation. For more information, see Conversions from numeric to char or varchar on page 2-7.
Overview of SQL/MX Migration Conduct the Initial Migration in a Test Environment Conduct the Initial Migration in a Test Environment To avoid disrupting the production database and daily business operations, isolate key applications and database objects to migrate and conduct the initial migration in a test environment. The test environment should simulate the production environment as much as possible.
Overview of SQL/MX Migration Assistance With Migration Assistance With Migration Specific strategies for migrating to NonStop SQL/MX depend on your unique database environment and business needs. Without the help of migration experts and special tools, determining and implementing a migration strategy can be time-consuming and costly.
Overview of SQL/MX Migration Application Migration Note. SQL/MX Release 2.3.x metadata (v 1200) for existing applications and data objects in existing catalogs can co-exist on a single system with SQL/MX Release 3.0 metadata (v 3000) for objects in newly created catalogs. To use version 3000 metadata for existing objects, all existing version 1200 metadata must be upgraded and embedded applications recompiled.
Overview of SQL/MX Migration Application Migration HP NonStop SQL/MX 2.3.x to SQL/MX 3.
2 Considerations for upgrading to SQL/MX Release 3.0 This section discusses the factors that you must consider before upgrading to SQL/MX Release 3.0. This section addresses the following topics: Migration and fallback paths on page 2-1 Compatibility of SQL/MX 3.0 systems on page 2-2 Migrating applications on page 2-4 Migrating databases on page 2-4 Upgrading sequence on page 2-4 Working with v3000 and v1200 objects in SQL/MX Release 3.
Compatibility of SQL/MX 3.0 systems Considerations for upgrading to SQL/MX Release 3.0 Figure 2-2. Falling Back from SQL/MX 3.0 to SQL/MX 2.3.4 SQL/MX R3.0 SQL/MX R2.3.4 Falling back to a supported SQL/MX 2.x version earlier than SQL/MX 2.3.4: To fall back from SQL/MX Release 3.0 to a supported SQL/MX 2.x version earlier than SQL/MX Release 2.3.4, you must first fall back to SQL/MX Release 2.3.4 and then to the required version, as shown in Figure 2-3. Figure 2-3. Falling back to a SQL/MX 2.
Considerations for upgrading to SQL/MX Release 3.0 Compatibility of SQL/MX 3.0 systems Figure 2-4. Compatibility of SQL/MX versions R2.3.x R3.0 R3.0 R3.0 If your network includes multiple versions of SQL/MX, such as SQL/MX Release 2.3.3 and SQL/MX Release 2.3.4, you must migrate all of them to SQL/MX Release 3.0 to ensure compatibility, as shown in Figure 2-5. Figure 2-5. Compatibility of SQL/MX systems after migration Before migration After migration R2.3.x R2.3.y R3.0 R3.
Considerations for upgrading to SQL/MX Release 3.0 Migrating applications Note. BR2 does not pose any separate limitations related to backup and restore of database objects. Migrating applications This section describes the features and limitations related to migration of applications. SQL/MX Release 3.0 does not support SQL/MX modules and query plans from earlier versions of SQL/MX. You must recompile (mxcmp) existing modules on SQL/MX Release 3.0. Note.
Upgrading sequence Considerations for upgrading to SQL/MX Release 3.0 Figure 2-6. Sequence to upgrade from SQL/MX 2.3.x to SQL/MX 3.0 App1 Start cat1 sch1 R2.3.x V1200 Starting version Stop applications Stop TMF, Start, Stop TMF Install R3.0 and cold load App1 cat1 sch1 R3.0 V1200 Resulting version Start TMF Run mxtool fixrcb Recompile applications Take new TMF dumps HP NonStop SQL/MX 2.3.x to SQL/MX 3.
Upgrading sequence Considerations for upgrading to SQL/MX Release 3.0 After upgrading to SQL/MX Release 3.0, you can use the SQL/MX database in one of the following ways: Upgrade scenario 1 - New features offered by SQL/MX Release 3.0 are not required: If you do not want to use any of the new features offered by SQL/MX Release 3.0, no further changes are required after upgrading. Figure 2-7. New features of SQL/MX Release 3.0 are not required R3.0 App1 R2.
Considerations for upgrading to SQL/MX Release 3.0 Figure 2-9. Coexistence of new and old databases App2 App1 V1200 cat1 sch1 cat2 sch2 V3000 App3 Working with v3000 and v1200 objects in SQL/MX Release 3.0 In SQL/MX Release 3.0, a catalog does not have an explicit version. A catalog can contain either v1200 or v3000 schemas, but not both. A CREATE SCHEMA statement will create a new schema of the same version as those in the catalog. By default, schemas created in new catalogs will be v3000.
Considerations for upgrading to SQL/MX Release 3.0 Conversions from numeric to char or varchar Example 1 >>create table tnumeric (a numeric (18,6)); --- SQL operation complete. >>insert into tnumeric values (123456789012.345678); --- 1 row(s) inserted. >>select cast(a * 10 as char(18)) from tnumeric; *** ERROR[8402] A string overflow occurred during the evaluation of a characterexpression.
Considerations for upgrading to SQL/MX Release 3.0 Conversions from numeric to char or varchar Example 2: >>create table tnumeric (a numeric (18,6)); --- SQL operation complete. >> >>insert into tnumeric values (3.0),(123456789012.345678); --- 2 row(s) inserted. >> >>select cast(a * 10 as char(18)) from tnumeric; (EXPR) ------------------ 30.000000 *** ERROR[8402] A string overflow occurred during the evaluation of a character expression.
Considerations for upgrading to SQL/MX Release 3.0 Using the read-only transaction access mode *** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:DECIMAL SIGNED(REC_DECIMAL_LSE) Source Val ue:0x2B0001050204010507080705030203080803060502070906080209090706050207090608 04 to Target Type:CHAR(REC_BYTE_F_ASCII). --- 1 row(s) selected.
Considerations for upgrading to SQL/MX Release 3.0 Using the read-only transaction access mode The previously prepared SELECT statement is executed. The transaction started by the SELECT statement is not committed. A WRITE operation (INSERT, UPDATE, or DELETE statement) is performed. The following set of operations demonstrate the error scenario for ODBC or JDBC T4 applications: The transaction access mode is set to read-only. A SELECT statement is prepared and executed.
Considerations for upgrading to SQL/MX Release 3.0 Using the read-only transaction access mode The following is an application code sample: conn.setReadOnly(true); PreparedStatement pstmt = null; String sql = "select ORDER_AMT from ORDERTBL where ORDER_QTY > 100 "; pstmt = conn.prepareStatement(sql); ResultSet rss = pstmt.executeQuery(); conn.setReadOnly(false); stmt.executeUpdate("delete from ORDERTBL"); Set the transaction access mode to read-only using the java.sql.Connection.
Considerations for upgrading to SQL/MX Release 3.0 Recommendations for Migrating to SQL/MX Release 3.x The following is an application code sample: conn.setReadOnly(true); String sql = "select ORDER_AMT from ORDERTBL where ORDER_QTY > 100 "; stmt=conn.createStatement(); ResultSet rss = stmt.executeQuery(sql); conn.setReadOnly(false); stmt.executeUpdate("delete from ORDERTBL"); Set the transaction access mode to read-only. Executing the SELECT statement starts a read-only transaction.
Considerations for upgrading to SQL/MX Release 3.0 Recommendations for Migrating to SQL/MX Release 3.x Do not change the access mode of a read-only transaction to readwrite and then perform WRITE operations. Commit the read-only transaction before changing the access mode to read-write and then perform WRITE operations. HP NonStop SQL/MX 2.3.x to SQL/MX 3.
3 Version Management and Interoperability This section covers these topics: NonStop SQL/MX Releases on page 3-1 Interoperability of NonStop SQL/MX Releases on page 3-7 Versions of SQL/MX Software Components on page 3-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.
SQL/MX Releases on Systems Running H-Series RVUs and J-Series RVUs Version Management and Interoperability Release 3.0. Table 3-1. SQL/MX Releases on Systems Running H-Series or J-series RVUs SQL/MX Release Product Version Identifier SPR Identifier Availability Date of Initial Release 3.0 H30 N/A General 02/2011 Delivery Supported RVUs Scout or H06.22 SUT H06.22, J06.11 HP NonStop SQL/MX 2.3.x to SQL/MX 3.
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: 1. The first number indicates a major release, which introduces major new functionality and a new SQL version. 2.
Version Management and Interoperability Naming Scheme for SQL/MX Releases Major and Incremental Releases Major releases introduce significant new functionality. These releases occur less frequently and usually require you to take specific action to use the new functionality, such as perform an upgrade operation or alter existing applications. Incremental releases introduce incremental changes and some new functionality, which does not significantly affect existing applications.
Version Management and Interoperability Product Version and SPR Identifiers RRR is the three-digit SQL/MX release number (for example, 300 for SQL/MX Release 3.0.0). MMDD is the final build date for the product, including the month and day (for example, 1130 for November 30). For example, this VPROC command displays information for the T1051 product that is associated with SQL/MX Release 3.0.
Version Management and Interoperability Delivery of SQL/MX Releases An EAP release introduces a new product to customers at a very early stage and is a type of test program conducted at a customer site. For more information about these types of releases, see the Managing Software Changes manual. Delivery of SQL/MX Releases Each SQL/MX release is delivered as a set of Software Product Revisions (SPRs).
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. All systems in the EXPAND network must have SQL/MX 3.0 software installed. 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.
SQL/MX System Software Version (MXV) Version Management and Interoperability Table 3-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 if the object can be accessed by a particular version of NonStop SQL/MX.
SQL/MX Query Plan and Module Versions Version Management and Interoperability Displaying the OFV The following 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 compiled the module. The SQL/MX Release 3.0 compiler produces the module version 3000. For a module to be executed on a node, the module version must be higher than or equal to the earliest supported plan version and lower than or equal to the current plan version. Otherwise, NonStop SQL/MX returns a versioning error. Note. SQL/MX Release 3.0 supports version 3000 plans or modules only.
Version Management and Interoperability HP NonStop SQL/MX 2.3.x to SQL/MX 3.
4 Upgrading to SQL/MX Release 3.0 This section addresses the following topics: Planning for Fallback on page 4-1 Installing SQL/MX Release 3.0 on page 4-1 Implementing the New Functionality on page 4-4 Planning for Fallback Before you install SQL/MX Release 3.0, plan for a fallback to a version of SQL/MX Release 2.x. To fall back to versions earlier than SQL/MX Release 2.3.4, you must first fall back to SQL/MX Release 2.3.4 and then to the required SQL/MX 2.x version.
Upgrading to SQL/MX Release 3.0 Installing SQL/MX 3.0 TMF 3> STOP TMF These steps are necessary to avoid volume recovery when TMF is started after the system upgrade. TMF recovery of SQL/MX database objects will access the RCBs of the affected objects. TMF shutdown must be done because the RCBs of the affected objects are not immediately compatible after an upgrade to SQL/MX Release 3.0. Installing SQL/MX 3.0 1.
Upgrading to SQL/MX Release 3.0 Migrating the Database After System Upgrade Note. The FIXRCB operation does not handle the objects in catalogs that have a manual reference on the local system. RCB fixup for objects in such catalogs must be performed on the system where the automatic reference is located. The FIXRCB operation is available on systems running J06.11 or later J-series RVUs, or H06.22 or later H-series RVUs, and on fallback SPR (H06.21-ANC).
Upgrading to SQL/MX Release 3.0 Implementing the New Functionality Implementing the New Functionality If there is a chance of falling back to SQL/MX Release 2.x, you must defer the implementation of the new functionality until you are sure that fallback is unlikely. HP NonStop SQL/MX 2.3.x to SQL/MX 3.
5 Falling Back to SQL/MX Release 2.x This section describes falling back from SQL/MX Release 3.0 to SQL/MX Release 2.x. You can fall back from SQL/MX Release 3.0 to SQL/MX Release 2.3.4 directly. If you want to fall back to a version earlier than SQL/MX Release 2.3.4, such as SQL/MX Release 2.2, you must first fall back to SQL/MX Release 2.3.4 and then to the required SQL/MX 2.x version. Note. When using new features, if you encounter a problem that is specific to SQL/MX Release 3.
Fallback sequence Falling Back to SQL/MX Release 2.x Figure 5-1. When v1200 objects are not upgraded and new objects are not created on SQL/MX 3.0 App1 Start Stop applications cat1 sch1 R3.0 V1200 Starting version Stop TMF, Start, Stop TMF Install R2.3.4 and fallback SPRs App1 cat1 sch1 R2.3.4 V1200 Resulting version Run mxtool fixrcb Recompile applications Install 2.x App1 cat1 sch1 R2.x Final version HP NonStop SQL/MX 2.3.x to SQL/MX 3.
Fallback sequence Falling Back to SQL/MX Release 2.x If you have created databases after upgrading to SQL/MX Release 3.0, you must fall back as shown in Figure 5-2. Figure 5-2. If new database objects are created on SQL/MX Release 3.0 Start Stop applications R3.0 App1 App2 cat1 sch1 cat2 sch2 Copy and drop unsupported objects V1200 Run downgrade command Starting version V3000 Stop TMF, Start, Stop TMF Install R2.3.4 and fallback SPRs R2.3.
Fallback Considerations Falling Back to SQL/MX Release 2.x Figure 5-3. If v1200 objects are upgraded to v3000 App1 Start Stop applications cat1 sch1 R3.0 V3000 Starting version Run downgrade command Stop TMF, Start, Stop TMF Install R2.3.4 and fallback SPRs App1 cat1 sch1 R2.3.4 V1200 Resulting version Run mxtool fixrcb Recompile applications Install 2.x App1 cat1 sch1 R2.
Falling Back to SQL/MX Release 2.x Preinstallation Applications that are compiled using the SQL/MX Release 3.0 compiler need to be pre-processed, recompiled, and linked with an earlier release of the SQL/MX compiler after the fallback. Note. Recompiling programs may result in different execution plans leading to a change in performance. The online dumps taken on SQL/MX Release 3.0 cannot be used by TMF recovery on SQL/MX Release 2.x.
Falling Back to SQL/MX Release 2.x Stopping TMF Stopping TMF After a system fallback, stop TMF twice with no database activity between the two stops: TMF 1> STOP TMF TMF 2> START TMF TMF 3> STOP TMF These steps are necessary to avoid volume recovery when TMF is started after the system fallback. TMF recovery of SQL/MX database objects will access the RCBs of the affected objects.
Falling Back to SQL/MX Release 2.x Installing SQL/MX 2.x This command regenerates RCBs for SQL/MX objects in all catalogs that were originally created on the system. If the system fallback is performed for a clustered or networked environment, you must fall back all affected systems simultaneously and must run the mxtool fixrcb command on each involved system.
Falling Back to SQL/MX Release 2.x Changes Required After Falling Back HP NonStop SQL/MX 2.3.x to SQL/MX 3.
Index A recompiling SQL/MX Release 2.
N Index planning for terminology 1-1, 2-1 Migration paths new NonStop hardware 1-4 upgrade to SQL/MX Release 2.
T Index software version identifiers 3-8 SQL/MX releases availability 3-5 delivery 3-6 H-series RVUs 3-2 interoperability 3-7 naming scheme 3-3 SQL/MX software components, version identification 3-7 SQL/MX system software version (MXV) 3-8 product version identifier 3-4 SPR identifier 3-4 SQL/MX release number 3-4 T Testing, guidelines 1-15 T-number 3-4 U Upgrade definition of 1-1, 2-2 planning for 4-1 upgrading to a newer version of SQL/MX Release 2.
Index HP NonStop SQL/MX 2.3.x to SQL/MX 3.
