SQL/MP Programming Manual for C

Data Retrieval and Modification

HP NonStop SQL/MP Programming Manual for C—429847-008

4-2

Opening and Closing Tables and Views

NonStop SQL/MP automatically opens and closes tables and views during the

execution of DDL statements, DML statements, and SQL utility operations such as a

LOAD or COPY. NonStop SQL/MP opens a table or view when a host-language

program runs the first SQL statement that refers to the table or view and then closes

the table or view when the program that opened it stops. A program cannot explicitly

open an SQL table or view; however, a program can force NonStop SQL/MP to close a

table using the CLOSE TABLES option of the FREE RESOURCES statement.

By default, NonStop SQL/MP opens partitions of base tables and indexes only if they

are needed by a program. To cause NonStop SQL/MP to open all indexes and

partitions the first time a partition is accessed, use the OPEN ALL option of the

CONTROL TABLE directive.

Causes of SQL Error 8204 (Lost Open Error)

SQL error 8204 is sometimes referred to as the “lost open” error. This scenario

explains how this error can occur:

UPDATE statement with a cursor Updates the values in one or more columns in a set

of rows, one row at a time. Use when you need to

test a column value in a row before you update the

row.

DELETE statement without a cursor Deletes a single row or a set of rows from a table or

protection view. Use when you do not need to test a

column value in a row before you delete the row.

DELETE statement with a cursor Deletes a set of rows, one row at a time, from a table

or protection view. Use when you need to test a

column value in a row before you delete the row.

Note. Using a cursor can sometimes degrade a program’s performance. A cursor operation

requires the OPEN, FETCH, and CLOSE statements, which increases the number of

messages between the file system and disk process. Therefore, consider not using a cursor if

a single-row SELECT statement is sufficient.

Note. Using the CONTROL TABLE statement with the OPEN ALL option could increase the

amount of work done by an SQL statement. For efficient performance, use the OPEN ALL

option with the CONTROL TABLE statement only if all these are true:



When all open activity must occur when the program first starts (add a "dummy" call to the

cursor during initialization).



When the object containing the cursor will eventually access all partitions.



When the plan for the cursor is not a parallel plan.

Table 4-1. SQL/MP Statements for Data Retrieval and Modification (page 2 of 2)

SQL/MP Statement Description