I can remember System 32, OK? Then came System 36 and I think there was a System 38 in there too. But I can really remember when IBM announced the AS/400 as the godbox of all end-user computing. Can you believe that I got certified in LU 6.2?
Because I'm so long in the system tooth. I've had the pleasure of getting data from an AS/400 source and putting it into a client/server interface. You realize of course that people had to be convinced. There are fewer things more aggravating that having to put up with the nonsense of AS/400 gurus as they begin fulminating about the inferiority of every other API and what a waste of time it is to replicate data from the 400 onto another platform. I patiently explain to them that their bosses bosses boss wants historical information charted in color and they give me one of these grimaces usually found on the face of Michael Moore when the security guards escort him from the premises. Yeah but I'm right, they smirk to themselves.
It is at times like these when I think of my girlfriend's sister's boyfriend who married her and moved up to Stockton, California. He was the god or RPG and drove a Mitsubishi Starion. Whoa. OK I exaggerate, it was a Sapporo. But I still couldn't bring myself, ghetto child that I was, to give up life in Los Angeles, even though he had a house and made 36 Thousand Dollars. That was 1980, and I had to hold on to my dreams. I knew RPG, in fact I knew RPG II. It was torture, I couldn't submit to its petty demands, and I wasn't ready to get married.
If you happen to find yourself staring into the wall eyes of an AS/400 guru, do as I do. Count backwards from 10 slowly and tell them how much respect you have for the innovative microcode of the AS/400 instruction set. Then turn around and run like the wind. If you actually have a mandate to work with this dweeb then keep a stiff upper lip and do what you need to do to get your data. Prostrate yourself.
Seriously, you should do whatever possible to get all necessary AS/400 data into SQL Server. You should get a nightly job to push transactions from the 400 onto a separate NT server and then use the ODS on NT.
Generally a customer will have green screens running against the 400. I know there's something of a conceit that the 400s are big hardware, but there really are no 400 databases out there that are too large for MSSQL. The 400 is really all about capturing transactions and they can't afford to run ad-hoc queries.
The best way to manage it is to add a native query job on the 400 side and dump that into a flat file on the 400 in a new outbucket directory. You can then FTP or map to the outbucket and initiate a transfer from the NT machine.
This has three primary advantages.
1. The 400 administrator will have control of the query and when it runs and where the output goes. He can optimize all that on his side. Nothing leaves his control.
2. The only thing that is initiated from the outside is a file transfer. That shouldn't interrupt anything on the 400 side.
3. Once you have the data on NT you can Perl it, DTS it into MSSQL and manipulate it any way you please. You get out of the 400's hair and you're essentially free.
The important thing is to make sure you get everything you need in that one big fat nightly query. Chances are you won't be able to get master data but ask anyway. Your better chance is to run master data from the data itself.
Now go have a chai spice. You win.