Connecting to AS400 from SSAS 2008 Project

Posted on 2009-12-16
Medium Priority
Last Modified: 2016-02-12
I am trying to connect to AS400 server using IBM iSeries Access for Windows from an SSAS 2008 project (from BIDS). I am able to create a datasource to the AS400 Server using IBMDA400 provider and the connection test is also successful. But when I try to create a data source view based on this datasource, it is not dispalying any tables/views from the underlying AS400 database. Why is it not displaying the tables/views list?

Secondly, I have created a blank data source view based on this datasource. Then I have tried to create a named query using a query (which fetches the data from a table from AS400 database). It is able to resolve all the columns names (ie it is replacing the * with the column names) and when I run the query in Named Query definition window, it is giving the results. But when I click on OK button to save the Named Query, it is not displaying the columns names in the DSV pane and the Named Query is not getting recognized when I try to select a table from the existing DSV while creating a Dimension. Please do let me know the reason for this. Bit urgent.
Question by:Srinivas_Vengala
  • 2
  • 2
LVL 36

Expert Comment

by:Gary Patterson
ID: 26068867
Please post your connection string for data source.

I'm not sure about the second issue.  PErhaps another expert can shed some light.

- Gary Patterson


Author Comment

ID: 26069981
Here is the connection string:

Provider=IBMDA400.DataSource.1;Data Source=abc.xyz.com;Persist Security Info=True;Password=**********;User ID=test;Initial Catalog=db1
LVL 36

Accepted Solution

Gary Patterson earned 1500 total points
ID: 26073602
Based on your connection string, here are a couple of thoughts:
  • The user ID that you are using ("test"), might not have rights to the libraries or tables required.
  • Does your "Initial Catalog" name match the RDB name configured on the AS/400?  You can use the WRKRDBDIRE and look for the entry with a remote location of *LOCAL.  That is the name that you need to use as the Initial Catalog.  You can generally drop this parameter when connecting to the AS/400.
  • You are not specifying a library list or default collection, so the provider will default to the system library list.  These libraries may not contain the tables that you are interested in.  You can specify the "Catalog Library List=lib1,lib2,lib2..." connection string parameter to define the library or libraries that you are interesting in browsing, or the "Default Collection=lib1" parameter to specify a single library.  Note that the library list is referenced when making unqualified references to table names, and is used when prompting.  When executing queries, if you qualify the table name, then the library list does not matter.
- Gary Patterson

Check out my EE profile: http://www.experts-exchange.com/M_4382324.html


Author Closing Comment

ID: 31666738
I will check this

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

594 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question