Tuesday, June 7, 2011

Loading data from AS/400(iseries,i5/OS) into Qlikview

Qlikview is an in-memory management BI technology allows to connect and combine data from different data sources. You can download and install a free personal edition from the website http://www.qlikview.com/. I will be posting about my experience in working with Qlikview.


Qlikview can connect to AS/400 (iSeries,i5/OS) and reload data using the SQL scripts. You will need to install client access for iSeries i5/OS from IBM and refer to my previous post http://shivakamathblog.blogspot.com/2008/11/configure-odbc-to-access-iseriesas400.html for more details.


Once the Qlikview and iSeries access is installed we can connect to AS/400 using the OLE DB provider. Start the Qlikview application and open a new document from the File menu. Open the scripting window using the option Edit Script… on the File Menu. At the bottom of the scripting window, select the Data tab as shown below


OLE DB Connect


From the Database, select the OLE DB from the dropdown list and click on the connect button. This will open a Data link Properties dialog box:


OLE DB Dialog


Select the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider, and click on the connection tab:


OLE DB Server


On the Data Source textbox, enter the Server name or the IP address of the AS/400 machine. Enter the AS/400 sign on user name and password and the Catalog name of the AS/400. Please contact your AS/400 administrator for any help. Make sure “Allow saving password” is checked and “Blank password” is unchecked.
Click OK button and it will insert the connection string (starting with OLEDB Connect …. ) in the scripting window:



OLE DB String 0


After the connection string, type the SQL statement as required. Click the Save option from the Toolbar and then Click OK button below to close the scripting window.
From the File menu, select Reload option to run the script. After reloading is finished, the data is available in Qlikview document to create objects like Charts or Lists.



In the next posting, I will write on creating the charts and other objects.

3 comments:

Anonymous said...
This comment has been removed by a blog administrator.
malini ecorp said...

Thanks for sharing this useful info. Keep updating

same way.
Regards,Siddu Online Training

arun kumar Gupta said...

Hi Shiva,

Thanks for information.

which port needs to be opened ?

I am getting Firewall blockage error

Thanks in advance


Arun