Using the CLOSQLCSR Parameter with CRTSQLxxx

The options available for the CLOSQLCSR parameter are *ENDPGM, ENDJOB, and *ENDSQL. The default for creation of High-Level language (HLL) SQL embedded programs is *ENDPGM. CLOSQLCSR defines how the programs handles cursors within the embedded SQL programs. 

The following is the documentation for each of the CLOSQLCSR parameters:

*ENDPGM

The SQL cursors are closed, SQL prepared statements are discarded when the program ends, and LOCK TABLE locks are released.

*ENDJOB

SQL cursors remain open between calls and can be fetched without running another SQL OPEN statement. None of the programs higher on the call stack need to have run SQL statements. SQL cursors are left open, SQL prepared statements are preserved, and LOCK TABLE locks are held when the first SQL program on the call stack ends. SQL cursors are closed, SQL prepared statements are discarded, and LOCK TABLE locks are released when the job ends.

*ENDSQL

The SQL cursors remain open between calls and rows can be fetched without running another SQL OPEN statement. One of the programs higher on the call stack must have run at least one SQL statement. The SQL cursors are closed, SQL prepared statements are discarded, and LOCK TABLE locks are released when the first SQL program on the call stack ends. If you specify *ENDSQL for a program that is the first SQL program called (the first SQL program on the call stack), the program is treated as if *ENDPGM was specified.

The differences between each of these options has to do with how the cursors are maintained:

1	Use of *ENDPGM discards SQL cursors, even if the cursor was not closed in the program. Subsequent call to the same program allows open of the same cursor without issuing an error message.
2	Use of *ENDJOB or *ENDSQL leaves the cursors in an active status unless specifically closed. Programs that exit and reenter access the previously opened cursor. This allows for a program to open a cursor, fetch a record, return to a calling program, be called again, and fetch the subsequent record. Use of *ENDJOB or *ENDSQL is beneficial from a performance standpoint in reducing cursor overhead and subsequent positioning required to locate the next record to process.
3	The differences between *ENDJOB and *ENDSQL are limited to when the SQL cursors are explicitly closed. *ENDJOB requires the job to end in order to close the cursors. *ENDSQL requires all SQL programs in the job stack to have ended to close the cursors.

The only impact the CLOSQLCSR parameter has on ODPs, (Open Data Paths) is when the ODPs are deleted. Use of *ENDPGM allows ODPs to be deleted via the RCLRSC command. Use of *ENDSQL will delete ODPs when all SQL programs in the job stack have ended or when RCLRSC is issued. Use of *ENDJOB will not delete ODPs until the job ends; RCLRSC has no effect on ODPs created with programs defined with *ENDJOB.

The use of LR (Last Record) within a RPG program has no effect on ODPs, regardless of the specification of the CLOSQLCSR parameter. The decision on whether to set on LR or RETRN  (return), should be made based on the benefits or drawbacks in relation to the RPG code, not based on the ODPs.

Cursor/ODP Considerations

1	HLL programs that do not explicitly close cursors within the program manage the cursors and ODPs based on the CLOSQLCSR option. Note the following circumstances: A RPG program is created with *ENDPGM. It issues a declare, open, and fetch of a cursor, then the program ends. Because the program ends, the cursor and the ODP are discarded. A RPG program is created with *ENDJOB or *ENDSQL. It issues a declare, open, and fetch of a cursor, then the program ends. In this case, the cursor is left active and the ODP is in an in use status. Subsequent call of the program again allows access to the same cursor. If an attempt is made to open that cursor again, it fails with msgSQL0502, cursor already open. Once the program closes the cursors, the ODP is available for reuse and subsequent open of the cursor again reuses the ODP.
2	Non-cursor SQL statements such as SELECT INTO, INSERT, UPDATE, or DELETE may open a reusable ODP. This ODP is not deleted when the program ends. It will be deleted only when a RCLRSC command is issued if it was created with the CLOSQLCSR option of *ENDPGM or *ENDSQL or when the job ends if it was created with CLOSQLCSR as *ENDJOB. However, since the ODP is reusable, calls to the same program executing the same statement reuse the ODP that was previously created. This reuse occurs regardless of the CLOSQLCSR option specified.
3	The number of active ODPs on a job has very little effect on the performance of the job. ODPs are only brought into main storage when they are addressed. The creation and deletion of ODPs has a significant performance impact due to the amount of time required to open and close a data path. However, once this ODP has been opened, it has little impact on the job's performance if it remains as an open data file in the job.

Recommendations

1	In general, use the CLOSQLCSR default of *ENDPGM for SQL embedded programs when little or no ODP analysis has been performed.
2	Explicitly close cursors in HLL programs when data retrieval operations have been completed for the cursor. This deletes non-reusable ODPs from the job. For programs compiled with *ENDJOB or *ENDSQL, it allows the cursors to be reopened without issuing message SQL0502.
3	Target the use of *ENDJOB or *ENDSQL toward programs with cursors that can be left in an open status. A typical program may be a transaction inquiry program where the cursor is opened at the beginning of a transaction list for a customer. The first transaction is fetched with the data being returned back to another program. When the user requests the next transaction, the *ENDJOB or *ENDSQL program is called again and performs another fetch to retrieve the next transaction. A good programming practice would be to close the cursor once no additional transactions are fetched.