Accessing data on Mainframes

ngulliver — Mon, 03 Feb 2014 10:32:25 GMT

Hi,

Can anyone recommend the best way of accessing data on the following:

IBM 3270 mainframe

AS400

RS6000

Can they all be accessed using ODBC connection ? Or do they require effort on their part to send data out into a compatible format ?

Regards,

Neil

Re: Accessing data on Mainframes

Gysbert_Wassenaar — Mon, 03 Feb 2014 10:52:10 GMT

See this document: Mainframe Database Connectivity

Re: Accessing data on Mainframes

Mon, 03 Feb 2014 10:58:38 GMT

Hello Neil,

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;

... and away you go.

Good luck,

John

Re: Accessing data on Mainframes

rajeshvaswani77 — Mon, 03 Feb 2014 11:02:08 GMT

Hi Niel,

It will mostly be legacy.

Have it converted to text files.

Build a dashboard from text files as source and then do away with the Mainframe.

thanks,

Rajesh Vaswani

Re: Accessing data on Mainframes

ngulliver — Mon, 03 Feb 2014 11:21:18 GMT

Hi.

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.

Cheers,

Neil

topic Re: Accessing data on Mainframes in QlikView

Accessing data on Mainframes

Re: Accessing data on Mainframes

Re: Accessing data on Mainframes

Re: Accessing data on Mainframes

Re: Accessing data on Mainframes