SQLSTATE=42829 while compiling a pro*c code with DB2 database

Question

I am trying to compile a .sqc file on AIX using DB2 getting sql error SQL0511N "The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified.SQLSTATE=42829" while declaring a Cursor with host_variable_int on "FETCH FIRST :host_variable_int ROWS ONLY" but same statement is getting compiled when pass number instead of host_variable like "FETCH FIRST 40 ROWS ONLY".

ERROR SQL Statement:

EXEC SQL DECLARE cursor_name CURSOR WITH HOLD FOR SELECT a.coulmn_names FROM TABLE_NAME a WHERE a.TABLE_COLUMN_1 = :host_variable AND a.TABLE_COLUMN_2 IN ( SELECT b.coulmn_names FROM TABLE_NAME b WHERE b.TABLE_COLUMN_1 = :host_variable AND a.TABLE_COLUMN_3 = b.TABLE_COLUMN_3) FETCH FIRST **:host_variable_int** ROWS ONLY FOR UPDATE OF coulmn_name;

NON ERROR SQL STATEMENT:

EXEC SQL DECLARE cursor_name CURSOR WITH HOLD FOR SELECT a.coulmn_names FROM TABLE_NAME a WHERE a.TABLE_COLUMN_1 = :host_variable AND a.TABLE_COLUMN_2 IN ( SELECT b.coulmn_names FROM TABLE_NAME b WHERE b.TABLE_COLUMN_1 = :host_variable AND a.TABLE_COLUMN_3 = b.TABLE_COLUMN_3) FETCH FIRST **40** ROWS ONLY FOR UPDATE OF coulmn_name;

I am not sure what difference in the two cursor declaration statement Does someone have any idea about this? Thanks in advance

Answer 1

For current versions of Db2, this is the expected result. You cannot use a host-variable for the fetch first row count.

In Db2, the fetch-clause has this syntax for the fetch-row-count, according to the documentation.

fetch-row-count An expression that specifies the maximum number of rows to retrieve. The expression must not contain a column reference, a scalar-fullselect, a function that is not deterministic, a function that has an external action, or a sequence reference (SQLSTATE 428H7). The numeric value must be a positive number or zero (SQLSTATE 2201W). If the data type of the expression is not BIGINT, the result of the expression is cast to a BIGINT value. When fetch-row-count is omitted, it is equivalent to 1.

If you want to vary the fetch first value , you have to use dynamic-SQL and you cannot use static-sql.