RE: Censoring SQL access -- ideas? -- MIDRANGE-L

True, you can also fetch more than 1 row at a time though. Just another
option.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx
Sent: 17 March 2011 13:50
To: Midrange Systems Technical Discussion
Subject: RE: Censoring SQL access -- ideas?

No offense but using SQL cursor & RLA will "smoke" this solution since
you're disconnecting/reconnecting on each row. That's a ton of extra
overhead incurred on every row vs fetch from remote, write to local.

Thanks,
Tommy Holden

From: "McGovern, Sean" <Sean.McGovern@xxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxx>
Date: 03/17/2011 08:43 AM
Subject: RE: Censoring SQL access -- ideas?
Sent by: midrange-l-bounces@xxxxxxxxxxxx

Why use RLA on the local system ? Following is an example of copying a
remote file to a local file...

H Option(*NoDebugIO:*SrcStmt)
H datfmt(*iso)
H dftactgrp(*No) actgrp('SQLTEST')

****************************************************************
* P R O G R A M P R O T O T Y P E (*ENTRY PARAMETERS)
****************************************************************
D MAIN PR EXTPGM('SQLTEST')
*
D MAIN PI

*****************************************************************
* V A R I A B L E S / C O N S T A N T S
*****************************************************************
* Variable used to PREPARE SQL statements, and access remote
* systems.
D SQLSTM S 4096A VARYING

D HOME_SERVER S 18A VARYING
D INZ('HOMEXXX')
D REMOTE_SERVER S 18A VARYING
D INZ('REMOTEXXX')
*****************************************************************
* I N P U T S P E C I F I C A T I O N S
*****************************************************************
* The following data structures are used as "targets" for the
* SQL processing used in this program.
D file E DS EXTNAME(file)
D INZ
/free

exec sql
SET OPTION
ALWBLK = *ALLREAD,
CLOSQLCSR = *ENDMOD,
COMMIT = *NONE,
DATFMT = *ISO,
RDBCNNMTH = *DUW,
DLYPRP = *YES;

exec sql
CONNECT TO :HOME_SERVER;
exec sql
SET CONNECTION :HOME_SERVER;

SQLSTM = 'SELECT * FROM rmtlib/rmtfile';
exec sql
CONNECT TO :REMOTE_SERVER;
exec sql
PREPARE C_S FROM :SQLSTM;
exec sql
DECLARE C CURSOR FOR C_S;
exec sql
OPEN C;
exec sql
FETCH FROM C INTO :file;

dow SQLCODE = *ZEROS;
exec sql
SET CONNECTION :HOME_SERVER;
exec sql
INSERT INTO homelib/homefile VALUES(:file);
exec sql
SET CONNECTION :REMOTE_SERVER;
exec sql
FETCH FROM C INTO :file;
enddo;

exec sql
CLOSE C;
exec sql
DISCONNECT :REMOTE_SERVER;

*inlr = *on;
return;

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: 17 March 2011 13:07
To: Midrange Systems Technical Discussion
Subject: Re: Censoring SQL access -- ideas?

Depends on your meaning of database. If you're talking relational data
lingo the answer is no. Each database is a separate entry in
WRKRDBDIRE.
If you're talking about libraries on the same system the answer is sure.

So, you cannot do a join with a file on your system with a file on
another
system. You cannot even do a
INSERT INTO LocalFile (
select * from RemoveFile)
or some such thing.

The work around is
CONNECT to remotesys
declare and open a cursor on the remote system
for every row retrieved from the remote system use RLA on the local
system
to post it locally.

Not sure if the optional part of the OS "DB2 Multisystem" (or some such
thing) will allow you do to that. It does give you partitioned tables.
Which, you can think of as multimember files, with benefits. RTM as an
alternative to the manual for that, I'm not.

Rob Berendt