RE: SYS21 - System 21 access from Vb -- SYSTEM21

We had some VB programs using ODBC accessing single records directly on the
AS/400 and got acceptable performance, but this was a technology
demonstration and we never put it in live production.  We used GeneXus to
generate the VB apps, which is capable of generating programs in RPG, Cobol,
VB, Visual FoxPro, C and Java.  One important thing to note is that if you
want these programs to update a table on the AS/400 you much have a unique
key on the AS/400.

For us, the issues of desktop deployment kept us from implementing VB
programs on a mass basis.  I can tell you that for our web based programs,
we didn't get very good performance when accessing single records.  We tried
using a commercial AS/400 record level access toolkit that has some controls
you can use in MS Active Server Pages and it did seem to improve performance
significantly.  I believe these are also usable in VB.  I think it is from
RJS Software and is still advertised in some trade publications.  I think
some other companies offer something similar.

However, we never ended up putting this into production either since we went
the route of using DataMirror to replicate AS/400 data to SQLServer where
our performance improved 10x - 100x over direct AS/400 access with standard
ODBC.

Dan Thomas
Director of Information Technology
MDI
4500 Progress Blvd
Louisville, KY  40218
(502) 318-1208


-----Original Message-----
From: Neil Thursby [mailto:neil.thursby@fone-logistics.co.uk]
Sent: Thursday, June 20, 2002 3:33 AM
To: jbausers-l@midrange.com
Subject: RE: SYS21 - System 21 access from Vb


Stuart / Dan,
Many thanks for the information.  What I'm trying to do is access single
AS/400 records in real-time.  Essentially a stock inquiry program
written in Vb.  ODBC won't perform in that circumstance so I'm trying to
find a technique that will.  Has anyone done this for real?

Thanks again,

Neil.

-----Original Message-----
From: Stuart Sandler [mailto:SSandler@KREBS.COM]
Sent: Wednesday 19 June 2002 22:19
To: 'jbausers-l@midrange.com'
Subject: RE: SYS21 - System 21 access from Vb


Another opinion.

We do virtually the same thing that Dan mentions below. Each morning
(around
3am local time) we copy the contents of various System 21 tables to our
SQL
server and have both Access and VB queries interrogate the data to
produce
various reports.

For example, we have complete replaced the System 21 Sales Analysis
module
with one of our own and run it across our WAN. Very fast, very thin
client
and all users are quite happy.

A comment on the speed of the Access queries. On occassion, we have had
to
do live queries into System 21 and have found the response time to be
dependent on what other jobs are running on our AS/400 at the time. A
year
or so ago, we upgraded the processor on our box and found these
interactive
queries to run faster. We are planning another upgraded in Sept and
believe
that will speed things up even more.

Using VB works similiarly to Access in that it uses the ODBC connection
to
grab the data and then you can do what you wish. If you want more
information, feel free to contact me directly. I would be happy to talk
with
you about it.

Good luck!

Stuart Sandler
Director of Information Systems
Krebs Engineers
(520) 744 - 5601
ssandler@krebs.com


-----Original Message-----
From: Dan Thomas [mailto:dthomas@mdimail.net]
Sent: Wednesday, June 19, 2002 8:42 AM
To: 'jbausers-l@midrange.com'
Subject: RE: SYS21 - System 21 access from Vb


I've successfully used MS Access to write hundreds of queries and
reports
against JBA data.  You link Access tables to data on the AS/400 using an
ODBC connection.  Data continues to reside on the AS/400, but can be
used
like local Access tables.  Access has a facility for defining
relationships
between files and storing those relationships, or you can do it
individually
for each query.  You simply drag arrows between corresponding fields to
do
the "chain".  I'm not that familiar with VB, but I would expect it uses
similar technologies.

We've found that performance varies dramatically from query to query and
probably depends upon your datasets and how the AS/400 query optimizer
is
working.   One very nice thing about Access is that a data source for a
query can be another query or a query combined with a table.  I've often
been able to dramatically improve the performance of a query by breaking
it
into multiple sub queries chained together.  You can easily have the
queries
prompt for runtime data, such as starting/ending customer#, invoice
date,
etc.  One nice thing about Access queries is they can easily be
extracted
into Excel with no programming.

You have the option in Access of writing SQL statements that run on the
AS/400 instead of letting Access run the normal way.  There are many
pundits
of this approach, but I personally have found it unnecessary.  One
performance issue to consider is the ODBC AS/400 access runs under the
interactive subsystem.  You have to be careful with this.  I expect some
expert users have figured out a solution for this.

Overall, I would say Access is a very good tool for developing queries.
Personally, I prefer it to any other tool I've used including DB2 SQL
Query
Manager, IQ Objects and Crystal Reports.  I don't particularly care for
the
Access report design tool.  In our environment, almost everything we do
is a
query that is then extracted into Excel.  If I was primarily developing
formatted reports for printing, I would probably use Crystal Reports.

In our environment, we use DataMirror to replicate our JBA AS/400 data
to MS
SQLServer to create a "datamart".  We use the datamart for our client
web
information portal since we get dramatically better performance
accessing
data from SQLServer than we do the AS/400.  Acess queries against this
datamart are also much faster than directly hitting the AS/400.

