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


Article ID: 52498


Updated On:





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>

Figure 1

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

<Please see attached file for image>

Figure 2

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

<Please see attached file for image>

Figure 3

Press Finish to complete the Create New Data Source process.

<Please see attached file for image>

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.

<Please see attached file for image>

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:

<Please see attached file for image>

Figure 6

Choose Data Source.

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

<Please see attached file for image>

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.

<Please see attached file for image>

Figure 8

Filter the data if required.

<Please see attached file for image>

Figure 9

Select the table and press OK.

<Please see attached file for image>

Figure 10

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

<Please see attached file for image>

Figure 11

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

<Please see attached file for image>

Figure 12


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


Component: IDMS


1558713693947000052498_sktwi1f5rjvs16t5o.gif get_app
1558713691971000052498_sktwi1f5rjvs16t5n.gif get_app
1558713689311000052498_sktwi1f5rjvs16t5m.gif get_app
1558713687343000052498_sktwi1f5rjvs16t5l.gif get_app
1558713685008000052498_sktwi1f5rjvs16t5k.gif get_app
1558713683065000052498_sktwi1f5rjvs16t5j.gif get_app
1558713680782000052498_sktwi1f5rjvs16t5i.gif get_app
1558713678898000052498_sktwi1f5rjvs16t5h.gif get_app
1558713676770000052498_sktwi1f5rjvs16t5g.gif get_app
1558713674795000052498_sktwi1f5rjvs16t5f.gif get_app
1558713671161000052498_sktwi1f5rjvs16t5e.gif get_app
1558713668552000052498_sktwi1f5rjvs16t5d.gif get_app