SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-36
Using DEFINEs to Switch Databases
Dynamically Selecting Different Databases
This scenario describes a situation where you have several similar SQL/MP databases
and you want a program to dynamically determine which database to access. For
example, your program might select the database depending on the type of
transaction. You do not want to use dynamic SQL statements because they require
extra programming time to write and can degrade your node’s performance during
execution. You could combine all the databases into a single database, but the
management of a large database would be complicated.
To provide dynamic access, specify DEFINEs for all table names and then use
execution-time name resolution and the similarity check.
Follow these steps to implement this solution:
1. Modify the program as follows:
•
Specify the CONTROL QUERY BIND NAMES AT EXECUTION directive in the
source file to enable execution-time name resolution for all DML statements.
You might need to specify more than one directive depending on the structure
of your program and the scoping rules for the host language you are using. For
more information, see The SQL/MP Reference Manual.
•
Use DEFINEs in all SQL DML statements.
•
Add source code that alters the DEFINEs used in each SQL statement to point
to the appropriate database when the SQL statement is executed.
2. Enable the similarity check for each table or protection view specified in the SQL
statements as follows:
•
For existing tables, use the ALTER TABLE or ALTER VIEW statement with the
SIMILARITY CHECK ENABLE clause.
•
If you are creating a new table or protection view, use the CREATE TABLE or
CREATE VIEW statement with the SIMILARITY CHECK ENABLE clause.
3. Explicitly SQL compile the program with the CHECK INOPERABLE PLANS option
to enable the similarity check for the program.
4. Run the program. The program uses the different DEFINE values to determine the
database to access. The SQL executor resolves the DEFINE names at statement
execution time and executes the similarity check to prevent automatic
recompilation.