How to load IDMS Mainframe SQL data to a Microsoft Excel spreadsheet?

Products

• CA IDMS
• CA IDMS - Database
• CA IDMS - ADS

Issue/Introduction

Solution:

To populate either Microsoft Access databases or Microsoft Excel spreadsheet with SQL data obtained from the IDMS Mainframe database, one method for ODBC access is using a file data source.

The two steps involved are creating a file data source in IDMS Server 16.1 and accessing the file data source in MS Excel.

Step 1: In IDMS Server, Start/Programs/CA/CA IDMS Server/ODBC Administrator, select tab "File DSN".

Click on the "Add" button.

<Please see attached file for image>

Create New Data Source: select a driver: CA-IDMS, press Next.

<Please see attached file for image>

Create New Data Source: type in name of file data source: excelfile, press Next.

<Please see attached file for image>

Press Finish to complete the Create New Data Source process.

<Please see attached file for image>

The next screen will be the CA-IDMS Driver Connect. Fill in the following:

Dictionary: SYSDICT (Dictionary name is the DBNAME or segment name of the dictionary that contains the definitions of the tables to be accessed.

The dictionary name should be defined in the DBNAME table. In this example, SYSDICT is the dictionary name).

Node Name: SYST1700 (SYSTEMID from the IDMS SYSGEN SYSTEM statement)

(or in the IDMS CV, at the Enter Next Task Code prompt, issue a 'DCMT DIS ME PLE CCILINE' command and the system ID will be at x'08')

Userid: USER1 (A valid Userid allocated access to this IDMS CV)

Password: password (only use if the IDMS CV is secured)

Leave the Optional and CCI Options blank, and press OK.

<Please see attached file for image>

Step 2: On the PC, Start/Programs/Microsoft Office/Microsoft Office Excel 2007; bring up MS Excel.

Click on the Data tab. From the left panel 'Get External Data', click on 'From Other Sources' and select

'From Microsoft Query' which includes the ability to import data by using ODBC:

<Please see attached file for image>

Choose Data Source.

Scroll down until you find the newly created file DSN: excelfile, press OK.

<Please see attached file for image>

CA-IDMS Driver Connect panel will appear, press OK.

"Connecting to Database"

Query Wizard - Choose Columns

Then choose the table and columns that you wish to obtain information from the IDMS mainframe.

<Please see attached file for image>

Filter the data if required.

<Please see attached file for image>

Select the table and press OK.

<Please see attached file for image>

There's an IDMS ODBC connection to the IDMS Mainframe database to retrieve the SQL data. Press OK.

<Please see attached file for image>

The requested table DENTAL-CLAIMS is loaded to the Microsoft Excel spreadsheet.

<Please see attached file for image>

References:

CA IDMS Server User Guide Chapter 5: Configuring the Client on Windows - Defining Data Sources

Environment

Release:
Component: IDMS