× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. March 2011

Re: Censoring SQL access -- ideas?

Me neither...learned something new today...but I'd bet Tommy's correct
about the performance concerns...

Multi-row fetch (and insert) would help, but I'd bet you'd have to
fetch quite a few rows to overcome the overhead of reconnecting.

I seem to recall that the single connection requirement applies to the
activation group, not the job. If so you could have the fetch
procedure in one activation group and the insert in another.

Still lot's of hoops to jump through that you don't have to worry
about on other DB platform...including DB2 for LUW.

Charles



On Thu, Mar 17, 2011 at 10:24 AM, <rob@xxxxxxxxx> wrote:
Maybe, but I didn't even think about connecting/reconnecting not breaking
the cursor.  Interesting.


Rob Berendt
--
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:   Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx
To:     Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:   03/17/2011 09:54 AM
Subject:        RE: Censoring SQL access -- ideas?
Sent by:        midrange-l-bounces@xxxxxxxxxxxx



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
--
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:   Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
To:     Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:   03/17/2011 08:42 AM
Subject:        Re: Censoring SQL access -- ideas?
Sent by:        midrange-l-bounces@xxxxxxxxxxxx



Somebody else mentioned this to me, Tommy.  I should know more about
this.  I'm going to do some research.  Can you JOIN files in two
different databases?

Joe

Sure it can be done with SQL.  You set up an RDB entry pointing to the
other system and use CONNECT to access the data on the remote system.

Thanks,
Tommy Holden



From:   Joe Pluta<joepluta@xxxxxxxxxxxxxxxxx>
To:     Midrange Systems Technical Discussion<midrange-l@xxxxxxxxxxxx>
Date:   03/16/2011 10:24 PM
Subject:        Re: Censoring SQL access -- ideas?
Sent by:        midrange-l-bounces@xxxxxxxxxxxx



On 3/16/2011 1:27 PM, rob@xxxxxxxxx wrote:
Joe,

True on the ORDER BY limit in a view, but you might want to take a
look
at
the enhancements done to CREATE INDEX.
CREATE INDEX, with the RCDFMT clause and the WHERE clause is a
beautiful
thing.  I believe that the index fields and the fields added in the
RCDFMT
clause can be UDF's.


http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzx
cindx.htm



Rob Berendt
Yeah, I've been reading about that.  It has its limitations as well;
as
far as I can tell you're stuck with the layout of the underlying
table.
One of the primary benefits of the view is to select and omit columns,
and the INDEX ... WHERE doesn't give you that.  Logical files still do
the combined sort, select and reformat better than any one tool in the
SQL toolkit.

One other thing - you can't do SQL on a DDM file.  There are
circumstances where getting at a table on another i is required.  With
RLA, it's CRTDDMF and I'm done.  With SQL, it's impossible.  (Yes, it
can be done with ODBC/JDBC, but not with standard SQL.)

Joe

--
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.

--
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.


--
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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.