DB2 Express C 10.1存储过程添加Scroll声明游标时遇-104错误求助

Got it, I’ve run into this exact quirk with DB2 Express C on Windows Vista before—super frustrating when basic cursor syntax breaks just by adding SCROLL or NOSCROLL! Let’s break down why this -104 syntax error pops up and how to fix it:

1. Double-check your cursor declaration syntax #

First, make sure you’re placing SCROLL/NOSCROLL in the correct position in your cursor declaration. DB2’s syntax requires it to come right after the cursor name, not after the CURSOR keyword. A common mistake that triggers -104 looks like this:

-- ❌ Wrong: SCROLL placed after CURSOR
DECLARE my_cursor CURSOR SCROLL FOR SELECT * FROM my_table;

Swap the order to match the correct syntax:

-- ✅ Correct: SCROLL follows the cursor name
DECLARE my_cursor SCROLL CURSOR FOR SELECT * FROM my_table;

2. DB2 Express C version compatibility with Vista #

Windows Vista is an older OS, and early versions of DB2 Express C (like 9.1 or earlier) had limited support for advanced cursor features in stored procedures on Vista. Here’s what to do:

• Run db2level in your CLP to check your current DB2 version.
• If you’re on 9.1 or below, upgrade to DB2 Express C 9.7 with the latest fix pack (it’s the last version that officially supports Vista). This fixes many syntax parsing bugs related to scrollable cursors in stored procedures.

3. Rule out hidden syntax conflicts #

Sometimes adding SCROLL can expose a subtle syntax error you missed earlier. For example:

• Make sure your SELECT statement doesn’t use clauses that are incompatible with scrollable cursors (like FOR UPDATE without proper locking, though this usually throws a different error).
• Check for typos or missing semicolons in lines adjacent to the cursor declaration—DB2’s parser can be finicky about context.

4. If upgrading isn’t an option: Workaround with temp tables #

If you can’t upgrade DB2, you can simulate scrollable cursor behavior using a temporary table:

CREATE PROCEDURE simulate_scroll()
LANGUAGE SQL
BEGIN
  DECLARE v_id INT;
  DECLARE v_row_num INT;
  
  -- Insert query results into a temp table with row numbers
  CREATE GLOBAL TEMPORARY TABLE temp_results (
    row_num INT GENERATED ALWAYS AS IDENTITY,
    id INT
  ) WITH DATA ON COMMIT PRESERVE ROWS;
  
  INSERT INTO temp_results (id) SELECT id FROM my_table;
  
  -- Simulate FETCH FIRST/LAST/PREVIOUS/NEXT by querying row_num
  SELECT id INTO v_id FROM temp_results WHERE row_num = 1; -- First row
  SELECT id INTO v_id FROM temp_results WHERE row_num = (SELECT MAX(row_num) FROM temp_results); -- Last row
  
  DROP TABLE temp_results;
END@

If you’re still hitting the -104 error after trying these steps, sharing your full stored procedure code would help pinpoint the exact issue—but in most cases, fixing the syntax order or upgrading DB2 resolves it.

内容的提问来源于stack exchange，提问作者John Hennesy