Compaq NonStop™ SQL/MP Version Management Guide Abstract This manual describes Compaq NonStop™ SQL/MP version management for different versions of the NonStop SQL software, catalogs, objects, message files, and programs. Product Version NonStop SQL/MP G07 Supported Releases This manual supports G06.13 and all subsequent releases until otherwise indicated in a new edition.
Document History Part Number Product Version Published 092222-001 NonStop SQL/MP D30 December 1994 427096-001 NonStop SQL/MP G06 March 2001 429833-001 NonStop SQL/MP G07 July 2001 Ordering Information For manual ordering information: domestic U.S. customers, call 1-800-243-6886; international customers, contact your local sales representative. Document Disclaimer Information contained in a manual is subject to change without notice.
Compaq NonStop™ SQL/MP Version Management Guide Index Figures Tables About This Manual vii Audience vii Related SQL/MP Manuals vii Your Comments Invited viii Notation Conventions viii What’s New in This Manual xi Manual Information xi New and Changed Information xi 1.
2. Software Versions and Releases Contents Migrating to a Newer Version 1-6 Reinstalling an Older Version 1-7 Version Considerations in a Network 1-7 2.
. Program Versions Contents Retrieving the Version of an Alternate Language Message File 4-3 5.
7. Version Compatibility for SQL Programs Contents Version 300 or Newer Utilities and Newer Version Software 6-11 7.
Index Contents Changes to Catalog Tables A-5 Catalog Changes in Version 2 A-5 Catalog Changes in Version 300 A-6 Catalog Changes in Version 310 A-7 Catalog Changes in Version 315 A-8 Catalog Changes in Version 320 A-8 Catalog Changes in Versions 325, 330, 335, and 340 Catalog Changes in Version 345 A-8 Catalog Changes in Version 350 A-8 A-8 Changes to SQL Data Structures A-9 Data Structure Changes in Version 2 Data Structure Changes in Version 300 A-9 A-10 Data Structure Changes in Vers
Tables Contents Tables Table 1-1. Version Numbers Used to Describe SQL/MP Products Table A-1. Version 2 Features Table A-2. Version 300 Features Table A-3. Version 310 Features A-3 Table A-4. Version 335 Features A-3 Table A-5. Version 350 Features A-4 Table A-6. Table Changes in Version 2 Table A-7. Table Changes in Version 300 A-6 Table A-8. Table Changes in Version 310 A-8 Table A-9. Table Changes in Version 320 A-8 Table A-10. Table Changes in Version 350 A-9 Table A-11.
About This Manual This manual provides guidelines for managing NonStopSQL/MP installations using the SQL/MP version management system. It explains how newer versions and older versions of SQL/MP work together with the various versions of SQL catalogs, objects, programs, messages, and data structures available to SQL/MP users. In addition, this manual provides guidelines for developing applications that run under current, older, and future versions of SQL/MP software.
Your Comments Invited About This Manual • NonStop SQL/MP Programming Manual for C and NonStop SQL/MP Programming Manual for COBOL85 describe the NonStop SQL/MP programmatic interfaces for C and COBOL85, respectively. NonStop SQL Programming Manual for Pascal and NonStop SQL Programming Manual for TAL are the equivalent manuals for Pascal and TAL. For information on converting applications from C-Series to D-Series, see the Guardian Application Conversion Guide.
General Syntax Notation About This Manual lowercase italic letters. Lowercase italic letters indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name Punctuation. Parentheses, commas, semicolons, and other symbols not previously described must be entered as shown. For example: error := NEXTFILENAME ( file-name ) ; LISTOPENS SU $process-name.
About This Manual General Syntax Notation Compaq NonStop™ SQL/MP Version Management Guide —429833-001 x
What’s New in This Manual Manual Information Compaq NonStop™ SQL/MP Version Management Guide Abstract This manual describes Compaq NonStop™ SQL/MP version management for different versions of the NonStop SQL software, catalogs, objects, message files, and programs. Product Version NonStop SQL/MP G07 Supported Releases This manual supports G06.13 and all subsequent releases until otherwise indicated in a new edition.
What’s New in This Manual New and Changed Information Compaq NonStop™ SQL/MP Version Management Guide —429833-001 xii
1 Overview of Version Management As NonStopSQL/MP implements new features, SQL/MP software and its components are assigned new version numbers. As these version numbers change, you must take steps to ensure that each new release of SQL/MP is compatible with previous and subsequent releases. In addition, you must maintain compatibility between SQL/MP software, its utilities and components, and related products such as host language compilers.
Overview of Version Management Version Management Conventions Each time a new version of SQL/MP software is released, all programs that ran correctly under previous versions must also run correctly on the new version, without recompilation. To implement these requirements, SQL/MP associates version numbers with SQL/MP software, catalogs, objects, message files, and programs, and with the host language compilers used to compile source files containing SQL code.
Versions of SQL/MP Products Overview of Version Management date or time when a catalog or object was created, or the date or time when a program was compiled. • Older version A SQL/MP product with an older version is one that has an older release date and a lower version number than a product to which it is being compared.
SQL/MP Software Version Overview of Version Management Table 1-1.
Overview of Version Management Object Version corresponds to the oldest version of SQL/MP software that can read from or write to the catalog. A catalog format version number, unlike a catalog version number, is based on the physical structure of an SQL user or system catalog. Therefore, it changes only when the structure of a catalog changes.
Overview of Version Management Host Language Compiler SQL Version used in the program. For example, collations are version 300 objects. Therefore, a program that uses collations must have a program catalog version of at least 300. Host Language Compiler SQL Version A host language compiler SQL version number is a version number assigned to host language compilers and COBOL85 preprocessors used to compile source-code files with embedded SQL statements.
Overview of Version Management Reinstalling an Older Version enhancements available to the newer-version SQL compiler, you must SQL-compile your programs. When you migrate to a newer version of SQL/MP, you can continue to use existing older-version user catalogs and the objects registered in older-version catalogs. You do not need to upgrade user catalogs until you are ready to register in the catalogs any objects or programs with newer-version features.
Overview of Version Management • Version Considerations in a Network You cannot access objects or programs registered in a remote catalog whose version is newer than the version of the SQL/MP software on your local node. For example, you cannot back up a program registered in a version 310 catalog on a remote node when you are running version 2 software on your local node.
2 Software Versions and Releases The version numbers and release numbers used in the NonStopSQL/MP versionmanagement system belong in one of these categories: • • • • Each significant revision of a SQL/MP product is called a product version. Each has a unique product version ID expressed in the form Vn, where V is a letter and n is a two-digit or three-digit number. An example of a product version number is the number D30.
Interim Product Modifications (IPMs) Software Versions and Releases Figure 2-1. Relationship Among Products, Releases, and Versions NonStop SQL Product NonStop SQL Release Number C10 R(1) C30 (up to C30.07) R(2) NonStop SQL Version Version 1 Version 2 C30.08 (and newer) D10, D20 R(2.1) D22 R(2.2) D24 Version 300 Version 310 NonStop SQL/MP D30 R(1.0) Version 315 VST003.
Software Versions and Releases Release Updates SQL/MP version introduced to incorporate IPM modifications might be assigned number 320 or any other number greater than 315. Release Updates To incorporate a minor change into an existing release of SQL/MP, Compaq often updates an existing software release. In the same way that an IPM is a modification to an existing product version, a release update is a modification to an existing release.
Software Versions in a Network Software Versions and Releases newer version number than the version of the SQL/MP software running on the same node. The version of the SQL/MP software running on any one node must always be the same as or newer than the versions of the SQL catalogs and objects running on that node. The version must also be the same as or newer than the versions of any SQL objects or programs registered in the catalogs.
Software Versions and Releases Using the GET VERSION OF SYSTEM Statement application developers and database administrators to determine which version of SQL/MP software is currently operating on a particular system. To retrieve the SQL/MP software version currently running, use either of these methods: • • Execute the GET VERSION OF SYSTEM statement, either programmatically or through SQLCI (use only with version 300 or newer).
Software Versions and Releases Using the SQLGETSYSTEMVERSION Procedure Compaq NonStop™ SQL/MP Version Management Guide —429833-001 2 -6
3 Database Versions To specify the version of a NonStopSQL/MP user catalog or system catalog, you use two different types of version numbers: • Catalog version Every user catalog and every system catalog has a catalog version number. A catalog version number identifies the newest version of SQL objects that can be registered in a user or system catalog. The number is determined by the version of the SQL/MP software used to create the catalog.
Catalog Format Version Database Versions Figure 3-1. Catalog Version and Object Version: Valid Relationships Version 1 Catalog Version 2 Catalog Version 315 Catalog V1 Objects V1 Objects V2 Objects V1 Objects V2 Objects V300 Objects V310 Objects VST005.vsd Catalog Format Version A catalog format version number is another version number assigned to an SQL user catalog or an SQL system catalog.
Relation of Catalog Version to Software Version Database Versions For example, you can create version 315 user catalogs registered in a version 1 or version 2 system catalog on a node running version 315 software. You cannot, however, use a version 315 system catalog on a node running version 2 software. Figure 3-2 illustrates the relationship of the system catalog to user catalogs on a node running version 315 software. Figure 3-2.
Initializing and Changing User Catalog Versions Database Versions Initializing a User Catalog Version There are two ways to initialize the version of a user catalog: • • Execute the CREATE CATALOG statement. Run the RESTORE utility with the AUTOCREATECATALOG option set to ON. Executing the CREATE CATALOG Command The CREATE CATALOG statement initializes the version of the user catalog being created.
Where Catalog Versions Are Stored Database Versions • The DOWNGRADE CATALOG statement converts an existing user catalog to an older version so the catalog can be accessed by an older version of SQL/MP software. When a catalog is downgraded, DOWNGRADE CATALOG records the change in the user and system catalogs. You cannot use DOWNGRADE CATALOG to downgrade a catalog to version 1. Note. The UPGRADE CATALOG statement is no longer used to upgrade the system catalog; use UPGRADE SYSTEM CATALOG instead.
Where Catalog Versions Are Stored Database Versions CATALOGFORMAT. The values in the VERSION column of a version 300 or newer catalog are maintained for compatibility with versions 1 and 2. The catalog version in the CATALOGVERSION and CATALOGFORMAT columns of a VERSIONS table is recorded in integer format, which is compatible with SQL/MP software and with SQL object and program versions. This table lists the contents of the VERSION, CATALOGVERSION, and CATALOGFORMAT columns in a VERSIONS table.
Retrieving Catalog Versions Database Versions For a description of the attributes of these columns, see the NonStop SQL/MP Reference Manual. In version 300 and newer catalogs, the version information in the VERSIONS table of a user catalog and the CATALOGS table of the system catalog are consistent. Similarly, for version 1 and 2 catalogs, the version information is consistent in both the VERSIONS and CATALOGS tables.
Retrieving Catalog Versions Database Versions For the syntax of the GET VERSION OF CATALOG statement and for examples of using this statement in a static or dynamic SQL program, see the NonStop SQL/MP Reference Manual and the NonStop SQL/MP Programming Manual for the language you are using. Using Catalog Queries You can retrieve the catalog version from either the VERSION or CATALOGVERSION column of a system or user catalog.
SQL Object Versions Database Versions GET CATALOG OF SYSTEM Statement You can execute the GET CATALOG OF SYSTEM statement from a static SQL program, but not from a dynamic SQL program. Alternatively, you can enter GET CATALOG OF SYSTEM as a command by using the SQLCI command interface. The statement returns the name of a local or remote system catalog.
Initializing Object Versions Database Versions For example, a collation is at least a version 300 object. Therefore, collations are supported only by software of version 300 or newer. A column with null values is a version 2 feature, so a column with null values is supported by version 2 or newer versions of the software. Initializing Object Versions When an SQL object is created, its version is initialized.
Object Versions and Catalog Versions Database Versions • CREATE CONSTRAINT. When this statement is executed to create a constraint, the table version is raised to match the version of the constraint if the version of any constraint feature is newer than the version of its base table. Raising the table’s version can affect the versions of other objects dependent on the base table. The version of a constraint is not registered in a catalog.
Where Object Versions Are Stored Database Versions Figure 3-3. Object and Catalog Versions for Testing New Software Node Running Version 315 NonStop(TM) SQL Version 2 Catalog (Production) Version 315 Catalog (Testing) V1 Objects V2 Objects V1 Objects V2 Objects V300 Objects V310 Objects VST007.vsd Where Object Versions Are Stored The object versions of SQL tables, indexes, views, and collations are stored in catalog tables and in file labels.
Retrieving Object Versions Database Versions in the corresponding OBJECTVERSION columns in the TABLES and INDEXES catalog tables. You can invoke the SQL VERIFY utility to make sure that version information recorded in an OBJECTVERSION column matches the version information stored in the file label of any table, view, index, or collation.
Retrieving Object Versions Database Versions As an example, suppose you are running version 310 SQLCI and want to retrieve the version of the CUSTOMER table. You can type these commands: >> VOLUME \SYS2.$VOL1.SALES; >> GET VERSION OF TABLE CUSTOMER; The response to this query is: VERSION: 310 --- SQL operation complete.
Object-Version Dependencies Database Versions For example, by entering the following command at the SQLCI prompt, you can execute FILEINFO to determine the version of the collation named COLL1: >> VOLUME \SYS2.$VOL1.SALES; >> FILEINFO COLL1, DETAIL; The response to this command is: \SYS2.$VOL1.SALES.COLL115 July 1994, 15:00 SQL CHARACTER PROCESSING RULES OBJECT CATALOG $VOL1.SALES VERSION 300 TYPE K . .
Object-Version Dependencies Database Versions • • • • • All partitions of a given table are considered to be a single object, and all have the same version number. For example, if one partition of a table has the version number 300, all partitions of that table are version 300. The version of a protection or shorthand view depends on the versions of the tables or views on which the view is based.
Object-Version Dependencies Database Versions DROP and Object Versions These rules determine how DROP affects object version and depend on the type of object being dropped: • • • • • DROP TABLE drops all objects dependent on the dropped table. The versions of objects remaining after you use DROP TABLE are not affected. For example, if you drop a table, any views dependent on the table are also dropped. DROP VIEW drops all views dependent on the dropped view.
Object-Version Dependencies Database Versions • • • ALTER TABLE can alter the table’s version and the version of any objects whose version depends on that table. For example, suppose you alter a version 2 table to add a column that refers to a collation. Because collations were introduced in version 300, this alteration raises the version of the table and of any dependent views to version 300.
4 Message File Versions Errors, warnings, and help text are stored as messages in NonStop SQL/MP message files. Messages in message files are returned through the SQL/MP conversational interface (SQLCI) and through the SQL programmatic interface. Messages returned by SQL/MP can originate from various SQL/MP software components, including the SQL file system, the SQL compiler, and the FastSort program.
Using Alternate Language Message Files Message File Versions MESSAGEFILE VRSN315 OUT$TERM1 OUT_REPORT$S.#PRINTER SYSTEM\SYS2 TRANSACTION ID\SYS2.0.474330 VOLUME$VOL1.SALES WORKIN PROGRESS In the preceding output, the current version of the message file, \SYS2.$SYSTEM.SYSTEM.SQLMSG, is 315. Using Alternate Language Message Files Message files can be written in languages other than English. For example, you can use SQL/MP with French or Japanese message files.
Message File Versions Retrieving the Version of an Alternate Language Message File To use an alternate language message file, you must specify the name of the file by executing a DEFINE command. The following DEFINE specifies the location and name of a file containing alternate language messages: DEFINE =_SQL_MSG_system In this DEFINE, system is the system name where the SQL/MP software will run; the system name is specified without a backslash.
Message File Versions Retrieving the Version of an Alternate Language Message File OUT_REPORT$S.#PRINTER SYSTEM\SYS2 TRANSACTION ID\SYS2.0.474330 VOLUME$VOL1.SALES WORKIN PROGRESS In this example, the message file version is listed as 315.
5 Program Versions SQL Program Creation To understand the information presented in this section, you should have a basic understanding of how SQL programs are created. With the SQL programmatic interface, you can embed SQL statements and directives in a C, COBOL85, Pascal, or TAL program. The steps you use to create an SQL program follow: 1. Write the program in a source file using host language statements and SQL statements and directives. 2.
Host Object SQL Version (HOSV) Program Versions COBOL85 preprocessor has the same host language SQL version as the corresponding COBOL85 compiler. Different releases of a host language compiler or preprocessor can be compatible with the same host language SQL version.
Retrieving the Host Language Compiler SQL Version Program Versions This figure illustrates the relationship of the host language compiler SQL version and the host object SQL version to the version of an SQL compiler: Host Language Compiler Host Language Compiler SQL Version Host Object SQL File = Maximum Host Object SQL Version SQL Compiler <= SQL Compiler Version VST009.
Retrieving the Host Object’s SQL Version Program Versions When you compile a source file that contains the pragma shown above, the C compiler places the compiler version in its compiled source listing: Host Object SQL Version = 310 When the COBOL85 compiler compiles a program by using the SQLMAP option of the SQL directive, the compiler places its own SQL version in the summary section of the compiler listing.
SQL Program Versions Program Versions SQL Program Versions To prevent programs developed for older versions of SQL/MP from behaving unpredictably, either when they are running or when they are being used with new features and new catalog structures, SQL/MP assigns two types of versions to every SQL program: the program format version (PFV) and the program catalog version (PCV). The program format version (PFV) identifies the minimum version of the SQL executor needed to execute the program.
Program Catalog Version (PCV) Program Versions • • The format version of a program must be the same as or newer than the version of any object referred to by the program. The format version of a program has no relation to the version of the catalog in which the program is registered. A program cannot refer to any object with a version newer than the program’s format version. This is true even if the program refers only to older-version features of an object.
Where SQL Program Versions Are Stored Program Versions This statement explicitly has a COLLATE clause. Because the COLLATE clause first appeared in SQL version 300, the successful compilation of the statement sets the program catalog version to 300. In contrast, the following SQL statement refers to a column, COLUMNA, which includes a COLLATE clause in its definition.
Program Versions Where SQL Program Versions Are Stored Program Format Version in Catalog Tables In a version 300 or newer catalog, the SQL/MP compiler stores the PFV of every SQL program in the PROGRAMFORMATVERSION column of the PROGRAMS table. A PFV of 1 or 2 can be stored in a version 300 or newer PROGRAMS table. The PFV of a program registered in a version 1 or version 2 catalog is not defined.
Retrieving SQL Program Versions Program Versions Program Versions in File Labels The SQL/MP compiler stores the PFVs and PCVs in the SQL file labels of version 300 and newer programs and in the PROGRAMS table. If the version information stored in a program’s file label is not consistent with the version information stored in the PROGRAMCATALOGVERSION and PROGRAMFORMATVERSION columns of the PROGRAMS catalog table, it is likely that version errors will be returned.
Retrieving SQL Program Versions Program Versions For example, suppose that a program named PROGX has been SQL-compiled into a file named \SYS1.$VOL1.MYSVOL.PROGX. To retrieve the program’s PFV, you can execute these SQLCI commands: >> VOLUME \SYS1.$VOL1.MYSVOL; >> GET FORMAT VERSION OF PROGRAM PROGX; If the format version is 315, the two preceding statements yield: VERSION: 315 --- SQL operation complete. Similarly, you can retrieve the catalog version of a program.
Retrieving SQL Program Versions Program Versions Ordinarily, if a program is registered in a version 1 or version 2 catalog, the FILEINFO command returns zero values for the program’s PFV and PCV, meaning that program’s PFV and PCV are undefined. An exception to this rule occurs if all the following conditions are true: • • • The format version of a program was originally registered in a catalog of version 300 or newer. The catalog was later downgraded to version 2.
SQL Data Structure Versions Program Versions For example, suppose you want to restore from tape a program file named $VOL1.MYSVOL.PROGX, which is registered in the catalog $VOL1.CAT1. To display the catalog and format versions of the program, type: 32> RESTORE $TAPE,*.*.*, LISTALL,AUDITED,SQLCOMPILEON,DETAIL; The RESTORE utility restores the program file as an SQL program to its original location and registers the program in its original catalog. The DETAIL option produces the following display: $VOL1.
SQL Data Structure Version Assignment Program Versions Because the versions of the SQL data structures can change, programs must have access to the versions of the SQL data structures they are designed to use. The INCLUDE STRUCTURES directive can help check that your programs meet this requirement. With the INCLUDE STRUCTURES directive, a program can inform the host language compiler of the SQL data-structure versions it requires.
SQL Data Structure Version Assignment Program Versions data structures that a source program requires, place the INCLUDE STRUCTURES directive in the source program. When the host language compiler compiles your program, it generates the data structure versions specified in the INCLUDE STRUCTURES directive. The INCLUDE STRUCTURES directive is not available to version 1 and version 2 SQL programs.
Relation of Data-Structure Version to Program Format Version Program Versions Relation of Data-Structure Version to Program Format Version An SQL program cannot use data structures newer than its PFV; however, there is no requirement that all SQL data structures used in a program have to be the same version. You can place version 2 SQL data structures in a program whose PFV is 300 or newer if you do not need the features of newer-version SQL data structures.
Program Versions Mixing Data Structure Versions Compaq NonStop™ SQL/MP Version Management Guide —429833-001 5- 16
6 Mixed Version Networks When different versions of NonStop SQL/MP software are running on different nodes in a network, the network is called a mixed version network. A network can become a mixed version network when: • • One or more nodes in the network migrate to a new version of the software, while other nodes continue to operate with older version software. One or more nodes in a network revert to an older version of the software, while other nodes continue to run a newer version of the software.
Fallback Nodes in a Network Mixed Version Networks Fallback Nodes in a Network A fallback node is a node on which SQL software has a lower version number than a catalog, object, or program on the same node. Fallback occurs when you install an older version of SQL on a node after you create newer version catalogs, objects, or programs on the same node without first downgrading the user catalogs running on the same node.
Mixed Version Networks Newer Version Catalogs on Remote Nodes A fallback node is created when a node running SQL reverts to an older version of the software without first downgrading the user catalogs running on the same node. If a node does not downgrade its user catalogs when it reverts to an older version of the software, the objects and catalogs are no longer accessible to the older version SQL software currently installed on the node.
SQL Programs in a Mixed Version Network Mixed Version Networks Figure 6-3. Access to Newer Version Catalogs Node Running Version 2 NonStop SQL Version NonStop SQL Software Node Running Version 315 NonStop SQL/MP 1 Version 2 Objects Version 2 Objects Version 315 Catalog Version 2 Catalog 1. Error detected. VST013.vsd SQL Programs in a Mixed Version Network The rules determining operation of SQL programs in a mixed version network are similar to the restrictions controlling SQL/MP software.
Partitioned Objects in a Mixed Version Network Mixed Version Networks Partitioned Objects in a Mixed Version Network All partitions of a given object have the same version number. Each partition must obey the version-management rules for objects outlined in Section 3, Database Versions. Specifically, the version of every partition of an object must have a version that is the same as or older than the version of the catalog in which the partition is registered.
Mixed Version Networks Version 2 and Newer Version Software Version 2 and Newer Version Software Nodes running version 2 SQL software can operate in networks with remote nodes running version 300 or newer software. SQL programs running on version 2 nodes can access version 2 objects. They can also access version 2 catalogs that reside on remote nodes running version 300 or newer software. Of course, all operations must follow proper version-management rules to work properly.
Mixed Version Networks Using Version 2 Catalogs in a Network Using Version 2 Catalogs in a Network These version restrictions apply to version 2 catalogs: • You cannot upgrade a version 2 catalog to version 300 or newer from a node running version 2 SQL software. To upgrade a version 2 catalog in this situation, you must execute the UPGRADE CATALOG or UPGRADE SYSTEM CATALOG statement from a node running version 300 or newer SQL software.
Mixed Version Networks Version 300 or Newer SQL Utilities in a Mixed Version Network As a general rule, SQL programs being executed on nodes running version 1 SQL should not attempt to access objects on nodes running version 300 or newer SQL software.
Mixed Version Networks SQL Utilities and Fallback Nodes attempt to access objects or programs on a fallback node or attempt to register version 300 or newer objects or programs in older version catalogs. • These SQL utilities can operate successfully on older version objects, programs, or catalogs residing on nodes running version 300 or newer SQL software.
Mixed Version Networks • • SQL Utilities and Fallback Nodes You attempt to use RESTORE to register new objects in older version catalogs. For example, SQL returns a version error if you attempt to use RESTORE to restore a version 300 collation and register it in a version 2 catalog. You attempt to use RESTORE to register a program in a catalog whose version is older than the catalog version of the program.
Mixed Version Networks Version 300 or Newer Utilities and Newer Version Software Version 300 or Newer Utilities and Newer Version Software You can use the version 300 or newer SQL utilities and the BACKUP and RESTORE utilities to perform an operation on an object or a program residing on a node running SQL/MP software newer than version 300. However, the object or program and the catalogs in which it is registered must not have a version newer than 300.
Mixed Version Networks • Version 300 or Newer Utilities and Newer Version Software BACKUP or RESTORE cannot access an object whose version is newer than the version of BACKUP or RESTORE being used. For example, these situations result in a version error: ° You attempt to use version 300 BACKUP to back up a table whose version is newer than 300.
7 Version Compatibility for SQL Programs When to Modify and Recompile Programs If your existing programs were developed on a C-series operating system, you should be aware of changes introduced in the D-series release of SQL. For information on the changes introduced with the D-series release, see the D-Series System Migration Planning Guide and the Guardian Application Conversion Guide.
Version Compatibility for SQL Programs RELEASE1 and RELEASE2 Options in Older Version Programs When a version 1 dynamic SQL program is executed or compiled on a node running version 300 or newer SQL/MP software, the execution or compilation can fail also if the program’s source code checks for a NULLP (null pointer) in the VAR-PTR field of the SQLDA data structure. In version 1 of SQL, NULLP is returned in the VAR-PTR field of the SQLDA data structure when the names buffer is too small.
Version Compatibility for SQL Programs Developing Version Independent SQL Programs Developing Version Independent SQL Programs Version independent programs are programs that can be executed successfully by more than one version of the SQL/MP software and are expected to be supported in future versions of the software.
Version Compatibility for SQL Programs • • Version Independent SQL Data Structures Replace SQLGETCATALOGVERSION procedures with GET VERSION OF CATALOG statements. Replace SQLGETSYSTEMVERSION procedures with GET VERSION OF SYSTEM statements. In existing version 1 or version 2 programs that contain RELEASE compiler options, remove the RELEASE options and replace them with INCLUDE STRUCTURES directives.
Version Compatibility for SQL Programs • • • • Version Independent SQL Data Structures If you upgrade the SQLDA structure to a newer version, initialize any fields in the new SQLDA structure that the older version structure did not contain. If you allocate SQL data structures at run time (not necessary if you use INCLUDE STRUCTURES), use the length literals returned by version 300 and newer INCLUDE directives rather than specifying a length directly.
Version Compatibility for SQL Programs Version Independent SQL Data Structures specify INCLUDE STRUCTURES ALL VERSION 2 or INCLUDE STRUCTURES SQLDA 2. To continue using version 1 SQLDA structures in a version 300 or newer program, follow the guidelines for converting to a version 300 or newer SQLDA, but use the INCLUDE STRUCTURES ALL VERSION 1 directive or the INCLUDE STRUCTURES SQLDA 1 directive.
Version Compatibility for SQL Programs • SQL Compilation for Version Independence If you change the version of an SQL data structure, you must recompile all the modules that declare the structure as an external structure. Otherwise, run-time version errors can result. SQL Compilation for Version Independence When you develop an SQL program that is to be executed on nodes running different versions of SQL/MP software, you must SQL compile the program separately on each node.
Version Compatibility for SQL Programs • • • Reverting to an Older Version The SQL compiler version must be the same as or newer than the newest host object SQL version in the host object file. SQL compilation fails if the SQL compiler version is older than the host object SQL version. An SQL compiler running on one node of a network cannot compile host object files of any version on another node of the network.
Reverting to an Older Release Version Compatibility for SQL Programs Figure 7-1. Results of Using an Older Version SQL Compiler Node Running Version 310 NonStop SQL PROG1 HOSV=2 PROG2 HOSV=310 Node Running Version 2 NonStop SQL (Reverted From Version 310) PROGX HOSV=310 1 Binder Version 2 SQL Compiler PROGX HOSV=310 1 Compilation attempt results in version error. VST015.
Reverting to an Older Release Version Compatibility for SQL Programs Figure 7-2. Results of Using an Older-Release Compiler Node Running Version 2 (D-Series) NonStop SQL Source Program Host Language Compiler Version 2 Code SQL Version=2 D-Series Node Running Version 2 (C-Series) NonStop SQL (Reverted From D-Series) HOSV=2 D-Series 1 Version 2 C-Series Host Object File 1 Host Object File SQL Compiler HOSV=2 D-Series Compilation attempt results in version error. VST016.
Version Compatibility for SQL Programs Migrating to a Newer Version Migrating to a Newer Version You do not need to recompile an existing SQL program to continue running it on a node on which the SQL/MP software is upgraded to a newer version. If you do not recompile the program, the program executes in the same way it did under the older version software. However, if you do recompile the program, the way in which it is recompiled can affect the way it executes.
Version Compatibility for SQL Programs Migrating to a Newer Version ° References to SQLDA fields that use new data types ° Checks for NULLP in SQLDA For more detailed information on the changes between version 1 and version 2 of SQL, see information about release compatibilities in the NonStop SQL/MP Installation and Management Manual.
Version Compatibility for SQL Programs • • • Migrating to a Newer Version A query that uses a predicate involving exponentiation to select from a table that has a partition on a node using version 1 SQL software. The version 1 software does not recognize exponentiation. In this case, the version 300 or newer SQL compiler again selects a plan in which the predicate is evaluated by the executor instead of by the disk process.
Using Parallel Execution Plans in a Mixed Version Network Version Compatibility for SQL Programs Figure 7-3.
SQL Execution in a Mixed Version Network Version Compatibility for SQL Programs The SQL compiler does not select parallel execution as a plan for query execution if the queried object is on a node using version 1 SQL software; the compiler does not select hash joins if the queried object is on a node using version 2 software. Figure 7-4 illustrates parallel execution when these query execution techniques are not supported by all the nodes in a mixed version network. Figure 7-4.
Version Compatibility for SQL Programs SQL Execution and Fallback Nodes version 1.
Execution Performance in Mixed Version Networks Version Compatibility for SQL Programs Figure 7-5. Effect of Reverting to Older Version Software Node Running Version 2 NonStop SQL (Reverted From Version 315) Node Running Version 315 NonStop SQL/MP Version 315 SQL Program 1 Version 315 Object 1 Version 315 Object 1 1 Version 2 SQL Program Access attempts result in version errors. VST019.
Version Compatibility for SQL Programs Program Invalidation in Mixed Version Network problems encountered because of older version software. Automatic recompilation is not attempted. (For details of the features not supported by older version software, see the discussion of query execution plans under SQL Compilation in a Mixed Version Network on page 7-7.
A Summary of Feature, Catalog, and Data Structure Changes This appendix describes: • • Changes in features that define different versions of the NonStop SQL/MP software. Format changes made to catalogs and SQL data structures for different versions of the software. For an overview of SQL/MP version management, see Section 1, Overview of Version Management. For a description of object and catalog versions, see Section 3, Database Versions.
Summary of Feature, Catalog, and Data Structure Changes Version 300 Features Table A-1.
Summary of Feature, Catalog, and Data Structure Changes Version 310 Features Version 310 Features Version 310 object definitions can include all version 1, version 2, and version 300 features, in addition to the features listed in Table A-3. Table A-3.
Summary of Feature, Catalog, and Data Structure Changes Compiler Options Table A-5. Version 350 Features Feature Description Table feature Catalog manipulation statements Format 2 enabled tables. New value for PARITIONARRAY clause, FORMAT2ENABLED. New limits for EXTENT for Format 2 enabled partitions. Create a Version 350 catalog. Catalog tables remain Format 1 tables themselves. Table manipulation statements Allow creation of Format 2 enabled tables and indexes, and Format 2 partitions.
Summary of Feature, Catalog, and Data Structure Changes • Changes to Catalog Tables The addition of the OSSFILE column to the PROGRAMS table causes the catalog version of the SQL program to become at least 315 when used with SQL/MP version 315. Changes to Catalog Tables The format of catalog tables has been changed in each release. This subsection summarizes the catalog table changes made with the introduction of each version of the SQL/MP software through version 315.
Summary of Feature, Catalog, and Data Structure Changes Catalog Changes in Version 300 Table A-6 lists the changes in tables introduced with version 2. Table A-6.
Summary of Feature, Catalog, and Data Structure Changes Catalog Changes in Version 310 Table A-7. Table Changes in Version 300 (page 2 of 2) Changes Tables or Columns Affected New values allowed in the COLUMNS table New values are allowed in the FSDATATYPE column corresponding to the new data type, NCHAR. OBJECTVERSION records the object versions of indexes. SECURITYMODE records the type of security used for the index; set to G for Guardian security.
Summary of Feature, Catalog, and Data Structure Changes Catalog Changes in Version 315 Table A-8 lists the data structure changes made in version 310. Table A-8. Table Changes in Version 310 Changes Columns affected Columns added to the PROGRAMS table FORCE SIMILARITYINFO RECOMPILEMODE CHECKMODE REGISTERONLY Columns added to the TABLES table SIMILARITYCHECK Catalog Changes in Version 315 In version 315 of SQL/MP, the OSSFILE column was added to the PROGRAMS table.
Summary of Feature, Catalog, and Data Structure Changes Changes to SQL Data Structures Table A-10. Table Changes in Version 350 Changes Column affected Columns added to the FILES table PRIMARYEXT2, SECONDARYEXTENT2, EOF2, FILEFORMAT Columns in the FILES table superseded by columnslisted above PRIMARYEXT, SECONDARYEXTENT, EOF New value allowed in the PARTIONARRAY column A value of FORMAT2ENABLED refers to Format 2 enabled tables or indexes.
Summary of Feature, Catalog, and Data Structure Changes Data Structure Changes in Version 300 Table A-11 lists the data structure changes made in version 2. Table A-11.
Summary of Feature, Catalog, and Data Structure Changes Data Structure Changes in Versions 310 and 315 Table A-12.
Summary of Feature, Catalog, and Data Structure Changes Data Structure Changes in Version 330 Compaq NonStop™ SQL/MP Version Management Guide —429833-001 A -12
Index A ALTER INDEX statement 3-18 ALTER statement and object versions 3-17/3-18 ALTER TABLE statement 3-18 ALTER VIEW statement 3-18 Alternate language message file version 4-2 Alternate language message files DEFINE command 4-3 retrieving a version 4-3 version of 4-2 AUTOCREATECATALOG option, RESTORE utility 6-10 Automatic recompilation 7-11 Automatic upgrading and DDL operations 3-16 B BACKUP utility catalog version 6-10 collation version 6-12 fallback node 6-10 mixed version network 6-10/6-12 newer ver
C Index Catalog versions (continued) changing 3-5 description of 3-2 initializing 3-5 using catalog queries 3-8 version 1 catalogs creating 6-8 upgrading 6-8 version 2 catalogs mixed version network 6-6 version 1 software 6-3, 6-8 VERSIONS table 3-5 viewing 3-9 CATALOGFORMAT column, VERSIONS table, in user catalog 3-6 CATALOGS table 3-6 Catalogs tables catalog version 3-5 object version 3-12 CATALOGVERSION column, CATALOGS table, in system catalog 3-6 CATALOGVERSION column, VERSIONS table, in user catalog
D Index C-series operating system 7-1 D Data structure changes description of A-9 version 2 A-10 version 300 A-9, A-10 version 310 A-8 version 315 A-9, A-11 version 330 A-11 Data structure versions assignment 5-13 converting programs 7-4 description of 5-12 host language compilers 5-12/5-15 mixing 5-15 program version 5-15 Data structures converting programs to use new versions 7-4 global scope 7-6 version independent 7-4/7-7 Database versions catalog versions 3-1 program catalog versions 5-6 program for
G Index Fallback node (continued) SQL execution 7-16 SQL utilities version 6-9 FastSort version 2-3 Features, relationship to version A-1 File label collation version 3-12 object version 3-12 program catalog version 5-9 program format version 5-9 view version 3-12 FILEINFO utility newer version software 6-11 object version 3-14 retrieving program versions 5-9 Future version 1-3 G GET CATALOG OF SYSTEM statement 3-9 GET CATALOG VERSION OF PROGRAM statement 5-9 GET FORMAT VERSION OF PROGRAM statement 5-9 G
L Index Index version (continued) file label 3-12 initializing 3-10 object version dependency 3-15 RESTORE utility 6-12 user catalog 3-12 INDEXES catalog tables 3-12 Initializing and changing catalog versions 3-3 Initializing user catalog version 3-4 Interim Product Modifications (IPMs) 2-2 Length literal, version independence 7-5 Mixed version network (continued) parallel execution plan 7-14 partitioned objects 6-5 program invalidation 7-18 program versions 6-4 RESTORE utility 6-10/6-12 rules 6-4 SQL c
P Index Object version (continued) automatic upgrading 3-16 catalog version 3-11 changing 3-16 DDL operations 3-16 dependencies 3-15 description of 1-5 DROP COLLATION statement 3-17 DROP CONSTRAINT statement 3-17 DROP INDEX statement 3-17 DROP statement 3-17 DROP TABLE statement 3-17 DROP VIEW statement 3-17 file label 3-12 FILEINFO utility 3-14 GET VERSION statement 3-13 initializing 3-10/3-11 older version utilities 6-11 program catalog version 5-4 program format version 5-4 querying catalog 3-13 retrie
Q Index Program format version (PFV) BACKUP utility 6-10 catalog tables 5-8 catalog version 5-8 data structure version 5-15 database versions 5-5 description of 1-5, 5-5 file label 5-9 FILEINFO utility 5-9, 5-10 GET VERSION OF PROGRAM statement 5-4 object version 5-4 querying catalog 5-11 RESTORE utility 5-11, 6-10 Program invalidation mixed version network 7-16, 7-18 Program versions data structure versions 5-15 description of 1-5, 5-5 mixed version network 6-4 RESTORE utility 5-11 rules for relationship
S Index RESTORE utility (continued) fallback nodes 6-10 index version 6-12 initializing user catalog version 3-4 mixed version network 6-10/6-12 newer version software 6-11 older version catalogs 6-9 older version software 6-10 program catalog version 5-11 program format version 5-11 retrieving program version 5-9 version 2-3 Retrieving software version 2-4 Reverting to older version 1-7 S SCI version 2-3 Software release 2-1 Software version components 2-3 host language compiler SQL version 5-1 host obj
S Index SQLCAGETINFOLIST procedure 7-5 SQLCAT version 2-3 SQLCI ENV command 4-1 SQLCI version 2-3 SQLCI2 version 2-3 SQLCOBOL version 2-3 SQLCOMP version 2-3 SQLDA converting to new version 7-5 description of 5-12 using mixed versions 7-6 version 1 7-5 version 2 7-5, A-9, A-10 version 300 A-9, A-10 versions 5-12 SQLEXE version 2-3 SQLFIL version 2-3 SQLGETCATALOGVERSION procedure description of 3-8 version independence 7-4 SQLGETOBJECTVERSION procedure description of 3-14 retrieving object version 3-13 ve
T Index System catalog version changing 3-5 description of 1-2, 3-2 initializing 3-5 retrieving 3-7/3-9 storage 3-5/3-7 user catalog versions, comparison 3-2 System version 2-3 T Table version ALTER statement 3-18 file label 3-12 initializing 3-10 object version dependency 3-9, 3-15 user catalog 3-12 Tables INDEXES 3-12 PROGRAMS 5-8/5-11 TABLES catalog table 3-12, 3-13 U Unsigned decimal numbers, query execution plans 7-13 UPGRADE CATALOG statement changing version of user catalog 3-4 version 1 software
V Index Version 310 catalog changes A-7 data structure changes A-8 description of A-3 relationship to release number 2-1 Version 315 catalog changes A-8 data structure changes A-9, A-11 description of A-3 OSSFILE column A-8 relationship to release number 2-1 Version 320, catalog changes A-8 Version 330 data structure changes A-11 SQLSA A-11 Version 335, description of A-3 Version 345, catalog changes A-8 Version 345, features A-3 Version 350, catalog changes A-8 Version 350, description of A-3 VERSION col
W Index Version numbers (continued) system components 1-2 Versions and releases of SQL/MP software 2-1 VERSIONS table user catalogs 3-5 Versions, categories of 2-1 View version ALTER statement 3-18 DROP statement 3-17 file label 3-12 initializing 3-10 object version dependency 3-9, 3-15 user catalog 3-12 VPROC program 5-3 W WITH HELP TEXT clause, CREATE TABLE statement 3-10 Special Characters _SQL_MSG_system DEFINE command 4-3 Compaq NonStop™ SQL/MP Version Management Guide —429833-001 Index -12
