SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Understanding and Planning SQL/MX Tables

HP NonStop SQL/MX Installation and Management Guide—523723-004

4-2

Avoid Renaming Nodes

After an SQL/MX database node has been installed and initialized, do not attempt to

change the node name. The node name is recorded in system metadata entries and

file labels throughout the database. The node number not recorded anywhere.

Avoid Changing Object Names or Moving Database Objects

You should develop and implement an object-naming strategy that prohibits changing

object names or moving named objects. This strategy is important because:

•

Changing object names is a time-consuming process that is easily subject to

human error. To change an object name, you must create a new object, copy the

data to the new object, and drop the original object.

•

SQL/MX object names are stored in system metadata and SQL programs. When

you change an object name, all references to the old object name in metadata and

programs must be updated to avoid the replication of invalid metadata and the

failure of programs to execute successfully. Many production databases contain

hundreds or thousands of database objects and hundreds of programs or MXCI

scripts that reference the original object names. No automated tools exist to

perform these metadata or programmatic updates.

The best way to avoid changing object names is to carefully plan for and develop in

advance object names that can be retained over the years and to enforce a policy

against changing names. To help in this endeavor, do not name objects with company,

project, product, or employee names that might need to be changed for legal reasons.

Understand the Schema Ownership Rule

It is important that you understand the rule of schema ownership and consider it in your

object-naming practices. The schema identified in a three-part ANSI name can only be

owned by its creator. All objects in the schema, including tables and indexes, are

owned by that user. While the schema owner or the super ID user can use the GRANT

statement to assign authorization to perform specific actions on a particular schema

object (SELECT, DELETE, INSERT, UPDATE, ALL PRIVILEGES, and so on), only the

schema owner has unrestricted access to all objects in the schema.

Accordingly, you should organize and name database objects with the schema

ownership rule in mind. If, for example, you create a database with the tables sales

and employees in the same catalog and schema, and the schema owner handles

only human resources records, those two tables are invariably linked when they should

not be. Consider the schema ownership rule when planning and creating database

objects that span many different organizational, geographical, and application

groupings.

For more information about GRANT and REVOKE, see Section 7, Creating an

SQL/MX Database, and the SQL/MX Reference Manual.