how to create static cursor in db2?

Question

I am facing a problem while creating a static cursor in DB2. This is the statement i used in my SP.

DECLARE CURNAME SENSITIVE STATIC SCROLL CURSOR FOR 
        SELECT COL1, COL2 
        FROM SCH.TABLENA 
        ORDER BY COL1;

on compilation it says : DB2 Database Error: ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "SENSITIVE" was found following "". Expected tokens may include: "FOR". LINE NUMBER=20. SQLSTATE=42601

The version of Db2 i am using is 9.5.

Please let me know how to create a Static cursor.

Answer 1

I am assuming you are declaring the cursor in an SP. Here's the syntax for the same:

>>-DECLARE--cursor-name--CURSOR--+-----------+------------------>
                                 '-WITH HOLD-'

>--+----------------------------+--FOR--+-select-statement-+---><
   |              .-TO CALLER-. |       '-statement-name---'
   '-WITH RETURN--+-----------+-'
                  '-TO CLIENT-'

Why have you specified SENSITIVE STATIC SCROLL? Is there a specific requirement?

You can specify SENSITIVE STATIC SCROLL only when you are declaring a cursor within a CLI application, in the case of which, the declaration precedes with the EXEC SQL stmt:

EXEC SQL DECLARE C2 SENSITIVE STATIC SCROLL CURSOR FOR
  SELECT DEPTNO, DEPTNAME, MGRNO
  FROM DSN8910.DEPT
  ORDER BY DEPTNO
END-EXEC