AS400 to Access

[Brad Allison]

I posted this to the adonet newsgroup and was recommended to post here.
Thank you for any help:

Okay, I need a bit of advice. I have an ODBC data adapter being filled by
AS400 which is very slow. If I were they user I would think that the
computer locked and try to stop the program, reboot, whatever.

So because management is still "investingating" ways of getting a faster
connection from our primary AS400 (which will never go away) to the internet
and internal employees I think some of the data will be able to be handled
with Access. I know, not the best way but something for now. I am not
familiar with DTS but what my thoughts are is to run a procedure once a week
during off hours that will fill the dataset (ds1) from the AS400's ODBC data
adapter then update the Access OLEDB data adapter using the same dataset.
Does anyone have any thoughts on this process?

I am trying my best to get the approval to the purchase of SQL, but nothing
yet and I have some projects lining up that would greatly benefit if
something like this is possible.

Thanks for any insight, comments, thoughts, ideas.

Brad

PS Besides SQL, the other ways being considered is: ASNA and another data
connection (can't remember it right now).

 

[Lucas Tam]

I am not familiar with DTS but what my thoughts
are is to run a procedure once a week during off hours that will fill
the dataset (ds1) from the AS400's ODBC data adapter then update the
Access OLEDB data adapter using the same dataset. Does anyone have any
thoughts on this process?

Datasets are no good for large sets of data - it replicates all the rows
in memory so your client machine may run out of memory. I would avoid
datasets if you are returning >20,000 - 30,000 rows of data.

A better alternative would be to bulk load the data - use a datareader
and write the data out as a text file. Bulk load the data into Access,
MS SQL or MySQL. Bulk loading is quite fast and not very resource
intensive on the client side.

Alternatively, DTS works very well - DTS is extremely fast and easy to
use. It would be worth your effort to take a look at DTS.

I am trying my best to get the approval to the purchase of SQL, but
nothing yet and I have some projects lining up that would greatly
benefit if something like this is possible.

Take a look at MySQL - if you're only using SQL Server as a datastore,
mySQL is a more than adequate replacement... and it's mostly free : )

http://www.ems-hitech.com has several great mySQL Utilities. There are
also several ways of accessing mySQL in .NET (ODBC or .NET drivers):

http://www.bytefx.com/dotdata.aspx

 

[Rob T]

We copy info off of our 400 all the time. I'm not sure if my way is the
best way to do it, but it works. ;-)

I would probably avoid copying the info into Access. Especially if you're
going to keep deleting records and adding new ones. The database likes to
keep growing, even though you deleted the older records. You'll probably
have to keep compacting the database on a regular basis.......

I have a vb program that opens an painfully slow ODBC connection to the 400
and selects the desired columns/records and puts them in a dataview. I then
simply do an Insert for each record into our SQL server, since I need to
manipulate some of the non-standard date records on-the-fly.

If you have a lot of records, you should watch your memory usage. I do
around 45,000 records and it takes around a minute to do it....most of that
time is wasted just opening the ODBC connection!

Good luck!

-- Rob T.

 

[Brad Allison]

Lucas,

Thank you for the valuable information.

Being very new to this, I thought I had to have SQL Server to use DTS. Is
DTS something else altogether separate as DTS has always been recommended to
me.

If I were to use Bulk loading, what would be involved in that? Just using a
datareader to extract the data, save to text and then use this text to enter
this information into Access? Or is Bulk loading using some routine that I
might not be aware of?

I am going to look into MySQL as that may be the answer. Our business is
built on data. The AS400 stores around 10 GB of various tables of data.
Eventually what we need to do is to have a system that replicates the AS400
data and allow that data to be accessible to the web and some various
utilities in-house. Obviously we do not want to put the data from the AS400
directly on the web.

Thanks for putting up with these questions and your help.

Brad

 

[Lucas Tam]

Lucas,

Thank you for the valuable information.

Being very new to this, I thought I had to have SQL Server to use DTS.
Is DTS something else altogether separate as DTS has always been
recommended to me.

DTS is SQL Server's Data Transformation Services. It allows a database
Administrator to write a load script in graphical format. Check out this
website for more information:

http://www.sqldts.com/

DTS vs. Bulk Load + .NET code - DTS is easier to administer for a non-
programmer since it provides a graphical interface. But, DTS can become
hard to maintain if the script is complex, and DTS has a couple of
quirks I don't like as a programmer. Bulk load is great for straight
inserts - i.e. a table copy. You can also create a format file for SQL
bulk loads to handle custom formatting (to an extent). Additional
formatting for bulk loads will have to be done by your extract tool.

If I were to use Bulk loading, what would be involved in that? Just
using a datareader to extract the data, save to text and then use this
text to enter this information into Access? Or is Bulk loading using
some routine that I might not be aware of?

Here is the documentation for SQL Server's Bulk Insert Command:

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_ba-bz_4fec.asp

SQL Server also has a command called BCP:

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/coprompt/cp_bcp_61et.asp

I am going to look into MySQL as that may be the answer. Our business
is built on data. The AS400 stores around 10 GB of various tables of
data. Eventually what we need to do is to have a system that
replicates the AS400 data and allow that data to be accessible to the
web and some various utilities in-house. Obviously we do not want to
put the data from the AS400 directly on the web.

MySQL also has the ability to bulk load a text file:

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

MySQL is great - reliablity is great, speed is great, and the cost is
great too. As I mentioned in my previous post, I highly recommend mySQL
Manager from EMS Hi-Tech if you're looking for a MySQL Administration
Tool (like SQL Server's Enterprise Manager).

I think a bulk load or a DTS package would be the easiest solution for
you. Ask your AS/400 administrator to provide you with a daily extract
in the proper bulk load format (i.e. CSV) . Run the bulk load/DTS
package once per day to sync your web database with your AS/400 system.

Handling record changes maybe a bit more difficult - you may have to
delete the records from the SQL Database, and reinsert the update
records from the AS/400. Alternatively, you might want to create a
syncronization tool to handle updates.

I'm sure there are packages for syncing SQL server with AS/400... but I
haven't looked into that route as the packages are probably pricy.