Considerations for mixed-node network

Database distribution in SQL/MX is transparent when all systems in a network run the same release of SQL/MX. User data and metadata in a catalog can be accessed from all systems where that catalog has been registered without considering where the catalog was originally created. When all systems in the network have been upgraded to SQL/MX 3.5, then the metadata can be upgraded as well, with no special considerations.

However, in a network of NonStop systems where some systems must be upgraded to SQL/MX 3.5 while other systems need to remain at a lower version of SQL/MX for a while, you must consider the impact on distributed SQL/MX catalogs.

As described in Interoperability of SQL/MX releases, a lower version system can execute SQL queries that access a higher version system - but the converse is not possible. That means that once a system has been upgraded to SQL/MX 3.5, that system can access neither user data nor metadata on a lower version system in the network.

Also, a system that remains on a lower version cannot access version 3500 metadata or user data. Therefore, metadata for a distributed catalog must remain on the lower version until all involved systems have been upgraded to SQL/MX 3.5. Scenarios 1a and 1b below describe two different aspects of having a distributed catalog in a mixed-version network.

Alternatively, if metadata for a catalog must be upgraded to version 3500 then that catalog cannot be registered on a lower version system. Scenario 2 below describes that possibility.

Scenario 1a: Distributed catalog with metadata tables on a system that remains on a lower version of SQL/MX

A system that is upgraded to SQL/MX 3.5 cannot access the metadata for that catalog, and it cannot access user data in that catalog that resides on the lower version system. The inability to access metadata means that the 3.5 system cannot:

  • compile queries that access that catalog

  • execute DDL or Utility operations that access that catalog

However, under specific conditions, it can continue to access user data in that catalog that resides on the 3.5 system itself:

  • access is from an embedded application using static SQL only

  • the application does not use prototype names for SQL/MX objects

  • the application does not require automatic recompilation

The lower version system will continue to have full access (DDL, DML, Utilities) to all user data and metadata in the catalog, due to the lower-to-higher version one-way interoperability.

Scenario 1b: Distributed catalog with metadata tables on a system that is upgraded to SQL/MX 3.5

After the upgrade, the system running SQL/MX 3.5 can access the metadata tables for that catalog, and it can access user data in that catalog that resides only on the system itself and on other systems in the network running SQL/MX 3.5. The system running SQL/MX 3.5 cannot:

  • compile queries that access database objects that reside fully or partially on a lower version system

  • execute DDL or Utility operations that access database objects that reside fully or partially on a lower version system

  • execute queries that access user data in that catalog that resides on a lower version system in the network

  • access system catalog metadata for that catalog that resides on a lower version system in the network. This means that the following DDL commands are not available because they will access system catalog metadata on all systems where the catalog is registered:

    • CREATE SCHEMA

    • DROP SCHEMA

    • GIVE SCHEMA

    • UPGRADE/DOWNGRADE

    • GIVE CATALOG

    • DROP CATALOG

    • UNREGISTER CATALOG

The lower version system will continue to have full access (DDL, DML, Utilities) to all user data and metadata in the catalog, due to the lower-to-higher version one-way interoperability.

Scenario 2: Catalog where metadata must be upgraded to version 3500

Version 3500 metadata is available only on systems running SQL/MX 3.5 or higher. A catalog that is created or registered on a lower version system in the network cannot have its metadata upgraded to version 3500.

If you have a catalog that:

  1. has metadata tables on a system that has been/will be upgraded to SQL/MX 3.5

  2. is registered on a lower version system

  3. requires version 3500 anyway, because new features must be used

then the catalog must be unregistered from all lower version systems where it is registered, using the UNREGISTER CATALOG command, see the SQL/MX Reference Manual for details.

A catalog cannot be unregistered from a system if user data in that catalog resides on that system. This means that partitions that reside on a lower version system must be moved to a system running SQL/MX 3.5 before they are unregistered.

The catalog can be unregistered before or after the system is upgraded to SQL/MX 3.5, however if it is to be unregistered after the upgrade then the UNREGISTER CATALOG command must be executed from the lower version system. In either case, the catalog must be unregistered before its metadata is upgraded to version 3500.

RDF replication

You can use RDF to replicate from a system running earlier SQL/MX release to a system running later release. You cannot use RDF and replicate from a system running a later release to a system running an earlier release.