I wanted to share some QlikView settings that I use daily for communications with our iSeries box. I use the ODBC driver “iSeries Access ODBC Driver” (version 13.00.01.00) which are included on the iSeries Client Access installations (I have the v7r1 version installed).
I have the ODBC connection setup under System DSN (good idea if on a server that way all the connected users can use it) called “SYS400”. On the Server tab, I use a SQL naming convention which does take some getting used to if you are a heavy IBMer. When you use the SQL Naming convention, your sql connection strings in QlikView will end up in this type of format: (ODBC DSN Name).(Library).(Member).
Below is an example of a SQL read from the iSeries box which I’m using the data for translating a composite key of company and customer bill-to to a customer name.
An important note: IBM System i Access is a licensed program and does require a license to System i Windows Family (Product ID: 5761-XW1) before use. I would recommend checking with your department that handles the iSeries machine(s) for a client access install disc or possibly calling IBM to see about getting just the ODBC driver. For Client Access installation instructions, I would recommend this install guide from IBM.
The next part will cover additional ODBC settings and best practices for data retrieval on a iSeries machine. Stay tuned!