I'm not an IBM expert (my expertise is DEC) but we do run Lawson M3 on an IBM i-Series and extract the data with no problems at all. We have the System i Access for Windows client, and this allows us to define connections using the windows ODBC administrator. Just point to the SQL default library and the library list to the relevant IBM library.
Then you need to add a connection string. This was the only part where we had to do a bit of Google searching.
OLEDB CONNECT TO [Provider=IBMDA400.DataSource.1;Persist Security Info=True;User ID=***USERNAME***;Password=***PASSWORD***;Data Source=LAWSONDR;Protection Level=None;Extended Properties="";Initial Catalog=LAWSONDR;Transport Product=Client Access;SSL=DEFAULT;Force Translate=65535;Default Collection=REPORTING;Convert Date Time To Char=TRUE;Catalog Library List=M3DDTAPRD;Cursor Sensitivity=3;Use SQL Packages=False;SQL Package Library Name="";SQL Package Name="";Add statements to SQL package=True;Unusable SQL Package Action=1;Block Fetch=True;Data Compression=True;Sort Sequence=0; Sort Table="";Sort Language ID="";Query Options File Library="";Trace=0;Hex Parser Option=0;Maximum Decimal Precision=31;Maximum Decimal Scale=31;Minimum Divide Scale=0;Library List=M3DDTAPRD;Naming Convention=0;Query Optimize Goal=0;Query Storage Limit=-1;Handle Numeric Data Errors=1;Keep Trailing Blanks=False;Application Name=QvConnect32.EXE;Decfloat Rounding Mode=0;Decfloat Error Option=0;Client User ID="";Client WorkStation Name="";Client Program ID="";Client Accounting="";Block Size=0];
There are probably a number of unnecessary fields in here, and many where we are simply including default values, but I'm not an expert, and if it works ...
After this it's simply a matter of :
LOAD MRTRNR as DistOrder, MRITNO as Item, Left(MRITNO,3) as M3Category, Left(MRITNO,11) as StyleColour, MRTRQT-MRRPQT as DOQTY, MGRIDT as DODate1, MGTRDT as DODate2, num(MakeDate(Left(MGRIDT,4),mid(MGRIDT,5,2),mid(MGRIDT,7,2))) as DODate
; SQL SELECT MRTRNR, MRITNO, MRTRQT, MRRPQT, MGRIDT, MGTRDT from M3DDTAPRD.MGLINE join M3DDTAPRD.MGHEAD on MGCONO=MRCONO and MGTRNR=MRTRNR where MRCONO='1' and MGTWLO='X01' and MRTRQT>MRRPQT;
Thanks for your input. I have a background in legacy systems and have come across a lot of these systems in financial institutions so assumed there must be a way of pulling data easily from these host systems.