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
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.