Load from cursor on nickname SQLCODE: -104, SQLSTATE: 42601

[truetomm...@gmail.com]

I have 2 DBs

-DB2SOURCE
-DB2TARGET

In DB2TARGET i have a nickname on a DB2SOURCE view, i need to load
data from that view in a DB2TARGET table using a stored procedure.

I found these sample commands:

DECLARE C1 CURSOR FOR SELECT * FROM schemaname.viewname
CALL SYSPROC.ADMIN_CMD('LOAD FROM C1 OF CURSOR MESSAGES ON SERVER
INSERT INTO schemaname.tablename NONRECOVERABLE');

It works perfectly on CLP and Command Editor but when it runs in a
stored procedure i got this error message:

Exception occurred while running:
A database manager error occurred.SQLCODE: -104, SQLSTATE: 42601 - DB2
SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=CURSOR;OF;ASC,
DRIVER=3.53.95

Help me plz!
Ivan

[Dave Hughes]

I'm not sure why it doesn't work from a stored proc, but have you
considered using the following syntax?

CALL SYSPROC.ADMIN_CMD('LOAD FROM (SELECT * FROM T1) OF CURSOR INSERT
INTO T2')

According to the ADMIN_CMD LOAD reference [1] that should permit loading
from T1 into T2 without a separate DECLARE CURSOR step (it's ADMIN_CMD
specific; doesn't work with the "normal" LOAD).

There's another variant which supports loading from a remote database,
provided both are on the same server and accept the same authentication:

CALL SYSPROC.ADMIN_CMD('LOAD FROM (DATABASE source SELECT * FROM T1) OF
CURSOR INSERT INTO T2')

[1]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0023577.html

Cheers,

Dave.

[Serge Rielau]

Note that on newer versions of DB2 (9.1 I think(?)) you can also do a
load from a remote cursor without going through the nickname.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab