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

Document ID : KB000052498
Last Modified Date : 14/02/2018
Show Technical Document Details


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.

Figure 1

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

Figure 2

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

Figure 3

Press Finish to complete the Create New Data Source process.

Figure 4

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.

Figure 5

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:

Figure 6

Choose Data Source.

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

Figure 7

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.

Figure 8

Filter the data if required.

Figure 9

Select the table and press OK.

Figure 10

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

Figure 11

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

Figure 12


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