SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-35
Using DEFINEs to Switch Databases
>> CREATE TABLE =EMPLOYEE
+> (EMP_NUM PIC 9(6) DEFAULT SYSTEM NOT NULL,
+> EMP_NAME PIC X(30) NO DEFAULT NOT NULL,
+> SS_NUMBER PIC X(11) NO DEFAULT NOT NULL,
+> ADDRESS PIC X(30) DEFAULT SYSTEM NOT NULL,
+> CITY PIC X(30) DEFAULT SYSTEM NOT NULL,
+> ST PIC X(2) DEFAULT SYSTEM NOT NULL,
+> ZIP_CODE PIC X(5) DEFAULT SYSTEM NOT NULL,
+> PRIMARY KEY EMP_NUM)
+> CATALOG =PR_CATALOG;
--- SQL operation complete.
Using DEFINEs to Switch Databases
By using the similarity check and DEFINEs, you can run programs against different
databases or dynamically select a database without auto-recompilation.
Running a Program Against Different Databases
This scenario describes a situation where you explicitly SQL compile a program using
a specific database. Several users, each with a different but similar database, want to
run the program. Each user wants to specify a set of DEFINEs that point to the
respective new database.
To allow access to different databases, specify DEFINEs for all tables and protection
views and use the similarity check to avoid automatic recompilation.
Follow these steps to implement this solution:
1. Specify DEFINEs for all tables and protection views used in the SQL statements.
These DEFINEs should point to tables and protection views in the first database.
2. Explicitly SQL compile the program with the CHECK INOPERABLE PLANS option
to enable the similarity check for the program.
Each user should then perform these steps:
1. 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.
2. Run the program with DEFINEs that point to the new database. The SQL executor
uses the similarity check to compare the original tables with the new tables. If the
similarity check passes for an SQL statement, the SQL executor executes the
statement without recompiling it. (The usage information is available only for the
original tables specified during the explicit SQL compilation.)