Page 1 of 1

Insert from a remote alias

PostPosted: Thu Jul 28, 2011 12:43 pm
by p19689
HI, I need to code the following SQL in a COBOL program. But the insert from subselect from a remote alias is not valid in DB2 V9. Can someone advise possible options to over come the issue please . I thought to write to a temporary dataset first and then inserting from that. Is this the way forward or is there a better option ?
EXEC SQL INSERT
INTO creator1.table_A
( SELECT COLUMN_1
FROM creator_remote_alias.TABLE_B
WHERE COLUMN_2 = : ws-var1 )

creator_remote_alias.TABLE_B is a alias pointing to another DB2 subsystem(remote alias).

Re: Insert from a remote alias

PostPosted: Thu Jul 28, 2011 5:00 pm
by GuyC
in your cobol program :

open a cursor on the remote table
fetch
loop
   insert into local table
    fetch
close cursor

ideally use "multi-row" fetch and insert

Re: Insert from a remote alias

PostPosted: Wed Aug 03, 2011 3:04 pm
by p19689
Thanks for th advice .

Re: Insert from a remote alias

PostPosted: Thu Aug 04, 2011 7:56 pm
by p19689
Hi GuyC,

The initial SQL(below) fail due to multiple record insert or even for a single record insert will it fail ? I 'm trying to understand weather db2 allows this SQL insert for single row or not ?

INTO creator1.table_A
( SELECT COLUMN_1
FROM creator_remote_alias.TABLE_B
WHERE COLUMN_2 = : ws-var1 )

Re: Insert from a remote alias

PostPosted: Fri Aug 05, 2011 4:27 am
by dick scherrer
Hello,

Please post the link where you found this documented in a DB2 manual. . .

You MUST use valid syntax provided by the product - not make some up to fit your need.

Re: Insert from a remote alias

PostPosted: Tue Aug 09, 2011 7:50 pm
by p19689
Thanks Dick,
What you are saying is the above syntax is not valid at all even for a single record insert ?

Re: Insert from a remote alias

PostPosted: Tue Aug 09, 2011 11:00 pm
by dick scherrer
Hello,

Yes, the syntax is incorrect. . .

Re: Insert from a remote alias

PostPosted: Wed Aug 10, 2011 3:40 pm
by p19689
Thanks Dick, What's the best material to learn more about this ?

Re: Insert from a remote alias

PostPosted: Wed Aug 10, 2011 4:05 pm
by dick scherrer
Hello,

Probably the SQL Reference and the SQL Programmng Guide for your release of db2.

These are available for download free from IBM.