Insert from a remote alias



IBM's flagship relational database management system

Insert from a remote alias

Postby p19689 » Thu Jul 28, 2011 12:43 pm

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).
p19689
 
Posts: 65
Joined: Fri Oct 01, 2010 1:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Insert from a remote alias

 

Re: Insert from a remote alias

Postby GuyC » Thu Jul 28, 2011 5:00 pm

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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Insert from a remote alias

Postby p19689 » Wed Aug 03, 2011 3:04 pm

Thanks for th advice .
p19689
 
Posts: 65
Joined: Fri Oct 01, 2010 1:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Insert from a remote alias

Postby p19689 » Thu Aug 04, 2011 7:56 pm

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 )
p19689
 
Posts: 65
Joined: Fri Oct 01, 2010 1:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Insert from a remote alias

Postby dick scherrer » Fri Aug 05, 2011 4:27 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Insert from a remote alias

Postby p19689 » Tue Aug 09, 2011 7:50 pm

Thanks Dick,
What you are saying is the above syntax is not valid at all even for a single record insert ?
p19689
 
Posts: 65
Joined: Fri Oct 01, 2010 1:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Insert from a remote alias

Postby dick scherrer » Tue Aug 09, 2011 11:00 pm

Hello,

Yes, the syntax is incorrect. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Insert from a remote alias

Postby p19689 » Wed Aug 10, 2011 3:40 pm

Thanks Dick, What's the best material to learn more about this ?
p19689
 
Posts: 65
Joined: Fri Oct 01, 2010 1:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Insert from a remote alias

Postby dick scherrer » Wed Aug 10, 2011 4:05 pm

Hello,

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

These are available for download free from IBM.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post