Re: Accessing multiple databases on different systems (IBM i 7.1 TR7)

Some further info.

Here's what I currently get:
select * from Localhost.jnzprddta.f0911
except
select * from Remotehost.jnzprddta.f0911


Statement references objects in multiple databases.
And as the F0911 is a pretty large table I don't want to copy it over into
a working temp table.

On 12 January 2015 at 16:51, PaultinNZ <paultormey@xxxxxxxxx> wrote:

Thanks Bryan, I had seen that article but if you look at the following
article:

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/INSERT%20with%20remote%20SUBSELECT
IBM says:
"*The INSERT SQL statement is enhanced to allow the sub-select to
reference a single remote database which is different than the current
server connection. Other SQL statements remain restricted to a single
database reference per statement."

So it looks as if this is a SQL limitation and IBM would need to modify
the select statement to lift the "single database reference per statement"
rule.


On 12 January 2015 at 16:29, Bdietz400 <bdietz400@xxxxxxxxx> wrote:

I think you may want to look at the 3 part naming.

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/RDB%20alias%20support%20for%203-part%20SQL%20statements

Looks like db2 group ptf #26
--
Bryan Dietz

On Jan 11, 2015, at 6:25 PM, PaultinNZ <paultormey@xxxxxxxxx> wrote:

I have a table that is replicated from SysA to SysB.

The table on SysB is not in sync with the origin table on SysA - there

are

records missing on SysB.

The normal procedure for getting these back in sync would be to take a
backup from SysA, move that to SysB and restore the table. Tape would be
the transfer medium. Once the transfer is done and the table restore
mirroring from SysA to SysB would be restarted.

Or use our Mirror software and force a refresh which can take some time.

I wanted to look at using a SQL insert using remote DB directory

entries.

However, it seems that even with the updates till TR7 I can only open 1
database simultaneously.

Anyone know of a way to open the "TableCommon" in both Database1 and
Database2 simultaneously and preferrably from a session on the i.

I'm trying to do a select with except to get back rows that do not

exist in

SysB.

Look forward to any suggestions.

Paul
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
Paul Tormey
3/11 Bramley Drive
Farm Cove
Auckland
2012
New Zealand
Home: 09 576 3415 or 0064 9 576 3415
Mobile: 021 810615 or 0064 21810615