When you use a cursor in an embedded SQL application program, at which point is the associated SELECT statement actually executed by the database?

Introduction / Context:
Cursors are used in embedded SQL programming to process query results one row at a time. A frequent interview question asks when the SQL SELECT statement associated with a cursor is actually executed. Understanding the timing is important for performance, error handling, and transaction control in environments such as COBOL-DB2 or C-DB2 programs.

Given Data / Assumptions:

• An application program declares a cursor for a SELECT statement with EXEC SQL DECLARE cursor-name CURSOR FOR SELECT ... END-EXEC.
• The program uses EXEC SQL OPEN cursor-name, followed by FETCH operations and finally CLOSE.
• We are interested in the moment when the database engine runs the SELECT query.

Concept / Approach:
Declaring a cursor in embedded SQL defines its attributes but does not execute the SELECT. The actual execution of the SELECT statement happens when the cursor is opened with the OPEN statement. At that time, the database parses the SQL (if not already prepared), checks authorisations, and establishes the result set. FETCH operations then retrieve rows from this active result set, and CLOSE releases it. This sequence ensures that the program can process results row by row while the database manages the underlying query execution and positioning.

Step-by-Step Solution:
Step 1: The program issues a DECLARE CURSOR statement, which associates a cursor name with a particular SELECT statement; this is a compile time or precompile time definition, not runtime execution. Step 2: When the program reaches the OPEN cursor-name statement at runtime, DB2 or the database executes the associated SELECT. Step 3: The database builds a result set, which may be materialised or accessed through an access path as rows are fetched, depending on isolation level and cursor type. Step 4: The program repeatedly issues FETCH statements, each of which moves to the next row in the result set and returns column values into host variables. Step 5: Finally, the program issues CLOSE cursor-name, which ends the cursor and frees associated resources on the database server.

Verification / Alternative check:
You can verify this by adding tracing or logging around OPEN and FETCH calls. SQL error codes related to the SELECT, such as syntax or authorisation errors, will usually appear at OPEN time, not at DECLARE or CLOSE time. Performance monitors and explain tools often attribute query execution to the moment of OPEN, confirming that OPEN is the trigger for the SELECT to run. If you declare a cursor and never open it, no query is executed, which further demonstrates that DECLARE alone does not run the SQL.

Why Other Options Are Wrong:
Option B is incorrect because the cursor declaration happens during precompile or compile; the database does not execute the SELECT simply because the source contains a DECLARE statement. Option C is wrong because closing a cursor terminates access to the result set; it does not initiate query execution. Option D is incorrect because cursors are directly tied to actual SQL statements and result sets; they are not purely logical constructs without real execution.

Common Pitfalls:
A common pitfall is misunderstanding when locks are acquired and held. Depending on isolation level, locks may be obtained at OPEN time and held across FETCH operations, affecting concurrency. Another issue is forgetting to close cursors, which can lead to resource leaks and performance problems. Developers may also mistakenly assume that changing host variable values after DECLARE will affect the SELECT, when in fact the values used are generally those present at OPEN time. Understanding the OPEN-FETCH-CLOSE sequence and when execution actually occurs is critical for building correct and efficient cursor based programs.

Final Answer:
When using a cursor, the associated SELECT statement is executed at the moment you OPEN the cursor, and subsequent FETCH statements simply retrieve rows from the resulting cursor result set.