DB2 Cursor Statement Validator

Problem Context

You are building a lightweight SQL linter for DB2 embedded SQL programs. Your task is to parse and validate cursor lifecycle statements. In DB2 embedded SQL, a cursor must follow a strict lifecycle:

1. DECLARE — Define the cursor with a SELECT query
2. OPEN — Open the cursor to execute the query
3. FETCH — Retrieve rows one at a time into host variables
4. CLOSE — Release the cursor resources

A valid cursor program must follow this exact order, and each statement must reference the same cursor name.

Task

Given a list of DB2 embedded SQL statements (as strings), determine if the cursor lifecycle is valid. Return "VALID" if the statements follow the correct order and reference the same cursor name, otherwise return "INVALID" along with the reason.

Input/Output Format

Input: A list of SQL statement strings (each trimmed, uppercase). The statements may include non-cursor SQL lines which should be ignored.

Output: A string — either "VALID" or "INVALID: <reason>" where reason is one of:

• "MISSING DECLARE"
• "MISSING OPEN"
• "MISSING FETCH"
• "MISSING CLOSE"
• "WRONG ORDER"
• "CURSOR NAME MISMATCH"

Rules

• DECLARE must come before OPEN, OPEN before FETCH, FETCH before CLOSE.
• All four statements must be present.
• The cursor name used in DECLARE, OPEN, FETCH, and CLOSE must be identical.
• If multiple errors exist, report the first one encountered (in the order: MISSING DECLARE → MISSING OPEN → MISSING FETCH → MISSING CLOSE → WRONG ORDER → CURSOR NAME MISMATCH).
• Non-cursor statements (e.g., INSERT, UPDATE, CONNECT) are ignored.

Approach Hints

• Parse each statement to extract the keyword (DECLARE CURSOR, OPEN, FETCH, CLOSE) and the cursor name.
• Track which lifecycle steps have been seen and in what order.
• Use a state machine approach: valid transitions are DECLARE → OPEN → FETCH → CLOSE.

Examples

Example 1 — Valid lifecycle:

DECLARE C1 CURSOR FOR SELECT * FROM EMPLOYEES
OPEN C1
FETCH C1 INTO :HV1, :HV2
CLOSE C1

Output: "VALID"

Example 2 — Missing OPEN:

DECLARE C1 CURSOR FOR SELECT * FROM EMPLOYEES
FETCH C1 INTO :HV1
CLOSE C1

Output: "INVALID: MISSING OPEN"

Example 3 — Cursor name mismatch:

DECLARE C1 CURSOR FOR SELECT * FROM EMPLOYEES
OPEN C1
FETCH C2 INTO :HV1
CLOSE C1

Output: "INVALID: CURSOR NAME MISMATCH"