RE: Link AS400 database wirh Access Database

If all you wanted was a PC based query tool, you could use MS Query instead
of MS Access.  At least at this point you could use MS Query to test your
ODBC connection and data retrieval.  Of course you have the issue of them
seeing all the tables in the library on the iSeries, not just the subset,
but MS Query is more of a GUI tool that a MS user might be able to use
quickly.

Although I use QUERY/400 for most everything that is iSeries based, I
occasionally will use MS Query just to check my ODBC connectivity and
quickly view some data in a table.

Just a thought, although with 10's of millions of records, it could be
REALLY slow.

Pete Helgren
Value Added Software, Inc
801.581.1154 x202

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Chaudhary, Sachin (GE
Equipment Services,Consultant)
Sent: Friday, January 21, 2005 5:30 PM
To: Midrange Systems Technical Discussion
Subject: RE: Link AS400 database wirh Access Database
Importance: High

Thanks Vern,
I am sorry its not the programmers but an audit team which wants to do
annual audit for Tax and all. Since they are not comfortable with AS400 (and
we don't expect them either), I was thinking to FTP AS400 Files to MS Access
so they can run the query. And then we came to this file (11 GB worth data ,
some 85,877,453 rcds). For audit there is no fixed report format they want,
as the requirement keeps changing depending on which fields/data they want
to focus on, I can't keep writing WRKQRY etc. So I thought lets give them
the files they need and they can do whatever they want.

Then came the question of how to get most current data, I thought instead of
FTPing the files, lets just link the AS400 table  to MS Access, so they
query in real-time data.
This is the whole story.

But since only 5 files were involved I thought lets go ahead with MS Access
option even if it uses more CPU (thatz where I wanted this EBDIC to ASCII
conversion done on MS Access rather on AS400!), But then even after changing
the default library in ODBC, it still gives me error that file object not
found for a particular production library! I asked this question to Bruce
Barret, still waiting for his reply!

If nothing goes fine, I guess I have a backup plan...we use Datamirror tool
to mirror AS400 data to Oracle and then to Tearadata (where we have
Datawarehouse built up). I can have the BO reports etc run for them. I don't
want to go for this as my Datawarehouse team is already overloaded neck deep
with work.

Thanks again;... Bruce if you have any ideas how to resolve error, I want to
give it a shot.

Everybody have a nice weekend!
Rgds,
Sachin


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Friday, January 21, 2005 12:09 PM
To: Midrange Systems Technical Discussion
Subject: RE: Link AS400 database wirh Access Database


Hi - I forgot the original original message. What are you really trying to
accomplish? There are ways to limit the impact of running SQL statements. We
can come up with ideas if you give us what is your desired result.

Maybe  ;-)

Vern
-------------- Original message -------------- 

> Yes you are right. There is already set of logical files and the 
> processing/reports etc go perfectly fine in production env. I just 
> wanted to do this for the new programmers who keep analysing data by 
> opening files and running SQLs time and again. We do not have a 
> separate test box:)
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Gary Monnier
> Sent: Friday, January 21, 2005 10:50 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Link AS400 database wirh Access Database
> 
> 
> 
> There are many ways to solve your dilemma. One is, unless you really 
> have your heart set on doing this through Access, is to use a logical 
> file keyed in the sequence you want your query in and run your query 
> over the logical view. Using this logical view will reduce the amount 
> of time and CPU your query uses. And yes, it will take some CPU to 
> build the logical view, but you can schedule it to be 
> created/installed during slow hours.
> 
> If your file is really enormous you may be better off writing a 
> program to spin through it, filter out records you don't want to 
> report on, write those that you do want to report on to a separate 
> physical file and run your query over your new file.
> 
> 
> 
> 
> --
> 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.