I hope that much of what I've written about Access applies to your VB
question.

Dan Thomas
Director of Information Technology
MDI
4500 Progress Blvd
Louisville, KY  40218
(502) 318-1208


-----Original Message-----
From: Kincer, Alan [mailto:akincer@kiusa.com]
Sent: Wednesday, June 19, 2002 11:08 AM
To: 'jbausers-l@midrange.com'
Subject: RE: SYS21 - System 21 access from Vb


This message is in MIME format. Since your mail reader does not
understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]

I will know in the future now to say "No vendor SOLICITATIONS" :) Of
course
replies are always appreciated.

My response would have been to do it in FoxPro. Blazingly fast access of
400
data compared to VB, especially now with version 7.

-----Original Message-----
From: Randy Rasp [mailto:rrasp@infoalt.com]
Sent: Wednesday, June 19, 2002 11:27 AM
To: jbausers-l@midrange.com
Subject: Re: SYS21 - System 21 access from Vb


[ Converted text/html to text/plain ]
Darn!  No vendor replies?  I was going to tell you how to do it.   : )

_________________________________________________
Randy Rasp, CCP
Dallas Branch Manager
Information Alternatives
PO Box 168386
Irving, TX  75016-8386
Phone:  972-753-1266
Fax:  513-936-3710
www.infoalt.com
-----jbausers-l-admin@midrange.com wrote: -----
To: <jbausers-l@midrange.com>
From: jbausers-l-admin@midrange.com
Date: 06/19/2002 09:22AM
Subject: SYS21 - System 21 access from Vb

We are looking to write an inquiry program in Vb which needs to get
somedata
from Inventory.  Basically the equivalent of doing a couple of"Chain's"
in
RPG.  Has anyone done this before, did it work, and whatmethod of
accessing
the data did you use?Thanks in advance,NeilPS  No vendor replies
please._________________________________________________________________
____
This transmission and any attachments are confidential and are intended
solely for the named addressee (s). If you are not the addressee, please
do
not read, copy, use or disclose this transmission and please notify us
immediately by telephone on+44 (0) 1670 594848 or by reply.  Please then
delete this transmission from your system.Although we have taken steps
to
ensure that this email and attachments are free from viruses, we advise
that
in keeping with good computing practice the recipient must ensure that
they
in fact are virus free.No contracts may be concluded on behalf of Fone
Logistics LTD by means of email
communications._______________________________________________This is
the
GEAC/JBA System 21 Users (JBAUSERS-L) mailing listTo post a message
email:
JBAUSERS-L@midrange.comTo subscribe, unsubscribe, or change list
options,visit:
http://lists.midrange.com/cgi-bin/listinfo/jbausers-l[1]or
email: JBAUSERS-L-request@midrange.comBefore posting, please take a
moment
to review the archivesat http://archive.midrange.com/jbausers-l[2].

===References:===
  1. http://lists.midrange.com/cgi-bin/listinfo/jbausers-l
  2. http://archive.midrange.com/jbausers-l

_______________________________________________
This is the GEAC/JBA System 21 Users (JBAUSERS-L) mailing list
To post a message email: JBAUSERS-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/jbausers-l
or email: JBAUSERS-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/jbausers-l.
_______________________________________________
This is the GEAC/JBA System 21 Users (JBAUSERS-L) mailing list
To post a message email: JBAUSERS-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/jbausers-l
or email: JBAUSERS-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/jbausers-l.
_______________________________________________
This is the GEAC/JBA System 21 Users (JBAUSERS-L) mailing list
To post a message email: JBAUSERS-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/jbausers-l
or email: JBAUSERS-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/jbausers-l.
_______________________________________________
This is the GEAC/JBA System 21 Users (JBAUSERS-L) mailing list
To post a message email: JBAUSERS-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/jbausers-l
or email: JBAUSERS-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/jbausers-l.


_____________________________________________________________________
This transmission and any attachments are confidential and are intended
solely for the named addressee (s). If you are not the addressee, please
do not read, copy, use or disclose this transmission and please notify
us immediately by telephone on
+44 (0) 1670 594848 or by reply.  Please then delete this transmission
from your system.

Although we have taken steps to ensure that this email and attachments
are free from viruses, we advise that in keeping with good computing
practice the recipient must ensure that they in fact are virus free.

No contracts may be concluded on behalf of Fone Logistics LTD by means
of email communications.

_____________________________________________________________________
This transmission and any attachments are confidential and are intended
solely for the named addressee (s). If you are not the addressee, please do
not read, copy, use or disclose this transmission and please notify us
immediately by telephone on
+44 (0) 1670 594848 or by reply.  Please then delete this transmission from
your system.

Although we have taken steps to ensure that this email and attachments are
free from viruses, we advise that in keeping with good computing practice
the recipient must ensure that they in fact are virus free.

No contracts may be concluded on behalf of Fone Logistics LTD by means of
email communications.
_______________________________________________
This is the GEAC/JBA System 21 Users (JBAUSERS-L) mailing list
To post a message email: JBAUSERS-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/jbausers-l
or email: JBAUSERS-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/jbausers-l.