HP NonStop SQL/MP User Manual

Program Invalidation and Automatic SQL
Recompilation
HP NonStop SQL/MP Programming Manual for C—429847-008
8-10
Preventing Automatic Recompilations
Specifying the CHECK INOPERABLE PLANS Option
To direct the SQL executor to use the similarity check for a program, specify the
CHECK INOPERABLE PLANS option when you explicitly compile the program as
shown in the next example:
SQLCOMP /IN sqlprog,OUT $s.#sqlist/ CHECK INOPERABLE PLANS
For the complete syntax of the CHECK option, see Section 6, Explicit Program
Compilation.
The CHECK INOPERABLE PLANS option directs the SQL compiler to store similarity
information in the program file. The SIMILARITYINFO column in the PROGRAMS table
indicates whether a program file contains similarity information:
To use the CHECK INOPERABLE PLANS option, you must have an SQL/MP software
version of 310 or later. If you specify a CHECK option, the SQL compiler sets the
program’s PFV to 310 (or later). The SQL compiler also sets the program’s PCV to 310
(or later). Therefore, the SQL catalog in which the program is registered must have a
catalog version of 310 (or later).
For more information, see the SQL/MP Version Management Guide.
Enabling the Similarity Check for Tables and Protection
Views
To use the CHECK INOPERABLE PLANS option, the similarity check must be enabled
for any referenced tables or protection views at run time. You must explicitly enable the
similarity check for a table or protection view, including any underlying tables for the
view, as shown in these DDL statements. (NonStop SQL/MP implicitly enables the
similarity check for other SQL objects.)
Y The execution plans in the program file contain similarity information.
N The program file does not contain similarity information.
CREATE TABLE table-name ...
[ SIMILARITY CHECK { ENABLE | DISABLE } ]
CREATE VIEW view-name ...
FOR PROTECTION
...
[ SIMILARITY CHECK { ENABLE | DISABLE } ]
ALTER TABLE table-name ...
[ SIMILARITY CHECK { ENABLE | DISABLE } ]
ALTER VIEW view-name ...
[ SIMILARITY CHECK { ENABLE | DISABLE } ]

HP NonStop SQL/MP Specifications

General

Brand	HP
Model	NonStop SQL/MP
Category	Software
Language	English

Summary

1. Introduction

Developing a C Program

Outlines the process of creating C programs with embedded SQL statements.

Dynamic SQL

Describes how to construct, compile, and run SQL statements at runtime.

2. Host Variables

3. SQL/MP Statements and Directives

4. Data Retrieval and Modification

Single-Row SELECT Statement

Retrieves a single row of data from tables or views into host variables.

Multirow SELECT Statement

Returns multiple rows from tables or views, one row at a time, using cursors.

INSERT Statement

Inserts one or more rows into a table or protection view using host variables.

Using SQL Cursors

Explains how to use named pointers to access sets of rows one at a time.

5. SQL/MP System Procedures

6. Explicit Program Compilation

Explicit Program Compilation

Outlines the general steps for compiling C programs with embedded SQL.

Developing a C Program in the Guardian Environment

Details C program development within the Guardian environment.

Developing a C Program in the OSS Environment

Developing a C Program in a PC Host Environment

7. Program Execution

Required Access Authority

Specifies the access permissions needed to run NonStop SQL program files.

Entering the TACL RUN Command

Running a Program in the OSS Environment

8. Program Invalidation and Automatic SQL Recompilation

Program Invalidation

Explains how SQL program files can become invalid due to changes or errors.

Causes of Program Invalidation

Automatic SQL Recompilation

Describes the run-time recompilation process invoked by the SQL executor.

9. Error and Status Reporting

Returning Error and Warning Information

Using the WHENEVER Directive

10. Dynamic SQL Operations

Uses for Dynamic SQL

Identifies scenarios where dynamic SQL programs are beneficial.

Dynamic SQL Statements

Summarizes dynamic SQL statements usable in a C program.

Dynamic SQL Features

11. Character Processing Rules (CPRL) Procedures

Appendix B: Memory Considerations

Estimating Memory Requirements

Avoiding Memory Stack Overflows

Provides guidelines to prevent stack overflows for SQL statements.