SQL/MP Programming Manual for COBOL

Data Retrieval and Modification

HP NonStop SQL/MP Programming Manual for COBOL—529758-003

4-2

Opening and Closing Tables and Views

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.

SQL/MP opens a table or view when a host-language program executes 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 SQL/MP to close a table using the CLOSE TABLES

option of the FREE RESOURCES statement.

By default, SQL/MP opens partitions of base tables and indexes only as they are

needed by a program. To cause SQL/MP to open all indexes and partitions the first

time any 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:

1. A program accesses a table or view by using one or more static DML statements

(SELECT, INSERT, UPDATE, or DELETE) or a static cursor. The SQL executor

opens the table or view for the program.

2. Any locks associated with the statements in Step 1 are released (for example,

because the transaction ended). Another user then executes one of these DDL

statements or utility operations for the table or view, which causes the system to

terminate the program’s open:



ALTER TABLE with ADD COLUMN, ADD PARTITION, DROP PARTITION, or

RENAME



ALTER TABLE with AUDIT, LOCKLENGTH



ALTER INDEX with ADD PARTITION, DROP PARTITION, or RENAME

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

requires three statements (OPEN, FETCH, and CLOSE), which increase the 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.