Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
ngulliver
Partner - Specialist III
Partner - Specialist III

Accessing data on Mainframes

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See this document: Mainframe Database Connectivity


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

See this document: Mainframe Database Connectivity


talk is cheap, supply exceeds demand
Not applicable

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.

SRT.jpg

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

rajeshvaswani77
Specialist III
Specialist III

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

ngulliver
Partner - Specialist III
Partner - Specialist III
Author

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