RE: Censoring SQL access -- ideas?

[Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx]

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