Currently we are using MS ACCESS with pass-thru queries to extract data from the AS400 into fixed width flat files. Is MS ACCESS the best way to handle this? Can anyone suggest alternative methods? This will be scheduled at night, so it will need to run without any user interaction.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to extract data into a flat file from the AS400?
- Thread starter ddiamond
- Start date
With Data transfer from Iseries you can transfer a ISeries file to a flatfile on a PC. AFAIK this is not something that can be scheduled.
According to AS400 programmers I know it is pretty hard to schedule output from AS400 to a place outside the AS400 itself. You can schedule the creation of a file within AS400 and download data later I guess...
Ties Blom
Information analyst
According to AS400 programmers I know it is pretty hard to schedule output from AS400 to a place outside the AS400 itself. You can schedule the creation of a file within AS400 and download data later I guess...
Ties Blom
Information analyst
- Thread starter
- #3
I use various approaches. If you have Client Access ODBC installed you can hook up MS Excel through MS query from within a worksheet. We call this a refreshable sheet.
Unfortunately, MS query does not allow pass-through queries, so in that case we need to log on to fetch the data.
I have dabbled with VBA to automatically refresh the query, which seems to kind of work.
(like open a Excel-file and get the new refresh directly)
From Access I use a pass-through query with automatic logon through ODBC settings
Ties Blom
Information analyst
Unfortunately, MS query does not allow pass-through queries, so in that case we need to log on to fetch the data.
I have dabbled with VBA to automatically refresh the query, which seems to kind of work.
(like open a Excel-file and get the new refresh directly)
From Access I use a pass-through query with automatic logon through ODBC settings
Ties Blom
Information analyst
- Thread starter
- #5
So it sounds like my current access approach isn't so terrible. My management would love it if we stopped using Access because they've heard rumors that it is not very robust, but I have had no problems so far using strickly pass-thru queries. I was just checking to see if there were any good alternatives to Access for extracting files from the as400 to the PC, but from what you've said, it doesn't sound like it. I had experimented with Data Transfer from iSeries, but that requires user intervention. I've also played with a number of ETL tools, but they were all much too slow.
I ETL with informatica's Powermart. Possibly a bit too expensive to own. AS400 is not even a fully supported platform 
(works okay , though)
Access has its quirks, but if you use it solely as a query-tool you will be okay.
The instability starts with implementation of multi-user applications that share one backend.
If you start using code in Access databases tend to grow at an astonishing rate which is an excellent proof that if you want to use it's full potential it really is an MS product
Weird idea:
If you have a machine with SQL server you can fetch AS400 data using DTS, schedule jobs on SQL server and export to any other server in the domain.
Well, tried that way over at my comp...
Ties Blom
Information analyst
(works okay , though)Access has its quirks, but if you use it solely as a query-tool you will be okay.
The instability starts with implementation of multi-user applications that share one backend.
If you start using code in Access databases tend to grow at an astonishing rate which is an excellent proof that if you want to use it's full potential it really is an MS product
Weird idea:
If you have a machine with SQL server you can fetch AS400 data using DTS, schedule jobs on SQL server and export to any other server in the domain.
Well, tried that way over at my comp...
Ties Blom
Information analyst
- Thread starter
- #7
Thanks for the suggestions, Ties Blom. I am loading the data into Oracle, but we will soon be switching from oracle to DB2 on AIX. Does DTS only work with SQL Server? The main problem I found with the ETL tools that I tested is that they tried to load the data into are target database using inserts and updates instead of using oracle's native sql-loader utility. My benchmark tests showed the SQL-Loader to be approximately 30 times faster than loading the data with inserts and updates.
As for Access quirks, I did run into locking issues because I was running 3 instances of the access database in parallel. I solved this problem by creating a cmd file that makes three separate copies of the mdb and executes those 3 copies in parallel. Since I did that, the locking issues vanished. The strange part is that I'm only doing selects, no updates or inserts, so I don't know why I would have had locking issues.
As for Access quirks, I did run into locking issues because I was running 3 instances of the access database in parallel. I solved this problem by creating a cmd file that makes three separate copies of the mdb and executes those 3 copies in parallel. Since I did that, the locking issues vanished. The strange part is that I'm only doing selects, no updates or inserts, so I don't know why I would have had locking issues.
Yes, DTS is strictly SQL server.
Anyway Informatica offer an ETL solution that can use the native bulk-loaders of both ORACLE and SQL server (as well as Sybase I think)
The bulkload utilities bypass logging so they will be a LOT faster.
For SQL server I have no ETL license, so I write flatfiles to the server and use post-session SQL to do a bulk insert.
On windows this will work with utilities as rcmd/osql, but I have no idea if AIX has anything similar...
Access allows you to set isolation level to a certain degree. I have applications that are used by multiple persons without ever running into locking problems (even with inserting and updating data)
Ties Blom
Information analyst
Anyway Informatica offer an ETL solution that can use the native bulk-loaders of both ORACLE and SQL server (as well as Sybase I think)
The bulkload utilities bypass logging so they will be a LOT faster.
For SQL server I have no ETL license, so I write flatfiles to the server and use post-session SQL to do a bulk insert.
On windows this will work with utilities as rcmd/osql, but I have no idea if AIX has anything similar...
Access allows you to set isolation level to a certain degree. I have applications that are used by multiple persons without ever running into locking problems (even with inserting and updating data)
Ties Blom
Information analyst
- Thread starter
- #9
Thanks for your tips, Tie Blom. If changing the isolation level solves my locking issues, that would certainly be a cleaner solution than making three copies of the mdb. I may have to take another look at Informatica. I had spoken to an Informatica consultant who didn't believe it could take advantage of Oracle and DB2's bulk loaders, but he may have been wrong. I personally think Informatica is too expensive, but I guess that depends on how badly management wants to get off of Access.
Dead Wrong..
Version I work with (=6) supports bulk load to ORACLE.
New version 7 supports bulk load to DB2 as well.
I guess that buying the tool will depend upon your needs.
If you have very simple ETL processes It will probably be too expensive to justify..
Ties Blom
Information analyst
- Thread starter
- #11
Well you might try a third party add-on product like Barnard FTP software. We used this on a mainframe to FTP files directly to a server. Works with IBM Personal Communications software.
We have printed to personal print que and retrieved it later. AS400 might be able to use it to print to file from the Print Que.
If you do not like my post feel free to point out your opinion or my errors.
We have printed to personal print que and retrieved it later. AS400 might be able to use it to print to file from the Print Que.
If you do not like my post feel free to point out your opinion or my errors.
MdnghtPgmr
Programmer
ddiamond,
On the AS/400 side you might want to look into the CPYTOIMPF (copy to import file) and/or CPYTOSTMF (copy to stream file) commands. Those will make a PC type of file (with delimiters if you want them) on the IFS of the AS/400.
HTH,
MdnghtPgmr
On the AS/400 side you might want to look into the CPYTOIMPF (copy to import file) and/or CPYTOSTMF (copy to stream file) commands. Those will make a PC type of file (with delimiters if you want them) on the IFS of the AS/400.
HTH,
MdnghtPgmr
- Thread starter
- #14
MdnghtPgmr
Programmer
ddiamond,
Yes, the CPYTOIMPF can be submitted and therefore scheduled. You would need to write a CLP (or REXX) to execute the command then put the CLP (or REXX) into the system scheduler. See the InfoCenter for more info at:
On the left click on "Programming" then "CL" then "Alphabetic list" then "C" then scroll to the "CPYTOxxxx" commands.
HTH,
MdnghtPgmr
Yes, the CPYTOIMPF can be submitted and therefore scheduled. You would need to write a CLP (or REXX) to execute the command then put the CLP (or REXX) into the system scheduler. See the InfoCenter for more info at:
On the left click on "Programming" then "CL" then "Alphabetic list" then "C" then scroll to the "CPYTOxxxx" commands.
HTH,
MdnghtPgmr
- Status