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

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.