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

ManualsBrandsHP ManualsServerHP NonStop G-Series

HP NonStop SQL/MX

Database and

Application

Migration Guide

Abstract

This manual explains how to migrate databases and applications on an HP NonStop™

system to NonStop SQL/MX and how to manage different versions of NonStop

SQL/MX.

Product Version

NonStop SQL/MX Release 2.0, 2.1, 2.2, 2.3, 2.3.1, 2.3.2, 2.3.3, 2.3.4

Supported Release Version Updates (RVUs)

This publication supports J06.03 and all subsequent J-series RVUs, H06.04 and all

subsequent H-series RVUs, and G06.23 and all subsequent G-series RVUs, until

otherwise indicated by its replacement publications.

Part Number Published

540435-005 April 2010

Summary of content (223 pages)

PAGE 1
HP NonStop SQL/MX Database and Application Migration Guide Abstract This manual explains how to migrate databases and applications on an HP NonStop™ system to NonStop SQL/MX and how to manage different versions of NonStop SQL/MX. Product Version NonStop SQL/MX Release 2.0, 2.1, 2.2, 2.3, 2.3.1, 2.3.2, 2.3.3, 2.3.4 Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs, H06.04 and all subsequent H-series RVUs, and G06.
PAGE 2
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.
PAGE 3
Legal Notices © Copyright 2010 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.
PAGE 4
PAGE 5
HP NonStop SQL/MX Database and Application Migration Guide Index Figures Tables Legal Notices What’s New in This Manual xi Manual Information xi New and Changed Information xi About This Manual xiii Audience xiii What This Manual Does Not Cover Organization xiv Related Documentation xv Notation Conventions xviii xiii 1. Overview of SQL/MX Migration Migration Terminology 1-1 Possible Migration Paths to NonStop SQL/MX 1-2 Upgrading to a Newer SQL/MX Release 1-2 Migrating From SQL/MX Release 1.
PAGE 6
1. Overview of SQL/MX Migration (continued) Contents 1. Overview of SQL/MX Migration (continued) NonStop Solutions Development and Implementation 1-20 Part I. Upgrading and Maintaining Different Versions of NonStop SQL/MX 2.
PAGE 7
4. Falling Back to an Earlier Version of SQL/MX Release 2.x Contents 4. Falling Back to an Earlier Version of SQL/MX Release 2.x Fallback Considerations 4-1 Steps for Falling Back SQL/MX Release 2.x Changes Required After Falling Back 4-2 4-1 Part II. Migrating From SQL/MX Release 1.8 to SQL/MX Release 2.x 5. Preparing to Migrate From SQL/MX Release 1.8 to SQL/MX Release 2.
PAGE 8
Contents 7. Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x 7. Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x Planning to Execute SQL/MX Release 1.8 Applications in SQL/MX Release 2.x 7-1 Adapting SQL/MX Release 1.8 Applications to SQL/MX Release 2.
PAGE 9
Contents 9. Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Part III. Migrating From NonStop SQL/MP to NonStop SQL/MX 9.
PAGE 10
Contents 10. Converting SQL/MP Applications to SQL/MX Applications (continued) 10.
PAGE 11
10. Converting SQL/MP Applications to SQL/MX Applications (continued) Contents 10. Converting SQL/MP Applications to SQL/MX Applications (continued) Steps for Moving an Application to Production 10-61 11.
PAGE 12
Figures Contents Figures Figure 1-1. Figure 1-2. Figure 1-3. Coexistence of NonStop SQL/MP and NonStop SQL/MX 1-4 Migrating SQL/MP Applications to SQL/MX Applications 1-4 Migrating SQL/MP Database Objects to SQL/MX Database Objects 1-5 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 1-11. Table 1-12. Table 2-1. Table 2-2. Table 2-3. Table 2-4. Table 2-5. Table 6-1. Table 7-1. Table 10-1. Table 10-2. Table 10-3. Table 10-4.
PAGE 13
Tables (continued) Contents Tables (continued) Table 10-11. Table 10-12. Table 10-13. Table 10-14. Table 10-15. Table 11-1. Table 11-2. Table 11-3. Table 11-4. Table 11-5. Table 11-6. Table 11-7. Table 11-8.
PAGE 14
Contents HP NonStop SQL/MX Database and Application Migration Guide —540435-005 x
PAGE 15
What’s New in This Manual Manual Information HP NonStop SQL/MX Database and Application Migration Guide Abstract This manual explains how to migrate databases and applications on an HP NonStop™ system to NonStop SQL/MX and how to manage different versions of NonStop SQL/MX. Product Version NonStop SQL/MX Release 2.0, 2.1, 2.2, 2.3, 2.3.1, 2.3.2, 2.3.3, 2.3.4 Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs, H06.
PAGE 16
What’s New in This Manual ° Changes to the 540435-002 Manual Compilation on an SQL/MX Release 2.0 Node from page 2-15 Changes to the 540435-002 Manual This is a new manual.
PAGE 17
About This Manual This manual explains how to migrate databases and applications on a NonStop system to NonStop SQL/MX 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, 2.1, 2.
PAGE 18
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.
PAGE 19
Related Documentation About This Manual Related Documentation This manual is part of the HP NonStop SQL/MX library of manuals, which includes: Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Quick Start Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual Installation Guides SQL/MX Installation and Management Guide NSM/web Installation Guide Connectivity Manuals SQL/MX Connectivity Service Manual Migration Guides SQL/MX Database and Application Migration Guide
PAGE 20
Related Documentation About This Manual Introductory Guides SQL/MX Comparison Guide for SQL/MP Users Describes SQL differences between NonStop SQL/MP and NonStop SQL/MX. SQL/MX Quick Start Describes basic techniques for using SQL in the SQL/MX conversational interface (MXCI). Includes information about installing the sample database. Reference Manuals SQL/MX Reference Manual Describes the syntax of SQL/MX statements, MXCI commands, functions, and other SQL/MX language elements.
PAGE 21
About This Manual Related Documentation Data Management Guides SQL/MX Data Mining Guide Describes the SQL/MX data structures and operations to carry out the knowledge-discovery process. SQL/MX Report Writer Guide Describes how to produce formatted reports using data from an SQL/MX database. DataLoader/MX Reference Manual Describes the features and functions of the DataLoader/MX product, a tool to load SQL/MX databases.
PAGE 22
About This Manual Notation Conventions These manuals are part of the SQL/MP library of manuals and are essential references for information about SQL/MP Data Definition Language (DDL) and SQL/MP installation and management: Related SQL/MP Manuals SQL/MP Reference Manual Describes the SQL/MP language elements, expressions, predicates, functions, and statements. SQL/MP Installation and Management Guide Describes how to plan, install, create, and manage an SQL/MP database.
PAGE 23
About This Manual General Syntax Notation [ ] Brackets. Brackets enclose optional syntax items. For example: TERM [\system-name.]$terminal-name INT[ERRUPTS] A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: FC [ num ] [ -num ] [ text ] K [ X | D ] address { } Braces.
PAGE 24
Notation for Messages About This Manual Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: CALL STEPMOM ( process-id ) ; If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: $process-name.#su-name Line Spacing.
PAGE 25
Notation for Messages About This Manual Bold Text. Bold text in an example indicates user input typed at the terminal. For example: ENTER RUN CODE ?123 CODE RECEIVED: 123.00 The user must press the Return key after typing the input. Nonitalic text. Nonitalic letters, numbers, and punctuation indicate text that is displayed or returned exactly as shown. For example: Backup Up. lowercase italic letters. Lowercase italic letters indicate variable items whose values are displayed or returned.
PAGE 26
About This Manual Change Bar Notation % Percent Sign. A percent sign precedes a number that is not in decimal notation. The % notation precedes an octal number. The %B notation precedes a binary number. The %H notation precedes a hexadecimal number. For example: %005400 %B101111 %H2F P=%p-register E=%e-register Change Bar Notation Change bars are used to indicate substantive differences between this manual and its preceding version.
PAGE 27
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 Possible Migration Paths to NonStop SQL/MX on page 1-2 Planning for Migration on page 1-9 Guidelines for Conducting the Migration on page 1-19 Assistance With Migration on page 1-20 Migration Terminology You should become familiar with these terms, which are used throughout the manual: Migration.
PAGE 28
Overview of SQL/MX Migration Possible Migration Paths to NonStop SQL/MX SQL/MX Release 1.8. SQL/MX Release 1.8 and SQL/MX Release 1.8.5, unless otherwise specified. SQL/MX Release 2.x. SQL/MX Release 2.0, 2.1, 2.2, and subsequent releases until otherwise indicated in a replacement publication. Possible Migration Paths to NonStop SQL/MX There are several paths for migrating to NonStop SQL/MX.
PAGE 29
Overview of SQL/MX Migration Migrating From NonStop SQL/MP to NonStop SQL/MX information, see Section 6, Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x. Some changes might be required to SQL/MX Release 1.8 applications in the SQL/MX Release 2.x environment. For more information, see Section 7, Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x.
PAGE 30
Migrating From NonStop SQL/MP to NonStop SQL/MX Overview of SQL/MX Migration 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.
PAGE 31
Overview of SQL/MX Migration Migrating From NonStop SQL/MP to NonStop SQL/MX 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.
PAGE 32
Migrating From NonStop SQL/MP to NonStop SQL/MX Overview of SQL/MX Migration Phased migration involves careful and gradual movement of your SQL/MP applications and database to NonStop SQL/MX. While the SQL/MP production database continues to operate as the primary database, preselected applications and portions of the database are migrated in phases to NonStop SQL/MX.
PAGE 33
Overview of SQL/MX Migration Migrating From Enscribe to NonStop SQL/MX Migrating From Enscribe to NonStop SQL/MX If you are still using legacy Enscribe databases and applications, consider migrating them to NonStop SQL/MX, which is an industry-standard relational database. Currently, there is no automated tool to convert Enscribe databases and applications directly to NonStop SQL/MX. If you want to migrate from Enscribe to NonStop SQL/MX, follow these steps: 1.
PAGE 34
Risks of Different Migration Paths Overview of SQL/MX Migration Table 1-2. Risks of Different Migration Paths Migration Path Undesired Consequences 1. Migrating SQL/MP database objects to SQL/MX database objects Downtime when migrating database objects and all applications dependent on them 2. Migrating SQL/MP applications to SQL/MX applications Downtime when migrating applications 3. Migrating from SQL/MX Release 1.8 to SQL/MX Release 2.x Downtime when upgrading the system to SQL/MX Release 2.
PAGE 35
Overview of SQL/MX Migration Planning for Migration Planning for Migration Before you begin to migrate applications and databases to NonStop SQL/MX, you must first determine your business objectives and plan the migration.
PAGE 36
Overview of SQL/MX Migration Assessing Your Current Application and Database Environment Table 1-3.
PAGE 37
Overview of SQL/MX Migration Assessing Your Current Application and Database Environment Table 1-4.
PAGE 38
Overview of SQL/MX Migration Assessing Your Current Application and Database Environment Table 1-5.
PAGE 39
Overview of SQL/MX Migration Assessing Your Current Application and Database Environment Table 1-5.
PAGE 40
Overview of SQL/MX Migration Assessing Your Current Application and Database Environment Table 1-7. Development Environment (page 2 of 2) Obtain this information Development and build tools (for example, NonStop tools, TACL macros, Visual Studio/ETK, Borland TDS, and JBuilder) Build commands and scripts by quantity of type Debugging tools and procedures Change control procedures and how change control is managed Table 1-8.
PAGE 41
Overview of SQL/MX Migration Choosing Which Database Applications and Database Objects to Migrate Table 1-9.
PAGE 42
Overview of SQL/MX Migration Choosing Which Database Applications and Database Objects to Migrate Table 1-10. Choosing Which Database Applications to Migrate (page 2 of 2) Obtain this information Can the application be down for a few seconds or more? Is the application transaction intensive? Do you have the source code of the application? If you do not have the application source code, can replacement source code be written quickly? What benefits will be realized by migrating the application? Table 1-11.
PAGE 43
Overview of SQL/MX Migration Planning the New Application and Database Environment Table 1-11.
PAGE 44
Planning the New Application and Database Environment Overview of SQL/MX Migration Table 1-12.
PAGE 45
Guidelines for Conducting the Migration Overview of SQL/MX Migration Migration Plan Plan for fallback Plan for updating disaster recovery or RDF configuration and procedures Plan for validating that migration is successful For more information, see... • • • • • Section 4, Falling Back to an Earlier Version of SQL/MX Release 2.x Section 8, Falling Back to SQL/MX Release 1.
PAGE 46
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.
PAGE 47
Part I. Upgrading and Maintaining Different Versions of NonStop SQL/MX • • • Section 2, Version Management and Interoperability Section 3, Upgrading to a Newer Version of SQL/MX Release 2.x Section 4, Falling Back to an Earlier Version of SQL/MX Release 2.
PAGE 48
Part I.
PAGE 49
2 Version Management and Interoperability This section covers these topics: • • • • • • • Purpose of Versioning on page 2-1 NonStop SQL/MX Releases on page 2-2 Interoperability of NonStop SQL/MX Releases on page 2-8 Versions of SQL/MX Software Components on page 2-9 Managing SQL/MX Database Object Versions on page 2-13 Managing SQL/MX Query Plan Versions on page 2-14 Managing a Mixed-Version Network on page 2-15 Purpose of Versioning Software versioning is a set of mechanisms in NonStop SQL/MX that enabl
PAGE 50
NonStop SQL/MX Releases Version Management and Interoperability NonStop SQL/MX Releases An SQL/MX release identifies a set of SQL/MX products as belonging to a given version of NonStop SQL/MX.
PAGE 51
SQL/MX Releases on Systems Running G-Series RVUs and H-Series RVUs Version Management and Interoperability Table 2-1. SQL/MX Releases on Systems Running G-Series RVUs (page 2 of 2) SQL/MX Release Product Version Identifier SPR Identifier Availability Date of Initial Release 2.0 G10 ABJ *** Controlled 02/2005 Scout or G06.25 and G06.26 SUTs G06.23 and later 2.1.0 G21 ABU Controlled 06/2005 Scout G06.23 and later 2.1.1 G21 ABX **** Controlled 09/2005 Scout or G06.
PAGE 52
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. An SQL/MX release name identifies a set of SQL/MX products as belonging to a given release of NonStop SQL/MX. Starting in SQL/MX Release 2.1.0, each SQL/MX release is identified by a three-part release identifier: SQL/MX Release 2.1.
PAGE 53
Version Management and Interoperability • • • • Naming Scheme for SQL/MX Releases SQL/MX Release 1.8 SQL/MX Release 2.0 SQL/MX Release 2.1 SQL/MX Release 2.2 References in the manual to “SQL/MX Release 1.8” represent SQL/MX Release 1.8, which was first available in G06.18, and SQL/MX Release 1.8.5, which was available later as a set of SPRs, unless otherwise specified. Maintenance Releases Maintenance releases introduce bug fixes and minimal new functionality.
PAGE 54
Version Management and Interoperability Product Version and SPR Identifiers SPR is the three-digit SPR identifier of the release (for example, ACF). For an initial PVU, this field is the same as the product version PNN (for example, H21). RRR is the three-digit SQL/MX release number (for example, 220 for SQL/MX Release 2.2.0). MMDD is the final build date for the product, including the month and day (for example, 1231 for December 31).
PAGE 55
Version Management and Interoperability Delivery of SQL/MX Releases A CA release limits the shipment of a product to a set of customers who agree to specific use conditions, which include migrating to a GA release when it is ready. For example, the SQL/MX DDL license (T0394) has controlled availability for current 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.
PAGE 56
Interoperability of NonStop SQL/MX Releases Version Management and Interoperability Interoperability of NonStop SQL/MX Releases The nodes in an Expand network can run different releases of NonStop SQL/MX. Each release of SQL/MX software can identify other SQL/MX releases with which it is compatible or incompatible. Table 2-4 on page 2-8 shows the interoperability of different SQL/MX releases on NonStop systems running G-series or H-series RVUs.
PAGE 57
Version Management and Interoperability Versions of SQL/MX Software Components 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.
PAGE 58
SQL/MX System Software Version (MXV) Version Management and Interoperability Table 2-5. Software Version Identifiers for SQL/MX Release 2.x Version Item For more Information, see... SQL/MX Release Version Identifier 2.3.4 2.3.3 2.3.2 2.3.1 2.3 2.2 2.1 2.
PAGE 59
Version Management and Interoperability SQL/MX Database Object Versions NonStop SQL/MX can also obtain the object schema versions (OSVs) of SQL/MX database objects on which the system software operates. NonStop SQL/MX uses the OSVs to perform version checking. For example, when an object is opened, the SQL/MX executor compares the OSV for that object with the earliest supported schema version. If the object’s OSV is earlier than the earliest supported schema version, a versioning error is generated.
PAGE 60
Version Management and Interoperability SQL/MX Query Plan and Module Versions The catalog and schema names must be in internal format. For more information, see the SQL/MX Reference Manual. Object Feature Version (OFV) The OFV of an SQL/MX database object represents the lowest version schema that can accommodate the features used by that object. The OFV is computed from the actual features used by a database object, not from the version of the software that creates the database object.
PAGE 61
Managing SQL/MX Database Object Versions Version Management and Interoperability Default Compiler Version Each SQL/MX release has a default version of the SQL/MX compiler. The default compiler is the first SQL/MX compiler that NonStop SQL/MX uses to compile a query execution plan. The default compiler version corresponds to the plan version that the compiler produces.
PAGE 62
Version Management and Interoperability Managing SQL/MX Query Plan Versions Managing SQL/MX Query Plan Versions To manage SQL/MX applications between different releases of NonStop SQL/MX, you need to understand query plan versioning, as described in the following subsections: • • Query Plan Versioning in SQL/MX Releases 2.x on page 2-14 Compiling Mixed-Version Queries on page 2-15 Query Plan Versioning in SQL/MX Releases 2.x In SQL/MX Release 2.
PAGE 63
Version Management and Interoperability Compiling Mixed-Version Queries Compiling Mixed-Version Queries A mixed-version query can be one of these types: • • A query that refers to a database object that is partitioned across several nodes that have different versions of SQL/MX Release 2.x A query that refers to several SQL/MX database objects that have different OSVs Querying a Database Object Partitioned Across Nodes With Different MXVs A mixed-version query from an SQL/MX Release 2.
PAGE 64
Version Management and Interoperability Reasons for Upgrading All Nodes to the Same Version of SQL/MX Release 2.x Reasons for Upgrading All Nodes to the Same Version of SQL/MX Release 2.x On SQL/MX Release 2.0, 2.1, and 2.2 nodes, compiling an SQL/MX application that refers to database objects on nodes that have earlier versions of SQL/MX Release 2.x might produce query execution plans that do not have the performance enhancements of the latest version of SQL/MX Release 2.x.
PAGE 65
3 Upgrading to a Newer Version of SQL/MX Release 2.x This section assumes that you are upgrading from SQL/MX Release 2.0, or later, to a new release of SQL/MX Release 2.x. To upgrade from SQL/MX Release 1.8 to Release 2.x, see Part II, Migrating From SQL/MX Release 1.8 to SQL/MX Release 2.x. This section addresses these topics: • • • Planning for Fallback on page 3-1 Steps for Upgrading SQL/MX Release 2.
PAGE 66
Upgrading to a Newer Version of SQL/MX Release 2.x Implementing New Functionality 3. Install, configure, and start the HP NonStop Transaction Management Facility (TMF), which must be running on the system node where you install NonStop SQL/MX. For more information, see the TMF Planning and Configuration Guide. 4. Install NonStop SQL/MX. For more information, see the SQL/MX Installation and Management Guide. 5. Apply any fallback SPRs that are required for falling back from SQL/MX Release 2.
PAGE 67
4 Falling Back to an Earlier Version of SQL/MX Release 2.x This section describes falling back from a newer version of SQL/MX Release 2.x, such as SQL/MX Release 2.2, to an earlier version of SQL/MX Release 2.x, such as Release 2.1 or 2.0. To fall back to SQL/MX Release 1.8, see Part II, Migrating From SQL/MX Release 1.8 to SQL/MX Release 2.x. If you encounter a serious problem with SQL/MX Release 2.x and decide you need to fall back to a previous version of SQL/MX Release 2.
PAGE 68
Falling Back to an Earlier Version of SQL/MX Release 2.x Changes Required After Falling Back To fall back to an earlier version of SQL/MX Release 2.x: 1. Reinstall the earlier version of SQL/MX Release 2.x. For installation instructions, see the SQL/MX Installation and Management Guide. 2. Perform required changes to your system after falling back. See Changes Required After Falling Back on page 4-2. 3. If you compiled embedded SQL/MX applications and user modules in the newer SQL/MX Release 2.
PAGE 69
Part II. Migrating From SQL/MX Release 1.8 to SQL/MX Release 2.x • • • • Section 5, Preparing to Migrate From SQL/MX Release 1.8 to SQL/MX Release 2.x Section 6, Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Section 7, Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x Section 8, Falling Back to SQL/MX Release 1.
PAGE 70
Part II. Migrating From SQL/MX Release 1.8 to SQL/MX Release 2.
PAGE 71
5 Preparing to Migrate From SQL/MX Release 1.8 to SQL/MX Release 2.x Before migrating from SQL/MX Release 1.8 to SQL/MX Release 2.x, follow these planning guidelines: • • • • • • • • • • Meeting System Hardware Requirements on page 5-1 Meeting Operating System Requirements on page 5-1 Planning for Performance and Capacity on page 5-2 Planning for Fallback on page 5-2 Ordering the Latest Supported Version of SQL/MX Release 2.
PAGE 72
Preparing to Migrate From SQL/MX Release 1.8 to SQL/MX Release 2.x Planning for Performance and Capacity an RVU that supports SQL/MX Release 1.8 (G06.18 through G06.22) before migrating to SQL/MX Release 2.x (G06.23 or later). Planning for Performance and Capacity Before migrating to SQL/MX Release 2.x, use Measure to obtain initial baseline data, such as average query run time, CPU time, and process time. At each phase of the migration, gather the same type of data and analyze it.
PAGE 73
Preparing to Migrate From SQL/MX Release 1.8 to SQL/MX Release 2.x Installing Fallback SPRs Installing Fallback SPRs To ensure a successful fallback, you must install these fallback SPRs (or superseding SPRs) on the SQL/MX Release 1.8 system before migrating to G06.23 (or a later RVU) and SQL/MX Release 2.
PAGE 74
Preparing to Migrate From SQL/MX Release 1.8 to SQL/MX Release 2.x Keeping SQL/MP Metadata Tables Keeping SQL/MP Metadata Tables If you are migrating to SQL/MX Release 2.x from SQL/MX Release 1.8, do not delete the SQL/MP metadata tables until you are sure you do not need to fall back. Deleting these metadata tables prevents a successful fallback. Ordering the Latest Supported Version of SQL/MX Release 2.x Order the latest version of SQL/MX Release 2.x that is supported by the RVU of the system.
PAGE 75
Preparing to Migrate From SQL/MX Release 1.8 to SQL/MX Release 2.x Preparing to Migrate Applications Preparing to Migrate Applications Planning activities for migrating applications depend on your environment and the types of applications you are currently using. After metadata migration, some SQL/MX applications run unchanged in the SQL/MX Release 2.x environment. However, others require recompiling.
PAGE 76
Preparing to Migrate From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating From SQL/MX Release 1.8 to SQL/MX Release 2.
PAGE 77
6 Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Because SQL/MX Release 1.8 does not have SQL/MX database objects, SQL/MX Release 1.8 stores the metadata of these database entities in SQL/MP user metadata tables: • • • • SQL/MP aliases System defaults ODBC/MX (MXCS) metadata Stored procedures in Java (SPJs) When migrating from SQL/MX Release 1.8 to SQL/MX Release 2.x, use the migrate utility to migrate the metadata from SQL/MP user metadata tables to SQL/MX Release 2.
PAGE 78
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x System Requirements for Migrating SQL/MP Metadata System Requirements for Migrating SQL/MP Metadata • • • • • You have migrated from SQL/MX Release 1.8 to SQL/MX Release 2.x by installing the G06.23 or later RVU and SQL/MX Release 2.x on the system. The HP NonStop Transaction Management Facility (TMF) is installed, configured, and running on the system.
PAGE 79
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Considerations for Falling Back From SQL/MX Release 2.x Considerations for Falling Back From SQL/MX Release 2.x You perform SQL/MP metadata migration as part of the migration from SQL/MX Release 1.8 to SQL/MX Release 2.x. If it becomes necessary to fall back from SQL/MX Release 2.x and reinstall SQL/MX Release 1.
PAGE 80
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Overview of Metadata Migration Using the migrate Utility Table 6-1. Source and Destination Metadata Tables for Metadata Migration (page 2 of 2) Metadata SQL/MX Release 1.8 Metadata Table ODBC/MX ZONAM2ID metadata ZOAS2DS SQL/MX Release 2.x Metadata Table or Tables —> NONSTOP_SQLMX_node.MXCS_SCHEMA.NAME2ID** —> NONSTOP_SQLMX_node.MXCS_SCHEMA.ASSOC2DS ZODS —> NONSTOP_SQLMX_node.MXCS_SCHEMA.
PAGE 81
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating MPALIAS Metadata to SQL/MX Release 2.x 4. Use the migrate SHOW SCRIPT command to generate a script file from which you can fix discrepancies if they exist. 5. Edit the script file as needed to fix the discrepancies. 6. Use the migrate EXECUTE command with the SCRIPT option to move entries from specified SQL/MP metadata to SQL/MX metadata by executing the edited script. 7.
PAGE 82
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating MPALIAS Metadata to SQL/MX Release 2.x 3. To determine which entries in the MPALIAS table to migrate, use the SHOW MPALIAS command of the migrate utility and direct this information to a file: /usr/tandem/sqlmx/bin: migrate SHOW MPALIAS > mpalias_info.out The command displays this result: Hewlett-Packard Nonstop (TM) SQL/MX Metadata Migration Utility 2.0 (c) Copyright 2003 Hewlett-Packard Development Company, LP.
PAGE 83
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating MPALIAS Metadata to SQL/MX Release 2.x The PRELIMINARY_REPORT command displays these discrepancies in the MPALIAS table contents to be migrated: • • References to base files that do not physically exist on disk in the SQL/MP environment References to catalogs or schemas that do not exist in the SQL/MX environment 5.
PAGE 84
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating DEFAULTS Metadata to SQL/MX Release 2.x before adding information about migration errors. If the error log file already exists and you omit the CLEAR option, the migrate utility uses error_log.out. 8. Examine and resolve any errors in the error log file. The migrate EXECUTE command captures information about the metadata entries that fail to migrate to SQL/MX Release 2.
PAGE 85
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating DEFAULTS Metadata to SQL/MX Release 2.x 3. To determine which system-level default settings exist in the DEFAULTS table in the SQL/MP system catalog, use the SHOW DEFAULTS command of the migrate utility and direct this information to a file: /usr/tandem/sqlmx/bin: migrate SHOW DEFAULTS > defaults_info.out The command displays this result: Hewlett-Packard Nonstop (TM) SQL/MX Metadata Migration Utility 2.
PAGE 86
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating ODBC/MX Metadata to SQL/MX Release 2.x Steps for Migrating ODBC/MX Metadata to SQL/MX Release 2.x To use ODBC/MX metadata from SQL/MX Release 1.8 in SQL/MX Release 2.x, you must migrate the metadata of the ODBC/MX tables.
PAGE 87
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating PROCS and PARAMS Metadata to SQL/MX Release 2.x Note. Because there are no discrepancies to report for the NonStop ODBC/MX metadata tables, it is not necessary to use the PRELIMINARY_REPORT command to display discrepancies. Use the SHOW SCRIPT command to generate a script file for editing purposes. 4. Migrate ODBC entries from SQL/MP metadata to SQL/MX metadata by using the EXECUTE command of the migrate utility.
PAGE 88
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating PROCS and PARAMS Metadata to SQL/MX Release 2.x 1. In an SQLCI session, enter the GET CATALOG OF SYSTEM command to display the volume and subvolume of the SQL/MP system catalog where the PROCS and PARAMS tables reside: GET CATALOG OF SYSTEM; Note the names of the volume and subvolume. 2.
PAGE 89
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating PROCS and PARAMS Metadata to SQL/MX Release 2.x The command displays this result: PRELIMINARY_REPORT: Checking for missing catalogs and schemas for MPALIAS Migration... ... PRELIMINARY_REPORT: Checking for missing Guardian files for MPALIAS Migration... \POOH.$MXQA01.SQL.OPTABLE does not exist and is referenced. ... PRELIMINARY_REPORT: Checking for missing SQL/MP tables for ODBC Migration...
PAGE 90
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating PROCS and PARAMS Metadata to SQL/MX Release 2.x 5. To fix the reported discrepancies: • • Create the missing catalogs and schemas in SQL/MX, or change the catalog and schema names in the migration script by using the SHOW SCRIPT command. See Step 6. Copy the missing class and JAR files to the specified paths, or change the path names in the migration script by using the SHOW SCRIPT command. See Step 6. 6.
PAGE 91
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating PROCS and PARAMS Metadata to SQL/MX Release 2.x 9. Migrate the SPJ entries from the SQL/MP metadata to the SQL/MX metadata by using the EXECUTE command of the migrate utility: • To migrate all the SPJs directly from the PROCS table, enter: /usr/tandem/sqlmx/bin: migrate EXECUTE PROCS ERROR_LOG spj_errors.
PAGE 92
Migrating SQL/MP Metadata From SQL/MX Release 1.8 to SQL/MX Release 2.x Steps for Migrating PROCS and PARAMS Metadata to SQL/MX Release 2.
PAGE 93
7 Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x After you migrate to SQL/MX Release 2.x, SQL/MX Release 1.8 applications should continue to query SQL/MP database objects on the SQL/MX Release 2.x system and should not require recompilation. However, some SQL/MX applications that were compiled under SQL/MX Release 1.8 might require changes to the source code or might require recompilation. To migrate SQL/MX Release 1.8 applications to SQL/MX Release 2.
PAGE 94
Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x Planning to Execute SQL/MX Release 1.8 Applications in SQL/MX Release 2.x • • • • • • • SQLJ application MXCI script Java application that uses JDBC/MX ODBC client that uses MXCS Embedded SQL/MX program in COBOL Application Attributes Embedded SQL/MX program in C/C++ Table 7-1. Checklist for Migrating SQL/MX Release 1.
PAGE 95
Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x Adapting SQL/MX Release 1.8 Applications to SQL/MX Release 2.x Functionality Adapting SQL/MX Release 1.8 Applications to SQL/MX Release 2.
PAGE 96
Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x Stable Access changing the source code (that is, embedding a CONTROL QUERY DEFAULT statement). For more information, see the SQL/MX Programming Manual for C and COBOL. For dynamically compiled applications, set UPD_ABORT_ON_ERROR in a CONTROL QUERY DEFAULT statement in the source code and recompile the application. For more information about UPD_ABORT_ON_ERROR, see the SQL/MX Reference Manual.
PAGE 97
Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x Using INVOKE for Date-Time and Interval Host Variables SQL/MX Release 1.8 Floating-Point Format In SQL/MX Release 1.8, all floating-point data types are in Tandem format, and all SQL computations on floating-point data are done in Tandem format. SQL/MX Release 2.x Floating-Point Format In SQL/MX Release 2.
PAGE 98
Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x KANJI and KSC5601 Character Data Effect of the New Behavior on SQL/MX Applications If you preprocess an SQL/MX Release 1.8 application that uses INVOKE to create a date-time or interval host variable, SQL/MX Release 2.x returns an error during SQL compilation. The CAST function in an SQL/MX Release application is incompatible with the SQL:1999 date-time host variables created by INVOKE in SQL/MX Release 2.x. Note.
PAGE 99
Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x SQL/MX Release 1.8 Applications in a Distributed Database Environment You can eliminate these risks by updating the source code of an SQL/MX Release 1.8 application and language-compiling the application in SQL/MX Release 2.x. Ensure that the host variables for all KANJI and KSC5601 characters specify the correct lengths for SQL/MX Release 2.x, and use C string functions that are valid for double byte-encoded character sets.
PAGE 100
Migrating SQL/MX Release 1.8 Applications to SQL/MX Release 2.x Converting SQLJ Applications to JDBC/MX The changes in the query optimizer and UPDATE STATISTICS include: • • • • Replacing the MATERIALIZE operator with the ORDERED_HASH_JOIN operator. As a result, a query plan from an earlier release that contains a MATERIALIZE operator does not work. Improved traversal of join order that speeds up query plan compilation. As a result, some SQL/MX Release 1.
PAGE 101
8 Falling Back to SQL/MX Release 1.8 This section describes falling back from SQL/MX Release 2.x to SQL/MX Release 1.8. Falling back to a release of SQL/MX earlier than SQL/MX Release 1.8 is not supported. Before you install SQL/MX Release 2.x, plan for a potential fallback to SQL/MX Release 1.8. That way, if necessary, you can fall back from SQL/MX Release 2.x more easily. For more information, see Planning for Fallback on page 5-2. If you encounter a serious problem with SQL/MX Release 2.
PAGE 102
Falling Back to SQL/MX Release 1.8 Obtaining SQL/MX Release 1.8 prerequisite or corequisite software products. See Obtaining Corequisite Independent Products on page 8-2. Fallback to SQL/MX releases earlier than SQL/MX Release 1.8 is not supported. Obtaining SQL/MX Release 1.8 Obtain the latest version of SQL/MX Release 1.8 that is supported by the previous RVU of the system. For more information, see NonStop SQL/MX Releases on page 2-2 and RVUs and Supported SQL/MX Releases on page 2-7.
PAGE 103
Falling Back to SQL/MX Release 1.8 • Planning to Fall Back Other Nodes in the Network You recompiled user modules only. Applications that you compiled with the SQL/MX Release 2.x compiler need to be recompiled with the SQL/MX Release 1.8 compiler after fallback. • You made changes to metadata tables. If you changed any MPALIAS, DEFAULTS, PROCS, PARAMS, or ODBC/MX metadata tables (for example, to add a new SQL/MP alias) and you want to retain these changes after falling back to SQL/MX Release 1.
PAGE 104
Falling Back to SQL/MX Release 1.8 Drop SQL/MX Objects Drop SQL/MX Objects Caution. Before falling back to SQL/MX Release 1.8, remove all SQL/MX objects, including user tables and system metadata tables. SQL/MX objects are difficult to remove from a system with an RVU earlier than the G06.23 RVU. 1. In the SQL/MX Release 2.x environment, identify and drop all SQL/MX objects, including both user tables and system metadata tables. a.
PAGE 105
Falling Back to SQL/MX Release 1.8 Load a Previous RVU For complete command syntax and descriptions, see the SQL/MX Reference Manual. Note. If the schema is large and contains many rows, the DROP SCHEMA command might fail with file system error 35 (“Unable to obtain an I/O process control block, or the transaction or open lock unit limit has been reached.”). In this case, too many locks were requested, and you must use the Subsystem Control Facility (SCF) to update MaxLocksPerTCB to 10000, minimum.
PAGE 106
Falling Back to SQL/MX Release 1.8 Install Compatible Versions of Independent Products Install Compatible Versions of Independent Products Independent products required by SQL/MX Release 1.8 are not installed when you install or reload a previous RVU. To ensure correct installation, follow the installation instructions of each independent product. Start TMF When you have loaded a previous RVU, start TMF using your site’s established procedures. Install SQL/MX Release 1.
PAGE 107
Falling Back to SQL/MX Release 1.8 Remove the InstallSqlmx Script Remove the InstallSqlmx Script Caution. When you fall back from SQL/MX Release 2.x to SQL/MX Release 1.8, the InstallSqlmx script remains in the /usr/tandem/sqlmx/bin directory. InstallSqlmx is not designed to run from the SQL/MX Release 1.8 environment. If you attempt to do so, error messages are generated, and the SQL/MX installation aborts. Delete InstallSqlmx after you fall back from SQL/MX Release 2.x to SQL/MX Release 1.8.
PAGE 108
Falling Back to SQL/MX Release 1.8 Reconfigure the NonStop ODBC/MX Server and Driver The mxinit script creates the PROCS and PARAMS tables, the MPALIAS and DEFAULTS tables, and ODBC tables (ZONAM2ID, ZODS, ZODT, ZOAS2DS, ZORES, ZOENV) in the same subvolume as the system catalog. These tables are registered in the system catalog. mxinit creates a link for MXCI from /usr/bin/ to /usr/tandem/sqlmx/bin/ so you can enter commands without typing in the full path.
PAGE 109
Falling Back to SQL/MX Release 1.8 Managing Module Files and Applications During Fallback From SQL/MX Release 2.x Managing Module Files and Applications During Fallback From SQL/MX Release 2.x • • Managing Globally Placed Modules and Applications During Fallback on page 8-9 Managing Locally Placed Module Files and Applications During Fallback on page 8-10 Managing Globally Placed Modules and Applications During Fallback If you fall back to SQL/MX Release 1.
PAGE 110
Falling Back to SQL/MX Release 1.8 Managing Locally Placed Module Files and Applications During Fallback 5. In SQL/MX Release 1.8, use mxcmp to SQL compile every SQL/MX Release 2.x application’s module definition file to generate a new globally placed module. New module files are automatically placed in the /usr/tandem/sqlmx/USERMODULES directory. Managing Locally Placed Module Files and Applications During Fallback Before you fall back, identify the version of SQL/MX Release 1.
PAGE 111
Falling Back to SQL/MX Release 1.8 Converting Locally Placed User Modules to Globally Placed User Modules 2. Fall back your SQL/MX Release 2.x environment to an SQL/MX Release 1.8 environment as described in Steps for Falling Back From SQL/MX Release 2.x to SQL/MX Release 1.8 on page 8-3. 3. Verify that you are falling back to SQL/MX Release 1.8.5 AAV or later, which supports locally placed modules. If not, see Converting Locally Placed User Modules to Globally Placed User Modules on page 8-11. 4.
PAGE 112
Falling Back to SQL/MX Release 1.
PAGE 113
Part III.
PAGE 114
Part III.
PAGE 115
9 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 9-1 Meeting System Hardware Requirements on page 9-1 Meeting Operating System Requirements on page 9-2 Planning for Performance and Capacity on page 9-2 Planning for Fallback on page 9-3 Installing the Latest Supported Version of SQL/MX Release 2.
PAGE 116
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Migrating From K-Series to S-Series Systems or NS-Series Systems Model 1954 (S72000) or later. Otherwise, the InstallSqlmx script does not install SQL/MX Release 2.x. For more information about installing NonStop SQL/MX, see the SQL/MX Installation and Management Guide.
PAGE 117
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Planning for Fallback multiple databases share the same disk. For minimum disk space requirements for SQL/MX Release 2.x, see the SQL/MX Installation and Management Guide. The Disk Space Analysis Program (DSAP) can help you analyze the use of space on disk volumes. For more information about DSAP, see the Guardian Disk and Tape Utilities Reference Manual. SQL/MX applications tend to use more memory than SQL/MP applications.
PAGE 118
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Installing the Latest Supported Version of SQL/MX Release 2.x Installing the Latest Supported Version of SQL/MX Release 2.x Install the latest version of SQL/MX Release 2.x that is supported by the RVU of the system. For more information, see NonStop SQL/MX Releases on page 2-2 and RVUs and Supported SQL/MX Releases on page 2-7. Be aware that installing an SQL/MX release on your system overwrites the previous SQL/MX release.
PAGE 119
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Preparing to Migrate SQL/MP Applications Preparing to Migrate SQL/MP Applications Decide which SQL/MP applications to convert to SQL/MX applications. For more information, see Why Convert SQL/MP Applications to SQL/MX Applications? on page 10-2 and Reasons for Keeping SQL/MP Applications on page 10-5. Planning activities for migrating applications depend on your development environment and on the types of applications that you are currently using.
PAGE 120
Preparing to Migrate From NonStop SQL/MP to NonStop SQL/MX Preparing to Migrate SQL/MP Database Objects HP NonStop SQL/MX Database and Application Migration Guide —540435-005 9 -6
PAGE 121
10 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.
PAGE 122
Converting SQL/MP Applications to SQL/MX Applications Why Convert SQL/MP Applications to SQL/MX Applications? Why Convert 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.
PAGE 123
Converting SQL/MP Applications to SQL/MX Applications • New Functionality in NonStop SQL/MX SQL/MX database objects (See Why Convert SQL/MP Tables to SQL/MX Tables? on page 11-1.) To use these new features, you must either create new SQL/MX applications or convert SQL/MP applications to SQL/MX applications. Publish/Subscribe NonStop SQL/MX has publish/subscribe functionality, which enables applications to receive notification of changes to the database as soon as they occur.
PAGE 124
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.
PAGE 125
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.
PAGE 126
Converting SQL/MP Applications to SQL/MX Applications Types of SQL/MP Applications to Convert Parallel Operations on Partitioned Tables Currently, NonStop SQL/MX does not open partitions in parallel as NonStop SQL/MP does. 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.
PAGE 127
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 10-7 Changes to SQL Syntax on page 10-16 Changes to Embedded SQL Constructs on page 10-41 3. Preprocess and compile the source files.
PAGE 128
Converting SQL/MP Applications to SQL/MX Applications Converting JDBC/MP Applications to NonStop SQL/MX Converting JDBC/MP Applications to NonStop SQL/MX If you want a Java application, which uses NonStop SQL/MP, to take advantage of SQL/MX functionality, you must change the application from using the HP JDBC Driver for NonStop SQL/MP (the JDBC/MP driver) to either the HP JDBC Driver for NonStop SQL/MX (the JDBC/MX driver) or the HP NonStop JDBC Type 4 driver (the Type 4 driver).
PAGE 129
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.
PAGE 130
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 10-1. SQLCI Commands and MXCI Commands (page 2 of 7) SQLCI Commands Implemented in MXCI? CLEANUP No Utility. See SQL/MX DDL Statements and Utilities on page 11-14. CONVERT No Utility. See SQL/MX DDL Statements and Utilities on page 11-14. COPY No Utility. See SQL/MX DDL Statements and Utilities on page 11-14.
PAGE 131
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 10-1. SQLCI Commands and MXCI Commands (page 3 of 7) SQLCI Commands Implemented in MXCI? Comments DOWNGRADE SYSTEM CATALOG No Utility. See SQL/MX DDL Statements and Utilities on page 11-14. 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.
PAGE 132
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 10-1. SQLCI Commands and MXCI Commands (page 4 of 7) SQLCI Commands Implemented in MXCI? INITIALIZE SQL Yes 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 an SQL/MX object, for example when you need to re-create it, use the SQL/MX SHOWDDL command.
PAGE 133
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 10-1. SQLCI Commands and MXCI Commands (page 5 of 7) SQLCI Commands Implemented in MXCI? PAGE TITLE Yes Report Writer command. See Migrating From SQL/MP Report Writer to SQL/MX Report Writer on page 10-15. PERUSE No Utility. See SQL/MX DDL Statements and Utilities on page 11-14. PREPARE Yes PURGE No Utility. See SQL/MX DDL Statements and Utilities on page 11-14. PURGEDATA Yes Utility.
PAGE 134
Converting SQL/MP Applications to SQL/MX Applications Converting SQLCI Scripts to NonStop SQL/MX Table 10-1. SQLCI Commands and MXCI Commands (page 6 of 7) SQLCI Commands Implemented in MXCI? SET STYLE Yes Report Writer command. See Migrating From SQL/MP Report Writer to SQL/MX Report Writer on page 10-15. SHOW CONTROL Changed In MXCI, SHOWCONTROL is one word instead of two. Otherwise, the command is the same in NonStop SQL/MP and NonStop SQL/MX.
PAGE 135
Converting SQL/MP Applications to SQL/MX Applications Steps for Converting SQL/MP Applications to SQL/MX Applications Table 10-1. SQLCI Commands and MXCI Commands (page 7 of 7) SQLCI Commands Implemented in MXCI? VERIFY Changed Comments SQL/MX VERIFY is an OSS command-line utility run from mxtool that reports whether SQL/MX objects and programs are consistently described in file labels, resource forks, and metadata. For more information, see the SQL/MX Reference Manual. Utility.
PAGE 136
Converting SQL/MP Applications to SQL/MX Applications Modifying the Source Code Modifying the Source Code To convert an 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.
PAGE 137
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 10-2 lists the SQL/MP DCL statements that are supported in NonStop SQL/MX and describes the syntax differences: Table 10-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.
PAGE 138
Converting SQL/MP Applications to SQL/MX Applications Data Control Language (DCL) Statements Table 10-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 10-59. JOIN METHOD Changed Use CONTROL QUERY SHAPE to force join methods. See Forcing Query Execution Plans on page 10-59.
PAGE 139
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.
PAGE 140
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 an SQL statement that refers to an audited table.
PAGE 141
Converting SQL/MP Applications to SQL/MX Applications Data Manipulation Language (DML) Statements Accessing Nonaudited 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 nonaudited 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 nonaudited tables.
PAGE 142
Converting SQL/MP Applications to SQL/MX Applications INSERT Statements Table 10-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 10-22.
PAGE 143
Converting SQL/MP Applications to SQL/MX Applications SELECT Statements In general, 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.
PAGE 144
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.
PAGE 145
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.
PAGE 146
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.
PAGE 147
Converting SQL/MP Applications to SQL/MX Applications SQL Functions Table 10-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.
PAGE 148
Converting SQL/MP Applications to SQL/MX Applications Predicates Table 10-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 10-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.
PAGE 149
Converting SQL/MP Applications to SQL/MX Applications Predicates Table 10-8. SQL/MX and SQL/MP Predicates (page 2 of 2) SQL/MP Predicates LIKE Implemented in SQL/MX? Yes, but changes might be required. NULL Quantified Comments NonStop SQL/MX does not support the TERMINATE character. For more information, see the LIKE Predicate on page 10-30. NonStop SQL/MP and NonStop SQL/MX differ in how they handle SQL/MP NCHAR columns in a LIKE predicate.
PAGE 150
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.
PAGE 151
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.
PAGE 152
Converting SQL/MP Applications to SQL/MX Applications Reserved Words SQL/MP Database Object References When converting an SQL/MP application to an 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.
PAGE 153
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.
PAGE 154
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. However, SQL/MX Release 2.
PAGE 155
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 an SQL/MP table that has a collation, other than the default collation.
PAGE 156
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 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.
PAGE 157
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
PAGE 158
Converting SQL/MP Applications to SQL/MX Applications Interval Data Types and Literals Table 10-9.
PAGE 159
Converting SQL/MP Applications to SQL/MX Applications Interval Data Types and Literals Table 10-10.
PAGE 160
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, an SQL/MX interval literal is either year-month or day-time.
PAGE 161
Converting SQL/MP Applications to SQL/MX Applications Data Definition Language (DDL) Statements Table 10-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.
PAGE 162
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 10-42 SETSCALE Function on page 10-42 Declaration of Character Host Variables on page 10-43 Variable-Length Character Data in Embedded SQL C Programs on page 10-43 INVOKE Directive on page 10-43
PAGE 163
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.
PAGE 164
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 Release 2.x, you can declare host variables with standard date-time and interval data types.
PAGE 165
Converting SQL/MP Applications to SQL/MX Applications Embedded SQL Statements if (parts.partdesc_i > 0 ) handle_truncated_value(); ... 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 nonnull 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.
PAGE 166
Converting SQL/MP Applications to SQL/MX Applications Embedded SQL Statements Table 10-12. Embedded SQL Statements (page 2 of 2) Embedded SQL/MP Statements and Directives Implemented in SQL/MX? OPEN Changed 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 10-23. WHENEVER Changed Usage changes.
PAGE 167
Converting SQL/MP Applications to SQL/MX Applications Dynamic SQL In NonStop SQL/MP, the cursor defaults to read-only unless specified FOR UPDATE. In NonStop SQL/MX, within the DECLARE CURSOR statement, the optional FOR clause has this form: FOR {READ ONLY | UPDATE [OF colname [,colname]...]} By using the FOR clause, you can specify whether the cursor is FOR READ ONLY (read-only cursor) or FOR UPDATE OF (updatable cursor).
PAGE 168
Converting SQL/MP Applications to SQL/MX Applications Dynamic SQL The next list summarizes the dynamic SQL statements that you use with an SQL/MX descriptor area. For information about the syntax and use of these statements, see the SQL/MX Reference Manual. ALLOCATE DESCRIPTOR Allocates an input or output SQL descriptor area. DEALLOCATE DESCRIPTOR Deallocates an SQL descriptor area.
PAGE 169
Converting SQL/MP Applications to SQL/MX Applications Error Handling descriptor area. If you have more than one dynamic input parameter, you can identify the values by their position in the prepared SQL statement. In NonStop SQL/MX, use the CAST specification to give input parameters a defined data type provided in the descriptor area. The CAST specification might improve the efficiency of the generated query plan.
PAGE 170
Converting SQL/MP Applications to SQL/MX Applications Error Handling Table 10-13. Mapping SQL/MP SQLCODE to SQL/MX SQLCODE Values (page 2 of 4) SQL/MP SQLCODE SQL/MX SQLCODE SQL/MX SQLSTATE The statement was not prepared before execution and failed. -8031 -8822 X08MU A specified cursor could not be located in the program where it was declared, so the statement failed.
PAGE 171
Converting SQL/MP Applications to SQL/MX Applications Error Handling Table 10-13. Mapping SQL/MP SQLCODE to SQL/MX SQLCODE Values (page 3 of 4) SQL/MP SQLCODE SQL/MX SQLCODE SQL/MX SQLSTATE The row being inserted or updated in a table contains a key value that must be unique, but the key value is already present in a row. The insert or update operation failed.
PAGE 172
Converting SQL/MP Applications to SQL/MX Applications Error Handling Table 10-13. Mapping SQL/MP SQLCODE to SQL/MX SQLCODE Values (page 4 of 4) SQL/MP SQLCODE SQL/MX SQLCODE SQL/MX SQLSTATE All the character data could not fit in the target buffer, so the truncated result was put in the buffer. -8403 -8402 22001 The denominator value for division was zero, so the statement failed.
PAGE 173
Converting SQL/MP Applications to SQL/MX Applications Statistics Area For information about handling errors, see Diagnostics Area. In NonStop SQL/MP, the WHENEVER declarative requires a colon (:) before the target location for the GO TO, PERFORM (in COBOL), or CALL (in C) actions. This restriction is removed in NonStop SQL/MX. Diagnostics Area NonStop SQL/MP provides a communication area (SQLCA) that enables an application program to check the status of SQL statement execution.
PAGE 174
Converting SQL/MP Applications to SQL/MX Applications Compiling the Application information on what type of statement was compiled or on the cost of a PREPARE statement. Compiling the Application After modifying the source code to be compliant with NonStop SQL/MX, compile the source files by using the host-language compiler for that type of application.
PAGE 175
Converting SQL/MP Applications to SQL/MX Applications Converting Guardian Applications to OSS Table 10-14. Development Environments for SQL/MX Applications (page 2 of 2) SQL/MX Application • • • Development Environment For more information...
PAGE 176
Converting SQL/MP Applications to SQL/MX Applications Similarity Checks and Automatic Recompilation of Embedded SQL/MX Applications Automatic Recompilation Warnings NonStop SQL/MP returns a warning to the application if a similarity check fails and automatic recompilation occurs. Because NonStop SQL/MX is ANSI-compliant, an automatic recompilation warning is not returned to the application by default. To obtain the same behavior as in NonStop SQL/MP, set the RECOMPILATION_WARNINGS default attribute to ON.
PAGE 177
Converting SQL/MP Applications to SQL/MX Applications Similarity Checks and Automatic Recompilation of Embedded SQL/MX Applications Table 10-15.
PAGE 178
Converting SQL/MP Applications to SQL/MX Applications Managing Modules of Embedded SQL/MX Applications Table 10-15.
PAGE 179
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 specify the default settings by using CONTROL directives.
PAGE 180
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.
PAGE 181
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.
PAGE 182
Converting SQL/MP Applications to SQL/MX Applications Steps for Moving an Application to Production HP NonStop SQL/MX Database and Application Migration Guide —540435-005 10 -62
PAGE 183
11 Converting an SQL/MP Database to SQL/MX Tables This section explains how to convert SQL/MP database objects to SQL/MX database objects and covers these topics: • • • • • • Why Convert SQL/MP Tables to SQL/MX Tables? on page 11-1 Differences Between SQL/MP and SQL/MX Databases on page 11-4 SQL/MX DDL Statements and Utilities on page 11-14 Steps for Converting an SQL/MP Database to SQL/MX Tables on page 11-21 Migrating Data From an SQL/MP Database to an SQL/MX Database on page 11-22 Updating Table Statis
PAGE 184
Converting an SQL/MP Database to SQL/MX Tables ANSI Compliance Before converting an SQL/MP database object to NonStop SQL/MX, be aware of the Reasons for Keeping SQL/MP Tables on page 11-3. 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 Release 2.x is based on ANSI SQL:1999 and includes support for features from higher levels of the standard. SQL/MX Release 2.
PAGE 185
Converting an SQL/MP Database to SQL/MX Tables Size of the Database Object your database consists of relative tables and you want to use features in NonStop SQL/MX, you might consider converting your database to SQL/MX tables. Size of the Database Object NonStop SQL/MP limits the number of partitions that can exist for an SQL/MP table. However, NonStop SQL/MX does not limit the number of partitions for an SQL/MX table.
PAGE 186
Converting an SQL/MP Database to SQL/MX Tables Differences Between SQL/MP and SQL/MX Databases 1. Splitting off the first half of the partition 2. Moving the second half to a new partition. 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.
PAGE 187
Converting an SQL/MP Database 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.
PAGE 188
Converting an SQL/MP Database to SQL/MX Tables User Catalogs and Schemas User Catalogs and Schemas An SQL/MP catalog is a set of tables and indexes that describe SQL/MP database objects. Tables in the set are called catalog tables, and NonStop SQL/MP creates them and their indexes when you execute a CREATE CATALOG statement. Each SQL/MP catalog (the set of catalog tables and their indexes) resides on its own Guardian subvolume, and the name of that subvolume is also the name of the catalog.
PAGE 189
Converting an SQL/MP Database to SQL/MX Tables Distributed Database Objects collections of schemas. Schema names must be unique within a given catalog. Multiple objects with the same name can exist in a schema provided that each name belongs to a different namespace. NonStop SQL/MX supports various namespaces, such as table value objects (tables, views, stored procedures, and SQL/MP aliases), indexes, and triggers. For more information about namespaces, see the SQL/MX Reference Manual.
PAGE 190
Converting an SQL/MP Database to SQL/MX Tables Tables SQL/MX applications are always stored in the OSS environment. For more information about OSS security, see the OSS User’s Guide. For more information about securing SQL/MX user modules, see the SQL/MX Installation and Management Guide. Tables An SQL/MX table is described in an SQL/MX schema yet stored in one or more physical files in the Guardian environment.
PAGE 191
Converting an SQL/MP Database to SQL/MX Tables Tables Table 11-1. File Attributes in NonStop SQL/MP and NonStop SQL/MX (page 2 of 2) SQL/MP Attributes Supported in SQL/MX? DCOMPRESS No EXTENT Yes FORMAT No (SQL/MX tables are always format 2.) ICOMPRESS No 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.
PAGE 192
Converting an SQL/MP Database to SQL/MX Tables Tables Column Data Types SQL/MX tables support most of the data types that exist in SQL/MP tables, with a few exceptions. Character String Columns Unlike SQL/MP tables, SQL/MX tables support only the UCS2 or ISO88591 character set for character string columns. NonStop SQL/MX does not support non-default collations on SQL/MX character columns. Date-Time Columns Many SQL/MP DATETIME data types map to SQL/MX DATE, TIME, and TIMESTAMP data types.
PAGE 193
Partitions Converting an SQL/MP Database to SQL/MX Tables Constraints Unlike NonStop SQL/MP, you do not create constraints as separate database objects for SQL/MX tables. The CREATE CONSTRAINT statement does not exist in NonStop SQL/MX. In NonStop SQL/MX, use the CREATE TABLE statement to create constraints on SQL/MX tables, and use the ALTER TABLE statement to add or drop constraints. NonStop SQL/MX supports more types of constraints than NonStop SQL/MP, as Table 11-2 shows. Table 11-2.
PAGE 194
Indexes Converting an SQL/MP Database to SQL/MX Tables based on the result. For more information about hash partitioning, see the SQL/MX Reference Manual. Partition management differs in NonStop SQL/MP and NonStop SQL/MX. In NonStop SQL/MP, you use the SQL/MP ALTER TABLE or ALTER INDEX statement to manage partitions. In NonStop SQL/MX, you use the SQL/MX MODIFY utility to manage partitions. For more information about managing partitions in NonStop SQL/MX, see the SQL/MX Installation and Management Guide.
PAGE 195
Converting an SQL/MP Database to SQL/MX Tables Views Table 11-4. Index Table Attributes in NonStop SQL/MP and NonStop SQL/MX SQL/MP Attributes Supported in SQL/MX? ALLOCATE Yes AUDITCOMPRESS Yes BLOCKSIZE Yes (only 4096) BUFFERED No CLEARONPURGE Yes DCOMPRESS No EXTENT Yes FORMAT No (SQL/MX tables are always format 2.
PAGE 196
Collations Converting an SQL/MP Database to SQL/MX Tables view to update the view. In NonStop SQL/MX, you need only have appropriate security on the view itself, not on the underlying table, to update the view. For more information about SQL/MX views, see the SQL/MX Reference Manual. Collations Unlike NonStop SQL/MP, NonStop SQL/MX does not support collations other than the DEFAULT collation, which uses binary ordering.
PAGE 197
SQL/MX DDL Statements Converting an SQL/MP Database to SQL/MX Tables Table 11-5. SQL/MP and SQL/MX DDL Statements (page 2 of 2) SQL/MP DDL Statements Implemented in SQL/MX? ALTER TABLE Yes For more information, see the SQL/MX Reference Manual. ALTER VIEW No In NonStop SQL/MX, you cannot alter views. To change a view, you must drop and then re-create the view. COMMENT No CONTINUE No CREATE CATALOG Yes For more information, see the SQL/MX Reference Manual.
PAGE 198
Converting an SQL/MP Database to SQL/MX Tables SQL/MX Utilities New SQL/MX DDL Statements NonStop SQL/MX introduces new DDL statements to create database objects, such as schemas, SQL/MP aliases, stored procedures, and triggers, that do not exist in NonStop SQL/MP: • • • • • • • • • • • • ALTER SQLMP ALIAS ALTER TRIGGER CREATE PROCEDURE CREATE SCHEMA CREATE SQLMP ALIAS CREATE TRIGGER DROP PROCEDURE DROP SCHEMA DROP SQL DROP SQLMP ALIAS DROP TRIGGER INITIALIZE SQL These new SQL/MX statements influence t
PAGE 199
SQL/MX Utilities Converting an SQL/MP Database to SQL/MX Tables Table 11-6. Comparing SQL/MP Utilities With SQL/MX Utilities (page 1 of 3) SQL/MP Utility Execution Environment SQL/MX Equivalent Execution Environment For more information... BACKUP Guardian BRCOM BACKUP Guardian Backup and Restore 2 Manual and SQL/MX Installation and Management Guide CLEANUP SQLCI None N.A. CONVERT SQLCI None N.A. COPY SQLCI None N.A.
PAGE 200
SQL/MX Utilities Converting an SQL/MP Database to SQL/MX Tables Table 11-6. Comparing SQL/MP Utilities With SQL/MX Utilities (page 2 of 3) SQL/MP Utility Execution Environment SQL/MX Equivalent Execution Environment For more information... FILES SQLCI None N.A.
PAGE 201
SQL/MX Utilities Converting an SQL/MP Database to SQL/MX Tables Table 11-6. Comparing SQL/MP Utilities With SQL/MX Utilities (page 3 of 3) SQL/MP Utility Execution Environment SQL/MX Equivalent Execution Environment For more information... TEDIT SQLCI TEDIT Guardian PS TEXT EDIT Reference Manual UPGRADE CATALOG SQLCI None N.A. UPGRADE SYSTEM CATALOG SQLCI None N.A.
PAGE 202
SQL/MX Utilities Converting an SQL/MP Database to SQL/MX Tables Table 11-7. New SQL/MX Utilities (page 2 of 2) SQL/MX Utility Execution Environment Function mxtool: OSS Performs various utility operations. FIXUP operation OSS Repairs problems in the SQL/MX database that cannot be repaired by normal operations. GOAWAY operation OSS Removes Guardian files associated with an SQL/MX database object.
PAGE 203
Converting an SQL/MP Database to SQL/MX Tables Steps for Converting an SQL/MP Database to SQL/MX Tables Steps for Converting an SQL/MP Database to SQL/MX Tables To convert an SQL/MP database to SQL/MX tables: 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 an SQL/MX database, see the SQL/MX Installation and Management Guide. 2. Create the SQL/MX database: a. Create the user catalogs. b.
PAGE 204
Converting an SQL/MP Database to SQL/MX Tables Migrating Data From an SQL/MP Database to an SQL/MX Database Migrating Data From an SQL/MP Database to an 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.
PAGE 205
Converting an SQL/MP Database 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 an SQL/MP table into an 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.
PAGE 206
Using DataLoader/MX to Copy Data Converting an SQL/MP Database to SQL/MX Tables Steps for Using DataLoader/MX To use DataLoader/MX to load data from an SQL/MP table into an SQL/MX table: 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. Start a copy of the customized DataLoader executable, which reads input from $RECEIVE and writes output to $RECEIVE. 3.
PAGE 207
Converting an SQL/MP Database to SQL/MX Tables Using DataLoader/MX to Copy Data 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.
PAGE 208
Converting an SQL/MP Database 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 an SQL/MP database into an SQL/MX database.
PAGE 209
12 Falling Back to NonStop SQL/MP This section 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 9-3.
PAGE 210
Falling Back to NonStop SQL/MP Steps for Falling Back to NonStop SQL/MP and c.cat_uid=cr.cat_uid and cr.
PAGE 211
Falling Back to NonStop SQL/MP Steps for Falling Back to NonStop SQL/MP e. Use the DROP SQL command to drop the SQL/MX Release 2.x system catalog. For complete command syntax and descriptions, see the SQL/MX Reference Manual. 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.
PAGE 212
Falling Back to NonStop SQL/MP Steps for Falling Back to NonStop SQL/MP HP NonStop SQL/MX Database and Application Migration Guide —540435-005 12- 4
PAGE 213
Index A Access options, STABLE access 10-25 Aggregate predicates 10-29 ANSI compliance 10-2, 11-2 Assistance 1-20 ASSOC2DS metadata table 6-4 Atomicity 7-3 Automatic recompilation, warnings 10-56 Auto-abort 10-20 Compound (continued) statements 10-3 Constraints 11-11 CONTROL QUERY directives changes in SQL/MX Release 2.
PAGE 214
E Index Database object (continued) security 11-7 versioning 2-11 DataLoader/MX 11-23 DATASOURCES metadata table 6-4 Date-time host variables, differences between SQL/MX Release 1.8 and SQL/MX Release 2.x 7-5, 10-44 Date-time literals 10-36 DDL files removing after fallback to NonStop SQL/MP 12-3 removing when falling back to SQL/MX Release 1.
PAGE 215
G Index FETCH statement 10-47 File attributes 11-8 Floating-point host variables, differences between SQL/MX Release 1.8 and SQL/MX Release 2.
PAGE 216
K Index K KANJI character data, differences between SQL/MX Release 1.8 and SQL/MX Release 2.x 7-6 KSC5601 character data, differences between SQL/MX Release 1.8 and SQL/MX Release 2.
PAGE 217
N Index Multivalue predicates See Row-value constructors MXCI script, migrating from SQL/MX Release 1.8 to SQL/MX Release 2.x 7-2 MXCS client, migrating from SQL/MX Release 1.8 to SQL/MX Release 2.
PAGE 218
Q Index PROCS metadata table, migrating 6-11 Product version identifiers 2-6 Product version update (PVU) 2-6 Program invalidation 10-55 Protection views 11-13 PROTOTYPE host variables 10-32 Publish/subscribe 10-3 PV See Initial product version (PV) PVU See Product version update (PVU) Q Queries, multinode 5-5, 9-4 Query execution plans displaying 10-59 forcing 10-59 optimizing performance 10-59 Query plan versioning description of 2-12 managing 2-14 SQL/MX Releases 2.0 through 2.3.
PAGE 219
S Index SQLCI commands, migrating (continued) DISPLAY STATISTICS 10-10 DISPLAY USE OF 10-10 DROP SYSTEM CATALOG 10-11 EXPLAIN 10-11 FILEINFO 10-11 INVOKE 10-12 LOAD 10-12 RESET REPORT 10-13 SHOWCONTROL 10-14 VERIFY 10-15 SQLCI scripts 10-8 SQLCODE mappings 10-49 SQLDA 10-47 SQLJ applications, converting to JDBC/MX 7-8 SQLSA 10-53 SQLSTATE mappings 10-49 SQL/MP See NonStop SQL/MP SQL/MP applications converting to SQL/MX applications 10-1/10-61 definition of 1-1 modifying source code 10-16 preparing to migr
PAGE 220
T Index SQL/MX Release 1.8 definition of 1-2 initializing after fallback 8-7 required RVU 8-1 steps to migrate to SQL/MX Release 2.x 5-5 SQL/MX Release 1.8 applications distributed databases 7-7 migrating 7-1/7-8 preparing to migrate to SQL/MX Release 2.x 5-5 recompiling 7-7 SQL/MX Release 2.2, query plan versioning 2-14 SQL/MX Release 2.
PAGE 221
V Index Upgrade (continued) planning for 3-1 upgrading to a newer version of SQL/MX Release 2.
PAGE 222
Z Index HP NonStop SQL/MX Database and Application Migration Guide —540435-005 Index -10
PAGE 223
Content Feedback First Name: __________________ Phone: _____________________ Company: ___________________ Last Name: _________________ e-mail address: ______________ (All contact information fields are required.) If you’re reporting an error or omission, is your issue: Minor: I can continue to work, but eventual resolution is requested. Major: I can continue to work, but prompt resolution is requested. Critical: I cannot continue to work without immediate response.