SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-41
Moving Programs Without Recompilation
Restrictions for the NOREGISTER Option
These static SQL statements, when compiled with the NOREGISTER ON option, must
use execution-time name resolution, or the SQL compilation fails with SQL error 2109:
•
DML statements: SELECT, INSERT, UPDATE, DELETE, and DECLARE CURSOR
•
LOCK and UNLOCK statements
•
GET VERSION and GET CATALOG statements
To prevent automatic SQL recompilation of these statements at SQL load time, specify
the CHECK INOPERABLE PLANS option during explicit compilation (if the SQL names
used in the statements are changed after explicit SQL compilation).
Example: Installing a Program at a New Location Without
Recompilation
The next example describes a scenario where you might develop a program on a
development system and then move the program to a production system. You do not
want new plans to be generated for the production system. You would like to avoid a
required compilation on the production system. The recompilation causes downtime for
the program and degrades the performance for the system.
To avoid recompilation, enable the similarity check for the program and any referenced
tables or protection views. After compiling the program on the development system,
move it to the production system and then install it by using the REGISTERONLY
option. Follow these steps:
1. On the development system, explicitly SQL compile the program using the CHECK
INOPERABLE PLANS option. Specify DEFINEs that point to objects on the
development system. You are not required to enable the similarity check for the
tables or protection views on the development system.
2. Move the program to the production system by using the FUP or SQLCI DUP
command or the BACKUP and RESTORE program.
3. On the production system, 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.
4. On the production system, run SQLCOMP with the REGISTERONLY ON option to
register the program in an SQL catalog. Specify a catalog name, if you wish, or use
the default catalog. This operation is much faster than explicitly compiling the
entire program, because it does not generate new execution plans.