Load from a remote table using cursors with the WITH UR clause fails with SQL0104n error.
$ db2 "declare myCursor cursor database <REMOTE_DB> user db2inst1 for SELECT * FROM <schema_name.table_name> WITH UR"
Enter current password for <user_id> : DB20000I The SQL command completed successfully.
$ db2 "load from myCursor of cursor messages attr.log replace into <schema_name>.<table_name>
SQL0104N An unexpected token "UR" was found following "<TABLE_NAME>" with".
Expected tokens may include: "<space>". SQLSTATE=42601
When doing remote fetch LOAD (LOAD from CURSOR with DATABASE), LOAD sometimes need to rewrite the input SQL (from the DECLARE CURSOR) when fetching the source data, depending on the complexity of the situation. LOAD code does not have a full SQL parser that can fully understand the input SQL.
breaking down the read-only / update / optimize-for / isolation clauses when doing the rewrite of the SQL statement.
In the above example, LOAD detects that the input SQL is not a plain "select * from tablename", and the input/output columns are not direct 1-to-1 mapping, and decides it need to construct the complex version of statement:
WITH db2temporarytableforinternalexportandimportandloadprocessingdonotusethisname (C0, C1, C2, C3, C4) AS ( select * from s5 with ur) SELECT C0, C1, C2, C3 FROM db2temporarytableforinternalexportandimportandloadprocessingdonotusethisname
Unfortunately the above SQL is invalid according to SQL grammar.
The isolation clause with UR is not allowed at that location of the SQL statement.
Thus the SQL0104 error message is returned.
Resolving the problem
You may use one of these two solutions:
- Do not use the WITH UR clause in the declaration of the cursor.
- Make sure the number of columns are the same on source and target tables.