Re: Using SQL scripts in interactive console on ACS with different systems

@Rob

Thanks, I have implemented something similar. The one system is at v5.4 and
the new system is at v7.2.

I have used a mix of SQL and CL commands and have what I need. The CL
commands on v%.4 take forever (it seems) to run, about 20 minutes.

Thank you, Darryl


On Tue, Jul 10, 2018 at 8:00 AM Rob Berendt <rob@xxxxxxxxx> wrote:

I can do this local query:
select l.system_table_schema, l.system_table_name, l.system_table_member,
l.number_rows, l.number_deleted_rows
from qsys2.syspartitionstat l
where l.system_table_schema = 'ERPLXF'
and l.system_table_name = 'IIM';

I can do this remote query:
select r.system_table_schema, r.system_table_name, r.system_table_member,
r.number_rows, r.number_deleted_rows
from gdihq.qsys2.syspartitionstat r
where r.system_table_schema = 'ERPLXF'
and r.system_table_name = 'IIM';

I can create a local table from a remote query:
create table qtemp.remote_count as(
select r.system_table_schema, r.system_table_name, r.system_table_member,
r.number_rows, r.number_deleted_rows
from gdihq.qsys2.syspartitionstat r
where r.system_table_schema = 'ERPLXF'
and r.system_table_name = 'IIM') with data;

But I am at 7.3 with recent PTF's.

I cannot join a local to a remote using either JOIN or the primitive WHERE
join.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "a4g atl" <a4ginatl2@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Date: 07/09/2018 02:16 PM
Subject: Using SQL scripts in interactive console on ACS with
different systems
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



The new system is V7.2 the old is V5.4.

I am trying to copy the systables on our old machine with the systables in
the new system to prove that all records restored.

I ran the following statements. The first completes normally whilst the
second fails for the local system.

create table dfreinkel1.systables_PRIN as (Select * from
prin.qsys2.systables ) with data;

create table dfreinkel1.systables.PRIN9 as (Select * from
D7006840.qsys2.systables) with Data ;

I have tried removing the system name D7006840 but still get this error.

SQL State: 56023
Vendor Code: -512
Message: [SQL0512] Statement references objects in multiple databases.
Cause . . . . . : The statement refers to objects that reside on
multiple
databases. The objects could be either explicitly qualified object names
or alias names that are defined to reference a different database. This
SQL
statement can only refer to a single database. A CREATE TABLE AS that
selects from a remote database cannot reference a table that has a
FIELDPROC defined. It cannot define a materialized query table. If the
remote database is not on an IBM i server, the INCLUDING clauses are not
allowed. Recovery . . . : Ensure all objects used in the statement
reside at the same database. Try the request again.

Questions:
1. Is this not possible to do?
2. What work arounds are there?

TIA

Darryl Freinkel
--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD