Steps to access mainframe data using MS Excel or MS Access

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

The Microsoft tools Excel and MS Access can be used to access SQL data sources. If the SQL Option is installed on an IDMS mainframe, they can also be used to access the data residing there. However, several steps must be taken to accomplish that access.

 

 

Environment

IDMS - all supported releases

Resolution

For both MS ACCESS and MS EXCEL, you will need to create a FILE DSN (Data Source) in IDMS Server in order to connect to the IDMS mainframe.

These are the basic steps to accomplish that:

1. In IDMS Server, ODBC Administrator, select tab "File DSN"
2. Click on the "Add" button
3. Create New Data Source: select a driver : CA-IDMS, press Finish
4. This opens a new window titled CA-IDMS Server ODBC Administrator. Define a data source there as follows:
   
   
   1. Data Source : type in name of file data source: excelfile
   2. Dictionary : <the name of your mainframe dictionary>
   3. Server : <your System ID from the IDMS sysgen>
   4. When you hit OK, it will create the data source

 

You can hit the TEST button to see if the configuration is successful; the CV must be up for this to work. The test will open a logon window asking for your mainframe credentials in order to sign you on. When you click OK, it will test the data source connection.

Once the above steps are complete, the data source has been defined through which MS Excel and MS Access can access the mainframe. Next, in MS EXCEL or MS Access, from the top panel select Data:

1. Get External Data, New Database Query
2. Choose Data Source: scroll down until you find the newly created file DSN (excelfile), and press OK
3. CA-IDMS Driver Connect panel will appear, press Ok
4. "Connecting to Database" will display
5. In the 'Query Wizard - Choose Columns', you can then choose the IDMS mainframe table(s) and column(s) from which you want to obtain data, from the options presented.

There are several prerequisites to the above steps:

1. You should already be able to establish an ODBC or JDBC connection to this mainframe;
2. SQL option and Server option should be installed on your mainframe; and
3. The view SYSCA.ACCESSIBLE_TABLES should be installed in the dictionary specified in the data source you defined in the above steps.

If the above steps are not successful, review these prerequisites to determine if one of them may causing the lack of success.