Excel & DB2

Mark Madsen

Excel & DB2

Looking for a 'Using Excel & DB2 For Dummies" manual.  Used to use Excel to run a  query in DB2, pull the data back into Excel and report/graph on the results.  Anyone have information on how to set up my environment to do this?

Lance Jackson

Excel & DB2
(in response to Mark Madsen)
Mark,



Here are the steps and screenshots to create a data source named LJHD which accesses data on DB2 subsystem DBHD:



1. Open EXCEL as administrator - I had to access it via Windows Explorer and drill down to the executable in order to be able to right-click and Run as Administrator. The path of the executable should be: C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.exe
2. Open a new spreadsheet
3.
4.
5.
6. (Note: substitute your mainframe user ID in the User ID field)
7. (Note: substitute your target table name in the Default table field if required)
8. Click OK to connect to the database
9.
10.
11.
12.
13.
14.
15.
16.





From: Mark Madsen [mailto:[login to unmask email]
Sent: Wednesday, September 20, 2017 09:54 AM
To: [login to unmask email]
Subject: [DB2-L] - Excel & DB2



Looking for a 'Using Excel & DB2 For Dummies" manual. Used to use Excel to run a query in DB2, pull the data back into Excel and report/graph on the results. Anyone have information on how to set up my environment to do this?



-----End Original Message-----

Attachments

  • image001.jpg (82.8k)
  • image002.jpg (96k)
  • image003.jpg (105.1k)
  • image004.jpg (102.4k)
  • image005.jpg (98.9k)
  • image006.jpg (95.2k)
  • image007.jpg (95.1k)
  • image008.jpg (97.1k)
  • image009.jpg (93.3k)
  • image010.jpg (92.6k)
  • image011.jpg (88k)
  • image012.jpg (92.2k)
  • image013.jpg (91.9k)

Mark Stone

Excel & DB2
(in response to Mark Madsen)
I do this on occasion using the Microsoft 4.0 DB2 OLE driver. The settings
get stored in an ODC file.

I think the steps are:

Blank worksbook
Data Tab
From Other Sources button - select From Data Connection Wizard
Data Connection Wizard - select Other/Advanced
Next
Data Link Properties - select Microsoft OLE DB Provider for DB2
Next
Data Link Properties - Data Source Name (e.g IDUG)
Data Link Properties - click ... (set ip and port of server)
Data Link Properties - Initial Catalog (I use database name)
Data Link Properties - package collection (NULLID)
Data Link Properties - You may need to create server side packages
ok
Data Connection Wizard - uncheck Connect to a specific Table
next
Data Connection Wizard - name the odc file
finish
it may have you logon
Select Table - (dont select anything)
ok
Import Data - click properties
Connection Properties - Definition tab
Connection Properties - Command type dropdown - SQL
Connection Properties - Command Text (your SQL statement)
ok (may get change/save dialog)
ok
SQL runs and worksheet contains output


On Wed, Sep 20, 2017 at 6:54 AM, Mark Madsen <[login to unmask email]> wrote:

> Looking for a 'Using Excel & DB2 For Dummies" manual. Used to use Excel
> to run a query in DB2, pull the data back into Excel and report/graph on
> the results. Anyone have information on how to set up my environment to do
> this?
>
> -----End Original Message-----
>

Tommy Petersen

RE: Excel & DB2
(in response to Mark Madsen)

DB2 LUW or z/OS?