SQL/MX Database Distribution White Paper

NonStop SQL/MX Database Distribution White Paper 22 November 2004
Hewlett-Packard Company – 529627-001 Page 9 of 12
Pros:
• Applications need not manage table names programmatically.
• Application modules can be moved between nodes without SQL compilation.
Cons:
• Ad-hoc queries must filter out replicated rows within the single table.
• Loading or updating read-only tables is complicated because each table now contains multiple
copies of the business data.
• Logical database design needs to consider replication issues.
• Application programs need to be replication aware. This design decision might prevent other
forms of table design later, including the use of the possible future ANSI Name DEFINEs.
2.2.5. Comparing the Alternatives
Source Code Changes
Converting an SQL/MP application to SQL/MX requires source code changes, even in a nondistributed
environment, because SQL/MP and SQL/MX table names are different. Unfortunately, converting a
nondistributed SQL/MX application and database to a distributed SQL/MX application and database with
replicated tables also requires some amount of source code changes because table names change, or
because the application is required to somehow be replication-knowledgeable, or both.
Source Code Changes Apart From Table Names in Queries
The following alternatives require source code changes because they require the application to perform
query-related administrative tasks using the name of the local node:
• Alternative 1: Application-Managed Node-Specific Names
• Alternative 4: Replication Through Partitioning
Logical Database Design Changes
Because it adds a partitioning column to the table layout of read-only tables, only Alternative 4, Replication
Through Partitioning, requires logical database design changes.
Module Compilation Required on Each Node
Alternative 2, Node Specific Default Schema Names, requires module compilation on each node because it
uses query defaults that are resolved at query compilation time. Alternative 3, Individual Catalogs, would
benefit from module compilation on each node, however, because such a strategy avoids late name
resolution of read-only table names.
Copies of Read-Only Tables Not Globally Visible
Alternative 3, Individual Catalogs, does not support globally visible, local copies of tables. The loading or
updating of such copies must happen on the node where the copy resides, rather than on a central node.