ODBC for AS/400
Recently I’ve been doing a lot of work for companies that use an AS/400 in combination with DB2 databases to store their data.
Since I didn’t have any previous experience with either I had some trouble trying to determine the best way to connect to the database from the Microsoft Server that runs the Business Objects XI suite. I installed the software and that all went fine, but when I then wanted to set up a connection to the AS/400 server I got kind of lost, because I didn’t know whether it would be best to pick native software or standard ODBC drivers. When I decided to go for the ODBC drivers supplied by IBM I then found myself seeing a library I didn’t want to see.
Let me share how Universe Designer determines what files to display as its default list when you log into the AS/400. When you want to add a table or data source to a Universe, you have to make a connection via the parameters screen, using a connection – setting it as an alias – in combination with your username and password. I typically connect to any datasourse via an ODBC connection, so I open the ODBC folder and see a list of all of the ODBC data sources configured on my machine. I pick an AS/400 data source (one that I have created using the ODBC Data Sources control panel applet in the Windows Control Panel), and Universe Designer opens the data source and queries the AS/400 for physical files. What Universe Designer is doing behind the scenes is calling to SQLTables, an ODBC function that returns a list of qualifiers, owners, and tables. Translated into AS/400-speak, this is a list of AS/400 system names, libraries, and physical files.
Now, what you get in this list will depend on a number of things, the first of which is how you set up your ODBC Data Source. With the Client Access Express ODBC driver, you are allowed to specify a list of libraries to search in the Server tab of the ODBC Set-up applet. If you put a list of libraries in this field, you will get a list of objects in those libraries in BO. However, if you leave the default library list field blank, Client Access will default the entry to QGPL and only objects in QGPL will be displayed. Something to remember when setting up the connection for the first time, since you may not have Administrator rights to the machine and it might prove difficult to change afterwards.