HP NonStop SQL/MP User Manual

Memory Considerations
HP NonStop SQL/MP Programming Manual for C—429847-008
B-4
Avoiding Memory Stack Overflows
The system allocates real memory in 16 KB pages. If an SQL statement uses only part
of a page, the system allocates the entire page. Therefore, the real memory used by
embedded SQL statements can be larger than the figures shown in Table B-2 on
page B-3.
A program can encounter memory problems in these situations:

The program contains a large number of embedded SQL statements.

The program runs on a system with limited memory (for example, 16 MB or less).

The program runs in a CPU that is also running a large number of other programs.
To reduce the memory use in the extended data segment, follow these guidelines:

Declare only the host variables that your program actually requires.

Declare all host variables in one Declare Section, if possible. The system allocates
the host variables contiguously in one or more pages, rather than allocating each
host variable in a separate page.

Run SQL statements in listing order as often as possible. Thus, the SQL
statements can share many of the pages in the extended data segment.

As a last measure, use dynamic SQL statements. Using dynamic SQL statements
can reduce memory use; however, it can also degrade a program’s performance
because of the additional SQL run-time compilations.
Avoiding Memory Stack Overflows
To avoid memory stack overflows for most SQL statements, the SQL executor needs at
least 3000 words of available stack space. To calculate the approximate stack space
that should be available to run an SQL statement, use this formula:
Stack Space (words) = 3000 +
300 * ( number of referenced tables - 3 )
For example, using this formula, an SQL statement that refers to five tables needs
approximately 3600 words of stack space:
Stack Space (words) = 3000 + ( 300 * (5-3) )
= 3600
The SQL executor handles a stack overflow caused by an SQL statement as follows:

Less than 1024 words of stack space are available. (The limit of 1024 words is an
arbitrary number that is used to prevent problems for existing applications, and
might be increased in a future RVU.)
If there is enough stack space available to call an error handling routine, the SQL
executor returns SQL error 8003. If there is not enough stack space to call the
routine, the executor abends without returning a message.

At least 1024 words of stack space are available.

HP NonStop SQL/MP Specifications

General

Platform	HP NonStop Servers
Data Model	Relational
SQL Standard	ANSI SQL
Concurrency Control	Multi-Version Concurrency Control (MVCC)
Fault Tolerance	Yes
High Availability	Yes
Category	Database Software
Initial Release	1980s
Operating System	HP NonStop OS
License	Proprietary
Type	Relational Database Management System
Transaction Management	ACID Compliance
Developer	Hewlett Packard Enterprise

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.